The “what if” scenario is fairly easy to understand – simply put, your question is: * “If this happens, what happens to my numbers or net profit? In other words, if we make $ 20,000 in sales over the next few months, how much profit will we make? »* In its most basic form, this is what what-if analysis is for – predictions.

As with everything else in Excel, this feature is robust. It allows you to do everything from relatively simple what-if predictions to very complex scenarios. And, as is usually the case with Excel functions, I cannot cover all the possibilities in this short tutorial.

What-If Analysis In Microsoft Excel”/>

Instead, we’ll cover the basics today, and I’ll give you some relatively simple “what if” concepts to get you started.

### Making Basic Forecasts

** Make basic expectations **

What-If Analysis In Microsoft Excel”/>

As you probably know, in the right hands you can manipulate the correct set of numbers to say anything. You have no doubt heard that this is expressed in many different ways, for example, * garbage in, garbage out. * Or perhaps * predictions are as good as their guesses are. *

Excel provides many ways to customize and use what-if analysis. So let’s look at a fairly simple and straightforward projection method – data tables. This method allows you to see how changing one or two variables, such as the amount of taxes paid, affects the bottom line of your business.

Two other important concepts are Goal Seek and Excel’s Scenario Manager. With Goal Seek, you try to project what needs to happen to you in order to achieve a predetermined goal, such as, say, making a million dollar profit, and Scenario Manager lets you create and manage your own collection of What-If (and other) scenarios.

#### Data Tables Method – One Variable

First, let’s create a new table and name our data cells. Why? Well, this allows us to use names in our formulas, not cell coordinates. Not only can this be useful – much more accurate and precise – when working with large tables, but it can be easier for some people (myself included).

Anyway, let’s start with one variable and then move on to two.

- Open a blank worksheet in Excel.
- Create the following simple table.

What-If Analysis In Microsoft Excel”/>

Note that I have merged cells A1 and B1 to create the table header on row 1. To do this, select two cells, then on the Home ribbon, click the Merge and Center down arrow and select Merge Cells.

- Ok, now let’s name cells B2 and B3. Right-click cell B2 and select Define Name to open the New Name dialog box.

As you can see, New Name is simple. With regard to the Region dropdown, this allows you to name the cell relative to the entire workbook or just the active sheet. In this case, everything is fine.

- Click OK.
- Name cell B3
*Growth_2019*, which is also the default in this case, so click OK. - Rename cell C5
*Sales_2019*

What-If Analysis In Microsoft Excel”/>

Now notice that if you click on any of these named cells, the name instead of the cell coordinate appears in the Name box (circled in red below) in the upper left corner above the sheet.

What-If Analysis In Microsoft Excel”/>

To create a What-If scenario, we need to write a formula in C5 (currently * Sales_2019
*

This percentage is now 2. To get different answers based on different growth percentages, when we are done with the table, you simply change the value in cell B3 (now * Growth_2019
*

- Enter the following formula in cell C5 (highlighted in red in the image below):

* = Sales_2018 + (Sales_2018 * Growth_2019) *

What-If Analysis In Microsoft Excel”/>

When you finish entering the formula, you should get the predicted number in cell C5. You can now predict your sales based on growth percentage by simply changing the value in cell B3.

Come on, try it. Change the value in cell B3 to * 2.25% *. Now try * 5% *. Do you get the idea? Yes, simple, but do you see the possibilities?

#### Data table method – two variables

Wouldn’t it be great to live in a world where all your income is profit – you have no expenses! Alas, this is not the case; that’s why our What-If spreadsheets aren’t always that rosy.

Our projections should also take into account our costs. In other words, your forecast will have two variables: income and expenses.

To set this up, let’s start by adding another variable to the spreadsheet we created earlier.

- Click cell A4 and enter
*Expenses 2019*, for example:

- Enter
*10.00%*in cell B4. - Right-click cell C4 and choose Define Name from the pop-up menu.
- In the New Name dialog box, click the Name field and enter
*Expenses_2019.*

What-If Analysis In Microsoft Excel”/>

So far, everything is simple, right? All that’s left to do is change our formula to include the value in cell C4, for example:

- Change the formula in cell C5 as follows (add
** Expenses_2019*at the end data in brackets.)

*=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019* * *

As I’m sure you can imagine, your what-ifs can be much more complex, depending on several factors, including the data you include, your formula writing skills, and so on.

In any case, now you can make predictions from two points of view: income (growth) and expenses. Go ahead and change the values ??in cells B3 and B4. Add your own numbers and try your little “What If” worksheet.

### Additional Research

** Additional Studies **

Like almost everything else you can do in Excel, you can use this what-if analysis feature for some fairly complex scenarios. In fact, I could write several articles on projection scripts and not even come close to covering this topic in detail.

In the meantime, here are some links to more detailed scenarios and what-if scenarios.

- Analyzing what-if: This well-illustrated how-to guide covers, among other things, Excel Script Manager. where you can create and manage your own collection of what-if scripts (and others).
- An introduction to what-if analysis: Here is an introduction on the Microsoft Office Support site to what-if analysis. There is a lot of information here with links to many useful “what if” instructions.
- How to Use Excel Target Search for What-If Analysis: Here’s an introduction to the what-if analysis function in Excel.

–