1. What-if analysis in financial models
Hello! In this screencast, we will review how to perform what-if analysis in a financial model.
Financial models are perfect for what-if analysis because they are set up to answer questions given certain assumptions. Everything in a financial model is dynamic, which means it can change if certain inputs change.
For example, we can easily analyze how our net income changes when we simply change the units sold assumption from two to four.
There are three specialized tools loaded into your default version of Excel that are extremely useful for what-if analysis. These can be found under the data ribbon in what-if analysis.
The first is the scenario manager. The scenario manager keeps track of scenarios so that we can easily compare them and show them in our model. Click add, type in a name. We'll call this Expected Sales. Then, we select the cells we want to change. To select multiple cells, separate them with a comma.
Now, we can set the values of each cell. Let’s keep these the same for now, as this will be our default scenario.
Let’s do this again, but for a high sales scenario where we double the previous numbers.
Now that we have two scenarios, we can easily switch between them by clicking Show. Pretty cool!
But that’s not all! We can create a summary table of these results by clicking on summary. The result cell is the outcome we're interested in analyzing. Let’s choose our net income for year four. This table displays the results side-by-side for each scenario, starting with the current values in our worksheet. Then we see the inputs for each scenario, and their results on the bottom.
The next tool is Goal Seek. This works like a reverse what-if analysis because instead of changing inputs to get an output, we set an output to get an input.
Let’s say we want to know how much sales growth we need to have exactly one million dollars in net income by the end of year four. We would open up goal seek, select the cell we want to set, then the value to one million, and finally, the input cell we want to change, sales growth. How cool to see that in action! Goal Seek continues to change the input until we get the answer we’re looking for.
Finally, the data table is a powerful tool for sensitivity analysis because it analyzes a range of inputs on the resulting cell. This is a bit complex, so make sure to pay attention.
First, let’s run this with only one input. We’ll analyze the change in starting sales and its impact on net income for year four.
In a column, we’ll list a range of values from one to five. Then, we’ll reference the cell for net income. Now, we’ll highlight this table and run the data table. We’ll get two options: row input or column input. The easiest way to remember this is that our values are in a column, so they are our column input. These numbers represent our starting sales, so we’ll select that as our column input cell. This tells Excel to replace our starting sales with the values in our column. Nice! Now we have a range of inputs for starting sales and we can see their impact on the net income in an easy-to-read table.
Now, let’s do this again with two variables. Let’s say we also want to see how changes in sales growth would impact this. The setup here is a little different, so first, we’ll list a range of values from one to five. Then, we’ll list different growth rates, from 50% to 250%. Now, we’ll reference our net income in the left corner where these two meet, highlight the table range, and run the data table tool.
Once again, we get our row input and column input options. Our row controls the sales growth rate, so we’ll reference that for our row input, and our column controls our starting cell, so we’ll reference that for our column input.
Nice! Let’s add a color scale from conditional formatting to make this a bit easier to read. That looks a lot nicer.
Now, we have a sensitivity table that displays the net income for the various inputs. For example, we can see what happens if our sales growth is 100% annually, and we start at one elephant sold in year one. We can follow this column down to see all the results for our starting sales. Likewise, we can follow a single row and see what our net income could grow to.
Now you've seen it, its your turn to give it a try.
2. Let's practice!