This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
Our Cash Flow account values are not displaying the exact result of the formulas that reference the Balance Sheet in the Actuals Version at the rollup level. The values on the parent levels under the rollup level are correct. The parent levels are tagged with different currencies than the rollup level, and they are being converted to the top level currency at the rollup level.
For example, we have the following formula on several parent levels under the rollup:
We think this is causing the discrepancy. Is there a way to let Cash Flow do the calculation based on our Balance Sheet in parent currency?
There are three options for resolving this scenario.
As of the 2018.2 release, you can utilize a Weighted Average Translation account with the Reset feature. More information can be found in the documentation HERE.
This formula takes the value of the Fixed Assets Account on the Balance Sheet from all child levels under the parent level from the previous period and subtracts the value of the Fixed Assets Account on the Balance Sheet from all child levels under the parent level for the current period. Both values in this formula will use the exchange rate for the current period when the values roll up to the top level. This is why the values are not displaying the exact value of difference between the periods for Fixed Asset Account on the Balance Sheet at the top rollup level. There are two different solutions that can be used to work around this issue.
If reporting on Cash Flow at the top level only:
The first solution would be to only apply the formula at the top Company (Only) Level. This would allow the formula to evaluate using the currency of the top level. This option would be used if you only want to report on the Cash Flow Accounts at the top level in the Organization Structure.
If reporting on Cash Flow at several parent levels as well as the top level:
To workaround the difference in exchange rates, another formula can be entered at the top Company (Only) level to consider the difference in exchange rates. In order to have the Fixed Assets from the previous period convert at the previous period's exchange rate and the Fixed Assets from the current period convert at the current period's exchange rate, the difference between the current period and previous period's exchange rate on the previous period's Fixed Assets would need to be added to the current period.
For example, lets say we have the following Level structure and exchange rates:
The Actuals values on the Balance Sheet are below.
The values on the Cash Flow sheet would then look similar to the values below.
The the difference between the current period and previous period's exchange rate on the previous period's Fixed Assets could be found using a formula similar to the one below:
((ASSUM.ExchangeRate.CAD.USD.E[time=this-1]-ASSUM.ExchangeRate.CAD.USD.E)*ACCT.Fixed_Assets[level=Division A, time=this-1])+((ASSUM.ExchangeRate.GBP.USD.E[time=this-1]-ASSUM.ExchangeRate.GBP.USD.E)*ACCT.Fixed_Assets[level=Division C, time=this-1])
When this formula is included on a level in the rollup, the total at the rollup level should match your expectations. The formula could be added to the Company Inc (Only) Level or another adjustment level.
Please see the "Cash Flow and Exchange Rates" worksheet of the attached file for an example of how the logic would be built. If you have any questions regarding this solution, please reach out to the support team.
You would need a weighted average exchange rate by month to calculate each month's exchange rate correctly. This would be a new exchange rate type in Adaptive Planning. If CTA feature is enabled in your model, the difference between the weighted average translation and the EOM rate would feed to CTA automatically.
Let's say you have the following values in the monthly periods of any account and the following AUD to NZD exchange rates.
Here's how the weighted average rate the weighted average would be calculated:
January = 1.01
February = (1000/1500)*1.01+(500/1500)*1.02
March = (1000/2300)*1.01+(500/2300)*1.02+(500/2300)*1.03
This could easily be done in Excel and pasted into the new exchange rate for each currency. Use the attached "Weighted Average Exchange Rate" template to get started.