Получение сводных статистических отчетов по трендовым моделям

Доказано - для экономистов работа с числами очень важный навык. Игоровой тренажер "Продолжи ряд" создан специально для работы с числами в уме. В начале обучения только 2 из 10 проходят тест без ошибок.

Пройти тест

Отчеты строятся по следующим исходным данным

время

объем экспорта, млрд. долл. США

t

t2

t3

Lnt

Ln(yt) (натуральные логарифмы значений объемов экспорта)

1983 год

176,424

1

1

1

0,000

5,173

1984 год

183,933

2

4

8

0,693

5,215

1985 год

192,86

3

9

27

1,099

5,262

1986 год

243,326

4

16

64

1,386

5,494

1987 год

294,369

5

25

125

1,609

5,685

1988 год

323,323

6

36

216

1,792

5,779

1989 год

341,231

7

49

343

1,946

5,833

1990 год

382,472

8

64

512

2,079

5,947

1991 год

402,843

9

81

729

2,197

5,999

1992 год

410,104

10

100

1000

2,303

6,016

1993 год

422,271

11

121

1331

2,398

6,046

1994 год

429,722

12

144

1728

2,485

6,063

1995 год

512,427

13

169

2197

2,565

6,239

 

 

 


Для получения отчета используется раздел «Регрессия» в «Анализе данных» Excel.

В разделе «Регрессия» необходимо задать исходные данные для получения отчета по конкретной модели тренда

В частности, для линейного тренда «Входной интервал Y» – это значения уровней ряда (данные по экспорту), а «Входной интервал X» – значения временного фактора t. Также необходимо установить включение значений остатков в отчет (ставится метка).   


В результате получается следующий отчет

 


Данный отчет необходимо представить в следующем виде (см. приложение).

В отчете представлены результаты расчета следующих показателей и решения следующих задач

Обозначение показателя в Excel

Название показателя

Формула для расчета

Формула для расчета в Excel

1

Множественный R

Эмпирическое корреляционное отношение

=((D12/(D12+D13))^0,5)

2

R-квадрат

Квадрат эмпирического корреляционного отношения

=B4^2

3

Нормированный R квадрат

Нормированный квадрат эмпирического корреляционного отношения

=1-(1-B5)*(B14/B13)

4

Стандартная ошибка

Остаточное среднеквадратическое отклонение

=((СУММКВ(C25:C37))/11)^0,5

5

SS (регрессия)

Сумма квадратов отклонений уровней тренда от среднего уровня тренда

=КВАДРОТКЛ(B25:B37)

6

SS (остаток)

Сумма квадратов отклонений уровней ряда от уровней тренда

=СУММКВ(C25:C37)

7

Итого

Сумма сумм квадратов по регрессии и остаткам

 

=C13+C12

8

MS (регрессия)

Факторная дисперсия

=C12/B12

9

MS (остаток)

Остаточная дисперсия

=C13/B13

10

F

Расчетное значение критерия Фишера-Снедекора

=D12/D13

11

Стандартная ошибка (y-пересечение)

Среднеквадратическая ошибка коэффициента в уравнении тренда

=C18*(('расчетный лист'!G15/B8)^0,5)

(ячейка G15 в «расчетном листе» содержит значение суммы t2. 819, если t меняется от 1 до 13)

12

Стандартная ошибка (переменная x1)

Среднеквадратическая ошибка коэффициента в уравнении тренда

=B7/('расчетный лист'!K2*(B8)^0,5)

(ячейка K2 в «расчетном листе» содержит значение среднеквадратического отклонения t, функция для расчета =СТАНДОТКЛОНП()). 3,74, если t меняется от 1 до 13)

13

         t-статистика     (y-пересечение)

Расчетное значение критерия Стьюдента

=B17/C17

14

t-статистика     (переменная x1)

Расчетное значение критерия Стьюдента

=B18/C18

Остальные показатели не пересчитываются.

Значимость F и P-значение – это расчетные вероятности для F и t-критериев соответственно.

Нижние и Верхние 95% - 95%-е доверительные интервалы для неизвестных генеральных параметров модели тренда (в отчетах значения продублированы, поэтому повторение можно удалить).

Предсказанное Y – значения уровней тренда или прогнозов.

Остатки – отклонения уровней тренда от уровней ряда

Обращаю ваше внимание на то, что в таблице представлены названия и формулы для расчета показателей для нелинейных моделей трендов. В случае с линейной моделью «Множественный R» – это коэффициент корреляции, «R-квадрат» - коэффициент детерминации, а «Нормированный R квадрат» - нормированный коэффициент детерминации. Также обращаю ваше внимание, что в таблице даны формулы для расчета показателей при условии сохранения структуры листа с отчетом и для 13 уровней ряда!

Прежде чем делать пересчет показателей для степенной и экспоненциальной модели тренда, необходимо сделать обратные преобразования уровней тренда yt = ey'


Для этого рассчитываются экспоненты значений уровней тренда. Для удобства расчеты проводятся на листе с отчетом в столбце «D». Используется функция =EXP().

Затем рассчитываются остатки как разность уровней ряда и преобразованных значений уровней тренда.


В данном примере уровни ряда взяты с «расчетного листа».


Далее делается замена «старых» значений в отчете на вновь рассчитанные через специальную вставку «значения». Если использовать стандартное копирование возникнет циклическая ссылка

 

Статистические показатели рассчитываются по новым данным автоматически.


Последнее преобразование – пересчет коэффициента «Y-Пересечение». Так как рассматриваются преобразования lny = lna + blnt и lny = lna + bt, то необходимо рассчитать экспоненту по коэффициенту в уравнении. Расчет делается в той же самой ячейке следующим образом: к старому значению добавляется формула 

 Все преобразования делаются для степенной и экспоненциальной модели тренда одинаковы.