In the course materials we frequently use the colour scheme:
- Input data is coloured in blue font (and may also be in a cell with a lightly-shaded background).
- Borders are placed around key areas (e.g. inputs and outputs).
- Calculations use the default black text font.
- Calculations which represent final model outputs can be coloured with green-shaded cells.
The idea of using colouring and borders in consistent ways is to make the role of each item clear to oneself and to other users. This is important for several reasons:
- To aid the general understanding and communication of the model, by distinguishing which items are inputs and which are calculations.
- To be able to work more quickly with the model in the future (e.g. by highlighting which items are inputs that may need to be revised as new or updated data on actual volume sold becomes available).
- To reduce the chance of errors, such as may occur if one were given an actual figure of the Sales Revenue that has occurred (say $7500) and one may be tempted to type this into cell C5. This would of course remove the formula and any logical link to Price and volume would be lost.
The following image shows (in its top right) the menu icons on the Home tab that were used to format the input cells with the colour scheme above, which is implemented by selecting the cells and applying the colouring:
Borders around cells or ranges can be added in a similar way – by using the border (selected from the drop-down menu to the left of the font-colouring options), as shown below:
The final model formatted in this way would look like:
Numbers can be formatted to shows decimal places, or as percentages, or as currencies and so on. The formatting options are on the Home tab:
The main options can be seen using the drop-down menu.
An important formatting type is percentage. For example, a figure entered into a cell as 0.1 and subsequently formatted as a percentage would be shown as 10%. The following are some examples:
- The figure 0.1 entered into a cell that uses General Format will be shown as 0.1.
- The figure 0.1 entered into a cell that uses Percentage Format will be shown as 10%.
- If the figure 10 is entered into a cell, and the % symbol is also typed into the cell (i.e. “10%” is entered into the cell), then this will display as 10% (equivalent to entering 0.1 and formatting as a percentage),
- If the figure 10 is entered a cell, and this is subsequently formatted as a percentage, this will display as 1000%.
These cases are shown in the image below: