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.

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 on Blackboard (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 revenues for year 2 are just year 1 revenue times (1 + g), where g is the fixed cell reference of the expected annual 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.

Year 1 Revenue input: $108,000

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.

Calculating NPV

Use the Excel NPV function to calculate NPV once you have a reasonable set of annual cash flows and investment. There is a quirk in the Excel NPV calculation that omits the initial investment, so you will have to add it manually to compute an NPV as we have defined it, i.e. NPV = Excel NPV -Initial Investment.

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 NPV to 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. Insert the reference to the cell containing the NPV calculation (B36 in my example) in the upper left corner. (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. The resulting data table should contain at least some negative NPVs. 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

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 increments of 2%, e.g. 50%, 52%, 54%, 56%, 58%, 60%. Similarly, if NPV is negative when the growth rate is 15%, but positive when growth is 20%, expand this range in 1% increments.

Discussion

You should provide some comments in your report about the sensitivity analysis results and your confidence in the 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.

Show all Calculations.