Adding Forecast Formulas
In most financial models, its amazing to find that the number of different forecast methods is actually quite low (10-15 in many models). The problem with spreadsheets is that you don't have any visibility over which forecast formulas are being used where and there is no way to ensure that they are consistent.
FinanceSeer solves this problem by enabling users to manage a forecast library. This library contains all the forecast methods that are either being used or might be used in the future. Once a forecast member is created it can be assigned to a dimension member via the forecast method drop down located in the member settings.
To add or edit a forecast method follow these guidelines:
- Go to the Architect module in FinanceSeer and click on an editable template in the upper right corner of the screen (usually core template).
- Click on the Forecast Method page found in the Architect pane on the left side of the screen (towards the bottom).
- To add a new Forecast method click on the Insert icon.
- With your cursor on the new method, use the pane on the right to change the name of the forecast method.
- Click on the graphic to change the property form.
- Start by clicking on the Base Method. Use the drop down to set the forecast method type.
- Next, click on the Dependent Variable graphic to set the dependent. The dependent variable is typically although not always a non weighted average variable. In the case of a price * volume formula, the dependent should always be the Volume. In the case of a % of another account formula, the dependent should be the "other account". In the case of a growth rate, the dependent should be the prior period value.
- For growth rates, select Depends on self and then select prior period with an offset of -1.
- Member Default will allow the user to specify the dependent when the forecast method is applied to an account. For examples the forecast method may be % of another account. But when that method is applied to Cost of Sales the dependent needs to be "Product Revenue", however, if the method is applied to SG&A the dependent might need to be "Total Revenue".
- Select Explicit Cell if you want to define the dependent variable within the formula. For example a % of Sales formula will have a dependent variable of Sales. Users would not want to change the dependent for any reason.
- Click apply to accept changes.
The new forecast formula will now be available in any forecast method drop down window.