As it gets closer to the holidays, I’m looking forward to some time off so I’m going to cover some quick Excel analysis tips I’ve run across during the past year during these last few weeks of the year. When I come back in January, I will be starting a series on Project Crescent in SQL Server 2012.
Solving for a goal in Excel
A common thing that we have been looking at as we prepare for a new budget year is evaluating which of several projects should we take on. One way to evaluate projects is to calculate the Net Present Value (NPV). This technique looks at a future stream of expenses and revenues and adjusts them for time to determine what that stream would represent in today’s dollars. To do this, you have to begin by calculating the expected expenditures and revenues over the next several years as shown in the following table for a simple 5 year project:

A 
B 
C 
D 
E 
F 
1 
Project: Widget Upgrade 

2  Year 1  Year 2  Year 3  Year 4  Year 5  
3  Expenses 
$275,000.00 
$77,250.00 
$79,567.50 
$81,954.53 
$84,413.16 
4  Revenue 
$120,000.00 
$126,000.00 
$132,300.00 
$138,915.00 
$145,860.75 
5  Net 
$155,000.00 
$48,750.00 
$52,732.50 
$56,960.48 
$61,447.59 
If you just sum each year’s net return, you would be saying that a dollar five years from now is the same as a dollar today. However, that simply is not true. Even if inflation were 0%, a future dollar is not worth a current dollar if for no other reason than you can invest a current dollar and have more than a dollar in 5 years (yes, even with the low interest rates banks are currently paying). In fact, unless you can earn more from your project than the bean counters in finance can earn by investing that dollar somewhere else, your project doesn’t stand a chance. How much more you must earn is a function of anticipated inflation and risk. So if you could invest your dollar and earn 5% a year, but expect inflation to be 2% and the project is moderately risky, you may need to earn at least 9 or 10%.
Most projects, such as the one in the above table have fairly high expenses in the first year or two but have moderate operating expenses in subsequent years, at least compared to the expected revenues. In this example, the first year of the project costs $155,000 more than it brings in. But in subsequent years, revenue exceeds expenses by about $50,000 or more each year.
Suppose you just added the year net returns together. You might think that by spending the $275,000 this year, you can cover all expenses and have an additional $64,890.56 in revenue. That sounds like a good deal right?
The problem is that merely summing the net returns does not take into account the time value of money. Let’s look at a simple example of the time value of money. Suppose you could invest the $275,000 and earn 5% per year for each of the next five years? In year 2 you would have $275,000 * 1.05 or $288,750. In other words, you would earn $13,750 on your investment. In year two you would have $288,750 * 1.05 or $303,187.50 earning another $14,437.50. In fact, over the five years you would earn $59,264.22
So how do we take into account the time value of money in our example? Well, in the first year, we have expenses of $275,000 but only revenue of $120,000 for a net loss of $155,000. In the second year, we have expenses of $77,250.00 with revenues of $126,000 for a net gain of $48,750. Can we simply take that $48,750 and credit it against the first year loss of $155,000? No, that $48,750 is not worth that much in today’s dollars. In fact, if you only consider the alternative investment rate of 5%, those second year dollars are only worth $48,750/1.05 or $46,428.57 today. Why? Because if you invest $46.428.57 today at 5%, you will have $48,750 a year from today.
Using similar logic, you would discount each of the future year net returns by 5% per year and then sum the results to get $39,016.19. So you would still make about 39 thousand dollars more by doing this project. Anything that returns a positive amount is potentially a good project.
NPV 
$39,016.19 
=B5+C5/B8+D5/B8^2+E5/B8^3+F5/B8^4  
% 
1.05 
But wait a minute, we did not yet account for inflation or risk. Suppose you said that you really need to discount future year net proceeds by 10% to account not only for alternative investment, but also for inflation and risk. Performing these calculations and summing the results still yields us a positive $17,663.54.
NPV 
$17,663.54 
=B5+C5/B8+D5/B8^2+E5/B8^3+F5/B8^4  
% 
1.1 
So at what interest rate would we become indifferent to this project? By definition, that is the interest rate that make NPV equal to $0.00. We could continue to guess at different interest rates, but there are some easier ways in Excel.
The first way is to use a feature called Goal Seek in Excel. Goal Seek is great when you are trying to force a specific cell’s calculated value by changing one and only one other cell. In this case, I set up the equation to calculate NPV in cell B7 and the discount interest rate in B8. Then I opened the Tools menu in Excel 2011 (for the Mac) and clicked on Goal Seek. This opens the dialog shown below:
(Note: if you are using Excel 2010 for the PC, you can find Goal Seek by clicking on the Data –> Data Tools –> WhatIf Analysis.)
First you have to set the cell that you want to force to a specific value. This is the cell that holds the NPV calculation, B7. In the second text box, I want to force the set cell to have a value of 0.00. To do this, I want to change the contents of cell B8.
When you click OK, Excel does the hard work of trying different values in cell B8 until it gets a value of 0.00 in B7. It does this by performing a series of iterations and projections to quickly narrow down to a correct result. In this example, it gets a value of 1.148540228. In other words, this project has a rate of return of just under 15%. Another way to look at this result is that if the cost of money (opportunity cost, inflation, and risk) were greater than 15%, you would not do this project.
Of course, in this case Excel has another way to calculate this rate, known as the Internal Rate of Return (IRR) for the project by using the IRR() function found in the financial set of functions.
=IRR(B5:F5,1)
The IRR function has two parameters, the first is the range of cells containing the net returns for a project. The second parameter is an initial guess at the rate of return which I usually start at 1.00 or 0%. Upon pressing the Enter or Tab key, this function performs the same goal seek analysis to determine the rate of return that results in a value of zero when you sum the values in the referenced set of cells.
Note however that while the IRR() function may be a lot easier to use than setting up a Goal Seek, this function is only good for a specific financial calculation. On the other hand, Goal Seek can be used for any set of calculations in which the final result must converge to a specific value.
What if your problem has more than one parameter that can vary? There is another technique within WhatIf analysis using an addin tool called Solver which I will cover next time.