AUGForums.com

An Acumatica User Group

  • Free
    • Start Here
    • Rolodex
    • Podcast
    • Blog
    • Forums
  • Paid
    • AugSQL
    • GI Course
    • Consulting
  • Login
  • Register

Forecasting Future Acumatica Project Revenue in Monthly Buckets (Wyatt.ERP Technique)

March 24, 2020 by Tim Rodman

How do you “spread” forecasted Acumatica revenue across multiple months for construction projects that are expected to take multiple months to complete?

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Have you ever tried forecasting the revenue from future Acumatica Projects in monthly buckets?

This can be challenging if your Projects last multiple months.

How do you “spread” the expected revenue across multiple future months?

This is a common need in construction where the Projects can take multiple months to complete.

The goal of this blog post is to outline a technique to forecast future expected revenue per month using CRM Opportunities, User-Defined fields, and a Generic Inquiry.

The Wyatt.ERP Technique

I have to say that I’ve always been a big fan of Generic Inquiries, but I’m an even BIGGER fan lately because of this technique outlined by Wyatt.ERP over in AUG Forums:
Generic Inquiry Magic from Wyatt.ERP (click here)

WANTED DEAD OR ALIVE: Tricky problems that you don’t think can be solved with Generic Inquiries.

I love AUG Forums because I’m constantly learning new things, but that post from Wyatt.ERP is currently my all-time favorite. It’s now been over 2 months since I read it and I’m still SUPER excited about it.

The technique that Wyatt.ERP talks about in the link above takes an Acumatica Attribute and uses it in a Generic Inquiry in a very creative way.

There are multiple applications of this technique, including these things (and I’m sure other things that I haven’t thought about yet):

1. Joining together separate datasets (eg. AR Invoices and AR Payments). In SQL you can do this with a UNION ALL statement, but Generic Inquiries can’t do this without the technique from Wyatt.ERP.

2. Printing multiple copies of a report, including the ability to dynamically determine the number of copies that will print when the report is run. This example would be done with Report Designer (not a Generic Inquiry), but the technique is the same.

3. Spreading amounts across multiple months. This example only occurred to me after I didn’t think it could be done in a Generic Inquiry, but a colleague pressed me to find a way. Thankfully I had recently read this post from Wyatt.ERP so it was fresh in my mind. That’s why I think there are more cool applications of this technique that I haven’t discovered yet.

We will focus on #3 in this blog post. Maybe I’ll do separate blog posts for #1 and #2 in the future.

Setting Things Up

We’ll use Acumatica CRM Opportunities to track future Construction Projects. This works well because you can do Project Quotes on Opportunities, then convert them into Projects for tracking a Revenue Budget, Cost Budget, Commitments, etc.

Before those Opportunities become Projects, we want to forecast expected revenue from those Projects.

Here are the assumptions for this example:

1. We’ll use the Estimation field on the Opportunities (CR304000) screen to track when the contract is expected to be signed.

2. We’ll use the Amount field on the Opportunities (CR304000) screen to track the expected revenue from this future project. The Amount can either populate automatically from line data or be populated manually by checking the Manual Amount checkbox.

3. We’ll add a User-Defined Field called Duration (Months) to the Opportunities (CR304000) screen to track how many months the project is expected to last.

4. We’ll assume that the Amount will get booked as revenue evenly in each month following the Estimation date, starting the month after the Estimation date. So, if the Estimation is April 11, 2020, the Amount is $20,000, and the Duration (Months) is 4, then we want to count estimated revenue of $5,000 in May 2020, $5,000 in June 2020, $5,000 in July 2020, and $5,000 in August 2020.

5. We’ll assume that we only want to forecast 12 months into the future.

Here are the relevant fields on the Opportunity:

Forecasting Future Acumatica Project Revenue in Monthly Buckets
Forecasting Future Acumatica Project Revenue in Monthly Buckets

And here is the definition of the User-Defined Field on the Attributes (CS205000) screen:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

But we also need to setup another Attribute. We won’t add this as a User-Defined Field, but we’re going to use it in our Generic Inquiry.

I added “SPRD” (for “spread”) at the end of “OPPDUR” so that OPPDUR and OPPDURSPRD will sort together in the list of Attributes.

You can’t see all of the rows in this screenshot, but it goes up to 12 since we want to forecast up to 12 months of revenue into the future.

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Building the Forecasting Generic Inquiry

Alright, now for the Generic Inquiry.

First we’ll add these two tables to the TABLES tab. Notice that I changed the Alias on the PX.Objects.CS.CSAttributeDetail table to “Spreader” because I think that’s a better description of what we’ll use it for.

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Now for the “magic” part, where this technique fries some circuits in your brain (at least it did for me).

We add this to the RELATIONS tab:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

What I really like about this is that it’s really elegant. If you think about it, it’s pretty simple. It’s the creativity that I like about it.

Now we add this to the RESULTS GRID tab:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Here are the formulas used in the previous screenshot:

Period =CInt(CStr(Year(DateAdd([CROpportunity.CloseDate],'m',[Spreader.SortOrder])))+Right('0'+CStr(Month(DateAdd([CROpportunity.CloseDate],'m',[Spreader.SortOrder]))),2))

