To demonstrate how Goal Seek can be used, let's look at a real-world example together. An airline company you're working for is considering expanding their portfolio by adding Atlanta as a new destination. This would require an initial investment of $250,000 to secure capacity at the Atlanta airport. Your market research team estimates that the additional raul would generate $100,000 and incremental profit in Year 1, and an additional 50,000 in profit during Years 2 through 5. In order to determine whether or not this is a worthwhile investment, let's look into net present value or NPV and internal rate of return or IRR. NPV accounts for the time value of money and calculates how much the returns are worth in today's terms if you were to receive that in a future time period. In simple terms, NPV is how much a dollar given to you in five years is worth today. NPV also includes any costs associated with the investment. IRR on the other hand, looks at the same problem from a different angle. IRR seeks to find the interest rate that sets the NPV equation to zero. In summary, NPV can be viewed as the profitability of a project in absolute terms, where IRR is the profitability of a project in relative terms or percentage. Now that we understand what we're trying to find, let's get back to the problem at hand. Based on the financial outlays table, we can calculate NPV and the IRR in two ways. First we'll use the NPV formula before showing how the Goal Seek can help us arrive at the same answer but much faster and more precisely. First, we'll set up a formula for NPV in Excel, which is dependent on the value of our discount rate. Let's type in the formula. As a reminder from Course 2, the syntax for NPV is the discount rate followed by the cash flow starting at Year 1, which equals NPV, open parenthesis, our discount rate H28 comma. Then our outlays range so B27 through rows B31 plus B26. We subtracted the Year 0 outlay or the initial investment after the NPV formula to take the investment into account without discounting it as it is assumed the cash outlay is made today. As you can see here, if the discount rate is 10 percent, the NPV is $472,168.75. To calculate what discount rate will force NPV to equal zero, we also have a few options. The brute force way is to use trial and error with the input value and see when you're getting close to the desired answer. We can try it with a few values; 25 percent, 35 percent, and 55 percent. Notice as the discount rate becomes larger, NPV is getting closer and closer to zero. So we can be sure that the IRR is close to 56 percent based on what we've seen. However, if you want to save the time and skip straight to the results, Goal Seek is a perfect tool. In order to quickly determine what the internal rate of return is or in other words, what discount rate will make the NPV equation zero, let's navigate to the data tab. Find What If Analysis and select Goal Seek from the drop down. In the pop-up window, you will see that there are three input parameters; set cell, to value, and by changing cell. Set cell is where we specify which cell is the outcome we're trying to monitor. In our case, it is the NPV. In the two value field, type in the value of the desired outcome which in our case we want the NPV to be equal to zero. Finally, the by changing cell field is where we can specify which variable we would like to manipulate to get the result we want, which in this case translates to the IRR. After all three parameters are filled out, let's click on Okay. Excel will then automatically cycle through the different values of an IRR to make the NPV equal to zero. The final answer Goal Seek came up with is 56.72 percent. In other words, if the discount rate is 56.72 percent, the net present value or NPV of the investment will be equal to zero. Now, let's double check our answers. Luckily for us, Excel already has a built-in formula specifically for calculating IRR as long as you have the financial outlay of the investment. So H30 let's type in the IRR formula. Equals IRR open parenthesis then B26 through cells B31. This refers to the same financial outlay table that we used earlier to build out our Goal Seek tool. As you can see, the formula returns 56.72 percent which is exactly the same result that the Goal Seek tool has arrived at. Now you can see Goal Seek allows you to experiment with an input variable to get a desired answer. We use this all the time to help define our scenarios. For example, Goal Seek is a perfect tool to determine what the price of electricity needs to be to make a natural gas power plant profitable at different gas prices. To summarize, Goal Seek is located under the data tab under What If Analysis and then you'll see Goal Seek in the drop down. In order to use Goal Seek, there needs to be a formula linking the cell you are changing to the end results. As long as you have established an Excel formula or a mathematical equation to define how the variable and the result are related, Goal Seek can help you take the guesswork out of your calculation by quickly finding what variable value you need to achieve the end results results you want. Now, it's your turn to practice.
