Some companies would set up an annual Budget target for certain accounts and want to automate any adjustment needed for each month when forecasting to keep the Target amount intact. Currently there isn’t a single model in Adaptive that is capable of achieving that. However we can combine models and formulas to achieve this goal. Below are steps on how to setup an Annual Budget goal models in Adaptive Insights.
Step #1: Annual Budget Modeled Sheet
First a sheet is needed to hold the Annual Budget amount for each level and account.
This sheet is just a modeled sheet with below columns
- Expenses Account dimension – this dimension will hold all GL accounts that uses annual budget
- Timespan – time stratum for the modeled sheet should be “Year”
Step #2: Account Attribute on General Ledger
Each of the Expense account on the General ledger will have two accounts, one to hold Actuals, and another to hold any Adjustment from Annual Budget. This is needed to avoid circular reference on formulas.
Create an Account Attribute for us to tag the accounts that are link, we will use this later when building formulas.
The Link account child should be tagged with Account Attribute “Link”
Step #3: Creating Custom account for Actuals and modeled numbers.
A set of custom accounts that can hold Actuals and calculations from different models for budget should be created.
The purpose of this is to show any Actuals and Committed costs for each account, so we can determine how much else is left compare to Annual Budget.
Step #4: Formula needed on General Ledger Expense Accounts.
Now that we have an Annual Budget for each account and a custom account that holds Actual and other committed costs, we can now create a formula on each General ledger expense account like this:
Div(Annual_Budget.Expense[Accounts=Office Supplies] – Acct.Actuals_OfficeSupplies, Acct.Forecast_Period[this.year])
On the formula you will notice that there an account used called “Acct.Forecast_Period” this is a custom account that counts the number of Forecast periods within the year. It has a Master formula as: