Excel para presupuestar

Descripción general

Durante un seminario web de la Universidad de Excel, Jeff hizo una demostración de Power Query relacionada con el proceso presupuestario anual de una empresa. Inicialmente, no estaba familiarizado con Power Query pero decidí aprenderlo. Al final, pude incorporar Power Query en nuestro proceso presupuestario anual, eliminando la necesidad del software que estábamos usando.

Nuestro contralor y director financiero quedaron encantados cuando les hice una demostración de lo que había construido y cómo funcionaba. Descontinuamos el otro software, reduciendo nuestros costos, y ese año pudimos distribuir una plantilla de presupuesto simple y elegante con instrucciones para los usuarios de nuestro departamento. En esta publicación, recorreré el proceso de construcción de esto de principio a fin.

Viaje de presupuestación de Power Query

El principio

Al principio, no tenía ni idea de qué era Power Query ni de cómo podría ayudarme. Asistí a un seminario web de la Universidad de Excel donde aprendí una breve descripción general de Power Query de la mano de Jeff. Hizo una demostración relacionada con el proceso presupuestario anual de una empresa, que me correspondía completamente en mi puesto.

Describió la creación de un proceso para crear un informe y, por lo tanto, el proceso, una vez creado, no tendría que repetirse cada vez para actualizar el informe. Esto podría hacer que la parte de ingreso de datos en nuestro proceso presupuestario anual sea mucho más eficiente.

• Nota: grabación de archivo del seminario web disponible para registrarse aquí .

Obtener datos presupuestarios de los gerentes

En el ejemplo, Jeff enseñó cómo usar Power Query para acceder a una carpeta de plantillas de presupuesto (teóricamente enviadas a los usuarios del departamento para su entrada) y ejecutar una serie de pasos para extraer solo los datos necesarios, formatearlos y luego usarlos en una versión final. Informe limpio y entregable. Me sorprendió lo relevante que esto era para mí y decidí reproducir este ejemplo para nuestro proceso presupuestario anual.

Anteriormente, enviaba plantillas por correo electrónico a cada departamento y actualizaba manualmente un archivo maestro de presupuesto. Esto se volvió bastante complicado ya que había varias versiones diferentes de presupuestos flotando y no siempre sabía quién había visto qué versión, lo que dificultaba las reuniones presupuestarias. Cada vez que enviaba una versión actualizada, recibía otra actualización de otra persona inmediatamente después. Claramente la coordinación de la entrada de datos era preocupante.

Solución que no es Excel

Para eliminar este problema, mi gerente sugirió un software CPM que coordinaría nuestro proceso presupuestario y permitiría a los usuarios acceder y obtener sus propios informes. Ambos hicimos un gran esfuerzo para integrar este software con nuestro sistema ERP y luego utilizarlo en nuestro proceso presupuestario anual.

Al principio me pareció una gran solución. Sin embargo, descubrimos que este software acabó complicando aún más las cosas.

Después de aprenderlo nosotros mismos, tuvimos que enseñar a los usuarios de nuestro departamento cómo acceder y utilizarlo para completar los flujos de trabajo que se les asignaron para la presentación del presupuesto. Desafortunadamente, hubo una gran curva de aprendizaje y me di cuenta de que este software no valía el tiempo y el esfuerzo cuando los usuarios solo accedían a él una vez al año para enviar sus presupuestos.

Sin mencionar que la parte de ingreso de datos en el software era muy lenta (me tomó 30 segundos ingresar un número) y recibí quejas de cada uno de los usuarios.

Había estado pensando: “Hombre, extraño Excel… Tiene que haber una mejor manera de hacer esto en Excel”.

Durante el seminario web, Jeff instantáneamente me hizo pensar en cuántas maneras podía hacer esto en Excel. Jeff creó un informe utilizando una consulta para extraer datos de los archivos de plantilla. Luego, a medida que esos archivos de plantilla se actualizaran o cambiaran, todo lo que tendría que hacer es hacer clic en “Actualizar todo” y la consulta se ejecutaría, extrayendo los datos actualizados de las plantillas y actualizándolos. mi informe final. ¡Auge! Así. Fue resbaladizo. ¡Me encantó al instante! Así que me propuse hacer precisamente esto.

Solución Excel

