What-If Analysis

Many users are unaware of the What-If Analysis features in Excel, which can be used to find answers to questions in an automated fashion. I'll start lead off by I'll start by showing you how to use the Scenario Manager feature to both you to back up key inputs and compare the results of different sets of inputs. I'll then use the Goal Seek feature to automate solving for a missing input within a calculation. After that I'll show you how the Data Table feature can streamline situations where, normally, the user would need to rewrite formulas to handle a variety of inputs, such as comparing what a mortgage payment would be at two different interest rates and loan terms. We'll use the Forecast Sheet feature to project amounts into the future, and then finish up with a brief look at Excel's Solver feature.

In this chapter, we will cover the following primary topics:

• PMT function
• CUMIMPT function
• CUMPRINC function
• Scenario...

Technical requirements

Everything that I discuss in this chapter will work in any current version of Excel, except for the Forecast Sheet feature, which requires the Windows version of Excel 2016 or later. This feature is not available in Excel for macOS or Excel for the Web. However, you can view Forecast Sheets that have been created in Excel for Windows in the macOS and online versions.

An example workbook that contains all of the formulas used in this chapter, along with work areas so that you can try the features I discuss, can be found on GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter6.

Let’s lead with a look at a worksheet function that enables you to calculate loan payment amounts. I’ll be using this function, and others, throughout this chapter as we work through various What-If Analysis scenarios.

The PMT function

The =-PMT(C3/12,C4*12,C5) formula in cell C6 of the PMT-CUMIPMT functions worksheet, as shown in Figure 6.1, returns $578.57 as the monthly payment for a $25,000 loan paid off over 4 years at 5.25% interest:

Figure 6.1 – The PMT, CUMIPMT, and CUMPRINC functions

Before I get to the PMT function, let me share a trick I used to display the word years in cell C4. Visually, you see 4 years in the cell, but if you look in the formula bar, only the number 4 appears. This is because I used a custom number format to add the word years. This is a way of displaying text and numbers in the same cell while maintaining the cell as a numeric input. To do so, perform the following steps:

1. Select the cell(s) you wish to format, in this case, cell C4.
2. Choose Format | Format Cells or press Ctrl + 1 ( + 1 in Excel for macOS).
3. Choose Custom on the Number tab.
4. Enter 0" years" in the Type field. In this case, 0 represents our numeric...

The CUMIPMT function

The =-CUMIPMT(C3/12,C4,C5,1,C4,0) formula in cell C7 of Figure 6.1 returns 2,771.26 as the interest due on a loan of $25,000 paid off over four years at an interest rate of 5.25%.

The CUMIMPT function has six required arguments:

• Rate: The interest rate for the loan, which, in this case, is 5.25% from cell C3 divided by 12 to create a monthly interest rate.
• Nper: The number of periods in the loan, which, in this case, is 4 years from cell C4 multiplied by 12 or 48 months.
• Pv: This argument name is short for present value, since money loses value over time, but is also known as the loan amount or principal from cell C5, which, in this case, is $25,000.
• Start_period: The period number within the loan to start calculating the interest due—this is not a date, but a period number, for instance, 1 to represent the first period in the loan.
• End_period: The period number through which to calculate the interest amount, which, in this case...

Understanding the Scenario Manager feature

Scenario Manager can save you time as it eliminates the need to recreate portions of a worksheet to compare different sets of assumptions. Even better, Excel offers two reports that will compare the results of your sets of assumptions automatically.

There are a couple of constraints to keep in mind:

• Scenarios are limited to a single worksheet, meaning all inputs in the scenario must be on the same worksheet.
• Each scenario is limited to 32 inputs. However, you can apply scenarios sequentially to go beyond the 32-item input.
• There is no limit to the number of scenarios that you can store on a given worksheet.

First, let’s set up a calculation that serves as a vehicle for understanding scenarios. Following this, we’ll create multiple scenarios that we’ll swap through the calculation, and then use comparison reports to see the results of our scenarios side by side. Additionally, you’ll see...

The Goal Seek feature

