[MUSIC] 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 route would generate $100,000 in incremental profit in year one. And the additional $50,000 in profit during years two through five. 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 the future time period. In simple terms, NPV is how much a dollar given to you in 5 years is worth today. NPV also includes any cost associated with the investment. IRR looks at the same problem from a different angle. IRR seeks to find what the interest rate what equal to reach an NPV of 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 percentages. Now that we understand what we're trying to find, let's get back to the problem at hand. Based on the financial outlaid table, we can calculate NPV and the IRR in two ways. First, we'll use the NPV formula before showing how Goal Seek can help us arrive at the same answer 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 two, the syntax for NPV is the discount rate followed by the cash flows starting at year one which equals NPV(H27,B26:B31). As you can see here, if the discount rate is 10%, the NPV is approximately $429,000. To calculate what discount rate will force the 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 25%. 35%, 55%. And as you can see as a discount rate becomes larger, the NPV is getting closer to zero. So we know that the IRR must be around 55%. 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 the discount rate is to make the NPV equals zero. Let's go the Data tab > What-If Analysis > Goal Seek. 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 To Value field, type in the value of the desired outcome, which in our case, we want the NPV to be 0. Finally, by changing cell field is where we can specify which variable we would like to manipulate to get the result we want. Here is the IRR. After all three fields are filled out let's click on OK. Excel will then automatically cycle through different values of an IRR to make the NPV equal to 0. The final answer Goal Seek came up with is 57%. In other words, if the discount rate is 57% the NPV of the investment will be 0. 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. In cell H30 let's type in the IRR formula =IRR(B26: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 57%, 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 to and 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 economic at different gas prices. To summarize, Goal Seek is located under Data > What-if Analysis > Goal Seek. 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 math equation to define how the variable and the end result are related. Goal Seek can help you take the guess work out of your calculation by quickly finding what variable value you need to achieve the end result you want. Now it's your turn to practice. [MUSIC]
