CHAPTER 7
Tools for Model Display
Leaving aside the formulas and calculations of a model, in this chapter we will focus on the look and feel of a model. There are many Excel tools that can be used to make the model more attractive and friendly to the user. We'll look at the basic formatting, then more complex custom formatting, conditional formatting, and then form controls, which can be built to help the user interact with the model more easily.
BASIC FORMATTING
Believe it or not, as simple as it may sound, good formatting is an important part of building a financial model. While it's critical of course to get the numbers right, making your model look good is the next logical step. A model that is well formatted will be more readable, user-friendly, and easy to navigate. Additionally, you, the modeller, will find it easier to work with the model if it is clearly formatted.
In the latest version of Excel, all the formatting options for colour, font, and alignment are clearly displayed on the Home tab. A shortcut exists on the Home tab where you can very quickly change the number format on any cell from the Number ribbon, as shown in Figure 7.1.
FIGURE 7.1 Formatting in the Ribbon
For detailed instructions on the more basic formatting tools, including changing number formats, percentage and currency formatting, number alignment, cell attributes, and styles, please see the supplementary material at www.plumsolutions.com.au/book, available for download in PDF format.
CUSTOM FORMATTING
We can go one step further with our formatting to customise the way data is displayed in cells without changing the underlying data. For instance, the function NOW() gets the current date and time from your computer and, by default, displays it in the format “dd/mm/yyyy hh:mm” (or according to the date conventions in your computer's Region and Language Options within Control Panel).
We can change the look of this date without disturbing its intrinsic value by formatting it differently. Let's have a look at how we'd do this:
- Open a blank spreadsheet and type into cell A1 Report as at, and into cell B1 type the function =NOW() or =TODAY(), as shown in Figure 7.2.
FIGURE 7.2 Showing the Current Date and Time Using the =NOW() Function
- Size the column widths to match the data, then select cell B1 for formatting.
- Choose the formatting drop-down in the Home tab on the ribbon. Select More Number Formats at the bottom of the drop-down list that appears.
- In Excel for Mac, from the Format menu, select Cells.
- The dialog box in Figure 7.3 appears, showing how Excel has already customised the cell.
FIGURE 7.3 Format Cells Dialog Box
- As can be seen from the dialog box, there are many preset formatting options for numbers, from General to Accounting to Custom. As suggested within the dialog box, we can type our own codes over preset ones if the format we want isn't available.
- To create a customised format, select Custom from the Category list. Overwrite Excel's choice of “dd/mm/yyyy hh:mm” with just the three characters “mmm” and observe what Excel does with your date. Add a further “m” to make our custom formatting “mmmm” and the result will be, after reflection, obvious.
- Custom format your current date to the following format: 24 Feb 2017 (your date will be different in content).
- The ribbon has an additional shortcut to preset formats in the Number section of the Home tab, as seen in Figure 7.4.
FIGURE 7.4 Changing the Date Format
Custom Currency Symbols
As mentioned earlier, changing the look of a cell doesn't affect the underlying data. So far we've played with dates (which are numbers to Excel, even though they're generally displayed as text), but there are plenty of other preset number formats that we can, if necessary, customise in four steps.
- Go to a blank sheet, and type 582504 into a cell. If you have not defined any formatting on that cell yet, Excel will assign a General format to it.
- Change the number to currency, preceded by your country's monetary symbol, by using the Currency category in the Format Cells dialog box.
- Change the currency symbol to that of the Japanese Yen, or some other commonly used currency.
- Change the currency symbol to that of a currency we've made up—Elbonian Spree (EL).
As you might have suspected, the Elbonian Spree isn't available in the list of symbols. We'll have to build a custom format for it.
Select the Custom category and overwrite Excel's ¥ symbol with EL, as follows: [$¥-804]#,##0 is changed to "EL"#,##0.
Note that EL, as invented by us, is probably unknown to your version of Excel and therefore must be presented as a string (i.e., in quotes). This custom currency symbol will be saved with the workbook and will be available for future use.
Understanding Excel's Number Formats Number formats have four elements:
- Positive number
- Negative number
- Zero
- Text
Each is separated by a semicolon. None of them is mandatory, but there should be a positive element at least.
Consider the elements in the format $*#,##0; [Red]$*-#,##0; $*"4";"*".
| Positive | Negative | Zero | Text | |
| Format | $*#,##0 | [Red]$*#,##0 | $*"4" | "*" |
| Value | 1000.00 | 100.00 | 0.00 | Jack |
| Representation | $1,000 | $100 | $ | * |
Leading zeros of both positive and negative numbers will be suppressed wherever # is specified. Negatives will show the minus symbol after $ and zeros will display. The text Jack will be represented as a single asterisk. The monetary symbol asterisks ($*) indicate that the symbol must be fixed to the left and not follow the number as it shrinks or expands.
You'll note that Excel sometimes uses codes, particularly in currency formats. These are used, for instance, in differentiating between Australian and US dollars—and we know that specifying currency units is critical for financial modelling best practice.
The important thing to remember is that the underlying number doesn't change, no matter how we format it. It retains its decimal places even if we can't see them. Arithmetic can continue to be done on a cell's contents, even if it's invisible.
Useful Formatting Options If you intentionally leave an element blank, it will be rendered invisible. $#,##0.00;; will show positives only, all the other elements being deliberately suppressed. The entry ;; will make everything in the cell invisible (so will changing the font colour to match the background).
To display a number as 000s, while still retaining its full value, custom format it as $#,##0, (the format followed by a comma). Millions are represented by $#,##0,,"m". $458,243,555.42 will display as $458m, which, if multiplied by one, will return $458,243,555.42. If this doesn't work, your digit grouping symbols within Region and Language Options in the Control Panel are not set as commas.
To physically change a number to rounded thousands, use the ROUND, ROUNDUP, or ROUNDDOWN functions, employing minus digits. ROUND(458243,-3) will change the number to $458,000. The format $#,##0, will display $458, which could easily lead to misinterpretation of the numbers, so I recommend the use of the format $#,##0"k" instead.
To make currency negatives by default show in brackets (parentheses), change your negative Currency Format options within Region and Language Options in the Control Panel to default to parentheses.
Custom Formatting in Reporting
Let's say you have a report similar to the one shown in Figure 7.5. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.
FIGURE 7.5 Sample Report
We are going to format the numbers in the following two steps so that they look like Figure 7.6 (without changing the underlying data):
FIGURE 7.6 Custom Formatting Using the Format Cells Dialog Box
- Highlight the data and, as described above, go to the Custom Formatting dialog box.
- Add a comma to the end of the format type, as shown in Figure 7.6.
This will change the look of the numbers without changing the value of the numbers at all. We can see in the formula bar in Figure 7.6 that the underlying value of cell B4 is 582504, but because of the custom formatting we have used, the cell shows the value 583.
Next, add up the visible values of the cells B4:B8 manually like this: =583+236+151+0+642. You'll see that the total should be $1,612, not $1,611! Use the ROUND function, as shown in Figure 7.7, to round the numbers to the nearest thousand, such that your report or PowerPoint slide is correct. Change the formatting back to show the additional zeros if you wish. Your formula should be =ROUND(B4,-3). Normally, the ROUND function is often used to remove decimal places, but in this case, it has been used to remove precision in reporting.
FIGURE 7.7 Using the ROUND Function to Truncate Values
CONDITIONAL FORMATTING
Conditional Formatting is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change, depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used.
This is handy for variance analysis on a profit and loss (P&L), for example. If you want to highlight any instance where a variance is 10 percent higher than budget or more, the cell will turn red.
Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations—use caution when applying to large ranges.
To Apply Conditional Formatting
- Select the desired cell or cells in the spreadsheet. Let's say, for example, you wanted to highlight with a red font any cell value that is between 1 and 10.
- On the Home tab, in the Styles group, select Conditional Formatting. Select Highlight Cells Rule and then Between. Type in 1 and 10, and define the desired font colour. See Figure 7.8.
FIGURE 7.8 Applying Conditional Formatting
Add a new rule for each new condition; you can have as many as you like (unlike in the old Excel 2003, where you were limited to three rules).
To Remove Conditional Formatting
On the Home tab, in the Styles group, select Conditional Formatting. Select Manage Rules, and you can delete your chosen rules one by one. Alternatively, you can clear rules from selected cells or from the entire sheet by clicking on Clear Rules.
Data Bars
Data bars are dynamic bar charts that you can apply to any numerical data in Excel. They graphically show the relative size of each value, as shown in Figure 7.9. You can create your own examples or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.
Go to a table of numeric data, and highlight the numbers in the following three steps:
FIGURE 7.9 Applying Data Bars
- On the Home tab, in the Styles group, select Conditional Formatting, and then Data Bars.
- Choose your desired bar.
- Practice changing the underlying numbers, and see the bars change.
Icon Sets and Colour Scales
Icon Sets allow you to conditionally display a small icon that represents changes in data. For example, if revenues are down this month compared to last, you may choose to represent this with a red arrow pointing down, or if your cash balance is above a certain threshold, you could use a green checkmark to give the user a quick way to see that everything is okay.
Your options for icons are shown in Figure 7.10, and these are extremely useful in dashboards and other reports.
FIGURE 7.10 Accessing Icon Sets
Another handy feature of conditional formatting is colour scales. Looking at Figure 7.11, how would you be able to quickly assess what is good, bad, and in between?
FIGURE 7.11 Sample Report without Formatting
By using colour scales, you can assign rules for data in the table and add an extra layer of meaning to the numbers with colour. You can see in Figure 7.12 how data is more useful when the user can quickly spot areas of concern.
FIGURE 7.12 Sample Report Using Colour Scales
Enhancements to Conditional Formatting
More icon options have been added to conditional formatting in recent versions, including triangles, stars, and boxes. You can also more easily hide icons from view, as is evident in Figure 7.13. Editing the rule gives you a variety of options, such as hiding certain icons, or even hiding the numbers and showing the icon only.
FIGURE 7.13 Edit Formatting Rule to Hide Icons
You can also easily mix and match data bars, colour scales, and icons from different sets. In Figure 7.14 the same range has both data bars and icons.
FIGURE 7.14 Applying Multiple Types of Conditional Formatting to the Same Range
SPARKLINES
Excel also has a useful micro charting feature termed the “sparkline”, which is a great way of displaying data trends in a small space. These lines are not as descriptive as regular graphs and charts, but they are very effective in displaying a quick view about the trends in the data or metrics.
Editing Sparklines
The attributes of the sparklines can be modified from options under the Design tab. You can easily change the sparkline from a line to a column or a win/loss chart by selecting the sparklines and clicking on one of the three options in the Type group under the Design tab.
To edit data, select the sparklines and click on Edit Data in the Sparkline ribbon under the Design tab, as shown on the left of Figure 7.17.
This will open the Edit Sparklines dialog box, where you can change the data range, the sparkline location or change to columns as shown in Figure 7.18.
FIGURE 7.18 Edit Sparklines Dialog Box
To highlight important data points, such as high point or low point, as has been done in Figure 7.17, select from a series of checkboxes in the Show group, under the Design tab. You can also edit the style of the sparklines by changing the line colours and marker colours.
Note that you can also Merge cells if you don't want to widen the columns or rows but want to make the sparkline bigger. To do this, highlight the cells that you want to merge, and select Merge & Center from the Alignment section on the Home tab.
Hiding the Sparklines' Data Source Sparklines are good at showing a lot in a small space and so we often want to hide the underlying source data for the sparkline. However, like charts, if you hide the underlying data, the sparkline also disappears. If you'd like to hide the rows or columns which contain the sparkline data then you'll need to click on the sparkline, go to the Design tab and in the Sparkline section, click on the drop-down below Edit Data and choose Hidden and Empty Cells. When the Hidden and Empty Cell Settings dialog box appears, tick “Show data in hidden rows and columns”, as shown in Figure 7.19.
FIGURE 7.19 Hidden and Empty Cell Settings Dialog Box
Changing Properties for a Group of Sparklines While each sparkline represents a unique series of data, you can edit the properties of a group of sparklines using the group and ungroup features. When the sparklines are grouped, you can edit the properties for the entire group, even if you select just a single line from the group. To separate out the sparklines in the group, select Ungroup in the Group ribbon under the Design tab.
BULLETPROOFING YOUR MODEL
If your model is publicly available, or if you have a lot of users entering data into it (especially if you are not sure of their level of Excel skill), it's a good idea to spend some time protecting it and making sure that all your hard work is not inadvertently corrupted!
Protection
Once you have completed building your model, there are three basic layers of protection you can include:
- Protect the file so that no one can open it without the password.
- Protect the structure of the workbook so that no structural changes can be made (normally used for hiding sheets and restricting access to them).
- Unlock individual cells on the worksheet and then protect the sheet so that only those cells that are unlocked can be changed.
Protecting the structure or worksheets can be done with or without a password. Passwords are case sensitive.
Protect the File This is done by changing the options and saving over the existing copy of the file. There are two options with this: protecting the file with a password or recommending read-only access.
Password-protect a file if you don't want anyone to be able to open your file without a password, or make changes without a password. This is done under General Options. You can select Password to Open or Password to Modify.
This is recommended if you are emailing a confidential file and are concerned that it might be intercepted or viewed by the wrong person!
Read-only recommended—instead of using a password to prevent access, you could use the read-only option to deter users from changing your workbooks. This way you prevent inadvertent changes to models.
Protect the Structure The most common use of protecting the structure is to protect hidden sheets, but it also stops users from adding or deleting sheets, or changing other structural aspects of your model, but not from editing cell contents. If you want to stop users from entering data or changing values in cells, you need to protect the worksheet.
Protecting hidden sheets—if you have a sheet containing sensitive data, you can hide the entire sheet, and then protect the workbook so that the sheet cannot be viewed. This is particularly handy if, for example, you have salary data on a sheet in a budget model and don't want all users to have access to the information.
Protect the Worksheet This is the most commonly used form of protection. If you have created a spreadsheet that you do not want to be altered by the others who might view it, you can protect the sheet. You can either protect the whole sheet, or just certain key cells in the sheet (probably those containing the formulas) so that new data can still be entered onto the sheet.
Protect Data by Locking Cells By following the process outlined above to protect the worksheet, you will find that you won't be able to make any changes at all to any cell! If you want to allow entry into certain cells, but leave part of the sheet protected (perhaps the cells containing formulas), you'll need to unlock those cells to be edited first, before protecting the sheet. Make sure you turn off your protection before unlocking cells!
By default, every cell in a sheet has a lock applied to it. It is the lock that is activated when the sheet is protected and stops people from editing cells. You need to unlock those cells that should be changed.
CUSTOMISING THE DISPLAY SETTINGS
The following is an example of a model that's been very highly protected, and the display settings have been changed so that it almost does not even look like an Excel file. You can download a copy of this file, called “Repayment Comparison Calculator”, from www.plumsolutions.com.au/book and if you would like to unprotect it, use the password plumsolutions. You will need to unprotect the sheet as well as the file structure, and how to do this is outlined in this section.
To make your model highly protected, like the model in Figure 7.20, you may choose to change a few settings to make your model look less like an Excel sheet, and more difficult for the user to change.
FIGURE 7.20 Model with Customised Display Settings
Useful Display Settings
Display settings can be changed by clicking on the File tab in the top left-hand corner, and then selecting Options. On the Advanced tab, there are a number of useful settings that can be edited to make your model more “bulletproof”:
- Hide row and column headings by unselecting Show Row and Column Headers.
- Remove sheet tabs by unselecting the Show Sheet Tabs box.
- Disable the scroll bars by unselecting the Show Vertical and Horizontal Scroll Bars.
- Hide the formula bar by unselecting Show Formula Bar.
In Excel for Mac, these options can be found under Excel, then Preferences and under View, in the Window Options section.
Minimising the Ribbon
Note that minimising the ribbon only changes the view in your Excel on your computer; it does not change the model. Hence, if colleagues open up the same model and their Excel settings have the ribbon maximised, they will see the whole ribbon.
To minimise the ribbon, go to the top right-hand corner of your screen and click on the little icon as shown in Figure 7.21; select Auto-hide Ribbon, or Show Tabs from the menu.
FIGURE 7.21 Minimising the Ribbon
Restrict the Work Area
You can also make the work area of the sheet much smaller so that users cannot enter data in cells you don't intend them to. See Figure 7.22.
FIGURE 7.22 A Worksheet with Restricted Work Area
To restrict the work area:
- Highlight the first column you don't want to see (for example, column F) and press Control+Shift+Right Arrow. Now right-click and select Hide.
- Highlight the first row you don't want to see (for example, row 6) and press Control+Shift+Down Arrow. Now right-click and select Hide.
To unhide, highlight column E and then click-drag the cursor across to where column F would be. Let go of the mouse button, and move the cursor back to column E. Right-click, and then select Unhide. Unhiding the rows works in exactly the same way.
Restricting Incorrect Data Entry with Data Validations
Usually, we are building models for others to use, and they might not have the same level of Excel skill as we do. Users often have a knack for finding strange and wonderful methods of entering data in the wrong format, which can mess up your formulas. They might enter text where the model requires numbers, or spell names in a different way to what the criteria in a VLOOKUP are expecting, for example. If unsure of a number they sometimes write TBA (for to be advised), or if the value is a range, they might enter 5–10%. They don't realise that the model must have a numeric value to calculate properly, and so they need to choose between 5 and 10 percent or enter the average of 7.5 percent! Using data validation will allow you to control the data that is typed into your model, and avoid errors.
Every cell will allow any value at all. Therefore, you can enter any type of data, of nearly any length (up to several thousand characters) into any cell. You can use the data validation feature to restrict the values users enter into cells in the model.
Data validations take protection one step further. Instead of restricting where data can be entered, they can also restrict what can be entered into the cell.
Once users enter a wrong value, they have to either enter the right value or hit the Esc (escape) key to get out of the error message. Hitting Esc will clear the entry in the cell they are trying to enter data into.
There are a couple of handy little tools you can add into the data validation while you are here. You can enter in cell comments on the Input Messages, as shown in Figure 7.23. Some text will pop up to help users or give them important information whenever the cell is selected.
FIGURE 7.23 Data Validation Comment
See the section “Methods and Tools of Assumptions Documentation” in Chapter 3 for more information on how to create the data validation comment shown in Figure 7.23.
In the Data Validation dialog box, you can also enter your own, customised error message on the Error Alert tab, as shown in Figure 7.24. This message will be shown if users enter invalid data. Resist the temptation to write something silly (or rude!). Now try entering incorrect data, and your error message will pop up, as shown in Figure 7.25.
FIGURE 7.24 Creating a Customised Error Message
FIGURE 7.25 Customised Popup Error Message
Note that you can control how vigilant the data validation restrictions on your model will be. One day your department may have grown and it will suddenly become valid to have more than 500 staff. If you change the options on the Error Alert tab to Warning instead of Stop, then the message will alert users and discourage them from entering staff number 501, but it will allow this, if necessary. The error message will then prompt users that the restriction should be changed. The users can choose Yes to allow the entry of 501.
If you change the options on the Error Alert tab to Information instead, users are alerted that they entered an unexpected value, but the message defaults to OK and allows them to keep the value that they entered.
Using Validations to Create a Drop-Down List
Instead of specifying a range, you can be more specific and only allow certain predefined entries. This is how we can create drop-down lists with data validations.
- First, create a list of valid regions, such as the list below:
- United Kingdom
- Germany
- India
- Australasia
- France
- United States
- Select the cell that should contain the drop-down list.
- Bring up the Data Validation box:
- On the Data tab from the Data Tools group, click on the Data Validation button.
- In the Allow section, change Any Value to List, as shown in Figure 7.26.
FIGURE 7.26 Creating a Drop-Down List
- In the Source field, enter the range that contains the data you want to appear in the drop-down list. Leave “In-cell dropdown” selected. See Figure 7.27.
FIGURE 7.27 Enter the Source Data Range
- Any time that the cell is selected, a drop-down arrow will appear and users will be able to select from the list. See Figure 7.28.
FIGURE 7.28 Completed Drop-Down List
If users try to enter data that is not on the list, an error alert will appear. Also, if you change the source data, the drop-down options will also change. Practice this; try changing United Kingdom to Britain. You'll see that Britain now appears on the list instead of United Kingdom.
Be aware, however, that it is possible to paste values over a restricted cell! You may have spent a long time protecting and setting up your worksheet so that users cannot enter incorrect data; however, it is very easy for users to inadvertently (or perhaps deliberately) paste over a data validation without receiving the error message or restrictions that would have appeared had they tried to enter the data directly into the cell.
Referencing Source Data on Another Sheet In older versions of Excel, a restriction with data validation drop-downs was that the source data and the drop-down needed to be on the same page. In newer versions of Excel, we can now link directly to source data on other sheets when creating a data validation drop-down box without having to use a named range. However, the file will not be compatible with Excel 2007 or earlier—the drop-down will simply not contain any data in the list. Microsoft says that it did install a patch to fix this in Excel 2007 but when testing this on various clients who were still using 2007, I found that it still did not work. It is therefore recommended to always use named ranges when linking drop-down boxes to source data on another page to ensure compatibility with earlier versions of Excel. Here's how to use a named range in a data validation drop-down box:
- Highlight the source data list and assign a named range to the list (for example, countries). See the section “Named Ranges” in Chapter 5 for instructions on how to do this.
- When creating the data validation drop-down, in the List box, enter an equal sign followed by the name of the range, as shown in Figure 7.29.
FIGURE 7.29 Creating a Drop-Down List Using a Named Range
- You must type “=” and then spell the named range correctly for it to work. Tip: Use the F3 shortcut if you cannot remember exactly how you spelt the named range.
FORM CONTROLS
Form controls are objects such as drop-down boxes and option buttons that sit over the top of Excel sheets like charts do. They can interact with formulas so that when the option is selected, it changes the formula and can drive the model. They can be especially useful for scenario selection and assumptions in a financial model, as well as interactive, dynamic dashboards.
Building form controls into a financial model can be time-consuming for the modeller, but they do make the model look very professional and the interface becomes extremely easy to use.
Accessing Form Controls
You will need to have the Developer tab showing in the ribbon. If you cannot see it in the ribbon, you'll need to change your options.
Showing the Developer Tab in the Ribbon
The Developer tab contains several functions that are useful when developing macros and form controls. Take a look at Figure 7.30, which shows the Developer tab in the ribbon. The default installation version of Excel will not have the Developer tab showing in the ribbon, so if you can't see this tab, you'll need to install it. You will need to change this setting only once unless you reinstall Excel, or move to another machine.
FIGURE 7.30 The Developer Tab
To display the Developer tab in the ribbon:
- Click on the File menu in the top left-hand corner of your screen.
- Scroll down to Options at the very bottom and select it.
- Select Customize Ribbon.
- In the box to your right, tick the Developer box.
In Excel for Mac:
- Click Excel in the upper left-hand corner.
- Click Preferences.
- Select Ribbon & Toolbar.
- On the right-hand side, check the Developer box.
- Click Save.
Checkboxes
Checkboxes are very handy tools that can help users choose their desired options from a list. By building checkboxes into a financial model, the developer allows users to customise the model by adjusting the combinations of inputs for the financial model's assumptions.
Columns A and B contain the raw data. A group of checkboxes has been created in column E, and the format control output cell that corresponds to each checkbox has been placed in column D. The formula in column F will then only pick up the values for the items that have a checkmark next to them. Because D2 contains a FALSE (or zero) value, multiplying a number by D2 will return a zero value. This technique can easily be applied to charts and dashboards.
You can find this model, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.
Option Button Sometimes users may need to choose one item from a list, which drives the rest of the calculations and analyses. The Option button (sometimes called a Radio button) offers you the ability to create this logic to restrict the number of options users can choose in the financial model.
Spin Buttons Spin buttons are useful for controlling what users can enter into the input fields in your financial models. By including a Spin button, users can increase or decrease the number in the increments that you specify. For example, if you set it to increments of 5, and the current value is 400 when users hit the up arrow, it will change to 405, then 410, 415, and so on, and the same with the down arrow.
Combo Boxes
Creating a drop-down box is an important tool in many user-friendly models where the modeller wants the user to select from a predefined number of options, making the model easier to use, and also avoiding mistyping and potential errors. The advantage of combo boxes is that you eliminate any ambiguity in user inputs and still keep things simple for users to provide the inputs. See Figure 7.44.
FIGURE 7.44 Completed Combo Box
Drop-Down The combo box is an alternative to using the data validation drop-down tool. See the section “Bulletproofing Your Model” earlier in this chapter for instructions on how to create a data validation, in-cell drop-down box. The only advantage of the combo box is that the drop-down arrow is visible whether the cell is selected or not. This is a significant advantage if users are not very Excel savvy.
From the modeller's perspective, however, a combo box takes much longer to build, requiring more cells for the process, and is, therefore, less efficient. For these reasons, most modellers prefer the data validation drop-down tool.
Boolean Logic (Binary Code)
Most form controls use Boolean logic, which converts everything to one of two values: a one or a zero. For example, if a checkbox is selected, it will return a value in the output cell of TRUE, which equals 1; and if it is left blank, it will be FALSE, which equals zero. We can use the output cell to drive a formula.
In Figure 7.49, the output cell is B5. When the checkbox is checked, it shows the value TRUE, which is equal to one, so this has been incorporated into the model by using the formula =B7*((B5*B2)+1).
FIGURE 7.49 Checked Checkboxes Drive Calculation
If the checkbox is left blank, the output cell would display FALSE, which gives the value zero, and according to the formula, the GST would then not be included in the total. See Figure 7.50.
FIGURE 7.50 Unselected Checkboxes Drive Calculation
Similar formulas can be created to link to other form controls, such as Option buttons and combo boxes.
Form Controls versus ActiveX Controls
You may have noticed that the Insert button under the Developer tab has two sets of options: form control and ActiveX controls. While both have the same set of functions, they have a slight difference. The ActiveX controls are tailor-made for a Microsoft Office environment and use Microsoft ActiveX to enhance their functionality. If you are absolutely certain that your financial model would be used in the MS Office environment only, then you could possibly add a few additional enhancements to your controls. However, if your users might also be using other environments like Mac OS, the ActiveX controls can cause problems—as Apple's operating system does not, at the time of writing, support Microsoft ActiveX.
Form controls generally provide all the functionality we need for financial models, so it is simpler to stick to them unless you specifically need the advanced functionality of ActiveX. If you want to keep your financial modelling product platform independent, stick to form controls. As far as core functionality is concerned, form controls will offer you all the necessary flexibility required for a smooth user experience.
SUMMARY
Formatting and displaying the data and inputs are important parts of building a financial model. Of course, the accuracy of the model and its calculation are most important, but having a model that looks good and is easy to use certainly adds to the integrity and usability of the model. A model in which the model builder has obviously taken care to use clear colour coding, borders, and correct formatting will be trusted more and given more credibility by the user. Therefore, good modellers should spend a little time (not too much) during the model build to format colours, borders, and styles to make it more accessible.