About Forecast Methods

You can create your own, or use the forecast methods provided to project account values in forecast periods.

Accessing Forecast Methods

To access Forecast Method:

1. Select an Account, and place your cursor on an account.

2. From the Account grouping label, click Account Forecast.

Entering Data for Predefined Forecast Methods

As An Actual Value

Enter data as the actual value as defined by the default currency units.

Growth Rate

You can enter an annual or a periodic growth rate. For example, for Sales growth of 10% per year, enter a 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 2003 Sales are to be 5% higher than January 2002 Sales, enter 5 in January 2003.

Percent of Another Account

Enter data for one account as a percent of another account (Associated Account) in the same period. For example, for Cost of Goods Sold as 46% of Sales, enter 46 for the forecast period input.

If you select this option, you must specify the Associated Account specified in the Forecast Method dialog.

Caution:

When calculating an account, you may get a message that says you can not use the Associated Account specified because it is calculated after the main account. For example, to enter Cash as a percentage of Total Assets, you must use the forecast method called Percent of Prior Period 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.

Note:

When forecasting using the Days method, do not select Increase in method on the Account Forecast dialog.

If you select this option, you must specify the Associated Account, which you select in the Associated Account section of the Account Forecast dialog. The Annualize Associated Account option is automatically turned on and the Input is... section is set to Annual. Strategic Modeling uses the correct time period handling in this calculation (That is, monthly A/R is calculated based on annualized sales, and so on.).

So, if you elect to forecast Accounts Receivable in Days of Sales, your Accounts Receivable balance is calculated as follows in each forecast period:

(Input for Days / No. of Days in Period) * Sales = Accts. Receivable Balance

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.

Note:

When forecasting using the Turns method, do not select the Increase in method in the Forecast section in the Forecast Method dialog.

If you select this option, you must specify the Associated Account, which you select in the Associated Account section of the Forecast Method dialog. The Input is... section is automatically set to Periodic. If you select this option, you must specify the Associated Account, which you select in the Associated Account section of the Forecast Method dialog.

If you choose to forecast Inventories using the Turns method and you select Cost of Goods Sold as the Associated Account calculation, your Inventories balance is calculated as follows in each forecast period:

Annualized Value of Cost of Goods Sold / Input for Turns

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, which you select in the Associated Account section of the Forecast Method dialog.

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, which you select in the Associated Account section of the Forecast Method dialog.

Selecting Predefined Forecast Methods

See Accessing Forecast Methods.

To select predefined forecast methods:

1. Access the Strategic Modeling ribbon in Microsoft Excel.

2. Select an Account by placing your cursor on an account.

3. From the Account grouping label, click Account Forecast.

4. On the Forecast Method, under Forecast type select Standard from the drop-down.

   Note:

   Ensure that you select Freeform type to apply the Freeform formula. See Using Freeform Formulas.

5. In Forecast Method, select a method to determine the format of your input data.

   See Entering Data for Predefined Forecast Methods.

6. Optional: Select Input if the account being forecast is Annual or Periodic. This input is useful when forecasting items such as interest rates.

7. Optional: Select an Associated Account to select the value to use for the Associated Account output.

8. In Take Associated Account’s output value from, select the value to use for the Associated Account.

9. 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.

10. Optional: Select Lag to Prior Input Period if you are forecasting a balance account. You can forecast the ending balance or the change in the ending balance from the prior period.

11. Optional: Select an Associate Account Value is option.

12. Optional: Select a Forecast input period values are option.

    Note:

    If you select Equal to the historical average, you do not need to enter a value.

13. Optional: Select Using Grid Pricing, and then click Edit Grid to indicate that the input for this field varies, enabling you to increment or decrement a contract interest rate based on selected criteria. See Using Grid Pricing.

14. Optional: Select Spread Over Another Account, and then select the Spread Account to add the input value to another account's output value to compute the final input value.

15. Click OK.

Entering Forecast Methods as Freeform Formulas

To enter a Freeform Formula:

1. Access the Strategic Modeling ribbon in Microsoft Excel.

2. Select an Account by placing your cursor on an account.

3. From the Account grouping label, click Account Forecast.

4. On the Forecast Method, under Forecast type select Freeform from the drop-down.

   See Accessing Forecast Methods.

5. In Formula, enter Freeform formulas to calculate the output values for the selected account. Using the account and function tabs, you can create Freeform Formulas using standard math operators.

   See Using Freeform Formulas.

6. In Method Description, enter formula descriptions for display.

7. Optional: In Input select the format of the input data. Input data used in the Freeform formula is entered in the Accounts view or in the Account Input dialog:

   • Currency

     Enter input data using the option set in the Currency.

   • Items

     Enter input data using option set in the 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. When this input type is chosen, 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.

8. Optional: Select the data Units specifications such as, Thousands or Millions.

9. Select Use in History to use Freeform formulas in historical periods.

   For example, you may select Use in History to calculate Sales as Price x Quantity in both history and forecast.

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

10. Select Allow Overrides to enable currency overrides in input fields.

    In input periods, the selected input method can be overridden 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.

11. Click Apply to… to display the Forecast Method Options dialog.

    Note:

    Displays the subaccounts and dimensional children.

12. Select the required accounts, which will contain the new freeform formula to be applied on it.

    Note:

    • If a subaccount is selected, and if you click Select Children, all children of the main account and the selected subaccounts are selected. The children of each subaccount is handled strictly based on whether that subaccount is selected.

    • For all subaccounts selected, you can act upon their children by performing Select Children and Deselect Children options.

    • Both Select All Subaccounts and Select Children buttons are mutually exclusive.

13. Click OK to apply changes to the selected accounts.

    Note:

    • After you click OK on the Forecast Method Options dialog the changes cannot be reverted.

    • Selecting Cancel on the Forecast Method dialog will only cancel changes to the account that was selected when the dialog was instantiated.

Using Grid Pricing

Grid Pricing enables you to model varying interest rates over time by incrementing or decrementing rates based on company performance against a metric.

To use grid pricing:

1. Select an account and select Account Forecast.

2. From Forecast Method, select Standard.

3. Select Use Grid Pricing.

4. Click Edit Grid.

5. In Base Grid Pricing on, select a criteria account.

   This account becomes the metric for measure.

6. In Comparison to use, select how to compare to the criteria account.

7. In Adjust by, select an adjustment type.

8. In Reprice, select the frequency of calculation. The system adjusts rates at the beginning periods.

9. In the Grid Pricing table, click to create rows. Then enter values:

   • Criterion in Millions of Dollars in this column, enter the value of the criteria in the same scale as the account.

   • Adjustment in Percent in this column is the effect on the rate, as a decimal. For example, if the rate increases a quarter of a point, enter .25.

   • To delete, select a row and click Delete.

   • To reorder, select a row and click the up and down arrows.

10. Click OK.