Fundamentals of Financial Modeling Course Transcript

This is the transcript from our most popular financial modeling course at the Corporate Finance Institute

Fundamentals of Financial Modeling

Part 1 (video course)

Financial modeling Slide 2: Session objectives

Welcome to Excel modeling tips and tricks. By the end of this session you will be able to, one, navigate around an Excel worksheet and workbook more quickly, two, edit cells and access Excel menus without your mouse, and three, make use of Powerful Excel formatting, editing, and navigation shortcuts. Let’s get going.

Financial modeling Slide 3: Avoiding the mouse

If you really want to speed up your Excel model building skills, it is important to limit your use of the mouse. Using the keyboard for Excel tasks is much faster than using the mouse. It takes up valuable time to move one hand from the keyboard to the mouse, identify the cell or item you want to select, and then move the mouse to that cell or item before finally clicking the mouse.

Financial modeling Slide 4: Accessing menus and tabs quickly

To access menus in Excel you simply hit the “Alt” button. In Excel 2007 and later versions, letters will appear next to menu items once you have hit “Alt”. Simply select the letter corresponding to the menu you wish to access. In Excel 2003 and prior versions, the shortcut letters are underlined.

Financial modeling Slide 5: Working with columns and rows

Here are a couple of examples of how you can use the Alt key to access menus rather than using your mouse. To insert a row without a mouse in Excel 2007 and later versions, you hit the “Alt” key, and then the letters “H”, “I”, “R”. In Excel 2003 and prior versions, the shortcut is the “Alt” key and then “I”, “R”. To insert a column in Excel 2007 and later versions, you again hit the Alt key and then the letters “H”, “I”, and finally “C”. In Excel 2003, the shortcut is the Alt key plus “I” and then “C”. While we are talking about inserting rows and columns, did you know that to insert a worksheet without your mouse it is as simple as hitting “Shift” and then “F11”?

Financial modeling Slide 6: Navigating columns and rows (demonstration)

Here is a sample workbook with a number of worksheets. Let’s begin by identifying how you can use the Alt key to quickly navigate through Excel menus. By hitting Alt, you’ll see that letters have appeared at the top of the screen. We can then select the letter for the ribbon or tab we want to see. Let’s type “H” for the home ribbon, and then let’s insert a row. We find “Insert”, which in this case is “I”, and then we can select “R” for row. Let’s move on.

Financial modeling Slide 7: Working with columns and rows

The mouse is often used to quickly select entire rows and columns; however, there is an even quicker way to do this without your mouse. To select a row without a mouse, choose any cell within the row and then hit “Shift + Spacebar”. To select a column without your mouse, choose any cell in the column you want to select and then hit “Ctrl + Spacebar”. To select the entire worksheet you can type “Shift + Spacebar” and then “Ctrl + Spacebar”.

Financial modeling Slide 8: Moving around a workbook rapidly

How do you move quickly around a workbook without your mouse? To move between worksheets, hit “Ctrl + Page Up” or “Ctrl + Page Down”. To move to the end of a data row or column, hit “Ctrl” and an arrow key. To select the cells between where you start and end, hit “Shift” and an arrow key. To move to the end of a data row or column and select all the cells between where you start and end, hit “Ctrl”, then “Shift”, and then an arrow key.

Financial modeling Slide 9: Right-click without the mouse

How do you right-click without your mouse? To view the right-click menu without using your mouse you simply hit “Shift” and “F10”.

Financial modeling Slide 10: Moving around menu tabs

If you are in a menu window, such as “Format Cells”, you can quickly move between menu tabs using the same shortcut we used to move between worksheets. Simply hit “Ctrl + Page Up” or “Ctrl”, “Page Down” to move between menu tabs. If you want to move between items on a menu window simply hit “Tab”.

Financial modeling Slide 11: Navigating around data (demonstration)

Let’s navigate around a workbook without our mouse. First of all, let’s navigate around this worksheet and in particular, navigate around some data without using our mouse. Let’s first go to “SalesGrowthPercent”. You can see that there is a table of data that goes from row 8 to row 14, from column A through to column H. What we want to do is quickly go to the various ends of the data range. Let’s start in column A, row 8. To go to the end of the data in that row, simply hold down the “Ctrl” key and press the right arrow key. In order to go to the bottom of this data range, which is row 14, column H, hold down “Ctrl” and hit the down arrow. In order to go to the very first column which is row A, we stay in row 14, hold down “Ctrl”, and the left arrow key. To go back to the original cell A8, simply hold down “Ctrl” and the up arrow key. Now if you would like to select all the data in row 8, simply keep holding down “Ctrl”, then hold down “Shift”, and then use your arrow key. In this case, we’ll hit the right arrow key together with “Ctrl” and “Shift”, and then the whole data row will be selected. In order to navigate around a workbook without your mouse and go between worksheets, use “Ctrl + Page Up” and “Ctrl + Page Down”. Let’s do that right now. “Ctrl + Page Down” will bring us to the income statement. If we want to go to the balance sheet, use “Ctrl + Page Down” again and so on.  If we want to go back to the income statement, press “Ctrl + Page Up” twice. This is quicker than using the mouse to select individual worksheets.

Financial modeling Slide 12: Moving around a workbook rapidly

Another way to move quickly around a workbook is to use the F5 key. F5 gives you quick access to the “Go To” function. The F5 Go To menu window includes a reference window that allows you to type the reference for the cell you wish to access.  When you click “OK” you will immediately be taken to the cell. At the bottom left-hand corner of the “Go To” menu window is a button labeled “Special”. “Go To Special” allows you to go directly to cells with comments, constants, formulas, and so on.  Finally, if you use names in your workbook, you can go directly to name cells or ranges also using F5 “Go To”.

Financial modeling Slide 13: Using F5 (demonstration)