Excel’s Goal Seek feature is a means of automating guesswork in Excel. Let’s say that you want to buy a car, and you know the interest rate, the term of the loan, and the payment that you’re seeking, but you don’t know how much you can borrow. Here are the steps:

1. Activate the Goal Seek worksheet.
2. Choose Data | What-If Analysis | Goal Seek.
3. Enter or select cell C5 in the Set cell field of the Goal Seek dialog box, as shown in Figure 6.12:

Figure 6.12 – The Goal Seek dialog box

4. Enter an amount, such as 350, into the To value field.
5. Enter or select cell C5 in the By Changing Cell field.

Nuance

Excel will show $C$4 in the Set cell field and $C$5 in the By changing cell field if you click on the worksheet with your mouse as opposed to typing the cell address into the field. The $ symbols indicate an absolute reference that is not required in this case because the Goal...

The Data Table feature

Data Tables allow you to swap one or more inputs through a formula to return an array of results. I’m going to walk you through three different variations for calculating loan payments:

• Using one input with five interest rates
• Using two inputs, five interest rates, and five loan lengths
• Using three inputs with five interest rates, five loan lengths, and five loan amounts

The common factor between all three is that we’ll start by calculating a loan payment in the fashion that I described in the PMT function. The PMT function will anchor all three of the Data Tables that we’ll work through.

Creating a Data Table with one input

Cells D2:D4 of the Data Table-One Input worksheet in this chapter’s example workbook are the inputs used by the =-PMT(D2/12,D3*12,D4) formula in cell D6, which returns $2,176.03 as the payment amount for a 30-year loan of $500,000 with a 3.25% interest rate. Let’s say that interest...

Projecting amounts with the Forecast Sheet feature

The Forecast Sheet feature is available in Excel 2016 or later for Windows. You can use it with data that comprises dates/times and values, as shown in Figure 6.20. Let’s assume that we want to protect this data through the end of 2024:

Figure 6.20 – The source data for a forecast

Nuance

The Forecast Feature adds a new worksheet to your workbook that uses the FORECAST.ETS and FORECAST.ETS.CONFINT worksheet functions to project your amounts. You can share a forecast with someone using Excel 2013 or earlier for viewing. The catch is that any edits to the workbook could cause the forecast functions to recalculate. In that event, the forecasted amounts will return #NAME? because older versions of Excel cannot recognize those worksheet functions.

Now, let’s give the Forecast Sheet feature a try:

1. Select any cell within your date-based or time-based data, such as cell B4 on...

Introducing the Solver feature

I’m only going to lightly touch on Excel’s Solver feature so that I can compare it to Goal Seek. Often, I describe Solver as Goal Seek on steroids. You can solve for multiple missing inputs, place constraints on the solve, and much more. With that said, if you like Goal Seek, you’ll like Solver even more once you get past the busier user interface. The Solver feature is an Excel add-in, which enables additional functionality that is not available in the default installation of Excel. The first step to using Solver is to enable the add-in:

1. Choose File | Options | Add-Ins (or choose Tools | Excel Add-Ins in Excel for Mac).
2. Click on the Go… button in the Manage: Excel Add-ins section of the Options dialog box (skip this step in Excel for Mac).
3. Click on the Solver Add-In checkbox, as shown in Figure 6.23:

Figure 6.23 – Enabling the Solver Add-in checkbox

4. Click on OK...

Summary

Often, Excel’s What-If Analysis features are overlooked as a means to automate repetitive tasks in Excel. In this chapter, you learned about the PMT, CUMIPMT, and CUMPRINC functions, which can be used to calculate loan payments and total interest for one or more periods in a loan. We used those functions as the basis for walking through the What-If Analysis features in Excel. For instance, the Scenario Manager feature enables you to swap different sets of inputs into a worksheet and compare the results by way of scenario reports. Alternatively, Goal Seek allows you to solve for a single missing input. This eliminates the manual guesswork that can otherwise be involved in trying to determine the missing amount.

Then, our trip through the Data | What-If Analysis menu took us to the Data Table feature. Data Tables can solve multiple equations at once based on swapping out one or more inputs. This gives your formulas better integrity because you don’t have to...