For this project you are to create an Excel template that calculates the NPV for a project and performs a sensitivity analysis of the NPV using the Excel Data Table. I am providing a pdf copy of a sample project as an example, but it is up to you to create the cell equations to make it work. You will be creating input numbers based on your student ID, so this is a game in that sense. The actual numbers are not as important as learning to use the EXCEL Data Table to perform a sensitivity analysis on an initial estimate of NPV.

Template Inputs

In the upper left section of the template you should list and provide values for all of the input data. Numbers that you input are shown in blue in the PDF file of instructions in the Class Projects Folder (cells B5 through B8, B10 through B18, J7 through J9, B41 through B45, and C40 through I40). All of the calculations should be done in EXCEL based on these inputs. You should set up the template so that no other number input is required in order for the template to calculate the projects NPV. If you use your calculator to do some calculations and just input the result in EXCEL, the data table will not work correctly. For instance, the annual revenue for year 2 is just year 1 revenue times (1 + g), where g is the fixed cell reference of the expected annual revenue growth rate. In my attached example, the growth rate is in cell B6, so the revenue multiplier is (1 + $B$6). If year 1 revenue is in cell B24 (which should just be a reference to the input cell, B5), then year 2 revenue will be =B24*(1+$B$6). Then just drag the cell equation across to calculate the revenues for the remaining years. Similarly, the annual depreciation expense is the depreciable base (cost + shipping + installation) times the depreciation percentage for that year.

Input Data

So that every persons template is unique, you should use a sequence of any four digits from your student I.D. with two zeroes added as the year 1 revenue, just make sure the first digit is non-zero. For instance, if your I.D. contains the sequence 2468, then the year 1 input would be $246,800. Similarly, make the processor cost a number within about 10 percent plus or minus of the year 1 revenue number. You may have to adjust the numbers slightly to get a reasonable positive NPV, say a number with one less digit than the year 1 revenue,(e.g. NPV = $15,338) thats not critical, but no two templates should be exact clones of one another. In general, do not replicate the set of numbers exactly that I used in the example for your project. Do not use numbers for year 1 revenue, etc. that are very similar to those of your classmates. You will receive little credit for templates that are essentially clones of those of another student.

Calculating NPV

Use the Excel NPV function to calculate NPV once you have a reasonable set of annual cash flows and initial investment. There is a quirk in the Excel NPV calculation that omits the initial investment, so you will have to include it manually to compute an NPV as we have defined it, i.e. NPV = Excel NPV -Initial Investment. Change the input numbers as necessary within reason to make the base case NPV positive. The scenario that you are assuming is that the base case NPV is positive, but you want to see how large errors in your assumptions about revenue growth and COGS% might be before NPV becomes negative.

Sensitivity Analysis

In many actual NPV calculations, the inputs may have varying degrees of forecast accuracy. To examine how well your results hold up if the estimates are in error, you may want to conduct a sensitivity analysis of the NPV to changes in the input. (Translation: to avoid getting fired when the project loses a lot of money, you may want to see how bad things could be before recommending the project to your boss.) In our case, we are examining the sensitivity of the base

case NPV to changes in the assumed revenue growth rate and the assumed COGS percentage. To consider varying these two inputs simultaneously, you should create a two dimensional data table like the one in the sample template copy provided. The data table should be set up as shown with the revenue percentages across the top and the COGS percentages down the side. Your base case numbers for revenue growth and COGS %, the ones that you used to calculate the base case NPV with, should be roughly in the middle of the range of numbers for revenue growth and COGS% in your data table. Insert the reference to the cell containing the NPV calculation (B36 in my example) in the upper left corner of the data table so that EXCEL knows how to do the calculations. (The numbers you must input before invoking the Data Table are shown in blue and the results of the data table calculations are shown in yellow.) To begin the data table process, block the area that has the NPV calculation reference and the numbers in blue as its left and top borders. After blocking the area, click on Data/What If Analysis/Data Table. When you invoke the data table and the dialog box appears, you should input the cell containing the current revenue growth percentage as the row input cell and the cell containing the current COGS percentage as the column input cell (cells B6 and B12 in my example). The resulting data table should contain at least some negative NPVs. If they are all positive, you may have to experiment with the revenue and COGS percentages used in the table (or reduce the year 1 revenue assumption to get a smaller NPV) to get some negative numbers, you can change these as required to make it work.

Data Tables

Your objective is to see how far from your base case the inputs might be and still have a positive NPV. Once you have some negative NPVs in your table, and have identified where the NPVs go from positive to negative, create at least one more data table that is more precise in identifying the change. For instance, if the NPV changes from positive to negative as the COGS percentage goes from 50% to 60 percent, create a second data table by expanding the range from 50% to 60%, maybe in increments of 1%, e.g. 51%, 52%, 53%, etc. Similarly, if NPV is negative when the growth rate is, e.g. 4%, but positive when growth is, e.g. 10%, expand this range in 1% increments.

Discussion

You should provide some comments in your report about the sensitivity analysis results and your confidence in your base case projects NPV. You should point out the combinations of inputs that produce negative NPVs. If extreme changes from your input assumptions are required to produce negative NPVs, you would be relatively confident in your results. If small changes result in negative NPVs you would be less confident. It is typically the case that the COGS % is relative stable; a ten percent change would be very significant. So if the data table shows that COGS must increase by more than ten percent from your original assumption before negative NPVs occur, you would feel very confident in your original calculation. On the other hand, if only a four percent increase in COGS would result in a negative NPV, you would be much less confident in your original estimate.

Format

You can submit everything in Excel for this project. Just include your name, the class and section, and the due date at the top of the template. You can also put your discussion in the Excel template. I need to see the Excel version so that I can see your cell equations, so dont convert it to pdf or word.

Caveat

Obviously, in a real project you would not be able to adjust the input numbers to get the answer you want, but since we are most interested in learning to conduct a sensitivity analysis and interpret its results, we are taking some unusual liberties with our inputs.