Retrieving Project Balances by GL Account

Purpose

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:

• Project Balances by GL Account
  

Example Screen:

Demo video link

In this video, Gabriel presents how to build a generic inquiry to retrieve project balances by GL account in Excel using Velixo, and then shows how to create the report that uses that inquiry.

He demonstrates how to group the balances by ledger, account, financial period, and project. In turn, he shows how you have the ability to filter and calculate the balances easily:

https://www.loom.com/share/865fd98c30e14953b064a7080a6882a6

Technical Details

• 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.

Report Implementation

• 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.
  

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.

Named lambda function

You can learn more about named lambda functions from Microsoft's documentation.

(In the video) Gabriel uses the Excel Labs add-in which includes a feature called "Advanced Formula Environment" which provides an interface for creating these types of functions.

=PROJECTTURNOVERAMOUNTBYACCOUNT(Connection, Project, AccountCode, StartPeriodInclusive)

Downloads

Download the Sample report file	Download the required generic inquiry
Download this report sample	Download the Generic Inquiry for this report. (need help importing the Generic Inquiry?)