Области применения эконометрических моделей

Определение параметра а

Выполнить установку  параметров линейной связи в Excel можно с помощью графика.

Построим график по имеющимся данным. Чтобы ось  Х отражала фактические данные, выберем  тип диаграммы Точечная. Выберем  опцию Добавить линию тренда. В  панели тренда во вкладке Тип  выбираем тип функции линейная.

Во вкладке  параметры введем название тренда и  установим флажки показывать уравнение  на диаграмме и поместить на диаграмму  величину достоверности аппроксимации (R²), т.е. коэффициент детерминации.

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

Установление  статистической значимости и общего качества уравнения линейной регрессии.

Рассчитаем  коэффициент детерминации. В столбце F рассчитаем вариацию (Yi - Yср)^2. В ячейке F7 проссумируем  ячейки F2: F6. В столбце G посчитаем вариацию остатков (Yi - a - bx)^2 , в столбце Н – вариацию регрессии (a+bx-Yср)^2, в H7 – ее сумму.

Вариация Y_i   представляется так:

Var(Y) = ∑ (Yi - Yср)^2 = ∑(Yi - a - bx)^2+∑(a+bx-Yср)^2

Обозначим ∑ (Yi - Yср)^2  через TSS (total sum of squares)- вся дисперсия;

Обозначим ∑(Yi - a - bx)^2  как ESS (erroe sum of squares)-необъясненная дисперсия;

Обозначим ∑(a+bx-Yср)^2 как RSS (regression sum of squares) – объясненная часть всей дисперсии.

Тогда можно  записать:

Var(Y)= TSS= ESS+ RSS

Коэффициент детерминации будет равен:

R²= 1- ESS/ TSS= RSS/ TSS.

Он по данной формуле рассчитан в ячейке G12. Также коэффициент детерминации можно рассчитать с помощью формулы КВ ПИРСОН

 

Представим  графически это понятие. Рассчитаем нарастающим итогом вариации остатков, регрессии и общую.

 

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

 

Рассчитаем  показатели статистической значимости. Проведем предварительные вычисления.

 

Стандартное отклонение ошибок регрессии определим с  помощью формулы СТОШYX   (B2:B6;A2:A6).

Для расчета  дисперсии параметра  b в ячейке М14 введена формула (M12^2)/K7.

В ячейке  М15 рассчитано стандартное отклонение параметра b, как корень квадратный из дисперсии (КОРЕНЬ(M14)).

В ячейке M16 рассчитана t-статистика параметра b как отношение величины параметра к его стандартному отклонению.

В ячейке М17 рассчитана дисперсия параметра а (=G7*L7/(5-2)/K7/5)

В   ячейках  М18 и М 19 аналогично рассчитаны стандартное  отклонение и t-статистика параметра а.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Задача  № 2

Сельскохозяйственное  предприятие культивирует четыре вида сельскохозяйственной продукции: гречиха, горох, соя, кукуруза. Для выращивания продукции, по технологической карте требуются следующие виды основных ресурсов: финансовые, внесение удобрений, затраты труда (в человеко-днях), объем которых ограничен. Известны потребность в ресурсах для каждого вида продукции, а также прибыль, получаемая от ее реализации сельхозпродукции.

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

Выращиваемые  культуры     

Гречиха	Горох	Соя	Кукуруза	Знак ограничения	Огранич. на ресурсы	Ресурсы
5	6	2	3	<	2000	Тыс. руб.
1	5	3	4	<	900	Удобрения т.
2	2	2,5	1,5	<	1500	Чел/дни
15	20	14	12			Прибыль от реализации 1 т зерна
Ограничения на количество зерна
30	20	200	15	минимальный объем
50			45	максимальный  объем

 

 

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

Целевая функция  в соответствии с требованиями задачи примет вид:

15Х₁ + 20Х₂ + 14Х₃ + 12Х₄ → max,

где Х₁,Х₂, Х₃, Х₄   - количество выращиваемых культур.

Существуют  ограничения на ресурсы :

• тыс. руб:

5Х₁ + 6Х₂ + 2Х₃ + 3Х₄  < 2000

• удобрения:

1Х₁ + 5Х₂ + 3Х₃ + 4Х₄  < 900

• чел/дни:

2Х₁ + 2Х₂ + 2,5Х₃ +1,5Х₄  < 1500

• количество выращиваемых культур:

30   > Х₁ < 50

20   > Х₂

200 > Х₃

15   > Х₄ < 45

 

Решение задачи.

Для решения  оптимизационных задач в MS EXCEL  используется команда «Поиск решения» в меню «Сервис».

