Прогноз в excel
Прогноз может быть осуществлен как в графическом, так и в аналитическом виде. В основу прогноза заложен метод наименьших квадратов с использованием аппроксимирующей линии. Для этого:
В таблицу заносятся исходные статистические данные анализа показателей за определенный период, по которым строится точечный график
Необходимо получить аналитическую зависимость F(x), гдеF – объем продаж, аx – период. Но, в данном случае, значение года (числа 1984, 1985 и т.д.) не может быть значением независимой переменной. В качестве независимой переменной может быть только номер периода (1, 2, 3 и т.д.). Поэтому необходимо войти в режим"Свойства диаграммы" - "Исходные данные" – вкладка "Ряд"и удалить значениях (если они выделялись при построении диаграммы).
Получение зависимости проводится по аппроксимирующей линии (линии тренда). Для добавления линии тренда в диаграмму необходимо установить указатель на любую точку диаграммы.
Вызвать контекстное меню и выбрать режим "Добавить линию тренда".
На вкладке "Тип"выбрать одну из пяти предложенных линий тренда (линейная).
На вкладке "Параметры"включить два переключателя
Получаем график с линией тренда и указанием уравнения и величины достоверности. Однако, задача заключается в том, чтобы из пяти предлагаемых зависимостей выбрать лучшую (по величине достоверности наиболее близкой к 1). Для этого нужно получить все пять зависимостей и сравнить их. Чтобы не накладывать все зависимости на один график, необходимо скопировать уравнение и величину достоверности первой зависимости в свободную ячейку листа, и установить курсор на линии тренда.
Вызвать контекстное меню и выбрать режим "Формат линии тренда".
На вкладке "Тип"выбрать другую зависимость (логарифмическая).
Выполнить пункты 6 – 8 для остальных зависимосьей.
Уравнение лучшей зависимости выделить цветом и оставить соответствующую линию тренда на графике.
10. Для получения графического отображения прогноза на 2 периода вперед, необходимо установить указатель на линии тренда, вызвать контекстное меню, выбрать режим "Формат линии тренда", на вкладке"Параметры"указать параметры прогноза:
Для получения аналитических значений прогноза необходимо в исходную таблицу добавить две строки: "Период" и "Расчет". В строку"Период"занести номера периодов, включая прогнозные. В строку"Расчет"занести расчетную формулу (по лучшей зависимости).
В свободных ячейках листа записать свои выводы относительно прогнозных значений.
ПРИМЕЧАНИЕ! Для прогноза используются зависимости, величина достоверности которых не менее 0,75. Если по реальным данным получается меньшая достоверность, необходимо пересмотреть исходные статистические данные: проверить их соответствие или добавить новые значения!!!
Выполнить прогноз с использованием специальных функций. Результаты добавить в таблицу.
Для прогнозирования значений могут использоваться функции:
|
Функция |
Описание |
|
ПРЕДСКАЗ |
Прогнозирование значений |
|
ТЕНДЕНЦИЯ |
Прогнозирование линейной зависимости. |
|
РОСТ |
Прогнозирование экспоненциальной зависимости. |
|
ЛИНЕЙН |
Построение линейного приближения. |
|
ЛГРФПРИБЛ |
Построение экспоненциального приближения. |