GL by Project GI

Thank you Gabriel, this is fantastic! I have got the GI working and into excel. Seems like this will cover everything needed and will read up about the lambda.

Great support - saved me days!

Thanks for your patience Carly and Nathaniel! I just finished building a sample for you, which you can find here: Project Totals by Account - Velixo Sample Reports Repository. The folder includes a spreadsheet, as well as the Generic Inquiry that was used to support it.

A video presenting this can be found here: 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 tab includes two formula field; one that gives us the net amount (debit minus credit) of the transaction, the other one that gives us 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 "Test" sheet contains a sample comparing the amount returned by 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 one that leverage Excel lambda functions to created a "named" version of the same function:

SUMIFS() version

=SUM(SUMIFS(Data!$D:$D,

    Data!$A:$A,$B$1,

    Data!$B:$B,">="&RIGHT($D$1,4)&LEFT($D$1,2),

    Data!$B:$B,"<="&RIGHT($D$2,4)&LEFT($D$2,2),

    Data!C:C,EXPANDACCOUNTRANGE("Acumatica",Test!$B5)))

The reason the SUMIFS() function is nested inside SUM() 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 here. Note that I like to use the Excel Labs add-in, they have a feature called "Advanced Formula Environment" which makes building these functions very easy.

=PROJECTTURNOVERAMOUNTBYACCOUNT(Connection, Project, AccountCode, StartPeriodInclusive)

Hi Carly,

Happy New Year! Glad to see another user jumping in. Even before your post I already knew this would interest others. You are correct, there's no way to filter by project in the GL functions because the historical balances for the GL module in Acumatica are not grouped by project. We can achieve this by creating a generic inquiry, however.

Would you happen to have a sample template or mockup of how you would like this to look?

@Nathaniel FYI I have not received anything from you, did you try to e-mail me?

Hello Gabriel, sorry for jumping on this post but this is exactly what I am looking to do. 

I need to create a report that lets me look at the GL turnover for a given period range and project. Not sure where to start! 

We removed the project number from our sub account but there doesn't seem to be a way to filter on project when looking at the Velixo formulas.

Thanks, Carly

Nathaniel,

Happy to help you over e-mail, it will be easier if we need to share anything confidential with me. Just use my first name at velixo.com. I'll make sure to create a sample file we can share her afterwards for the benefit of our community.

Regards,

Gabriel

Hi Gabriel,

Thanks for the response, and that makes sense to me.  Our project teams all work in the project transactions world, its when I am seeing anomalies, or other margin issues is when I want project data tied to the GL.  I want to isolate where the anomalies are located to dig into a specific project.  And as you noted not all project transactions flow to the GL so only using Project Transactions does not tell me the whole story.  Do you have an email I could contact you at to take this off-forum?

thanks,

Nathaniel

(and to further expand on my answer below -- the reason Acumatica does not allow you to get project balances by GL account is because project transactions themselves don't directly reference GL accounts; from Project Transactions, you can create off-balance transactions that only reference an account group, without impacting the GL)

Hi Nathaniel,

As you probably noticed, all the project inquiries, and project functions in Velixo work at the account group level; you cannot calculate the total for a project by GL account. Ideally, our recommendation is to structure your project account groups as granular as needed for your reporting needs.

That being said, it's definitely possible to create a generic inquiry that gives you total by project/account, and then retrieve this using Velixo. I already have some inquiries that group by ledger, project, account and period, but you may need a different level of aggregation (by branch, by subaccount, etc.). Do you happen to have or would you be willing to build a simple spreadsheet that shows how you would like the data displayed? I'll help you with the inquiry and Excel functions.

Regards,

Gabriel