Лабораторная работа № 7 Моделирование оптимальной отраслевой структуры производства сельскохозяйственного предприятия

Цель: научиться составлять и решать типовые задачи оптимизации производства средствами MS Excel.

Задачи: освоить решение задачи линейного программирования использую программную надстройку «Поиск решения» симплекс-методом

Теоретический материал

Приведем наиболее типичные задачи оптимального внутрихозяйственного анализа и планирования.

В растениеводстве:

1. Задача оптимизации структуры производства продуктов растениеводства.

2. Задача оптимального размещения посевов сельскохозяйственных культур по участкам (предшественникам, отделениям, бригадам и т. д.).

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

4. Задача оптимизации структуры кормопроизводства.

5. Задача оптимизации структуры зерновых (овощных, технических) культур.

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

7. Задача оптимизации севооборотов (культурооборотов для защищенного грунта).

8. Задача выяснения зависимости урожайности различных сельскохозяйственных культур от основных факторов.

В животноводстве:

1. Задача оптимизации производства продуктов животноводства.

2. Задача оптимизации структуры стада скота (птицы).

3. Задача оптимизации оборота стада скота (птицы).

4. Задача оптимизации кормовых рационов для различных поло- возрастных групп скота (птицы).

5. Задача оптимизации кормосмесей.

6. Задача оптимального распределения заготовленных кормов между различными видами и половозрастными группами скота и птицы.

7. Задача оптимального выбора типовых животноводческих помещений (ферм, комплексов).

8. Задача выявления зависимости продуктивности скота (птицы) от основных факторов.

9. Задача оптимального размещения скота (птицы) по отделениям (фермам, бригадам).

Общехозяйственные задачи:

1. Задача определения оптимальной специализации и сочетания отраслей в сельскохозяйственном предприягии.

2. Задача определения оптимальных размеров сельскохозяйственного предприятия.

3. Задача оптимального размещения производства продукции в сельхозпредприятии по отделениям (бригадам, фермам).

4. Задача оптимизации структуры МТП.

5. Задача оптимизации использования МТП по видам работ.

6. Задача оптимизации состава автопарка.

7. Задача оптимизации внутрихозяйственных перевозок.

8. Задача выявления зависимости результатов производства (прибыли, рентабельности, объема выпускаемой продукции, производительности труда и т. п.) от основных факторов.

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

Например, задачи по оптимизации структуры производства продукции растениеводства ставится так:

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

Основное требование к информации экономико-математической задачи: – соответствие информации тому временному периоду, которому отвечает смысл задачи. Так, если рассчитываются кормовые рационы на зимний период конкретного года, то и вся исходная информация (по наличию, качеству, стоимости кормов) должна ему соответствовать. В задачу не следует закладывать непроверенные или недостаточно точные данные, тем более, если задача носит практическую направленность.

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

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

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

В хозяйстве имеется 2630 га пашни, 245 га естественных сенокосов и 361 га пастбищ. Ресурсы труда составляют 349500 чел.-ч.

Основными отраслями являются молочное скотоводство, свиноводство, производство зерна и картофеля.

Плановое задание по продаже продукции следующее (ц):

зерно –12 000, картофель – 6 000, молоко – 9340, мясо –7120.

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

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

Таблица 1

Основные показатели по растениеводству

Показатели	Урожай-ность ц	Содержится в 1 ц			Затраты труда на 1 на 1 га, чел.-ч		Материаьно-денежные затраты на1 га, y.e		Стоимость валовой продукции с га, y.e.
		Кормовых единиц, ц	Перевари-ваемого протеина, ц
