Хитрости EXCEL: Анализ «Что если»

ВЫПУСК №11 (НОЯБРЬ, 2017)

Журнал «Финансовый директор компании» совместно с DATA ANALYSIS SYSTEMS запускает новую рубрику «Хитрости Excel», где мы будем исследовать то, что вы могли еще не знать. Начинаем с «Анализа «что если» — подробнее в материале

 

Яна Нудельман,

тренер DATA ANALYSIS SYSTEMS

Инструмент «Анализ «что если» (далее — АЧЕ) достаточно простой вид анализа, который позволяет решать оптимизационные задачи.

Основными оптимизационными задачами принято считать решение следующих вопросов:

• минимизация расходов;

• максимизация прибыли;

• подбор наилучшей цены для продукта;

• поиск точки безубыточности;

• анализ изменения различных показателей вследствие изменения определенных факторов.

 

АЧЕ содержит в себе три подраздела — «таблица данных», «подбор параметра» и «диспетчер сценариев». Каждый из этих инструментов рассмотрим на примере конкретных задач. Для работы используем Excel 2016 версии Pro, поскольку многие инструменты в нем уже встроенные и нет необходимости в дальнейшей доустановке. Кнопка АЧЕ находится на закладке «Данные», раздел «Прогноз» (в некоторых версиях «Работа с данными», рисунок 1):

Рисунок 1

Хитрости EXCEL: Анализ «Что если»

 

Задача 1. Подбор лучшей цены для товара, оценка ее влияния на оборот, прибыль, переменные затраты.

Условия: мы планируем открыть небольшое предприятие по продаже одного продукта, например мороженого в торговом центре. У нас есть поставщик, готовый поставлять нам продукт по цене 27 грн/шт. Посчитали, что постоянные затраты (аренда, коммуналка и т.д.) будут составлять 50 тыс. грн в год. Конкуренты продают подобный товар по 40 грн за штуку. Также мы провели исследование спроса (таблица 1) и выяснили следующее:

Таблица 1

Цена, грн

Спрос, шт.

28

40000

35

25000

40

10000

45

1000

 

Будем использовать подраздел «Таблица данных» для того, чтобы ответить на вопросы:

1. Какими будут наши оборот, прибыль, переменные затраты, если мы поставим цену как у конкурентов?

2. Насколько мы можем снижать цену, и как при этом будут изменяться наши   оборот, прибыль и переменные затраты?

3. По каким ценам нужно закупать товар, и какую отпускную цену ставить, чтобы получить максимальную прибыль?

 

Для начала необходимо заполнить модель задачи в ячейках — внести формулы, которые будут считать оборот, затраты, прибыль (таблица 2).

Таблица 2

Хитрости EXCEL: Анализ «Что если»

Для этого следует посчитать, какое количество товара мы будем продавать. Кроме того, поскольку мы будем искать оптимальную отпускную цену, желательно чтобы количество штук считалось автоматически при разных ценах.

ВАЖНО! Для применения данного инструмента необходимо, чтобы все цифры, задействованные в расчете, были связаны между собой формулами.

 

Читать дальше →