LINEBURG


<< Пред. стр.

страница 56
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

текания процессов и значения случайной величины временного ряда. Если ва­
риация средних значений незначительна, для прогноза на короткие интервалы
времени применяется метод скользящего среднего. Если поздние значения вре­
менного ряда имеют большую значимость для прогноза, а начальные значения —
меньшую, применяется метод экспоненциального сглаживания.

Метод скользящего среднего
Метод скользящего среднего предполагает, что все наблюдения временного ряда
имеют одинаковую значимость («вес») для прогноза. Каждое значение времен­
ного ряда, кроме начальных, участвует в формировании нескольких прогнозных
значений:
_У, +У„1+к+Ум-<
v
465
Среда моделирования Microsoft Excel


где Y, — сглаженное значение, у, — исходное значение, t — интервал сглаженно­
сти.
Результат прогноза выводится в виде таблицы и графика для сопоставления
фактических и прогнозных значений. Для каждой точки ряда Прогноз вычисля­
ется стандартная погрешность.
Пример 11
Получить прогнозное значение сальдо счета 051 методом скользящего среднего.
Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
2. Выбрать лист Статистика.
3. С помощью команды меню Сервис • Анализ данных вызвать диалоговое окно
Анализ данных. Выбрать инструмент анализа — Скользящее среднее. Указать
параметры для вычисления скользящего среднего (рис. 6.20):
О входной интервал — блок ячеек, содержащий сальдо счета 051, включая
название столбца; указать Метки в первой строке; интервал — 3 (расчет
среднего из 3 смежных значений);
О выходной интервал — любая ячейка на рабочем листе с данными;
О указать Вывод графика; Стандартные погрешности;
О нажать кнопку ОК.
4. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

ВНИМАНИЕ
Выходной и входной интервалы ячеек должны находиться на одном листе.


ШШШ35ШШ
га" Г У 1
4




31
|ЯЯ




Р Сгтмрпыеодоеинвств


Рис. 6.20. Параметры скользящего среднего

Результат скользящего среднего — таблица прогнозных значений и график
(рис. 6.21).
По результатам анализа можно сделать вывод, что общая тенденция — умень­
шение сальдо счета 051. На графике можно определить наиболее вероятное зна­
чение сальдо счета 051 для будущего учетного периода — 12399,67.
466 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel




Рис. 6 . 2 1 . График скользящего среднего сальдо счета 051


М е т о д экспоненциального сглаживания

Метод экспоненциального сглаживания предполагает, что последние значения
временного ряда более значимы для прогноза. Каждое значение участвует в
формировании прогнозных значений с переменным «весом», который убывает
по мере устаревания данных:
Y, = а У , . 1 + ( 1 - а ) г / „
где Y, — прогнозное значение, Y, 1 — прогнозное значение предыдущего периода;
у, — фактическое значение; а — фактор затухания (чем меньше а, тем более зна­
чимы фактические данные для прогноза).
Пример 12
Получить прогнозное значение сальдо счета 051 методом экспоненциального
сглаживания.
Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
2. Выбрать лист Статистика.
3. С помощью команды меню Сервис • Анализ данных вызвать диалоговое окно
Анализ данных. Выбрать инструмент анализа — Экспоненциальное сглажива­
ние. Указать параметры для вычисления экспоненциального сглаживания
(рис. 6.22):
О входной интервал — блок ячеек, содержащий сальдо счета 051, включая
название столбца; фактор затухания — 0,3; указать Метки в первой строке;
О выходной интервал — любая ячейка на рабочем листе с данными;
О установить Вывод графика; Стандартные погрешности;
О нажать кнопку ОК.
4. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
Результат экспоненциального сглаживания — таблица прогнозных значений и
график (рис. 6.23).
467
Среда моделирования Microsoft Excel


Экспоненциальное сглаживание




Рис. 6.22. Параметры экспоненциального сглаживания


Экспоненциальное сглаживание
16000
14000
12000

