Метод Монте-Карло (стр. 2 из 2)

=СРЗНАЧ(A3:A9) и =СРЗНАЧ(B3:B9).

В ячейки В12, В13 вводятся формулы для определения оценок параметров соответственно a и b.

=(B11*C10-B10*A10)/(B11*D10-A10^2) – для параметра а;

=D12-B12*D11 – для параметра b.

а=-0,602, b=301,55,

уравнение регрессии:

Y=-0,602×Х + 301,55

2.3. Для вычисления расчетных значений

(і= ) в ячейку E3 вводим формулу с абсолютными ссылками координат-параметров a и относительной ссылкой координаты . Полученную формулу в ячейке E3 копируем в блок E4:E9 В ячейке E10 будет находиться сумма блока E3:E9. Поскольку математическое ожидание отклонения фактических данных от расчетных равняется нулю, то при правильном выполнении расчетов значения ячеек B10 и E10 будут совпадать.

Для определения адекватности принятой эконометрической модели экспериментальным данным воспользуемся F-критерием Фишера. Расчетное значение критерия Фишера определяется по формуле:

Значение

вычисляем соответственно в блоках F3:F9, G3:G9, H3:H9, а их суммы в блоке F10:H10.

Значения коэффициента детерминации

вычисляется в ячейке F11 с использованием встроенной математической функции КОРЕНЬ.

Для оценки коэффициента корреляции

в ячейку I3 вводим формулу для вычисления значения

и копируется в блок I4:I9. Сумма блока I3:I9 вычисляется в ячейке I10.

Значения коэффициента корреляции вычисляется в ячейке F13.

K_kor=-0,672194

Расчетное значение критерия Фишера: F_роз= 4,121495

Табличное значение F-критерия для вероятностей P=0,95 и числа степеней свободы

K₁ = m = 1,

K₂ = n – m – 1 = n – 2 = 7 – 2 = 5 равняется: F(0.95;1;5)= 5,99

Поскольку

, то с надежностью P=0,95 эконометрическую модель можно считать неадекватной экспериментальным данным. Об этом также говорит невысокое значение коэффициента корреляции K_kor=-0,672194

Таблица с расчетными данными:

Задача 3

Предприятие имеет 7 филиалов по реализации продукции. Руководству предприятия необходимо, исходя из статистических данных об их деятельность оценить силу зависимости товарооборота (Y) от факторов: объема торговой площади (S), интенсивности потока покупателей (N) и стоимости основных фондов (F). С помощью линейной регрессионной модели вида

,

установить связь между товарооборотом и двумя наиболее существенными факторами.

3.1. Вычислить коэффициенты корреляции между результативным признаком Y и факторами: S, N и F.

3.2. Определить два фактора, которые наиболее влияют на товарооборот Y.

3.3. Вычислить параметры регрессионной модели а , b , с методом наименьших квадратов.

3.4. Оценить соответствие построенной зависимости статистическим данным.

Вариант 7	S, кв.м.	15	23	18	18	19	17	23
	N, чел.	567	568	569	345	234	453	345
	F,тис.грн.	10	7	11	28	15	10	57
	Y,млн.грн	0,25	0,24	0,23	0,28	0,23	0,27	0,27

Выполнение задания

3.1. Исходные данные факторов размещаем в блоке B2:D18, а показатели в столбце E2:E8.

3.2. В блоке A13:C14 используя встроенную функции Excel =КОРРЕЛ() находим коэффициенты корреляции между показателем Y и факторами Х1, Х2, Х3

Корф. кор.-ции
Y - X1	Y - X2	Y - X3
0,208604	-0,60362	-0,04747

3.3. Как видно из корреляционной матрицы для регрессионной модели можно выбрать две переменные – Х1 и Х2, так как для них значения коэффициента корреляции с показателем близки к 1 и равны 0,208604и -0,60362 соответственно.

3.4. Допустим, что между показателем Y и факторами Х1, Х2 существует линейная зависимость

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

, где

Если помножить матричное уравнение слева на матрицу

, то для оценки параметров вектора получим формулу

.

Нахождение оценок параметров регрессии:

1. Находим транспонированную матрицу

в блоке E13:K15 по отношению к матрице в блоке A2:C8, используя в категории "Ссылки и массивы" встроенную функцию ТРАНСП(A2:C8).

2. Находим произведение матриц

в блоке A18:C20, используя встроенную математическую функцию МУМНОЖ(блок данных первой матрицы A18:C20; блок данных второй матрицы A2:C8).

3. Обратную матрицу

находим в блоке D18:F20, используя встроенную математическую функцию =МОБР(A18:C20).

4. Произведение матриц

находим в блоке H18:H20, встроенную математическую функцию =МУМНОЖ(E13:K15;E2:E8).

5. Оценки вектора находим в блоке J39:J41, встроенную математическую функцию =МУМНОЖ(D18:F20;H18:H20).

[X^T][X]^-1[X^T]Y

0,32512

0,00040

-0,00005

a= 0,00040, b= -0,00005, c= 0,32512.

Уравнение регрессии:

Y=0,00040X1 + -0,00005X2 + 0,32512

3.5. Проверим адекватность принятой модели экспериментальным данным с помощью критерия Фишера. Расчетные значения Y_расч считаем в столбце F по формуле Y_расч=0,00040Х₁+-0,00005Х₂+0,32512..

Рассчитываем F-статистику Фишера с m и (n- m- 1) степенями свободы:

где m — количество факторов, которые вошли в модель; m=2

n – общее количество наблюдений; n=7

В ячейках F2:F10 находятся расчетные значения показателя, а в ячейках G2:G10 квадраты их отклонений от экспериментальных значений.

В ячейках H2:H10 квадраты отклонений от среднего значения.

Расчетное значение F_расч= 1,19895497

По F- таблице Фишера находим критическое значение Fкр с m и (n-m-1) степенями свободы: F_крит(0,95;2;4)= 6,94

Расчетное значение критерия 1,19895497 меньше критического, значит с надежностью

можно считать, что принятая математическая модель неадекватна по экспериментальным данным.

Таблица с расчетными данными: