2015-04-08
1264
Составить прогноз товарооборота торгового предприятия по данным таблицы 3.6 с помощью функции ЛИНЕЙН.
Выполнение:
Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Для построения трендовой модели она использует уравнение вида у = mх + b, где у — исследуемый показатель; х = t — временной тренд; b, m — параметры уравнения, характеризующие соответственно у -пересечение и наклон линии тренда.
Вызвать функцию ЛИНЕЙН можно в диалоговом окне Мастера функций (категория «Статистические»), расположенном на панели инструментов Стандартная.
Используя метод наименьших квадратов, функция ЛИНЕЙН создает массив значений, который описывает искомую модель тренда. Учитывая, что создается массив значений, функция должна задаваться пользователем в виде формулы массива. Поэтому перед началом работы с ЛИНЕЙН необходимо на рабочем листе выделить диапазон ячеек, достаточный для размещения создаваемого ею массива значений. Так, для прогнозирования товарооборота по данным таблицы 3.6 обозначим ячейками E10:F14 диапазон для формирования выходного массива (таблица 3.12). После того, как выделен выходной диапазон и пользователь определился с аргументами функции посредством диалогового окна ЛИНЕЙН, следует нажать на клавиатуре кнопки Ctrl+Shift+Enter.
Таблица 3.12 - Расчет и оценка линейной модели тренда с помощью функции ЛИНЕЙН
| A | B | C | D | E | F | |
| Порядковый номер месяца | Объем товарооборота, ден. ед. | |||||
| Линейная оценка | 437,425 | 27920,1 | ||||
| Статистика | 34,958505 | 338,033 | ||||
| 0,917921 | 644,603 | |||||
| 156,56746 | ||||||
Функция ЛИНЕЙН имеет четыре аргумента (рисунок 3.11):
1) Известные значения у — это множество уже известных значений исследуемого показателя, на основе которых будет производиться оценка параметров уравнения тренда. Так, при составлении прогноза товарооборота торгового предприятия по данным таблицы 3.6 известные значения у представлены в виде столбца и находятся в ячейках В3:В18;
2) Известные значения х — при построении трендовой модели представляют собой временной ряд, соответствующий по размерам первому аргументу. В нашем примере он находится в ячейках А3:А18 таблицы 3.6 и отражает порядковые номера месяца;
3) Конст — логическое значение, которое указывает на необходимость расчета параметра b (свободного члена) при построении модели тренда. Если Конст имеет значение ИСТИНА, то параметр b вычисляется. Если Конст имеет значение ЛОЖЬ, то параметр b принимается равным нулю;
4) Статистика — логическое значение, которое указывает на необходимость отражения на рабочем листе дополнительной статистической информации, позволяющей судить о качестве построенной модели. Если этот аргумент имеет значение ЛОЖЬ или ссылка на него отсутствует, то функция ЛИНЕЙН не рассчитывает статистические характеристики. Если Статистика задана значением ИСТИНА, то массив, создаваемый функцией, содержит значения следующих статистических величин (таблица 3.13).
Таблица 3.13 - Значения статистических величин функции ЛИНЕЙН
| Стандартная ошибка для параметра m (COm) | Стандартная ошибка для свободного члена b (СОb) |
| Квадрат коэффициента корреляции (r2) | Стандартная ошибки для у (СОу) |
| F -критерий (F) | Степень свободы (df) |
| Сумма квадратов регрессии (SSp) | Остаточная сумма квадратов (SS0) |
Рисунок 3.11 - Окно диалога функции ЛИНЕЙН
Вывод: Число в ячейке Е10 представляет собой наклон линии тренда (m = 437,425), а число в ячейке F10 — это у -пересечение прямой линии (b = 27920,1). Можно составить линейную модель, описывающую динамику товарооборота торгового предприятия, которая принимает следующий вид:
Y = 27920,1 + 437,425x,
где х = t — порядковый номер месяца.
В нашем примере коэффициент корреляции (см. таблицу 3.12, ячейка Е12) r2 = 0,9179, что указывает на высокое качество линейной модели.
В нашем примере Fкрит находится по таблице F -распределения на пересечении столбца 1 (так как в модели только одна переменная х — временной тренд) и строки 14 (см. ячейку F13 таблицы 3.12). В приложении А находим для распределения Фишера с (1;14) степенями свободы, что при 5%-м уровне значимости (доверительная вероятность 95 %) табличное значение Fкp = 4,6. Поскольку F = 156,567 > 4,6 (см. ячейку Е13 таблицы 3.12), то полученная модель тренда полезна для использования в прогнозировании.
Рассчитаем значения t -статистики для оценки параметров m и b построенной нами модели на основе данных таблицы 3.12:
Табличное значение tкрит для уровня значимости 0,05 (доверительная вероятность 0,95) с df = 14 степенями свободы равно 2,145 (см. приложение Б). Поскольку | tm | > 2,145, | tf | > 2,145, статистическая значимость параметров построенной модели признается весьма высокой.
