假设情景相当容易理解——(– put)简单地说，你的问题是，“If this happens, what happens to my numbers, or bottom line? In other words, if we do $20,000 worth of sales over the next few months, how much profit will we show?” 在其最基本的形式中，这就是假设分析(What-If Analysis)的目的——预测。
与Excel中的大多数其他内容一样，此功能非常强大。它允许您执行从相对简单的假设预测到高度复杂的场景的所有操作。而且，与Excel功能的常见情况一样，我无法在这个简短的教程中涵盖所有可能性。
相反，我们今天将研究基础知识，我将为您提供一些相对简单的假设概念来帮助您入门。
制作基本投影(Making Basic
Projections)
正如您可能知道的那样，在正确的手中，可以操纵正确的一组数字来表达任何事情。毫无疑问，您已经听说过以各种方式表达这一点，例如Garbage in、garbage out。或者，也许预测仅与它们的假设一样好。(Projections are only as good as their presumptions. )
Excel提供了许多设置和使用假设分析的方法。那么让我们看一个相当简单直接的投影方法(projection method)，Data Tables。此方法使您能够了解更改一两个变量（例如，您缴纳多少税款）如何影响您的业务底线。
另外两个重要的概念是Goal Seek和 Excel 的Scenario Manager。使用Goal Seek，您可以尝试预测为实现预定目标而必须发生的事情，例如赚取一百万美元的利润，而Scenario Manager允许您创建和管理自己的What-If集合（以及其他）场景。
数据表法——一个变量(The Data Tables Method – One Variable)
首先，让我们创建一个新表并命名我们的数据单元格。为什么？好吧，这允许我们在公式中使用名称，而不是单元格坐标。这不仅在处理大表时有用——更加准确和准确，而且有些人（包括我）发现它更容易。
在任何情况下，让我们从一个变量开始，然后转到两个变量。
- 在 Excel 中打开一个空白工作表。
- 创建以下简单表。
请注意，要在第 1 行(Row 1)创建表格标题，我合并了单元格A1 和 B1(A1 and B1)。为此，请选择两个单元格，然后在“主页”(Home)功能区上，单击“合并和居中”(Merge & Center )向下箭头并选择“合并单元格(Merge Cells)” 。
- 好的(Okay)，现在让我们命名单元格B2 和 B3(B2 and B3)。右键单击单元格(Right-click cell) B2并选择定义名称(Define Name)以打开新名称对话框(New Name dialog)。
如您所见，新名称(New Name)很简单。至于范围(Scope)下拉菜单，这使您可以相(cell relative)对于整个工作簿或仅活动工作表来命名单元格。在这种情况下，默认值很好。
- 单击确定(OK)。
- 将单元格命名为 B3 Growth_2019，这也是默认值，在本例中，因此单击OK。
- 重命名单元格 C5 Sales_2019
现在请注意，如果单击您命名的任何这些单元格，名称而不是单元格坐标(cell coordinate)将显示在工作表左上角的名称框（下方以红色框出）中。(Name)
要创建我们的假设情景(What-If scenario)，我们需要在 C5（现在是Sales_2019）中编写一个公式。这个小预测表(projection sheet)可以让您查看按增长百分比计算您将赚到多少钱。
现在，该百分比为 2。要根据不同的增长百分比获得不同的答案，当我们完成电子表格时，您只需更改单元格 B3 中的值（现在，Growth_2019）。但我正在超越自己。
- 在单元格 C5 中输入(Enter)以下公式（下图中以红色标出）：
=Sales_2018+(Sales_2018*Growth_2019)
输入完公式后，您应该在单元格 C5 中获得预计的数字。您现在可以通过简单地更改单元格 B3 中的值来根据增长百分比预测您的销售额。
继续尝试。将单元格 B3 中的值更改为2.25%.现在试试，5%。你明白了吗？很简单，是的，但你能看到可能性吗？
数据表法——两个变量(The Data Table Method – Two Variables)
生活在一个你所有的收入都是利润的世界里，这不是很棒吗——你没有任何开支！唉(Alas)，事实并非如此；因此，我们的 What-If 电子表格并不总是那么乐观。
我们的预测还需要考虑到我们的开支。换句话说，您的预测将有两个变量：收入和支出(income and expenses)。
要设置它，让我们首先将另一个变量添加到我们之前创建的电子表格中。
- 单击单元格A4并输入Expenses 2019，如下所示：
- 在单元格B4中键入10.00%
- 右键单击单元格C4并从弹出菜单中选择定义名称。(Define Name)
- 在新名称对话框(New Name dialog)中，单击名称(Name)字段并输入费用_2019。(Expenses_2019.)
(Easy)到目前为止很容易，对吧？剩下要做的就是修改我们的公式以包含单元格 C4 中的值，如下所示：
- 修改C5单元格中的公式如下（在括号数据末尾添加*Expenses_2019
=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)
我相信您可以想象，您的假设分析可以更加精细，这取决于几个因素，包括您包含的数据、您的公式编写技巧等等。
无论如何，现在您可以从收入（增长(Growth)）和费用(Expenses)两个角度进行预测。继续并更改单元格B3 和 B4(B3 and B4)中的值。插入您自己的数字并旋转您的小假设工作表。
附加研究(Additional
Studies)
与您可以在Excel中执行的几乎所有其他操作一样，您可以将此假设分析功能(Analysis feature)用于一些相当复杂的场景。事实上，我可以写几篇关于投影场景的文章，甚至无法详细介绍该主题。
同时，这里有一些链接指向一些更详细的假设(What-If)脚本和场景。
- What-If 分析(What-If Analysis)：这个说明性很强的操作方法着眼于 Excel 的Scenario Manager等内容，您可以在其中创建和管理自己的What-If（和其他）场景集合。
- 假设分析简介(Introduction to What-If Analysis)：这是Microsoft Office 支持网站(Microsoft Office Support site)对假设分析的简介。这里有很多信息，其中包含指向大量有用假设说明的链接。
- 如何在 Excel 中使用 Goal Seek 进行 What-If 分析(How to use Goal Seek in Excel for What-If analysis)：这里介绍 Excel 的Goal Seek What-If分析功能(Analysis feature)。
How To Understand What-If Analysis In Microsoft Excel
A what-if scenario is fairly easy tо understand – put simply, your question is, “If this happens, what happens to my numbers, or bottom line? In other words, if we do $20,000 worth of sales over the next few months, how much profit will we show?” At its basest form, this is what What-If Analysis is designed to do – projections.
As with most everything else in Excel, this feature is robust. It allows you to perform everything from relatively simple What-If projections to highly sophisticated scenarios. And, as is usually the case with Excel features, there’s no way I can cover all the possibilities in this short tutorial.
Instead we’ll look at the basics today, and I’ll provide you with a few relatively easy What-If concepts to get you started.
Making Basic
Projections
As you probably know, in the right hands, the right set of numbers can be manipulated to say just about anything. You’ve undoubtedly heard this expressed in all sorts of ways, such as Garbage in, garbage out. Or perhaps Projections are only as good as their presumptions.
Excel provides many, many ways to set up and use What-If Analysis. So let’s look at a fairly simple and straightforward projection method, Data Tables. This method enables you to see how changing one or two variables, such as, say, how much taxes you pay, affects your business’s bottom line.
Two other significant concepts are Goal Seek and Excel’s Scenario Manager. With Goal Seek, you try to project what has to happen for you to achieve a predetermined objective, such as, say, making a million-dollar profit, and Scenario Manager allows you to create and manage your own collection of What-If (and other) scenarios.
The Data Tables Method – One Variable
To get started, let’s create a new table and name our data cells. Why? Well, this allows us to use names in our formulas, rather than cell coordinates. Not only can this be useful – much more exact and accurate – when working with large tables, but some people (including me) find it easier.
In any case, let’s 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 title in Row 1, I merged cells A1 and B1. To do so, select the two cells, then, on the Home ribbon, click the Merge & Center down-arrow and choose Merge Cells.
- Okay, now let’s name cells B2 and B3. Right-click cell B2 and choose Define Name to bring up the New Name dialog box.
As you can see, New Name is straightforward. As for the Scope drop-down, this lets you name the cell relative to the entire workbook, or just the active worksheet. In this case, the defaults are fine.
- Click OK.
- Name cell B3 Growth_2019, which is also the default, in this case, so click OK.
- Rename cell C5 Sales_2019
Now notice that if you click any of these cells you named, the name, instead of the cell coordinate, shows up in the Name box (outlined in red below) in the upper-left corner above the worksheet.
To create our What-If scenario, we need to write a formula in C5 (now Sales_2019). This little projection sheet allows you to see how much money you’ll make by percentage of growth.
Right now, that percentage is 2. To get different answers based on varying percentages of growth, when we finish the spreadsheet, you’ll simply change the value in cell B3 (now, Growth_2019). But I’m getting ahead of myself.
- Enter the following formula in cell C5 (outlined in red in the image below):
=Sales_2018+(Sales_2018*Growth_2019)
When you finish entering the formula, you should get the projected number in cell C5. You can now project your sales based on a percentage of 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?
The Data Table Method – Two Variables
Wouldn’t it be terrific to live in a world where all your income is profit – you have no expenses! Alas, that’s not the case; therefore, our What-If spreadsheets aren’t always so rosy.
Our projections also need to take into account our expenses. In other words, your projection will have two variables: income and expenses.
To set that up, let’s start by adding another variable to the spreadsheet we created earlier.
- Click in cell A4 and type Expenses 2019, like this:
- Type 10.00% in cell B4.
- Right-click in cell C4 and select Define Name from the pop-up menu.
- In the New Name dialog box, click in the Name field and type Expenses_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 *Expenses_2019 at the end of the parenthetical data.)
=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)
As I’m sure you can imagine, your What-If’s can be much more elaborate, depending on several factors, including the data you include, your formula-writing skills, and so on.
In any case, now you can make projections from two perspectives, income (Growth) and Expenses. Go ahead and change the values in cells B3 and B4. Plugin your own numbers and give your little What-If worksheet a spin.
Additional
Studies
As with almost everything else you can do in Excel, you can take this What-If Analysis feature to some rather elaborate scenarios. In fact, I could write several articles on projection scenarios and not even come close to covering the topic in detail.
Meanwhile, here are a few links to some more elaborate What-If scripts and scenarios.
- What-If Analysis: This well-illustrated how-to looks at, among other things, Excel’s Scenario Manager, where you can create and manage your own collection of What-If (and other) scenarios.
- Introduction to What-If Analysis: Here’s the Microsoft Office Support site’s introduction to What-If Analysis. There’s lots of information here with links to a slew of useful What-If instructions.
- How to use Goal Seek in Excel for What-If analysis: Here’s an introduction to Excel’s Goal Seek What-If Analysis feature.
