4.1 Modelling Exercise: Growth Forecast with Corkscrew
Purpose of Exercise
This exercise requires you to build a model that will be needed to answer questions in the next Assessment Test. To do so will require you to build some of the core important formulas discussed earlier, including for growth and ratio calculations, as well as a classical corkscrew structure.
This model must be built from scratch (there is not a partially-complete downloadable model available), in order to ensure that you have mastered all aspects required, and to consolidate your overall skills further.
Description of the Model that You Should Build
The structure and logic of the following model should be largely self-explanatory given the earlier reading materials, exercises and quizzes:
Some points that can be highlighted include that the model:
- Sales Revenue and Fixed Cost are each forecast using growth formulas. (Note that “fixed cost” means an item that does not vary with Sales Revenue. Nevertheless, such an item can change over time due to inflation or other factors).
- Variable Cost is derived using a ratio calculation.
- The Total Cost is the sum of the Fixed and Variable Cost, whilst Profit is the difference between Sales Revenue and Total Cost.
With reference to some specific cells and calculations:
- Cells C5 and C8 contain numerical assumptions for the annual growth rate in Sales Revenue and Fixed Cost respectively. The growth rate is assumed to be the same in each year, for each item.
- Cells D4 and D7 contain the prior year values for Sales Revenue and Fixed Cost, to which the above growth rates are to be applied.
- Variable Cost is calculated as a % of Sales Revenue, based in the assumption in cell C11, which is to be applied in each year.
- Total Cost is the sum of Fixed Cost and Variable Cost.
- Profit is Sales Revenue less Total Cost.
- The structure is used for (from the beginning of) time period “Year Prior 1”. The initial value of 100 (cell C21) is that which applies from the beginning of “Year Prior 1” (i.e. the profits of “Year Prior 1” are added to the corkscrew which then continues for three further years).
- The corkscrew is a classical structure, but has only one flow item (i.e. the increase, due to Profit). In more general models, there could also be a second flow item (such as distributions or dividends paid) which would be used to calculate the cumulated (retained) profit.
The formula used in each cell are as follows:
Using the Model in the Assessment Test
Some of the questions in the Assessment Test require you to change the value of some of the input assumptions in the model and to find the values of calculations and outputs as these changes are made. Please test your model before starting the quiz (for example, checking that dependent items all change appropriately as the input values are varied).