Skip to main content

How to automatically update project current margin estimate that equals total revenue budget minus total cost budget divided by total revenue budget?

@dmzasdf,

 

Our VP of Product Management put out a great article on how to set up Project Account and Revenue Recognition.

https://www.acumatica.com/blog/technical-tuesday-project-accounting-revenue-recognition/


Thank you for the article David.

More to the point, how does Acumatica 2020R1 (or a future release) support GAAP construction revenue recognition including:

1) Having revenue at the project level and costs at the task level so that a project-level profit margin can be utilized in allocation rule formulas

2) Auto-adjusting recognized revenue for a project based on a change to the project-level profit margin by adjusting the recognized revenue/costs in excess that has been allocated from expenses to match the current profit margin.

3) Automatically move dollars between Billings in Excess of Costs and Costs in Excess Billings. For example and vice versa, Billings in Excess of Costs should never have a debit balance. The dollar amount that made that account within a project go to a debit balance should be automatically moved to Costs in Excess Billings.

Thank you,

Dave Z


@dmzasdf,

 

Our Projects team has put out a webinar on Revenue Recognition on Projects.

 

It can be seen here. 

 


Hello

The answers posted above only address part of the question.  i.e. Revenue.

Based on my understanding and many weeks of working with PM, you can recognize Revenue or you can recognize Costs, but you cannot recognize both.  So it fails the GAAP requirement to recognize profitability.

Back a few years ago, it used to have a concept of WIP (costs), that it would allow you to post costs at time of invoicing.  There used to be a field on the billing rules for the WIP Accounting Group.  This got moved to the Tasks, but its no longer functional, plus it limited to just one account group, so it was barely functional.

It makes a lot of business sense as well as being GAAP compliant.  I guess it must be a bit complex, but hey, that what ERP systems are meant to handle.

I hope my understanding is incorrect, and someone would advise me how to recognize both revenue and costs simultaneously and then perform the appropriate posting at Invoicing.


We have found issues with expenses being greater than expected and then revenue being recognized at over 100%  Is there a way to stop revenue recognition to go over 100%


Thank you for the questions.

The billing rule still allows you to bill the WIP account group or any account group if using Time and Expense.  The Non-Billable WIP Account Group on the task is for Progress Billing purposes and the details can be found here-

https://help-2020r2.acumatica.com/(W(1))/Help?ScreenId=ShowWiki&pageid=97a58591-40d8-4edf-a9ad-896d633d6980

 

There is not a way to stop revenue recognition from going over 100% at this time.  This is a great idea and others have asked before, please add to the Ideas section for future consideration.

 


@dmzasdf - I’m on the same page with how you laid this out. Did you find the answer you need? If so, how are you approaching revenue recognition and reflecting over/under billings? 

I’ve not found the allocation rules & tools highlighted to be effective; perhaps I need to explore them more.

For context: We are a specialty subcontractor that (i) develops and submit progress billings based on observable progress and (ii) recognize revenue on a cost-to-cost basis per GAAP 606.

As you pointed out progress billings project to date = 75% and you’ve only expended 60% of current budgeted costs you are over-billed requiring an offset for the overbilling by Dr: Revenue and Cr. Overbilling Liability. 

Currently, I’m planning to handle over/under billings analysis in spreadsheet outside of Acumatica as part of monthly close and input a top-side JE to reflect the aggregate adjustment for all projects. 

Issues we’re currently seeing: 

  1. Cost Projection: I’d like to use the Cost Projection screen where we’d reflect an updated Completed Cost; leaving the “Revised Budget” to be the original budget + change orders. Effectively, not including budget updates into the  Revised Budgeted Amount
  2. Progress Billings: We currently derive billings independent of actual costs incurred to reflect progress; not looking to move to a formulaically derived progress billings 
  3. Actual Costs (not full picture): Currently we’re missing some incurred costs in “Actual Cost” resulting from materials received into Inventory that we've not been able to configure to hit the Project ‘Actual Cost’ … at least not until an IN Issue. While, yes, we’d have an AP bill that would show this cost in “Committed Invoice Amount” it will not have hit the “Actual Cost” field in the Cost Budget.

Thanks, John 

 

Thank you for the article David.

More to the point, how does Acumatica 2020R1 (or a future release) support GAAP construction revenue recognition including:

