Анализ деятельности офиса

Сценарии

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

Итак, пусть результирующая функция F(a1,a2, …an) зависит от n параметров. Сценарием будем называть набор значений этих параметров. Добавить новый сценарий в коллекцию означает ввести новый набор значений параметров и связать с ним имя сценария. Ячейки, хранящие параметры, на которые ссылается функция F, называются изменяемыми. При выборе сценария в них будут посланы значения, заданные этим сценарием. После чего будет вычислено соответствующее значение результирующей функции. Результаты вычислений по всем сценариям можно объединить в одной сводной таблице.

Такова основная идея сценариев. Их можно рассматривать, как некоторое обобщение таблиц подстановки. Как обычно, создать и анализировать сценарии можно вручную или программно. Мы рассмотрим оба способа. Но вначале обсудим задачу, требующую введения сценариев.

Задача менеджера

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

Дадим имена параметрам, которыми может управлять менеджер: Тир, Рек и Цен. Менеджер хотел бы подобрать их значения так, чтобы оптимизировать доход от выпуска книги. Для начала он решил ограничиться тремя возможными вариантами (сценариями). Приведем их названия и соответствующие значения параметров:

• Сценарий(1). МинимальныйТираж - (Тир = 5000, Рек = 0, Цен = 1,5)
• Сценарий(2). НормальныйТираж - (Тир = 10000, Рек = 2, Цен = 2)
• Сценарий(3). МаксимальныйТираж - (Тир = 30000, Рек = 5, Цен = 2,5)

Рассмотрим теперь, как доход связан с параметрами, управляемыми менеджером. Конечно, можно было бы написать совсем простую функцию. Чтобы научиться работать со сценариями, вид функции не важен. Но мы усложним задачу и напишем нечто правдоподобное. Доход зависит от продаж, а чтобы их прогнозировать, желательно иметь соответствующую модель. Наш менеджер уже построил модель продаж для среднесрочных прогнозов. Но сейчас ему нужна общая модель, подходящая для полного (годового) цикла продаж. Обобщая данные по продажам выпущенных книг, менеджер построил такую модель и получил соотношение, позволяющее рассчитать ожидаемое количество проданных книг в каждом месяце в течение года с момента выхода книги. Вот общий вид этого соотношения:

Продажи книг =a0+a1*t+a2*t^2+a3*Рек+a4*Кон*Цен

Зависимость продаж книги во времени можно описать квадратичным полиномом с отрицательным коэффициентом a2 при t^2. Это соответствует тому, что вначале спрос на книгу растет, достигает пика на рассматриваемом временном интервале и идет на убыль. Но на спрос влияют и другие факторы. Так, элемент a3*Рек отражает увеличение спроса, вызванное улучшением рекламы. Высокие надбавки на цену по отношению к себестоимости снижают спрос при наличии конкурирующих книг: коэффициент a4 всегда отрицательный. Оценки параметров a0, a1, a2, a3 и a4 менеджер получил по результатам измерений, используя функцию ЛИНЕЙН.

Второе ключевое соотношение связывает доход с количеством проданных книг с учетом произведенных затрат на их выпуск:

Доход = N * Цен * Себ - b1*Рек - b2*Тир*Себ -b3*T

Здесь N - это проданное количество книг, T - время продажи (в месяцах), Себ - себестоимость книги. Доход, согласно этому соотношению, зависит от количества проданных книг и той надбавки (Цен), которую менеджер решил установить на цену. Расходы определяются затратами на выпуск всего тиража, затратами на рекламу и затратами на продажу в течение периода T. Чтобы модель получила законченный вид, скажем, как считаются N и T.

N = min(Продажи книг(tI), Тир)

Это соотношение отражает тот очевидный факт, что при хорошем спросе весь тираж может быть распродан быстрее, чем за год, и тогда N совпадает с Тир. В случае неудачи за год будет продано N книг, возможно существенно меньше, чем полный тираж. Вместе с N считается и T - количество месяцев, за которое удалось распродать весь тираж. Заметьте, я не привожу соотношение для расчета T. Хотя алгоритм его расчета очевиден, его не удается описать одной формулой, и мне пришлось реализовать его программно отдельной процедурой с именем "ПериодПродаж", текст которой приведу чуть позже.

Применение сценариев для решения задачи менеджера

Итак, сформулирована задача, в которой имеется результирующая функция, задающая доход. Эта функция зависит от многих параметров. Некоторыми из них можно управлять. Задача, которую должен решить менеджер для оптимизации дохода, заключается в поиске подходящих значений этих параметров. Конечно, можно считать, что наилучшие значения этих параметров менеджер мог бы получить, если бы попытался решать свою задачу как задачу оптимизации. Но здесь не все так просто. Точная формулировка задачи оптимизации потребовала бы дополнительных усилий, трудно было бы сформулировать некоторые ограничения, например, условия накладываемые типографией. Можно отметить и сложность решения оптимизационных задач. Кроме того, часто нет смысла стрелять из пушки по воробьям и искать точное решение в условиях, когда сама модель и ее параметры далеко не точны и отражают лишь суть дела.

В этих типичных условиях сценарии имеют большое практическое значение. Здесь пользователю предоставляется возможность рассмотреть наиболее разумные варианты. А опытный пользователь в своем деле эксперт - всегда знает, где лежит подходящее решение. Поэтому ему достаточно обычно просмотреть несколько возможных вариантов и выбрать наилучший среди них. Варианты, или, как их называют, сценарии, могут быть предложены разными специалистами. Отчет по результатам применения различных сценариев позволяет обосновать принятое решение.

Я специально построил довольно сложную результирующую функцию, по ходу вычисления которой приходится выполнять сложные расчеты, в том числе обращаться к процедурам, написанным на 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

  где SumN_I посчитано заранее вместе с таблицей подстановки. Для реализации данного соотношения в соответствующую ячейку - D49 записана формула:

  =ЕСЛИ(B49<13;J37;O42)
• На следующем шаге в ячейку - H49, задающую доход, я записал формулу, его вычисляющую:

  =D49*Цен*Себ -G37*Рек -H37*Тир*Себ -I37*B49

  Общую формулу определения дохода я приводил выше.

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

Рис. 8.32. Окно диспетчера сценариев

В окне добавления сценария указывается его имя, даются ссылки на изменяемые ячейки, устанавливается защита сценария. Вот его вид:

Рис. 8.33. Добавление сценария