Технологии прогнозирования деятельности предприятия Прогнозирование с помощьюMicrosoftExcel

Для прогнозирования с Microsoft Excel необходимо ввести следующие основные понятия.

Линия тренда – графическое представление трендов в рядах данных. Линии тренда могут быть добавлены к ряду данных плоской диаграммы, линейчатой, гистограмме, графику, точечному графику. Линию тренда можно отформатировать.

Метка линии тренда – текст для линии тренда, который формируется электронными таблицами и может содержать уравнение регрессии и (или) среднее квадратическое отклонение. Метку линии тренда можно форматировать и перемещать, но нельзя изменить по размеру.

Регрессионный анализ (экстраполяция) – форма статистического анализа, используемая при прогнозировании. Оценивается отношение между переменными, в результате чего одна переменная может быть предсказана через другие.

Среднее квадратическое отклонение – вычисляемое значение, которое в регрессионном анализе характеризует достоверность линии тренда для прогнозирования. Среднее квадратическое отклонение помогает определить наиболее подходящую линию тренда. Близость его к нулю означает низкую степень соответствия, близость к единице – высокую, вполне достоверную линию тренда.

MS EXCEL содержит ряд родственных статистических функций для экстраполяционного анализа. К ним относят функции «ТЕНДЕНЦИЯ», «РОСТ», «ЛИНЕЙН», «ЛГРФПРИБЛ». Подробнее рассмотрим первую из них.

ТЕНДЕНЦИЯ –возвращает значение в соответствии с линейным трендом, аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные значения У» и «известные значения X», возвращает значения У в соответствии с этой прямой для заданного массива «новые значения X». Синтаксис данной функции:

ТЕНДЕНЦИЯ(известные значения У, известные значения X, новые значения X, конст) где:

• известные значения у – это множество значений у, которые уже известны для соотношения у = мх + + в;

• известные значения х – это необязательное множество значений х, которые уже известны для соотношения у = м х + в;

• новые значения х – это новые значения х, для которых тренд возвращает соответствующие значения у;

• конст – это логическое значение, которое указывает, требуется ли, чтобы константа «в» была равна нулю.

РОСТ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.

ЛИНЕЙН – вычисляет прямую, но возвращает параметры прямой, а не массив значений У.

ЛГРФПРИБЛ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.

Получить информацию о том, как MS EXCEL аппроксимирует данные прямой можно с помощью справки: [F1]  «Использование Microsoft Excel»  «Решение задач путём анализа данных»  «Статистический анализ данных»  «Регрессия»  «ЛИНЕЙН».

Команда «ЛИНИЯ ТРЕНДА» (меню «ВСТАВКА») содержит две вкладки:

вкладку «ТИП» и вкладку «ПАРАМЕТРЫ». Вкладка «ТИП» имеет функции:

• Добавление линии тренда или изменение типа линии тренда, связанного с рядом данных в диаграмме из типовой группы рядов, содержащей линейчатые и графические диаграммы, гистограммы, диаграммы с областями и точки диаграммы. Не может добавить линию тренда к объёмной диаграмме, круговой, кольцевой, диаграмме типа радар.

• Тип тренда / регрессии определяет тип линии тренда: линейный, полиномиальный (степень выражается как целое число от 2 до 6), логарифмический, экспоненциальный, степенной, скользящее среднее (указывается количество периодов, использованное для усреднения).

Вкладка «ПАРАМЕТРЫ» имеет функции модификации линии тренда:

• Имя тренда (располагается в легенде):

«автоматическое». Microsoft Excel именует линию тренда, основываясь на выбранном типе и на ряде данных, с которым она ассоциирована. Например, если «линейный» тренд добавляется ко второму ряду данных в группе типа диаграмм, он выводит имя «Линейный (Ряд 2)».

«пользовательское». Позволяет ввести имя длиной до 256 символов (в версии 5.0).

• Прогноз. Доступен только для регрессий (для скользящего среднего не доступен).

«Вперёд на» –указывается количество периодов, на которое проектируется будущее в линии тренда, или от оси у.

«Назад на» –указывается количество периодов, на которое проектируется прошлое в линии тренда, или от оси у.

•. Точка, в которой линия тренда пересекает ось у. Доступен только для некоторых типов регрессии.

• Показывать уравнение на диаграмме. Выводит уравнение регрессии для линии тренда в метке линии тренда на диаграмме.

• Показывать значение R-квадрат на диаграмме. Выводит значение среднего квадрэтического отклонения для линии тренда в метке линии тренда на диаграмме.

Добавление линии тренда для ряда данных. Каждый из рядов данных типовых групп диаграмм, линейчатых, гистограмм, ХУ-точечных и графиков можно дополнить одной или несколькими линиями тренда. Если добавляется скользящее среднее для ХУ-точечной диаграммы, то оно будет основываться на порядке, в котором значения по оси X нанесены на диаграмму. Чтобы получить нужный результат, может потребоваться отсортировать значения по X перед тем, как проводить построение скользящего среднего. В общем виде последовательность добавления линии тренда для ряда данных выглядит следующим образом:

1. Активизируйте диаграмму.

2. Выделите ряд данных, для которого нужно добавить линию тренда или скользящее среднее.

3. В меню вставка выберите команду «Линия тренда».

