Применение сценариев для решения задачи менеджера
Итак, сформулирована задача, в которой имеется результирующая функция, задающая доход. Эта функция зависит от многих параметров. Некоторыми из них можно управлять. Задача, которую должен решить менеджер для оптимизации дохода, заключается в поиске подходящих значений этих параметров. Конечно, можно считать, что наилучшие значения этих параметров менеджер мог бы получить, если бы попытался решать свою задачу как задачу оптимизации. Но здесь не все так просто. Точная формулировка задачи оптимизации потребовала бы дополнительных усилий, трудно было бы сформулировать некоторые ограничения, например, условия накладываемые типографией. Можно отметить и сложность решения оптимизационных задач. Кроме того, часто нет смысла стрелять из пушки по воробьям и искать точное решение в условиях, когда сама модель и ее параметры далеко не точны и отражают лишь суть дела.
В этих типичных условиях сценарии имеют большое практическое значение. Здесь пользователю предоставляется возможность рассмотреть наиболее разумные варианты. А опытный пользователь в своем деле эксперт - всегда знает, где лежит подходящее решение. Поэтому ему достаточно обычно просмотреть несколько возможных вариантов и выбрать наилучший среди них. Варианты, или, как их называют, сценарии, могут быть предложены разными специалистами. Отчет по результатам применения различных сценариев позволяет обосновать принятое решение.
Я специально построил довольно сложную результирующую функцию, по ходу вычисления которой приходится выполнять сложные расчеты, в том числе обращаться к процедурам, написанным на VBA. Вообще результаты принятого решения можно оценивать сразу по нескольким критериям, так что функций, выдающих результат, может быть несколько. Все это не мешает применять сценарии.
Рассмотрим решение задачи менеджера с применением сценариев. Обращаю внимание, что прежде чем обращаться к сценариям предстоит довольно большая работа по формированию на рабочем листе модели исследуемого процесса. В этой модели нужно определить ту функцию, которая подлежит оптимизации, параметры, которые будут изменяться в сценариях. На нашем примере можно убедиться, что это, возможно, достаточно серьезная работа. Но не буду Вас больше пугать, лучше взгляните, как выглядит рабочий лист, на котором эта работа уже проделана:
увеличить изображение
Рис. 8.31. Решение задачи менеджера с применением сценариев
Опишем последовательно этапы решения задачи:
- На рабочем листе Excel вначале выписаны все параметры, используемые в модели.
- Затем построена таблица подстановки с данными по продаже книг по месяцам в течение года. Для расчета прогнозируемых значений продаж используется построенное менеджером соотношение, задающее продажи книг. Общий вид его приведен выше. В терминах ячеек Excel эта функция записана в ячейку B42 и имеет вид:
=A37 + B37*A40 +C37*A40*A40 +D37*Рек +E37*Кон*Цен
Эта функция и используется в таблице подстановки, расположенной в ячейках B41:N42.
Значения используемых в расчетах параметров видны на рисунке. - Далее вычисляется период продаж T. В соответствующей ячейке - B49 вызывается пользовательская функция ПериодПродаж:
=ПериодПродаж(C42:N42; Тир)
У нее на входе два параметра. Первый - объект Range, задающий продажи книг, определенные в таблице подстановки на предыдущем этапе; второй - тираж книг. В результате функция возвращает количество месяцев, в течение которых распродан тираж. Если он не распродан в течение года, возвращается число 13. Вот текст этой простой функции:
Public Function ПериодПродаж(Sails As Variant, Tir As Integer) As Integer 'Вычисляет число месяцев, в течение которых распродан тираж. 'Если тираж не распродан в течение года, возвращается число 13 'Параметр Sails задает продажи по месяцам, Tir - объем тиража Sum = 0 For i = 1 To 12 Sum = Sum + Sails.Cells(i) If Sum >= Tir Then Exit For Next i ПериодПродаж = i End Function - Затем вычисляется количество проданных книг по формуле:
If T < 13 Then N = Тир Else N = SumNI
где SumNI посчитано заранее вместе с таблицей подстановки. Для реализации данного соотношения в соответствующую ячейку - D49 записана формула:
=ЕСЛИ(B49<13;J37;O42) - На следующем шаге в ячейку - H49, задающую доход, я записал формулу, его вычисляющую:
=D49*Цен*Себ -G37*Рек -H37*Тир*Себ -I37*B49
Общую формулу определения дохода я приводил выше.
На этом завершается подготовительный этап работы по формированию на рабочем листе нужной модели. Теперь модель определена, - пора задать сценарии. Вручную это делается так. В меню "Сервис" выбирается пункт "Сценарии", а в открывшемся окне Диспетчера сценариев - нужная кнопка. Для первоначального создания сценариев служит кнопка "Добавить". Вот это окно:
Рис. 8.32. Окно диспетчера сценариев
В окне добавления сценария указывается его имя, даются ссылки на изменяемые ячейки, устанавливается защита сценария. Вот его вид:
Рис. 8.33. Добавление сценария
=A37 + B37*A40 +C37*A40*A40 +D37*Рек +E37*Кон*Цен
Эта функция и используется в таблице подстановки, расположенной в ячейках B41:N42.
Значения используемых в расчетах параметров видны на рисунке.
=ПериодПродаж(C42:N42; Тир)
У нее на входе два параметра. Первый - объект Range, задающий продажи книг, определенные в таблице подстановки на предыдущем этапе; второй - тираж книг. В результате функция возвращает количество месяцев, в течение которых распродан тираж. Если он не распродан в течение года, возвращается число 13. Вот текст этой простой функции:
Public Function ПериодПродаж(Sails As Variant, Tir As Integer) As Integer 'Вычисляет число месяцев, в течение которых распродан тираж. 'Если тираж не распродан в течение года, возвращается число 13 'Параметр Sails задает продажи по месяцам, Tir - объем тиража Sum = 0 For i = 1 To 12 Sum = Sum + Sails.Cells(i) If Sum >= Tir Then Exit For Next i ПериодПродаж = i End Function
If T < 13 Then N = Тир Else N = SumNI
где SumNI посчитано заранее вместе с таблицей подстановки. Для реализации данного соотношения в соответствующую ячейку - D49 записана формула:
=ЕСЛИ(B49<13;J37;O42)
=D49*Цен*Себ -G37*Рек -H37*Тир*Себ -I37*B49
Общую формулу определения дохода я приводил выше.
На этом завершается подготовительный этап работы по формированию на рабочем листе нужной модели. Теперь модель определена, - пора задать сценарии. Вручную это делается так. В меню "Сервис" выбирается пункт "Сценарии", а в открывшемся окне Диспетчера сценариев - нужная кнопка. Для первоначального создания сценариев служит кнопка "Добавить". Вот это окно:
Рис. 8.32. Окно диспетчера сценариев
В окне добавления сценария указывается его имя, даются ссылки на изменяемые ячейки, устанавливается защита сценария. Вот его вид:
Рис. 8.33. Добавление сценария
В следующем окне задаются значения изменяемых ячеек, устанавливаемых сценарием:
Рис. 8.34. Установка значений параметров, заданных сценарием
Последовательно можно задать нужное количество сценариев. Но их можно добавлять в любой момент, изменять возможные значения его параметров. И главное, сценарии можно выполнять, и тогда все соотношения, определенные моделью, будут посчитаны для заданного сценарием набора значений управляющих параметров.
Заключительный шаг при работе со сценариями - подведение итогов. Щелкнув в окне Диспетчера кнопку "Отчет" и выбрав один из двух типов отчета, Вы получите итоговый отчет, позволяющий обосновать принимаемое решение:
увеличить изображение
Рис. 8.35. Отчет по результатам вычисления сценариев
Кнопки в левом поле сценария позволяют скрыть или развернуть для показа его отдельные части. В примере вся информация о сценариях показана. С содержательной точки зрения главным итогом является обоснование принятого менеджером решения применить сценарий "Нормальный тираж" - он и обеспечивает максимальный выигрыш.
