AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Forecasting Future Acumatica Project Revenue in Monthly Buckets

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

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • RE: Using « start with » unstead of « contrains »

    It's a contains search, not a "starts with" search unfo...

    By Tim Rodman, 1 hour ago

  • RE: Problem when i have more then one Alternate ID

    This article I wrote explains how to work with barcodes...

    By Carl Brooks, 8 hours ago

  • RE: Problem when i have more then one Alternate ID

    The xref is actually its own report table. You'll need ...

    By lesliealt0502, 9 hours ago

  • RE: Construction Module/Purchase Orders - Commitments

    Do you see them in the Commitment tab of the project? I...

    By johnymt, 2 days ago

  • RE: Compressing Images

    That would be cool. Not that I know of personally. Are ...

    By Tim Rodman, 2 days ago

  • RE: Support for custom fonts

    @ckwiat That's what I've experienced with MICR which is...

    By Tim Rodman, 3 days ago

  • RE: Support for custom fonts

    I actually came across this that I will try:

    By Cory, 3 days ago

  • RE: Support for custom fonts

    @timrodman do you know if anything was definitely figur...

    By Cory, 3 days ago

  • RE: Adding bill description on check stub

    @cfritsch Chip great modifications. Just a quick questi...

    By Jswartz, 3 days ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

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