Российская Экономическая Академия
Им. Г.В. Плеханова
Курсовая работа
по дисциплине
«Информационные технологии в экономике»
на тему:
«Оптимизация аварийного запаса продуктов для снабжения населения в районе чрезвычайных ситуаций».
Выполнила
студентка ЭМФ, 411-в
.
Проверил
Москва
2006
Структура:
I Формулировка задания………………………………………………………………………...3
Таб.1 «Таблица основных продуктов питания»……………………………………...3
Таб.2 «Продукт. Единицы упаковки»………………………………………………...3
II Задание 1……………………………………………………………………………………….4
Рис.1 «Пищевая ценность и калорийность продуктов»...………………..…………4
III Задание 2……………………………………………………………………………...………4
Таб.3 «Имена хх1…хх7»………………………………………………………………5
Таб.4 «Результаты работы инструмента Поиск Решений»………………………….5
Таб.5 «Продукт. Вес г/100»……………………………………………………………6
Рис.2 «Оптимальный суточный запас продуктов»…………………………………..6
IV Задание 3…………………………………………………………………………………...…7
Таб.6 «10-дневный запас для 5000 человек»…………………………………..……..7
V Задание 4……………………………………………………………………………………….8
Отчет по результатам…………………………………………………………….……8
Отчет по устойчивости…………………………………………………………….…..9
Отчет по пределам……………………………………………………………………..9
Задание:
| Таблица основных продуктов питания | ||||||
| Имя | Продукт | Кол-во веществ на 100г продукта | Калорийность в Ккал | Цена в руб. на 100г | ||
| белки | жиры | углеводы | ||||
| x1 | тушенка мясная | 13,4 | 27,8 | 0,4 | 315 | 4 |
| x2 | молоко сгущенное с сахаром | 7,1 | 8,6 | 54,9 | 334 | 2 |
| x3 | молоко сухое | 27,1 | 24,6 | 25,8 | 480 | 3 |
| x4 | сухари | 10,5 | 1,2 | 68,5 | 335 | 1,5 |
| x5 | макароны | 9,6 | 0,8 | 71,2 | 338 | 1,6 |
| x6 | масло топленое | 0 | 94,1 | 0 | 875 | 5 |
| x7 | яичный порошок | 49,9 | 34,2 | 0 | 523 | 8 |
Таб.1
Построить диаграмму пищевой ценности и калорийности основных продуктов.
Оптимизировать суточный аварийный запас продуктов в зависимости от показателей их калорийности, стоимости и пищевой ценности (формализовать задачу оптимизации суточного запаса как задачу линейного программирования).
Калорийность (К) ≥ Р1
Белки (Б) ≥ Р2
Общий вес набора продуктов (В) = Р3
Стоимость (С) 
min
Р1 = 2100 Ккал
Р2 = 25 г
Р3 = 3 г/100
Используя инструмент Поиск Решений, решить задачу оптимизации и построить диаграмму полученного решения.
На основе рассчитанного суточного оптимального запаса, рассчитать Q1 – дневной запас для Q2 – человек в единицах упаковки. При Q1 = 10, Q2 = 5000.
| Продукт | Ед. упаковки | Вес, г |
| тушенка мясная | банка | 378 |
| молоко сгущенное с сахаром | банка | 400 |
| молоко сухое | пачка | 500 |
| сухари | пачка | 250 |
| макароны | пачка | 500 |
| масло топленое | банка | 400 |
| яичный порошок | банка | 200 |
Таб.2
Создать соответствующую таблицу.
4) Составить итоговый отчет.
Задание 1.
Используя инструмент Мастер Диаграмм, строим диаграмму «Пищевая ценность и калорийность продуктов».
Р
ис.1
Задание 2.
Стоимость набора продуктов
Калорийность набора продуктов
Количество белков в наборе продуктов
Общий вес набора продуктов
min
≥ 2100
≥ 25
= 3
Используя данные Таб.1, составим задачу линейного программирования:
4
min
Далее заполняем соответствующую таблицу в Excel, меняя обозначения. В Excel обозначения выглядят следующим образом:
| x1 | хх1 |
| x2 | хх2 |
| x3 | хх3 |
| x4 | хх4 |
| x5 | хх5 |
| x6 | хх6 |
| x7 | хх7 |
Таб.3
Целевую функцию стоимости набора продуктов представляем формулой в ячейке:
D14 = 4*хх1+2*хх2+3*хх3+1,5*хх4+1,6*хх5+5*хх6+8*хх7
Ограничение по калорийности набора продуктов в ячейке:
D17 = 315*хх1+334*хх2+480*хх3+335*хх4+338*хх5+875*хх6+523*хх7
Ограничение по количеству белков в наборе продуктов в ячейке:
D18 = 13,4*хх1+7,1*хх2+27,1*хх3+10,5*хх4+9,6*хх5+0*хх6+49,9*хх7
Ограничение по общему весу набора продуктов в ячейке:
D19 = хх1+хх2+хх3+хх4+хх5+хх6+хх7.
Далее в меню Сервис выбираем инструмент Поиск Решений. Устанавливаем целевую функцию, равную минимальному значению, в ячейке E14. Набор изменяемых параметров находится в диапазоне $A$24:$G$24. Ограничения устанавливаются: 1) калорийность - $E$18 >= 2100; 2) белки - $E$19 >= 25; 3) общий вес - $E$20 = 3.
Р
езультаты работы инструмента Поиск Решений.
Таб.4
Калорийность набора продуктов
Количество белков в наборе продуктов
Общий вес набора продуктов
при минимуме целевой функции
Стоимость набора продуктов
- 2100 Ккал
- 25 г
- 3 г/100
= 12,02 руб.
| Продукты | Вес, г/100 |
| тушенка мясная | 0 |
| молоко сгущенное с сахаром | 0 |
| молоко сухое | 0,76 |
| сухари | 0,42 |
| макароны | 0 |
| масло топленое | 1,82 |
| яичный порошок | 0 |
Таб.5
Используя инструмент Мастер Диаграмм, строим диаграмму «Оптимальный суточный запас продуктов».
Рис.2
Задание 3.
Используя данные Таб.2 и рассчитанный суточный оптимальный запас, составим Таб.6:
| 10-дневный запас для 5000 человек | |||
| Продукт | Оптимальное значение веса продукта на одни сутки для человека в г/100 | 100 - г продукта; 5000 - человек; 10 - дней запаса; 500, 250, 400 - г продукта в ед. упаковки | 10-дневный запас для 5000 человек в ед. упаковки |
| молоко сухое | 0,76 | *100*5000*10/500 | 7600 |
| сухари | 0,42 | *100*5000*10/250 | 8400 |
| масло топленое | 1,82 | *100*5000*10/400 | 22750 |
Таб.6
Вывод:
Следовательно, на 10 дней для 5000 человек нужно брать 7600 пачек сухого молока, 8400 пачек сухарей и 22750 банок топленого масла, при суточном запасе на человека: молока сухого – 0,76 г/100; сухарей - 0,42 г/100; топленого масла - 1,82 г/100; при стоимости суточного оптимального запаса на одного человека 12,02 руб., калорийности 2100 Ккал, количестве белков 25 г и общем весе 3 г/100.
Задание 4.
Отчет:
по результатам;
по устойчивости.
по пределам;
| 1) Microsoft Excel 11.0 Отчет по результатам | ||||||
| Рабочий лист: [основная_таб.xls]Лист1 | ||||||
| Отчет создан: 26.04.2006 0:19:43 | ||||||
| Целевая ячейка (Минимум) | ||||||
| Ячейка | Имя | Исходное значение | Результат | |||
| $D$14 | Стоимость | 10,99976852 | 12,0239117 | |||
| Изменяемые ячейки | ||||||
| Ячейка | Имя | Исходное значение | Результат | |||
| $C$4 | хх1 | 0 | 0 | |||
| $C$5 | хх2 | 0 | 0 | |||
| $C$6 | хх3 | 0 | 0,761693616 | |||
| $C$7 | хх4 | 2,414351852 | 0,415057448 | |||
| $C$8 | хх5 | 0 | 0 | |||
| $C$9 | хх6 | 1,475648148 | 1,823248937 | |||
| $C$10 | хх7 | 0 | 0 | |||
| Ограничения | ||||||
| Ячейка | Имя | Значение | Формула | Статус | Разница | |
| $D$17 | калорийность Ограничения | 2100 | $D$17>=2100 | связанное | 0 | |
| $D$18 | белки Ограничения | 25,00000019 | $D$18>=25 | связанное | 0 | |
| $D$19 | вес Ограничения | 3 | $D$19=3 | не связан. | 0 | |
| $C$10 | хх7 | 0 | $C$10>=0 | связанное | 0 | |
| $C$4 | хх1 | 0 | $C$4>=0 | связанное | 0 | |
| $C$5 | хх2 | 0 | $C$5>=0 | связанное | 0 | |
| $C$6 | хх3 | 0,761693616 | $C$6>=0 | не связан. | 0,761693616 | |
| $C$8 | хх5 | 0 | $C$8>=0 | связанное | 0 | |
| $C$7 | хх4 | 0,415057448 | $C$7>=0 | не связан. | 0,415057448 | |
| $C$9 | хх6 | 1,823248937 | $C$9>=0 | не связан. | 1,823248937 | |
| 2) Microsoft Excel 11.0 Отчет по устойчивости | ||||
| Рабочий лист: [основная_таб.xls]Лист1 | ||||
| Отчет создан: 26.04.2006 0:19:44 | ||||
| Изменяемые ячейки | ||||
| Результ. | Нормир. | |||
| Ячейка | Имя | значение | градиент | |
| $C$4 | хх1 | 0 | 2,557192593 | |
| $C$5 | хх2 | 0 | 0,605120871 | |
| $C$6 | хх3 | 0,761693616 | 0 | |
| $C$7 | хх4 | 0,415057448 | 0 | |
| $C$8 | хх5 | 0 | 0,1048348 | |
| $C$9 | хх6 | 1,823248937 | 0 | |
| $C$10 | хх7 | 0 | 4,039474543 | |
| Ограничения | ||||
| Результ. | Лагранжа | |||
| Ячейка | Имя | значение | Множитель | |
| $D$17 | калорийность Ограничения | 2100 | 0,007042388 | |
| $D$18 | белки Ограничения | 25,00000019 | 0,028846612 | |
| $D$19 | вес Ограничения | 3 | -1,162089343 | |
| 3) Microsoft Excel 11.0 Отчет по пределам | |||||||||
| Рабочий лист: [основная_таб.xls]Отчет по пределам | |||||||||
| Отчет создан: 26.04.2006 0:19:44 | |||||||||
| Целевое | |||||||||
| Ячейка | Имя | Значение | |||||||
| $D$14 | Стоимость | 12,0239117 | |||||||
| Изменяемое | Нижний | Целевой | Верхний | Целевой | |||||
| Ячейка | Имя | Значение | предел | результат | предел | результат | |||
| $C$4 | хх1 | 0 | 0 | 12,0239117 | 0 | 12,0239117 | |||
| $C$5 | хх2 | 0 | 0 | 12,0239117 | 0 | 12,0239117 | |||
| $C$6 | хх3 | 0,761693616 | 0,761693616 | 12,0239117 | 0,761693616 | 12,0239117 | |||
| $C$7 | хх4 | 0,415057448 | 0,415057448 | 12,0239117 | 0,415057448 | 12,0239117 | |||
| $C$8 | хх5 | 0 | 0 | 12,0239117 | 0 | 12,0239117 | |||
| $C$9 | хх6 | 1,823248937 | 1,823248937 | 12,0239117 | 1,823248937 | 12,0239117 | |||
| $C$10 | хх7 | 0 | 0 | 12,0239117 | 0 | 12,0239117 | |||