Задание 9. Анализ нелинейных процессов с помощью функции лгрфприбл

Рассчитать параметры модели тренда и дать ей качественную оценку с помощью функции ЛГРФПРИБЛ для базовых данных о динамике товарооборота предприя­тия, приведенных в таблице 3.6.

Выполнение:

Функция ЛГРФПРИБЛ определяет параметры экспоненциальной кривой, наилучшим об­разом аппроксимирующей исходные данные. Эта функция относится к категории «Статистические» и может быть вызвана с помощью окна диалога Мастера фун­кций.

Функция ЛГРФПРИБЛ имеет те же четыре аргумента, что и функция ЛИНЕЙН и формирует аналогичный массив результатов (рисунок 3.14). Поэтому она вводится как формула для работы с массивами, т.е. требует: во-первых, выделения диапазона ячеек, в которых будет формироваться массив результатов; во-вторых, после работы с окном диалога ЛГРФПРИБЛ — нажатия клавиш Ctrl + Shift + Enter.

Однако следует учиты­вать, что дополнительная статистика, которую отражает функция ЛГРФПРИБЛ, основана на линейной модели вида

In у = х × ln т + ln b.

Поэтому при оценке значений стандартных ошибок пара­метров модели (СО_т и CO_b) их необходимо сравнивать не с са­мими значениями параметров, а с их натуральными лога­рифмами — ln m и ln b. Последние можно рассчитать с по­мощью функции LN (категория «Математические»), вызы­вая ее в окне диалога Мастера функций.

С этой целью на рабочем листе Excel выделим диапазон ячеек D11:E15 для формирования выходного массива (таблица 3.16). Работая с окном диалога ЛГРФПРИБЛ, формируем следующую формулу массива: =ЛГРФПРИБЛ(В3:В18;А3:А18;ИСТИНА; ИСТИНА). Затем нажимаем клавиши Ctrl + Shift + Enter.

Рисунок 3.14 - Окно диалога функции ЛГРФПРИБЛ

Таблица 3.16 - Расчет и оценка модели тренда с помощью функции ЛГРФПРИБЛ

Вывод: Приведенный в таблице 3.16 массив результатов работы фун­кции позволяет построить следующую модель тренда, в осно­ве которой лежит уравнение экспоненциальной кривой роста:

у = 28080,897 · 1,01^х,

где х = t — порядковый номер месяца.

Оценка статистических характеристик приведенной мо­дели показывает, что качество ее подгонки к фактическим значениям у выше, чем модели, построенной с помощью функции ЛИНЕЙН. Коэффициент r² в данном случае имеет значение 0,922 (ячейка D13), что несколько больше соответ­ствующего показателя в случае линейной модели, равного 0,9179. В этой связи (и учитывая однофакторный характер модели) можно ожидать улучшение оценок F- и t -статистики. Следо­вательно, качество построенной модели позволяет использо­вать ее при составлении прогнозов развития товарооборота на ближайшую перспективу.