Методы планирования выручки: как увеличить прибыль компании

Прогнозирование выручки предприятия с помощью Excel

Сегодня мы поговорим с вами про прогнозирование, а прогнозировать мы будем выручку предприятия на основании предыдущих ее продаж. У нас есть две колонки: с месяцами и с выручкой за 36 месяцев – то есть за 3 года.

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

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

– Реалистичный – тот, на который стоит ориентироваться;

– Оптимистичный – тот, который с меньшей вероятностью тоже может случиться;

– Пессимистичный – тот, который случится, если что-то пойдет не так.

Расчет коэффициента сезонности

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

Последний месяц у нас 36-й, так что начнем с 37-го и посчитаем прогноз на ближайший год – соответственно, до 48-го месяца. Проще всего это сделать с помощью формулой массива.

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

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

Расчет прогноза с помощью линейной регрессии

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

Х – это месяц, для которого мы будем предсказывать. У нас это 37-й месяц, следующий после 36-го.

Известные значения Y – это выручка;

Известные значения Х – это месяцы, которые нам известны.

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

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

Расчет доверительного интервала

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

Снова используем штатную функцию Excel – ДОВЕРИТ.НОРМ с использованием нормального распределения.

Альфа – уровень надежности, указываем 0,05;

Это значит, что с вероятностью 95% наше значение реальное попадет в наш интервал.

Стандартное отклонение – штатная функция СТАНДАРТОТКЛОН.В, а в качестве выборки берем наш прогноз;

Размер – воспользуемся функцией СЧЁТ, берем ту же выборку.

Фиксировать в этот раз ничего не нужно, потому что доверительный интервал – это одно число.

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

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