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

  1. Level
  2. Expenses Account dimension – this dimension will hold all GL accounts that uses annual budget
  3. Timespan – time stratum for the modeled sheet should be “Year”

Model Sheet Properties

Sample Annual Expense Budget Sheet

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.
Account Attributes

The Link account child should be tagged with Account Attribute “Link”

General Ledger Structure

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.

General Ledger Structure - Enter your Master Formula

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:

iff(this.version.isactuals, 0,1)

If you have questions or need assistance implementing Adaptive Insight please feel to contact us!

About Abaci
Abaci is a premier NetSuite & Adaptive Solution Provider with deep knowledge in both business process and technology architecture. Our team is passionate about NetSuite & Adaptive and has an exclusive commitment to providing the highest level of system expertise on its full suite of cloud-based business management applications.