После этого появится эта самая строка, в каждой ячейке которой можно будет выбрать итоговый показатель по столбцу. При добавлении новых данных он будет пересчитываться автоматически.
Сценарный анализ в excel – 1. Таблицы данных
В старых версиях Excel этот инструмент назывался не «Таблицы данных», а «Таблицы подстановки». Суть же не изменилась.
Это простой способ получить наглядное представление, как на итоговый результат (ставку кредита или прибыль, например) повлияют разные сочетания входящих параметров (срока кредита или выручки соответственно).
Рассмотрим этот инструмент на примере. Допустим, у нас есть простая модель расчета прибыли от продаж (серым обозначены входящие параметры, а в белых ячейках – расчетные показатели):
Если мы хотим посмотреть влияние изменения одного параметра (например, проанализировать влияние изменения объема производства) на несколько расчетных показателей, нужно поставить в соседние ячейки одной строки ссылки на ячейки с расчетными показателями, а в столбец перечислить разные сценарии по входному параметру:
В этом примере в строке стоят ссылки на ячейки с выручкой, себестоимостью и прибылью от продаж. В столбце – разные варианты по объему производства. После того как данные готовы, необходимо выделить всю таблицу (в данном случае с ячейки «Количество товаров» и до правого нижнего угла), на ленте инструментов выбрать:
Данные → Анализ «Что если» → Таблица данных
и в появившемся диалоговом окне в пункте «Подставлять данные по строкам» (то есть наши варианты по количеству производимых товаров) поставить ссылку на ячейку с количеством товаров в нашей модели – в примере это ячейка B3.
После этого в таблице будут отображены разные сценарии изменения выручки, себестоимости и прибыли от продаж при шести вариантах объема производства:
Вы наверняка обратили внимание, что в диалоговом окне был и пункт «Подставлять значения по столбцам», который остался незаполненным. Есть возможность делать таблицы данных с двумя входящими параметрами – для этого и нужны оба пункта. Тогда конечный параметр будет только один, а не три (как в примере) или более.
Допустим, вы хотите рассмотреть разные сценарии по прибыли от продаж при изменении удельной себестоимости и объема производства. Объем производства оставим в столбце, а в строке приведем разные варианты по удельной себестоимости. В левую верхнюю ячейку заготовки поставим ссылку на ячейку с конечным показателем – прибылью от продаж:
Выделяем таблицу и снова вызываем инструмент «Таблица данных». Но теперь в диалоговом окне мы ставим ссылки на две ячейки исходной модели – с удельной себестоимостью и объемом производства:
И получаем результат:
Сценарный анализ – 2. Подбор параметра
Еще один полезный встроенный инструмент Excel для проведения анализа «Что если» – «Подбор параметра». Его можно найти там же, где и таблицы данных:
Данные → Анализ «Что если» → Подбор параметра.
Подбор параметра позволяет получить ответ на вопрос:
Каким должен быть входящий параметр, чтобы получить заданный результат?
Или, если рассматривать пример – модель из предыдущего раздела:
Какой должна быть себестоимость единицы товара (при прочих равных), чтобы получить прибыль, равную 58 000 рублей?
Вызовем инструмент «Подбор параметра», чтобы получить ответ:
В первом пункте мы указываем ссылку на ячейку с целевым показателем – в данном случае она была активна и подставилась автоматически (B9). Во втором пункте диалогового окна нужно указать желаемое целевое значение – мы хотим прибыль от продаж на уровне 58 тысяч. Изменять мы будем параметр в ячейке B5 – себестоимость единицы.
Нажимаем ОК и получаем результат. Можно сохранить его в таблице или вернуть исходные значения.
Как построить простой прогноз в Excel
В Excel можно построить простой прогноз продаж или другого показателя – с учетом сезонности или без.
Самый простой способ – добавить линию тренда на график с показателем.
Допустим, у вас есть график с динамикой продаж. Щелкните правой кнопкой мыши на ряд данных и нажмите «Добавить линию тренда …»:
В окне «Формат линии тренда» выберите тип тренда (в данном случае может подойти линейный), а также на сколько периодов (в данном случае месяцев) вперед построить прогноз. Можно вывести на график коэффициент детерминации (R2) чем он ближе к единице, тем точнее тренд описывает реальные данные, – соответственно, если вы будете сравнивать несколько типов линий тренда, то с помощью этого коэффициента сможете выбрать лучшую.
