Лабораторная работа №2




НазваниеЛабораторная работа №2
страница4/6
Дата публикации22.10.2014
Размер0.8 Mb.
ТипЛабораторная работа
5-bal.ru > Информатика > Лабораторная работа
1   2   3   4   5   6

Рисунок 4 – Таблица чувствительности с двумя переменными

3. Выделим диапазон таблицы чувствительности вместе с формулой (F10:K20).

4. Воспользуемся таблицей данных для анализа чувствительности. В поле Подставлять значения по строкам укажем ячейку, содержащую процентную ставку, при которой рассчитывалась формула. При этом автоматически вводится абсолютная ссылка $B$7. В поле Подставлять значения по столбцам укажем ячейку, содержащую размер ссуды, по которой получено рассчитанное значение. При этом автоматически будет введена абсолютная ссылка $B$4. Результаты анализа приведены на рисунке 5.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l11_3.gif
Рисунок 5 – Таблица чувствительности с двумя переменными. Диалоговое окно таблицы данных 

Пример 2. Компания, производящая электроприборы, продает 13000 шт. ежегодно по цене 1200р. за штуку. Себестоимость единицы продукции равна 400 руб., накладные расходы составляют 1000000 руб. Маркетинговая политика компании позволит увеличить объем продаж в течение последующих трех лет выпуска продукции данного вида на некоторый процент по отношению к текущему объему. Определим, как зависит сумма прибыли от процента увеличения объема продаж.

Вид листа с исходными данными и формулами для расчета прибыли, а также макет таблицы чувствительности для проводимого анализа приведен на рисунке 6. В таблице данных в качестве ячейки подстановки указан адрес ячейки, содержащей значение процента В7.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l11_4.gif
Рисунок 6 – Вид листа с исходными данными и формулами 

На рисунке 7 представлен анализ чувствительности изменения прибыли в зависимости от величины процента увеличения объема продаж по отношению к текущему объему.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l11_5.gif
Рисунок 7 – Результаты анализа чувствительности

Следующий пример также представляет расчет электронной таблицы, содержащей предположения, касающиеся входных данных модели.

Пример 3. Сотовая компания рассматривает возможность внедрения проекта модернизации оборудования. Проекту требуется инвестиционное вложение в размере  400 млн. руб. (Год 0). Проект будет приносить прибыль в течение 10 лет. Предполагается, что поток денежных средств в первый год составит 40 млн. руб. Процент роста годового потока денежных средств может изменяться в пределах от 10% до 30% в год. Проанализируем, как изменяется прибыль проекта в зависимости от процентов роста денежного потока. На основании расчетов прибыли для каждого года работы проекта определим срок окупаемости. Срок окупаемости – это количество лет, за которые будущая прибыль от проекта покроет начальное инвестиционное вложение в проект.

Порядок выполнения

1. Заполним лист исходными данными и введем в диапазоны В9:В19 и С9:С19 формулы для вычисления денежных потоков и прибыли при росте 0% (рисунку 8).

2. В диапазоне Е5:Е25 создадим вектор процентов роста денежных потоков. В ячейках F4:O4 введем ссылки на формулы расчета прибыли для каждого года анализируемого периода (рисунок 9).

 

1

2

3

4

5

6

7

8

9

10

 

=C10

=C11

=C12

=C13

=C14

=C15

=C16

=C17

=C18

=C19

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l11_6.gif
Рисунок 8 – Лист с исходными данными и формулами для расчета денежных потоков и прибыли

3. Для автоматизированного поиска срока окупаемости воспользуемся функцией ПОИСКПОЗ(). В ячейку Q4 введем функцию =ПОИСКПОЗ(0;F4:O4;1) и скопируем ее до конца диапазона (рисунок 10).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l11_7.gif
Рисунок 9 – Макет таблицы чувствительности

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\11-8.gif
Рисунок 10 – Анализ влияния процента роста денежных потоков на срок окупаемости проекта

 

Лабораторная работа № 6

Использование инструмента Поиска решения для решения прикладных задач

Цель работы: Приобрести навыки использования технологий MS Excel Поиск решения.

Задание к лабораторной работе:

1. Изучите материалы лекций 12 и 13 и решите приведенные примеры.

2. Выполните контрольные задания на новом листе или в отдельной книге MS Excel.

Контрольные задания:

Задание 1. На рабочем листе MS Excel введем данные об объемах выполненных заказов в 20 филиалах фирмы  за сентябрь в виде таблицы, фрагмент которой показан на рисунке. Для выборки значений сумм заказов определить, сколько значений попадает в заданные интервалы значений. Например, от 0 до 1000,  от 1001 до 1500, от 1501 до 2000, от 2001 до 2500, свыше 2500. Проведите частотный анализ с помощью функции рабочего листа и Пакета анализа.

 

А

B

C

D

E

1

№ филиала

Сентябрь

 

 

 

2

1

1230

 

1000

 

3

2

980

 

1500

 

4



….

 

2000

 

5

 

 

 

2500

 

6

 

 

 

 

 

Рисунок – Данные выборки 

Задание 2. По данным таблиц рассчитайте

1) линейный и экспоненциальный прогноз на последующие пять периодов с помощью маркера заполнения;

2) линейный и экспоненциальный прогноз на последующие пять периодов с помощью функций рабочего листа ТЕНДЕНЦИЯ() и РОСТ().

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

ЧИСЛЕННОСТЬ НАСЕЛЕНИЯ
(оценка на конец года; тысяч человек)

 

1990

1995

2000

2001

2002

2003

2004

2005

2006

2007

Российская Федерация

148274

148292

146304

145649

144964

144168

143474

142754

142221

142009

 Доля

0,01

0,01

0,01

0,01

0,01

0,01

0,01

0,01

0,01

0,01

3. Добавьте в архив файлы с решениями и результатами.

 

ЛЕКЦИЯ 12

Многочисленные примеры этой лекции помогут Вам научиться разрабатывать математические модели для решения оптимизационных задач и использовать надстройку табличного редактора для поиска оптимального решения. Используйте этот мощный инструмент MS Excel при решении различного типа задач для получения точного и быстрого результата!

ИНФОРМАЦИОННАЯ ТЕХНОЛОГИЯ ПОИСКА РЕШЕНИЯ

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

  • обозначить переменные;

  • составить целевую функцию в соответствии с задачей;

  • записать систему ограничений с учетом имеющихся в условии задачи показателей.

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

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

Надстройка  Поиск решения относится к группе команд, являющихся средством анализа «что если». Надстройки — это компоненты Microsoft Excel, предоставляющие доступ к дополнительным функциям и командам. Двумя из наиболее популярных надстроек являются «Пакет анализа» и «Поиск решения», которые реализуют функции расширенного анализа данных для планирования «что-если». Чтобы использовать эти надстройки, их необходимо установить и активировать.

Для того, чтобы загрузить эту надстройку в Excel 2010, нужно открыть вкладку Файл/Параметры. В окне Параметры Excel выбрать меню Надстройка и в поле Управление надстройками нажать кнопку Перейти. В окне доступных надстроек установить флажок Поиск решения и нажать ОК. После этого в группе Анализ на вкладке Данные станет доступной команда Поиск решения.

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

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

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

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

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

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

Пример1. Решим следующую задачу симплекс-методом.

L(x)=x1+2*x2+2*x3+x4+6*x5>min

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_1.gif
xj f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_2.gif0, j=1,5

Все значения переменных должны быть целыми и неотрицательными.

Для решения задачи заполним диапазон рабочего листа  Excel исходными данными.

1. В ячейку B3 введем целевую функцию, в ячейки B9:B11 и С9:С11- соответственно левые и правые части уравнений системы ограничений (рисунок 1).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_3.gif
Рисунок 1 – Вид рабочего листа с формулами

Ячейки A7:E7 используются в уравнениях в качестве переменных и вывода результатов решения.

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

3. Введем диапазоны переменных и с помощью кнопки Добавить – заданные ограничения (равенства левых и правых частей уравнений и целочисленные значения переменных). С помощью флажка устанавливаем все переменные без ограничений неотрицательными и выбираем в качестве метода решения симлекс - метод. Пример заполнения окна приведен на рисунке .

4. После щелчка мышью по кнопке Найти решение Excel находит оптимальное решение (рисунок 2).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_4.gif
Рисунок 2 – Заполнение диалогового окна Параметры поиска решения

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_5.gif
Рисунок 3 – Результаты решения задачи линейного программирования

Разработаем математическую модель и решим задачу оптимального распределения ресурсов средствами MS Excel.

Пример 2. Предприятие располагает тремя производственными ресурсами (сырьем, оборудованием, электроэнергией) и может организовать производственный процесс двумя различными способами. При первом способе за один месяц предприятие выпускает 3 тыс. изделий, при втором – 4 тыс. изделий.

Исходные данные приведены в таблице 1.

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

Производственный ресурс

Расход ресурсов за месяц

Общий ресурс

1–й способ

2–й способ

Сырье

1

2

4

Оборудование

1

1

3

Электроэнергия

2

1

8

Сколько месяцев должно работать предприятие каждым из этих способов, чтобы при имеющихся ресурсах обеспечить наибольший выпуск продукции?

Порядок действий:

1. На рабочем листе определим блок ячеек для переменных х1 и х2.  Под заголовками имен переменных оставим пустые ячейки для значений переменных (A14:B14). Эти ячейки используются для ссылок на переменные в формулах и вывода результатов поиска решения

2. Создадим блок клеток для целевой функции. Введем заголовок (A11) и в смежной ячейке (B11) запишем функцию цели, как это показано на рисунке 4. Аргументами в формуле являются ссылки на пустые ячейки для значений переменных (A14 и B14). После ввода формулы в ячейке B11 появится нулевое значение.

3. Создадим блок клеток, содержащих ограничения, накладываемые на переменные. Введем заголовок и формулы ограничений в виде текста (A17:A19). В смежном столбце введем левые части неравенств системы ограничений (ячейки B17:B19), а в ячейки C17:C19 – правые части ограничений. Аргументами в формулах также будут являться ссылки на свободные ячейки для значений переменных. Результат подготовленных данных разработанной математической модели на рабочем листе MS Excel представлен на рисунке 4.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_6.gif
Рисунок 4 – Лист с формулами

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_7.gif
Рисунок 5 – Пример решения задачи линейного программирования

4. Выполним команду Данные/ Анализ/Поиск решения. В диалоговом окне Параметры поиска решения сформируем модель задачи оптимизации (рисунок 5).

5. Запустим задачу на выполнение нажатием кнопки  Найти решение. Результат представлен на рисунке 6.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_8.gif
Рисунок 6 – Результат поиска решения

6. Сохраним результаты поиска решения в виде сценария. Для этого в диалоговом окне щелкаем по кнопке Сохранить сценарий и вводим имя сценария.

7. Сформируем отчет на основе полученного решения оптимизационной задачи. Выберем пункт меню Данные/Анализ «что если»/ Диспетчер сценариев…, выведем отчет по созданному в п.6 сценарию в виде сводной таблицы и сохраним результаты работы.

Пример 3. Предприятию связи требуется рассчитать оптимальный заказ государственных знаков почтовой оплаты (ГЗПО).

Таблица исходных данных:

Почтовые отправления

Тариф, тнг

Номиналы марок

Письма простые

32

1

2

3

4

5

7

8

10

Себестоимость марок, тнг

 

0,253

0,28

0,28

0,25

0,25

0,26

0,274

0,257

Задача предполагает определение оптимального сочетания номиналов марок для получения значений по тарифу с учетом следующих ограничений:

- Стоимость марок должна быть тоже минимальна, так как ее увеличение скажется на величине доходов от предоставления услуг по отправке корреспонденции;

- Количество марок должно быть минимальным, так как увеличение количества скажется на производительности при обработке корреспонденции.

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

Вариант 1: Будем стремиться при обеспечении ограничений по номиналу к минимальной стоимости заказа.

Порядок действий:

1. На рабочем листе определим блок ячеек для переменных х1,х2,x3,x4,x5,x6,x7,x8 (рисунок 7).  Под заголовками имен переменных оставим пустые ячейки для значений переменных (A7:H7), которые будут использоваться для ссылок на переменные в формулах и вывода результатов поиска решения.

2. В ячейку B13 запишем функцию цели, определяемую как минимально возможную сумму затрат. Аргументами в формуле являются ссылки на пустые ячейки для значений переменных (A7 и H7). После ввода формулы в ячейке B13 появится нулевое значение.

3. Создадим блок клеток, содержащих ограничения, накладываемые на переменные. Введем величину ограничения по тарифу в ячейке (B11). В смежной ячейке введем формулу ограничений (ячейки A11), а в ячейки C10:C12 – правые части ограничений. Аргументами в формулах будут являться ссылки на свободные ячейки для значений переменных (количество марок определенного номинала) и ячейки со значениями номиналов. Результат подготовленных данных разработанной математической модели на рабочем листе MS Excel представлен на рисунке 8.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_9.gif
Рисунок 7 – Вид рабочего листа с данными и формулами

4. Выполним команду Данные/ Поиск решения. Заполним диалоговое окно Поиск решения в соответствии с моделью задачи оптимизации. Проверьте Параметры поиска решения и в случае необходимости сбросьте флажок Игнорировать целочисленные ограничения.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_10.gif
Рисунок 8 – Вид заполненного окна Параметры поиска решения 

5. Нажмем кнопку Найти решение и получим следующий результат.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_11.gif
Рисунок 9 – Вид рабочего листа с результатами поиска решения для варианта 1

Таким образом, при минимальных материальных затратах и заданных ограничений по номиналу получается, что выгоднее заказывать марки трех номиналов 5,7 и 10 в соотношении 1:1:2.

Вариант 2.  Рассмотрим другой вариант, когда при обеспечении ограничения по номиналу стремятся к минимальному количеству марок на конверте.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_12.gif
Рисунок 10 – Вид рабочего листа с формулами для варианта 2

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_13.gif
Рисунок 11 – Рабочий лист после ввода формул для варианта 2

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_14.gif
Рисунок 12 – Результаты поиска решения для варианта 2

Таким образом, в данном примере оба варианта при заданном ограничении по номиналу предполагают использование четырех марок на конверте. Однако себестоимость 4-х марок для первого варианта согласно расчету (целевая функция) составила 1,024 тнг, а для второго варианта – 1,051тнг. Результат расчета себестоимости для второго варианта приведен в ячейке B12. Обратите внимание, для расчета применена формула =СУММ(ЕСЛИ(A4:H4<>0;A4:H4*A6:H6)) к массиву данных. Таким образом, первый вариант заказа является наиболее предпочтительным.

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

Пример 3. Предположим, что предприятие имеет 4 фабрики и четыре пункта, в которые поставляется продукция. Фабрики располагаются в пунктах А, Б, В, Г с производственными возможностями 20, 60, 50 и 30 ед. продукции ежедневно. В пункты поставки К, Л, М, Н предприятие должно отправлять ежедневно 25, 35, 80 и 20 ед. продукции соответственно. Хранение на фабрике единицы продукции, не поставленной вовремя, в день обходится предприятию 15 ден. ед. Штраф за просроченную поставку единицы продукции составляет 50 ден.ед. в день. Транспортные расходы приведены в таблице 2.

Таблица 2  – Транспортные расходы

Стоимость перевозки единицы продукции

6

3

4

5

5

2

3

3

3

4

2

4

5

6

2

7

Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы.

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

Для решения задачи рассмотрим ее математическую модель. Неизвестными в этой задаче являются объемы перевозок. Пусть объем перевозок с i-той фабрики в j-тый центр поставок Xij. Функция цели – это суммарные транспортные расходы, т.е.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_15.gif

Где Cij – стоимость перевозки единицы продукции с i-той фабрики в j-тый центр поставок.

Неизвестные в этой модели должны удовлетворять следующим условиям:

  • объем перевозок не может быть отрицательным;

  • так как модель сбалансирована, то вся продукция должна быть вывезена с фабрик, а заказы поставщиков должны быть выполнены.

В результате получается следующая модель:

Необходимо минимизировать

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_15.gif

При ограничениях  

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_16.gif

где ai – объем производства на i-той фабрике, bj – спрос j- того центра поставок.

Выполним решение задачи средствами MS Excel.

Порядок действий:

1. Введем исходные данные о стоимости перевозок в диапазон A2:D5 (рисунок 13).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_17.gif
Рисунок 13 – Вид рабочего листа с данными и формулами

2. В ячейки A13:D13 введем формулы, вычисляющие объем продукции, ввозимой в пункты поставок. [1]

3. В ячейки E9:E12 введем формулы, вычисляющие объем продукции, вывозимой с фабрик.

4. В ячейку E13 введем целевую функцию

5. =СУММПРОИЗВ(A2:D6;A9:D12).

6. Выполним команду меню Данные/Поиск решения и заполним диалоговое окно Поиска решения следующим образом:

Установить целевую функцию                 $E$13

 

Минимальному значению                              0

 

Изменяя ячейки                                            $A$9:$D$12

 

Ограничения                                                 $A$13:$D$13=$A$14:$D$14

                                                                         $A$9:$D$12>=0

                                                                         $E$9:$E$12=$F$9:F$12

В Параметрах поиска решения установите флажок Линейная модель.

После нажатия кнопки Найти решение инструмент поиска решения найдет оптимальный план поставок продукции и соответствующие ему транспортные расходы.

Пример 6. Предприятие почтовой связи заказывает упаковочные ящики для четырех отделений на трех фабриках, расположенных в разных регионах. Первая фабрика может поставлять еженедельно до 300 ящиков, вторая – 400, третья – 500. Потребности отделений составляют соответственно ПО 1 - 350 ед., ПО2 – 300 ед., ПО3 - 190 ед., ПО 4 – 340 ед. Цена продажи у первой фабрики составляет 50р, у второй – 48р, у третьей - 45р.

Стоимость доставки единицы упаковочной продукции для предприятия почтовой связи зависит от фабрики поставщика и приведена в таблице.

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

1. Введем цену упаковочных ящиков в диапазон С4:Е4.

2. Диапазон С8:F10 заполним значениями стоимости доставки единицы товара в соответствии с исходными данными.

3. В диапазон С23:F25 введем расчетные формулы для нахождения затрат на единицу товара:

=$C$4+C8

=$C$4+D8

=$C$4+E8

=$C$4+F8

=$D$4+C9

=$D$4+D9

=$D$4+E9

=$D$4+F9

=$E$4+C10

=$E$4+D10

=$E$4+E10

=$E$4+F10

 

 

 

 

4. В ячейки С28:F30 введем формулы для расчета величины затрат для каждого отделения связи.

=C15*C23

=D15*D23

=E15*E23

=F15*F23

=C16*C24

=D16*D24

=E16*E24

=F16*F24

=C17*C25

=D17*D25

=E17*E25

=F17*F25

=СУММ(C28:C30)

=СУММ(D28:D30)

=СУММ(E28:E30)

=СУММ(F28:F30)

 

 

 

 

5. Диапазоны G15:G17 и C18:F18 заполним формулами ограничений, а в ячейках H15:H17 и C19:F19 значения ограничений по предложению поставщиков и потребностями каждого отделения в упаковочных ящиках.

6. В ячейку I4 введем функцию вычисления величины затрат, которую нужно минимизировать (рисунок 14).

7. Заполним окно Параметры поиска решения в соответствии с разработанной моделью (рисунок 15).

Результат поиска решения приведен на рисунке 16.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_18.gif
Рисунок 14 – Вид листа с формулами для решения транспортной задачи

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_19.gif
Рисунок 15 – Окно параметров поиска решения для транспортной задачи

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_20.gif
Рисунок 16– Вид рабочего листа с результатами решения транспортной задачи 

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

Заполним рабочий лист исходными данными: величину чистой приведенной стоимости в млн. руб. для каждого проекта введем в диапазон C9:C20, а количество сотрудников, для участия в каждом из проектов в течение трех последующих лет - в диапазон D9:F20. Результаты решения должны быть бинарными (1-«да», 0-«нет») и будут размещены в диапазоне A8:A20. В диапазоне B4:D4 введем ограничения по трудовым ресурсам для каждого исследуемого года. В диапазоне B6:D6 введем формулы для определения используемых людских ресурсов при полученном наборе проектов для каждого года работы. Понятно, что используемые ресурсы не должны превосходить имеющиеся. Функцией цели является максимальная величина чистой приведенной стоимости выбранных проектов.

На рисунке 17 приведен пример заполнения рабочего листа в соответствии с описанной моделью.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_21.gif

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_22.gif
Рисунок 17 – Листы с формулами и значениями ячеек после ввода формул

В окне Параметры поиска решения укажем ячейку с целевой функцией (ячейка $А$2), диапазон переменных ($А$9:$А$20) и систему ограничений ($A$9:$A$20=бинарное, $B$6:$D$6<=$B$4:$D$4). Установим флажок Сделать переменные без ограничений неотрицательными и выберем симплекс-метод для решения задачи. На рисунке приведены результаты поиска решения.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_23.gif
Рисунок 18 – Результаты поиска решения по поддержке проектов

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

Пример 5. Составим график выходов работников бригады по ремонту оборудования.

На рисунке 18 приведен вид рабочего листа с формулами для модели поиска решения.

Количество рабочих выражается формулой: =СУММ(A5:A11), это значение, которое нужно минимизировать.

В ячейках рабочего листа введены следующие формулы:

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_24.gif
Рисунок 18– Лист с исходными данными  

  • В окне Параметры решения  укажем ячейку, содержащую целевую функцию, которую нужно минимизировать (ячейка $A$2).

  • В качестве изменяемых ячеек переменных введем ссылки на ячейки $A$5:$A$11.

  • Добавим следующие ограничения: 

  • $A$5:$A$11=целое

  • и $C$12:$I$12>$C$14:$I$14

  • Устанавливаем флажок Сделать переменные без ограничений неотрицательными и выбираем для решения симплекс-метод.

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l12_25.gif
Рисунок 19 – Результаты поиска решения

ЛЕКЦИЯ 13

1   2   3   4   5   6

Похожие:

Лабораторная работа №2 iconЛабораторная работа. Получение и свойства оксидов, гидроксидов и солей
Лабораторная работа. Ряд напряжений металлов. Гальванические элементы. Электролиз юююююю

Лабораторная работа №2 iconЛабораторная работа по курсу «Теория языков программирования и методов...
Данная лабораторная работа предназначается для студентов специальности пвс изучающих «Теория языков программирования и методов трансляции»....

Лабораторная работа №2 iconЛабораторная работа №5
Лабораторная работа №5 включает 5 заданий. Для выполнения этих заданий необходимо ознакомиться с теоретическим материалом, приведенным...

Лабораторная работа №2 iconУрока физики по теме “ Измерение массы тела с помощью весов лабораторная...
Технологическая карта конструирования урока физики по теме “Измерение массы тела с помощью весов – лабораторная работа №3”

Лабораторная работа №2 iconЛабораторная работа
Уоу деревьев и кустарников, их биологией, значением в жизни человека, в мае –июне в период летней практики с многообразием травянистых...

Лабораторная работа №2 iconДата Группа
«Лабораторная работа Получение входных и выходных характеристик биполярного транзистора в схеме с оэ»

Лабораторная работа №2 iconЛабораторная работа 1
Цель работы: ознакомиться с топографической картой и научиться решать следующие задачи

Лабораторная работа №2 iconЛабораторная работа №14
Цель работы: определить экспериментально с помощью оборотного маятника ускорение свободного падения

Лабораторная работа №2 iconЛабораторная работа №4
Целью работы является изучение возможностей субд db2 по созданию и ведению баз данных

Лабораторная работа №2 iconКонспект открытого урока Предмет- биология
Методы и приемы: объяснительно иллюстративный, проблемный, лабораторная работа (исследовательский)


Учебный материал


При копировании материала укажите ссылку © 2013
контакты
5-bal.ru