How to use Excel’s Scenario Manager analysis tool

Last month, we used Goal Seek to determine the most reasonable mortgage payment within a specific budget. This month, we continue with the mortgage example using Scenario Manager. We’ll generate a summary report that lets us compare all the possible mortgage terms at the same time. Then, we’ll change the example a bit by calculating the total cost of the mortgage and compare those.

I’m using Excel 2013 and Windows 7. For your convenience, you can download the example .xlsx or .xls file (which also contains last month’s examples). Excel 2003 doesn’t support this feature, but you can save the .xlsx file as an .xls file and still view the results.

Quick review

All three analysis tools are what-if tools. In other words, you can see the impact of changing input values without changing the actual data. Last month’s article showed you how to use Goal Seek, a what-if analysis tool that tells you how input values must change to achieve a specific goal. The Scenario Manager lets you substitute input values for multiple cells (up to 32). In this way, you can view the results of different input values (or scenarios) at the same time.

Compare mortgage terms

We’ll use the simple mortgage calculator shown in Figure A.

Figure A

We’ll use this simple mortgage calculator to demonstrate Excel’s Scenario Manager.

Refer to Table A to build this sheet if you like, or download the example file. With the current input values of 6%, 180 months, and a principal of $200,000, a monthly payment is $1,687.71, and you’ll repay a total of $303,788.46.

Table A

Simple mortgage calculator.

Now, let’s use the Scenario Manager to compare several sets of interest and term to see how the monthly payment changes:

1. Select B4:C4 (the input cells).
2. Click the Data tab.
3. In the Data Tools group, click the What-if Analysis drop-down and choose Scenario Manager (Figure B). Figure B
   
4. Click Add and give the scenario a name, such as BestCase (Figure C), and click OK.
   Figure C
   
5. Next, enter the best terms you could possible get for the loan, .01 and 120 (Figure D). When you want to see only one set of values, click OK. In this case, we want to add more input values, so click Add.
   Figure D
   
6. Repeat steps 4 and 5 to create four more scenarios (Figure E): WorstCase: .06; 360 AlmostWorstCase: .045; 360 GettingThere: .03; 240 CouldSwingIt: .03; 120
   Figure E
   
7. After adding all the scenarios you want to consider, click Summary.
8. In the resulting dialog, make sure the Result Cell control displays E4, the cell that contains the mortgage function, and click OK (Figure F).
   Figure F
   

Let’s take a minute to review the names and input values; they’re a bit subjective and, in this case, could definitely be updated. It depends on your perspective. For instance, your BestCase scenario might offer the lowest interest rate for the fewest months, but the payment isn’t the lowest. If the names aren’t meaningful, reconsider your goals: are you looking for the lowest monthly payment or to pay back the least amount of money over the life of the loan? In the end, the names aren’t all that important.

Compare total payout

CouldWingIt demands the largest monthly payment, but will you pay less in the long run? We can use the Scenario Manager again to find out:

1. Select B4:C4, click Data, and then choose Scenario Manager from the What-If Analysis drop-down in the Data Tools group.
2. In the resulting dialog, don’t change anything. You’ve already set it all up. Instead, click Summary.
3. In the final dialog, change the Result Cell to F4 (the payback formula), and click OK (Figure H).
   Figure H
   

At this point, you have all the possibilities in front of you and you can make the best decision for you, which might not be the best or the worst case at all, but somewhere in between. The BestCase scenario has a large monthly payment, but you’ll pay back the least amount of money. The WorstCase scenario’s pay back value is more than double! AlmostWorstCase has the lowest payment but a large payback. GettingThere seems to offer a low payment and one of the lower paybacks.

Most borrowers will choose the highest affordable monthly payment to pay back the least amount of money. Scenario Manager can help you determine the best fit for your budget if you have a lender that’s willing to negotiate terms.

Quick comparison

As I mentioned, Excel offers three what-if analysis tools. Goal Seek takes a result and projects an outcome to determine the possible input values that reduce that outcome. Both the Scenario Manager and Data Tables use sets of input values to project a possible result.

There are often many possibilities to consider. For instance, even with our simple example, you could add the principal amount as a third input value. Perhaps grandma gives you a large deposit–you might want to determine how that figures in, especially if you’d like to put part of that money into an emergency fund instead of using it all as a deposit. The possibilities are almost endless, and that’s why Scenario Manager is such a great tool to have in your what-if toolbox!

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

For a subsequent article on this topic, please read: How to use Excel’s Data Table analysis tool.