Культуры:
Озимая пшеница:
Зерно	40			19,4		214,27		461,6
Яровой ячмень:
Зерно	35	1,21	0,081
Солома	40	0,36	0,012	14,5		171,72		353,05
Овес:
Зерно	30	1	0,085
Солома	40	0,31	0,014	12,7		147,22		328,5
Картофель:	250	0,3	0,016	31,8		781,71		4780
Кормовые корнеплоды:
Корни	600	0,12	0,009
Ботва	400	0,09	0,021	81,3		521,21		2326
Кукуруза на силос	400	0,2	0,014	28,6		362,09		444
Однолетние травы на зеленый корм	200	0,16	0,027	10,3		227,74		266
Многолетние травы:
Зеленый корм	150	0,22	0,019	11,9		128,88		199,5
Сено	50	0,5	0,052	14,4		241,47		283
Силос	150	0,22	0,03	16,1		273,58		283
Естественные сенокосы	12	0,42	0,048	13,3		130,54		38,76
Естественные пастбища	50	0,22	0,021

Основные данные по животноводству приведены в таблице 2, причем затраты и выход продукции по крупному рогатому скоту рассчитаны на одну структурную корову (50 % коров в стаде), а по свиноводству – на 1 сложную свиноматку (12,317 голов).

Таблица 2

Основные показатели по животноводству

(в расчете на структурную корову и сложную свиноматку)

Показатели	Крупный рогатый скот	Свиньи
Выход на голову:
Молока, ц	19,1
в т. ч. реализуется:	18,2
Мяса, ц	1,74	26
в т. ч. реализуется:	1,65	25,7
Потребность в кормах-всего, ц к. ед.	67,03	209,87
в т. ч. концентраты	10,05	136,42
грубых	12,07
сочных	21,45	10,49
корнеклубнеплодах	3,35	31,48
зеленых	20,11	12,59
животных		18,89
Потребность в перев. протеине ,ц	6,98	22,4
Затраты труда, чел.-ч	338,5	385,4
Матер.-денежные затраты (без стоимости кормов) , y.e.	344,22	540,97
Стоимость валовой продукции, y.e.	1172,25	5591,3

При разработке экономико-математической модели необходимо учесть, что:

1. В структуре зерновых площадь овса должна составлять не менее 30%;

2. Отходы, естественные потери кормов и при хранении (%):

зерна – 2, грубых – 15, картофеля – 20, силоса – 32;

3. Реализуется 50% картофеля;

4. Нормы высева (посадки) семян с учетом страхового фонда (ц): озимой пшеницы – 2,7, ячменя – 2,7, овса – 2,5, картофеля – 34;

5. В структуре кормления должно быть соломы не более 20% грубых кормов; кукурузного силоса не более 30% сочных; однолетних на зеленый корм не менее 10% зеленых;

6. На корм свиньям мясокостная мука покупается в пределах полной потребности, в 1ц которой содержится 0,89ц корм. ед., 0,337ц перевариваемого протеина, цена 1ц – 26 у.е.;

7. Цена реализации 1 ц (у.е.): зерна пшеницы – 14, картофеля –13, молока – 36, мяса крупного рогатого скота – 178, мяса свиней – 175.

Подготовка исходных данных

При составлении развернутой экономико-математической модели по оптимизации структуры производства необходимо подготовить исходные данные. Для расчета выхода товарной продукции и питательных веществ с единицы площади необходимо знать, какая часть продукции теряется, какая используется на семена и какая на корм. Соответствующим образом подготавливается и остальная исходная информация. Затем определяется перечень переменных и составляется модель.

Подготовим необходимые для составления развернутой модели исходные данные (таблица 3).

Таблица 3

Распределение продукции растениеводства и выход питательных веществ с 1 га посева кормовых культур

Показатели	Урожай-ность, ц	Отходы, потери естеств. и при хранении, ц	Потребность в семенах с учетом страх. фонда	Используется, ц		Выход с га
				на продажу	на корм	корм. ед, ц	пер. прот, ц