Primero, creé las plantillas de presupuesto para que nuestros departamentos las completaran. Mostré el presupuesto de años anteriores y las cifras reales como referencia, pero bloqueé esas celdas para que no pudieran cambiarse (intencionalmente o no).

• Nota: esta publicación habla sobre bloquear celdas con protección de hoja de trabajo

En el extremo derecho había una columna resaltada para el presupuesto del próximo año que se les ordenó ingresar.

• Nota: esta publicación habla sobre un par de formas de formatear celdas bloqueadas o desbloqueadas

Luego, creé otra pestaña (solo para mi uso), que vinculaba solo los nombres de las cuentas y la columna de presupuesto “nueva” de la plantilla, ya que estos eran los datos que quería extraer. También bloqueé las celdas de esta pestaña para que no pudieran ser manipuladas.

Quería proteger con contraseña los libros para que nadie pudiera simplemente ingresar y cambiar los números, pero advertencia: esto no funciona con Power Query. En su lugar, almacené los archivos de plantilla en una ubicación específica de nuestro servidor y solo los usuarios del departamento correspondiente sabían dónde acceder a ellos, lo que sirvió como control suficiente para ello.

Luego, publiqué nuestro archivo maestro de presupuesto, la versión final, limpia y formateada que se editaría en PDF y finalmente se distribuiría a nuestra Junta y otras partes interesadas.

Agregué una pestaña al final para Power Query, que extrajo los datos relevantes de la carpeta de plantillas de presupuesto y los mostró en una tabla.

• Nota: esta publicación muestra cómo usar Power Query para extraer datos de varios libros y esta muestra cómo extraer valores de varios archivos CSV .

Luego, utilicé BUSCARV en el archivo maestro para hacer coincidir el nombre de la cuenta en esa tabla y encontrar el número de presupuesto correspondiente. ¡AUGE! Esto funcionó de maravilla.

• Nota: para obtener más información sobre BUSCARV, consulte estas publicaciones de BUSCARV

Entonces, cuando los usuarios del departamento ingresaban y actualizaban sus números de presupuesto, simplemente necesitaban guardar el archivo. Luego, periódicamente, ingresaba a mi archivo maestro, actualizaba todos los datos, lo que incluiría cualquier actualización en mi pestaña PQ y luego actualizaba los números del archivo maestro del presupuesto en consecuencia.

Utilicé varios amarres a lo largo del camino y probé el proceso repetidamente.

• Nota: para aprender cómo configurar una hoja de trabajo de verificación de errores, consulte este artículo .

Una complicación fue agregar o eliminar una partida presupuestaria. Si un departamento deseaba hacerlo, no podía (debido a las celdas cerradas). Por lo tanto, les pedí que me enviaran su solicitud por correo electrónico, en la que podía desbloquear las celdas y agregar o eliminar la fila en consecuencia. Luego necesitaba rastrear esto a través del proceso para asegurarme de que fluyera a través del archivo maestro de manera adecuada. Esto sirvió como control adicional para garantizar que estos cambios presupuestarios “estructurales” se completaran en todos los documentos.

Resultados de Excel (menor costo, mayor eficiencia)

Al final, pude incorporar Power Query en nuestro proceso presupuestario anual, eliminando la necesidad del software que estábamos usando. Nuestro contralor y director financiero quedaron encantados cuando les hice una demostración de lo que había construido y cómo funcionaba. Descontinuamos el otro software, reduciendo nuestros costos, y ese año pudimos distribuir una plantilla de presupuesto simple y elegante con instrucciones para los usuarios de nuestro departamento.

Recibimos excelentes comentarios y me sentí muy orgulloso del nuevo proceso que había creado, implementado y en el que había capacitado en toda nuestra organización. No puedo agradecer lo suficiente a Jeff y Excel University, ya que Power Query marcó una gran diferencia para nosotros.

Recursos adicionales

Así se hace Moss, esa es una historia inspiradora… ¡y felicidades por tu éxito!

Aquí hay algunos enlaces para obtener más información sobre los temas que Moss analiza en esta publicación:

• Seminario web
• Proteccion
• Publicaciones de Power Query
• BUSCARV publicaciones
• Presupuesto con Excel
• Error Ver artículo

Si usa Excel para hacer presupuestos, ¡me encantaría saberlo! Comparta qué funciones de Excel le ayudan con su proceso presupuestario publicando un comentario a continuación… ¡gracias!