1. В ячейку J11 помещаем целевую функцию A11*A12+B11*B12+C11*C12+D11*D12;
2. Начальные значения искомых переменных помещаем в ячейки                      А12; В12;С12;D12;Е12;
3. Значения единичной прибыли помещаем в ячейки А11; В11;С11;D11;Е11;
4. Стоимость выращивания 1 т. зерна (тыс. руб.) помещаем в ячейки                А8; В8;С8;D8;Е8;
5. Необходимое количество удобрений помещаем в ячейки                                   А9; В9;С9;D9;Е9;
6. Необходимое количество чел/дней помещаем в ячейки                                     А10; В10;С10;D10;Е10;
7. Формулу расчета общей стоимости (тыс. руб.) выращиваемых культур  A8*A12+B8*B12+C8*C12+D8*D12  помещаем в ячейку J8;
8. Формулу расчета общего количества удобрений A9*A12+B9*B12+C9*C12+D9*D12  помещаем в ячейку J9
9. Формулу расчета общего количества чел/дни   A10*A12+B10*B12+C10*C12+D10*D12  помещаем в ячейку J10

 

 

В диалоговом окне «Поиск решения» указываем адреса ячеек  и добавляем необходимые ограничения

 

 

 

Microsoft Excel 12.0 Отчет по  результатам
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$J$11	Прибыль от реализации 1 т зерна	0	4550
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$A$12	Гречиха	0	50
	$B$12	Горох	0	20
	$C$12	Соя	0	230
	$D$12	Кукуруза	0	15
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$J$8	Тыс. руб.	875	$J$8<=$F$8	не связан.	1125
	$J$9	Удобреният.	900	$J$9<=$F$9	связанное	0
	$J$10	Чел/дни	737,5	$J$10<=$F$10	не связан.	762,5
	$A$12	Гречиха	50	$A$12<=$A$16	связанное	0
	$D$12	Кукуруза	15	$D$12<=$D$16	не связан.	30
	$A$12	Гречиха	50	$A$12>=$A$15	не связан.	20
	$B$12	Горох	20	$B$12>=$B$15	связанное	0
	$C$12	Соя	230	$C$12>=$C$15	не связан.	30
	$D$12	Кукуруза	15	$D$12>=$D$15	связанное	0

 

Как видно из отчета, для  получения максимальной прибыли  сельхозпредприятию нужно выращивать 50т. гречихи, 20т. гороха, 230т. сои, 15т. кукурузы. Максимальная прибыль будет составлять 4550 тыс. руб.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Задача  № 3.

Исходные данные  

№ п/п	1	2	3	4	5	6
х1	9.42	6.33	7.45	10.0	6.6	9.1
х2	0.15	0.48	0.62	0.32	0.5	0.9

где х1 - число  автостоянок  на 1000 автомашин

      х2 - число сервисных предприятий  по ремонту

 

Провести классификацию  шести объектов, каждый из которых характеризуется двумя признаками (табл.1). В качестве расстояния между объектами принять , расстояние между кластерами исчислить по принципам: 1) “ближайшего соседа” и 2) “дальнего соседа”.

 

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

Создаем таблицу  с исходными данными и таблицы (матрицы) с расчетами (табл.1).

Задача № 3
№ п/п	1	2	3	4	5	6
х1	9,42	6,33	7,45	10	6,6	9,1
х2	0,15	0,48	0,62	0,32	0,5	0,9

 

 

Рис. 1 Зависимость  между числом автостоянок автомашин  и числом сервисных предприятий по ремонту.

 

 

 

 

Воспользуемся агломеративным иерархическим алгоритмом классификации. В качестве расстояния между объектами примем обычное  евклидовое расстояние. Тогда согласно формуле:

,

где l - признаки; k - количество признаков.

Расчеты расстояний

                           

 

1. Принцип “ближайшего  соседа”.

 

Полученные  данные помещаем в таблицу (матрицу):

        

                

 

Решение будем  производить по принципу «ближнего  соседа». Из матрицы расстояний следует, что объекты 2 и 3 наиболее близки Р =0,27  и поэтому объединяются в один кластер.

 

Составляем  матрицу расстояний

                    

 

                  

 

 

                           

 

      

 

Таким образом, при проведении кластерного анализа по принципу «ближайшего соседа» получили два кластера: S_(1,.2,.3), S_(4,5,6), расстояние между которыми равно: P_{(1,2,3); (4,5,6)} = 0,82

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

 

 

 

 

        

        1      0,82

      0,80    

 

     0,60                0,60

     0,40

                                 0,27

    0,20

  

             0    1         2           3        4         5          6

                                       Рис. 2 Дендограмма

 

 

 

 

  2. Принцип  “дальнего соседа”. 

 

Решение будем  производить по принципу «дальнего  соседа». Из матрицы расстояний следует, что объекты 2 и 3 наиболее близки Р =0,27  и поэтому объединяются в один кластер.

 

Составляем  матрицу расстояний

 

 

              

 

                           

 

 

                               

 

 

 

 

 

                                   

 

 

 

 

                      

                                            

 

Таким образом, при проведении кластерного анализа  по принципу «дальнего соседа» получили два кластера: S_(1,.2,.3), S_(4,5,6), расстояние между которыми равно: P_{(1,2,3); (4,5,6)} = 3,67

 

 

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