Defining the Forecast Method for Accounts

Every account uses a unique forecast method to project account values in future (forecast) time periods. Use Account Forecast to review or define the forecast method for accounts. You can use pre-built forecasting methods, or build your own freeform formula. Many of the accounts in the template have a pre-defined forecast method; you can revise the method if required for your business. (All the accounts that accept input do have a default forecast method defined.)

To define a forecast method for accounts.

  1. Open a model.

  2. In the Account View, click the cell for the account you want to review or change.

    Tip:

    To quickly find an account, from the Actions menu, click Find Account, and then enter the name or the subaccount identifier number.

  3. From the Actions menu, click Account Forecast. The Forecast page shows the calculation used to calculate the forecast value.

    You can also right-click a cell and click Account Forecast.

    In this example, Unit Volume uses a standard formula:


    Standard forecast method

    In this example, OFS_Product Revenue uses a freeform formula:


    Freeform forecast method
  4. Edit the standard formula options, or edit the freeform formula and options, and then click Apply. Click Apply to to apply the formula to selected subaccounts or children of the current account.

    For information about editing a standard forecast formula, see Creating a Standard Formula for the Account Forecast Method.

    For information about editing a freeform forecast formula, see Creating a Freeform Forecast Formula for the Account Forecast Method.

Creating a Standard Formula for the Account Forecast Method

To edit a standard formula:

  1. In the Account Forecast page, for Forecast Type, select Standard.
  2. Specify values for these options:

    Table 7-3 Options for Defining a Standard Formula for the Forecast Method

    Option Description
    Forecast As

    Select the account to forecast. Typically you use the name of the selected account, but in some cases you select a related account. For example, if you are forecasting cash, you can choose to forecast increase in cash, rather than cash.

    Method

    Select the forecast method to use. The method determines the format of your input data.

    • As Actual Value—Enter data as the actual value as defined by the default currency units. This is the default method, using the base currency.
    • Growth Rate—Enter an annual or a periodic growth rate. For example, for Sales growth of 10% per year, enter 10 for the forecast period input.
    • Growth Rate (Year over Year)—Enter data as a growth rate over the same period one year prior. For example, if January 2020 Sales are to be 5% higher than January 2019 Sales, enter 5 in January.
    • % of Another Account—Enter data for one account as a percentage of another account (Associated Account) in the same period. If you select this option, you must specify the Associated Account.
    • Days—Enter data for an account as the number of days (typically of sales or cost of goods sold) that this item represents. It is most commonly used for working capital balances, such as receivables and payables forecasting. If you select this option, you must specify the Associated Account.
    • Turns—Enter data for an account as the number of turns (how often the balance turns over) this item represents. This method is most commonly applied to inventory forecasting. If you select this option, you must specify the Associated Account.
    • Absolute Multiple of Another Account—Enter data for one account as an absolute multiple of another account (Associated Account) in the same period. This method is primarily used for price/quantity forecasting. For example, you might forecast unit volume (100 million units) in a Memo Account (v300) and calculate revenue as a unit price of $50 (absolute multiple) times unit volume in the Memo Account (v300). If you select this option, you must specify the Associated Account.
    • Default Multiple of Another Account—Enter data for one account as a default currency unit multiple of another account (Associated Account) in the same period. This method is also primarily used for price/quantity forecasting. For example, you might forecast unit volume (10 units) in a Memo Account (v300) and calculate revenue as a unit price of $20 million (default multiple) times unit volume in the Memo Account(v300). If you select this option, you must specify the Associated Account.
    Input

    Select the input method; available options depend on the forecast method you selected.

    Associated Account - Account

    Select the account to use for methods that require an associated account.

    Take Output Value From

    If the forecast method requires an associated account, select the value to use for the Associated Account's output—Current Period, Prior Period, Change In, or Average.

    Match Dimensions

    If the forecast method requires an associated account, select Match Dimensions to match the dimension of the associated account with the dimension of the account being forecast.

    For example, to forecast Cost of Goods Sold/Product XX/Region YY as a percent of Sales/Product XX/Region YY, choose Sales as the associated account and select Match Dimensions.

    Lag to Prior Input Period

    If the forecast method requires an associated account, and you are forecasting a balance account, you can forecast the ending balance or the change in the ending balance from the prior period.

    Use this option when you don't want to use the prior period's value. This option finds the previous period that has input values (rather than calculated values) and uses that value.

    For example, if you have year-to-date values through July, but actuals only for January, if you want to forecast July, choosing this option would use the value from January because it has an actual value.

    Associated Account Value

    Depending on the forecast method, select an appropriate value:

    • Unadjusted—Make no changes

    • Annualized—Take the value and annualize it
    • Normalized—Take an existing previous value and normalize for a time period of a different length

    If the forecasting method is Days or Turns, this option is not available.

    Forecast Input Period Values

    If the forecast method requires an associated account, select from:

    • Variable in each period—You can enter different values in each period
    • Constant for all periods—All forecast periods use the same value
    • Equal to the historical average—No input is required; the historical average is applied to all forecast periods
    Use Grid Pricing

    Indicates that the input for this field varies—input values adjust depending on the value of another account. For example, you can increment or decrement a contract interest rate based on selected criteria. Click Add and then specify the grid pricing options and pricing criteria. You define the criteria for when the values change and how they should change (usually additive or multiplicative).

    Spread Over Another Account

    Adds the input value to another account's output value to compute the final input value. Select the check box and then select the account over which to spread.

    Usually used for forecasting interest rates.

    Spread Over Account Select the account over which to spread.

Creating a Freeform Forecast Formula for the Account Forecast Method

To edit a freeform formula:

  1. In the Account Forecast page, for Forecast Type, select Free Form.
  2. To edit a freeform formula, type in the text box, or click Edit to open the Formula Builder.
  3. In the Formula Builder, select from accounts, functions, and operators to build the formula to calculate output values for selected accounts, and then click Save.

    See "Using Freeform Formulas" in Working with Strategic Modeling in Smart View for a description of functions and arguments. The formula is validated when you save it.

  4. Specify values for these options:

    Table 7-4 Options for Defining a Freeform Formula for the Forecast Method

    Option Description
    Forecast As

    Select the account to forecast. Typically you use the name of the selected account, but in some cases you select a related account.

    Method Description Enter a description of the formula.
    Input

    Optional: For formulas using @input, from Input select the format of the input data.

    • Currency—Enter input data using the option set in Units.

    • Items— Enter input data using option set in Units.

    • Percent—Enter input data as a percentage. Useful for tax rate formulas.

    • Ratio—Enter input data as a ratio.

    • Days—Enter input data as a number of days. The input must be multiplied by another account to produce the output value.

    • Turns— Enter the input data as the number of turns. The input must be multiplied by another account to produce the output value.

    Units

    For formulas using @input, select the input data unit (thousands, or millions, for example).

    Use in History

    Select to use freeform formulas in historical periods.

    For example, you might want to calculate Sales as Price x Quantity in both historical and forecast periods.

    If this option is not selected, the freeform formula is used only in forecast periods and historical data must be input separately.

    Lag to Prior Input Period

    For formulas using lag time periods.

    Allow Overrides

    Select to allow users to input values instead of using the formula.

    In input periods, you can override the selected input method to enable input of that period's value as Default Currency/Items. To override the input method, enter a pound sign (#) before or after the number.

  5. Click Apply.