Now let’s explore how we can use F5 “Go To” to quickly navigate to a cell or named range. When we press “F5” we get the “Go To” window. In this workbook names have been used, so that’s why the window at the top is populated with a number of things ranging from “CapitalAssetsPercentofSales” all the way down to “TaxRatePercentofEBT”. Let’s say you want to find out where revenues are in the workbook, or you want to go directly there. You need only select the name, then press “Ok” or “Enter”, and you are immediately taken to where revenues are in this workbook, which in this case is on a separate worksheet.

Slide 14: Excel navigator exercise

Now it’s your turn. Click on the link entitled “Tensel Navigator Exercise with instructions”. Then once you’ve had a go, check your attempt with the attached “Tensel Navigator Solution”. Good Luck!

Financial modeling Slide 15: Editing cells

Now let’s look at several useful Excel editing shortcuts. For example, if you want to edit a formula in a cell, you can select the cell you wish to edit and then use your mouse to access the formula bar. Alternatively, you can use the “F2” key to edit a cell’s content more quickly. “F2” allows you to edit an active cell directly in that cell without having to access the formula bar.

Financial modeling Slide 16: Toggling between absolute and relative references

The “F4” key is a useful editing shortcut in Excel. Excel accepts absolute and relative references. Absolute references have a dollar sign before the column portion of the reference and/or the row portion of the reference. For example, “\$A\$1” tells Excel to keep the column and row reference fixed on A1 when copying a formula with this cell reference into other cells. In order to quickly add dollar signs to your cell references you can use the “F4” key. Simply ensure that your cursor is in the cell reference you want to change and then hit “F4”. If you are not editing a cell reference, F4 provides an additional useful shortcut. It allows you to repeat your previous command.

Financial modeling Slide 17: Inserting and editing comments

To quickly insert and edit comments you can use the shortcut “Shift” plus the “F2” key. “Shift”+ “F2”  allows you to type in a new text or edit text in a comment box. If you want to then format the comment box, press “Shift + F10” to access the right-click menu while in the comment box. In order to exit, hit “Esc” once to exit text edit. You can still format a comment after hitting “Esc” only once. Hitting “Esc” twice allows you to exit the comment altogether.

Financial modeling Slide 18: Fill right and fill down

Fill right and fill down are two useful functions that can be used instead of copy and paste. Let’s work through an example using fill right. To fill right, you must first select the cell with the formula you want to copy across to the other cells. You then use “Shift” plus the right arrow key to select the cells you want to copy to. Then, you press “Ctrl + R” to fill right.

Financial modeling Slide 19: Formatting cells

To quickly format cells, consider using “Ctrl + 1”.  In order to define your own number formats, you need to find “custom” on the number tab. The first number format tells Excel how you want positive numbers formatted. The second number format tells Excel how you want negative numbers formatted. The third number format tells Excel how you want to format zero. You need to make sure that each of these number formats is separated by a semi-colon.

Financial modeling Slide 20: Undo and redo

It is common to use the arrow menu icons to undo or redo commands. Consider instead using “Ctrl + Z” and “Ctrl + Y”. “Ctrl + Z” is undo. “Ctrl + Y” is redo. If you hold down “Ctrl + Z”, Excel will undo multiple commands.

Financial modeling Slide 21: Quickly inserting formulas and functions…

If you want to quickly insert argument names associated with a function, select the name of the function you want to use. For example, for the net present value function, type “=NPV” and then use “Ctrl + Shift”, plus the letter “A” key to automatically insert argument names and parentheses.

Financial modeling Slide 22: Quickly inserting formulas and functions…

Finally, if you want to quickly sum numbers, consider using the “Alt” key, plus the “=” sign.

Financial modeling Slide 23: Editing shortcuts (demonstration)

Let’s explore some of the Excel editing shortcuts that we’ve just considered in the previous few slides. First of all, let’s insert a comment in D6. Right now, we are on an income statement forecast that is only partially complete. We can insert a comment here by pressing “Shift + F2” and then we can type the comment. In order to get out of the comment, we hit “Esc” twice. Hitting “Esc” once will allow the comment to be formatted. By hitting it a second time, the comment is then only identifiable by a red triangle. The next thing we will do is a quick reminder of how to edit a cell without having to go to the formula bar. Let’s start at the formula bar that you see at the top. In this case, the formula says “=-D6*(1-GrossMargin)”. Now instead of actually going up to the formula bar to edit the formula, we can hit “F2” and edit the formula in the cell itself. So that is the “F2” or edit function. Another thing we covered is how to quickly sum cells. In order to sum cells quickly, you can use “Alt” plus the “=” sign. To do that, we start by highlighting the cells we want to sum. We stop highlighting in the cell where we want the sum to appear. By hitting “Alt” + “=”, the cells are summed. We can then repeat the process to complete the column. Now we also saw “Ctrl + R” which is fill right, or “Ctrl + D” which is fill down. There is nothing on this income statement forecast that we need to copy down, but we do want to copy the forecast across. Instead of using “Ctrl + C” and “Ctrl + V”, consider using fill right. To highlight the cells we want copied across, use the “Shift” and the arrow keys. Here, we hold down “Shift” and move to the end of the forecast with the “Right” arrow key. After this, simply press “Ctrl+ R”, fill right. The forecast is now complete.

Financial modeling Slide 24: Excel edit exercise

It’s your turn again. Click on the link entitled “Tensel Edit Exercise with instructions”. Once you’ve had a go, check your attempt with the attached “Tensel Edit Solution”. Have fun!

Financial modeling Slide 25: Excel modeling tips and tricks conclusion

That concludes this session on Excel tips and tricks. The key messages from this session are: if you really want to speed up your Excel model building skills it’s important to limit your use of the mouse. Also, Excel has numerous shortcuts in order to quickly navigate around Excel, edit cells, and access Excel commands. I encourage you to practice using as many Excel shortcuts as possible. Bye for now.

Financial modeling Slide 26: Thank you

Part 2 (video course)

Financial modeling Slide 2: Session objectives

Welcome to Key Design Issues “Better formulas”. By the end of this session you will be able to: build greater flexibility into your financial models, break down your financial models and complex calculations so that they are easier to audit and update, minimize errors in your Excel models by making your model assumptions explicit, and finally, understand the importance of Excel function syntax. Ok, let’s get started.

