I want to be able to take an average of actual expenses and figure out an estimate of expenses for the entire fiscal year. For example, if we have 5 months of expenses (July – November), I would want to take total expenses (July-November) divide it by 5 months and multiply that by 12 months to get estimated expenses (July- June).
Typically clients who wish to annualize their Actuals for forecasts do so by using assumptions and a custom account.
For this example, let's say we want to annualize our Actuals to forecast the Revenue account in our planning version.
- Create two Assumption Accounts:
- Flag – Populate 0’s for all months in the Actuals version and 1’s for all months in the current planning version.
- Number of Actuals Months – Set this to
=12-ASSUM.Flag[time=this.year]in the planning version.
- Create a Custom account for YTD Revenue Actuals with the following formulas:
- Actuals version
=ACCT.Revenue[time=this.year]←This will calculate the YTD value of the Revenue account in the Actuals version.
- Planning version
=ACCT.YTD_Revenue_Actuals[time=this-1]←This will pull in the value of the YTD Actuals for the Revenue account into the planning version.
- Actuals version
- Create a formula for the Annualized Revenue account in the planning version with the following formula:
=div (ACCT.YTD_Revenue_Actuals, ASSUM.NumberofActualsMonths)←This will divide the YTD Actuals for the Revenue account by the current number of Actuals months.
- Helpful Hint: The above formula will only calculate the annualized Actuals value of the Revenue account for the current year. In our example, this would be 2013. If you would like to carry the value into additional planning years, additional formula logic can be added
(ASSUM.NumberofActualsMonths>0,div(ACCT.YTD_Revenue_Actuals,ASSUM.NumberofActualsMonths),ACCT.Annualized_Revenue[time=this-1])This says to calculate according to our formula above as long as there are Actuals months in the current year. If there are not Actuals months, carry forward the value of the account in the last month of the year that has Actuals.
We can use Cell Explorer to verify that in August 2013, the formula is dividing the YTD Actuals value of the Revenue account by the 7 months of Actuals.
Helpful Hint: Utilize the Formulas tab to populate the formulas created in steps 2 and 3 for all levels where the account should be annualized at one time.