Changing Formulas & Drivers – Quick Time-Saving Tips
Blog post by Jill King – Anaplan Pre-Sales
Many of the accounts that I spent my time working on in the past were forecasted based on some kind of driver such as a rate per Full Time Employee (FTE), a prior year run-rate or a current year run-rate. But changes often occurred that required me to update the driver for the forecasted months after having a few months of actuals in the current year.
CHANGING FORMULAS OR DRIVERS FOR A SINGLE ACCOUNT
For example, each department was allocated a real estate charge based on the rent paid at each location where the FTE’s were located. When the budget was built, it based the real estate charges on the prior year charges and did not accommodate for headcount changes that occur throughout the year. Once into the year, your original budget is irrelevant and the best indicator for the expense in the future months is the actuals from the current year.
The process to update the model with a run-rate based on the prior 3 months actuals would be to:
1) Go into each of the 20 models
2) Run an Essbase query to ensure the latest actual data was available
3) Update the formula in the first forecast month to calculate the per FTE cost of real estate in the actual months
4) Use that rate to forecast the remaining months of the year and then spread that formula forward to the remaining months of the year (making sure you create a formula that will spread appropriately to the future months).
Anaplan Solution
In Anaplan – The forecast version of your model would be all you would need to review, because actuals are fed and updated in Anaplan daily all an analyst needs to do is click on one of the cells for the account and update the formula. Because of Anaplan’s modular nature, each of the departments in my model that uses this account is updated – future months are updated and even future years can be updated if your model accommodates for that. With this it is a five minuet fix for 20 department update including all future updates now that your actuals are flowing in real-time.
Estimated Monthly time savings = 2-4 hours
Note: Quick Time-Saving Tips is new and will be updated every Monday on the Anaplan Blog . Interested in having Jill @jilleking review a tedious task or want to submit your own Time-Saving Tip – Tweet us at @anaplan and let us know!