1/23/2017 1 Chapter 1B: Instructions for Solving Examples from First Lecture in Excel This document contains step-by-step instructions on how to perform the Excel analysis demonstrated in the first lecture. It is intended to be consulted only after listening to the first lecture. Students needing help with Excel are encouraged to work through these problems with the aid of this tutorial starting with the template sheets provided in the Excel workbook “C01 Intro to Mgmt Sci and Data Tables”. Outline 1.0 The function wizard & YouTube 1.1 Joe’s Warranty problem 1.2 Life insurance problem with fixed discount rate of r = 4% 1.3 Sensitivity analysis on discount rate with a 1-way data table 1.4 Breakeven analysis and goal seek 1.5 Loan payoff amount example – 2D Data table 1.0 The function wizard & YouTube Excel’s function wizard is very useful; get to know it. It is the little bold “fx” that appears just to the left of the place where you would type to add content to the Excel cell that has been highlighted by the cursor. Clicking it brings up the “Insert Function” dialogue box. Excel has so many built-in functions that they are organized into categories. Choose the appropriate category (or choose all functions) and then browse for the function you want to use. Each is accompanied by (brief) instructions on how to use it. If more assistance is needed, type “Excel tutorial ___” into YouTube’s search feature, filling in the ____ with whatever Excel function, feature, or trick you want to learn. The quality of content on the internet is always uneven, but many of the Excel tutorials are excellent. 1.1 Joe’s Warranty problem Problem statement: Joe has just bought a car. The seller is offering a five-year, extended warranty for $1,000. Joe estimates the covered repairs can be expected to cost $100 in the first year and increase linearly to $500 in the fifth year. The amounts are small enough that Joe can be modeled as a risk neutral decision maker. The warranty requires Joe to maintain careful maintenance records; he figures the hassle of doing so is worth $50/year after the first year. He would pay for the repairs or warranty with money that would otherwise be invested in CD’s yielding 6% per year; he’s in the 33% marginal tax bracket (with federal, state, and local taxes). Should he buy the warranty? The key function is =NPV( r, vector ), which takes a vector of cash flows occurring at the end of period 1, period 2, …, period n, where n is the number of elements in the vector, and returns their total discounted present value, discounting at a per period interest rate of r. The first payment would be made today, not in 12 months. E.g., if Joe buys the warranty, he’d have to write a check for $1,000 today, not a year from now. Hence,1/23/2017 2 entering in cell B11 the formula =NPV( A3, B5:B10 ) will give the wrong answer, because the value in cell B5 is the cost now, not in 12 months. There are two options. One is to move the cash flow in cell B5 outside of the discounting, writing =B5 + NPV( A3, B6:B10 ). The other is to discount the entire vector by one period too many, but then “undiscount” by inflating that quantity back up by the discount rate, i.e., =(1 + A3 ) * NPV( A3, B5:B10 ). We could then type the same sort of formula into cell C11, but retyping formulae is tedious and greatly increases the risk of errors. We would rather just copy the formula over. If we literally copy the formula =B5 + NPV( A3, B6:B10 ) from cell B11 and paste it into cell C11 the formula becomes =C5 + NPV( B3, C6:C10 ). I.e., Excel is “smart enough” to understand that we want the formula copied into column C to apply to the data given in column C, so it automatically updates/shifts the column addresses from B to C. That is called relative addressing. Relative addressing is wonderful, but in this case Excel was “too smart” because it also adjusted the column reference for the first argument from A3 to B3. That asks Excel to use the character string “Discount Rate” (the contents of cell B3) as our discount rate which makes no sense; the discount rate needs to be a number, not a character string. We want to “turn off” Excel’s relative addressing for that argument and instead use absolute addressing. This is accomplished by inserting a ‘$’ symbol in front of the ‘A’ in the reference to cell A3. That is, if we typed into cell B11 the formula =B5 + NPV( $A3, B6:B10 ) [note the addition of the ‘$’], then when we copy cell B11 into cell C11 the formula becomes =C5 + NPV( $A3, C6:C10 ), which is the correct formula. 1.2 Life insurance problem with fixed discount rate r = 4% Problem statement: You want to buy $250,000 in term life insurance coverage for the next 20 years. You can either renew the policy annually at steadily increasing cost or you can lock in the premium for 10 years at a time, by buying a 10-year policy now and another in 10 years. The annual policy is cheaper initially but costs more in later years, so which is preferred depends on how strongly you prefer deferring costs into the future. That “time rate of preference” is captured by a “discount rate”. Again the key function is =NPV( r, vector ). You’ll want to create two cells, one that contains the PV of the cost with the annual policies, and another with the PV of the cost with the 10-year policies. In the solved Excel sheet I posted (“Life Ins.-Solved”), I inserted five blank rows between the header of the SS (cells A1:A3) and the data (originally in cells A5:C25) because I like to put the most important cells in the upper left of the worksheet, where they are easiest to see. Ideally all of the cells used will be visible in a single screen, but that will often not be possible. So an important choice in “spreadsheet design” is what you make visible in that first screen. I want to have a portion of the data visible, but I don’t care if the ends of the long columns of data disappear off the bottom of the screen. The user (and reader) can easily understand from seeing just the top of that data display what he or she would find by scrolling further down. Discounting requires a discount rate, so I typed 4% in cell A5, and labeled it with a text annotation in cell B5. (Labeling is terribly important if anyone is ever going to1/23/2017 3 reuse the spreadsheet – or if you want to get credit