 |
Построение сценариев
|
|
|
|
Сценарии входят в состав набора команд, которые называются средствами анализа гипотетических вариантов. При использовании сценариев выполняется анализ гипотетических вариантов, который включает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения формул на листе.
7. По данным рабочего листа Лист2 постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».
8. Для построения каждого сценария необходимо:
· на вкладке Данные в группе Работа с данными выбрать команду Анализ “что-если”, а затем выбрать в списке пункт Диспетчер сценариев;
· в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;
· в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены 1»);
· в поле Изменяемые ячейки ввести абсолютную ссылку на ячейку, содержащую значение изменяемого параметра (например, цены);
· нажать кнопку OK;
· в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены ввести 175);
· нажать кнопку OK.
9. Повторите указанные в пункте 8 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);
10. Для просмотра сценариев в окне Диспетчер сценариев поочередно выбирайте сценарии из списка и щелкайте по кнопке Вывести. Excel заменит содержимое ячеек листа значениями из сценария и отобразит результаты на листе.
11. Для создания отчета по сценарию в диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.
12. В окне Отчет по сценарию выберите тип отчета Сводная таблица, установите абсолютную ссылку на ячейку со значением результата (Прибыль за продукцию) и нажмите ОК.
|
|
|
13. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2009 год и выполните прогнозирование бюджета на 2010, 2011 и 2012 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет.
Бюджет предприятия на 2009 г. приведен в таблице:
Прогнозируемый ежегодный рост отдельных показателей приведен в таблице:
Для реализации поставленной задачи выполните следующие действия:
· присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку диапазона В13-В17, на вкладке Формулы в группе Определенные имена выбирайте команду Присвоить имя и в окне Создание имени нажимайте ОК.
· присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2010», «Прибыль_2011», «Прибыль_2012»;
· введите расчетные формулы для вычисления показателей в ячейках С2:Е11:
Общая прибыль= Объем продаж * Размер прибыли в %
Расход=Аренда + Услуги + Выплаты
Чистая прибыль=Общая прибыль-Расход
Показатели в столбцах C,D,E вычисляются по схеме:
Объем продаж 2010 г = Объем продаж 2009 г *(1+% роста объема продаж)
Размер прибыли 2010 г = Размер прибыли 2009 г *(1+% роста размера прибыли)
и т.д;
· определите первый сценарий, выполнив команду Данные/ Работа с данными/Анализ “что-если”/Диспетчер сценариев;
· аналогично создайте еще три сценария («Изменение показателей 1» и т. п.), щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне значения процентов роста показателей в ячейках B13:B17;
· создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на диапазон ячеек C11:E11, содержащие значения чистой прибыли;
|
|
|
· создайте отчет по сценарию, выбрав тип отчета – сводная таблица;
· проанализируйте полученные результаты решения задачи.
Поиск решения
Основывается на методе линейной оптимизации и используется для решения задач со многими неизвестными и ограничениями.
Средство поиска решения является надстройкой[1] Microsoft Office Excel, которая доступна при установке Microsoft Office или Microsoft Excel. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее:
· Выберите команду Файл/Параметры/Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.
· Нажмите кнопку Перейти.
· В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.
Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор.
В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.
Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
В табл. 2 приведены данные для вычисления прибыли от продажи трех видов продукции.
Таблица 2
Изменяя значения ячеек В3:В5 нужно подобрать такие значения количества товаров, при которых может быть получен наибольший суммарный доход. При решении данной задачи должны быть учтены следующие ограничения:
· общий объем производства – всего 300 изделий в день;
· должно быть произведено не менее 50 изделий А;
· должно быть произведено не менее 40 изделий В;
· должно быть произведено не более 40 изделий С.
14. Введите на новый рабочий лист данные табл. 2 для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6 должны быть введены формулы.
15. Запустите задачу поиска решений. Для этого на вкладке Данные в группе Анализ нажмите кнопку Поиск решения и в диалоговом окне Поиск решения введите данные:
|
|
|
· в поле Установить целевую ячейку укажите адрес $D$6, щелкнув мышью по соответствующей ячейке;
· установите переключатель Равной максимальному значению;
· в поле Изменяя ячейки определите изменяемые ячейки ($B$3:$B$5);
· в поле Ограничения по одному добавьте каждое из следующих четырех ограничений задачи ($B$6=300; $B$3>=50; $B$4>=40; $B$5<=40), для чего:
- щелкните кнопку Добавить и в появившемся окне Добавление ограничения введите ссылку на ячейку $B$6 (щелкая по ней мышью), оператор ограничения (=) и значение (300);
- для добавления следующего ограничения щелкните кнопку Добавить и повторите процедуру добавления ограничения;
- после ввода последнего ограничения щелкните кнопку ОК;
· в диалоговом окне Поиск решения щелкните кнопку Выполнить;
· в диалоговом окне Результаты поиска решения установите переключатель Сохранить найденное решение, в окне Тип отчета выберите Результаты и нажмите кнопку OK;
· ознакомьтесь с отчетом по результатам, помещенным на новом листе.
13. С помощью средства Поиск решения решите задачу минимизации расходов на перевозку.
Постановка задачи
Компания имеет 3 склада, территориально расположенных в разных районах города. Заказы на перевозку грузов поступают из сети розничных магазинов, распределенных по всей территории города. Цель задачи – удовлетворить потребность всех шести розничных магазинов в товарах, находящихся на трех складах, и сохранить при этом общие расходы на перевозку на минимальном уровне.
Исходные таблицы с данными для решения поставленной задачи представлены на рис.1.
Ячейки, которые должны содержать расчетные формулы, выделены серым фоном и жирным шрифтом значений.
Для осуществления поиска решения введите в новый рабочий лист все таблицы, представленные на рис. 1, причем в выделенные ячейки введите следующие формулы:
· в ячейку F12 введите формулу автосуммирования для вычисления суммы товаров, перевезенных в магазин 1 со всех трех складов:
|
|
|
=СУММ(С12:Е12);
· размножьте формулу на диапазон ячеек F13:F17;
· в ячейку В18 введите формулу для подсчета суммарной потребности в товаре:
=СУММ(В12:В17);
· размножьте формулу на диапазон ячеек С18:F18 для вычисления сумм товаров, перевезенных с каждого из трех складов в шесть магазинов и суммарного количества перевезенного товара;
· в ячейку С21 введите формулу для вычисления запасов товаров на складе 1 после отпуска товаров:
= С20-С18;
· размножьте формулу на диапазон ячеек D21:E21;
· в ячейку С23 введите формулу для вычисления суммы произведений стоимостей перевозок на количество перевозок во все магазины по каждому складу:
=СУММПРОИЗВ(С3:С8;С12:С17);
· размножьте формулу на диапазон ячеек D23:E23;
· в ячейку F23 введите формулу для подсчета общей стоимости перевозок для всех заказов:
=СУММ(С23:Е23)
Рис. 1. Исходные данные для решения транспортной задачи
Процедура поиска решений должна найти такие значения диапазона ячеек C12:E17 (количества перевезенных товаров с каждого из трех складов в каждый из шести магазинов), при которых каждый розничный магазин будет получать желаемое количество товара и общая стоимость перевозок (значение ячейки F24) будет минимальной. Нужно минимизировать значение, вычисляемое в ячейке F23, изменяя значения ячеек диапазона C12:E17 с учетом следующих ограничений (всего 27):
· количества необходимого товара для каждого розничного магазина, представленные в ячейках с B12 по B17, должны быть равны количествам перевезенного товара (ячейки с F12 по F17 соответственно), т.е. все заказы должны быть выполнены;
· значения в изменяемых ячейках (диапазон С12:Е17), соответствующие количествам перевезенных товаров с каждого из трех складов в каждый из шести магазинов, не могут быть отрицательными;
· количество запасов после отпуска на каждом складе (диапазон С21:Е21) не должно быть отрицательным.
18. Самостоятельно решите задачу нахождения максимальной прибыли.
Постановка задачи
Для изготовления пластмассовых втулок и шестеренок требуется стеклоткань, эпоксидная смола и отвердитель. На изготовление одной втулки затрачивается 4 ед. стеклоткани, 3 ед. - эпоксидной смолы и 2 ед. – отвердителя, а на изготовление одной шестеренки – соответственно 3, 4 и 6 ед. материалов. Прибыль предприятия от изготовления одной втулки составляет 20 руб., а шестеренки – 40 руб. Сколько втулок и шестеренок должно изготовить предприятие для получения наибольшей прибыли, если в его распоряжении имеется 480 ед. стеклоткани, 444 ед. эпоксидной смолы и 546 ед. отвердителя.
19. Сохраните результаты лабораторной работы в файле с именем lab4.xlsm.
|
|
|
|
|
|
