Методы прогнозирования и формулы в Excel

This guide explains элементарные методы прогнозирования, которые можно легко применять в электронных таблицах Microsoft Excel. Руководство предназначено для менеджеров и руководителей, которым необходимо предвидеть спрос клиентов. Теория проиллюстрирована с помощью Microsoft Excel. Дополнительные примечания доступны для разработчиков программного обеспечения, которые хотели бы воспроизвести теорию в собственном приложении.

Преимущества прогнозирования

Прогнозирование может помочь вам принимать правильные решения и зарабатывать/экономить деньги. Вот один пример.

• Определите оптимальный размер запасов

Время — деньги. Пространство — деньги. Поэтому вы должны использовать все доступные средства, чтобы сократить запасы – без возникновения дефицита, конечно.

Как? С помощью прогнозирования!

Как упростить работу: метки, комментарии, имена файлов

Со временем, по мере накопления данных, вы будете все чаще путаться и допускать ошибки. Решение? Не создавайте беспорядок: грамотное использование меток, комментариев и правильное именование файлов могут избавить вас от множества проблем.

• Всегда подписывайте столбцы. Используйте первую строку каждого столбца для описания содержащихся в нем данных.
• Разные данные — разные столбцы. Не помещайте разные числа (например, ваши затраты и продажи) в один столбец. Это чрезвычайно увеличивает вероятность ошибок, и делает вычисления и обработку данных более сложными.
• Дайте каждому файлу понятное и однозначное имя. Это требует немного усилий, но ускоряет работу. Файлы становятся легче для визуальной идентификации и их проще найти с помощью функции поиска Windows.
• Используйте комментарии.

Даже если вы обычно не работаете с большим объемом данных, легко запутаться. Это особенно относится к возврату к данным, созданным давным-давно. Excel предлагает отличное решение: комментарии.

Полезность комментариев

Просто щелкните правой кнопкой мыши по ячейке, которую хотите прокомментировать, и выберите « вставить комментарий ».

Вы можете использовать их:

• чтобы объяснить содержание ячейки (например, стоимость единицы согласно оценкам мистера Доу)
• чтобы оставить предупреждения будущим пользователям таблицы (например, У меня есть сомнения относительно этого расчета…)

Начало работы: простой пример прогнозирования с использованием линий тренда

Давайте теперь сделаем наш первый прогноз. В этой части мы будем использовать файл: Example1.xls. Чтобы повторить шаги самостоятельно, вы можете скачать файл. Эти данные служат лишь в качестве примера.

Наши данные: В первом столбце — данные о стоимости единицы аналогичных продуктов (стоимость отражает качество продукта). Во втором — данные о количестве проданных единиц.

Что мы хотим узнать: Если мы продадим еще один продукт, качество которого соответствует цене в $150 за единицу, сколько единиц мы можем ожидать продать?

Как мы к этому придем: Здесь все довольно просто. Мы хотим найти простую математическую зависимость между стоимостью единицы и продажами, а затем использовать эту зависимость для нашего прогноза.

Во-первых, всегда полезно создать график в Excel, чтобы посмотреть на данные. Ваши глаза — отличные инструменты, способные определить тренды за считанные секунды.

Для этого мы выделяем данные, затем используем вкладку Вставка > Диаграмма, и выбираем тип графика «Точечная (XY)». Мы хотим оценить продажи как функцию качества, поэтому по горизонтали откладываем стоимость единицы, а по вертикали — продажи.

Теперь, остановимся на несколько секунд и внимательно посмотрим на получившееся: зависимость, кажется, возрастающая и линейная.

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

Построение линии тренда

Теперь нам нужно выбрать ту зависимость, которая, как кажется, подходит (то есть наилучшим образом описывает) наши данные. Опять же, мы доверяем глазам: в данном случае точки почти выстроены в прямую линию, поэтому мы используем настройку «линейная». Позже мы воспользуемся другими – более сложными, но зачастую более реалистичными – настройками, такими как «экспоненциальная».

Наша линия тренда теперь отображается на диаграмме. Еще один клик правой кнопкой позволяет увидеть точную форму зависимости: y = 102.4x - 191.64.

Поймите: количество проданных единиц = 102.4 умножить на стоимость единицы - 191.64.

Таким образом, если мы решим производить по цене $150 за единицу, можно ожидать продажи 102.4*150 - 191.64 = 15168 единиц

Линейная линия тренда

Мы только что успешно завершили наш первый прогноз.

Однако будьте осторожны: программа всегда способна найти зависимость между двумя столбцами, даже если эта зависимость на самом деле очень слаба! Поэтому необходима проверка на надежность. Вот как вы можете быстро это сделать:

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

Точки разбросаны повсюду: нет явной зависимости, прогнозы ненадежны

Точки складываются в картину и позволяют делать более надежные прогнозы

• После просмотра диаграммы вы можете использовать функцию CORREL. В нашем примере функция будет выглядеть так: CORREL(A2:A83,B2:B83). Если результат близок к 0, то корреляция низкая, и вывод таков: реального тренда просто нет. Если же результат близок к 1, то корреляция сильная. Последнее является полезным, так как повышает объяснительную силу найденной вами зависимости.

Существуют более тонкие методы проверки высокой корреляции; мы вернемся к этому позже.

Конечно, последние шаги можно автоматизировать: вам не нужно записывать зависимость и использовать карманный калькулятор для вычислений. Вам понадобится надстройка Analysis Toolpak!

