0. Estadistica Para Auditores Con Excel 4c3j38

CURSO EXCEL-ESTADÍSTICO LIC. GUILLERMO RIVERA.

[email protected]

Contenido 1.

Introducción ........................................................................................................................... 1

2.

Normativa Técnica. ................................................................................................................ 1

3.

Conceptualización de Población y Muestra. ............................................................................ 2

4.

Método Estadístico. ............................................................................................................... 2

5.

Técnicas de Selección Estadísticas. ......................................................................................... 5

"Lo mejor que puedes hacer por los demás no es enseñarles tus riquezas, sino hacerles ver la suya propia." – Goethe Gran Dramaturgo Alemán

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

1. Introducción En este curso abordaremos aplicaciones prácticas utilizando herramientas estadísticas desarrolladas a partir de bases de datos en Excel para el uso de auditores, desde la experiencia adquirida en el campo laboral. Entre los temas a abordar están: Normativa Técnica, Conceptualización Población y Muestra, métodos, técnicas y complementos con Excel versión 2010. Cada tema será abordado de forma práctica para una mejor comprensión y asimilación de los mismos. Por lo tanto, el participante deberá realizar paso a paso las diferentes prácticas para facilitar su aplicación.

2. Normativa Técnica. El marco de referencia al que hacemos alusión en este curso son Ley Reguladora del Ejercicio la Contaduría (LREC), Normas Internacionales de Auditorías (NIA´s) y las Normas Auditoría Gubernamental (NAG). 2.1

LREC Artículo 1.

El Artículo 1 literal C) párrafo segundo de la LREC establece: “Se reconoce que la auditoría independiente debe realizarse sobre bases selectivas de la evidencia que respalda las aseveraciones de la istración. En consecuencia no se orienta a dar fe plena sobre todos los actos mercantiles realizados por los comerciantes”. 2.2

NIA 530

La NIA 530 “Muestreo de Auditoría” establece: “El objetivo del auditor, al utilizar el muestreo de auditoría, es proporcionar una base razonable a partir de la cual alcanzar conclusiones sobre la población de la que se selecciona la muestra.” 2.3

NAG

El Reglamento de las Normas de Auditoría Art. 142 Alcance establece: “La norma de muestreo de Auditoría, trata sobre el uso del muestreo estadístico y no estadístico…”

1

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

3. Conceptualización Básica. 3.1

Población:

“Conjunto completo de datos del que se selecciona una muestra y sobre el que el auditor desea obtener conclusiones” 3.2

Muestreo de Auditoría (Muestreo):

“Aplicación de los procedimientos de auditoría a un porcentaje inferior al 100% de los elementos de una población considerada relevante para la auditoría, de forma que todas las unidades de muestreo tengan las mismas posibilidad de ser seleccionadas con el fin de proporcionar al auditor una base razonable a partir de la cual obtener conclusiones sobre todo el conjunto de la población.”

4. Método Estadístico. El método estadístico que vamos a desarrollar a través de la hoja electrónica de Excel es: Muestreo para poblaciones finitas calculado por medio de fórmulas y funciones desde Excel. Las unidades de muestreo pueden ser pueden ser elementos físicos (Bases de Datos de Cheques, CCF, Facturas, Clientes, Expedientes, etc). 4.1 Poblaciones Finitas. a) Para efectos de llevar a cabo el cálculo de la muestra para poblaciones finitas vamos hacer uso de la siguiente ecuación:

Donde:

n N e Z p q

= = = = = =

Muestra Tamaño de la población Error Deseado (Error máximo permitido) Nivel de confianza (90%, 95% y 99%) Cumple con la característica deseada No cumple con la característica deseada.

2

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

Cuando se desconoce “p”, se reemplaza el porcentaje de la característica deseada por p=0.5, ocasionando un aumento en el tamaño de la muestra y el tiempo de ejecución del trabajo. b) Corrección de población finita: Para efectos de llevar cabo el cálculo mínimo del tamaño de una muestra representativa de la población objetivo, utilizamos la siguiente ecuación: no 1+ (no/N)

n= Donde:

N = Es el Tamaño de la población no = Es el Tamaño de la muestra preliminar c)

Caso de Poblaciones Finitas con Excel, abrir 4.1.c Poblaciones Finitas. En la de auditoría solicitamos los libros de IVA por ventas, obteniendo una población 505 documentos emitidos en el período. Para el cálculo de la muestra se requiere tener un nivel de confianza del 95% (Z=1.96) en los aspectos formales y sustantivos de los CCF y no se desea una equivocación en la conclusión, en más de un 5% (error máximo permitido). Por lo tanto, con la ayuda de la hoja de Excel vamos a calcular la muestra y la corrección para poblaciones finitas: Procedimiento: 1. Abrimos el libro de Microsoft Excel 2. Nos posicionamos en la hoja 1 y le cambiamos el nombre a PF (Poblaciones Finitas) 3. Nos posicionamos en la Celda B2, combinamos con la celda C2 y digitamos lo siguiente: “Calculo del tamaño de una muestra”

3

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

4. Digitamos los siguientes textos en las celdas B4 a B8 así:

5. Agregamos los valores en la columna “C4 a C9” C4= 505 C5= 5% C6= 0.9 C7= 0.1 C8=1.96

En celda C9 vamos a digitar la siguiente fórmula para determinar la muestra preliminar: C9= (C4*C6*C7*(C8*C8))/(((C5*C5))*(C4-1)+(C6*C7*(C8*C8)))

Como resultado nos da 109 como número de tamaño de muestra preliminar…

4

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

6. Finalmente agregamos la fórmula de factor de corrección C10= C9/(1+(C9/C4)) Como resultado nos da 89 como número de muestra representativa

