Методы и формулы прогнозирования в Excel
Это руководство объясняет элементарные методы прогнозирования, которые могут быть легко применены в таблицах 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 предлагает для более сложных ситуаций с более сложными данными.
Дальнейшее развитие: пример экспоненциальной аппроксимации
Как вы можете себе представить, такая линейная модель ваших данных не всегда вероятна. Фактически, есть много причин полагать, что она должна следовать экспоненциальной модели. Многие явления в экономике определяются экспоненциальными уравнениями (например, вычисления сложных процентов являются классическим примером).
Вот как выполнить экспоненциальную аппроксимацию:
- Взгляните на ваши данные. Нарисуйте простой график и просто посмотрите на него. Если они следуют экспоненциальной эволюции, они должны выглядеть так:
Это идеальный случай. Конечно, данные никогда не будут точно выглядеть так. Но если точки кажутся приблизительно следующими этому распределению, это должно побудить вас задуматься об экспоненциальной аппроксимации.
Как и в предыдущем примере, вы всегда можете построить график ваших данных, запросить трендовую линию и выбрать «экспоненциальную» вместо линейной. Затем, как обычно, соберите отображаемое уравнение.
- К счастью, вы также можете сделать все это непосредственно, используя Analysis Toolpak: поместите все ваши данные в пустой лист Excel и перейдите к Инструменты => Анализ данных
Установка Analysis Toolpak (ATP)
ATP - это надстройка, поставляемая с Microsoft Excel, но она не всегда устанавливается по умолчанию. Чтобы установить ее, можно поступить следующим образом:
- Убедитесь, что у вас есть диск Office. Excel может потребовать вставить диск для установки файлов ATP.
- Откройте лист Excel, перейдите в меню “Инструменты”, а затем выберите “Надстройки”. Установите флажок в первом окне, помеченном «2. Analysis Toolpak».
- Вставьте диск Office, если программа попросит вас это сделать.
- Вот и все! Обратите внимание, что ваше меню “Инструменты” теперь включает гораздо больше функций, включая опцию “Анализ данных”. Именно ее мы будем использовать чаще всего.
Использование Analysis Toolpak (ATP)
… в линейной среде
Теперь вернемся к нашему линейному примеру. Если ваши данные “выглядят” хорошо (см. выше), вы можете использовать ATP для получения прямой оценки функциональной формы, не прибегая к процессу “трендовой линии”.
Откройте лист с данными, затем откройте меню “Инструменты” и выберите “Анализ данных”. Появится окно, в котором вас спросят, какой анализ вы хотите выполнить. Выберите “регрессию” для линейных настроек.
Теперь вам нужно указать Excel два аргумента: “диапазон Y” и “диапазон X”. Диапазон Y указывает, что вы хотите оценить (например, ваши продажи), а диапазон X содержит данные, которые, по вашему мнению, могут объяснить ваши продажи (здесь - стоимость единицы). В нашем примере (см. example1.xls) наши данные о продажах находятся в столбце B, от строки 3 до строки 90, поэтому вам нужно указать «$B$3:$B$90» в качестве диапазона Y и «$A$3:$A$90» в качестве диапазона X. Когда вы закончите, нажмите «ок».
Появляется новый лист, содержащий “результаты регрессии”.
Самый важный результат содержится в столбце “Коэффициенты” внизу листа. Перехват - это константа, а коэффициент “X переменной” - это коэффициент X (в данном случае, стоимость единицы). Таким образом, мы получаем ту же самую уравнение, которое мы получили с помощью функции “трендовой линии”. Продажи = Перехват + Коэффициент X * стоимость единицыПродажи = -126 + 100 * стоимость единицы
На этом листе также содержится полезное число, которое дает вам информацию о том, насколько хороша ваша оценка: “R Square”. Если оно близко к 1, то ваша оценка хороша, что означает, что найденное вами уравнение является довольно хорошим представлением ваших данных. Если оно близко к 0, то оценка не является хорошей, и вам следует, вероятно, попробовать другой вид подгонки (см. экспоненциальную подгонку ниже).
Этот метод, вероятно, быстрее, чем методы “трендовой линии”. Однако он немного более технический и намного менее наглядный. Поэтому, если вы не хотите заморачиваться с построением графиков и оценкой ваших данных, убедитесь, что вы хотя бы проверяете значение “R square”.
… с использованием экспоненциальной подгонки
Если линейная оценка не дает хороших результатов (например, если вы получаете низкое значение R-квадрат, т.е. 0,1), вы можете воспользоваться экспоненциальной подгонкой.
Запустите инструментарий анализа, как обычно: откройте свой лист данных, затем откройте меню “инструменты” и выберите “Анализ данных”. Появится окно, в котором будет предложено выбрать вид анализа, который вы хотите выполнить.
В нашем случае с экспоненциальной подгонкой, мы хотим выбрать “экспоненциальную”.
Обратите внимание, что Excel просит вас только о одном диапазоне ввода. Выберите столбец, который содержит данные, которые вы хотите прогнозировать (т.е. стоимость единицы), и выберите “коэффициент сглаживания”.
Как я могу узнать, какую модель выбрать?
Обратите внимание, что вам не нужно пробовать каждый метод оценки, чтобы найти тот, который лучше всего подходит для вас. Это можно сделать только с помощью автоматизации, так как доступно такое большое количество методов. Если вы хотите, чтобы все модели были протестированы на ваших данных, вы можете рассмотреть возможность отправки их в Lokad. У нас есть мощная компьютерная система, которая “тестирует” все модели и выбирает только те, которые лучше всего подходят для данных вашего бизнеса.