Period As Date =CDate(CStr(Year(DateAdd([CROpportunity.CloseDate],'m',[Spreader.SortOrder])))+Right('0'+CStr(Month(DateAdd([CROpportunity.CloseDate],'m',[Spreader.SortOrder]))),2)+'01')

Amount =([CROpportunity.CuryAmount]/[CROpportunity.AttributeOPPDUR])

Now, let’s populate the Duration (Months) User-Defined Field for the following Opportunities with the following values:
OP000387 – 4
OP000388 – 3
OP000389 – 2

Before we go any further, I want to point out some weird User-Defined Field behavior that I encountered.

To see this weird behavior, let’s deactivate three of our columns and add an additional column called Temp to the bottom:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Now run the Generic Inquiry and you should see something like this. I’m not sure why all that extra junk is stored in the User-Defined Field:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

But here’s the weird part. If you make the Attribute ID called BURDEN (which is available in the SalesDemo database) available as a User-Defined Field…

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Note that you don’t even have to populate Project Burden Percentage with data, just make it available as a User-Defined Field as in the previous screenshot.

… you can then go refresh your Generic Inquiry (without making any changes to it) and it will look fine:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Now you can go back into the Generic Inquiry and re-activate the columns that we de-activated. You can also delete the Temp column.

The reason why this matters is that you will get errors if you don’t add the BURDEN Attribute as a User-Defined Field because the Generic Inquiry will be trying to turn that long ugly “<v type” text value into a number and you’ll get an error like this:

Error converting data type nvarchar to numeric.
Forecasting Future Acumatica Project Revenue in Monthly Buckets

There is probably a way to get around this weirdness with something other than the BURDEN Attribute, but I haven’t found it yet.

The Result

Finally, let’s take a look at our Generic Inquiry.

Forecasting Future Acumatica Project Revenue in Monthly Buckets

Isn’t this cool?

The original amounts on those Opportunities were:
OP000387 – 713,000.00
OP000388 – 420,000.00
OP000389 – 375,000.00

If you do the math, you’ll see that the Generic Inquiry took those amounts, spread them out across the expected duration (in months), and produced separate rows for each monthly period.

What’s nice about having data in this format is that we can now easily add a Pivot Table to the Generic Inquiry to get monthly totals like this:

Forecasting Future Acumatica Project Revenue in Monthly Buckets

I really like this technique. Nice and clean. And very useful.

Thanks Wyatt.ERP!

Filed Under: Acumatica Learning Tagged With: Acumatica, Acumatica Blog, Acumatica Learning, Acumatica Revenue Forecasting, Acumatica Training, Acumatica Wyatt.ERP Technique

By using this website, you agree to our Terms of Use (click here)

Online Members

 No online members at the moment

Recent Blog Posts

  • acuCONNECT 2023 – Acumatica SQL View Examples with Generic Inquiries September 14, 2023
  • EP 110: Why I canceled AUG Reporting Summit 2023 (Podcast) August 4, 2023
  • EP 109: Adrian Radikovich – Advanced ARM Financial Reporting made easy in Acumatica Cloud ERP (Podcast) May 2, 2023
  • EP 108: Ryan Brown & Dakota Clum – 3rd Party Acumatica Hosting (Podcast) April 26, 2023
  • Acumatica Summit 2023 – Workflow: Low-Code, No-Code Customizations (Las Vegas, NV) January 31, 2023
Building Generic Inquiries & Pivot Tables

Recent Forum Posts

  • June Henderson

    RE: Timecard reports/queries

    I am trying to create a report for missing timecards f...

    By June Henderson , 2 days ago

  • Tonya Sey

    GI to show Time Cards with Materials Only (no time entries)

    I'm looking to write a GI that shows all timecards wher...

    By Tonya Sey , 3 days ago

  • Teckla Kapuenene

    RE: Transaction were not created during the allocations

    @kburns Thank you Kelly.

    By Teckla Kapuenene , 4 days ago

  • Teckla Kapuenene

    RE: Demand Forecast Model of Moving Average - My Notes

    @Tim, I am currently working on this topic. For some re...

    By Teckla Kapuenene , 4 days ago

  • Pankaj Soni

    RE: Time Card GI Decimal Hours from Time Spent

    Hi @grant-westmill I'm trying to create GI to extrac...

    By Pankaj Soni , 7 days ago

  • Bettie Heyns

    RE: Ledger Currency ID - Display on ARM Report

    Cheaters route that I would suggest is to use the @orga...

    By Bettie Heyns , 1 week ago

  • busyfritz

    RE: Device Hub Error - trying to connect a printer

    Putting this here to prevent someone else from wasting ...

    By busyfritz , 2 weeks ago

  • Michael Pruet

    RE: Transaction-specific costing

    Did anybody come up with any good solutions to this?

    By Michael Pruet , 2 weeks ago

  • Henry Marambio

    RE: What SQL table store Audit History information for Acumatica?

    Kurt Yes thanks for the clarification -- the combined...

    By Henry Marambio , 2 weeks ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2023 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.