Тема: Побудова багатофакторної нелінійної моделі

Мета заняття: На підставі статистичних даних показника Y факторів X₁, X₂ знайти оцінки параметрів моделі, якщо припустити, що вона має наступну структуру: Y=ln(a₀+ a₁/ X₁+ a₂*X₂).

ü Оцінити адекватність прийнятої математичної моделі статистичним даним з надійністю Р=0,95.

ü Якщо прийнята математична модель адекватна знайти:

- оцінку прогнозу та з надійністю Р=0,95 його довірчий інтервал;

- оцінки часткових коефіцієнтів еластичності для прогнозу.

Хід роботи

Висувається гіпотеза, що між факторами X₁, X₂ і показником Y існує стохастична залежність Y=ln(a₀+ a₁/ x₁+ a₂x₂).

1. Завантажити програму EXCEL.

2. Сформувати таблицю вихідних даних, заповнивши діапазон комірок А4:C16 (рис.1).

3. Для приведення регресії до лінійного виду зробимо заміну величин Y₁=exp(Y), Z₁=1/X_{1 .} Для того, щоб таблицю можна було використати, необхідно ввести Z₂= Х_{2 .}

4. У комірці G20 за допомогою вбудованої функції СЧЕТЗ знайдемо об’єм вибірки n.

5. Розрахуємо суму величин , _, , _{, , ,} використовуючи вбудовану функцію СУММ або Автосумму, результати розмістити у комірки А18,В18,С18,D18,Е18 й F18 відповідно (рис.1).

6. Складемо систему нормальних рівнянь, використовуючи вбудовані функції СУММ і СУММПРОИЗВ (для стислості запису ці вбудовані функції позначимо відповідно через С и СП).

7. Симплекс-таблиця для рішення системи нормальних рівнянь записується в наступному виді:

8. Для розв’язання системи нормальних рівнянь використаємо звичайні жорданові виключення, вибираючи за розв'язувальні елементи діагональні елементи.

9. Процедура обчислення одного кроку ЗЖВ:

1) елементи, що не належать розв'язувальному рядку та розв'язувальному стовпцю, обчислюються за формулою: , так для комірки С28:=C23-C$22*$B23/$B$22. Скопіювати формулу у решту комірок таблиці D28,E28,C29:Е29;

2) елементи розв'язувального стовпця ділять на розв'язувальний елемент, так для комірки В28: = B23/B$22. Скопіювати формулу у комірку розв'язувального стовпця В29;

3) елементи розв'язувального рядка змінюють знак на протилежний і ділять на розв'язувальний елемент, так для комірки С27: =-C22/$B22. Скопіювати формулу у комірки розв'язного рядка D27, Е27;

4) замість розв'язувального елемента береться його обернене значення В27:=1/B22.

10. Таким же чином виконуються інші кроки ЗЖВ. Після трьох кроків ЗЖВ у блоці В37:D39 буде знаходитися обернена матриця блоку В22:D24, а у блоці Е37:Е39 вектор оцінених параметрів (рис.2).

11. Якщо допустити, що після двох кроків ЗЖВ значення наступного розв'язувального елемента близьке нулю, тоді можна вважати, що існує залежність між факторами, тобто мультиколінеарність. У цьому випадку одну із змінних слід виключити із розв’язку.

12. Запишемо отримане рівняння =ln(a₀+ a₁/ x₁+ a₂ x₂) у блоці А40:J40.

13. Знайдемо розрахункові значення для наведеної моделі, розмістивши значення у блоці G4:G17. Спочатку перше значення G4:=$E$37+$E$38*D4+$E$39*E4. Отриману формулу скопіюємо у інші комірки блоку (G5:G17). У комірці G18 розрахуємо суму комірок блоку G4:G16 (рис.1).

14. Розрахункове значення фактора блоку Н4:Н17 знайдемо, пролагорифмуючи значення блоку G4:G17. У комірці Н18 розрахуємо суму комірок блоку Н4:Н16 (рис.1).

15. Для визначення адекватності прийнятої моделі експериментальним даним у комірці G21 обчислимо розрахункове значення критерію Фишера, для цього введемо у комірку G21 формулу G21:=СТАНДОТКЛОН(H4:H16)^2*12*5/I18, де у комірці I18 попередньо розраховано суму квадратів різниці Y й Y_розр (рис.1)_, а у комірці G22 – табличне (F_(0,05;2;10)=4,103) (рис.2).

16. Порівняємо отримані результати та зробимо висновок проадекватність прийнятої моделі експериментальним даним.

17. Знайдемо оцінку прогнозного значення показника у комірці Н17 і внесемо його в таблицю прогнозних значень А46, ввівши дані прогнозу X_1, X₂ й Y у комірки А17, В17 і С17 відповідно (X_1п=1,5; X_2п=8; Y_п=1) (рис.1,3).

18. Обчислимо значення частинних коефіцієнтів еластичності за формулами:

,

Значення частинних коефіцієнтів еластичності обчислимо у комірках Е46, F46 таблиці прогнозних значень (блок А45: F46) (рис.3).

19. Для розрахунку дисперсії використаємо формулу :

Матриця знайдена у блоці В37:D39, а значення координат вектора – у рядку С17:Е17. Використовуючи вбудовану функцію СУММПРОИЗВ, знайдемо добуток вектора-рядка та матриці (СУММПРОИЗВ вектор-рядок, рядок матриці). Результат добутку запишемо у блоці С41:Е41.

Результат матричного добутку знайдемо у комірці С43, ввівши формулу С43:= СУММПРОИЗВ(C41:E41;C17:E17).

20. Обчислимо значення у комірці В46, ввівши формулу В46: =E43*КОРЕНЬ(K18*(1+C43)/(G20-4)), попередньо розрахувавши у комірці К18 суму квадратів різниці Y₁ й Y_1розр (мал.3), а у комірці Е43 значення коефіцієнта t_ak, використовуючи вбудовану функцію СТЬЮДРАСПОБР (t(_0,05;₁₀₎=2,23).

21. Знайдемо межі довірчого інтервалу у комірках С46 й D46 за формулами С46: =LN(G17-B46) і D46: =LN(G17+B46) (рис.3).

22. Підвести підсумки лабораторної роботи і зробити висновки.

23. Зберегти книгу у своїй робочій папці під ім'ям Лаб.7.

Висновки:

1. Оскільки Fроз>Fтаб, то з надійністю Р=0,95 математичну модель можна вважати адекватною експериментальним даним і на підставі прийнятої моделі можна проводити економічний аналіз.
2. З надійністю р=0,95 можна вважати, що вплив факторів Х₁ і Х₂ на показник Y значний.
3. Середнє значення прогнозу дорівнює 2,54 з надійністю р=0,95 буде знаходитися у проміжку (2,46; 2,61).
4. Для прогнозу зміна фактора Х₁ на 1% при незмінних інших факторах викличе зміну показника у зворотному напрямку з на 0,064%, а зміна фактора Х₂ на 1% при незмінних інших факторах викличе зміну показника на 0,265% у тому ж напрямку.