Культуры:
Озимая пшеница:
Зерно	40	0,8	2,7	36,5
Яровой ячмень:
Зерно	35	0,7	2,7		31,6	38,236	2,559
Солома	40	6			34	12,24	0,408
Овес:
Зерно	30	0,6	2,5		26,9	26,9	2,287
Солома	40	6			34	10,54	0,476
Картофель:	250	50	34	125	41	12,3	0,656
Кормовые корнеплоды:
Корни	600				600	72	5,4
Ботва	400				400	36	8,4
Кукуруза на силос	400	128			272	54,4	3,808
Однолетние травы на зел.корм	200				200	32	5,4
Многолет.травы:
Зеленый корм	150				150	33	2,85
Сено	50	7,5			42	21,25	2,21
Силос	150	48			102	24,44	3,06
Естественные сенокосы	12	1,8			10,2	4,284	0,49
Естественные пастбища	50				50	11	1,05

Определим перечень переменных:

х₁ – площадь под озимой пшеницей, га;

х₂ – площадь под яровым ячменем, га;

х₃ – площадь под овсом, га;

х₄ – площадь под картофелем, га;

х₅ – площадь под кормовыми корнеклубнеплодами, га;

х₆ – площадь под кукурузой на силос, га;

х₇ – площадь под однолетними травами на зеленый корм, га;

х₈ – площадь под многолетними травами на зеленый корм, га;

х₉ – площадь под многолетними травами на сено, га;

х₁₀ – площадь под многолетними травами на силос, га;

х₁₁ – площадь под естественными сенокосами, га;

х₁₂ – площадь под естественными пастбищами, га;

х₁₃ – поголовье крупного рогатого скота, структурных коров;

х₁₄ – поголовье свиней, сложных свиноматок;

х₁₅ – солома ячменя, ц корм. ед.;

х₁₆ – солома овса, ц корм. ед.;

х₁₇ – мясокостная мука, ц;

х₁₈ – материально-денежные затраты, у.е.;

х₁₉ – стоимость валовой продукции, у.е.

Составление развернутой экономико-математической модели

Запишем систему ограничений в развернутом виде

1. Условия по производственным ресурсам:

– по площади пашни:

;

– по площади естественных сенокосов:

– по площади естественных пастбищ:

– по трудовым ресурсам:

2. Условия по обеспечению животноводства питательными веществами:

– кормовыми единицами:

– перевариваемым протеином:

3. Условия по обеспечению животноводства отдельными группами кормов:

– концентратами:

или после преобразований:

– грубыми:

– сочными:

– корнеклубнеплодами:

– зелеными:

– животными:

4. Условия по соотношению отдельных групп кормов:

– производство соломы ячменя:

или после преобразования:

– производство соломы овса:

или после преобразований:

– удельный вес соломы в грубых:

или после преобразований:

– удельный вес кукурузного силоса в сочных:

или после преобразований:

– удельный вес зеленого корма однолетних трав в зеленых:

или после преобразований:

5. Условия по структуре посева сельскохозяйственных культур:

– удельный вес овса в структуре зерновых:

или после преобразований:

6. Условия по гарантированному производству товарной продукции;

– зерна пшеницы:

– продажа картофеля:

– продажа молока:

–продажа мяса:

7. Условие суммирования материально-денежных затрат:

или после преобразований:

8. Условие суммирования валовой продукции:

или после преобразований:

Целевая функция - максимум валового дохода:

Ограничения задачи в табличной форме

Для решения задачи на компьютере:

1. Создайте форму для ввода условий задачи.

– введите в таблицу Excel переменные (см. предыдущие лабораторные работы).

2. Введите исходные данные.

3. Введите в ячейкуV2 формулу:

=СУММПРОИЗВ(B2:U2;$B$28:$U$28)

или

=B2*$B$28+C2*$C$28+D2*$D$28+E2*$E$28+F2*$F$28+G2*$G$28+H2*$H$28+I2*$I$28+J2*$J$28+K2*$K$28+L2*$L$28+M2*$M$28+N2*$N$28+O2*$O$28+P2*$P$28+Q2*$Q$28+R2*$R$28+S2*$S$28+T2*$T$28+U2*$U$28

Рис.2 – Диалоговое окно заполнения ячейки V2

4. Протяните формулу до ячейки V27 (V26) – в зависимости от варианта введенной формулы.

5. В ячейку целевой функции V27 введите формулу: =S27*S28+T27*T28

