1.3. Пример использование таблицы подстановок
Ранее в лаб. раб.2 (прогноз) - было построено уравнение регрессии, в котором продажи зависят от уровня рекламы и количества конкурирующих книг. Менеджер уже использовал его для прогноза состояния продажи книги и построил соответствующие графики, демонстрирующие прогнозируемую зависимость продаж от уровня рекламы. Однако графики были построены для фиксированного числа конкурирующих книг. Менеджер не определил еще окончательно, каков будет уровень рекламы в точке прогноза, и тем более он не знает точного значения количества конкурентов.
Поэтому он решает построить таблицу подстановки, чтобы оценить все реально возможные варианты. Вот что получилось:
Рис. 3.1. Таблица подстановки, используемая в анализе "Что, если ...?"
Эта таблица позволяет получить ответы на вопросы: каков прогноз на продажу книг на следующий месяц и что будет, если появится новый конкурент, и что будет, если повысить уровень рекламы?
2. Сценарии
Таблицы подстановки применимы, когда результат, точнее функция, его вычисляющая, зависит максимум от двух параметров. При анализе более сложных моделей, когда результат зависит от большего количества факторов, следует использовать другое средство - сценарии. Заметьте, даже в рассматриваемой нами достаточно простой ситуации уровень продаж зависит от трех параметров - времени, уровня рекламы и числа конкурентов.
Итак, пусть результирующая функция F(a1,a2, …an) зависит от n параметров.
Сценарием будем называть набор значений этих параметров. Добавить новый сценарий в коллекцию означает ввести новый набор значений параметров и связать с ним имя сценария. Ячейки, хранящие параметры, на которые ссылается функция F, называются изменяемыми. При выборе сценария в них будут посланы значения, заданные этим сценарием. После чего будет вычислено соответствующее значение результирующей функции. Результаты вычислений по всем сценариям можно объединить в одной сводной таблице.
Такова основная идея сценариев. Их можно рассматривать, как некоторое обобщение таблиц подстановки.
Рассмотрим задачу, требующую введения сценариев.
Постановка задачи:
Управляющий должен принять важные решения в связи с выходом новой книги. Он должен определить тираж книги, установить уровень рекламы и назначить цену, точнее коэффициент надбавки по отношению к себестоимости книги. Целью менеджера является получение максимального дохода от выпуска книги.
Дадим имена параметрам, которыми может управлять менеджер: Тир, Рек и Цен.
Менеджер хотел бы подобрать их значения так, чтобы оптимизировать доход от выпуска книги. Для начала он решил ограничиться тремя возможными вариантами (сценариями). Приведем их названия и соответствующие значения параметров:
Сценарий(1). МинимальныйТираж - (Тир = 5000, Рек = 0, Цен = 1,5)
Сценарий(2). НормальныйТираж - (Тир = 10000, Рек = 2, Цен = 2)
Сценарий(3). МаксимальныйТираж - (Тир = 30000, Рек = 5, Цен = 2,5)
Рассмотрим теперь, как доход связан с параметрами, управляемыми менеджером. Конечно, можно было бы написать совсем простую функцию. Чтобы научиться работать со сценариями, вид функции не важен. Но мы усложним задачу и напишем нечто правдоподобное.
Доход зависит от продаж, а чтобы их прогнозировать, желательно иметь соответствующую модель.
Менеджер уже построил модель продаж для среднесрочных прогнозов. (см. лаб.2)
Но сейчас нужна общая модель, подходящая для полного (годового) цикла продаж. Обобщая данные по продажам выпущенных книг, менеджер построил такую модель и получил соотношение, позволяющее рассчитать ожидаемое количество проданных книг в каждом месяце в течение года с момента выхода книги.
Пусть общий вид этого соотношения:
Продажи книг = а0 + а1 t + a2 * t2 + a3 * Рек + а4 Кон * Цен
Зависимость продаж книги во времени можно описать квадратичным полиномом с отрицательным коэффициентом а2 при t2 . Это соответствует тому, что вначале спрос на книгу растет, достигает пика на рассматриваемом временном интервале и идет на убыль. Но на спрос влияют и другие факторы. Так, элемент a3 * Рек отражает увеличение спроса, вызванное улучшением рекламы. Высокие надбавки на цену по отношению к себестоимости снижают спрос при наличии конкурирующих книг: коэффициент а4 всегда отрицательный.
Оценки параметров а0, а1, а2, а3, а4 менеджер получил по результатам измерений с использованием функции ЛИНЕЙН (ниже по тексту рис.3.2)
Второе ключевое соотношение связывает доход с количеством проданных книг с учетом произведенных затрат на их выпуск:
Доход = N * Цен * Себ - b1*Рек - b2*Тир*Себ -b3*T
Здесь N - это проданное количество книг, T - время продажи (в месяцах), Себ - себестоимость книги. Доход, согласно этому соотношению, зависит от количества проданных книг и той надбавки (Цен), которую менеджер решил установить на цену. Расходы определяются затратами на выпуск всего тиража, затратами на рекламу и затратами на продажу в течение периода T. Чтобы модель получила законченный вид, посмотрим , как считаются N и T.
N = min(Продажи книг(tI), Тир)
Это соотношение отражает тот очевидный факт, что при хорошем спросе весь тираж может быть распродан быстрее, чем за год, и тогда N совпадает с Тир. В случае неудачи за год будет продано N книг, возможно существенно меньше, чем полный тираж. Вместе с N считается и T - количество месяцев, за которое удалось распродать весь тираж.
Заметьте, для расчета T- формулы нет. Алгоритм его расчета реализован программно отдельной процедурой с именем "ПериодПродаж", текст которой ниже.
Таким образом, сформулирована задача, в которой имеется результирующая функция, задающая доход. Эта функция зависит от многих параметров. Некоторыми из них можно управлять.
Задача, которую должен решить менеджер для оптимизации дохода, заключается в поиске подходящих значений этих параметров. Конечно, можно считать, что наилучшие значения этих параметров менеджер мог бы получить, если бы попытался решать свою задачу как задачу оптимизации. Но здесь не все так просто. Точная формулировка задачи оптимизации потребовала бы дополнительных усилий, трудно было бы сформулировать некоторые ограничения, например, условия накладываемые типографией. Можно отметить и сложность решения оптимизационных задач. Кроме того, часто нет смысла стрелять из пушки по воробьям и искать точное решение в условиях, когда сама модель и ее параметры далеко не точны и отражают лишь суть дела.
В этих типичных условиях сценарии имеют большое практическое значение. Здесь пользователю предоставляется возможность рассмотреть наиболее разумные варианты. А опытный пользователь в своем деле эксперт - всегда знает, где лежит подходящее решение. Поэтому ему достаточно обычно просмотреть несколько возможных вариантов и выбрать наилучший среди них. Отчет по результатам применения различных сценариев позволяет обосновать принятое решение.
Пример решения задачи с применением сценариев
Прежде чем обращаться к сценариям предстоит довольно большая работа по формированию на рабочем листе модели исследуемого процесса.
В этой модели нужно определить ту функцию, которая подлежит оптимизации, параметры, которые будут изменяться в сценариях. На нашем примере можно убедиться, что это, достаточно серьезная работа. Вот, как выглядит рабочий лист, на котором эта работа уже проделана:
Рис. 3.2. Решение задачи менеджера с применением сценариев
Опишем последовательно этапы решения задачи:
На рабочем листе Excel вначале выписаны все параметры, используемые в модели.
Затем построена таблица подстановки с данными по продаже книг по месяцам в течение года. Для расчета прогнозируемых значений продаж используется построенное менеджером соотношение, задающее продажи книг. Общий вид его приведен выше. В терминах ячеек Excel эта функция записана в ячейку B42 и имеет вид:
=A37 + B37*A40 +C37*A40*A40 +D37*Рек +E37*Кон*Цен
Эта функция и используется в таблице подстановки, расположенной в ячейках B41:N42.
Значения используемых в расчетах параметров видны на рисунке 3.2.
Далее вычисляется период продаж 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
Общую формулу определения дохода см. выше.
На этом завершается подготовительный этап работы по формированию на рабочем листе нужной модели.
Теперь модель определена, - пора задать сценарии.
Вручную это делается так. В меню "Сервис" выбирается пункт "Сценарии", а в открывшемся окне Диспетчера сценариев - нужная кнопка. Для первоначального создания сценариев служит кнопка "Добавить". Вот это окно:
Рис. 3.3. Окно диспетчера сценариев
В окне добавления сценария указывается его имя, даются ссылки на изменяемые ячейки, устанавливается защита сценария. Вот его вид:
Рис. 3.4. Добавление сценария
В следующем окне задаются значения изменяемых ячеек, устанавливаемых сценарием:
Рис. 3.5. Установка значений параметров, заданных сценарием
Последовательно можно задать нужное количество сценариев. Но их можно добавлять в любой момент, изменять возможные значения его параметров.
И главное, сценарии можно выполнять, и тогда все соотношения, определенные моделью, будут посчитаны для заданного сценарием набора значений управляющих параметров.
Заключительный шаг при работе со сценариями - подведение итогов. Щелкнув в окне Диспетчера кнопку "Отчет" и выбрав один из двух типов отчета, Вы получите итоговый отчет, позволяющий обосновать принимаемое решение:
Рис.3.6. Отчет по результатам вычисления сценариев
Кнопки в левом поле сценария позволяют скрыть или развернуть для показа его отдельные части. В примере вся информация о сценариях показана. С содержательной точки зрения главным итогом является обоснование принятого менеджером решения применить сценарий "Нормальный тираж" - он и обеспечивает максимальный выигрыш.
Можно включить в итоговый отчет сведения о трех параметрах: доходе, периоде продажи и количестве проданных книг.
Поскольку стандартный тип отчета уже приведен, то взгляните, как выглядит сводная таблица, построенная в результате выполнения этой процедуры:
Рис. .3.7. Сводная таблица построенная по результатам выполнения сценариев