Best Practise for Loading Actual Data
Would anyone have a process defined for "How" to load Actual data into a model. I get that I need an Actual data file with tuple detail. And then I load the data into a Module, likely that has base members to load values. BUT my issue is then, how do I get that data into other Modules, when I cannot run a test against the Version in an IF statement? For example, I have a Gross Margin Statement, which has product revenue. In the FCST process we have products value by volume estimates...thus building the Revenue as we need for a forecast. Now I have Revenue data from accounts in my GL that get loaded into a table described above, in a seperate Module. I now need to marry the two parts together, so that ACTUAL values come from a book of record, versus a calculation. I prefer not to attempt to emmulate the Revenue from Actuals using the calculation, but see that we could enter in a value of 1 for price and the revenue for volume. But then if we have various sales currencies, this isn't a good option. How does an actual GL account get connected to another account (or line item) which is a calculated member in a forecast? I have tried several different scenarios, but don't see a simple direct path to the solution. What is the best advice from the design teams? Thanks. Petar
Tagged:
0
Best Answers
-
Hi Peter,
The actual version can get recognised in a conditional statement with ISACTUALVERSION calc eg.
IF ISACTUALVERSION() THEN book of record.Amount ELSE Units*Cost
Hope this resolves the issue?
Grantham0 -
Hi Peter, Hopefully this is clear enough, please shoot back questions.
versions = Actual, Forecast
[list=1]
Actual Module (Rev x Org x LC x Time) - only actual data loaded, therefore no version (not applicable) in module
Revenue Module (Calc Rev x Org x LC x Time x Version)- 'Forecast' version Data is added to a module which calculates the revenue, calculated data to be converted for currency
Gross Margin module (Rev results x Org x Currency list x Time x Version) - Gross Margin output combined view of module 1 & 2 in the 'forecast' version.
Currency Assumption module (Rate x Org x Currency list x Time x Version) - Exchange rates x time.
Option: Link Actual Module data to Actual version in gross Margin Module. Utilise switchover date (Settings> versions) for 'forecast' version, therefore actuals will overlay on actual months while forecast can be sourced using a simple formula below?
Note : If you do not want siwtchover date occuring in the Revenue Module, then you can turn it off in the module blueprint view x line item.
Also, the LC is in the Revenue Module and Actual Module? Therefore you can do currency conversion in the GM Module for all results, - do this through an assumptions module with Org x Currency list by time, have each Orgs LC as value 1. Then use this as a multiplier (the currency list will need to to be in the GM Module).
In GM Module Rev results formula is
IF ISACTUALVERSION() THEN Actual Module.Amount * Currency Assumption.Rate ELSE Revenue Module.Revenue * Currency Assumption.Rate
But if the actuals has already been converted (in source) then load all values into Actual Module (with currency list included in module), then link actuals one to one match with currency.
If actuals is already converted GM Module change to use
IF ISACTUALVERSION() THEN Actual Module.Amount ELSE Revenue Module.Revenue * Currency Assumption.Rate0 -
Hi Peter,
You seem to be all over the various features using subsets and switchover. It is correct a total is required for a LC subset related RPtCURR list and this would be irrelevant for reporting. You method of just adding the actuals to the formula works (as long as the calculated 'forecast' component in actual months is zero).
If the LC was a separate List and you used IF ITEM(RPtCURR) = RPtCURR.LC THEN get value from actuals ELSE do currency conversion based on actual , unsure if this helps the comment below?
But based on my current design, the LC values from Actual will not convert to the various report currencies. 0
Comments
-
Thanks for the recommendation. The issue at hand is that I have 2 data sources. Actual data loaded to a module is source 1, which includes values that are forecasted in units and prices. Forecast Data is added to a module which calculates the revenue and then allows the calculated data to be converted for currency.
Ex. Module with loaded Actual Data = Direct Revenue, Channel Revenue by LC by Organization for ACTUAL Scenario when this is loaded, I want to see this module data in my Gross Margin Module.
Next - Revenue Module = Units, Price, Revenue (Units X Price), by LC by Org, by Channel by Cost Center, and when this is Forecasted Scenario, I want to see these values in my Gross Margin Module.
My real issue is really process related - Can Anaplan source data from 2 separate modules based on version? So even though I am in a forecast Scenario, I need Actual data from one Actual data Module, rather than the calculation of data from the Revenue model. Currently, all I can figure is that I would need to load my ACTUAL revenue values not as a calculated single value, but rather the parts of an equation to get the values to flow properly.
Thoughts??
Thanks in advance.
Petar0 -
Grantham,
Thanks again. We are in alignment for design concepts and dimensionality. In fact, our setup is almost exact; difference is in a couple areas. One being LC; I tried to use just LC, out of a report currency list; but since it does not have a top level, it causes issues in the formula sections.
The second is the formula using ISACTUALVERSION is not sufficient for the view from a Forecast perspective. If a customer wants to look at a 6+6 view, 6 actual and 6 forecast, and we do use the switchover (since I think this is a stregth of the product) the formula result with the one stated is zero when selecting a forecast for the first 6 months.
What I have found that will work is to append the actual line item values from the Actual table, to the formula from the Forecast table. But this process only works in the dimensionality is consistent enough for the engine to aggregate across the module cube views. What I think I will need is to add either a parent level to RPtCURR (Which I was trying to avoid), or the whole dimension to the Actual module, not just a subset of "LC".
Currently, I have values for the LC for actuals months and forecast months. Plus I have the Forecast LC values converting on demand. But based on my current design, the LC values from Actual will not convert to the various report currencies. I need to re-check my design settings for the module version control etc, but hoping the calc will be resolved when I add a parent level to rptcurr.
Appreciate the help.
Petar0
