Порядок выполнения задания. Порядок выполнения задания рассмотрим на примере создания сценариев «Удвоение объема производства» и «Удвоение цены»

Порядок выполнения задания рассмотрим на примере создания сценариев «Удвоение объема производства» и «Удвоение цены».

Исходные данные представлены на рисунке 1.

1. Ярлычок листа рабочей книги назовем «Сценарии».

2. Создадим на листе «Сценарии» таблицу как показано на рисунках 1, 2.

Рис.1. Исходные данные. Режим чисел

На рисунке 2 показаны формулы, по которым рассчитывается таблица.

Рис. 2. Исходные данные. Режим формул

3. Присвоим соответствующим ячейкам таблицы имена: Цена, Объем, Прибыль (Контекстное меню ячейки – Присвоить имя).

4. Создание и сохранение сценариев.

4.1. Вкладка Данные – группа Работа с данными - кнопка Анализ «что-если» - Диспетчер сценариев. Появится диалоговое окно «Диспетчер сценариев» (рис. 3).

Рис. 3. Диалоговое окно «Диспетчер сценариев»

4.2. Щелкнем по кнопке Добавить, чтобы открыть диалоговое окно «Добавление сценария» (рис. 4).

Рис. 4. Диалоговое окно «Добавление сценария»

4.3. В поле «Название сценария» введем название первого сценария «Удвоение объема».

4.4. В поле «Изменяемые ячейки» введем ссылку на изменяемую ячейку - C6. Если изменяемых ячеек несколько, то можно сразу ввести все изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятой. Несмежные ячейки добавляются при нажатой клавише Ctrl.

4.5. В поле «Примечание» укажем, за счет изменения какого параметра найдена прибыль в этом сценарии. Нажмем кнопку ОК.

4.6. Появится диалоговое окно «Значения ячеек сценария», в котором отображаются текущие значения изменяемых ячеек. Введем удвоенное значение объема равное 140. Нажмем кнопку ОК.

4.7. Опять появится окно «Диспетчер сценариев» с именем только что созданного сценария.

Нажмем кнопку Добавить и аналогичным образом построим сценарий «Удвоение цены».

4.8. Закроем окно «Диспетчер сценариев».

5. Создадим итоговый отчет о выполнении сценариев.

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

Примечание. Если сценарии выполнялись до получения итогового отчета, то нужно привести содержимое всех переменных в исходное значение, а потом создавать отчет. Поэтому отчет удобнее создавать до выполнения сценария.

5.1. Для создания отчета с результатами выполнения всех сценариев текущего рабочего листа установим курсор на лист, в котором созданы сценарии и в окне «Диспетчер сценариев» щелкнем кнопку Отчет. Появится диалоговое окно «Отчет по сценарию» (рис. 5).

Рис. 5. Диалоговое окно «Отчет по сценарию»

В группе параметров Тип отчета выберем переключатель Структура и убедимся, что выделена ячейка с результатом выполнения сценариев. Нажмем кнопку ОК.

Excel добавит в рабочую книгу лист, содержащий итоговый отчет с названием Структура сценария (рис. 6).

Рис. 6. Отчет типа «Структура сценария»

6. Запуск сценариев.

Сценарий запускается на выполнение после анализа отчета, в случае, если именно он выбирается как наилучший. Кнопка Вывести в окне «Диспетчер сценариев» изменяет значения исходной анализируемой таблицы в соответствии с выбранным сценарием.

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

6.2. Нажмем кнопку Вывести. В изменяемых ячейках листа появятся значения, соответствующие выбранному сценарию.

Задание 2. Применение средства «Подбор параметра» в решении экономической задачи

Назовите лист рабочей книги «Подбор параметра» и выполните на нем следующие задания.

1. Определите прибыль после реализации продукции по данным таблицы 2, выбрав строку, соответствующую своему варианту.

2. Определите, как можно добиться прибыли в 15000 рублей за счет изменения цены реализации единицы продукции. Какой в этом случае должна быть цена за единицу готовой продукции?

3. Определите, как можно добиться прибыли в 15000 рублей за счет изменения переменных издержек на единицу продукции. Какими будут в этом случае переменные издержки?

4. Определите, как можно добиться прибыли в 15000 рублей за счет изменения постоянных издержек. Какими они должны быть в этом случае?

Для вычисления прибыли используйте следующую формулу 1:

P = (X*Z*(1 – V/Z)) – C, (1)

где: Х – критический объем производства;

С – постоянные издержки (руб.);

Z – цена реализации единицы продукции (руб.);

V – переменные издержки за единицу продукции (руб.).

Получите следующие распечатки:

1) после выполнения пункта 1 данного задания сделайте распечатки в режиме чисел и в режиме формул с координатной сеткой и заголовками строк и столбцов (Распечатка 5, Распечатка 6);

2) после выполнения пунктов 2, 3, 4 сделайте распечатки в режиме чисел (Распечатка 7, Распечатка 8, Распечатка 9).

Таблица 2

Варианты для выполнения задания 2

Поиск по сайту: