Файл: Методические указания для выполнения лабораторных работ по теме анализ и моделирование деятельности организации с целью принятия управленческих решений.doc
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 11.04.2024
Просмотров: 57
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
N совпадает с Тир. В случае неудачи за год будет продано N книг, возможно существенно меньше, чем полный тираж. Вместе с N считается и T - количество месяцев, за которое удалось распродать весь тираж.
Заметьте, для расчета T- формулы нет. Алгоритм его расчета реализован программно отдельной процедурой с именем "ПериодПродаж", текст которой ниже.
В этих типичных условиях сценарии имеют большое практическое значение. Здесь пользователю предоставляется возможность рассмотреть наиболее разумные варианты. А опытный пользователь в своем деле эксперт - всегда знает, где лежит подходящее решение. Поэтому ему достаточно обычно просмотреть несколько возможных вариантов и выбрать наилучший среди них. Отчет по результатам применения различных сценариев позволяет обосновать принятое решение.
Пример решения задачи с применением сценариев
Прежде чем обращаться к сценариям предстоит довольно большая работа по формированию на рабочем листе модели исследуемого процесса.
В этой модели нужно определить ту функцию, которая подлежит оптимизации, параметры, которые будут изменяться в сценариях. На нашем примере можно убедиться, что это, достаточно серьезная работа. Вот, как выглядит рабочий лист, на котором эта работа уже проделана:
Рис. 3.2. Решение задачи менеджера с применением сценариев
Опишем последовательно этапы решения задачи:
=A37 + B37*A40 +C37*A40*A40 +D37*Рек +E37*Кон*Цен
Эта функция и используется в таблице подстановки, расположенной в ячейках B41:N42.
Значения используемых в расчетах параметров видны на рисунке 3.2.
=ПериодПродаж(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
Общую формулу определения дохода см. выше.
На этом завершается подготовительный этап работы по формированию на рабочем листе нужной модели.
Теперь модель определена, - пора задать сценарии.
Вручную это делается так. В меню "Сервис" выбирается пункт "Сценарии", а в открывшемся окне Диспетчера сценариев - нужная кнопка. Для первоначального создания сценариев служит кнопка "Добавить". Вот это окно:
Рис. 3.3. Окно диспетчера сценариев
В окне добавления сценария указывается его имя, даются ссылки на изменяемые ячейки, устанавливается защита сценария. Вот его вид:
Рис. 3.4. Добавление сценария
В следующем окне задаются значения изменяемых ячеек, устанавливаемых сценарием:
Рис. 3.5. Установка значений параметров, заданных сценарием
Последовательно можно задать нужное количество сценариев. Но их можно добавлять в любой момент, изменять возможные значения его параметров.
И главное, сценарии можно выполнять, и тогда все соотношения, определенные моделью, будут посчитаны для заданного сценарием набора значений управляющих параметров.
Заключительный шаг при работе со сценариями - подведение итогов. Щелкнув в окне Диспетчера кнопку "Отчет" и выбрав один из двух типов отчета, Вы получите итоговый отчет, позволяющий обосновать принимаемое решение:
Рис.3.6. Отчет по результатам вычисления сценариев
Кнопки в левом поле сценария позволяют скрыть или развернуть для показа его отдельные части. В примере вся информация о сценариях показана. С содержательной точки зрения главным итогом является обоснование принятого менеджером решения применить сценарий "Нормальный тираж" - он и обеспечивает максимальный выигрыш.
Можно включить в итоговый отчет сведения о трех параметрах: доходе, периоде продажи и количестве проданных книг.
Поскольку стандартный тип отчета уже приведен, то взгляните, как выглядит сводная таблица, построенная в результате выполнения этой процедуры:
Рис. .3.7. Сводная таблица построенная по результатам выполнения сценариев
Средства оптимизации - мощные инструменты, используемые в анализе "Что, если ..?". Рассмотрим вначале то, что попроще. "Подбор Параметра" позволяет для функции одного параметра
F(a) подобрать, если можно, такое значение параметра a^, что функция в этой точке будет иметь заранее заданное значение F* = F(a^).
Менеджер, найдя наилучший вариант сценария, спросил себя: "Что, если слегка увеличить тираж? Увеличу ли я за счет этого доход до 200 000?"
Чтобы ответить на эти вопросы, он выбрал в меню "Сервис" пункт "Подбор параметра". В появившемся окне он задал доход как целевую функцию, 200000 - как желаемое значение дохода, Тираж - как параметр (изменяемую ячейку), значение которого нужно подобрать так, чтобы достичь заданной величины дохода. Но сделать этого невозможно.
В такой постановке у задачи решения нет, о чем и было ему сообщено. Менеджеру пришлось умерить свои аппетиты: он повторно вызвал "Подбор параметра ", задав теперь значение дохода, равное 180000. Теперь решение удалось найти. Оно достигается при тираже, равном 10900. Менеджер округлил значение тиража до 10500, что принесло увеличение дохода еще на 10000. На этом менеджер и остановился. Найденное им решение практически оптимально.
И в заключение взгляните на окошко, уведомляющее о достигнутом доходе:
Рис. 3.8. Уведомление о достигнутом доходе
4. Задание
4.1. Изучить теоретический материал методических указаний.
4.2. Выполнить пример, предлагаемый в методических указаниях.
4.3. Выполнить задание по варианту (см. ниже): постановку и решение задачи оптимизации с применением сценариев для показателей деятельности предприятия, а также ее анализ для своих данных.
4.4. Сделать отчет, сохраняя копии экрана в текстовом документе.
Варианты заданий
Заметьте, для расчета 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. Сводная таблица построенная по результатам выполнения сценариев
3.Оптимизация и анализ "Что, если ...?"
Средства оптимизации - мощные инструменты, используемые в анализе "Что, если ..?". Рассмотрим вначале то, что попроще. "Подбор Параметра" позволяет для функции одного параметра
F(a) подобрать, если можно, такое значение параметра a^, что функция в этой точке будет иметь заранее заданное значение F* = F(a^).
Менеджер, найдя наилучший вариант сценария, спросил себя: "Что, если слегка увеличить тираж? Увеличу ли я за счет этого доход до 200 000?"
Чтобы ответить на эти вопросы, он выбрал в меню "Сервис" пункт "Подбор параметра". В появившемся окне он задал доход как целевую функцию, 200000 - как желаемое значение дохода, Тираж - как параметр (изменяемую ячейку), значение которого нужно подобрать так, чтобы достичь заданной величины дохода. Но сделать этого невозможно.
В такой постановке у задачи решения нет, о чем и было ему сообщено. Менеджеру пришлось умерить свои аппетиты: он повторно вызвал "Подбор параметра ", задав теперь значение дохода, равное 180000. Теперь решение удалось найти. Оно достигается при тираже, равном 10900. Менеджер округлил значение тиража до 10500, что принесло увеличение дохода еще на 10000. На этом менеджер и остановился. Найденное им решение практически оптимально.
И в заключение взгляните на окошко, уведомляющее о достигнутом доходе:
Рис. 3.8. Уведомление о достигнутом доходе
4. Задание
4.1. Изучить теоретический материал методических указаний.
4.2. Выполнить пример, предлагаемый в методических указаниях.
4.3. Выполнить задание по варианту (см. ниже): постановку и решение задачи оптимизации с применением сценариев для показателей деятельности предприятия, а также ее анализ для своих данных.
4.4. Сделать отчет, сохраняя копии экрана в текстовом документе.
Варианты заданий
| № варианта | Вид деятельности | Влияющие факторы | ||
| 1 | 2 | 3 | ||
| 1 | Продажа провайдерских услуг | Стоимость трафика | Конкуренты | Устойчивость и скорость передачи данных |
| 2 | Такси (заказ по телефону) | Конкуренты | Тарифы | Реклама |
| 3 | Пицца | Конкуренты | Стоимость | Рекламная политика |
| 4 | Курсы иностранных языков | Стоимость курсов | Достижения выпускников (число сдавших Toefl, TestDaf и т.д.) | Прайм время занятий |
| 5 | Бассейн | Прайм время сеансов | Отдаленность от транспорта | Стоимость абонемента |
| 6 | Автошкола | Автопарк учебных машин | Количество успешно сдавших экзамены в ГАИ | Стоимость занятий и топлива |
| 7 | Автобусные перевозки | Тарифы | Конкуренты | Безопасность |
| 8 | Каток | Реклама | Качество ледового покрытия | Цена абонемента |
| 9 | Кинотеатр | Цена билетов | Количество премьерных показов | Реклама |
| 10 | Туристическое агентство | Стоимость туров | Спектр услуг (проезд, страховка, сопровождение и т.д.) | Реклама |
| 11 | Пекарня | Конкуренты | Разнообразие ассортимента | Свежесть продуктов |
| 12 | Бензозаправка | Качество топлива | Полнота долива | Цена топлива |
| 13 | Фитнес-клуб | Реклама | Стоимость абонемента | Загруженность тренажеров (доступность и разнообразие) |
| 14 | Компьютерные курсы | Стоимость занятий | Качество и количество компьютеров и программного обеспечения | Престижность полученного сертификата |
| 15 | Шиномонтаж | Конкуренция | Стоимость услуг | Время исполнения услуг |
| 16 | Дом кофе | Конкуренция | Цена кофейных продуктов | Ассортимент |
| 17 | Автобазар | Реклама | Система скидок | Тюнинг |
| 18 | Аптечный киоск | Конкуренты | Цены | Ассортимент |
| 19 | Диагностический центр | Качество оборудования | Квалификация персонала | Стоимость диагностики |
| 20 | Типография | Цены на издания | Качество продукции | Конкуренты |