Решение задач оптимизации в MS Excel

Министерство образования и  науки РФ

Байкальский государственный университет  экономики и права (БГУЭП)

Кафедра математики, эконометрики и  статистики

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Бизнес-проект

 

по дисциплине: Методы оптимальных  решений

на тему:

Решение задач  оптимизации в MS Excel

 

 

 

 

 

 

 

 

 

 

 

 

                          Выполнил: Кузнецов Борис Витальевич

                                                                                                         УБ-11-2

 

                                                                                     Проверил: Шерстянкина Нина Павловна

                                      к. т. н., доцент

 

 

 

 

 

 

 

 

 

 

Иркутск, 2013

Постановка  задачи

После реконструкции, возобновилось  производство на шинном заводе «IRK-Tyre»   Начался выпуск трех видов покрышек: летние, зимние, всесезонные. Покупателями продукции могут быть как и юридические, так и физические лица. Мы осуществляем оптовую и розничную торговлю.

          

Для производства шин необходимо:

1. Э/э
2. Финансы
3. Рабочая сила
4. Каучук
5. Технический углерод
6. Кремниевая кислота
7. Масла и смолы
8. Сера
9. Вулканизационные активаторы
10. Экологические наполнители

Завод планирует выпуск:

1. Шины летние;
2. Шины зимние;
3. Шины всесезонные.

Предполагаемая цена летних шин равна 3500 рублей за колесо.

Предполагаемая цена зимних равна 5500 рублей за булку колесо.

Предполагаемая цена всесезонных равна 4200 рублей за булку колесо.

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

Математическая  модель

Целевая функция:

3500 * x₁ + 5500* x₂ + 4200 * x₃ max

- где коэффициенты целевой функции – предполагаемая прибыль от продажи одной покрышки.

Ограничения:

1. Оплата э/э. Предприятию  необходимо производить оплату  за потребляемую электроэнергию.

0,06 * x₁ + 0,075 * x₂ + 0,7 * x₃ 20 тыс. руб.,

где коэффициенты при переменных означают часть э/э, которая условно пригодится для выпечки 1 покрышки.

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

0,14 * x₁ + 0,15 * x₂ + 0,13 * x₃ 500 тыс. руб.,

где коэффициенты при  переменных означают финансовые затраты  на выпуск одной покрышки.

3. Рабочая сила.

0,8 * x₁ + 0,7 * x₂ + 0,6 * x₃ 1500 чел./час.,

где коэффициенты при переменных – затраты времени в чел./час. на выпуск одной покрышки, включают в себя работу управляющего, технолога-резиносмесителя, прессовщика, грузчика, сборщика шин

4. Каучук

0,026 * x₁ + 0,025 * x₂ + 0,027 * x₃ 7.5 тыс. руб.,

где коэффициенты при переменных означают затраченного каучука на производство 1 покрышки.

5. Технический углерод

0,55 * x₁ + 0,55 * x₂ + 0,6 * x₃ 10 тыс. кг,

где коэффициенты при переменных означают количество кг тех. углерода, необходимого для производства 1 покрышки.

6. Кремниевая кислота

0,33 * x₁ + 0,33 *x₂ + 0,33 * x₃ 3400 л,

где коэффициенты при переменных означают кремниевой кислоты, необходимой для производства 1 покрышки.

7. Масла и смолы

0,13 * x₁ + 0,141 * x₂ + 0,13 * x₃ 370 кг,

где коэффициенты при переменных означают количество кг смол для выпуска 1 покрышки.

8. Сера

0,009 * x₁ + 0,01 * x₂ + 0,008 * x₃ 200 кг,

где коэффициенты при переменных означают количество кг серы необходимого для выпуска 1 покрышки.

9. Вулканизационные активаторы

0,013 * x₁ + 0,029 * x₂ + 0,031 * x₃ 100 л,

где коэффициенты при  переменных означают количество л активаторов необходимых для производства 1 покрышки.

10. Экологические наполнители

0,3 * x₁ + 0,31 * x₂ + 0,2 * x₃ 2100 кг.,

где коэффициенты при  переменных означают необходимое количество кг наполнителей для производства 1 покрышки

 

 

Первоначальная  таблица в MS Excel

 

Шины	Летние	Зимние	Всесезонные
Количество	1	1,0	1	Чистая прибыль
Чистая прибыль	3500	5500	4200	13200,00
Ограничения:				0
Э/э	0,06	0,075	0,07	0,2	20	тыс. руб.
Финансы	0,14	0,15	0,13	0,4	500	тыс. руб.
Рабочая сила	0,8	0,7	0,6	2,1	1500	чел./час.
Каучук	0,026	0,025	0,027	0,1	7,5	тыс. руб.
Технический углерод	0,55	0,55	0,6	1,7	10	тыс. кг
Кремниевая  кислота	0,33	0,33	0,33	1,0	3400	л
Масла и смолы	0,13	0,141	0,13	0,4	370	кг
Сера	0,009	0,01	0,008	0,0	200	кг
Вулканизационные  активаторы	0,013	0,029	0,031	0,1	100	л
Экологические наполнители	0,3	0,31	0,2	0,8	2100	кг

 

Итоговая таблица  в MS Excel

Шины	Летние	Зимние	Всесезонные
Количество	0	18,2	0	Чистая прибыль
Чистая прибыль	3500	5500	4200	100000,00
Ограничения:				0
Э/э	0,06	0,075	0,07	1,4	20	тыс. руб.
Финансы	0,14	0,15	0,13	2,7	500	тыс. руб.
Рабочая сила	0,8	0,7	0,6	12,7	1500	чел./час.
Каучук	0,026	0,025	0,027	0,5	7,5	тыс. руб.
Технический углерод	0,55	0,55	0,6	10,0	10	тыс. кг
Кремниевая  кислота	0,33	0,33	0,33	6,0	3400	л
Масла и смолы	0,13	0,141	0,13	2,6	370	кг
Сера	0,009	0,01	0,008	0,2	200	кг
Вулканизационные  активаторы	0,013	0,029	0,031	0,5	100	л
Экологические наполнители	0,3	0,31	0,2	5,6	2100	кг

 

