Financial Modeling in Excel Applying conditional formatting to an error check
Applying conditional formatting to an error check
To make the error check even more prominent to the user, consider using condi- tional formatting to add a rule that makes the entire cell turn red if the error check has been triggered. On the Home Tab of the Ribbon, in the Styles group, click the Conditional Formatting button. Then hover the mouse over Highlight Cells Rule and select Equal To (see Figure 4-11).
When the Equal To dialog box appears, as shown in Figure 4-12, type the word error into the Format Cells that are EQUAL TO box and click OK. By default, it will turn the cell to Light Red Fill with Dark Red Text, but you can change this in the drop-down box.
FIGURE 4-11:
Applying conditional formatting to an error check.
FIGURE 4-12:
Editing the conditional formatting color.
Conditional formatting is popular in error checks because it makes the error check stand out more when it has been triggered. And, with any luck, the user will real- ize that there is an error in the financial model. Conditional formatting is not limited to error checks — it can be useful for other models where you want to draw the user’s attention to abnormalities or highlights in the calculations.
Format and Label for Clarity
Applying appropriate formatting and labeling to financial models is sometimes neglected by even experienced financial modelers. You need to include as many descriptions and instructions as possible within the model to make it absolutely obvious how to use the model and how the calculations work. Don’t assume that someone using the model will be able to understand what he’s supposed to do with the model, its purpose, and what should be changed when.
Here are some simple formatting rules to follow:
» Format input cells diff from calculation cells. (See “Create Dynamic Formulas Using Links,” earlier in this chapter.)
» Use the correct symbols for currency. If the currency is dollars, format 5000 as $5,000 for example, or if it’s in euros, format it as €5,000.
» Use commas for thousands. This makes your model easier to read and prevents mistakes and misinterpretations.
» Label your data clearly. It sounds simple, but mixing units (for example, mixing apples and oranges, or miles and kilometers) is a common source of error in fi modeling, and good formatting and labeling will avoid this.
» Include a dedicated units column. Make sure the units denoted by the amounts in that column are entered into that column’s heading (for example, “MWh,” “Liters,” or “Headcount”).
» If you round fi into thousands, show this clearly at the top of the row with a descriptive heading. For example, make the column heading “Revenue $’000” to avoid confusion and misinterpretation.
» Include units or currencies in column and row headings. Where possible, each column or row should contain only one type of unit or currency.
» Reserve a column for constants that apply to all years, months, or days. For example, if growth rate is 5 percent, have that in column D, and then link all calculations to column D.