4. На панели «Tип» выберите нужный тип регрессионного анализа (экстраполяции) линии тренда или скользящее среднее.

5. Если выбран «Полиномиальный» тип, то в поле «Порядок» следует указать максимальную степень независимой переменной (степень аппроксимирующего многочлена) от 2 до 6.

6. Если выбран «Скользящий средний» тип, то в поле «Период» указывается число периодов, которые нужно использовать при вычислении скользящего среднего.

7. Для добавления ещё одной линии нужно снова выделить ряд данных перед тем, как выбрать команду «Линия тренда».

8. Можно пользоваться дополнительными сервисными функциями (присваивать название линии тренда, выводить метки линии тренда).

9. При окончании редактирования активизируйте [ОК].

Изменение установок линии тренда приемлемо для регрессионного анализа (экстраполяции), а не для скользящего среднего. Можно присвоить имя линии тренда или изменить тип её экстраполяции. Можно также вывести метки линии тренда, например, среднеквадратическое отклонение или уравнение экстраполяции, можно изменить У-пересечение.

1. Активизируйте диаграмму.

2. Сделайте двойной щелчок мышью по линии тренда, которую хотите модифицировать.

3. Выберите вкладку «Параметры».

4. В соответствующее поле введите пользовательское имя тренда.

5. Чтобы прогнозировать вперёд, назад или в обоих направлениях нужно щёлкнуть мышью по стрелкам регуляторов в группе «Прогноз» для того, чтобы задать количество периодов, на которое экстраполируется линия тренда. При работе с ХУ-точечной диаграммой указывается количество прогнозов, а не число периодов.

6. Для установления У-диапазона, выведения уравнения экстраполяции или оценки среднеквадратического отклонения ставятся или очищаются соответствующие флажки.

7. Для изменения типа линии тренда нужно выбрать вкладку «Тип», а затем выбрать на ней нужный тип.

8. «ОК».

Экспоненциальное сглаживание –предсказывает значение на основе прогноза для предыдущего периода, скорректированного с учетом ошибки в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияние на прогнозы ошибок в предыдущем прогнозе.

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

Входной интервал. Введите ссылку на интервал данных рабочего листа, подлежащих анализу. Входной интервал должен состоять из одного столбца или одной строки, содержащих четыре или более ячеек данных. Если данные во входном интервале не числовые, Microsoft Excel выведет сообщение.

Выходной интервал. Введите ссылку для верхней левой ячейки интервала, в который хотите вывести выходную таблицу. Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце. Если недостаточно прошлых значений для построения прогноза или для вычисления стандартной ошибки, Microsoft Excel возвратит ошибочное значение «#N/A».

Новый Рабочий Лист. Этот параметр вставляет новый лист в рабочую книгу, где располагается текущий слой, и вставляет результаты в ячейку А1 нового листа. Текстовое окно рядом с параметром «Новый Рабочий Лист» используется для произвольного наименования нового листа.

Новая Рабочая Книга. Этот параметр создает новую рабочую книгу, добавляет новый рабочий лист в рабочую книгу и вставляет результаты в ячейку А1 нового листа.

Фактор затухания. Введите фактор затухания, который будет использоваться в качестве константы экспоненциального сглаживания. Значение по умолчанию для фактора затухания равно 0.3.

Стандартные ошибки. Выделите флажок «Стандартные ошибки», если хотите включить значения стандартных ошибок в выходную таблицу. Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце. Если флажок «Стандартные ошибки» не выделен, Microsoft Excel сгенерирует одноколоночную таблицу без значений стандартных ошибок.

Вывод диаграммы. Выделите флажок «Вывод диаграммы», чтобы автоматически вместе с выходной таблицей была сгенерирована диаграмма для фактических и прогнозируемых значении. Диаграмма будет внедрена в тот же лист, что и выходная таблица. Если этот флажок не выделен, диаграмма не генерируется.

Метки. Если первая строка и первый столбец входного интервала содержат метки, выделите флажок «Метки». Если входной интервал не содержит меток, очистите флажок «Метки». Microsoft Excel сгенерирует подходящие метки данных для выходной таблицы.

Линию тренда можно отформатировать, задать её цвет, стиль, толщину. Если при этом доступна «Метка линии тренда», то можно и задавать числовой формат, цвет шрифта и прочие характеристики этой метки.

Форматирование линии тренда.

• Активизируйте диаграмму.

• Сделайте двойной щелчок по линии тренда.

• Выберите вкладку «Вид».

• В группе «Линия» выберите стиль, цвет и толщину линий. Для отмены пользовательского формата и восстановления формата по умолчанию, выберите переключатель «Автоматическая».

• Проверьте образец отформатированной линии тренда в диалоговом окне.

• «ОК»

Форматирование метки линии тренда.

• Активизируйте диаграмму.

• Сделайте двойной щелчок по линии тренда.

• Выберите вкладку «Число».

• Выберите нужный числовой формат.

• «ОК»

В Excel 97 и Excel 2000 линиями тренда можно дополнить ряды данных, представленные на ненормированных плоских диаграммах с областями, линейчатых диаграммах, гистограммах, графиках, биржевых, точечных и пузырьковых диаграммах. Нельзя дополнить линиями тренда ряды данных на объемных диаграммах, нормированных диаграммах, лепестковых диаграммах, круговых и кольцевых диаграммах.