Financial modeling Slide 3: Understanding complex calculations

Let’s start with a formula example.  Here is a spreadsheet where the Weighted Average Cost of Capital, or WACC, is calculated.  If you look at the formula bar, you’ll see the various components that have been input in order to calculate the cost of capital. How do you know the formula is correct?  Are you confident that the formula gives the correct answer?  The short answer is that you don’t.  It is almost impossible to tell whether or not the formula gives the correct answer.  In addition, hard-coding the inputs makes it very difficult to undertake what-if or scenario analysis.

Financial modeling Slide 4: Tips for breaking down calculations

Instead of hard-coding numbers into cells, financial model builders will always aim to 1) Break down calculations into steps, 2) Make all their inputs explicit, 3) Use the Excel naming feature,  and 4) Use color and structure.  Let’s look at each one of these in more detail.

Financial modeling Slide 5: Breaking down complex calculations

Here is the same cost of capital calculation.  The first thing you will note is that where we originally had only one cell in our worksheet, which is called the cost of capital output, we now have several. All the inputs are now explicit, and the calculation has been broken down into several steps. Color has been used to separate inputs from outputs.  In this spreadsheet, yellow is used to identify input cells, while blue represents formulas or output cells. By changing any of the yellow input cells, the cost of capital will automatically change. This makes our model much more flexible and ensures we can undertake scenario analysis. If you look at the formula bar, you will also see that instead of numbers, we now have names. Let’s move on and explore names in more detail.

Financial modeling Slide 6: Using names

By using names in your financial models, it will help you mitigate inadvertently linking incorrect cells.  It will allow you to speed up the model building process.  It also allows the financial model builder to avoid switching between worksheets. And finally, it helps facilitate transparency which will help later when we want to audit models.  Now in Excel 2007, names are accessed from the formulas ribbon under Define Names.  In other versions of Excel, names are accessed from the Insert Names pulldown menu.

Slide 7: Defining names in Excel 2007

In Excel 2007, to find names you go to the ‘Formulas’ ribbon, you then look for the box that is called ‘Define Names’ where you’ll see a number of choices that all relate to managing, creating, and using names in your Excel workbooks.

Slide 8: Defining names in Excel 2003

In Excel 2003, and other older versions of Excel, you find names by going to the ‘Insert’ menu and then to ‘Names’. Here you’ll see you can define, paste, create, apply, and label names.

Financial modeling Slide 9: Creating names

Let’s explore how to create names in Excel.  You will most likely want to name your input cells.  In our example, the input cells that we wish to name are highlighted in yellow and include Current Sales, Sales Growth Percent, and Gross Margin. If the text names are adjacent to the number or input cell you wish to name, you can simply highlight the cells and create the names.  In Excel 2007, you create names by going to the ‘Formulas’ ribbon, ‘Define Names’, ‘Create from Selection’. In other versions of Excel, you create names by going to ‘Insert’, ‘Name’, ‘Create’.  Once you’ve highlighted the cells you want to name, including the text cells, and found the create names option in your version of Excel, a Create Names window will appear.  In our example, since we have highlighted cells where the text names are in the left column, Excel will automatically select the create names in ‘left column’ option. You can also name cells by selecting the cell you want to name, and then typing the name you want in the name box.  The name box is found to the left of the formula bar.

Financial modeling Slide 10: Creating names in Excel 2003

Okay, here’s the actual Excel spreadsheet we saw earlier.  Now, in order to create names, there are two ways that we can do it.  First of all, we can choose the cell we want to name and I’m going to first go to ‘C3’.  I want to name it ‘CurrentSales’. I could simply overtype the cell reference in the name box.  The name box is to the far left of the formula bar.  And so, what I do is just click in the name box and overtype the C3 with the name I want to use. I’m going to type in ‘CurrentSales’, hit ‘Enter’ and now that cell is named.  I could though name more than one cell at once.  This is similar to what we just talked about.  So, I highlight the cells I want to name, including the text names. I then go to the following menu, ‘Insert’, ‘Name’, and then ‘Create’. Instead of using your mouse, you could access the same menu by highlighting the cells; again just use your arrow keys rather than your mouse. Hit ‘Alt’ to access the menus, and then go ‘I’ for Insert, ‘N’ which is underlined for Name, and ‘C’ which is the underlined number for Create.  When you have actually done those steps, you will get the Create Names window, and the left column is already selected.  If you hit ‘OK’, you now have all your names.  How can we tell?  Well, if I move my arrow keys around and select cell ‘C5’, you will see in the name box. Let me just highlight again, you’ll see that GrossMargin appears.  When I select Sales Growth Percent, SalesGrowthPercent appears in the name box.  And finally, when I look at Current Sales, you’ll see that CurrentSales appears in the name box.

Financial modeling Slide 11: Creating names in Excel 2007

In Excel 2007, we do very much the same thing. The key difference to note is that we go to the ‘Formulas’ ribbon to create names, we go to ‘Define Names’ as I’ve highlighted earlier, and then we click on ‘Create from Selection’.  Your shortcut, in this case, is to click ‘Alt’ to access your menus, go to ‘M’ for Formulas, and then ‘C’ for Create from Selection.

Slide 12: Using names – F3

Once you have created names, it is very quick and easy to access and use these names in other cells and formulas.  The easiest way to access your newly created names is by simply hitting ‘F3’.  F3 calls up the Paste Name window. The Paste Name window shows all the names that you have created.  Simply click on the name you want to use.  You will see that on the bottom left-hand corner of the Paste Name window is a button named Paste List. Once you have completed your financial model, it is important to document all the names used in your model.  The Paste List button pastes all the names that you have used in your workbook together with the cells they refer to.  In Excel 2007, you can also paste names by going to ‘Formulas’ ribbon, ‘Define Names’, ‘Use in Formula’.  In other versions of Excel, you can paste names by going to ‘Insert’, ‘Name’, ‘Paste’.  Finally, you can use names simply by typing them into your formula.

Financial modeling Slide 13: Using names in Excel 2003

Okay, let’s use our names.  First of all, we’re going to use the F3 function key to access our names.  We’re going to calculate forecast sales and so I’m going to go equals ‘=’, and then I’m going to hit ‘F3’.  And you can see that I can then select ‘CurrentSales’, and then I’ll click ‘OK’, and we immediately have access to that name.  Times ‘*’, 1 plus ‘+’, and then I’m going to hit ‘F3’ again and find ‘SalesGrowthPercent’.  It’s as quick and easy as that.  Now I could’ve simply typed the name, so I’m going to do that for CostofSales and GrossProfit.  So I’m going to put a negative sign ‘-‘ because I want my expenses to be negative. I’m going to go negative ‘-‘, ‘C7’ because I haven’t named this cell. Times ‘*’, 1 minus ‘-‘, the ‘GrossMargin’.  Again I’m typing the name as opposed to hitting ‘F3’ or Paste Name. And finally, I could just sum these up and we know that the sum is 8333.  Or if I want to use a name, I’m going to just say equals ‘=’, ‘ForecastSales’, times ‘*’, ‘F3’, select ‘GrossMargin’, and we’re done!

Financial modeling Using names in Excel 2007

In Excel 2007, it’s very similar.  The function ‘F3’ still works so if we want to go equals ‘=’ CurrentSales, we can still hit ‘F3’.  And again select ‘CurrentSales’.  You’d go times ‘*’ 1 plus ‘+’ and again ‘F3’, and just choose ‘SalesGrowthPercent’.  So whether we’re using Excel 2007 or prior versions of Excel, the principles are very much the same.

Financial modeling: Names exercise

Now it’s your turn.  Click on the link entitled ‘Names Template with Instructions’.  And then once you’ve had a go, check your attempt with the attached ‘Names Template Solution’.  Good luck!

Financial modeling: Understanding financial functions

When building better formulas in Excel, it is also important to ensure that you are using functions correctly.  Financial functions in Excel don’t always work as you might expect. The NPV function, for example, is commonly used incorrectly.  In this example, we have a stream of free cash flows.  Your ‘0’ means the cash flow happens now.  Your ‘1’ means the cash flow happens in one year’s time.  And so on. In this instance, the model builder has typed in ‘=’ equals, ‘NPV’, ‘(‘ bracket, then the discount rate, ‘B2’.  And then highlighted all four cash flows.  Year 0 to 3.  Excel has calculated the result as \$1,000.  Unfortunately, this NPV calculation is not correct.

Financial modeling

: Understanding NPV functions

Here is an Excel spreadsheet with the same free cash flow information.  You will see that the formula in ‘B1’ has been changed.  And that the correct net present value or NPV, is actually \$1,100.  So, what went wrong?  The NPV function in Excel assumes that the first cash flow happens at the end of the first period.  In the previous example, we include the year 0 cash flow, which did not need to be discounted. Even if you’re a bit rusty on the NPV function, the key message is that Excel financial functions don’t always work as you might expect.  It is critically important that you understand syntax and assumptions built into Excel before using any financial function.

Financial modeling: NPV exercise

Ok, it’s your turn again. Click on the link entitled ‘NPV Template with Instructions’.  And once you’ve had a go, check your attempt with the attached ‘NPV Template Solution’.  Have fun!

Financial modeling: Dealing with multiple conditions

In order to build better formulas in Excel, it is also important to consider whether or not there is more than one way or function available to solve a modeling problem. If so, what function or formula is going to be the easiest to update, audit, or undertake scenario analysis with.  Here we see an example for Telco Inc. Telco Inc. is a major telecommunications company that regularly issues debt securities or corporate bonds.  The coupon rate on any new bond issuance will depend on Telco’s credit rating. The credit ratings you see here come from Moody’s. If Telco’s leverage ratio is under 40%, Moody’s will likely rate Telco as a ‘Baa1’.  And that Telco will be able to issue bonds with a coupon of only 8.5%. If Telco’s leverage ratio is between 40 and 49.9%, Moody’s will likely rate Telco as a ‘Baa2’, and Telco will need to offer a coupon of 8.8% on any bonds it issues. You’re looking to build a financial model that will automate this calculation. You have two options: 1) Use IF statements or 2) Use Lookup tables.

Financial modeling: Using VLOOKUP in Excel 2007

In Excel 2007, in order to insert a function like IF statements or VLOOKUP, you need to go to the insert function menu. You can find that in Excel 2007 by ‘Alt’ and going to the ‘Formulas’ ribbon. I’ve hit ‘M’ there and then you can hit ‘F’ as a shortcut for insert function. You will see it in the far left of the Formulas ribbon. You then simply type the function that you want to use, hit ‘Enter’ and then select the function in order for Excel to talk you through the various arguments that make up a function. Good luck!

Financial modeling: Using VLOOKUP in Excel 2003

In Excel 2003, and other older versions of Excel, you can insert functions by going to the Insert pulldown menu.  If I hit ‘Alt’, and then ‘I’, for the Insert pulldown menu. You’ll see that halfway down you find Function, and again I can hit ‘F’ to go right to Function, and then you get a menu that looks very similar to the one we just saw in Excel 2007.

Financial modeling: Building scenarios using IF statements

Here is a solution using nested IF statements.  This formula does work; however, it is very hard to audit.  Again, we have used yellow to represent inputs things that the user can change or update.  And blue, to represent outputs that should not be changed.

Financial modeling: Building scenarios using VLOOKUP

Here we have a solution that looks very similar at first glance but uses a VLOOKUP formula instead of using nested IF statements.  The VLOOKUP formula achieves the same result, but it is much easier to audit. The syntax for a VLOOKUP function is… First, the input cell that the table will look up.  In our case, we want VLOOKUP to reference cell ‘B11’, which is our leverage input assumption.  The second variable is the VLOOKUP table. In our instance, the table we want to reference is in cells ‘A15’ through to ‘C18’.  The third variable tells VLOOKUP which column to return as an output.  Our VLOOKUP table has three columns. The first column in a VLOOKUP table must match the input.  Since our input is leverage, the first column of our VLOOKUP table must also be leverage.  In this situation, we have the option of choosing column 2 or column 3. If we choose 3, VLOOKUP will return the coupon rate.  If we choose 2, VLOOKUP will choose the Moody’s credit rating.  The fourth and final component can be set at either true or false.  Our VLOOKUP is set to ‘True’ as we want VLOOKUP to see the table as a series of thresholds.  With a ‘True’ setting, VLOOKUP will return a value of 8.5% for any leverage input from 0 to 39.99%. If we set this final component to ‘False’, we are telling Excel only to return an exact match.  In this instance, if we type 40%, Excel will return 8.8%.  However, if we typed 39.99%, Excel will return a not-applicable.

Financial modeling: VLOOKUP exercise

Ok, why don’t you have a go.  Click on the link entitled ‘VLOOKUP Template with Instructions’.  Once you’ve finished, check your result with the attached ‘VLOOKUP Template Solution’.  Good luck!

Financial modeling: Better formulas conclusion

That concludes this session on Building Better Formulas. The three key messages from this session are: First, break down complex calculations into their components. Secondly, make your inputs or assumptions explicit rather than hard-coding inputs into cells and use color to separate your inputs from your outputs. And thirdly, remember that although functions provide useful formula shortcuts, ensure that you understand fully how functions work before using them. I encourage you to practice using Names, breaking down your calculations, and trying different functions such as VLOOKUP. Bye for now.

Slide 26: Thank you

Part 3 (video course)

Financial modeling: Session objectives

Welcome to Key Design Issues “Catering for uncertainty”. By the end of this session, you will be able to effectively use Excel tools, such as “goal seek” and “data tables”, and apply Excel scenario tools including “scenarios” and “solver” to your models. Let’s begin!

Financial modeling: Sensitivity analysis and scenario planning

Let’s first talk about sensitivity analysis and scenario planning. Both are used to measure the impact of future uncertainty on cash flows and profit. In the world of financial modeling, “sensitivity analysis” is the term normally used when you alter one material variable at a time. Scenario planning or analysis, on the other hand, focuses on running your model through alternative future scenarios where several variables are changed at the same time. In Excel, sensitivity analysis is best undertaken with “Data Tables” and “Goal Seek”. Scenario planning is best undertaken using the Excel “Scenarios” or “Solver” tools.

Financial modeling: Sensitivity analysis

Let’s start this session by looking at sensitivity analysis in more detail. The purpose of sensitivity analysis is to focus on the impact and consequence of material risk. Typically we look to sensitize a base case, with the real value from this analysis being derived from ‘downward’ sensitivities.  It is important to remember that sensitivity analysis is looking at the impact of changing a single material variable. Material variables in your financial model could be things like sales volumes, gross margins, labor costs, inflation, and so on.

Financial modeling: Using data tables

Data tables are an Excel tool that lends itself to sensitivity analysis. Data tables can be run based on changing one or two input variables. Unfortunately, data tables are a bit fiddly to set up. You may want to try building a data table as we go in order to confirm your understanding of how data tables are constructed. There are four critical steps that go into building a data table. First, we have to select the material variable we have to sensitize. We then create a list of values for this variable in a column or a row. The example you will see has the variable values in a column. Step two is to link the data table to the model output we want to sensitize. Step three is to highlight the data table in question. And finally, step four is to go to the data table menu and complete the input boxes. As you can see, there are several critical steps. In a moment we will go through a practice example. To access data tables in Excel 2007 and later versions, you will need to go to the Data Ribbon > Data Tools > What-if Analysis menu. To access data tables in other versions of Excel you go to the “Data” pull-down menu and then find “Tables”

Financial modeling: Single input data tables

Here is an example of a data table that we can work through together. The financial model in this example is relatively straight forward. We are looking to create a forecast income statement extract based on only three inputs. The three inputs are highlighted in yellow. They are: CurrentSales, SalesGrowthPercent, and GrossMargin. Let’s say that we believe the most material and unpredictable input in our financial model is sales growth. Our first step is to put values for this input into a column.  We have done this in column B, rows 13-22. The second step is to link the output cell we want to sensitize in our model to the table. We do this in cell C12. You will notice that the output cell needs to be placed one row up and one column to the right of our first input value. The formula in C12 reads “=C9”. This links the data table to our larger financial model. Step three in the data table construction process is to highlight the entire data table. In our example, we would highlight cells B12 through to C22. The final step involves selecting “Data Table” from the appropriate Excel menu. When you select “Data Table”, a window entitled “Table” will appear. As our input values are listed in a column, we will leave the “Row input cell” blank, and type “C4” into the “Column input cell”. When we click “OK” on the table window, the data table will then be populated. In effect, the data table is running our financial model ten times and outputting the forecast gross profit based on our various sales growth percent values.

Financial modeling: Double input data tables

Data tables can cope with a maximum of two input variables. The steps are very much the same as for single input variable tables. The key differences are, first, you must put one of your inputs in a column and the other in a row. In our example, we have left “sales growth percent” in a column and then added “gross margin” in a row. The row input must be placed one row above and one column to the right of the column input.  In our example, that means row twelve, columns C- F. Second, the output cell we want to sensitize must go in cell B12, the cell where the input column and input row meet. Third, we still need to highlight the entire table. However, this time around the table is larger, covering B12 through to F22. Finally, we need to call up the data table function and fill in both the row input cell and column input cell before hitting “OK”.

Financial modeling: Excel navigator exercise (demonstration)

Now you are seeing a version of Excel, and we are going to go through the same steps before you have a go in the next exercise. Step one is to highlight and identify the input you want to sensitize. We are going to start with the table on the far left, in columns B and C. We are sensitizing “sales growth” and have input values from 1% to 10%.  Step two is to link the output that we want to sensitize to the table, so we go to cell C12 and link it to the model. We can either input a plus sign, or use an equal sign, before typing the cell. Here, we are using the plus sign and then typing “C9”.  We have now linked the output to the data table. The next step, step three, is to highlight the whole table. Whereas step four, the final step, is to go to the data table option on your menu. In this version of Excel, it’s under “Data” then “Table”. From there, we complete the window that pops up. The row input cell will only be filled in if we had an input in a row. We only have an input in a column, which is “sales growth”, so we’re going to leave row input blank and complete column input. We are going to type in “C4” because that’s where we find C4 in our model, and then we can click “Ok”. Let’s do it again now with two inputs. On the right, you will see a table already set up. Again, in one column we have “sales growth percent” that we want to sensitize against, and then we have “gross margins” in a row. The first thing we have to do after this step is to link the output to our data table. We will go into cell E12 and input “+C9”.  Step three is to highlight the whole table, and step four is to go to Data > Tables. Then we need to complete both the row input and the column input this time. The row input is “gross margins”, which we find in cell C5. And the column input, as before, is “sales growth” which is in C4.  After that, we’re done.

Financial modeling: Excel navigator exercise

Now it’s your turn. Click on the link entitled “Data Tables Exercise” with instructions, and once you’ve had a go, check your attempt with the attached “Data Table Solution”. Good luck!

Financial modeling: Goal seek

The other Excel tool that allows users to undertake sensitivity analysis is “Goal Seek”.  Goal seek is limited to changing one input variable in your model at a time. To access “Goal Seek” in Excel 2007 and later versions, you’ll need to go to the Data ribbon > Data tools > What-if-analysis menu. To access “Goal Seek” in other versions of Excel, you go to the “Tools” pull-down menu, and then find “Goal Seek”.

Financial modeling: Goal seek exercise (demonstration)

Let’s look at “goal seek” in more detail. In this version of Excel, goal seek is found under the tools menu. When it’s selected, a window pops up with three boxes that need to be completed. The first thing is to identify the target cell. Because we would like to target “gross profit”, we change the cell from “C6” to “C9”. The next item to complete is to set a target value for C9 or “gross profit”. We’ll set the value to 10,000. The final item to complete is to identify the input cell we would like Excel to change in order to try and hit the target value. Here, we will choose “sales growth percent”, which is C4. When we click “OK”, a new window appears, and “goal seek” has found that it could change “sales growth percent” sufficiently in order to deliver our target.  It has delivered \$10,000 of gross profit by changing sales growth percent to 21%. If we want to keep the goal seek values, simply click “OK”. To return to the original values, click “Cancel”.

Financial modeling: Goal seek exercise

It’s your turn again. Click on the link entitled “Goal Seek Exercise” with instructions. Once you’ve had a go, check your attempt with the attached “Goal Seek Solution”. Enjoy!

Financial modeling: Scenario planning

As outlined earlier, scenario planning is multi-variable. That is to say, we want to change several input variables at the same time to see the impact on our model. Typically, when we talk about scenario planning we are looking at creating a base case, worst case, and best case set of scenarios. It is important when creating scenarios to ensure that scenarios have internally consistent assumptions and that scenarios reflect foreseeable eventualities rather than extreme highly unlikely situations.

Financial modeling: Scenarios

The scenario tool in Excel allows you to model, and change, up to 32 input variables at one time. An important limitation of the scenario tool is that it attaches to an Excel worksheet rather than a workbook. That means that you need to bring the result or output cells you want to impact onto the same worksheet as the inputs. To access “Scenarios”, in Excel 2007 and later versions, you’ll need to go to the Data ribbon > Data tools > What-if-analysis. To access “Scenarios” in other versions of Excel, go to the “Tools” pull-down menu, and then go to “Scenarios”.

Financial modeling: Building scenarios

Let’s now go through building a scenario step by step. We will use the same model that we have used to showcase the data table and goal seek tools. When you click on the “Scenario” tool, a “Scenario Manager” window will appear. To add a new scenario you simply click the add button on the scenario manager window. You are then prompted for a scenario name before you can input scenario values. Here’s a tip: if you name your cells, these names will appear in the scenario values window rather than cell references. In our example, you will see that scenario value “1” is “current sales”. If we hadn’t named this cell, you would simply see the cell reference “C3”.

You can add several scenarios. Our example has three. In order to see the impact of any one of the scenarios, go back to the “Scenario Manager” window and click the “show” button. If you want Excel to create a summary of all of your scenarios and their impact on your target output, you click the “Summary” button on the “Scenario Manager” window and Excel will produce a scenario summary automatically.

Financial modeling: Scenario exercise (demonstration)

Let’s look at scenarios in more detail. First of all, we need to access the scenario manager window. In this version of Excel, it is under “Tools”. Select “Scenarios”, and the “Scenario Manager” window will appear. You’ll see that there are already three scenarios in this workbook: a base case, a worst case, and a best case. To show any one of these scenarios, select the scenario you want to see and then click the “Show” button. To add a scenario simply click the “Add” button. Let’s do that now. The very first thing you’ll be prompted for is to give the new scenario a name. Let’s call this “Another Scenario”. Right below that, you can see that you can change the input cells. Let’s leave it right now as “C3:C5”. When we click “OK”, we are taken to the “Scenario Values” window. Here, we can change any of the input values that drive the model. Let’s change “sales growth percent” to 0.5. If we were to click “Add”, we could immediately add another scenario. By clicking “OK”, you will be taken back to the “Scenario Manager” window. If you want to see the new scenario, simply make sure it is selected, and then click “Show”. Finally, if you want to have a summary of all of your scenarios, click the “Summary” button. On the “Scenario Summary” window, you’ll have the opportunity to choose your results cells and also choose your report type. Either a “Scenarios Summary”, or for large amounts of data, a “Scenario PivotTable Report”.  Assuming we are happy with the scenario summary, we will click “OK” and Excel will automatically generate a summarized report.

Financial modeling: Scenario exercise

Now you have a go. Click on the link entitled “Scenario Exercise” with instructions. Once you’ve finished, check your results with the attached “Scenario Exercise Solution”. Enjoy!

Financial modeling Solver

For more complex problems, you may want to consider the Excel tool “Solver”. In Excel 2007 and later versions, “Solver” is accessed from the Data Ribbon > Analysis > Solver menu. If it is not on the data ribbon, you’ll need to go to the “Office” button, “Excel options”, and find “Add-Ins”. In other versions of Excel, Solver is accessed from the “Tools” menu. If it is not under the Tools menu, you’ll need to go to “Tools” and then “Add-Ins”.

Financial modeling: Changing multiple cells

Solver is a more advanced version of goal seek. You can change multiple input cells, whereas goal seek is limited to changing one input cell. Solver also allows you to set constraints on your inputs. Solver only allows you to target one output cell. However, you have the option to maximize, minimize, or set a target value for this output cell.   In our example, we are still targeting forecast “gross profit”. However, we are changing both gross margins and sales growth. We have constrained gross margins so that they must be between 15 and 65 percent. Likewise, sales growth must be between 0 and 10 percent. Once you have selected your target cell, your target goal, the input cells you want to change, and your input constraints, you click “Solve”.

Financial modeling: Solver results

A “Solver Result” window will appear. The “Solver” window allows you to keep your solver solution, restore your model to its original values, or save your solver solution as an additional scenario.

Financial modeling: Catering for uncertainty conclusion

That concludes this session on Catering for Uncertainty. The key messages from this session are: “Data Tables” and “Goal Seek” are useful tools when undertaking sensitivity analysis. If you need to undertake scenario planning, consider using Excel’s built-in “Solver” and “Scenario” tools. I encourage you to practice using Data Tables, Goal Seek, Scenarios, and Solver in order to cement your understanding of these critical Excel tools. Bye for now.

Slide 23: Thank you

Part 4 (video course)

Financial modeling: Session objectives

Welcome to Auditing a Model. By the end of this session you will be able to: use “Go To Special” to audit an Excel model; use the Excel auditing toolbar to trace precedents, dependents, and find errors; and finally, use “test data” to uncover model errors. Let’s begin.

Financial modeling: Checking your model’s integrity

Most Excel models, particularly large models, contain errors. How do you know all of your Excel model formulas point to the correct inputs? How do you know that all of your financial models input assumptions are reasonable and within sensible ranges? What are the chances that a formula in your financial model has been accidentally overtyped with a hard-coded number? There are several Excel audit tools that enable the model builder, or model reviewer, to test the model’s integrity. The three tools we will talk through in this session are: F5 “Go To Special”; Excel’s formula “Auditing Tools”, in particular Tracing Precedents and Dependents; and using “Test Data” to locate model errors.

Financial modeling: Model structure

Robust financial models clearly separate model inputs or assumptions from model outputs or formulas. Using this input/output structure makes financial models easier to audit. In particular, it makes it much easier to check if someone accidentally overtyped a formula. The quickest way to check if someone has overtyped a formula is with the “Go To Special” function. “Go To Special” can be accessed quickly using “F5”. Alternatively, you can use “Ctrl” + “G”. When you initially hit “F5”, the “Go To” window will appear. On this window, you need to select the “Special” button.

Financial modeling: Constants – Go To Special

The input, or assumptions area of your financial model, should contain all constants with few to no formulas. In order to quickly check if this is the case, select “Go To Special” and then select “Constants”. Excel will highlight everything that is not a formula.

Financial modeling: Formulas – Go To Special

The processing and output parts of your financial model should contain formulas only. We can ensure that this is the case by going to “Go To Special”, and then selecting “Formulas”. Let’s look at this in more detail.

Financial modeling: Go to special exercise (demonstration)

Here, we have a simple financial model. This financial model simply has a list of equities or stocks, the number of shares or stocks we hold in each of the companies, and their price at a given point in time. Yellow in this model is meant to highlight inputs. Numbers that aren’t in yellow should all be formulas. But how do we know someone hasn’t accidentally overtyped a formula? We can use “F5” or “Go To Special”.  When we use “F5”, we get the “Go To” window. You can see that it is currently blank, but we are more interested in the button on the bottom left that says “Special”. Let’s focus on that now. We want to show all cells that have formulas. By clicking on “Formulas” in the window, those cells with formulas will be highlighted. This will allow us to see if somebody has accidentally overtyped a formula. When we click “OK”, you will see that there are two cells that look like they have been overtyped. There is the “Research in Motion” holding that says 3,500 and has remained white. There is also the “Stantec” holding that says \$81,789 but again is not shaded blue. Let’s look at those cells to see if they are actually constants or formulas. We first click off anywhere on the Excel workbook and then go to the cell “E7” to begin with, to look at the contents of that cell. When we move the cursor over the contents you will see that it isn’t a formula but rather it is a hard-coded formula. Let’s look at the cell above to demonstrate. If we go to the cell above, you can see that it is in fact a formula linking C6 with D6. We can then copy that formula down. The number will not have changed, because the number is correct, it’s just that the formula was overtyped with the number. You may recall that the other cell that was highlighted by the F5 “Go To Special” tool, was the Stantec total value. Again if we look in the cell above, you’ll see that there is a formula reference: “E13*F13”, whereas when we look at the contents of “G14” we just get the number. When we correct that by copying the formula down, we again look at cell “G14” and now see a formula with various cell references. When we now select F5, “Go To Special” again, and ask Excel to find all the formulas, you’ll see that everything is now shaded in.

Financial modeling: Go to special exercise

Now it’s your turn. Click on the link entitled “Go To Special Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Go To Special Solution”. Good Luck!

Financial modeling: Formula auditing

Now let’s turn to Excel’s formula auditing tools. In Excel 2003 and prior versions, in order to gain access to these tools, you’ll need to find the “Formula Auditing” toolbar. This can be accessed on the “View” pull-down menu, and then finding the toolbar: “Formula Auditing”. In Excel 2007 and later versions, formula auditing is found on the “Formula” ribbon in the box entitled: “Formula Auditing”. You will see that there are several icons associated with formula auditing, we are going to focus on “Tracing Precedents” and “Tracing Dependents”.

Financial modeling: Trace precedents

