Por Chuck Vigeant
Este es el segundo de dos artículos en los que exploramos la capacidad de generar un informe de Ventas por Estado de Envío en Excel. En el primer artículo creamos un ejemplo sencillo usando sólo facturas, y le mostramos la mecánica de usar la función de subtotal en Excel 2007 para calcular, tanto el total de ventas como el impuesto sobre las ventas por factura.
En este artículo crearemos un informe completo que le dará lo siguiente: total de ventas, total de ventas sujetas a impuestos, total de ventas no sujetas a impuestos y total de ventas – todo por estado de envío. También se incluyen en este ejemplo los filtros para el cliente, el código fiscal del cliente, los artículos, las fechas, etc. (Para este ejemplo, no incluiremos el total de impuestos cargados o recaudados.)
Los pasos se organizan de la siguiente manera:
- Te da una completa declaración SQL que puedes copiar y pegar en Excel – sin tener que entender nada de tablas, relaciones, etc. Hago todo ese trabajo por ti.
- Crear una tabla pivotante para obtener su informe
- Demostrar las opciones de la tabla pivotante para mostrarle cómo lograr varias vistas diferentes utilizando la misma consulta y datos
¡Bienvenido al poder de las mesas giratorias!
Conectando Excel a QuickBooks
Desde dentro de Excel, invoca el Microsoft Query Wizard:
Elija la fuente de datos de QuickBooks:
Asegúrate de que el Asistente de Consultas esté apagado , ya que utilizaremos una consulta SQL manual que he generado para ti.
Verá una lista de tablas. Haz clic en el botón Cerrar para eliminar el cuadro de diálogo.
En el menú, haz clic en «Ver» y luego elige «SQL»
Verá la siguiente ventana:
Aquí es donde pegarás la declaración SQL que he creado para ti.
Sólo corta y pega todo esto en el cuadro de diálogo de la declaración SQL:
SELECCIONE «InvoiceLine». «CustomerRefFullName» COMO «CustomerRefFullName»,
«InvoiceLine». «CustomerSalesTaxCodeRefFullName» AS CustomerTaxable,
«InvoiceLine». «TxnDate» como fecha,
«InvoiceLine». «RefNumber» AS RefNumber,
«InvoiceLine». «ShipAddressState» AS ShippingState,
«InvoiceLine». «BillAddressState» COMO BillingState,
{fn IFNULL(«InvoiceLine». «ShipAddressState»,InvoiceLine». «BillAddressState»)} Estado AS,
«Artículo». «Nombre completo» como nombre del artículo,
«Artículo». «Tipo» como «Tipo de artículo»,
«InvoiceLine». «InvoiceLineSalesTaxCodeRefFullName» como ItemTaxable,
«InvoiceLine». «InvoiceLineAmount» como cantidad,
Tipo AS ‘Factura’
DE «InvoiceLine» «InvoiceLine»»,Item» «Item»
Dónde «InvoiceLine». «InvoiceLineItemRefListID»=»Item». «ListID» Y «InvoiceLine». «IsPending» = 0
Y («Artículo». «Tipo» =’ArtículoDescuento’ O «Artículo». «Tipo» =’ArtículoActivo fijo’ O «Artículo». «Tipo» =’ArtículoInventario’ O «Artículo». «Tipo» =’ArtículoInventarioAjuste’ O «Artículo». «Tipo» =’ArtículoNo Inventario’ O «Artículo». «Tipo» =’ArtículoOtroCargo’ O «Artículo». «Tipo» =’ArtículoServicio’)
AND «InvoiceLine». «TxnDate»>= {d’ 2011-12-01′ } Y «InvoiceLine». «TxnDate» <= {d' 2011-12-15′ }
UNION ALL
SELECCIONE «SalesReceiptLine». «CustomerRefFullName» COMO «CustomerRefFullName»,
«SalesReceiptLine». «CustomerSalesTaxCodeRefFullName» AS CustomerTaxable,
«SalesReceiptLine». «TxnDate» como fecha,
«SalesReceiptLine». «RefNumber» AS RefNumber,
«SalesReceiptLine». «ShipAddressState» AS ShippingState,
«SalesReceiptLine». «BillAddressState» AS BillingState,
{fn IFNULL(«SalesReceiptLine». «ShipAddressState»), SalesReceiptLine. «BillAddressState»)} Estado AS,
«Artículo». «Nombre completo» como nombre del artículo,
«Artículo». «Tipo» como «Tipo de artículo»,
«SalesReceiptLine». «SalesReceiptLineSalesTaxCodeRefFullName» As ItemTaxable,
«SalesReceiptLine». «SalesReceiptLineAmount» como cantidad,
Tipo AS ‘SalesReceipt’
DE «SalesReceiptLine» «SalesReceiptLine»»,Item» «Item»
Dónde «SalesReceiptLine». «SalesReceiptLineItemRefListID»=»Item». «ListID» Y «SalesReceiptLine». «IsPending» = 0
Y («Artículo». «Tipo» =’ArtículoDescuento’ O «Artículo». «Tipo» =’ArtículoActivo fijo’ O «Artículo». «Tipo» =’ArtículoInventario’ O «Artículo». «Tipo» =’ArtículoInventarioAjuste’ O «Artículo». «Tipo» =’ArtículoNo Inventario’ O «Artículo». «Tipo» =’ArtículoOtroCargo’ O «Artículo». «Tipo» =’ArtículoServicio’)
AND «SalesReceiptLine». «TxnDate»>= {d’ 2011-12-01′ } Y «SalesReceiptLine». «TxnDate» <= {d' 2011-12-15′ }
UNION ALL
SELECCIONE «CreditMemoLine». «CustomerRefFullName» COMO «CustomerRefFullName»,
«CreditMemoLine». «CustomerSalesTaxCodeRefFullName» AS CustomerTaxable,
«CreditMemoLine». «TxnDate» como fecha,
«CreditMemoLine». «RefNumber» AS RefNumber,
«CreditMemoLine». «ShipAddressState» AS ShippingState,
«CreditMemoLine». «BillAddressState» COMO BillingState,
{fn IFNULL(«CreditMemoLine». «ShipAddressState»,CreditMemoLine». «BillAddressState»)} Estado AS,
«Artículo». «Nombre completo» como nombre del artículo,
«Artículo». «Tipo» como «Tipo de artículo»,
«CreditMemoLine». «CreditMemoLineSalesTaxCodeRefFullName» como ItemTaxable,
(«CreditMemoLine». «CreditMemoLineAmount»)*-1 As Amount,
Tipo AS ‘Factura’
DE «CreditMemoLine» «CreditMemoLine»»,Item» «Item»
Dónde «CreditMemoLine». «CreditMemoLineItemRefListID»=»Item». «ListID» Y «CreditMemoLine». «IsPending» = 0
Y («Artículo». «Tipo» =’ArtículoDescuento’ O «Artículo». «Tipo» =’ArtículoActivo fijo’ O «Artículo». «Tipo» =’ArtículoInventario’ O «Artículo». «Tipo» =’ArtículoInventarioAjuste’ O «Artículo». «Tipo» =’ArtículoNo Inventario’ O «Artículo». «Tipo» =’ArtículoOtroCargo’ O «Artículo». «Tipo» =’ArtículoServicio’)
Y «CreditMemoLine». «TxnDate»>= {d’ 2011-12-01′ } Y «CreditmemoLine». «TxnDate» <= {d' 2011-12-15′ }
(Fíjese que he puesto en negrita los lugares en los que puede cambiar las fechas; y sí, debe cambiarlo en los tres lugares.)
Tu caja se verá algo así:
Antes de pulsar el botón OK voy a explicar – en términos generales lo que hace esta consulta:
- Combina las tablas de transacciones de los artículos de venta – Factura, Recepción de ventas, Abono, Cargo de extracto como si fueran una sola transacción (Esto no incluye los asientos.)
- Se incluyen sólo los artículos de venta (por ejemplo, se omiten los artículos de subtotal y los artículos y grupos de impuestos)
- Creó una condición, que si falta el estado de envío, usaremos el estado de facturación en su lugar
- Transacciones pendientes omitidas
- Permitió la posibilidad de cambiar las fechas para limitar el rango de datos con los que se quiere trabajar
Para aquellos que son usuarios avanzados de bases de datos o programadores, he optimizado esta consulta para que funcione con el controlador QODBC; podría haber usado una declaración mucho más corta usando Oracle o Sybase. Además, la parte importante de este ejercicio es dar a los practicantes una forma bastante simple de (a) tomar la información de QuickBooks, (b) manipularla usando tablas pivotantes, y (c) refrescar los datos sin tener que exportar, cortar y pegar desde QuickBooks.
Ejecutando la consulta
Bien, hagamos la consulta. Dependiendo del tamaño del archivo, podría tardar entre un par de minutos y una hora. Si el controlador está bien configurado, la primera vez que se ejecute el informe será, con diferencia, la más larga; cada uso posterior debería producir resultados bastante rápidos. (La configuración del controlador QODBC está fuera del alcance de este artículo, pero la configuración predeterminada debería funcionar bien en la mayoría de los casos).
Aparecerá el siguiente mensaje. Haz clic en el botón OK para continuar.
Una vez más, después de un período de tiempo, verá resultados similares a los siguientes:
En el menú, elija la opción «Archivo», y luego elija «Devolver datos a Microsoft Office Excel».
En este cuadro de diálogo, elija «Informe de la mesa giratoria», y una celda/hoja de trabajo para colocar sus datos. Luego haz clic en OK.
La consulta volverá a QuickBooks y recuperará los datos, y luego producirá una hoja de trabajo como la siguiente:
Ahora comienza la magia. Sólo tienes que arrastrar y soltar los campos que quieras en los recuadros de la esquina inferior derecha, y los datos aparecerán instantáneamente en el cuerpo de tu hoja de cálculo.
En el siguiente ejemplo, mostraremos las ventas por estado de envío a través de las columnas, y representaremos las ventas gravables frente a las no gravables en las filas.
NOTA: Recuerde que los artículos de descuento sí tienen un código de impuestos asociado.
A continuación, vamos a añadir un filtro. En este ejemplo filtraremos por los clientes que están exentos. Sólo tienes que arrastrar ‘CustomerTaxable’ en el cuadro de filtro de informe.
Noten que Excel ha colocado un área de filtro para nosotros en la parte superior de la hoja de trabajo. Ahora todo lo que tenemos que hacer es filtrar por «No» para obtener los clientes que fueron clasificados como no gravables.
Para obtener los siguientes resultados:
Además, podemos averiguar quién es el cliente no sujeto a impuestos. Añadamos el cliente a las filas.
Podemos ver que Diarmuld Roche fue clasificado como no imponible. Y si quisiéramos saber qué artículos estaban en blanco, podemos añadirlos a las filas de la siguiente manera:
Vemos que los cargos financieros no tenían una designación imponible (En QuickBooks, el impuesto sobre las ventas no se añade cuando se introducen las transacciones directamente en el registro de clientes o clientes).
Los datos están siempre en vivo
La última parte de este ejercicio consiste en observar que puedes guardar el informe y refrescar los datos en tu hoja de cálculo en cualquier momento . Incluso cuando la hoja de cálculo esté abierta – y hagas un cambio en QB – la operación de refresco actualizará los datos de tu hoja de cálculo en tiempo real .
Para refrescar tus datos puedes hacer dos cosas:
- Desde cualquier lugar dentro del área de datos, haga clic con el botón derecho del ratón y elija «Actualizar»
- En la pestaña «Datos», seleccione el icono «Actualizar todo» y elija «Actualizar». (‘Refrescar todo’ refrescaría múltiples consultas dentro de un mismo libro de trabajo).
Resumen
En este ejercicio, nos hemos esforzado por ofrecerles varias comidas para llevar:
- Las mesas giratorias son fáciles de usar, utilizando la tecnología de arrastrar y soltar
- Usando el controlador QODBC para QuickBooks, puedes recuperar datos en vivo y manipularlos de la manera que consideres adecuada.
- Las tablas pivotantes son dinámicas, flexibles y muy poderosas, con un mínimo de curva de aprendizaje.
Sobre el autor:
Chuck Vigeant M.Ed., fundador de ACCOUNTiGRATE, LP , es considerado por muchos como la mayor autoridad en integración de QuickBooks, reportes e Inteligencia de Negocios, y es el padre de los reportes personalizados de QuickBooks usando Crystal Reports y el controlador QODBC ahora incluido en QuickBooks Enterprise Solutions. Vigeant también es consultor de The Sleeter Group .
Artículos relacionados:
- Ventas/Impuestos por estado de embarque en Excel – El poder de las tablas pivotantes, Parte 1
- Oficina 2007 Los temas normalizan el aspecto y la sensación del documento