text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как спланировать выручку компании на несколько месяцев вперед с помощью Excel

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

Как выявить сложившиеся тенденции в продажах компании

Первое, что предстоит сделать, – построить график и определить тенденцию в продажах. Сначала создаем таблицу в Excel, в которой в одном столбце будут отражаться месяцы и годы, а в другом – сумма выручки. Учитывать последнюю с НДС или нет – не имеет значения. Далее строим график (на ленте меню программы «Вставка» – «График»).

Здесь лучше воспользоваться еще одним из сервисов Excel – линией тренда. Этот инструмент позволяет на ранее созданном графике отразить тенденцию в выручке, а также рассчитывает составляющие уравнения, описывающего тенденцию, и достоверность построенного тренда (см. график. Фактические данные о выручке компании и тренд продаж).

Сделать это просто – кликнуть правой кнопкой мышки по линии графика, из открывшегося меню выбрать «Добавить линию тренда». Потом на вкладке «Параметры линии тренда» выбрать экспоненциальный и поставить галки у пунктов «Параметры». Поставить галки напротив опций «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности апроксимации (R^2)». Если значение апроксимации (R2) больше 0,8, уже хорошо, значит, описанный тренд «угадал» больше 80 процентов фактических продаж. А уравнение, выведенное на диаграмму, можно использовать в дальнейших расчетах.

Как определить сезонность продаж

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

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

К примеру, фактическое значение выручки за январь 2006 года – 3 656 174,72 руб. Чтобы определить значение тренда в январе 2006 года, в эту формулу вместо Х нужно подставить сумму выручки. В Excel выражение будет выглядеть так: «=3409872,466741*EXP(0,037068*C9)», где «С9» – это ячейка таблицы с фактической выручкой за первый месяц 2006 года.

Затем вычисляется среднее значение индекса сезонности за несколько лет (см. таблицу. Расчет индекса сезонности).

Как оценить темпы роста продаж

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

Первое, что потребуется, – собрать данные о выручке за 4–6 месяцев, предшествующих текущему.

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

К примеру, если продажи за декабрь 2009 года фактически составили 10 903,7 тыс. руб., а индекс сезонности в том же декабре – 119,1 процента, соответственно, очищенная от сезонности выручка будет равна 9154,8 тыс. руб. (10 903,7 тыс. руб. : 119,1%).

И наконец третье – вычислить среднемесячный темп роста выручки. Сначала определить темп роста в исследуемом периоде (за те самые шесть месяцев, на основе которых проводится анализ, – для примера будем использовать месяцы с декабря по июнь 2009 года). Он рассчитывается как отношение очищенной выручки за последний месяц в выборке (декабрь 2009 года) к первому месяцу (июню 2009 года) по формуле 1.

Формула 1. Расчет темпа роста выручки за период

Среднемесячный темп роста выручки определяется по формуле 2.

Формула 2. Расчет среднемесячного темпа роста выручки

Кстати, в Excel рассчитать корень шестой степени не составит труда, а формула расчета среднего ежемесячного темпа прироста будет записана так: «=А16^(1/6)-1», где «A16» – ячейка, содержащая темп роста за шесть месяцев.

Как составить прогноз выручки

Будущая выручка компании рассчитывается по формуле 3.

Формула 3. Расчет прогнозируемой выручки от продаж

Если компания планирует выручку в начале января, то расчет на цифрах будет выглядеть так. Выручка за декабрь, очищенная от фактора сезонности, – 9154,8 тыс. руб., среднемесячный темп роста – 99,28 процента, индекс сезонности для января – 99,3 процента. Соответственно, ожидаемая выручка января составит 9025,3 тыс. руб. (9154,8 тыс. руб. × 99,28% × 99,3%). Если нужно спрогнозировать выручку не только на январь, но и на февраль, март, апрель и т.  д., придется последовательно выполнить те же расчеты, принимая за фактические продажи очищенный от сезонности прогноз предшествующего месяца.

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

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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