Хід виконання роботи

Припустимо, що між показником Y – обсягом випущеної продукції і факторами X₁ – обсягом основних засобів, X₂ – працезатратами існує стохастична залежність: (виробнича регресія Кобба-Дугласа).

Для оцінки параметрів виробничої регресії приводимо її до лінійної форми. Після логарифмування і заміни змінних Y₁=ln(Y), Z₁=ln(X₁), Z₂=ln(X₂) отримаємо приведену лінійну регресію Y₁=a₀+a₁Z₁+a₂Z₂, де a₀=ln(a_o). Для розв’язування задачі складаємо електронну таблицю в Excel.

Вихідні дані розміщуються в блоці A3:C13. Значення факторів для прогнозу – в комірках A14,B14. Перетворені змінні Z1=ln(X1), Z2=ln(X2), Y1=ln(Y) розміщуються відповідно в діапазонах D3:D14, E3:E14, F3:F13. Для обчислення значень у цих блоках використовуємо вбудовану математичну функцію Ln. У комірку D3 записуємо формулу (=ln(A3)) та копіюємо в діапазон D3:F13. Перетворені значення використовуємо для складання системи нормальних рівнянь.

Рис. 13. Логарифмування змінних

Для обчислення коефіцієнтів при невідомих a₀, a₁, a₂ і вільних членів знаходимо: транспоновану, обернену матрицю та добуток матриць.

Порядок знаходження оцінок параметрів регресії:

1. Знаходимо транспоновану матрицю в блоці Е17:О19 по відношенню до матриці в діапазоні А17:С27, використовуючи в категорії «Ссылки и массивы» вбудовану функцію (=ТРАНСП(A17:C27)) для вибору масиву одночасно натискаємо Shift+Ctrl+Enter.

2. Знаходимо добуток матриць в діапазоні E21:G23, використовуючи вбудовану матричну функцію (=МУМНОЖ(E17:O19;A17:C27)) (діапазон даних першої матриці E17:O19, діапазон даних другої матриці A17:C27).

3. Обернену матрицю знаходимо в діапазоні E25:G27, використовуючи вбудовану математичну функцію (=МОБР(E21:G23)).

4. Добуток матриць знаходимо в діапазоні Е29:О31, використовуючи вбудовану математичну функцію (=МУМНОЖ(E25:G27;E17:O19)).

5. Оцінки вектора знаходяться в діапазоні Е33:Е35, використовуючи вбудовану математичну функцію МУМНОЖ (блок даних матриці (E29:O31), блок даних матриці Y1 (F3:F13)).

Рис. 14. Мариця Z

Рис. 15. Знаходження коефіцієнтів a₀, a₁, a₂

У діапазоні G3:G14за формулою Y₁=a₀₁+a₁Z₁+a₂Z₂ (=$E$33+$E$34*D3+$E$35*E3) обчислюємо значення показника приведеної лінійної регресії. У діапазоні H3:H14, з використанням вбудованої математичної функції EXP та значень діапазону G3:G14, знаходяться розрахункові базисні та прогнозні значення показника.

Рис. 16. Розрахункові значення показника

Для визначення адекватності вибраної математичної моделі експериментальним даним обчислюємо S^2 та D[Y]. Для цього обчислюємо значення квадратів відхилень фактичних значень показника від розрахункових та середніх значень показника у діапазоні I3:K13 ((Y-Yr)^2 (=(C3-H3)^2), (Y-Ys)^2 (=(C3-$J$16)^2), (Y1-Y1r)^2 (=(G3-F3)^2)). Для обчислення середніх значень діапазона C3:C13використовуємо вбудовану статистичну функцію Ys (= СРЗНАЧ (C3:C13)) – середнє статистичне значення.

Рис. 17. Квадрат відхилень фактичних значень від розрахункових

Коефіцієнт детермінації та розрахункове значення критерію Фішера обчислюється у комірках В39, В40відповідно. В комірці В37 знаходимо кількість показників (=СЧЁТ(C3:C13)).Значення коефіцієнта кореляції R В38 (=КОРЕНЬ(1-I15/J15)).

.

Розрахункове значення критерію Фішера Fр обчислюється у комірці В39 за формулою (=СТАНДОТКЛОН(H3:H13)^2*8/(2*I15)).

Значення S^2 дорівнює і обчислюється у комірці В40 (=K15).

Табличне критичне значення критерію Стьюдента Ткр розраховується в комірці В41за формулою (=СТЬЮДРАСПОБР(0,05;8)).

Критичне (табличне) значення критерію Фішера розраховується в комірці D39 за формулою (=FРАСПОБР(0,05;2;8)).

Рис. 18. Встановлення адекватності прийнятої математичної моделі статистичним даним та надійного інтервалу

Виділяємо діапазон комірок D40:F40 викликаємо функцію (=МУМНОЖ(C14:E14;E25:G27)), для вибору масиву одночасно натискаємо Shift+Ctrl+Enter. Для обчислення довірчого інтервалу оцінки прогнозного значення обчислюємо добуток , використовуючи вбудовану статистичну функцію скалярного добутку D42 (=СУММПРОИЗВ(блок1:блок2)). Вектор Z_p знаходиться у рядку D40:F40, а матриця – в діапазоні C14:E14 (=СУММПРОИЗВ(D40:F40;C14:E14)).

Значення DY1 розраховується у клітинці В42 за формулою:

.

В42 =B41*КОРЕНЬ(K15*(1+D42)/(B37-4))

Значення Y1p-DY1 (=G14-B42), Y1p+DY1 (=G14+B42) обчислюються відповідно в комірках В43:В44.

У комірках В45:В46, з використанням вбудованої математичної функції EXP та значень комірок В43:В44, знаходимо Yp-DY1 та Yp+DY1.

Для наочного уявлення взаємозамінюваності факторів побудуємо ізокванти. Спочатку у комірці F33 за допомогою ЕХР знаходимо значення a₀ (=EXP(E33)).

Змінна y_i залежно від працезатрат X₂ (H50:H60) дорівнює (В3:В13), основні засоби X₁ (J50:J60)обчислюються за формулою X₁=b/(X₂^(a₂/a₁)) J50 (=(F50/$F$33/H50^$E$35)^(1/$E$34)). Використовуючи Мастер диаграмм, будуємо графік ізокванти Y=y₁.

Рис. 19. Побудова ізокванти

Дата добавления: 2015-07-26; просмотров: 317; Нарушение авторских прав

Мы поможем в написании ваших работ!