d) Caso práctico, abrir hoja de Excel 4.1.d Caso Práctico PF: Al 30 de junio de 2015 tenemos un total de compras con CCF de 1,610 documentos emitidos en el período. Para el cálculo de la muestra se requiere probar los niveles de confianza del 99%, 95% y 90% (Determinar los valores de Z), error máximo permitido 5% y proporción de éxito de P=0.5, P=0.6, P=0.7 y P=…

5. Técnicas de Selección Estadísticas. El auditor debe seleccionar los elementos que integran la muestra de tal forma que todos los elementos de la población tengan la posibilidad de ser seleccionados. Una de las exigencias del muestreo estadístico es que los elementos sean seleccionados aleatoriamente de modo que todos tengan la posibilidad de formar parte de la muestra. Para el caso de muestreo no estadístico, el auditor aplica el juicio profesional para seleccionar elementos de la muestra. En este curso vamos a desarrollar las técnicas de selección de números aleatorios, sistemática y la macro VB que es parte de los complementos de Excel 2010. 5

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

5.1 Técnica de Selección de Números Aleatorios En la técnica de selección aleatoria se utiliza la generación de números aleatorios a través del computador o mediante tablas. A continuación vamos a realizar una selección de números Aleatorios con funciones de Excel estadística: a)

Aleatorio y Aleatorio entre: Está función no tiene argumentos, solamente nos devuelve un valor igual o mayor que cero "0" y un valor menor o igual que uno "1". Lo que devuelve es logicamente con decimales. Si pulsamos la tecla F9 funciona como actualizar

Aleatorio

Función de Excel

Aleatorio .entre

En esta función si tenemos argumentos, aunque su aplicación es práctimanente igual; ya que, nos devuelve un valor mayor o igual que el que indiquemos o un menor o igual. Si pulsamos la tecla F9 funciona como actualizar

b) Caso práctico selección aleatoria, abrir hoja de Excel 5.1.2 Técnica de Selección Aleatorio.

6

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

5.2 Técnicas de Selección Sistemática Este método consiste en dividir el número de elementos de la población por el número de elementos de la muestra para obtener un intervalo de muestreo. Si éste es por ejemplo, de 50, y se fija como punto de partida el tercer elemento, la muestra estará compuesta por los elementos 3, 53, 103, 153 y así sucesivamente. A continuación vamos a realizar una selección de números Aleatorios con funciones de Excel estadística: a) Selección Sistemática con Excel 1. Abrimos el archivo 5.2.1 Sistemático 2. En la hoja TSS revisamos la BD de Facturas 3. Determinamos la población con la función Contara = contara (B2,B301) = 300 facturas

4. Abrir el archivo 4.1.d Caso Práctico, para determinar la muestra agregando los siguientes datos: N E NCZ P

= = = =

300 5% 95% = Z=1.96 0.95

5. Muestra representativa de ________ 6. Como requerimos revisar 49 facturas de una población de 300, se puede elegir cada X cantidad de facturas así: = redondear (B302/B303,0) =6 7. Como punto de partida, elegirán aleatoriamente uno de las primeras facturas, para el caso partiremos desde la primera factura; es decir, la factura 1 como la primera, 1,7 (1+6),13(7+6)…Vamos a ver la hoja de Excel. b) Caso práctico, abrir hoja de Excel 5.2.2 Técnica de Selección Sistemático. 5.3 Complemento de Excel- Muestra Excel tiene un complemento para hacer cálculos estadísticos, pero como no se trata de un tema básico, hay que activarlo en el menú programador/complementos/Activar las herramientas de Análisis; tal cual, se describe a continuación: 7

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

a) Activar el complemento de Excel 1.

Dar Clic en la cinta de opciones/Más comandos

2. Clic en Personalizar Cinta de Opciones/Activar Programador/Aceptar

8

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

3. Ya que activamos el comando de programador/ damos clic en la ficha-pestaña programador/Complementos/activamos herramientas de análisis/Aceptar.

4. Finalmente, vamos a buscar a la ficha o pestaña de Datos/Clic en análisis de datos. b) Determinar la selección de una muestra con el complemento de Análisis de Datos 1. Abrimos el libro 5.3 Complemento de Excel- Muestra 2. En la hoja 5.3. b CE 3. Observamos una población de 100 cheques

9

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

4. Con ayuda del complemento de Excel vamos a determinar una muestra aleatoria de 10 cheques. Por lo tanto, nos posicionamos en la ficha o pestaña de Datos/buscamos la herramienta de análisis de datos y seleccionamos la función de análisis de “Muestra”/Aceptar.

5. Nos aparecerá una ventana con tres opciones:  Rango de entrada: En donde seleccionaremos el rango de la población; para nuestro caso, es el rango de los números de cheque (C3:C102)

 Método de Muestreo: El complemento de Excel nos da dos opciones para seleccionar muestras; la primera, es periódica o sistemática y la segunda, es aleatoria. Para nuestro ejemplo, vamos a seleccionar la opción aleatoria y vamos digitar la cantidad de 10 cheques que se han determinado como muestra.

10

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

 Opciones de Salida: Finalmente, el complemento de Excel nos pregunta dónde queremos ubicar los datos; la primera, rango de salida es de seleccionar la celda donde se ubicará el resultado; la segunda y tercera opción solamente basta con seleccionar la opción para que el resultado se ubica en una nueva hoja o un nuevo libro.

 Finalmente, tenemos el resultado de la técnica de selección aleatoria a través del complemento de Excel- herramientas de análisis.

11

EXCEL- ESTADÍSTICO PARA AUDITORES. LIC. GUILERMO RIVERA [email protected]

c) Caso práctico, abrir hoja de Excel 5.3.c Selección de muestras a través del complemento de Excel-herramientas de análisis.

Gracias!!!

12