Модуль "Математические и статистические методы анализа данных в MS Excel"

Модуль "Математические и статистические методы анализа данных в MS Excel"

Сценарный подход

Сценарный подход обеспечивает решение исследовательских задач типа «Что–Если (What - If)?».

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

Традиционно с помощью сценарного подхода решаются задачи следующих типов:

  оценка чувствительности модели к изменению значений исходных данных;

  оценка устойчивости результатов моделирования;

  прогнозирования с учетом рисков («наилучший», «наихудший», «наиболее вероятный» наборы значений исходных данных и т.п.) и др.

Сценарный подход позволяет выполнить многовариантные расчеты без необходимости дублирования расчетных формул модели. Для использования информационной технологии сценарного подхода следует:

- классифицировать параметры расчетной модели на категории:

задаваемые (ограничения модели, не подлежащие изменению),

выбираемые (значения задаются в процессе исследования модели);

результатные;

- разместить выбираемые параметры расчетной модели в ячейках одного листа;

- проанализировать связи результатных параметров с исходными данными — команда Сервис, Зависимости формул, Влияющие ячейки;

- выделить выбираемые параметры, принимающие вариантные значения для создания сценариев.

Сценарный подход имеет ряд ограничений:

1.  Каждый сценарий должен получить уникальное имя — не более 63 символов;

2.  В сценарий входят ячейки только одного листа;

3.  Ячейки сценариев не должны содержать формул;

4.  Число ячеек в сценариев не более 32;

5.  Число сценариев, выводимых в отчете, не более 251.

Для работы со сценариями используется диалоговое окно «Диспетчер сценариев», вызываемое с помощью команды Сервис, Сценарии.

Кнопка Добавить обеспечивает открытие другого диалогового окна - «Добавление сценария» (рис.1), в котором создается новый сценарий. В окне вводится произвольное имя сценария, указываются изменяемые ячейки — один или более диапазонов ячеек, разделяемых символом; (точка с запятой). При указании несмежных диапазонов ячеек следует нажать клавишу Ctrl.

Рис. 1

Для каждого сценария можно вводить примечание (произвольный текст), обеспечить защиту от изменений на защищенном листе книги (защита сценария действует только после выполнения команды Сервис, Защита, Защитить лист). Флажок Скрыть позволяет скрыть сценарий в списке сценариев на защищенном листе.

Сценарии можно просматривать и редактировать — кнопка Изменить, ненужные сценарии можно удалить  кнопка Удалить. С помощью кнопки Вывести выполняется подстановка значений в ячейки таблицы и вычисление зависящих от них формул. По всем сценариям листа можно подготовить отчет в виде структурной или сводной таблицы — кнопка Отчет. В отчет включаются значения ячеек, входящих в сценарии, и результат вычисления указанных ячеек с формулами, расположенных в произвольном месте рабочей книги. Кнопка Объединить позволяет подготовить отчет по сценариям нескольких листов или книг.

Точка безубыточности вычисляется с учетом вероятности сбыта продукции.

1.  Создать рабочую книгу MATSTAT.xls.

2.  На листе РАСЧЕТЫ подготовить данные согласно рис. 2.

	A	B	C
1	Цена единицы продукции	100,00р.
2	Прямые расходы
3	Материалы	20,00р.
4	Зарплата	30,00р.
5	Постоянные расходы	12 000,00р.
6	Объем выпуска	600
7	Вероятность реализации, %	80
8	Объем спроса
9	Ожидаемая выручка
10	Итого затраты
11	Расчетная прибыль
12	Прибыль на единицу продукции
13	Точка безубыточности (с учетом вероятности реализации)
14	Точка безубыточности (без учета вероятности реализации)

Рис. 2

3. Создать элемент управления «Полоса прокрутки» для удобства выбора значения Вероятность реализации, %:

§  команда Вид, Панели инструментов, Формы;

§  выбрать элемент Полоса прокрутки и поместить его в таблицу (около ячейки В7);

§  выделить элемент управления «Полоса прокрутки», выполнить контекстную команду Формат объекта; в диалоговом окне «Формат элемента управления» на вкладке «Элемент управления» указать: минимальное значение — 0, максимальное значение — 100, шаг изменение — 1, связь с ячейкой В7.

4.  В ячейки В1, В3–В7 вводятся первичные данные.

5.  В ячейки В2, В8–В14 вводятся формулы:

Ячейка	Формула
В2	=В3+В4
В8	=В6*В7/100
В9	=В8*В1
В10	=В5 + (В3+В4)*В6
В11	=В9-В10
В12	=В11/В6
В13	=В5/(В1*В7/100 – В3 – В4)
В14	=В5/(В1 – В3 – В4)

 6. Создать с помощью команды Сервис, Сценарии сценарии, включив в них ячейки В1, В3–В5 (табл.1).
 Сценарии соответствуют последовательному изменению значений исходных данных на 10%.

Таблица 1

Сценарии
Ячейка	1	2	3	4	5	6
B1	100р.	110р.	100р.	100р.	100р.	110р.
B3	20р.	20р.	22р.	20р.	20р.	22р.
B4	30р.	30р.	30р.	33р.	30р.	33р.
B5	12000р.	12000р.	12000р.	12000р.	13200р.	13200р.

 7. Просмотреть результаты подстановки различных сценариев, если значение Вероятность реализации, % изменяется в диапазоне 50% — 100% , использовать кнопку Вывести для вывода сценариев.

8. Подготовить отчет в виде структурной таблицы — кнопка Отчет для анализа В13. Исходные данные Объем выпуска — 1000,  Вероятность реализации, % — 80.

9. Графически изобразить значение точки безубыточности для различных сценариев (по сравнению с вариантом 1), тип диаграммы — График. Определить параметр, оказывающий наибольшее влияние на результат.

10. Сохранить рабочую книгу MATSTAT.xls.

В начало ...

ВНИМАНИЕ. После завершения работы проверить наличие файлов: