ГлавнаяОценка продажПланирование продаж и денежных потоков по модели AR. Пример расчета в Excel

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

Планирование продаж и денежных потоков бизнеса

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

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

Цель оценки денежных потоков – оценка потенциала компании для развития инноваций и реализации инвестиционных проектов.

Продажи компании и денежные потоки тесно взаимосвязаны между собой следующей формулой:

Связь между объемом продаж и денежными потоками предприятия. Формула

где: CFi (Cash Flow) – денежный поток.

Методы планирования продаж и денежных потоков

Существует множество различных методов прогнозирования объема продаж (денежных потоков): модель скользящего среднего (MA, Moving Average),  модель авторегрессии (AR, AutoRegressive), модель авторегрессии скользящего среднего (Autoregressive Moving Average model, ARMA), модель Бокса-Дженкинса и др. В данной статье мы более подробно разберем прогнозирование с помощью модели авторегрессии.

Авторегрессионные модели (англ. AR, AutoRegressive model) используются для описания устойчивых (стационарных) процессов в экономике, когда на будущие значения прогнозируемой величины влияют предыдущие значения. Авторегрессионные модели (AR) используются в прогнозировании как макроэкономических показателей (ВВП, инфляция и др.), так и для оценки микроэкономических показателей: объем будущих продаж, чистой прибыли, размера денежных потоков т.д.

 

Посмотрите Excel таблицу:
Экспресс-оценка инвестиционной привлекательности компании за 1 час!

 

Модель прогнозирования продаж и денежных потоков

Авторегрессионная модель планирования объема продаж и денежных потоков имеет следующий аналитический вид:

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

Yi – прогноз денежного потока или объема продаж;

Yi-1 – значение денежного потока и продаж в предыдущем периоде;

α, β – коэффициенты в модели авторегрессии;

ξ – случайная величина (белый шум).

Пример планирования продаж и денежных потоков предприятия ОАО «МТС» в Excel

Разберем практический пример планирования продаж (выручки) и объема денежных потоков предприятия ОАО «МТС». Данное предприятие было выбрано для анализа, потому что имеет устойчивую сеть дистрибьюторов и постоянный спрос на продукцию, что позволяет адекватно сделать оценку. На рисунке ниже представлена выручка и денежный поток компании за 10 лет. Данные были взяты из официальной отчетности предприятия. Денежные потоки представляли собой сумму чистой прибыли предприятия и амортизации (Форма №5 стр. 640 + Форма №2 стр. 190).

Пример прогнозирования объема продаж и денежных потоков для ОАО МТС в Excel

Объем продаж и денежный поток для ОАО «МТС»

Графически изменение объема продажи и денежного потока имеет следующий вид:

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

Как мы видим из рисунка, денежный поток компании резко изменился в 2009 году из-за большого размера начисленной амортизации, что сильно искажает динамику изменения денежного потока. Сделаем прогноз на два года вперед объема продаж и денежного потока предприятия по модели AR.

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

Планирование продаж и денежных потоков предприятия по модели авторегрессии в Excel

Расчет авторегрессии для объема продаж и денежного потока в Excel

На следующем этапе необходимо рассчитать значения коэффициентов регрессии между рядами и рядами с лагами в один год. Воспользуемся надстройкой: Главное меню Excel → «Данные» → «Анализ данных» → «Регрессия». Рассчитаем параметры отдельно для прогнозирования выручки и денежного потока. Пример оценки объема продаж представлен на рисунке ниже.

Построение регрессии в Excel для оценки продаж и денежного потока предприятия

Мы получили базовые значения в модели регрессии для выручки (объема продаж). Так коэффициент альфа (α) в модели регрессии равен 16851967162, а коэффициент бета (β) 1,04. Полученная статистика по регрессионной модели имеет следующие важные показатели оценки ее адекватности и точности прогнозирования. Первое на что следует обратить внимание это показатель R-квадрат (коэффициент детерминации), который показывает качество модели в шкале от 0 до 1. В нашем примере качество модели высокое и составляет 0,97. Показатель модели критерий-F близок к 0, что показывает устойчивость модели. Статистический показатель P-значение отражает адекватность значений данных коэффициент (альфа, бета) для полученной модели он меньше 15% для обоих коэффициентов, что удовлетворяет нормативам.

Оценка качества эконометрической модели прогнозирования объема продаж и денежного потока предприятия в Excel

Показатели оценки качества регрессионной модели в Excel

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

Модель планирования объема продаж и денежного потока предприятия

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

Прогноз продаж по модели AR =$B$19+B6*$B$20

Прогноз денежного потока по модели AR =$C$19+$C$20*C6

Планирование продаж и денежных потоков предприятия по модели авторегрессии в Excel

Планирование продаж и денежных потоков предприятия по модели авторегрессии в Excel

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

График прогноза объема продаж (выручки) предприятия

График прогноза объема продаж (выручки) предприятия

Графически прогноз денежного потока на два года вперед сильно не изменится.

Прогнозирование денежного потока предприятия по модели авторегрессии AR

Прогнозирование денежного потока предприятия по модели авторегрессии (AR)

Использование методов прогнозирования денежных потоков позволяет оценить показатели эффективности инвестиционных проектов, более подробно про методы оценки проектов читайте в моей статье: «6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI«. Существуют также другие методы планирования объемов продаж компании: XYZ-анализ, ABC-анализ, которые тоже зарекомендовали себя на практике. Так метод ABC анализа на практическом примере разобран в статье: «ABC анализ продаж. Пример расчета в Excel«.

 

Посмотрите Excel таблицу:
Экспресс-оценка инвестиционной привлекательности компании за 1 час!

Мастер-класс: «Как рассчитать план продаж»

Резюме

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

Автор: к.э.н. Жданов Иван Юрьевич