Отчёт по результатам:

	Ячейка	Имя	Исходное значение	Окончательное значение
	$E$3	Чистая прибыль  Чистая прибыль	13200	100000
Ячейки переменных
	Ячейка	Имя	Исходное значение	Окончательное значение
	$B$2	Летние	1	0
	$C$2	Зимние	1	18
	$D$2	Всесезонные	1	0
Ограничения
	Ячейка	Имя	Значение ячейки	Формула	Состояние	Разница
	$E$5	Э/э	1,4	$E$5<=$F$5	Без привязки	18,6
	$E$6	Финансы	2,7	$E$6<=$F$6	Без привязки	497,3
	$E$7	Рабочая сила	12,7	$E$7<=$F$7	Без привязки	1487,3
	$E$8	Каучук	0,5	$E$8<=$F$8	Без привязки	7,0
	$E$9	Технический углерод	10,0	$E$9<=$F$9	Привязка	0,0
	$E$10	Кремниевая кислота	6,0	$E$10<=$F$10	Без привязки	3394,0
	$E$11	Масла и смолы	2,6	$E$11<=$F$11	Без привязки	367,4
	$E$12	Сера	0,2	$E$12<=$F$12	Без привязки	199,8
	$E$13	Вулканизационные  активаторы	0,5	$E$13<=$F$13	Без привязки	99,5
	$E$14	Экологические наполнители	5,6	$E$14<=$F$14	Без привязки	2094,4

 

 

Максимальная прибыль составляет 545,5 руб.

Летние шины: 0 шт – нерентабельный;

Зимние шины: 18 шт –рентабельный;

Всесезонные: 0 шт – нерентабельный.

Шины летние и всесезонные выпускать не выгодно.

Остатки ресурсов:

Э/э: 18,6 – недефицитный;

Финансы: 497,3 – недефицитный;

Рабочая сила: 1487,3 – недефицитный;

Каучук: 7 – недефицитный;

Технический углерод: 0 – дефицитный;

Кремниевая кислота:  3394 – недефицитный;

Масла и смолы: 367,4 – недефицитный;

Сера: 199,8 – недефицитный;

Вулканизационные активаторы: 99,5 – недефицитный;

Экологические наполнители: 2093,5 – недефицитный.

 

Отчёт по устойчивости:

		Окончательное	Приведенн.	Целевая функция	Допустимое	Допустимое
Ячейка	Имя	Значение	Стоимость	Коэффициент	Увеличение	Уменьшение
$B$2	Летние	0,0	-2000	3500	2000	1E+30
$C$2	Зимние	18,2	0	5500	1E+30	1650
$D$2	Всесезонные	0,0	-1800	4200	1800	1E+30
		Окончательное	Тень	Ограничение	Допустимое	Допустимое
Ячейка	Имя	Значение	Цена	Правая сторона	Увеличение	Уменьшение
$E$5	Э/э	1,4	0	20	1E+30	18,6
$E$6	Финансы	2,7	0	500	1E+30	497,3
$E$7	Рабочая сила	12,7	0	1500	1E+30	1487,3
$E$8	Каучук	0,5	0	7,5	1E+30	7,0
$E$9	Технический углерод	10,0	10000	10	136,7	10,0
$E$10	Кремниевая кислота	6,0	0	3400	1E+30	3394,0
$E$11	Масла и смолы	2,6	0	370	1E+30	367,4
$E$12	Сера Чистая	0,2	0	200	1E+30	199,8
$E$13	Вулканизационные  активаторы	0,5	0	100	1E+30	99,5
$E$14	Экологические наполнители	5,6	0	2100	1E+30	2094,4

 

 

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

Выпуск зимних шин будет рентабельным(т.е. оптимальным), пока его цена не будет ниже 3950 руб. за 1 штуку.

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

 

Летние шины:         

 

  

Зимние шины:

    

 

Всесезонные:

 

Изменение оптимального плана:

На производство поступил заказ от владельцев машин повышенной проходимости на выпуск внедорожных  шин

В результате проведённых  расчётов предприятие выяснило, что  выпускать такой вид покрышек не выгодно, и принято решение не выпускать их.

Итоговая таблица  в MS Excel

Шины	Летние	Зимние	Всесезонные	Внедорожные
Количество	0	18	0	0	Чистая прибыль
Чистая прибыль	3500	5500	4200	6000	100000
Ограничения:					0
Э/э	0,06	0,075	0,07	0,75	1,36	20	тыс. руб.
Финансы	0,14	0,15	0,13	0,2	2,73	500	тыс. руб.
Рабочая сила	0,8	0,7	0,6	0,7	12,73	1500	чел./час.
Каучук	0,026	0,025	0,027	0,3	0,45	7,5	тыс. руб.
Технический углерод	0,55	0,55	0,6	0,7	10,00	10	тыс. кг
Кремниевая  кислота	0,33	0,33	0,33	0,5	6,00	3400	л
Масла и смолы	0,13	0,141	0,13	0,15	2,56	370	кг
Сера	0,009	0,01	0,008	0,2	0,18	200	кг
Вулканизационные  активаторы	0,013	0,029	0,031	0,5	0,53	100	л
Экологические наполнители	0,3	0,31	0,2	0,4	5,64	2100	кг