What-if analysis in Excel
1. What-if analysis in Excel
Hello! In this screencast, we will review how to perform what-if analysis in Excel. Excel is one of the best tools for creating mathematical models, and what-if analysis is no exception. What-if analysis is essentially creating formulas in a way that allows questions to be explored. Here the formula shows that net sales equal our gross sales minus discounts. If we wanted to know what would happen to total sales if they increased by 20% and discounts increased by 5%, we could simply insert a new cell for each variable and then multiply the growth rates in our net sales formula. Remember to add one when multiplying a growth rate. But what if we had a specific goal in mind? For example, what if our goal was 20 million in sales? What would the sales growth rate need to be? The Goal Seek tool can help us out. First, we identify the cell that holds our goal; in this case, it would be our projected sales, then we set it to the value we want that cell to be: 20 million. Then we identify the input that should be changed to meet the goal, which is our sales growth. And voila! We would need a growth rate of about 42%. The Scenario Manager is also a great tool when analyzing multiple scenarios and multiple variable changes. We can add various scenarios here, naming them and changing values for the selected cells. When we click show, it puts the values into the cells, so this allows us to easily flip through scenarios. We can also export a summary of our analysis into a sensitivity table. Make sure to reference the correct results cell, then click OK. The summary essentially creates a sensitivity table with all the various inputs and outputs. This is a great way to review all the possible outcomes. The Data Table is another what-if analysis tool that performs sensitivity analysis. This one is very powerful but a bit tricky to set up correctly, so pay attention closely to these next steps. This can be done with one or two variables, so let’s review both methods. First, let's put our input values for sales growth in a column. Then, we need to reference the cell with our formula here in this exact position: one cell above and one cell to the right of our first value. Now we can run the analysis by highlighting the table range and clicking the data table under the what-if analysis tools. Now look, we have two options of inputs here: row input cell or column input cell. Since our values are listed in a column, we use the column input cell. Remember that: column to column. Click OK. And voila! We have a whole range of results. Finally, let's run this again but with two variables. We need to delete the Data Table range because it will not allow us to edit anything. We can keep our column values here, and place our inputs for the discount growth rates in the rows. We need to reference the formula in the corner between the row and column. We’ll highlight the range for our table, and then run the analysis. Remember that our row changes the discount growth rate, and our column is changing the sales growth rate. Then we click OK. Great! Now we have our analysis, but we need to add some formatting to make this easier to read. First, we’ll format the numbers, and then we can add a color scale. There, much better! Now that you’ve seen it; it's your turn to try.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.