EduPristine – A leading training provider in. EduPristine was founded in 2008 by four industry professionals from MNCs like Standard Chartered, Goldman Sachs, S & P and Read More.
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:
- Select B4:C4 (the input cells).
- Click the Data tab.
- In the Data Tools group, click the What-if Analysis drop-down and choose Scenario Manager (Figure B). Figure B
- Click Add and give the scenario a name, such as BestCase (Figure C), and click OK.
Figure C - 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 - 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 - After adding all the scenarios you want to consider, click Summary.
- 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:
- Select B4:C4, click Data, and then choose Scenario Manager from the What-If Analysis drop-down in the Data Tools group.
- In the resulting dialog, don't change anything. You've already set it all up. Instead, click Summary.
- 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 [email protected].
For a subsequent article on this topic, please read: How to use Excel's Data Table analysis tool.
Also see
Microsoft Weekly Newsletter
Be your company's Microsoft insider with the help of these Windows and Office tutorials and our experts' analyses of Microsoft's enterprise products. Delivered Mondays and Wednesdays
Sign up today Sign up todayUpdated
3/1/2016Released
1/28/2016 Excel expert Dennis Taylor demystifies some of the most useful of the 450+ formulas and functions in Excel and shows how to put them to their best use. Dennis starts with a review of the more basic functions (SUM, AVERAGE, and MAX), and a few critical keyboard shortcuts that will let you locate and display formula cells and accelerate working with Excel formulas—even on multiple sheets. He then covers how to find and retrieve data with the VLOOKUP and INDEX functions, calculate totals with counting and statistical functions, extract data with text functions, and work with date, time, array, math and information functions. The course focuses on practical examples that will help viewers easily transition to using Excel's most powerful formulas and functions in real-world scenarios. Topics include:- Displaying and highlighting formulas
- Converting formulas to values
- Tabulating data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the powerful COUNTIF family of functions
- Analyzing data with statistical functions
- Calculating dates and times
- Analyzing data with array formulas and functions
- Extracting data with text function
Duration
- Hi, I'm Dennis Taylor,and welcome to Excel 2016: Advanced Formulas and Functions.For many Excel users,the most important thing they do in Excelis work with formulas and functions.If you really want to take advantage of Excel's power,you need exposure to these features.I'll start off by showing you some great shortcutsfor building formulas and functions,numerous variationson the powerful IF and VLOOKUP functions,vital statistical and math functions,and some date functionsthat could prove to be indispensable.
I'll show you how text functionscan help you clean up flawed data,how powerful array formulas and functionsgive you even more analytical possibilities,and I'll show you how to use functionsin combination with one anotherto achieve even greater power.Welcome to Excel 2016: Advanced Formulas and Functions.
Q: This course was updated on 03/01/2016. What changed?
A: We added one tutorial about the new formulas in Excel 2016.