Наверняка практически каждому из нас приходилось решать оптимизационные задачи, суть которых состоит в том, чтобы найти какой-то параметр (диапазон параметров), которые будут удовлетворять условиям задачи.

в 12 модуле курса "Excel бизнес-анализ и прогнозирование" мы детально изучаем этот вопрос. Сегодня разберём один наглядный пример! 

К примеру, у нас есть следующие условия:

- мы покупаем товар за условные 100 гривен.

- продаем этот товар с наценкой 20%

- зарплата персонала в месяц равняется 35 000 гривен.

- арендная плата 7 000 гривен

- прочие постоянные расходы 4 000 гривен

- переменные затраты на единицу товара 8 гривен.

Задача, которая стоит перед нами – посчитать, какое же количество штук товара нам нужно продавать, чтобы в итоге получить прибыль в размере 5 000 гривен.

Конечно, можно составить уравнение и решить его на листочке:

Но входных данных может быть гораздо больше, да и при каждом их изменении придется каждый раз пересчитывать. Гораздо удобнее создать «модель задачи» и решить её с помощью инструмента «Подбор параметра» (Goal Seek), который входит в группу команд «Анализ «что если» (What-If Analysis).

Что такое модель задачи? – Это расписанные входные данные, которые связанные между собой формулами, для расчёта целевого показателя (в нашем примере целевым показателем является прибыль, которая должна равняться 5000).

Давайте сначала запишем данные, которые нам известны.

Далее приступим к самой модели, свяжем наши данные формулами.

1. Оборот (в грн.) – умножим нашу ячейку с количеством штук на цену продажи.

=D9*D7

1. Закупка (в грн.) - умножим нашу ячейку с количеством штук на цену закупки.

=D9*D4

1. Расходы на доставку (в грн.) - умножим нашу ячейку с количеством штук на стоимость доставки за единицу.

=D9*D17

1. Постоянные затраты (в грн.) – просуммируем затраты на зарплату, аренду и прочие постоянные расходы.

=D12+D13+D14

1. Прибыль (наш целевой показатель) – от Оборота отнимем переменные и постоянные затраты.

=D20-D22-D24-D26

Теперь у нас всё готово для подбора параметра (поиска необходимого количества штук для получения прибыли в размере 5000 грн.)

На вкладке Данные (Data), в группе команд Прогноз (Forecast), выбираем «Анализ «что если» (What-If Analysis), и в ней «Подбор параметра» (Goal Seek).

Появилось диалоговое окно инструмента, которое нужно заполнить.

Если модель составлена верно, то после нажатия на кнопку «ОК» инструмент «Подбор параметра» найдёт решение нашей задачи, подставляя значения в ячейку с Х значения.

Детально учимся работать с подобными задачами в курсе "Excel бизнес-анализ и прогнозирование"  в 12 МОДУЛЕЕ: РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ БИЗНЕСА: «АНАЛИЗ ЧТО-ЕСЛИ» И ДИСПЕТЧЕР СЦЕНАРИЕВ

Автор статьи тренер DATAbi Михаил Беленчук

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"