3.1 Modelling Exercise
Purpose of Exercise
This exercise requires you to build a model that will be needed to answer questions in the next quiz. To do so will require you to build some of the core important formulas discussed earlier (for growth-based and ratio-driven calculations), as well as reinforce the use of sensitivity analysis and DataTables.
Description of the Model that You Should Build
The context of this model is that there is prior year data for sales and profit margin, and that a 3-year forecast will be built that is partly informed by this data. The completed model should look like:
The formulas required can be seen here:
Note: We recommend that you build the whole model from scratch. However, you can instead choose to download the core structure, which contains the input values and some formatting, but not the formulas (i.e. so that you are required only to build the formulas). This downloadable file looks like:
… and can be downloaded using the link I.2.d.Exercise.ProfitRatio1
Adding a DataTable to the Completed Model
When you have completed the model, please produce a two-way DataTable, using the following row and column structure and input values:
Populate the table so that the item that is tracked (the output of the analysis) is the Profit Margin in Year 3 (cell G7), and the row input is cell E5, and the column input is cell E8.
Using the Model in the Quiz
Before starting the quiz, please note which of the DataTables is correct (Table 1 or Table 2).
The quiz will also require you to work with the model and to provide answers to the values obtained in some specific input scenarios.