Как посчитать прогноз выполнения плана в процентах в excel

Обновлено: 08.01.2025

Каким в ближайшее время станет объем продаж? Упадет ли трафик или же внезапно вырастет? Чтобы ответить на эти вопросы, аналитиком быть не обязательно. Ниже вы узнаете, что нужно делать.

Способ прогнозирования, о котором пойдет речь, занимает максимум 30 минут. Этим отчетом удобно пользоваться в различных случаях.

Примеры использования

• Для бизнеса без штатных аналитиков. При этом специалисты не умеют строить правильные прогнозы.
• Для составления примерного плана по увеличению продаж. Можно вовремя увидеть спрос или же принять необходимые меры, если он падает.
• Для прогнозирования трафика.

Давайте посмотрим на простой пример — прогноз по продажам. Можно пользоваться Google Таблицами, однако и простой Excel тоже вполне подойдет.

Шаг №1 — собираем данные

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

Нужно создать Таблицу в Google и указать необходимые данные за прошедшие, к примеру, 20 месяцев. В нашем случае в первую очередь мы выставляем доход:

Шаг №2 — пишем формулу Forecast

В русской локализации эта формула называется “Предзаказом”. Её можно определить по предполагаемому значению Y, если исходить из имеющегося параметра X, где:

• X — точка, для которой требуется сделать прогноз;
• известное Y — сумма продаж за прошедший период;
• известное X — дата этих периодов.

К примеру, требуется выяснить, какими окажутся продажи в следующие 4 месяца. Для это в Таблицу нужно добавить такое же количество строчек и указать порядковые номера месяцев. Там, где прогнозируется доход, необходимо вставить формулу:

• Round — способно округлить полученное значение;
• Forecast — сам прогноз;
• A22 — ячейка, для которой делается прогноз.

• $B$2:$B$21 — известные данные от переменной Y;
• $A$2:$A$21 — диапазон дат за прошлые месяцы.

Шаг №3 — делаем график

Сначала нужно зайти во вкладку “Вставка”. После этого:

• перейдите в раздел “Диаграмма”;
• пройдите в “Настройки”;
• в качестве типа визуализации выберите “График”.

После этого в настройках зайдите в “Дополнительные”, а оттуда — в “Серии”. Поставьте галочку рядом с линиями тренда. Вы увидите линию, которая будет выступать продажами. На нижнем графике заметно, что продажи скорее всего обвалятся.

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

Итоги

Такой отчет можно составить, не обладая даже должными навыками по аналитике. Так вы можете понять, есть ли какие-то риски.

Прогнозирование – это очень важный элемент практически любой сферы деятельности, начиная от экономики и заканчивая инженерией. Существует большое количество программного обеспечения, специализирующегося именно на этом направлении. К сожалению, далеко не все пользователи знают, что обычный табличный процессор Excel имеет в своем арсенале инструменты для выполнения прогнозирования, которые по своей эффективности мало чем уступают профессиональным программам. Давайте выясним, что это за инструменты, и как сделать прогноз на практике.

Процедура прогнозирования

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.

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

• Линейная;
• Логарифмическая;
• Экспоненциальная;
• Степенная;
• Полиномиальная;
• Линейная фильтрация.

Давайте для начала выберем линейную аппроксимацию.

Способ 2: оператор ПРЕДСКАЗ

Экстраполяцию для табличных данных можно произвести через стандартную функцию Эксель ПРЕДСКАЗ. Этот аргумент относится к категории статистических инструментов и имеет следующий синтаксис:

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

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.

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

В поле «Известные значения y» указываем координаты столбца «Прибыль предприятия». Это можно сделать, установив курсор в поле, а затем, зажав левую кнопку мыши и выделив соответствующий столбец на листе.

Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.

Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ. Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ, а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа», но он не является обязательным и используется только при наличии постоянных факторов.

Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.

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

Производим обозначение ячейки для вывода результата и запускаем Мастер функций обычным способом. В категории «Статистические» находим и выделяем наименование «ТЕНДЕНЦИЯ». Жмем на кнопку «OK».