Прогнозирование с использованием Analysis Toolpak

Перед продолжением следует проверить, установлена ли Excel ATP (Analysis Toolpak). Обратитесь к разделу “Установка Analysis Toolpak” для дополнительной информации.

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

Дальнейшее развитие: пример экспоненциального приближения

Как вы можете предположить, такая линейная модель данных не всегда применима. На самом деле, существует много причин полагать, что данные должны подчиняться экспоненциальной модели. Многие экономические процессы описываются экспоненциальными уравнениями (например, расчёты сложных процентов — классический пример).

Вот как выполнить экспоненциальное приближение:

1. Посмотрите на ваши данные. Постройте простой график и просто взгляните на него. Если данные подчиняются экспоненциальному закону, график должен выглядеть примерно так:

идеальная экспоненциальная форма

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

Использование линий тренда

Как и в предыдущем примере, вы всегда можете построить диаграмму своих данных, добавить линию тренда и выбрать «экспоненциальную» вместо линейной. Затем, как обычно, запишите полученное уравнение.

2. К счастью, вы можете сделать всё это напрямую, используя надстройку Analysis Toolpak: внесите все ваши данные в пустой лист Excel и перейдите в Инструменты => Анализ данных

Установка надстройки Analysis Toolpak (ATP)

Надстройка ATP входит в состав Microsoft Excel, но не всегда устанавливается по умолчанию. Чтобы установить её, выполните следующие действия:

1. Убедитесь, что у вас есть ваш Office CD. Excel может потребовать вставить диск для установки файлов ATP.
2. Откройте лист Excel, перейдите в меню «Инструменты», затем выберите «Надстройки». Установите флажок в первом поле окна, обозначенном «2.Analysis Toolpak».
3. Вставьте Office CD, если программа запросит.
4. Всё готово! Обратите внимание, что в вашем меню «Инструменты» теперь появилось много дополнительных функций, включая опцию «Анализ данных». Именно этой функции мы будем пользоваться чаще всего.

Использование надстройки Analysis Toolpak (ATP)

… в линейном случае

Теперь вернемся к нашему линейному примеру. Если ваши данные «выглядят» хорошо (см. иллюстрацию выше), вы можете использовать ATP для прямой оценки функциональной формы без использования процесса «линии тренда».

Откройте лист с данными, затем в меню «Инструменты» выберите «Анализ данных». Появится окно, предлагающее выбрать тип анализа, который вы хотите выполнить. Выберите «регрессию» для линейной модели.

Теперь вам нужно задать Excel два параметра: диапазон «Y» и диапазон «X». Диапазон Y указывает то, что вы хотите оценить (например, ваши продажи), а диапазон X содержит данные, которые, по вашему мнению, могут объяснить продажи (в данном случае, стоимость единицы). В нашем примере (см. example1.xls) данные о продажах находятся в столбце B, со строки 3 по строку 90, поэтому введите «$B$3:$B$90» как диапазон Y, и «$A$3:$A$90» как диапазон X. Когда закончите, нажмите «ок».

Появится новый лист, содержащий «результаты регрессии».

Вывод Analysis Toolpak при регрессии методом наименьших квадратов

Самый важный результат содержится в столбце «Коэффициенты» в нижней части листа. Перехват является константой, а коэффициент при «X-переменной» — коэффициентом X (в данном случае, стоимости единицы). Таким образом, мы получаем то же уравнение, что и при использовании функции «линии тренда»: Продажи = Перехват + (Коэффициент X) * стоимость единицы, то есть Продажи = -126 + 100 * стоимость единицы

Этот лист также содержит полезное число, которое дает представление о качестве вашей оценки: «R-квадрат». Если оно близко к 1, то оценка хорошая, то есть найденное уравнение достаточно точно отражает ваши данные. Если же оно близко к 0, оценка плохая, и вам, вероятно, следует попробовать другой метод приближения (см. экспоненциальное приближение ниже).

Этот метод, вероятно, быстрее, чем техника «линии тренда». Однако он несколько более технический и менее наглядный. Поэтому, если вы не хотите тратить время на построение графиков и визуальную оценку данных, убедитесь хотя бы в корректности значения «R-квадрат».

… с использованием экспоненциального приближения

Если линейная оценка не получается (например, вы получаете низкий R-квадрат, т.е. 0.1), вы можете воспользоваться экспоненциальным приближением.

Запустите Analysis Toolpak, как обычно: откройте лист с данными, затем в меню «Инструменты» выберите «Анализ данных». Появится окно с вопросом о том, какой тип анализа вы хотите провести.

В случае экспоненциального приближения нам следует выбрать опцию «экспоненциальная».

Обратите внимание, что Excel запрашивает только один диапазон ввода. Выберите столбец, содержащий данные, которые вы хотите прогнозировать (например, стоимость единицы), и задайте «коэффициент сглаживания».

Как определить, какую модель выбрать?

Обратите внимание, что вам не нужно пробовать каждый метод оценки, чтобы найти подходящий именно вам. Это можно сделать только с помощью автоматизации, поскольку доступно так много методов. Если вы хотите, чтобы все модели были протестированы на ваших данных, вы можете отправить их в Lokad. У нас есть мощная компьютерная система, которая «тестирует» все модели и выбирает только те, которые лучше всего соответствуют данным вашего бизнеса.