Рис.2 – Диалоговое окно заполнения ячейки V27

5. Вызовите команду: Данные / Поиск решения:

Рис.3 – Диалоговое окно команды «Поиск решения»

6. Программа MS Excel осуществит поиск оптимального решения (таблица 4).

7. Просмотрите отчет по Результатам и отчет по Устойчивости.

Таблица 4

Матрица экономико-математической задачи определения структуры производства в сельскохозяйственном предприятии после получения оптимального решения

ограничения	х1	х2	х3	х4	х5	х6	х7	х8	х9	х10	х11	х12	х13	х14	х15	х16	х17	х18	х19	х20	Реше-ние	тип огра-ничения	общее кол-во
пашня	1	1	1	1	1	1	1	1	1	1											2630		2630
сенокосы											1										0		245
пастбища												1									361		361
по трудовым ресурсам	19	14,5	12,7	31,8	81,3	28,6	10,3	11,9	14,4	16,1	13,3		338,50	385,40							326462		349590
кормовые ед.		38,24	26,9	12,3	108	54,4	32,0	33,0	21,3	22,4	4,3	11,0	-67,03	-209,87	1	1	0,89				3467		0
протеин		2,56	2,287	0,656	13,8	3,808	5,400	2,850	2,21	3,06	0,49	1,05	-6,93	-22,40	0,408	0,476	0,337				742		0
концентраты		38,24	26,9										-10,05	-136,42							0		0
грубые									21,25		4,281		-12,07		1	1					6655		0
сочные						54,4			22,44				-21,45	-10,49							0		0
корнеклубни				12,3	72								-3,35	-31,48							10381		0
зеленые					36		32	33				11	-20,11	-12,59							0		0
животные														-18,89			0,89				0		0
производство соломы ячменя		12,24													-1						8527		0
производство соломы овса			10,540													-1					4632		0
удельный вес соломы в грубых									-4,25		-0,857				0,8	0,8					-2570		0
удельный вес кукурузы в сочных						38,08				-6,732											0		0
удельный вес зеленого корма					-3,6		28,800	-3,300				-1,1									0		0
удельный вес овса	-0,3	-0,3	0,7																		0		0
производство пшеницы	36,5																				12000		12000
продажа картофеля				125																	36767		6000
продажа молока													18,2								9340		9340
продажа мяса													1,65	25,7							7120		7120
мат-ден затраты	214,3	171,7	147,22	781,71	521,2	362,09	227,7	121	241,5	273,6	130,54		344,22	540,97			26	-1			0	=	0
валовая продукция	461,6	353,1	328,5	4780	2326	444	266	200	283	283	38,76		1172,25	5591,3					-1		0	=	0
прибыль	461,6			2390									1172	5591						-1	0	=	0
целевая функция																		-1	1		3418254		макс
переменные	329	697	439	294	225	0	42	0	605	0	0	361	513	244	0	0	5181	1200653	4618908	2821128

Задание для самостоятельного выполнения

Решите задачу на:

1) максимум валового дохода,

2) минимум издержек,

3) максимум прибыли.

Для этого:

1. Удалите значения переменных в строке «Переменные». Введите новую целевую функцию в прежнюю ячейку целевой функции (V27) =Т27*Т28. Далее вызовите Данные, Поиск решения. В диалоговом окне укажите значение целевой функции = max; изменяя ячейки $B$28:$U$28. Выполнить.

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

2. Удалите значения переменных в строке «Переменные». Введите в ячейку целевой функции (V27) формулу =S27*S28 /Данные/Поиск решения/Задача на минимум/Выполнить/ОК. Проанализируйте результаты.

3. Удалите значения переменных в строке «Переменные». Введите в ячейку целевой функции (V27) формулу =U28/Сервис/Поиск решения/Задача на максимум/Выполнить/ОК. Проанализируйте результаты.

4. Составьте итоговую таблицу по трем вариантам исследования, рассчитайте уровни рентабельности и, сопоставив показатели эффективности, дайте заключение о целесообразности инновационного решения по данному предприятию.