Способ 4: оператор РОСТ

Ещё одной функцией, с помощью которой можно производить прогнозирование в Экселе, является оператор РОСТ. Он тоже относится к статистической группе инструментов, но, в отличие от предыдущих, при расчете применяет не метод линейной зависимости, а экспоненциальной. Синтаксис этого инструмента выглядит таким образом:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ, так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.

Выделяем ячейку вывода результата и уже привычным путем вызываем Мастер функций. В списке статистических операторов ищем пункт «РОСТ», выделяем его и щелкаем по кнопке «OK».

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ. Его синтаксис имеет такой вид:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Последние два аргумента являются необязательными. С первыми же двумя мы знакомы по предыдущим способам. Но вы, наверное, заметили, что в этой функции отсутствует аргумент, указывающий на новые значения. Дело в том, что данный инструмент определяет только изменение величины выручки за единицу периода, который в нашем случае равен одному году, а вот общий итог нам предстоит подсчитать отдельно, прибавив к последнему фактическому значению прибыли результат вычисления оператора ЛИНЕЙН, умноженный на количество лет.

Производим выделение ячейки, в которой будет производиться вычисление и запускаем Мастер функций. Выделяем наименование «ЛИНЕЙН» в категории «Статистические» и жмем на кнопку «OK».

Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ. Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

В списке операторов Мастера функций выделяем наименование «ЛГРФПРИБЛ». Делаем щелчок по кнопке «OK».

Прогнозируемая сумма прибыли в 2019 году, которая была рассчитана методом экспоненциального приближения, составит 4639,2 тыс. рублей, что опять не сильно отличается от результатов, полученных при вычислении предыдущими способами.

Мы выяснили, какими способами можно произвести прогнозирование в программе Эксель. Графическим путем это можно сделать через применение линии тренда, а аналитическим – используя целый ряд встроенных статистических функций. В результате обработки идентичных данных этими операторами может получиться разный итог. Но это не удивительно, так как все они используют разные методы расчета. Если колебание небольшое, то все эти варианты, применимые к конкретному случаю, можно считать относительно достоверными.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

2 декабря Закон.ру проведет бесплатный вебинар о взыскании упущенной выгоды и особенностях ее расчета. Спикерами выступят специалисты "Экспертной группы VETA": юридический советник Ильяс Янбаев и управляющий партнер Илья Жарский.

В Торгово-промышленной палате Российской Федерации состоялся круглый стол «Социальное предпринимательство в России». Участники круглого стола обсудили состояние и перспективы социального предпринимательства в России. Круглый стол «Упущенная выгода: теория и практика взыскания» состоялся в Нижнем Новгороде. Мероприятие, которое проходило в «Феста кафе», собрало более 30 практикующих юристов и вызвало живой отклик среди участников. Усиление мер господдержки и сотрудничества с институтами развития поможет банковскому сектору активизировать кредитование и способствовать наметившемуся росту промышленного производства

Run Rate: прогноз выполнения плана продаж

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

Так что же такое Run Rate?
Run - (от англ.) идти, бежать.
Rate - (от англ.) скорость, тепм, процент.

Так вот, в продажах Run Rate - это показатель, позволяющий определить какой процент/значение выполнения плана продаж достигнет отдел продаж на конец периода, двигаясь существующими темпами продаж на момент измерения показателя.

1. Для прогнозирования планового показателя выполнения плана продаж (в процентах):

(продажи на момент измерения/количество прошедших рабочих дней) (количество рабочих дней за весь период/значение продаж за весь период)

Пример: отдел продаж на момент прогнозирования продал 50 единиц продукции за 10 рабочих дней. План продаж до конца месяца: продать 150 единиц продукции. Осталось 12 рабочих дней до конца месяца. На какой процент выполнения плана продаж выходит отдел продаж на конец месяца?

Получается, отдел продаж, продавая теми же темпами, выйдет на плановый показатель 73%.

2. Вторая формула прогнозирует значение продаж на конец периода (выручку, кол-во единиц продукции и т.д.):

