SISTEMA CONTABLE EN EXCEL DESDE CERO III

1.-Control Total

En la celda F28 de la Hoja Asientos! incluiremos un control total de nuestra carga de asientos:

=SI(B10="","",+SI(CONTAR(B10:B25)=CONTAR.SI(H10:H25,"OK"),SI(Y(F26="OK",D26="OK"),"Asiento correcto","¡ERROR!"),"¡ERROR!"))

2.-Diseñando Diario General

Ya hemos diseñado nuestra plantilla para cargar los asientos, ahora nos vamos a nuestra Hoja DatosM, Seleccionamos desde la Celda A202 hasta la Celda I202, clic derecho, copiamos y nos ubicamos en la Hoja Diario en la Celda A10 y pegamos.

Seleccionamos de la Celda A1 a Celda I1 y combinamos y centramos a continuación insertamos la Función
=Menú!B1      Estilos-->Estilo de Celda-->Titulo, Sin bordes, Color de Fuente-->Anaranjado,Énfasis6, Oscuro 50%, Negrita.

En la Celda A5 Escribimos "FECHA", Sin bordes,  Estilos-->Estilo de Celda-->Titulo 3, Sin bordes, Color de Fuente-->Anaranjado,Énfasis6, Oscuro 50%, Negrita.

Seleccionamos las Celdas B5 y C5 y combinamos y centramos e Insertamos la función: =HOY()      Color de Fuente-->Anaranjado,Énfasis6, Oscuro 50%, Negrita. Alinear texto a la Izquierda, Formato de Fecha-->14 de Marzo de 2001 (pongo esta fecha porque así se encuentra en el cuadro de diálogo de Fecha).

Seleccionamos de la Celda A6 a la Celda I6, combinamos y Centramos, escribimos "DIARIO GENERAL"
 Estilos-->Estilo de Celda-->Titulo, Sin bordes, Color de Fuente-->Anaranjado,Énfasis6, Oscuro 50%, Negrita.

En la columna FECHA, en Celda A11 insertamos la Fórmula: =SI(B11<>"",Asientos!B4,"")    Nos ubicamos en la fórmula en donde está B4 y hacemos clic a la Tecla F4, de tal manera de que nos aparezca así: =SI(B11<>"",Asientos!$B$4,"") y la copiaremos hasta la Celda A5000.

