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 = Sales_2018 + (Sales_2018 * Growth_2019) 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? 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. 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: =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. 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. –
What-If Analysis In Microsoft Excel”/> Data table method – two variables
What-If Analysis In Microsoft Excel”/> Additional Research
Additional Studies