т 8000 4
6000
I
4000 4
• • 1 ..|М . .|..^.. . .. . 1 .1)1. .^1.).1.(.^ .. 1.. Ц1.. . . . .. . .у.
2000 Г Т Т Г ГТ 1 у ТТ Г Г Т 1 Г ГП Г Т Т Г Г

-* Г— О ГО (D О

0
Е=З Фактический Прогноз
Рис. 6.23. График экспоненциального сглаживания сальдо счета 051

Общая тенденция — уменьшение сальдо счета 051. Можно по графику опреде­
лить наиболее вероятное значение сальдо счета на будущий учетный период —
13183,92.

ВНИМАНИЕ
Выходной и входной интервалы должны быть расположены на одном листе

Метод регрессии
Метод регрессии предполагает анализ взаимосвязи случайных величин (при­
знаков), среди которых выделяется один результативный признак, зависящий
от прочих независимых между собой факторов. Оценка связи выполняется с по­
мощью коэффициента детерминации (индекса корреляции).
По числу факторов различают простую (парную) и множественную (несколько
факторов) регрессию. Вид и параметры уравнения регрессии устанавливаются с
помощью метода наименьших квадратов отклонений эмпирических данных от
ожидаемых значений. По типу уравнения регрессии различают: линейную и не­
линейную регрессию.
Статистическая оценка степени зависимости результата от различных факторов
основана на показателях вариации-
468 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


• общая дисперсия результативного признака, обусловленная влиянием всех
факторов в совокупности — а2;
• факторная дисперсия результативного признака, отражающая вариацию ре­
зультативного признака от воздействия единичного фактора — а2х;
• остаточная дисперсия результативного признака от воздействия всех факто­
ров, кроме выделенного, — а2:
2 Ж -ю2 -уJ2
ZQ/, - Ю 2 Z<J/,
2 2 2
п п п
Основное соотношение:

al=a2yt+G2, R2 у
_2 '


Коэффициент детерминации — R2 вычисляется как отношение факторной дис­
персии к общей дисперсии, индекс корреляции — R является корнем квадрат­
ным из коэффициента детерминации. Для оценки значимости индекса R рас­
считывается показатель:
R2
„ п-т
F
r= г >
2
1-R т
где п — размер выборки, т — число факторов.
Используется F-критерий Фишера для определения критического значения —
•FKp при ki = т, k2 = п-т. Вычисленное критическое значение сравнивается с
фактическим значением Fr. Если Fr > FKp, величина R признается существенной.
Величина FKp вычисляется с помощью встроенной функции FPACriPOBP.
На практике используется порог, равный 0,7. Связь считается сильной и урав­
нение регрессии пригодно для прогнозирования, если R больше 0,7.
Стандартное уравнение парной регрессии линейного вида: ух = а0 + Ьх.
Для каждого коэффициента уравнения регрессии вычисляются оценки t-крите-
рия Стьюдента:
• стандартная ошибка коэффициента регрессии;
• t-статистика (отношение коэффициента к стандартной ошибке).
Если t-статистика значима, коэффициенты принимаются для построения урав­
нения регрессии, в противном случае из уравнения регрессии исключается пе­
ременная. Критическое значение t-статистики вычисляется с помощью встроен­
ной функции СТЬЮДРАСПОБР.

Пример 13
Построить уравнение регрессии для расчета затрат на ремонт оборудования в
зависимости от срока эксплуатации и выпуска (съема) продукции. Исходные
данные для анализа представлены в табл. 6.7.
469
Среда моделирования Microsoft Excel


Таблица 6.7. Затраты на ремонт оборудования

Затраты на ремонт,
Срок эксплуатации Выпуск продукции,
т. руб/год
оборудования, лег т. руб/год

0,12
1,3 1200

2100
2,1 1,1
2,3
5000
4,1
0
1 4500

0
0 5000

1,75 6000 1,1

1,8
2,3 3200

7,8
1000
12,1

10 6700 7,1

0,67
1 1200

6,75
8,6 4300

1,75
3,4 2670


Последовательность действий.
1. Открыть рабочую книгу Анализов с помощью команды меню Файл • Открыть.
2. Установить курсор на лист Статистика.
3. С помощью команды меню Сервис • Анализ данных вызвать диалоговое окно
Анализ данных. Выбрать инструмент анализа — Регрессия. Указать параметры
для регрессии (рис. 6.24):
О входной интервал Y — блок ячеек, содержащий затраты на ремонт, вклю­
чая название столбца;
О входной интервал X — блоки ячеек, содержащие срок эксплуатации обо­
рудования и выпуск продукции, включая название столбцов; указать Мет­
ки;
О не выбирать Константа 0 (линия регрессии не проходит через начало ко­
ординат); уровень надежности — 67 % (уровень 95 % вычисляется автома­
тически);
О выходной интервал — ячейка листа;
О установить Остатки, Стандартизованные остатки, График остатков, График
подбора, График нормальной вероятности;
О нажать кнопку ОК.
470 Глава б. Специальные информационные технологии анализа данных в Microsoft Excel




Рис. 6.24. Параметры регрессии

Результат анализа данных представлен в трех таблицах (табл. 6.8-6.10) и гра­
фиках.

Таблица 6.8. Коэффициент детерминации

0,9896
Множественный R

0,97943
R-квадрат

Нормированный R-квадрат 0,97486
0,46310
Стандартная ошибка

12
Наблюдения


Эти значения свидетельствуют о наличии сильной связи показателя затрат на
ремонт от срока службы оборудования и объема выпуска продукции.
Таблица 6.9. Дисперсионный анализ

Значимость F
Df F
MS
SS

2.57Е-08
2 91,88817 45,94408 214,2334
Регрессия

Остаток 9 1,93012 0,21445

Итого 11 93,81829


Таблица 6.9 содержит условные обозначения рассчитываемых показателей для
регрессии, остатка и итога:
• Df — число степеней свободы (независимые значения);
• SS — сумма квадратов отклонений;
471
Среда моделирования Microsoft Excel


• MS — дисперсия, рассчитывается как отношение SS/Df;
• F — отношение дисперсии регрессии к дисперсии остатка;
• Значимость F — уровень значимости, рассчитывается как
МБРегрессия/МБОстаток.
Уравнение регрессии значимо для прогнозирования, если выражение ( 1 - Зна­
чимость F) близко к 1.

Таблица 6.10. Параметры уравнения регрессии 1

Y-пересечение Срок эксплуатации Выпуск, т. руб/год
оборудования

Коэффициенты -0,62695 0,71885 8.77Е-05

Стандартная ошибка 0,31343 0,03497 7.15Е-05

t-статистика -2,00024 20,55806 1,227356

0,07652 7Д2Е-09
Р-значение 0,250823

-1,33599 0,639752
Нижние 95 % -7.4Е-05

Верхние 95 % 0,08210 0,79795 0,000249

Нижние 67,0 % -0,94971 0,68284 1.41Е-05

Верхние 67,0 % -0,30418 0,754861 0,000161


Коэффициент переменной может использоваться в уравнении регрессии, если
вычисленная для него величина (1 - Р-значение) близка к 1. Параметр «Выпуск
продукции» и «Y-пересечение» (свободный член уравнения регрессии) не яв­
ляются значимыми. Поэтому модельное уравнение регрессии:
Y = -0,62695 + 0,71885 • ХСр1Ж + 8,77? - 05 • ХВштк
можно представить в виде:
У = 0,71885-XСрок '
Коэффициенты для уравнения регрессии принадлежат интервалу: верхние, ниж­
ние значения которого соответствуют указанному уровню надежности (рис. 6.24).
Для прогнозирования могут применяться следующие три уравнения:
У = 0,68284 • XСрок '

У = 0,71885 • XСрок '

Y = 0,79795 • XСрок '


• Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.


1
Таблица представлена в транспонированном виде.
472 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel



Графические методы решения задач
Деловая графика создается с помощью программы MS Graph, которая может
вызываться из различных приложений Microsoft Office Наиболее часто деловая
графика используется для анализа информации в Microsoft Excel С помощью
деловой графики с учетом ограничений (табл 6 11) можно
• выполнять статистическую обработку данных (построение рядов, гистограмм
распределения, графиков сглаживания и т п ),
• прогнозировать значение экономических показателей (построение трендовых
моделей),
• визуально подбирать параметры моделей

Таблица 6.11. Основные технические ограничения Microsoft Graph
Параметр Ограничение

Объем имеющейся памяти
Максимальное количество диаграмм

Максимальное число листов диаграмм 255

Максимальное число рядов данных в диаграмме 255

Максимальное число точек данных в одном ряду данных 32 000
для плоских диаграмм

Максимальное число точек данных в одном ряду данных 4 000
для объемных диаграмм

<< Пред. стр.

страница 56
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign