Принятие решений с помощью Excel Задача 1
Работа добавлена на сайт samzan.net:
|
Примеры взяты из книги А. А .Минько «Принятие решений с помощью Excel» |
Задача 1. Линейная модель
Завод «Лакокраска» производит два типа краски: Алую и Белую.
Производственные мощности позволяют выпускать в месяц не более 500 тонн краски суммарно. Одна тонна Алой краски приносит прибыли в среднем 2000 руб. (от 1500 до 2300 руб), а одна тонна Белой краски – 2500 руб. ( от 2100 до 3000 руб).
Отдел маркетинга требует, чтобы Алой краски за месяц произвели не менее 200 тонн, поскольку уже есть договора на такое количество, а Белую краску нельзя производить более 150 тонн, поскольку большее количество трудно реализовать.
На изготовление красок имеется сырье, общее количество которого, используемого для производства краски, не должно превышать месячный запас.
Табл. 1
|
Сырье |
Алая краска, т |
Белая краска, т |
Месячный запас, т |
|
Сырье 1 |
0,05 |
0,10 |
50 |
|
Сырье 2 |
0,07 |
0,08 |
30 |
|
Сырье 3 |
0,04 |
0,07 |
25 |
Построим математическую модель.
Суммарная прибыль при производстве Х1тонн Алой каски при прибыли С1=2000 р/тонна и Х2 Белой краски при прибыли С2= 2500р/тонна составит:
Суммарная прибыль Z = 2000*Х1+2500*Х2 (руб) Это – целевая функция, которую надо максимизировать.
Теперь запишем ограничения:
1.Суммарный объем производства краски не должен превышать 500 тонн: Х1+Х2 <=500
2.Маркетинговые требования:Х1>=200 и Х2<=150
3. Ограничения на сырье (табл. 1). Таким образом, на производство Х1тонн Алой краски и Х2 Белой краски потребуется 0,05*Х1+0,1*Х2 тонн сырья 1. Эта величина не должна превышать 50 тонн. Отсюда получаем значение: 0,05*Х1+0,10*Х2 <=50. Аналогично с сырьем 2 и 3:
0,07*Х1+0,08*Х2 <=30
0,04*Х1+0,07*Х2 <=25
4.Еще одно ограничение, что Х1 иХ2 не должны быть отрицательными: Х1>=0; Х2>=0. Поскольку в п.2 мы уже писали, что Х1>=200, то неравенство Х1>=0 исключаем.
Таким образом:
Максимизировать Z = 2000*Х1+2500*Х2
Х1>=200;
Х2<=150;
0,05*Х1+0,10*Х2 <=50.
0,07*Х1+0,08*Х2 <=30
0,04*Х1+0,07*Х2 <=25
Начинаем максимизировать прибыль предприятия от производства названных красок с помощью Excel.
В ячейки В4 и С4 вначале ставим произвольные значения (100 тонн).
В ячейку D8 вставляем нашу целевую формулу:
B8*B4+C8*C4
В диапазоне В11:С17 записаны коэффициенты функций ограничений, в диапазоне D11:D17 вычисляются значения левых частей ограничений, в диапазоне Е11:Е17 записаны знаки неравенств ограничений, в F11:F17 – значения правых частей ограничений.
Произведем вычисления левых частей целевого уравнения в ячейках D11:D17:
=СУММПРОИЗВ($B$4:$C$4;B11:C11)
Приступим к Поиску Решения: Данные- Анализ – Поиск решения.
Задача 2. Линейная модель
Фабрика детских игрушек ОАО «Салют» на одном из участков собирает игрушечные автомобили: легковой, гоночный и грузовик. При сборке каждой модели используют три типа операций: ручная сборки, механическая сборка и проверка сборки.
Ежедневный суммарный фонд рабочего времени на выполнение каждой операции ограничен 490, 500 и 580 минут. Доход на одну игрушку каждого вида составляет соответственно 85, 100 и 125 руб. Время выполнения каждой операции в минутах, необходимое на сборку одной игрушки, показано в следующей таблице.
|
Легковой автомобиль |
Гоночный автомобиль |
Грузовой автомобиль |
|
|
Операция 1 |
2 |
3 |
3 |
|
Операция 2 |
3 |
2 |
5 |
|
Операция 3 |
4 |
2 |
6 |
Ежедневно собирается 50 легковых авто, 100 гоночных и 30 грузовиков при общей доходности 18000 руб. в день.
Руководство решила дополнительно выпускать модель экскаватора с доходностью 150 руб. каждая модель экскаватора требует 3, 4 и 3 минуты для выполнения операций трех видов. Фонд рабочего времени ограничен имеющимися рабочими и остается неизменным. Надо определить, выгодно ли фабрике производство новых игрушек.
Целевая функция вычисляет общую доходность:
Z = 85*Х1+100*Х2+ 125*Х3+150*Х4 (руб)
Эту функцию требуется максимизировать.
Записываем ограничения, которые диктуются фондом рабочего времени:
2*Х1+3*Х2+ 3*Х3+3*Х4<=490 (ограничение по 1-ой операции)
3*Х1+2*Х2+ 5*Х3+4*Х4<=500 (ограничение по 2-ой операции)
4*Х1+2*Х2+ 6*Х3+3*Х4<=580 (ограничение по 3-ой операции)
Сюда надо добавить ограничения неотрицательности Х1>=0; Х2>=0;
Х3>=0; Х4>=0; а так же условие целочисленности Х1,Х2,Х3,Х4 – целые числа.
|
Производственный план фабрики игрушек "Салют" Переменные решения Х1 Х2 Х3 Х4 50 100 30 0 Коэф. Целевой функции Значение целевой функции С1 С2 С3 С4 Z 85 100 125 150 18000 Ограничения Коэффициенты Левая часть Правая часть Время операции 1 2 3 3 3 490 <= 490 Время операции 2 3 2 5 4 500 <= 500 Время операции 3 4 2 6 3 580 <= 580 Сначала определим значение целевой функции в ячейке F8 Затем, ограничения по фонду рабочего времени в ячейки F11:F13 Приступим к Поиску решения: Данные – Поиск решения Щелкаем Добавить |
||||||||
|
Щелкаем Добавить |
||||||||
|
Добавить Получим Щелкаем Параметры |
||||||||
|
Корректируем параметры ОК |
||||||||
|
Выполнить |
ОК
Задача 3 . Нелинейная модель.
Завод «Электроника» наряду с другой продукцией, производит изделия А, Б и В одной номенклатурной группы, на одном и том же оборудовании с общим фондом рабочего времени. Экономисты подсчитали, что если производятся ХА, ХБ и ХВ изделий этой группы, то общая стоимость их производства равна:
С =40*ХА+120*ХБ+80*ХВ – 20*ХА*ХБ – 10*ХБ*ХВ +30*ХА*ХВ (руб).
Также установлено, что цены, по которым реализуются эти товары, также зависят от количества произведенной продукции – чем больше произведено, тем ниже цены:
Цена за 1 изделие А = 150(2-ХА/500)
Цена за 1 изделие Б = 250(2,5-ХБ/500)
Цена за 1 изделие В = 300(1,5-ХВ/500)
Производственные мощности позволяют изготавливать суммарно не более 500 штук изделий всех типов.
В ячейки В4:D4 введем произвольные 10, 30, 100, а в ячейку Е15 их сумму = В4+С4+D4.
Запишем целевую функцию прибыли Z:
Прибыль Z= прибыль от продажи - стоимость производства =
цена за 1 изделие А*ХА = цена за 1 изделие А*ХА + цена за 1 изделие Б*ХБ + цена за 1 изделие В*ХВ – С = 150(2-ХА/500)*ХА + 200(2,5 -ХБ/500)*ХБ + 300(1,5-ХВ/500)*ХВ - (40*ХА+120*ХБ+80*ХВ – 20*ХА*ХБ – 10*ХБ*ХВ +30*ХА*ХВ) = 260ХА + 380ХБ + 370ХВ – 0,3(ХА)2 – 0,4(ХБ)2 – 0,6(ХВ)2 + 20*ХА*ХБ + 10*ХБ*ХВ - 30*ХА*ХВ (руб)
Поскольку полученная функция явно не линейная, то имеем модель нелинейной оптимизации.
Запишем ограничения в ячейки B8:В8; B10:В10 B12:В12 ( для вашей ориентации соответствующие цифры в целевой функции и в табличной модели на листе Excel окрашены соответственно в красный, синий и зеленый цвета).
Введем в ячейку Е12 формулу «суммпроизв»
Приступим к Поиску решения: Данные – Поиск решения
Добавить
Щелкаем Параметры
ОК
Выполнить
Задача 3 . Расчет аренды помещения.
Фирма сдает в аренду свои помещения сроком не менее, чем на квартал блоками по 1 тысячи метров квадратных. Стоимость аренды зависит от сроков аренды и составляет соответственно 1000, 900, 800 и 750 руб. за 1 кв. метр для аренды сроком на один, два, три квартала и четыре квартала (год) соответственно.
Компания-арендатор подсчитала, что ей необходимо в новом году
12000 кв.м в 1квартале, 15000кв.м. во П квартале, 10000кв.м. в Ш квартале и 20000 в 1V квартале. За год на аренду компания может выделить 50 млн. руб: по 12 млн. руб в 1 и П кварталах, 8 млн. в Ш и 18 млн в четвертом. Требуется составить план аренды, минимизирующий затраты компании по оплате складов.
PAGE \* MERGEFORMAT 1
2. Древние германцы и их языки
3. Инвестиционный портфель
4. Управление международным сотрудничеством КУРСОВАЯ РАБОТА Индустрия культуры и её рол
5. Малая Балканская 303 в форме заочного голосования Уважаемые собственн
6. Бухгалтерский учет внешнеэкономической деятельности
7. Тема урока- Подготовка к сочинениюрассуждению с учетом требований к заданию с развернутым о
8. Российская академия правосудия Кафедра гуманитарных и социальноэкономических дисциплин
9. Перспективи та особливості електронного подання податкової звітності в Україні
10. даралы~ т~сініктерін пайдаланады
11. Данные Знания Данные ~ это совокупность сведений зафиксированных на определенном носителе в форме при
12. Асн жанры і стылі арыгін старабел літры
13. РЕФЕРАТ дисертації на здобуття наукового ступеня кандидата економічних наук.2
14. .По класции ЮНКТАД нетар меры- Паратариф 1 ~платежи и сборы взымаем за тов пересекаем т.
15. САМАРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ
16. Услуги общественного питания
17. Революции 1648 и 1789 годор
18. Тема Великий дім ~ держава лад у ньому ~ закон
19. Організація обліку та контролю основних засобів на підприємстві
20. Пояснительная записка Планирование составлено в соответствии с программой общеобразовательных учреждени