En la columna CONCEPTO haremos algo parecido a la formula anterior en la Celda I11: =SI(B11<>"",Asientos!$A$6.

Es importante decir que tendremos que proceder de la misma forma que en la Hoja DatosM, es decir, debemos reducir la Columna H hasta 0.08.px.

3.-Insertar Macro. 

En la Hoja Asientos nos vamos a la pestaña de PROGRAMADOR-->Macros-->Crear o PROGRAMADOR-->Visual Basic.
y ponemos:

Sub Cargar_asiento()
Dim Asiento_Nº

'Consistencia de la carga

If  Range("F28") = "Asiento correcto" Then

'Copiando carga de Datos de asiento

    Range("A10:G25").Select
    Selection.Copy

'Guardar asientos en Hoja DatosM

    Sheets("DatosM").Select
    Range("C5000").Select
    Selection.End(xlUp).Select
    Selection.Offset(1, -1).Select

 'Guardar asientos

   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 'Contabilizar

    Sheets("Diario").Select
    Range("C5000").Select
    Selection.End(xlUp).Select
    Selection.Offset(1, -1).Select
 'Pegar datos de asiento
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
 'Inicio
    Application.CutCopyMode = False
    Range("B10").Select
   
 'Mensaje indicando numero de asiento

     Sheets("Asientos").Select
     Asiento_Nº = Range("A10").Value
     MsgBox ("Se ha contabilizado el Asiento Nº " & Asiento_Nº)

 'Numerar asiento
     
     Range("A10").Value = Asiento_Nº + 1
   
 'Limpiar carga de siento
   
     Range("E10:G25,B10:C25").Select
     Selection.ClearContents
     Range("B10").Select
Else
     MsgBox ("Existen errores en la carga del asiento, por favor verificar")
End If

End sub

En la Hoja Asientos en la forma que ya hemos insertado Contabilizar     
le damos clic derecho y en Asignar macro seleccionamos Cargar_Asiento.

Carguemos un asiento para ver cómo funciona

Asiento Cargado por Venta Realizada

Hacemos clic en el Botón Contabilizar

Los siguientes  son Nuestro DIARIO GENERAL y RESUMEN DE ASIENTOS (en la HojaDatosM), al enviar nuestros asientos estamos evitando errores ya que si por error borramos los datos en el LIBRO DIARIO, aún nos quedarían los datos en DatosM, los cuales utilizaremos para nuestros Informes y Reportes Contables, más adelante.

Loas Asientos fueron Cargados de manera correcta.

En DatosM también se cargaron.

Como se podrá Observar en nuestra Plantilla Asientos de Diario se eliminaron los datos contabilizados y en la columna Asientos Nº , se puso el número 2 automáticamente, sin embargo, para rellenar otras plantillas, podemos ponerle el asiento que queramos en la Celda A10,  puesto que en las demás celdas de esa columna ya tenemos insertadas fórmulas.

En el siguiente post continuaremos con el diseño de nuestro Sistema Contable, espero que los post anteriores les hayan servido. Debo aclarar que cometí un error en el post anterior al escribir que en la Columna I10 insertamos una fórmula, pero, tal fórmula debe ser insertada en la Columna H, ya corregí el error en el post, ofrezco disculpas.

Leer más

SISTEMA CONTABLE EN EXCEL DESDE CERO II

Continuamos con el diseño de nuestro sistema contable en excel, ahora nos iremos a la hoja2 a la que le cambiamos el nombre por "Asientos".

1.- Diseñar plantilla.

Nos situamos en la celda A1 e insertamos la función  =Menú!B1, para que el nombre de la organización aparezca más adelante, seleccionamos de la celda A1 a la G1 y damos clic en la cinta de opciones de excel a Inicio-->Combinar y centrar; Estilos-->Estilo de celda-->Titulo,  Fuente-->Sin bordes, -->Color de fuente-->Anaranjado,enfasis6,oscuro50%, Negrita.

En la Celda A3 tipeamos  "ASIENTOS DE DIARIO", Seleccionamos de A3 a G3 y clic en el icono de combinar y centrar, Estilos-->Estilos de celda-->Titulo1,  Fuente-->Sin bordes, -->Color de fuente-->Anaranjado,enfasis6,oscuro50%, Negrita

En la celda A4 Tipeamos "FECHA" Estilos-->Estilos de celda-->Titulo2,   Fuente-->Sin bordes, -->Color de fuente-->Anaranjado,Énfasis6,oscuro50%, Negrita

En la CeldaB4 Escribimos la función: =HOY( ), hacemos clic derecho y elegimos "Formato de Celdas"-->Número-->Fecha y elegimos la que nos proporciona lo siguiente: "04 de Agosto de 2014"(21 de Marzo de 2001).   Seleccionamos de B4 a D4, combinamos y centramos. Alineación-->Izquierda, Color de fuente-->Anaranjado, enfasis6, oscuro50%, Negrita.

En la celda A5 escribimos "Concepto",  Color de fuente-->Anaranjado, Énfasis6, oscuro50%, Negrita, Estilo de Celda-->Entrada, Borde-->Superior Color de Borde Anaranjado, Énfasis 6.
Celda C6-->Borde Diagonal, Seleccionamos Celdas D5, E5, F5 y le ponemos Borde Inferior, Color de Borde Anaranjado, Énfasis 6.

Seleccionamos de la Celda A6 a la Celda F8 y hacemos clic en Combinar y centrar, ponemos Borde Izquierdo, Borde Derecho y Borde Inferior,  Color de Borde Anaranjado, Énfasis 6.

De la celda A9 a G9 pondremos los títulos Siguientes: AsientoNº(A9), Cuenta(B9), D/H(C9), Descripción(D), Debe(E9), Haber(F9), Detalle(G9).

Seleccionamos Rango A9:G25 y le ponemos Borde Cuadro Grueso, Color de Borde Anaranjado, Énfasis 6. Seleccionamos de la Celda A9 a la Celda G9, le damos "Clic Derecho"-->Formato de Celda-->Relleno-->Efectos de Relleno-->Estilos de Sombreado-->Horizontal, Color1 Blanco, Color 2 Anaranjado, Énfasis 6.
Seleccionamos el Rango A10:G25 y le ponemos "Todos los bordes", color de Bordes Blanco. Es importante Cambiar el tamaño de las columnas, de tal manera que los datos que habremos de ingresar ajusten perfectamente en el tamaño de las Celdas.

2.-Insertar Funciones

En la Celda A10 no escribiremos nada ya que la macro que crearemos nos proporcionará el Número de Asiento. En la Celda A11 pondremos la Función  =SI para que si existe un numero de cuenta, nos copie, el número de Asiento que corresponda,  la Fórmula es:

                                       =SI(B11<>0,A10,"")

Esta Fórmula la Copiaremos hasta la celda A25.

En la Celda D10 de la columna Descripción, escribiremos una fórmula para que escriba el nombre de la cuenta cuyo número hemos ingresado, ya en el post anterior le asignamos un nombre a nuestro plan de cuentas, Insertaremos las funciones SI, ESERROR y BUSCARV,     la Fórmula es la siguiente:

=SI(B10="","",SI(ESERROR(+BUSCARV(B10,PLAN,2,0)),"Número de cuenta incorrecto",+BUSCAV(B10,PLAN,2,0)))

Esta Fórmula la copiaremos hasta la Celda D25.

En la columna D/H haremos una validación de datos para llevar un mejor control sobre nuestra carga de asientos y evitar en lo posible la carga de datos erróneos:
 Seleccionamos el Rango C10:C25, hacemos clic en Datos en la cinta de opciones-->Validación de Datos-->Configuración-->Permitir = Lista, Origen=D/H; Mensaje de Entrada-->Titulo = Ingresar D/H, Mensaje de Entrada = Por Favor indicar Debe(D) o Haber(H).  Aceptar.

En la Celda H10 ingresaremos las funciones SI, Y, O, para asegurarnos de que nuestra carga de asientos se ha hecho correctamente ya que la carga de asientos es lo más importante para poder hacer nuestros informes: DIARIO, BALANCE GENERAL Y ESTADO DE RESULTADOS. La Fórmula es:

=SI(B10="","",+SI(O(Y(C10="D",E10>0,F10=0),Y(C10="H",F10>0,E10=0,"OK",SI(Y(E10+F10>0,C10=""),"Indicar D/H","Inconsistencia")))

Esta fórmula la copiaremos hasta la celda H25.

En la Celda D26 insertaremos una fórmula para asegurarnos de que los números de cuenta ingresados sean correctos:
          =SI(+CONTAR.SI(D10:D25,"Número de cuenta incorrecto")=0,"OK","¡ERROR!")

En la Celda F26 Pondremos =SI(E27-F27=0,"OK",+E27-F27)          Esto es para asegurarnos de que las sumas de las cantidades ingresadas tanto en el Debe cómo en el Haber sean iguales.

En la celda D27 Escribiremos "Sumas Iguales:".
En la Celda E27 Haremos la Suma del Debe  =SUMA(E10:E25)     En la Celda F27 Sumaremos el Haber   =SUMA(F10:F25)
Seleccionamos las Celdad E27 y F27 y le ponemos todos los Bordes más doble borde inferior.

Aproximadamente en la celda D27 insertamos una Forma cómo ya lo mencioné en el post anterior y le pondremos como texto "Contabilizar".

Esto es un blogspot por lo que continuaremos con la creación de nuestro Sistema Contable en el siguiente post para poder poner toda la macro que insertaremos.

Leer más

SISTEMA CONTABLE EN EXCEL, DESDE CERO

El sistema de contabilidad que diseñaremos es básico, pero servirá para poner los cimientos de un sistema más complejo. Debido a la cantidad de información necesaria para explicar los procedimientos necesarios para crear nuestro sistema de contabilidad tal vez se requiera de varios post, los cuales iré insertando y actualizando.

1.-Insertar hojas.

Para este sistema de contabilidad tendremos que insertar y cambiar de nombre a las hojas. Para este caso en excel 2007 debemos insertar cinco hojas más de las tres que ya tiene excel: hoja1, hoja2, hoja3, hoja4, hoja5, hoja6, hoja7, hoja8. Utilizaremos  clic en el icono situado en la esquina inferior derecha o con F11, para insertar dichas hojas.

 Nos posicionamos en  la etiqueta de cada una de las hojas, le hacemos click derecho y en la opción "cambiar nombre" pondremos los siguientes nombres en cada una de las hojas. Menú, DatosM(datos maestros), Asientos, Diario, Balanza, Balance y Resultados.

2.- Catálogo o Plan de cuentas.

Para crear el Plan de cuentas tomaremos como base el procedimiento llevado a cabo por la mayoría de los sistemas de contabilidad, es decir, asignando los siguientes números para cada uno de los rubros:

              1 ACTIVO            2 PASIVO            3 CAPITAL CONTABLE
             11 Circulante         21 Flotante             31 Capital
             12 Fijo                  22 Consolidado
             13 Diferido            23 Créditos diferidos

               4 INGRESOS       5 COSTO DE VENTAS           6 EGRESOS
             41 Ventas.              51 Costo de lo vendido.            61 Gastos de Operación.
             42 Ingresos Fin.                                                      62 Egresos Financieros
             43 Otros Ingresos                                                   63 Egresos diversos

A estos rubros le añadiremos las Cuentas y subcuentas que se requieran para registrar las operaciones de la Organización. En la hoja1 a la que le cambiaremos el nombre por DatosM(datos maestros), insertaremos nuestro Plan de cuentas a partir de la Celda A9 Como encabezados pondremos en A9, CUENTA y en B9,DESCRIPCIÓN . Este es un ejemplo de Plan de Cuentas extraído de un libro de texto.

La lista de Plan de cuentas fue editada para sacar la foto.

 Asignar nombre a Plan de cuentas.

Seleccionamos  las celdas en donde se encuentra nuestro plan de cuentas(A10:B77), vamos a menú de herramientas de Excel y damos clic en Fórmulas-->Asignar nombre a un rango: en el cuadro de nombre le llamaremos Plan, Ámbito=Libro, Hace referencia a =DatosM!$A$10:$B$200. -->Aceptar.

3.- Resumen de los asientos.

Nos posicionamos en la celdas A202, I202 y pondremos los siguientes encabezados con los tamaños de celda siguientes:
    A202                  B202                       C202              D202               E202
FECHA(12px) ASIENTO Nº(80px), CUENTA(83px), D/H(35px), DESCRIPCIÓN(272px),
    F202                G202                     I202
DEBE(102px), HABER(102px) y CONCEPTO(192px)

Como se podrá apreciar, nos hemos saltado la columna H, a esta columna(H) le cambiaremos el tamaño a únicamente 0.08px.  para evitar que la macro que insertaremos más adelante  nos elimine las fórmulas que insertaremos en I202.

En la celda A203 insertaremos la siguiente fórmula: =SI(B203<>"",Asientos!B4,"")  nos situamos en la formula en donde está B4 y damos clic en la tecla F4 para que quede así: =SI(B203<>"",Asientos!$B$4,"")
y la copiaremos hasta la celda B5000.

En la celda I203 la formula es:  =SI(B203<>"",Asientos!$A$6,"") aplicamos el mismo procedimiento de la formula anterior.

Como se podrá observar en las celdas G, I 201 inserté un botón con el texto MENÚ, para insertarlo vamos a Herramientas de Excel-->INSERTAR-->FORMAS -->RECTÁNGULO REDONDEADO y lo delineamos, clic derecho-->formato de forma-->Relleno sólido-->Color anaranjado énfasis 6; Color de Linea--> Sin linea; Formato 3D-->Bisel Superior-->Circulo, en Texto-->Menú-->Color de fuente Blanco.
Para el botón "Eliminar asientos" se debe proceder de la misma manera que con el control "Menú", sólo que recomiendo que se utilice otro color de relleno y de fuente para evitar al máximo los errores.

En el siguiente post continuaremos con la creación  de nuestro sistema contable.

Leer más