Let’s start by looking at the “Tracing Precedents” tool. As you have probably already guessed, tracing precedents is all about identifying what precedes the cell calculation you are interested in. The first thing to ask is, what is the last thing to be calculated in this financial model? For example, in a financial forecast going out five years, financials in year five will typically be calculated after years one, two, three, and four.

Financial modeling: Using tracing precedents

Once you’ve identified the last calculation in your model, continue clicking the “Trace Precedents” button until all precedents have been identified. Excel identifies precedents with blue arrows. “Tracing Precedents” is a visual tool. You are looking for breaks in blue arrow symmetry, or for stray arrows. Once you have identified a stray arrow, you should select the cell where the stray arrow was pointing and check the cell’s contents.

Financial modeling: Trace dependents

“Tracing Dependents” works in a similar way to “Tracing Precedents”. However, you typically start with the first input or assumption in your model, rather than the last calculation in your model. Let’s move on to an example so we can explain these formula auditing tools in more detail.

Financial modeling: Tracing precedents exercise (demonstration)

At first glance this probably looks like the exact same Excel workbook we have been using; however, the numbers are different. In this case, we want to demonstrate the tracing precedents and dependent tools. Normally, it is better to start by tracing precedents rather than dependents.  With that in mind, the first question to ask is, what is the last calculation in the model? In this simple model, the last calculation will be the total of all the values of all the stocks in this portfolio: G19. We then select the cell and find the formula auditing tools. In this version of Excel, it is a toolbar. We then start clicking “Trace Precedents”. We click it as many times as we can until no more blue arrows are identified. You will see that there are problems in this model based on what we are looking for, which is stray arrows or lack of symmetry. To get rid of the arrows, simply press the eraser button called “Remove All Arrows”.  You can then use “Trace Precedents” again to show the blue arrows. Now we can go to a cell where a stray arrow is pointing. Let’s start by going to cell E8. First, we’ll eliminate some errors to show the screen more clearly. If you look at E8, you’ll see that it’s related to CIBC and that the formula in E8 shows “D8+C9”. We can quickly see that there is an error here. What we should have is “C8+D8”. We can now correct this and then let’s do trace precedents again. We again go to the last cell and click trace precedents. The original stray arrow is gone, but we still have another stray arrow. We will move to the cell, use the eraser button to get rid of the blue arrows to make it easier to read, and then edit cell G15.In the case of Loblaw, you can see that what someone has done with this model is taken the holding for Loblaw, but multiplied it by the price of Bombardier. So let’s correct that because it should have read “F15*E15”. Now let’s go back to the sum or total again, and again trace precedents. By looking at the arrows you will see that we now have full symmetry. However, one last thing to notice is that the sum fails to pick up the very first stock, which is Teck Cominco. Let’s erase and correct that so that we are summing the entire total. We will then trace precedents one last time. And when we do, you can see that we are rid of any stray arrows, that all of the table has been covered, and that this total: \$2,127,367 is the correct value of the portfolio.

Financial modeling: Tracing precedents exercise

Ok, why don’t you have a go. Click on the link entitled “Tracing Precedents Exercise” with instructions. Once you’ve finished, check your result with the attached “Tracing Precedents Solution”. Have fun!

Financial modeling: Test data

The final tool that you should consider when looking for errors in your financial models is “Test Data”. “Test Data” is a powerful auditing technique. “Test Data” is all about using simple test assumptions or input throughout your model to ensure that calculations work as expected. For example, if you were building an income statement forecast, set your sales or revenue growth assumptions for all forecast years to 0%. Forecast revenues for all years should now be identical. If they are not, you know there is a calculation error. Let’s move on to an example so that we can explore test data in more detail.

Financial modeling: Test data exercise (demonstration)

Here we have a simple model that aims at forecasting a part, the extract, of the income statement. At the top, you have the income statement assumptions that are going to drive the income statement forecast. The yellow represents anything that is an input. Numbers that aren’t in yellow should be formula driven. In order to read the model you will see that, for example, we are forecasting sales growth percent. If we look at 2010, you will see that we are expecting sales in 2010 to be 4% greater than they were in 2009. We have then forecasted a number of operating expenses from gross margins down to depreciation. In this case, we have forecasted all of them as a percent of sales or revenue. Now, let’s use test data to look for errors in the model. First, we will set sales growth percent in 2010 to 0. We will leave gross margins at 55%. We will also change distribution expense to 7.5%, leave marketing at 35%, but change the others to round them by setting research to 2% and depreciation to 3%. Therefore, operating profit should be 7.5% of revenues. Now, let’s take the assumptions for 2010 and copy them straight across the forecast so that all the assumptions read identically. When we look at operating income as a percent of sales you will see that it reads 7.5 across. As a result, if we were to look at the actual income statement below, it should also read exactly the same across the entire forecast period. By scrolling down to see the forecast, you will see that EBIT in 2010 is 6352. In 2011 it’s also 6352. Let’s leave 2012 because that’s where the error is, but you can see that in 2013 and 2014 the numbers are all identical. The problem, using test data, has been identified in 2012. If we move up and analyze individual line items, you’ll see that the difference between 2012 and all of the other years in the forecast resides in cell F26. When we look at the contents of cell F26, you will note that it is a hard-coded number. However, you can see that the cell on the left is a formula, and the cell on the right is a similar formula. So by using test data, we have identified where someone has overtyped a formula. By making the correction, we now have all of our EBITs across the forecast period read the same. This example is a good demonstration of how you can use test data to quickly find errors.

Financial modeling: Test data exercise

Now it’s your turn again. Click on the link entitled “Test Data Exercise” with instructions. Once you’ve had a go, check your attempt with the attached “Test Data Solution”. Good luck!

Financial modeling: Auditing a model conclusion

That concludes this session on Auditing a Model. The key message from this session is that most large financial models contain errors. Work on the assumption that your financial model contains errors and use the auditing tools to make your models more robust. We encourage you to practice using F5 “Go To Special”, the formula auditing tools, and test data in order to identify errors in your financial models. Bye for now.

Slide 19: Thank you