651
.pdfТаблица 4. Отчет по устойчивости
Microsoft Excel 9.0 Отчет по устойчивости Рабочий лист: [Задача модели.xls]Лист2
Изменяемые ячейки
|
|
Результ. |
Нормир. |
Целевой |
Допустимое |
Допустимое |
Ячейка |
Имя |
значение |
стоимость |
Коэффициент |
Увеличение |
Уменьшение |
$B$5 Значение переменных х1 выпуск вет.богучар,ц
$C$5 Значение переменных х2 выпуск вет.ветчина,ц
$D$5 Значение переменных х3 выпуск вет.подмоск,ц
$E$5 Значение переменных х4 выпуск вет.липецкая,ц
Значение переменных х5 выпуск сарделек $F$5 пикантных,ц
Значение переменных х6 выпуск сосисок $G$5 низкокалор,ц
|
- |
|
|
|
0 |
1894,845361 |
1000 |
1894,845361 |
1E+30 |
|
- |
|
|
|
0 |
3071,030928 |
1150 |
3071,030928 |
1E+30 |
3000 |
0 |
1200 |
1E+30 |
658,7628866 |
|
- |
|
|
|
0 |
1227,319588 |
1250 |
1227,319588 |
1E+30 |
525 |
0 |
2000 |
273,1958763 |
1,42227E+18 |
880,1546392 |
0 |
1500 |
0 |
142 |
$H$5 |
Значение переменных х7 |
выпуск п/к казачья,ц |
2075 |
0 |
1500 |
0 |
273,1958763 |
|
$I$5 |
Значение переменных х8 |
выпуск п/к ставропол.,ц |
5075 |
0 |
1800 |
1E+30 |
1104,123711 |
|
|
|
|
|
|
- |
|
|
|
$J$5 |
Значение переменных х9 |
выпуск фарша кур,ц |
0 |
146,3917526 |
1400 |
146,3917526 |
1E+30 |
|
|
Значение переменных х10 |
выпуск мяса кур |
|
|
|
|
|
|
$K$5 |
кускового,ц |
|
|
1050 |
0 |
1300 |
3153,608247 |
1E+30 |
$L$5 |
Значение переменных х11 |
объѐм говядины в/с,ц |
1522,5 |
0 |
0 |
0 |
3680,412371 |
|
$M$5 |
Значение переменных х12 |
объѐм говядины 1с,ц |
5937,75 |
0 |
0 |
0 |
0 |
|
$N$5 |
Значение переменных х13 |
объѐм гов.с/п,ц |
7764,75 |
0 |
0 |
0 |
721,6494845 |
|
$O$5 |
Значение переменных х14 |
объѐм свинины в/с,ц |
10904,75 |
0 |
0 |
0 |
0 |
|
$P$5 |
Значение переменных х15 |
объѐм свинины п/ж,ц |
2057,5 |
0 |
0 |
0 |
546,3917526 |
|
$Q$5 |
Значение переменных х16 |
объѐм свинины шпик,ц |
3086,25 |
0 |
0 |
0 |
0 |
$R$5 |
Значение переменных х17 |
объѐм свинины с/п,ц |
4526,5 |
0 |
0 |
0 |
0 |
$S$5 |
Значение переменных х18 |
объѐм ММО фарша кур,ц |
5100 |
0 |
0 |
1E+30 |
1546,391753 |
|
Значение переменных х19 |
объѐм мяса кур |
|
|
|
|
|
$T$5 |
кускового,ц |
|
1700 |
0 |
0 |
1E+30 |
4453,608247 |
$U$5 |
Значение переменных х20 |
объѐм куры с/п,ц |
3200 |
0 |
0 |
1546,391753 |
4830,541237 |
Ограничения
|
|
|
|
Результ. |
Теневая |
Ограничение |
Допустимое |
Допустимое |
Ячейка |
|
|
Имя |
значение |
Цена |
Правая часть |
Увеличение |
Уменьшение |
$V$7 |
1 |
выпуск вет.богучар,ц левая часть |
0 |
0 |
2060 |
1E+30 |
2060 |
|
$V$8 |
2 |
выпуск ветчины,ц левая часть |
0 |
0 |
2060 |
1E+30 |
2060 |
|
$V$9 |
3 |
вып.вет.подмосковной левая часть |
3000 |
0 |
830 |
2170 |
1E+30 |
|
$V$10 |
4 |
вет.подмосковной(max) левая часть |
3000 |
658,7628866 |
3000 |
2439,285714 |
2170 |
|
$V$11 |
5 |
вет.липецкая,ц левая часть |
0 |
0 |
2050 |
1E+30 |
2050 |
|
|
|
|
|
|
- |
|
|
|
$V$12 |
6 |
сарделек пикантных,ц левая часть |
525 |
273,1958763 |
525 |
1425 |
525 |
|
$V$13 |
7 |
сосисок низкокалор.,ц левая часть |
880,1546392 |
0 |
3050 |
1E+30 |
2169,845361 |
|
$V$14 |
8 |
п/к казачьей,ц левая часть |
2075 |
0 |
650 |
1425 |
1E+30 |
|
$V$15 |
9 |
п/к ставропольской,ц левая часть |
5075 |
1104,123711 |
5075 |
1897,222222 |
4305,769231 |
|
$V$16 |
10 |
фарша куринного,ц левая часть |
0 |
0 |
4550 |
1E+30 |
4550 |
|
|
|
|
|
|
- |
|
|
|
$V$17 |
11 |
мяса кур кускового,ц левая часть |
1050 |
3153,608247 |
1050 |
356,25 |
853,75 |
|
$V$18 |
12 |
необход.объѐм говядины в/с левая часть |
0 |
0 |
0 |
1291,730769 |
1,5046E+18 |
|
$V$19 |
13 |
объѐму говядины 1с,ц левая часть |
-5037,75 |
0 |
0 |
1E+30 |
5037,75 |
|
$V$20 |
14 |
объѐму свинины в/с,ц левая часть |
-10904,75 |
0 |
0 |
1E+30 |
10904,75 |
|
$V$21 |
15 |
объѐму свинины п/ж,ц левая часть |
0 |
0 |
0 |
1211,666667 |
1E+30 |
|
$V$22 |
16 |
объѐму свинины шпик,ц левая часть |
-1817,5 |
0 |
0 |
1E+30 |
1817,5 |
|
$V$23 |
17 |
объѐму ММО фарша кур,ц левая часть |
1,81899E-12 |
1546,391753 |
0 |
2104,75 |
853,75 |
|
$V$24 |
18 |
объѐму мяса кур кускового левая часть |
2,27374E-13 |
4453,608247 |
0 |
853,75 |
356,25 |
|
$V$25 |
19 |
уд.вес в туше говяд.в/с(min) левая часть |
2496,9 |
0 |
0 |
2496,9 |
1E+30 |
|
$V$26 |
20 |
уд.вес в туше говяд.в/с(max) левая часть |
0 |
0 |
0 |
1272,909836 |
1,75536E+18 |
|
$V$27 |
21 |
уд.вес говядины 1с(min),ц левая часть |
609 |
0 |
0 |
609 |
1E+30 |
$V$28 22 уд.вес говядины 1с(max),ц левая часть |
0 |
0 |
0 |
7764,75 |
609 |
||
$V$29 23 уд.вес свинины в/с(min),ц левая часть |
823 |
0 |
0 |
823 |
1E+30 |
||
|
|
|
-4,54747E- |
|
|
|
|
$V$30 24 уд.вес свинины в/с(max),ц левая часть |
13 |
0 |
0 |
4526,5 |
823 |
||
$V$31 25 уд.вес свинины п/ж(min),ц левая часть |
617,25 |
0 |
0 |
617,25 |
1E+30 |
||
$V$32 26 уд.вес свинины п/ж(max),ц левая часть |
-1,7053E-13 |
0 |
0 |
1211,666667 |
881,7857143 |
||
$V$33 27 уд.вес свин.шпик(min),ц левая часть |
617,25 |
0 |
0 |
617,25 |
1E+30 |
||
$V$34 28 уд.вес свин.шпик(max),ц левая часть |
1,13687E-13 |
0 |
0 |
4526,5 |
617,25 |
||
$V$35 29 уд.вес ММО фарш кур(min) левая часть |
400 |
0 |
0 |
400 |
1E+30 |
||
$V$36 |
30 |
уд.вес ММО фарш кур(max) левая часть |
0 |
1546,391753 |
0 |
2104,75 |
400 |
$V$37 |
31 |
уд.вес мяса кур кусков(min) левая часть |
400 |
0 |
0 |
400 |
1E+30 |
|
|
|
-1,13687E- |
|
|
|
|
$V$38 |
32 |
уд.вес мяса кур кусков(max) левая часть |
13 |
4453,608247 |
0 |
853,75 |
356,25 |
$V$39 |
33 общ.объѐм мяса-сырья кур,ц левая часть |
10000 |
1545,773196 |
10000 |
6190,441176 |
2095,588235 |
4. Анализ полученных результатов
Анализ полученных результатов проведем на основании таблицы 4.
Проанализируем первый блок отчета по устойчивости – «Изменяемые ячейки».
Оптимальным ассортиментом продукции колбасного цеха птицефабрики будет ассортимент, приведенный в таблице 5. (В столбец «выпуск» взяты значения из столбца «Результ. значение» блока «Изменяемые ячейки» отчета по устойчивости).
Таблица 5. Оптимальная структура ассортимента выпуска продукции
Ассортимент продукции |
Выпуск, ц |
Структура, % |
|
|
|
Ветчина “Подмосковная” |
3000 |
23,8 |
Сардельки “Пикантные” |
525 |
4,2 |
Сосиски “Низкокалорийные” |
880 |
7,0 |
|
|
|
Колбаса полукопчѐная “Казачья” |
2075 |
16,5 |
Колбаса полукопчѐная “Ставропольская” |
5075 |
40,2 |
Мясо кур кусковое |
1050 |
8,3 |
Всего |
12605 |
100 |
Анализируя данные таблицы 3, можно сделать вывод, что преобладающее место в структуре ассортимента занимает производство колбасы п/к “Ставропольской” 40,2%, затем ветчина “Подмосковная” 23,8% и колбаса п/к “Казачья” 16,5%. Предложенный в таблице ассортимент выпуска продукции является выгодным для производства. При реализации предложенного оптимального плана выпуска продукции колбасного цеха птицефабрика получит максимальную прибыль в размере 19582732 рубля (размер прибыли см на Листе1в ячейке целевой функции).
Анализ невыгодной продукции
Выпуск продукции ветчина “Богучар”, ветчина “Ветчина”, ветчина “Липецкая” и фарш куриный в структуре оптимального плана отсутствуют, являются невыгодными для колбасного цеха птицефабрики.
При выпуске и реализации 1ц ветчины «Богучар» прибыль уменьшится на 1894,8 руб., ветчины “Ветчина” – на 3071 руб.,
ветчины липецкой – на 1227,3 руб., фарша кур - на 146,4 руб. (Оценки взяты из столбца «Нормир. стоимость» блока «Изменяемые ячейки» отчета по устойчивости).
Для выполнения предлагаемого оптимального плана по выпуску продукции необходимы следующие объѐмы категорий мяса - сырья (таблица 6).
Таблица 6. Мясо-сырьѐ по категориям, необходимое для выпуска оптимального ассортимента продукции
Категории мяса-сырья |
Требуемый объѐм сырья |
Расход сырья |
|
|
количество, ц |
% |
на план |
Говядина в/с |
1522,5 |
10 |
1522,5 |
Говядина 1с |
5937,75 |
39 |
900 |
Говядина с/п |
7764,75 |
51 |
- |
Итого говядины |
15225 |
100 |
2422,5 |
Свинина в/с |
10904,75 |
53 |
- |
Свинина п/ж |
2057,5 |
10 |
2057,5 |
Свинина шпик |
3086,25 |
15 |
1268,75 |
Свинина с/п |
4526,5 |
22 |
- |
Итого свинины |
20575 |
100 |
3326,25 |
ММО фарш кур |
5100 |
51 |
5100 |
Мясо кур кусковое |
1700 |
17 |
1700 |
Куры с/п |
3200 |
32 |
- |
Итого мясо кур |
10000 |
100 |
6800 |
Всего |
45800 |
|
12548,75 |
Анализируя данные таблицы 6, можно сделать вывод, что выгодно использовать категории мяса-сырья по удельным весам в туше придерживаясь максимальной границы, заданной в модели задачи (см таблицу 1), для всего мяса-сырья. Для выполнения оптимального плана по выпуску продукции категории мяса-сырья – говядина в/с, свинина п/ж, ММО фар кур и мясо кур кусковое используется в полном объѐме.
Категории мяса-сырья – говядина 1с, свинина шпик используются не полностью на 5037,75 ц и на 1200,25 ц, соответственно данного сырья. Так же категория мяса-сырья – свинина в/с осталась невостребованной при реализации предложенного оптимального плана. Это даѐт возможность для работы над расширением ассортимента выпуска попутной продукции из невостребованного в полном объѐме мяса-сырья.
Анализ ограничений
I Блок. Проанализировав характеристику ограничений оптимальной задачи, можно сделать вывод, что выгодной выпускаемой продукцией для колбасного цеха является ветчина “Подмосковная”, при увеличении производства на 1ц прибыль увеличится на 658 рублей. Выпуск продукции совпадает с максимальным ограничением и его выгодно увеличивать, но оно ограничено производственной мощностью цеха (<= 3000 ц).
Производство сарделек “Пикантных” не выгодно для цеха, так как каждый ц сарделек приносит убыток 273 рубля. Сардельки “Пикантные” вошли в оптимальный план, потому что в модели задано еѐ производство больше или равно 525 ц.
Производство сосисок “Низкокалорийных” выгодно, но производится в объѐме 880 ц. Это ниже производственной мощности на 2170 ц, ввиду ограничения использования ресурса мяса-сырья входящего в состав сосисок.
Производить колбасу полукопчѐную “Казачью” цеху выгодно. Ресурсы и общие прочие условия производства позволяют производить колбасу в количестве 2075 ц, что на 1425 ц выше минимального предела производства продукции.
Производство колбасы полукопчѐной “Ставропольской” ограничено производственными мощностями (<= 5075 ц), но еѐ выгодно производить и увеличивать выпуск продукции на 1897 ц. При реализации 1ц колбасы прибыль увеличивается на 1104 рублей.
Фарш куриный выгодно производить и увеличивать производство, что принесѐт на 1ц прибыли в размере 1546 рублей. Но выпуск фарша кур не вошѐл в оптимальный план, так как он используется в полном объѐме как мясо-сырьѐ в производстве колбас. При реализации 1ц фарша прибыль уменьшается на 146 рублей.
Производство мяса кур кускового не выгодно для цеха, так как каждый ц мяса приносит убыток 3153 рубля.
Мясо кур вошло в оптимальный план, потому что в модели задано еѐ производство больше или равно 1 050 ц. Рекомендуется либо увеличивать его выпуск на 356 ц, либо уменьшить до 854 ц.
II Блок. Требуемый объѐм говядины высшего сорта в количестве 1522,5 ц используется полностью.
Требуемый объѐм говядины 1 сорта в размере 5937,75 ц недоиспользуется на 5 037,75 ц, что может послужить резервом для выпуска новой продукции.
Требуемый объѐм свинины высшего сорта недоиспользуется в полном объѐме 10904,75 ц, что может послужить резервом для выпуска новой продукции.
Требуемый объѐм свинины полужирной используется в полном объѐме 2057,5 ц.
Требуемый объѐм свинины шпик в размере 3 086,25 ц недоиспользуется на 1817,5 ц, что может послужить резервом для выпуска новой продукции.
ММО фарш кур и мяса кур кускового используется полностью 5100ц и 1700 ц. Данное мясо-сырьѐ выгодно производить, увеличение производства на 1ц увеличивает прибыль на 1546 рублей от фарша кур и на 4454 рубля от мяса кур кускового.
III Блок. Так как удельный вес использования категорий мяса-сырья в туше не влияет на получение максимальной прибыли,
то в ограничениях 19,20; 21,22; 23,24; 25,26; 27,28 удельный вес определяется по максимально заданной границе для каждой категории мяса-сырья.
Ограничение 29,30 удельный вес категории мяса-сырья ММО фарш кур выгодно использовать по максимуму, при увеличении на 1ц фарша прибыль увеличивается на 1 546 рублей.
Ограничение 31,32 удельный вес категории мяса-сырья мясо кур кускового выгодно использовать по максимуму, при увеличении на 1ц мяса кур кускового увеличивается прибыль на 4 454 рубля.
IV Блок. Мясо-сырье кур используется полностью (10000 ц). Его выгодно увеличивать. При увеличении его объѐма на 1ц прибыль увеличится на 1546 рублей.
Для колбасного цеха птицефабрики предложен оптимальный план выпуска ассортимента продукции, который удовлетворяет условиям, заданным при составлении оптимальной модели задачи.
Производство предложенной продукции выгодно для цеха и при увеличении объѐмов выпуска продукции на 1ц приносит соответствующее увеличение прибыли. При этом имеется необходимая ресурсная база для производства продукции и резерв для производства дополнительной продукции.
При реализации предложенного оптимального плана выпуска продукции колбасный цех птицефабрики получит максимальную прибыль в размере 19 582 732 рубля.
Задание: Решить задачу по вариантам на ЭВМ и
проанализировать полученные результаты.
ГЛАВА 2. ЭКОНОМИКО-СТАТИСТИЧЕСКОЕ МОДЕЛИРОВАНИЕ
2.1. Построение и использование трендовой модели
Многие экспериментальные данные можно интерпретировать как временные ряды - последовательность измерений, полученных в определенные моменты времени ti, где i - порядковый номер измерения на оси времени. Такие ряды характеризуются некоторой тенденцией развития процесса во времени и называются трендовыми. Используя трендовые модели, можно выдавать прогнозы на краткосрочный и среднесрочный периоды. Microsoft Excel 2007 имеет средства для создания трендовых моделей, встроенные в построитель диаграмм.
Задание: Построить трендовую модель с применением ЭВМ и показать ее использование по вариантам, приведенным в исходных данных. Ниже приведен образец решения задачи
Табл1. |
Исходные данные |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ОПЛАТА ТРУДА, тыс.руб. (варианты) |
|
|
|
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ГОДЫ |
|
1 |
2 |
3 |
|
4 |
|
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
|
13 |
14 |
15 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2006 |
|
20 |
5 |
10 |
|
15 |
|
11 |
12 |
15 |
6 |
8 |
7 |
9 |
13 |
|
14 |
16 |
17 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2007 |
|
30 |
15 |
11 |
|
20 |
|
21 |
19 |
23 |
16 |
18 |
16 |
16 |
21 |
|
24 |
29 |
31 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2008 |
|
32 |
20 |
20 |
|
30 |
|
35 |
25 |
31 |
21 |
23 |
21 |
25 |
33 |
|
39 |
36 |
39 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2009 |
|
40 |
30 |
25 |
|
41 |
|
40 |
36 |
43 |
32 |
34 |
32 |
36 |
48 |
|
49 |
46 |
46 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2010 |
|
45 |
35 |
35 |
|
48 |
|
56 |
50 |
50 |
39 |
46 |
48 |
52 |
65 |
|
62 |
60 |
58 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Этапы построения уравнения тренда:
1.Подготавливаем массив исходных статистических данных в
Microsoft Excel 2007 (Рис.1).
Рис.1. Исходные данные
2.Построим уравнения тренда. Для этого:
Выделяем столбец (Y) с шапкой;
В меню выбираем «Вставка» → «График» → «График с маркерами». Получаем график «Объем реализации»
(Рис.2).
|
Объем реализации, ц (Y) |
||
700 |
|
|
|
600 |
|
|
|
500 |
|
|
|
400 |
|
|
|
300 |
|
|
Объем реализации, ц |
|
|
(Y) |
|
|
|
|
|
200 |
|
|
|
100 |
|
|
|
0 |
|
|
|
1 |
2 |
3 |
4 |
|
|
|
Рис.2 - График |