[MUSIC] Hello, my name is Corey Gallon and I help our clients solve complex problems in finance by developing tailored analytical solutions. I'm excited to introduce you to the analytical concepts we will explore this week and provide an overview of the use cases for each. When we are trying to solve problems analytically, many times there is not one right answer to these problems. In fact, it is good practice to develop a sense of how our analytical solutions vary as we change our assumptions and inputs to our analyses. This week we will study formalized approaches to changing assumptions and inputs to better understand possible outcomes of our analyses. We think about these cases of questions broadly as what if questions. For example, what happens to my model output if I change the inputs to my model across a range of values? We'll look at three generally different approaches to answering these, what if, questions, scenario analysis, sensitivity analysis, and simulation. Scenario analysis involves the definition of a specific state of the world, and the related impact on variables being analyzed, to capture and quantify outcomes. Scenario analysis helps us formalize one or more possible answers to questions about the future. For example, what would the Net Present Value of a particular capital project be if interest rates were to increase and the project's lifetime was decreased by 5 years? We can think about scenario analysis as a story driven what if analysis across one or more variables. Sensitivity analysis involves changing the values of an input to a model or formula incrementally and measuring the related change in outcomes. For example, what would the Net Present Value of a particular capital project be as interest rates move from 1% to 15%? We can think about sensitivity analysis as numerical values driven what if analysis on one or more variables. Simulation involves iterating through possible values of one or more variables of a model, in order to capture possible and ultimately likely outcomes. Simulation can be deterministic, in that we iterate over all possible values of a variable, or non deterministic or random, in that we iterate over values based on some assumption of the probability of a variable taking on these values. For example given a 40% probability of a $5 increase in cost per unit in the next 12 months, what is expected profit per unit? We can think about simulation as numerical values-driven what if analysis on one or more variables across a range of probable values. We'll speak a little more about each of these what if methods as we work through the example problems this week. Excel provides us a number of useful tools to perform such what if analysis when we are building spreadsheet models. This week, we will develop an understanding of using goal seek to back-into outcomes in our models, using solver to mathematically optimize outcomes of our models, using data tables to perform sensitivity analysis of our models. Building scenarios for scenario analysis using Excel's Scenario Manager feature. And lastly, implementing simulation in Excel to better understand probable outcomes as reflected in our models. We will explore each of these features of Excel in depth as the week progresses. But for now, let's walk through a quick overview of these powerful tools for what if analysis. Goal seek allows us to answer what if questions, by starting first with the outcome of the analysis. We tell Excel the value we want to see as the output of a model and ask it to change one input value in our model repeatedly in order to achieve the stated output. For example, in a capital budgeting problem, if we wanted to know the discount rate we should use in order to achieve a specific Net Present Value, we can use Goal Seek to determine the discount rate to use in the NPV calculation. Solver is a powerful tool to help us mathematically optimize outcomes in our models to support scenario analysis such as best case or worst case analyses. Mathematical optimization can be thought of as determining the best or worst outcome subject to defined constraints, from a group of alternative possible outcomes. For example, we may ask, what is the combination of capital projects, that is, best case, I should pursue in order to maximize the Net Present Value of my capital allocation? This is a simplified example and the built in Solver tool provided with Excel is very powerful. But we will barely scratch the surface for use cases for this potent optimization engine. We encourage you to explore Solver and it's many uses independently, to enhance your own spreadsheet analyses. Excel provides us two convenient and related tools for sensitivity analysis in it's data tables feature. Data tables provide a useful tool to change inputs incrementally to our models, either one or two at a time and determine the impact of these changes on modeled outcomes. We could use a one way data table to answer the question, how sensitive is a capital project's Net Present Value to changes in the required return, that is discount rate, between 1 and 10%. Excel also provides two-way data tables, which allows us to change two variables in our model and observe the incremental impact of these changes on modeled outcomes. Scenario analysis calls for defining specific input to a model which relate to a specific story driven state of the world. Excel provides a useful tool to capture these different parameterizations of our model, to define scenarios in it's Scenario Manager feature. Scenario Manager allows us to save inputs to our model and therefore related outputs as named scenarios, to facilitate asking these what if questions in our analysis. We may use Scenario Manager, for example, to capture outputs of multiple scenarios which answer the question, what is the Net Present Value under my best, moderate and worst case views of the prospects for a given capital project? Excel provides a number of functions which a system developing simulations of probable outcomes in our analysis. We will explore simulation using the Monte Carlo method, whereby we repeatedly allow variables in our analysis to take on possible values in accordance with probability distribution, in order to determine possible outcomes of our analysis. We will discuss Monte Carlo simulation more as we work through the related example this week. And we encourage participants to study this useful simulation method independently to gain a full appreciation for its applications. When conducting data analysis, it is important to understand the impact of changes in model variables to the outcomes and conclusions of our analysis. We ask what if questions in a variety of ways, though we'll focus on questions pertaining to scenario analysis, sensitivity analysis and simulation in particular. We'll explore answering these questions using functionality Excel provides in it's Goal Seek, Solver, Data Tables, Scenario Manager, and Simulation features. You'll need the Excel Analysis ToolPak installed and enabled, as for prior exercises in this specialization. We'll provide a refresher on how to install and enable this add-on should you need it. As with week's previous, we will work through example problems with you and then provide you with problems to work through on your own to confirm your understanding. We encourage you to prepare for the culminating exercise at the end of each module and use these exercises as an indication of your comprehension of the concepts presented therein. Now let's dive in and explore answering what if questions in our analyses, enjoy. [MUSIC]