(продажи на момент измерения/количество прошедших рабочих дней)

Возьмем вышеуказанный пример:
RR= (50/10)*22=110

Получается, что отдел продаж, работая теми же темпами, на конец месяца продаст 110 единиц продукции.
Таким же образом можно прогнозировать выручку в деньгах на конец периода.

Итак, плюсы и минусы данного метода:

1. 1. С помощью данного показателя легко прогнозировать % выполнения плана продаж на конец периода, но только в том случае, если ваш отдел продаж работает равномерно в течение месяца (периода), что во многих организациях это неверно, и основной объем продаж приходится на конец месяца. Почему? Напишу в другой статье.

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

3. С помощью данного метода можно «привести в чувства» своих менеджеров по продажам, придавая ускорение и показывая, что при таких результатах выполнения плана продаж премии им не получить.

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

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

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Любому бизнесу интересно заглянуть в будущее и правильно ответить на вопрос: «А сколько денег мы заработаем за следующий период?» Ответить на такого рода вопросы позволяют различные методики прогнозирования. В данной статье мы с вами рассмотрим несколько таких методик и произведем все необходимые расчеты в Excel. Еще больше про анализ данных в Excel мы рассказываем на нашем открытом курсе «Аналитика в Excel».

Постановка задачи

Исходные данные

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

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

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

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

Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

• Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
• Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

• Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

Решение задачи с помощью Excel

Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:

1. с использованием линейного тренда
2. с использованием полиномиального тренда

Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Модель с линейным трендом

Пусть у нас есть исходная информация по продажам за 2 года:

Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения

Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:

1. Выделяем две ячейки рядом
2. Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
3. Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива

Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.

Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).

Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.

То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.

Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.

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

Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.

Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.

И последний штрих — умножаем полученное значение на коэффициент сезонности. Вуаля, это и есть итоговый ответ в данной модели!

Модель с полиномиальным трендом

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

Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.

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

Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:

У полиномиального тренда же уравнение выглядит иначе:

где конечная степень определяется степенью полинома.

Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:

Согласитесь, выглядит немного страшно. Однако, ничего страшного нет, и мы с легкостью можем решить эту задачку с помощью уже известных нам методов.

1. Ставим в ячейку F4 курсор и вводим формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;1). Функция ЛИНЕЙН позволяет произвести расчет коэффициентов, а с помощью функции ИНДЕКС мы вытаскиваем нужный нам коэффициент. В данном случае за выбор коэффициента отвечает самый последний аргумент. У нас стоит 1 — это коэффициент при самой высокой степени (т.е. при 4 степени, коэффициент). Кстати, узнать о самых полезных математических формулах Excel можно в нашем бесплатном гайде «Математические функции Excel».
2. Аналогично прописываем формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;2) в ячейке ниже.
3. Делаем такие же действия, пока не найдем все коэффициенты.

Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.

1. Выделяем столбец с продажами
2. Выбираем «Вставка» → «График» → «Точечный» → «Точечная диаграмма»
3. Нажимаем на любую точку графика правой кнопкой мыши и выбираем «Добавить линию тренда»
4. В открывшемся справа меню выбираем «Полиномиальная модель», меняем степень на 4 и ставим галочку на «Показывать уравнение на диаграмме»

Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!

Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:

1. Вы можете вывести коэффициент на график, поставив галочку «Поместить на диаграмму величину достоверности аппроксимации»
2. Вы можете рассчитать коэффициент R^2 самостоятельно по формуле =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^;;1);3;1)

Заключение

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

Если вас интересуют еще какие-то модели прогнозирования — напишите нам об этом, и мы постараемся осветить эти темы в дальнейших своих статьях! Или запишитесь на курс «Excel Academy» от SF Education, где мы рассказываем про возможности Excel, необходимые для анализа.

Автор: Алексанян Андрон, эксперт SF Education

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Читайте также:

  
• Как открыть макрос в excel для редактирования
  
• Усн 03 в 1с что это
  
• Почему меняется стартовая страница в браузере
  
• Запись на техническое обслуживание программа для iphone
  
• Как удалить страницу в adobe indesign