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:
And here is the definition of the User-Defined Field on the Attributes (CS205000) screen:
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.
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.
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:
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:
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:
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:
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…
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:
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.
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.
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:
I really like this technique. Nice and clean. And very useful.
Thanks Wyatt.ERP!