A FEW WORDS ABOUT SEMANTICS
Learning about financial modeling can be tricky in written instructional form since words translate into commands, which can be very specific for computer programs. To avoid confusion, the following is a quick guide to the words that are used in this text and how they translate into the required actions the reader must perform. The key is to understand that there are four main operations we will perform on a cell and a fifth word to be aware of:
Enter a value. When the Model Builder exercises ask for a value to be entered, this will be a number, date, or Boolean (TRUE or FALSE) value. These are values that will be referenced for some type of calculation purpose.
Enter a label. A label is text in a cell to help the model operator understand values and formulas in relative proximity. Note that I use the word as a verb as well. For example, I may say label A1, “Project Basic Cash Flow”. This means that the text “Project Basic Cash Flow” should be entered into A1. Also, there are times when I will use the word label with a number. This means that a number will be used as a label and not referenced in the actual calculation on the sheet or be used by the VBA code. Mostly these types of numbers will be used to describe time periods.
Name a cell or range of cells. Not to be confused with labeling, naming is a specific technique that converts the reference of a cell or range to a user defined name. This is done using the Name Box in the upper left corner of the Excel application or by selecting the Formulas tab and selecting the Name Manager button. In the Name Manager dialogue boxes, you can create, edit, and/or delete named references. It is particularly important to name a cell or range of cells as commanded if VBA code is being used for the Model Builder. This is because the name will be used in the code to reference the cell or range on the sheet. If the name does not exist the code will break down with an error.
Enter a formula. The core reason we are using Excel is for calculation purposes. A formula is initiated in Excel with the “=” sign. When I state to enter a formula, I will provide the cell it should be entered in and the exact formula that should be entered. Often I have copied the formulas from the Excel models themselves to ensure that the text exactly corresponds to the example models provided on the website.
Function. Be careful with the difference between a formula and function, as some people are used to hearing “Look at the function in A2” or “Enter the function there.” The word function in this book can be used in a few ways. The most common way is when readers are instructed to use a pre-defined Excel function such as SUM, NORMSDIST, AVERAGE, and so forth. These are functions that are already created in the Excel Worksheet Function library and require the user to type only the name with parameters in parentheses to return a value. You will also hear the word function used when we describe the theoretical formulas that underpin the topics being taught. Readers should be aware that the use of the word is to help explain the formula at hand. Finally, the word function may show up in certain sections where VBA is used. In VBA, users can use functions from many precreated libraries or create their own functions for use on the sheet or within VBA modules. If we use the word function in that context, it means we are most likely talking about a function that returns a value in the code. See Figure 1.4.
FIGURE 1.4 Commands in this book should be followed as described in the figure and in the text preceding it.
MODEL BUILDER 1.1: Initial Settings
With the first Model Builder, we should take a moment to understand how the Model Builder sections differ from other parts of the book. Each Model Builder is an instructional section that should be completed with the use of a computer running Excel. It should be followed step-by-step using the instructions. Each Model Builder assumes that each step in the Model Builder was read and implemented. The eventual result of the Model Builder sections is a complete version of the model we are discussing. Versions completed by the authors reside on the book's companion website. If at any point you find yourself lost you should open the corresponding completed file on the website to see how the relevant section should be completed.
This first Model Builder is to make sure that our versions of Excel are all set to identical settings. Depending how you installed Microsoft Excel or Office you may need the installation disc to enable all of these settings.
First, we will be using a few functions and tools that require the Analysis Tool Pak, Analysis Tool Pak VBA, and Solver Add-Ins to be installed. To do this:
For Excel 2007: Select the Office button, select Excel Options, select Add-Ins, and then select the Go button, which is to the right of Manage and a box that should default to Excel Add-Ins. This will bring up the same box as in Figure 1.5. Check the boxes for Analysis Tool Pak, Analysis Tool Pak VBA, and Solver. Select OK. If the Add-Ins are not installed it may prompt you with a few messages stating that Excel will need to install them. Depending on how Excel was initially installed, you may need the installation disc to complete the install.
For Excel 2003 and earlier: Select Tools, select Add-Ins, and check the boxes for Analysis Tool Pak, Analysis Tool Pak VBA, and Solver. Typically the Analysis Tool Pak and the Analysis Tool Pak VBA are the first two Add-Ins on the Add-Ins list. Solver is usually at the bottom. Select OK. If the Add-Ins are not installed, it may prompt you with a few messages stating that Excel will need to install them. Depending on how Excel was initially installed, you may need the installation disc to complete the install. Figure 1.5 depicts the Add-In selection box.
The next setting we should set is the ability to run macros. We will add significant functionality through the use of VBA. If you would like to take advantage of this you will need to continue on to step 3.
For Excel 2007: Excel 2007 requires a bit more setup to work with macros. Select the Office button, and select Excel Options. On the default tab, the Popular tab, check the third check box down, “Show the Developer tab in the Ribbon.” Press OK. Once the Developer tab is visible, select it and then select Macro Security. In Excel 2007 you have four options for Macro settings, three of which are similar to Excel 2003. The only exception is that you can disable all macros except ones with a digital signature. Since hardly anyone has taken Microsoft up on its security measures and people rarely use digital signatures for Excel files, we will ignore that option. We can safely set it to disable all macros with notification. The notification will occur when the workbook is opened and will be a button with “Options...” in it at the top of the sheet or through a dialogue box asking you to enable macros. All workbooks from this book's website can be accepted, as they are virus-free. An example of one of these dialogue boxes is shown in Figure 1.6. In Excel 2007 you should not have to restart Excel for this to take effect.
FIGURE 1.5 The Add-In selection box allows users to install precreated or user-created Add-Ins.
For Excel 2003 or earlier: Select Tools, select Macros, select Security. You have the choice of either Low, Medium, or High. Low will allow macros without prompting, Medium will prompt you to enable or disable macros within a workbook when it is opened, and High disables macros in a workbook. The main concern is that viruses can be built into macros, which can cause significant damage or security concerns. All VBA subroutines and functions in this book contain no viruses and can safely be opened with macros enabled. You may want to set your computer to medium security so that you enable only trusted workbooks. For the changes to take effect, you must shut down Excel and reopen it. When prompted, enable macros for each file by selecting Enable.
Once the Add-Ins are installed and the macro security is set, we can actually start constructing our models. At this point we should go over repetitive processes for the remaining Model Builders. For many of the new Model Builders that are constructed, you will be asked to create a new Excel workbook. It is recommended to get in the habit of using the naming convention we recommend. This normally follows MBX.Y_User.xlsm. This will let you quickly identify which ones you worked on and put them in order for later reference. It also means that completed versions on the website will be filed similarly, since they are named MBX.Y_Completed.xlsm.
FIGURE 1.6 Once the macro security setting is set to “Disable All Macros with Notification,” the following “Options ...” button appears when workbooks with macros are opened.
