Microsoft Excel is an excellent planning tool for What-If Analysis, but did you know it has a Scenario Manager feature? Here’s a real life example: you are considering purchasing a new car and you are trying to decide how much of a down payment to provide. The dealer is offering different interest rates based on the length of the loan.
Here are the four scenarios you are considering:
The dealer is offering 1.9% for 60 months and 2.9% for 72 months.
Since we will be changing the Down Payment, Annual Interest Rate and Number of Months we will want to give those cells a specific reference. In my example, the Down Payment is in Cell B4. I want to name that cell “Down Payment” so in top of the screen where you see the cell number displayed, all you have to do is type “Down Payment” in that area and now B4 can be referred as B4 or Down Payment. Many software programs do not like a space being used in a reference so using the underscore character “_” is often used to connect multiple words. Now we need to do the same with B6, Annual Interest Rate, B7 Number of Months B9 Monthly Payment, B10 Total Paid and B11 Total Interest.
Next, we go to the Data Tab and select What-If Analysis. We enter a Scenario Name: “5K, 60” and in the Changing Cells we hold down the CTRL Key and select the cells we will change in each scenario” B4, B6, B7. We then add the other three scenarios giving them each a unique name and enter the values we want to change, the Down Payment, Annual Interest Rate and Number of Months.
When that is done, we click on the Summary button and results tab will be automatically created in Excel all you need to do is select the cells that hold our results: B9:B11. Awesome, right?! Spreadsheets are so great!
If you did everything correctly you will have a new tab named Scenario Summary and it will look like this:
You can have as many scenarios as you please! This is also true for the number of cells you may change and the number of result cells. The spreadsheet could contain hundreds or more rows of data. That means you can have very complicated formulas, but the What-If Analysis will create a separate tab that just shows the cells that you change and the results.
Of course you can apply this example to your business! If you like this idea and need assistance in your implementation, please contact me at michael.ericksen@wacptrs.com.
Leave a Reply