Лабораторная работа 7 Анализ финансово-экономических ситуаций с помощью надстройки «Поиск решения»

 

Цель работы: Освоить навыки решения финансово-экономических ситуаций с помощью надстройки «Поиск решения» Excel.

 

Порядок работы:

1. Запустите программу MS Excel 2010.

2. В открывшемся окне нажмите кнопку Новая книга для создания новой рабочей области. Щелкните по кнопке Создать.

3. В поле Имя файла (файл сохранить как) задайте имя вашей рабочей книги lab7_FIO (где FIO - ваши инициалы) и выберите диск D/папка студент/папка FIO и номер группы (созданная на первом занятии).

4. При этом откроется окно вашей рабочей книги.

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

Чтобы воспользоваться надстройкой Поиск решения, её необходимо установить и активировать. Для этого выполняются действия: Вкладка Файл/кнопка Параметры/категория Надстройки/кнопка Перейти. В появившемся диалоговом окне Надстройки (Рис. 45) установить флажок Поиск решения и нажать кнопку ОК.

Рис. 45. Диалоговое окно Надстройки

На вкладке Данныев группе Анализ,появится кнопка для надстройки Поиск решения(Рис. 46).

Рис. 46. Вкладка Данные/группа Анализ/Поиск решения

Для того чтобы обратиться к надстройке Поиск решения для нахождения оптимальных значений функции F(x) необходимо определиться с терминологией. Модель оптимизации состоит из трех частей: целевая ячейка, изменяемые ячейки и ограничения (Рис. 47).

Рис. 47. Параметры для поиска решения

Задачи, которые можно решать с помощью Поиска решения, в общей постановке формулируются так:

Найти:

такие, что:

при ограничениях:

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

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

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

После заполнения диалогового окна Параметры поиска решения следует нажать кнопку Найти решение. При нахождении оптимального решения на экран выводится диалоговое окно Результаты поиска решения. Значения, отображаемые на рабочем листе, представляют собой оптимальное решение задачи (Рис. 48). Время решения задачи зависит от количества изменяемых ячеек, а также размера и сложности модели.

 

Рис. 48. Результаты поиска решения

 

Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.

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

Отчет Устойчивость. Используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле (поле Оптимизировать целевую функцию диалогового окна Параметры Поиска решения) или в формулах ограничений. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел.

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

Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

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

Пример 23.

Фирма производит две модели книжных полок – модель А и модель В. Их производство ограничено наличием сырья и временем машинной обработки. Для каждого изделия модели А требуется 3м2 досок, а для изделия модели В – 4м2 . Фирма получает от своих поставщиков до 1700 м2 досок в неделю. Для каждого изделия модели А требуется

12 мин машинного времени, а для изделия модели В – 30 мин. В неделю можно использовать 160 часов машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, чтобы получать максимальную прибыль, если каждое изделие модели А приносит 2$ прибыли, а каждое изделие модели В – 4$ прибыли?

Алгоритм решения.

В вашей рабочей книге lab7_FIO, откройте Лист 1.

1. Количество полок модели А обозначим через Х1 (ячейка A2), а модели В – Х2 (ячейка B2). Далее на рисунке (Рис. 49) описана компьютерная модель с учетом условий задачи. Задана целевая функция в ячейке I2 и прописаны формулы в ячейках E2 и Н2 для наложения ограничений.

Рис. 49. Модель решения примера

2. Обращаемся к инструменту Поиск решения Вкладка Данные/Поиск решения (Рис. 50) и заполняем соответствующие поля. В качестве ограничений добавляем условия, что значения X1,X2 должны быть >=0

 

Рис. 50. Параметры поиска решения

3. Получаем результат. Максимальная прибыль в 1400$ будет получена если полки модели А будут выпущены в количестве 300 штук, а полки модели В – 200 штук (Рис 51).

 

 

Рис. 51. Результат решения

Пример 24.

Фирме необходимо выбрать деловых партнеров (из числа пяти фирм) для заключения контрактов на поставку товаров на сумму до 2 миллионов рублей, определив объем сделки с каждым из партнеров и обеспечив при этом максимальную прибыль с учетом того, что ожидаемая сумма рисков от сделок не превысит суммы ожидаемой прибыли. Известные параметры, характеризующие значения прибыли, риска и максимальной суммы сделки с каждым из партнеров (с учетом далее введенных условных обозначений) приведены на рис. 52.

 

Рис. 52. Исходные данные

Введем следующие условные обозначения:

общая возможная сумма сделки, руб. - Р;

общее количество i - х деловых партнеров - n;

максимально возможная сумма сделки с i-м партнером, руб - k_i;

сумма сделки с i - м партнером, руб. - xi;

прибыль от сделки с i -м партнером, % - с_i;

риск от сделки с i -м партнером ,% - h_i.

Тогда целевая функция может быть записана :

(3)

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

Алгоритм решения.

В вашей рабочей книге lab7_FIO, откройте Лист 2.

Создадим в табличной форме модель решаемой задачи согласно рис.53, в которой в ячейки В4:F4 и В5:F5 занесены данные из таблицы рис. 52 . Значения ячеек В3:F3 могут быть, в принципе, заполнены произвольно. Расчетные формулы столбца Итог: G3=СУММ(В3:G3), G4=В3*В4 + С3*С4 + D3*D4 + E3*E4 + F3*F4, G5= В3*В5 + С3*С5+ D3*D5 + E3*E5 + F3*F5.

Рис. 53. Компьютерная модель задачи

Установим курсор в ячейку G4 и обратимся к инструменту Поиск решения: Вкладка Данные/Поиск решения (Рис.54). Установим значение для целевой функции до Максимум. Задаем изменяемые ячейки переменных B3:F3, далее вводим ограничения для данной задачи:

§ максимально возможные суммы сделки ki: $B$3 <= 250000; $C$3 <=600000; $D$3<=140000; $E$3 <= 500000; $F$3 <= 1200000;

§ возможная суммарная прибыль должна превосходить возможный суммарный убыток: $G$5<=$G$4;

§ общая возможная сделка равняется 2000000 руб.:$G$3=2000000;

§ значения возможных сделок должны быть величинами положительными: В3:F3>=0.

После нажатия на кнопку Найти решение и после успешного завершения поиска (Рис. 55) получаем результат (Рис. 56)

 

Рис. 54. Параметры поиска решения для задачи поиска партнеров

 

 

Рис. 55. Окно Результаты поиска решения

 

Рис. 56. Результат решения

 

Пример 25.

Пусть дана следующая система линейных уравнений

Задачу можно свести к оптимизационной задаче. Для чего одно из уравнений (например, первое) взять в качестве целевой функции, а оставшиеся 2 уравнения рассматривать в качестве ограничений.

Поставленную задачу можно сформулировать так: найти x, y, z при которых значение первого уравнения будет равно 5, второго 0, третьего 15.

Алгоритм решения.

В вашей рабочей книге lab7_FIO, откройте Лист 3.

1. Выпишите матрицу коэффициентов при неизвестных системы (ячейки Е2:G4) и вектор свободных членов (ячейки I2:I4) (рис. 57).

 

 

Рис. 57. Решение систем линейных уравнений с помощью инструмента "Поиск решения"

2. Определите область для переменных x, y и z (ячейки А9:С9).

3. Запишите в ячейки B12:B14 формулы для решения уравнений, ссылаясь на соответствующие области, содержащие значения переменных.

4. Обратитесь к инструменту «Поиск решения».

5. Первое уравнение системы задайте в качестве целевой функции, а остальные уравнения системы разместите в области ограничений.

6. В поле Изменяя ячейки переменных укажите диапазон А9:С9, соответствующий расположению переменных x, y, z.

7. Нажмите кнопку Найти решение

8. Область значений переменных заполнится значениями

 

x	y	z
2,000001	1,000002