Inputs, Cases, and Scenarios. Seldom is the future clear. Because of this, most financial models do not have only one set of inputs. Many models rely on scenarios or cases with particular themes to capture different outcomes. Creating an input slate that is easy to change between discrete scenarios is straight forward. Please download the free cases excel spreadsheet example: Inputs, Cases, Scenarios. Cases can be built in many formats. Things to consider are ease of use and the source of inputs. If you typically think about revenue in terms of growth rates, then use growth rates off of historic data. If you project revenue with dollars or projects, or unit volume, then use these to drive your model. In any case, you will want a simple organizational structure to lay out the cases and be able to rapidly switch between them. It is also helpful to map output to the input sheet so that you can see the results of your inputs as you adjust them.
The case input sheet is arranged so that all of the different cases for each input are in a particular row. The handy “CHOOSE” formula is used to pick the appropriate input from each case. The construction of this formula is such that it can be copied around the output section of the sheet. The “live” data is presented on the left most portion of the sheet. This is what will feed the model. The inputs to the model are in yellow to the right.
The inputs are all similar. Please find them to the right of column L. Note that there are formulas imbedded in the inputs. This is OK, as these are still inputs to the model. If someone types over them, they will not destroy the function of the model, only how you got to that input. However, they should, in general, only refer to other cases. Avoid writing deep formulas here. For example, if you are using units and prices to drive revenue, you should have separate inputs for both, not a hard-coded formula for each. If things become crowded, place the inputs here and the calculations off sheet. In the first input case (starting column P), there are two shades of yellow. In most purchase situations, you will be presented with a seller forecast. It is always a good idea to first “code up” what you were given prior to launching into different scenarios. The bright yellow is an additional forecast that the buyer added to match their forecast horizon.
The cases are visually separated for ease of review. This sheet’s format allows you to quickly add a large number of cases by copying on old case over, modifying it, and modifying the “CHOOSE” formula.
In the output section, columns F through K, the “live” case shows up and is used by the model to drive results (there is a skeleton income statement on the other populated tab as an example of carrying over inputs). There are also additional inputs in this section. This may seem inconsistent, but there are some operations that require this to happen. First, in B26 there is a checkbox (add the developer tool bar (Forms in older Excel) to the ribbon and insert a “form”. Right clicking to Format Control allows you to link a cell to the box). This checkbox allows to the user to turn synergies on and off independent of the case selected. There are many instances, such as this, where layered inputs add insight to analysis. The synergy margin is a formula input on this sheet. This calls the user’s attention to the fact that the revenue synergy must have a cost (both COGS and SG&A) and that this cost is calculated live as the average margin of the base business. This margin cannot be calculated directly using % revenue growth inputs, so it must be calculated in the output section using the model (as an aside, if you add the historic revenue and future revenue “output” to your assumptions as additional rows in each case, you will be able to calculate synergy margin in each case rather than in the output).
Columns C, D, and E are added to the output so that the formulas in the model can be consistent across the row. They are repeated for ease of viewing in the input section. The synergy output is green to remind the user that the formula is different (referring to the checkbox), so the user will not accidentally paste over it. The history was left grey, because while it is an “input”, it is fixed (should never be changed) and is not a driver to the model outputs.
A complete model companion to this sheet would obviously use all of the inputs to drive the balance sheet and cash flow statement. In addition, capital assumptions (debt, equity, etc.) would typically be on this sheet.