Лабораторная работа № 14. Подбор параметров и поиск решений.

 

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

Краткое описание теоретической части.

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

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

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

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

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

Порядок выполнения работы.

1.Сначала решим простую задачу по математике: найдём численное решение уравнение .

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

2. Теперь более сложная задача. На Лист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
	Цена продукции (руб.)
	Себестоимость продукции (руб.)

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

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

Для этого:

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

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

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

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

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

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

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

Для этого:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

15. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 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, содержащие значения чистой прибыли (ссылки должны разделяться символом «;» - «точка с запятой»);

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

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

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

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

· Щелкните значок Кнопка Microsoft Office, а затем щелкните Параметры Excel.

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

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

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

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

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

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

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

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

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

Таблица 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

20.Компания имеет 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) не должно быть отрицательным.

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

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

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

	Втулки	Шестеренки	Расход	Имеется
Стеклоткань
Эпоксидка
Отвердитель
Прибыль от 1 шт.
Выпуск
Общая прибыль

Контрольные вопросы.

1. Как добавить команду Поиск решения?

2. В каких случаях используется команда Поиск параметров?

3. Что такое сценарий?

Дата защиты ____________ подпись преподавателя______________