A pretty straightforward hypothetical scenario – put simply, your question is, “If this happens, what will happen to my numbers, or the bottom line? In other words, if we hit $20,000 worth of sales in the next few months, how much profit will we show? ” In its most basic form, this is what What if the analysis designed to do – projections.
As with most things in Excel, this feature is very powerful. It allows you to do everything from relatively simple What-If projections to complex scenarios. And, as usual with Excel features, there’s no way I could cover all the possibilities in this short tutorial.
Instead, we’re going to look at the basics today, and I’ll give you a few relatively easy What-If concepts to get you started.
Table of Contents
Make basic projections
As you probably know, in the right hand the matching set of numbers can be used to talk about anything. You have certainly heard this expressed in any way, such as Garbage in, garbage out. Or maybe Predictions are only as good as their assumptions.
Excel provides lots of ways to set up and use Analysis if it happens. So let’s consider a rather simple and straightforward projection method, Data sheet. This method allows you to see how changing one or two variables, such as the amount of tax you pay, affects your business profits.
Two other important concepts are Target Search and Excel’s Scenario Manager. With Goal Seek, you try to predict what will happen to you to achieve your predetermined goals such as generating millions of dollars in profits, and the Situation Manager allows you to create and manage collections What-If episodes (and other situations).
Data Table Method – One Variable
To get started, let’s create a new table and name our data cells. Why? This allows us to use names in our formulas, instead of cell coordinates. Not only can this be useful – much more precise and accurate – when working with large tables, but some people (myself included) find it easier.
In any case, start with one variable and then move on to two.
- Open a blank worksheet in Excel.
- Create the following simple table.
Note that to create the table header in Row 1, I merged cells A1 and B1. To do so, select two cells, then, on Home ribbon, click Merge & Center down arrow and select Merge cells.
- Okay, now let’s name cells B2 and B3. Right click on the cell B2 and choose Identify the name to display the New Name dialog box.
You can see, New name is simple. For Range drop-down, this allows you to set cell names relative to the entire workbook or just the active sheet. In this case the defaults are fine.
- Click ALRIGHT.
- Name cell B3 Growth_2019is also the default, in this case, so click ALRIGHT.
- Rename cell C5 Sell_2019
Now note that if you click on any of the cells you have named this, the name, instead of the cell coordinates, will show up in Name (outlined in red below) in the upper-left corner of the sheet.
To create our What-If scenario, we need to write a formula in C5 (now Sell_2019). This little forecast sheet lets you know how much you will earn as a percentage of growth.
Now that percentage is 2. To get different answers based on different growth percentages, when we complete the spreadsheet, you just need to change the value in cell B3 (now, Growth_2019). But I’m getting ahead of myself.
- Enter the following formula in cell C5 (highlighted in red in the image below):
=Sales_2018+(Sales_2018*Growth_2019)
When you finish entering the formula, you will get the expected number in cell C5. You can now predict your sales based on percentage growth by simply changing the value in cell B3.
Go ahead and try it. Change the value in cell B3 to 2.25%. Now try, 5%. Are you getting the idea? Simple yes, but can you see the possibilities?
Data Table Method – Two Variables
It wouldn’t be great to live in a world where all your income is profit – you have no expenses! Alas, that is not the case; so our What-If spreadsheet isn’t always so rosy.
Our forecasts also need to take our costs into account. In other words, your forecast will have two variables: income and expenses.
To set it up, let’s start by adding another variable to the spreadsheet we created earlier.
- Click on the box A4 and type Cost of 2019like this:
- Type 10.00% in the cell B 4.
- Right click on the cell C4 and choose Identify the name from the pop-up menu.
- In the New Name dialog box, click Name field and type Cost_2019.
Easy so far, right? All that’s left to do is modify our formula to include the value in cell C4, like this:
- Modify the formula in cell C5 as follows (add * Chi_2019 at the end of the data in parentheses.)
=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)
As I’m sure you can imagine, your What-If can be much more complicated, depending on a number of factors, including the data you put in, your formula writing skills, etc.
In any case, you can now make a forecast from two aspects, Income (Growth) and Expenses. Go ahead and change the values in cells B3 and B4. Add your own numbers and give your small If-If sheet a spin.
Additional research
As with pretty much anything else you can do in Excel, you can use this Analysis What If for some rather complex situations. In fact, I could write several articles on projection scenarios and not even cover the topic in detail.
In the meantime, here are some links to some of the more complex What-If scenarios and scenarios.
- What if the analysis: This well-illustrated how-to is, among other things, Excel’s Case Manager, where you can create and manage a collection of If-If (and other) scenarios of only me.
- Introduction to If-If-If Analysis: This is the introduction of the Microsoft Office Support page on What-If Occurrence Analysis. There’s a lot of information here with links to a bunch of helpful If-If tutorials.
- How to use Goal Seek in Excel for What If Occurrence analysis: This is an introduction to Excel’s Target If Occurrence Analysis feature.
