Пример применения "Поиска решений" Excel в бюджетировании

Недавно пришлось делать очередной бюджет. При сборе информации по одному из типов расходов были получены следующие данные, о количестве человек, на которых компания
должна потрать по 545 рублей:

Подразделение	Количество персонала
Отдел 1	95
Отдел 2	44
Отдел 3	22
Отдел 4	28
Отдел 5	109
Отдел 6	19
Отдел 7	38
Отдел 8	55
Отдел 9	16
Отдел 10	6
Отдел 11	6
Отдел 12	18
Отдел 13	4
Итого	460

При этом, ответственный за предоставление данных смог определить только общее количество сотрудников на каждый месяц:

март	май	июнь	август	декабрь	Итого
66	67	102	75	150	460

Как распределить это количество по месяцам в разрезе отделов? Получить точный ответ не удалось, а распределять по спасительному принципу "пол-палец-потолок" желания не было. Тут вспомнил про инструмент оптимизации в  Excel "Поиск решений". Для начало нужно было построить исходную модель. Вот как она выглядит:

Что тут есть: нужные нам значения оставляем пустыми, задаем поля ограничений по отделам по столбцу I, и ограничения задачи в разрезе месяцев по строке 17.

Считаем итоги по строкам и по столбцам - это будут наши сравниваемые с ограничением ячейки.

Теперь приступает к формулированию задачи, если у вас не установлена надстройка "Поиск решений"-установите ее: файл-параметры - надстройки-перейти, выбрать нужный инструмент и нажать на  "ок":

Пакет установлен, теперь необходимо задать параметры для вычисления нашей модели. Главной целевой ячейкой должна стать $H$16, она обязана равняться 460 единицам. Задаем задаем параметры по каждой строке и каждому столбцу, нажимаем на кнопку "Добавить" для дополнительных условий, выделяем нужную ячейку, ставим условие равно и проставляем значение по соответствующему отделу. Проставляем все условия по столбцам и по строкам, итог который должен получиться виден на скриншоте. Жмем "Найти решение", и получаем нужный результат.

Нажимаем "Ok" и получаем ответ о распределении сотрудников по месяцам, теперь осталось перемножить численность на 545 рублей и получить готовую сумму расходов.

Надеюсь, эта статья вам поможет составлять прогнозы более эффективно. Успехов всем в работе.