1) Having revenue at the project level and costs at the task level so that a project-level profit margin can be utilized in allocation rule formulas

2) Auto-adjusting recognized revenue for a project based on a change to the project-level profit margin by adjusting the recognized revenue/costs in excess that has been allocated from expenses to match the current profit margin.

3) Automatically move dollars between Billings in Excess of Costs and Costs in Excess Billings. For example and vice versa, Billings in Excess of Costs should never have a debit balance. The dollar amount that made that account within a project go to a debit balance should be automatically moved to Costs in Excess Billings.

Thank you,

Dave Z

 


Hi, All.

Not sure why I was getting noticed on responses and just noticed the mention in my ever growing inbox.

We have been given various attempts at solutions, but have not been able to fully validate.

We created a simple formula to avoid over allocation (e.g., if x>100, 100, x) and business events to trigger allocations without project transactions. The currently proposed solution has way too many parts for what should be supported out-of-box, but hopefully it will test out correctly this time.

Thank you,

Dave Z


@dmzasdf  I added  Min(100   before my formula and it does stop at 100%.  Reach out to me and we can look at your formula.


 All - we’ve updated our approach a bit after some internal discussion and assist from Heidi so I wanted to circle back on my post. If others are working through this topic, I’d be open to visiting. 

Update to my earlier points below by # … 

  1. Cost Projections: We are planning to use this independent projection of costs to drive Project over/under billings. 
  2. Progress Billings: No issue here. We’re content with manually derived billings that are not formula driven from any cost inputs.  
  3. Actual Costs: We’ve decided that we will keep the $ value in Inventory upon receipt, until subsequent Inventory Issue to the Project. In this way, it will delay the realization of job costs and subsequently the earner revenue on the Project given the cost-to-cost approach we’re taking to revenue recognition. But, we believe that will be appropriate and conservative. 

WIP Report tracking: We are going to customize the WIP Report to pull from the ‘Cost Projection’ not the resulting CO impacted Cost Budget. Minor report modification that will address the over/under billing analysis. 

In the first months post go-live we’ll plan to run the analysis in spreadsheet in parallel to Acumatica until we’re settled. 

 Thanks, John 

...

Issues we’re currently seeing: 

  1. Cost Projection: I’d like to use the Cost Projection screen where we’d reflect an updated Completed Cost; leaving the “Revised Budget” to be the original budget + change orders. Effectively, not including budget updates into the  Revised Budgeted Amount
  2. Progress Billings: We currently derive billings independent of actual costs incurred to reflect progress; not looking to move to a formulaically derived progress billings 
  3. Actual Costs (not full picture): Currently we’re missing some incurred costs in “Actual Cost” resulting from materials received into Inventory that we've not been able to configure to hit the Project ‘Actual Cost’ … at least not until an IN Issue. While, yes, we’d have an AP bill that would show this cost in “Committed Invoice Amount” it will not have hit the “Actual Cost” field in the Cost Budget.

Thanks, John 

 

 

 


Thanks for sharing!

Seems the definition of cost over cost is different in that we use actual cost divided by revised budget cost to compute (recognize) actual revenue.

The workarounds after developing allocation rule formulas was with business events to get change orders to trigger allocations and all allocation passes for each project to run automatically.


2) Auto-adjusting recognized revenue for a project based on a change to the project-level profit margin by adjusting the recognized revenue/costs in excess that has been allocated from expenses to match the current profit margin.

3) Automatically move dollars between Billings in Excess of Costs and Costs in Excess Billings. For example and vice versa, Billings in Excess of Costs should never have a debit balance. The dollar amount that made that account within a project go to a debit balance should be automatically moved to Costs in Excess Billings.

 

@dmzasdf - Did you you figure out solutions for these? Especially #2 - 
Allocations based on only ‘Not Allocated Transactions’ - I can’t see how I can write a formula to recognize the correct adjusted revenue for each period with a margin that is moving each period!


We stopped trying to solve after determining the business is not ready for this automation, so we are back to using Excel, but at least we are pulling the data from Acumatica with Velixo into Excel to compute the project transactions. 


Do you basically just pull everything into excel and then book a project transaction for all revenue?


Yes. The Excel workbook is a bear since we do budget line versus entire project revenue recognition which results in a massive project transaction file since we have so many budget lines on each project.


Reply