Подбор параметров и поиск решения

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков использования таких полезных средств EXCEL, как подбор параметра, поиска решений и использования сценариев для анализа данных.

Основные сведения об использовании сценариев,

Подборе параметров и поиске решения

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

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

Для проведения такого анализа «что-если» наоборот EXCEL имеет два средства: подбор параметра и поиск решения.

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

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

Содержание лабораторной работы

Выполнение данной лабораторной работы состоит из двух самостоятельных, не связанных между собой разделов:

· исследование информации, представленной в табл. 1 Калькуляция на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев;

· использование средства Поиск решения для решения двух задач линейного программирования.

Подбор параметра

Сначала решим простую задачу по математике: найдём численное решение уравнение Подбор параметров и поиск решения - student2.ru .

В ячейке B4 вводим начальное значение х, в B5 =exp(-B4)-B4. Чтобы в B4 оказался корень уравнения, в B5 должен быть 0. Вкладка Данные/Анализ «что-если»/Подбор параметра. В открывшемся диалоговом окне указываем целевую ячейку B5 (Поле Установить в ячейке), Изменяя значение ячейки B4. В поле Значение вводим 0. Всё. Задача решена:х=0,567138

1. Теперь более сложная задача. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1. Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции

(в таблице эти значения показаны на сером фоне жирным шрифтом). Остальные данные должны быть представлены в виде расчетных формул:

Доход= Цена продукции ^x Количество экземпляров;

Себестоимость реализованной продукции= Себестоимость продукции ^x Количество экземпляров;

Валовая прибыль= Доход – Себестоимость реализованной продукции;

Накладные расходы= Доход ^x Проценты накладных расходов;

Валовые издержки= Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции= Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

КалькуляцияТаблица 1

	А	В
	Количество экземпляров (шт.)	20 000
	Доход (руб.)	3 000 000
	Себестоимость реализованной продукции (руб.)	1 000 000
	Валовая прибыль (руб.)	2 000 000
	Проценты накладных расходов	30%
	Затраты на зарплату (руб.)	125 000
	Затраты на рекламу (руб.)	25 000
	Накладные расходы (руб.)	900 000
	Валовые издержки (руб.)	1 050 000

	Прибыль от продукции (руб.)	950 000


	Цена продукции (руб.)
	Себестоимость продукции (руб.)

2. Переименуйте Лист1 вКалькуляция и скопируйте отлаженную таблицу с формулами в Лист2. Исследуйте информацию, представленную на таблице листа Калькуляция.Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

Для этого:

· выполните команду меню Данные/«анализ что-если»/Подбор параметра;

· в диалоговом окне Диспетчер сценариев в поле Установить в ячейке укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);

· щелкните кнопку OK.

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и щелкните кнопку OK для изменения значений ячеек таблицы в соответствии с найденным решением.

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

6. Самостоятельно определите, каков должен быть показатель процентов накладных расходов, чтобы прибыль за продукцию составила 1000 000 рублей.

7. Выполните подбор параметров для данных, представленных в виде диаграммы.

Для этого:

· с помощью мастера диаграмм на основании таблицы Калькуляцияпостройте обычную (плоскую) гистограмму;

· разместите диаграмму справа от таблицы и увеличьте ее размер;

· щелкните левой клавиши мыши по одному из столбцов диаграммы, тем самым выделив весь ряд значений;

· щелкните левой клавишей мыши при нажатой клавише CTRL по столбику диаграммы, соответствующему показателю прибыли от продукции, и растяните вверх этот столбик;

· в диалоге Подбор параметра заполните необходимые окна для получения новых значений.

Построение сценариев

8. По данным рабочего листа Лист2постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

9. Для построения каждого сценария необходимо:

· выполнить команду Данные/Анализ «что-если»/Сценарии;

· в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;

· в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены1»);

· в поле Изменяемые ячейки ввести абсолютную ссылку на ячейку, содержащую значение изменяемого параметра (например, цены);

· нажать кнопку OK;

· в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены 175).

10. Повторите указанные в пункте 9 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);

11. Для просмотра сценариев в окне Диспетчер сценариев поочередно выбирайте сценарии из списка и щелкайте по кнопке Вывести. Excel заменит содержимое ячеек листа значениями из сценария и отобразит результаты на листе.

12. Для создания отчета по сценарию в диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.

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

14. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2000 год и выполните прогнозирование бюджета на 2001, 2002 и 2003 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет.

Бюджет предприятия на 2000 г. приведен в таблице:

А	B	C	D	E
	2000 г.	2001 г.	2002 г.	2003 г.
Объем продаж
Размер прибыли в %	25%
Общая прибыль

Аренда
Услуги
Выплаты
Расход

Чистая прибыль

Прогнозируемый ежегодный рост отдельных показателей приведен в таблице:

	А	В
	Объем продаж	4%
	Размер прибыли	2%
	Аренда	5%
	Услуги	3%
	Выплаты	5%

Для реализации поставленной задачи выполните следующие действия:

· присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку и назначайте имя, вводя его слева от строки формул. Например, щёлкаете по В13, заменяете В13 слева от строки формул на Рост_объёма_продаж;

· присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2001», «Прибыль_2002», «Прибыль_2003»;

· Введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

Общая прибыль= Объем продаж * Размер прибыли в %

Расход=Аренда + Услуги + Выплаты

Чистая прибыль=Общая прибыль-Расход

Показатели в столбцах C,D,E вычисляются по схеме:

Объем продаж 2001 г = Объем продаж 2000 г *(1+% роста объема продаж)

Размер прибыли 2001 г = Размер прибыли 2000 г *(1+% роста размера прибыли)

и т.д;

· определите первый сценарий, выполнив команду Данные/Анализ «что-если»/Сценарии аналогично создайте еще три сценария, щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне значения процентов роста показателей в ячейках B13:B17;

· создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на ячейки C11, D11, E11, содержащие значения чистой прибыли (ссылки должны разделяться символом «;» - «точка с запятой»);

· создайте отчет по сценарию, выбрав тип отчета – сводная таблица;

· проанализируйте полученные результаты решения задачи.

Поиск решения

Основывается на методе линейной оптимизации и используется для решения задач со многими неизвестными и ограничениями.

Поиск решения относится к так называемым надстройкам Excel, которые могут быть установлены в системе, а могут и отсутствовать. Средство поиска решения является надстройкой (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) Microsoft Office Excel, которая доступна при установке Microsoft Office или Microsoft Excel. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее.

1. Щелкните значок Кнопка Microsoft Office Подбор параметров и поиск решения - student2.ru , а затем щелкните Параметры Excel.

2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.

3. Нажмите кнопку Перейти.

4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

Совет Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор.

В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

5. После загрузки надстройки для поиска решения в группе Анализ на вкладки Данные становится доступна команда Поиск решения.

Если в списке надстроек Поиск решения отсутствует, то следует повторить установку Excel, добавив данный компонент.

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

В табл. 2 приведены данные для вычисления прибыли от продажи трех видов продукции.

Таблица 2

A	B	C	D

	Количество	Прибыль/шт	Доход
Товар А
Товар В
Товар С
Всего

Изменяя значения ячеек В3:В5 нужно подобрать такие значения количества товаров, при которых может быть получен наибольший суммарный доход. При решении данной задачи должны быть учтены следующие ограничения:

· общий объем производства – всего 300 изделий в день;

· должно быть произведено не менее 50 изделий А;

· должно быть произведено не менее 40 изделий В;

· должно быть произведено не более 40 изделий С.

15. Введите в новый рабочий лист данные табл. 2 для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6должны быть введены формулы.

16. Запустите задачу поиска решений. Для этого выполните команду Данные/Поиск решения …и в диалоговом окне Поиск решениявведите данные:

· в поле Установить целевую ячейку укажите адрес $D$6,щелкнув мышью по соответствующей ячейке;

· установите переключатель Равной максимальному значению;

· в поле Изменяя ячейки определите изменяемые ячейки ($B$3:$B$5);

· в поле Ограничения по одному добавьте каждое из следующих четырех ограничений задачи ($B$6=300; $B$3>=50; $B$4>=40; $B$5<=40),для чего:

- щелкните кнопку Добавить и в появившемся окне Добавление ограничениявведите ссылку на ячейку $B$6 (щелкая по ней мышью), оператор ограничения (=) и значение (300);

- для добавления следующего ограничения щелкните кнопку Добавить и повторите процедуру добавления ограничения;

- после ввода последнего ограничения щелкните кнопку ОК;

· в диалоговом окне Поиск решения щелкните кнопку Выполнить;

· в диалоге Результаты поиска решения установите переключатель Сохранить найденное решение, в окне Тип отчета выберите Результаты и нажмите кнопку OK;

· ознакомьтесь с отчетом по результатам, помещенным на новом листе.

17. С помощью средства Поиск решения решите задачу минимизации расходов на перевозку.

Постановка задачи

Компания имеет 3 склада, территориально расположенных в разных районах города. Заказы на перевозку грузов поступают из сети розничных магазинов, распределенных по всей территории города. Цель задачи – удовлетворить потребность всех шести розничных магазинов в товарах, находящихся на трех складах, и сохранить при этом общие расходы на перевозку на минимальном уровне.

Исходные таблицы с данными для решения поставленной задачи представлены на рис.1.

A	B	C	D	E	F
	Стоимость перевозок грузов
		Склад1	Склад2	Склад3
	Магазин 1
	Магазин 2
	Магазин 3
	Магазин 4
	Магазин 5
	Магазин 6

	Потреб- ность	Количество перевезенного товара со склада	Переве- зено
	в товаре	Склад1	Склад2	Склад3	всего
Магазин 1
Магазин 2
Магазин 3
Магазин 4
Магазин 5
Магазин 6
Всего

Запасы на складе
Запасы после отпуска


Стоимость перевозок

Рис. 1. Исходные данные для решения транспортной задачи

Ячейки, которые должны содержать расчетные формулы, выделены серым фоном и жирным шрифтом значений.

Для осуществления поиска решения введите в новый рабочий лист все таблицы, представленные на рис. 1, причем в выделенные ячейки введите следующие формулы:

· в ячейку F12 введите формулу автосуммирования для вычисления суммы товаров, перевезенных в магазин 1 со всех трех складов:

=СУММ(С12:Е12);

· размножьте формулу на диапазон ячеек F13:F17;

· в ячейку В18 введите формулу для подсчета суммарной потребности в товаре:

=СУММ(В12:В17);

· размножьте формулу на диапазон ячеек С18:F18 для вычисления сумм товаров, перевезенных с каждого из трех складов в шесть магазинов и суммарного количества перевезенного товара;

· в ячейку С21 введите формулу для вычисления запасов товаров на складе 1 после отпуска товаров:

= С20-С18;

· размножьте формулу на диапазон ячеек D21:E21;

· в ячейку С24 введите формулу для вычисления суммы произведений стоимостей перевозок на количество перевозок во все магазины по каждому складу:

=СУММПРОИЗВ(С3:С8;С12:С17);

· размножьте формулу на диапазон ячеек D24:E24;

· в ячейку F24 введите формулу для подсчета общей стоимости перевозок для всех заказов:

=СУММ(С24:Е24)

Процедура поиска решений должна найти такие значения диапазона ячеек C12:E17 (количества перевезенных товаров с каждого из трех складов в каждый из шести магазинов), при которых каждый розничный магазин будет получать желаемое количество товара и общая стоимость перевозок (значение ячейки F24) будет минимальной. Нужно минимизировать значение, вычисляемое в ячейке F24, изменяя значения ячеек диапазона C12:E17 с учетом следующих ограничений (всего 27):

· количества необходимого товара для каждого розничного магазина, представленные в ячейках с B12 по B17, должны быть равны количествам перевезенного товара (ячейки с F12 по F17 соответственно), т.е. все заказы должны быть выполнены;

· значения в изменяемых ячейках (диапазон С12:Е17), соответствующие количествам перевезенных товаров с каждого из трех складов в каждый из шести магазинов, не могут быть отрицательными;

· количество запасов после отпуска на каждом складе (диапазон С21:Е21) не должно быть отрицательным.

18. Самостоятельно решите задачу нахождения максимальной прибыли.

Постановка задачи

Для изготовления пластмассовых втулок и шестеренок требуется стеклоткань, эпоксидная смола и отвердитель. На изготовление одной втулки затрачивается 4 ед. стеклоткани, 3 ед. - эпоксидной смолы и 2 ед. – отвердителя, а на изготовление одной шестеренки – соответственно 3, 4 и 6 ед. материалов. Прибыль предприятия от изготовления одной втулки составляет 20 руб., а шестеренки – 40 руб. Сколько втулок и шестеренок должно изготовить предприятие для получения наибольшей прибыли, если в его распоряжении имеется 480 ед. стеклоткани, 444 ед. эпоксидной смолы и 546 ед. отвердителя.

Для решения задачи можно использовать таблицу:

	Втулки	Шестеренки	Расход	Имеется
Стеклоткань
Эпоксидка
Отвердитель

Прибыль от 1 шт.

Выпуск

Общая прибыль