!_МУ для лаб. Методические указания для выполнения лабораторных работ по теме анализ и моделирование деятельности организации с целью принятия управленческих решений
 Скачать 2.84 Mb.
|
Средства анализа данных: Таблицы подстановок, Сценарии и Поиск решенияЦель: освоить средства анализа данных для принятия управленческих решений1.Таблица подстановок - одно из средств анализа данных 1.1. Задача, которая приводит к построению таблицы подстановок Рассмотрим набор функций, зависящих от одного и того же параметра: F1(a), F2(a), …Fm(a). Пусть каждая из этих функций задается формулой Excel. Пусть также требуется проанализировать зависимость этих функций от значений параметра a. Обычно нас интересуют результаты для конечного набора значений параметра - a1, a2, …an. В этом случае все, что нужно для анализа, - это построить прямоугольную таблицу размерности n*m, элементами которой будут значения Fj(ai). Excel позволяет без особого труда построить такую таблицу. Таблицы подстановок упрощают решение этой задачи. Чтобы вручную построить такую таблицу, надо записать в столбец значения ai, в строку, расположенную на одну ячейку выше и правее, записать формулы Fj(Ain). Все формулы должны ссылаться на одну и ту же ячейку Ain - ячейку ввода. Можно, конечно, значения параметра записать в строку, а формулы в столбец. Основное требование к расположению формул и значений параметра состоит в том, чтобы они определяли прямоугольную область таблицы и воспринимались как заголовки ее строк и столбцов. Проделав эту подготовительную работу, достаточно выделить прямоугольную область, занятую таблицей, включая заголовки, и выбрать в меню "Данные" пункт "Таблица подстановок". В появившемся окне нужно задать ссылку на ячейку ввода. Заметьте, если значения параметра располагаются в столбец, то ссылку на ячейку ввода нужно задавать в окне строк, а не в окне столбцов. По щелчку кнопки OK таблица значений Fj(ai) будет автоматически построена. 1.2 Другая задача, приводящая к таблице подстановок, состоит в том, что рассматривается только одна функция F(a,b), но теперь зависящая от двух параметров. Элементами таблицы являются значения этой функции F(ai, bj). В роли заголовков строк и столбцов выступают значения ai и bj. Для записи формулы осталось одно свободное место - ячейка в левом верхнем углу таблицы. Формула, записанная в нее, ссылается теперь на две ячейки ввода - ячейку ввода строки и ячейку ввода столбца. Этим нюансом в расположении заголовков отличаются подготовительные действия по созданию таблицы подстановок в первом и во втором случае. Остальные действия аналогичны. И здесь нужно быть аккуратным в выборе окон при задании ссылок. . Конечно, таблицы подстановки - не столь уж мощное средство анализа данных. Собственно говоря, никакого анализа данных они не выполняют. Это лишь часто используемое средство, облегчающее построение таблицы данных для ее визуального анализа. Таблицу анализирует сам пользователь, исходя из содержательных соображений. Обычно, только он может понять, какие значения параметров a и b наиболее подходят для его целей. Хотя, конечно, может существовать и формальный алгоритм выбора из таблицы наилучшего значения. 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- формулы нет. Алгоритм его расчета реализован программно отдельной процедурой с именем "ПериодПродаж", текст которой ниже. |