Сценарий «что, если» довольно легко понять —^{(– 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^{(What-If scenario)} , нам нужно написать формулу в C5 (теперь Sales_2019 ). Этот небольшой прогнозный лист^{(projection sheet)} позволяет вам увидеть, сколько денег вы заработаете в процентах от роста.

Прямо сейчас этот процент равен 2. Чтобы получить разные ответы на основе разных процентов роста, когда мы закончим электронную таблицу, вы просто измените значение в ячейке B3 (сейчас это Growth_2019^{(Growth_2019)} ). Но я забегаю вперед.

• Введите^(Enter) следующую формулу в ячейку C5 (обведена красным на изображении ниже):

=Sales_2018+(Sales_2018*Growth_2019)

Когда вы закончите вводить формулу, вы должны получить прогнозируемое число в ячейке C5. Теперь вы можете прогнозировать свои продажи на основе процентного роста, просто изменив значение в ячейке B3.

Давай, попробуй. Измените значение в ячейке B3 на 2.25%.Теперь попробуйте, 5% . Вы поняли идею? Да, просто, но видите ли вы возможности?

Метод таблицы данных — две переменные^{(The Data Table Method – Two Variables)}

Было бы здорово жить в мире, где все ваши доходы — это прибыль — у вас нет расходов! Увы^(Alas) , это не так; поэтому наши электронные таблицы «что, если» не всегда такие радужные.

Наши прогнозы также должны учитывать наши расходы. Другими словами, ваш прогноз будет иметь две переменные: доход и расход^{(income and expenses)} .

Чтобы настроить это, давайте начнем с добавления еще одной переменной в электронную таблицу, которую мы создали ранее.

• Щелкните ячейку A4 и введите Расходы 2019^{(Expenses 2019)} , например:

• Введите 10.00% в ячейку B4.
• Щелкните правой кнопкой мыши ячейку 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 Analysis)} : это хорошо иллюстрированное практическое руководство рассматривает, среди прочего, диспетчер сценариев^{(Scenario Manager)} Excel , где вы можете создавать и управлять своей собственной коллекцией сценариев « что, если^(What-If) » (и других).
• Введение в анализ «что, если»^{(Introduction to What-If Analysis)} : Вот введение на сайте поддержки Microsoft Office^{(Microsoft Office Support site)} в анализ «что, если». Здесь много информации со ссылками на множество полезных инструкций «что, если».
• Как использовать функцию «Поиск цели» в Excel для анализа «что, если^{(How to use Goal Seek in Excel for What-If analysis)} » : вот введение в функцию «Поиск цели ^{(Analysis feature)}»^{(Goal Seek)} в Excel .

How To Understand What-If Analysis In Microsoft Excel

A what-if scenario is fairly easy to 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.

Related posts

• Как создать Gantt Charts в Microsoft Excel

• Учебное пособие по основам Microsoft Excel — обучение работе с Excel

• 40 лучших сочетаний клавиш Microsoft Excel

• Как использовать анализ «что, если» в Excel

• Как изменить Background в Microsoft Teams

• График ваших данных Excel

• Как добавить планки погрешностей в Excel

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как получить Microsoft Word бесплатно

• Как вставить подпись в Microsoft Word Document

• Как надежно Password Protect Excel File

• Microsoft Word не отвечает? 8 способов исправить это

• Как добавить комментарии к ячейке листа Excel

• Как Create and Use AutoText в Microsoft Word

• Исправить Microsoft Outlook обнаружил проблему с ошибкой

• Вставить лист Excel в документ Word

• Как использовать СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧСЛИМН в Excel

• Как создать ярлыки Microsoft Outlook для электронной почты и задач

• Как рассчитать Variance в Excel

• Как использовать функции автоматического восстановления и резервного копирования Excel