Retrieving Project Balances by GL Account

Please see Introduction to Report Templates for information about Samples, Demos, and Production Templates and the differences between them.  You many also want to refer to How to Modify Velixo Sample Reports.

This sample template demonstrates how to use Excel 365 and Velixo to retrieve Project Balances dimensioned by GL accounts

This workbook includes the following sample reports:

• The generic inquiry retrieves the total by ledger, account, project and financial period. The grouping allows us to minimize the amount of data to be retrieved when you run & refresh the report.
  
  
• If you need to be able to able to filter by subaccount, or project task, the inquiry will need to be modified accordingly.
  
  
• The inquiry results grid includes two formula fields: one that gives us the net amount (debit minus credit) of the transaction, another one that provides the financial period in YYYYMM format.  The reason the schema field is set to Batch.LineCntr is to ensure the field is formatted as an integer; this will allow Excel to properly filter by period range in the report.

• The raw data is loaded into the "Data" sheet of the workbook.  There are no filters, but you could decide to only load the data of a specific project, or for a specific date range.
  
  
• The "Project Balances by GL Account" sheet compares the amount returned by the Velixo project functions with what we calculate straight from the GL.  Note that revenue accounts show up as negative because we show the raw GL balance (which has a credit normal balance).
  
  
• Two different implementations of the GL calculation are provided: one using the =SUMIFS() function, another that leverages Excel's lambda functions to create a "named" version of the same function.

Note: The reason the SUMIFS() function is nested inside a SUM() function is because when we have multiple accounts in the range, SUMIFS() will return one row per GL account.