Принятия решений и методы прогнозирования»
Казань 2011
Составители: старший преподаватель Батаршина Р.Р.
к.с. н., доцент Степанова Ю.В.
ассистент Фатыхова Л.Г.
Рецензент: к.э.н., доцент Харитонова Р.С.
Обсуждена на заседании кафедры математики и экономической информатики, протокол № 10 от 2.06.11
Контроль качества:
методист: к.п.н., доцент Пайгунова Ю.В.
старший методист: к.э.н., доцент Калинина Т.Н.
начальник отдела УККО: к.э.н., доцент Андреева Р.Н.
Содержание
Введение …………………………………………………………………………….4
Требования к выполнению индивидуальных заданий …………………………...4
Задание 1. Создание сценариев для получения аналитических отчетов ………..5
Задание 2. Применение средства «Подбор параметра» в решении
экономической задачи …………………………………………………………….11
Задание 3. Нахождение вариантов оптимальной производственной
программы по выпуску продукции с использованием поиска решения ………15
Задание 4. Применение таблиц подстановки данных в экономических
расчетах …………………………………………………………………………….49
Рекомендуемая литература ……………………………………………………….56
Приложения ………………………………………………………………………..57
Введение
Данная методическая разработка предназначена для организации индивидуальной работы в среде MS Excel студентов 2 курса дневного отделения, обучающихся по направлениям 080100.62 «Экономика», 080200.62 «Менеджмент».
Методическая разработка подготовлена в соответствии с программой и календарно-тематическим планом курсов «Информационные системы в экономике» и «Информационные технологии в менеджменте».
Методическая разработка поможет более эффективно организовать индивидуальную работу студентов по освоению методов прогнозирования и анализа при решении экономических задач средствами MS Excel.
Методическая разработка содержит варианты индивидуальных заданий для самостоятельной работы студентов по решению экономических задач и предполагает освоение технологии применения элементов системы поддержки принятия эффективных управленческих решений, таких как подбор параметра, поиск решения, сценарии, таблицы подстановки данных.
Контроль за выполнением индивидуальных заданий осуществляется в аудитории и включает проверку работы в печатном и электронном виде и ее защиту.
Требования к выполнению индивидуальных заданий
Задание предполагает применение средства «Подбор параметра» в решении экономической задачи; анализ экономической информации на основе сценариев; применение таблиц подстановки данных и финансовых функций в экономических расчетах; нахождение варианта оптимальной производственной программы по выпуску продукции с использованием средства «Поиск решения».
При выполнении индивидуального задания каждый студент должен соблюдать следующие требования.
1. Студент должен выполнить свой вариант задания, который выбирается в соответствии с порядковым номером в списке группы.
2. Результат работы представляется в виде компьютерных распечаток в среде MS Excel. Распечатки нумеруются и располагаются в последовательности, указанной в задании.
3. Каждая распечатка должна содержать верхний колонтитул с указанием слева - фамилии, инициалов и номера группы студента, в центре – имени листа, справа – даты.
4. Титульный лист оформляется в соответствии с образцом, приведенным в Приложении 1.
5. Индивидуальные задания должны быть выполнены в срок, указанный преподавателем. После проверки работы студент должен устранить замечания преподавателя и защитить работу.
Задание 1. Создание сценариев для получения аналитических отчетов
Проанализируйте с помощью сценариев влияние на объем прибыли от реализуемой продукции различных значений цены реализации и объема производства. Исходные данные для каждого варианта приведены в таблице 1.
По данным своего варианта составьте три сценария:
· «Увеличение объема производства на 20%»;
· «Увеличение цены реализации на 20%»;
· «Увеличение цены и объема на 20%».
Постройте итоговый отчет по сценариям и проанализируйте результаты применения каждого сценария. Оцените влияние каждого из параметров на прибыль. Сделайте аналитические выводы и выполните выбранный сценарий.
Получите следующие распечатки:
1) таблица с исходными данными в режиме чисел (Распечатка 1);
2) таблица с исходными данными в режиме формул с координатной сеткой и заголовками строк и столбцов (Распечатка 2);
3) структура сценария (Распечатка 3);
4) таблица после вывода выбранного сценария (Распечатка 4).
Таблица 1
Варианты для выполнения задания 1
| № варианта | Объем производства, шт. | Цена реализации, руб. | № варианта | Объем производства, шт. | Цена реализации, руб. |
| 1500,00 | 900,00 | ||||
| 1000,00 | 850,00 | ||||
| 1300,00 | 3500,00 | ||||
| 1400,00 | 4000,00 | ||||
| 3000,00 | 2500,00 | ||||
| 1300,00 | 800,00 | ||||
| 1100,00 | 4500,00 | ||||
| 15000,00 | 10000,00 | ||||
| 992,00 | 11000,00 | ||||
| 1700,00 | 20000,00 | ||||
| 1000,00 | 500,00 | ||||
| 1200,00 | 1125,00 | ||||
| 17000,00 | 18000,00 | ||||
| 1000,00 | 950,00 |
Порядок выполнения задания
Порядок выполнения задания рассмотрим на примере создания сценариев «Удвоение объема производства» и «Удвоение цены».
Исходные данные представлены на рисунке 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.2. В окне «Диспетчер сценариев» щелкнем кнопку Отчет. Появится диалоговое окно «Отчет по сценарию» (рис. 5).
Рис. 5. Диалоговое окно «Отчет по сценарию»
В группе параметров Тип отчета выберем переключатель Структура и убедимся, что выделена ячейка с результатом выполнения сценариев. Нажмем кнопку ОК.
Excel добавит в рабочую книгу лист, содержащий итоговый отчет с названием Структура сценария (рис. 6).
Рис. 6. Отчет типа «Структура сценария»
6. Запуск сценариев.
Сценарий запускается на выполнение после анализа отчета, в случае, если именно он выбирается как наилучший. Кнопка Вывести в окне «Диспетчер сценариев» изменяет значения исходной анализируемой таблицы в соответствии с выбранным сценарием.
6.1.Установим курсор на лист, в котором были созданы сценарии и выполним команду Сервис – Сценарии.
6.2. В окне «Диспетчер сценариев» щелкнем на имени сценария, который нужно запустить на выполнение.
6.3. Нажмем кнопку Вывести. В изменяемых ячейках листа появятся значения, соответствующие выбранному сценарию.
Задание 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
| № вар. | Постоянные издержки, руб. (С) | Объем производства, шт. (Х) | Цена реализации ед. продукции, руб. (Z) | Переменные издержки за ед. продукции, руб. (V) |
| 20000,00 | 180,00 | 140,00 | ||
| 12000,00 | 150,00 | 70,00 | ||
| 30000,00 | 150,00 | 80,00 | ||
| 20000,00 | 100,00 | 60,00 | ||
| 20000,00 | 120,00 | 80,00 | ||
| 13000,00 | 80,00 | 40,00 | ||
| 2000,00 | 70,00 | 30,00 | ||
| 12000,00 | 60,00 | 40,00 | ||
| 18000,00 | 80,00 | 55,00 | ||
| 8000,00 | 50,00 | 30,00 | ||
| 1800,00 | 60,00 | 30,00 | ||
| 18000,00 | 65,00 | 35,00 | ||
| 8000,00 | 60,00 | 30,00 | ||
| 8000,00 | 70,00 | 40,00 | ||
| 9000,00 | 30,00 | |||
| 7000,00 | 96,00 | 42,00 | ||
| 3700,00 | 58,00 | 37,00 | ||
| 3400,00 | 125,00 | 100,00 | ||
| 9000,00 | 125,00 | 75,00 | ||
| 8300,00 | 130,00 | 94,00 | ||
| 8600,00 | 90,00 | 47,00 | ||
| 10000,00 | 156,00 | 78,00 | ||
| 10000,00 | 150,00 | 100,00 | ||
| 9500,00 | 150,00 | 85,00 | ||
| 8000,00 | 135,00 | 90,00 | ||
| 9000,00 | 135,00 | 80,00 | ||
| 8500,00 | 140,00 | 67,00 | ||
| 9000,00 | 300,00 | 140,00 |