налоги
Анализ финансовых ситуаций в MS Excel. Прогноз и анализ дохода от продаж Планирование бизнес-процессов на основе доходов от продаж может оказаться самым достоверным.
Прийт Сандер, PhD,
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
доцент факультета экономики Тартуского университета
4
Марк Канчуков,
докторант факультета экономики Тартуского университета
прибыль)1, поэтому планирование бизнеспроцессов на основе доходов от продаж может оказаться самым достоверным. Отсюда возникает также вопрос, как прогнозировать доходы от продаж будущих периодов. При прогнозировании дохода от продаж или вообще прогнозе спроса следует выделить два аспекта: направление прогноза и методологический подход (см. ниже рисунок 1). Направление указывает на то, с какого уровня начать движение по направлению к оценочному доходу от продаж предприятия: при
прогнозировании дохода от продаж согласно принципу «сверху-вниз» движение происходит с агрегированного уровня (например, рассматривается все потребление в том секторе, где анализируемое предприятие действует, и прогнозируется изменение агрегированного потребления) на уровень единичного предприятия;
Авторы статьи выражают благодарность доценту Тоомасу Раусу за сделанные им замечания и предложения. Одной из существенных частей финансового планирования предприятия является прогнозирование и анализ дохода от продаж. Можно утверждать, что основой финансовых планов большинства предприятий являются оценочные доходы. Как показывает практика, ошибка при прогнозировании дохода от продаж всегда меньше, чем при прогнозировании других финансовых показателей (EBITDA, EBIT и чистая
1
Подтверждение этому в числе прочего можно найти также в магистерской работе Сандера Пуллеритса, написанной в 2010 году в Тартуском университете, «Эффект привязки в прогнозах эстонских аналитиков». Работа доступна на сайте: http://www. nasdaqomxbaltic.com//files/tallinn/investmentor/ yliopilastood/Pullerits.pdf
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
Направление прогноза
Рисунок 1. Направление прогноза и методологический подход
Сверху-вниз
Прямой
Снизу-вверх
l
l
l
Структурированные и неструктурированные экспертные мнения Симулированное взаимодействие (теория игр) Ожидания, намерения, эксперименты Оценочный (качественный)
l
Модели экстраполирования
l
Разработка данных
l
Причинные модели
Статистический (количественный)
Методологический подход
подходу «снизу-вверх» прогнозируются изменения одиночных компонентов, влияющих на доход от продаж (например, в случае супермаркета ими могут быть число клиентов и стоимость средней корзины покупок, помимо этого – число магазинов; в случае строительного предприятия таким фактором может быть число строительных договоров), а затем «двигаются» в направлении агрегирования;
согласно
прямому методу изменение дохода от продаж в разрезе каждого сегмента продукции или услуги прогнозируется напрямую, без разделения продукции или услуги на одиночные составляющие.
согласно
С точки зрения методологии, можно выделить качественные и количественные методы. Выбор количественного или качественного метода во многом зависит от того, каким является источник информации – оценочным (judgmental) или статистическим (statistical). Понятно, что на практике можно получить более адекватную прогнозируемую оценку, комбинируя различные методы. При объединении количественной и качественной моделей можно говорить о технологическом под-
ходе (technological approach), целью которого является комбинирование технологических, социальных, политических и экономических изменений при прогнозировании социальноэкономических явлений. В настоящей статье мы познакомим читателей с некоторыми возможностями и способами прогнозирования дохода от продаж с помощью количественных методов, рассматривая принципы, которые можно применять при экстраполировании данных. В качестве вспомогательного средства при этом будем использовать MS Excel, хотя в случае многих способов использование программы не является необходимым. В статье рассматриваются такие статистические методы, которые связаны с анализом и моделированием временных рядов, по этой причине не рассматриваются подходы «сверху-вниз» и «снизу-вверх» при прогнозировании доходов от продаж. В качестве вводных данных будем использовать квартальные показатели доходов от продаж одного реального эстонского предприятия в период 2003‑2009 годов, при этом данные за 2009 год используются для проверки результативности различных методов прогнозирования. Размер доходов от продаж по кварталам представлен в таблице 1. 5
налоги Таблица 1. Квартальные доходы от продаж предприятия в 2003‑2009 гг. Год
2003
2004
2005
2006
2007
2008
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
2009
6
Квартал
Доход от продаж (‘000 EUR)
1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4
4 914.8 8 717.5 10 015.0 5 963.0 5 394.1 10 909.7 10 289.8 7 337.1 6 998.3 12 437.2 12 424.4 9 356.7 7 989.0 14 373.7 15 671.1 11 190.9 11 580.8 18 087.0 16 399.7 12 200.7 10 021.3 17 019.7 13 574.8 9 752.9 7 298.7 12 002.6 10 679.6 7 739.7
Если изобразить данные на рисунке, то мы увидим, что в доходах от продаж наблюдается сезонность, – в годовом разрезе наивысшие доходы от продаж предприятие имело во втором и третьем кварталах. Также до конца 2007 года заметен тренд роста продаж. Эти два обстоятельства – сезонность и тренд – с одной стороны, делают проблему прогнозирования дохода от продаж интересной, но в то же время и осложняют ее. Начиная с 2008 года в доходах от продаж отмечается снижение, обусловленное экономичес-
ким кризисом, – восходящий тренд переходит в нисходящий. Интуитивно, это обстоятельство может повлиять на оценки 2009 года, поскольку в подходах, рассматриваемых в статье, не принимаются в расчет макроэкономические связи с финансовыми результатами предприятия. Для начала следует отметить, что горизонт прогноза влияет на выбор модели прогнозирования. Иными словами, выбор используемой модели зависит также от того, на какое время вперед мы желаем составить прогноз. Одно
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
Рисунок 2. Квартальные доходы от продаж предприятия в 2003‑2009 годах. 20 000,0 17 500,0 15 000,0 12 500,0 10 000,0 7 500,0 5 000,0 2 500,0
дело – составить прогноз доходов от продаж на следующее полугодие, и совсем другое – на следующие пять лет. Однако в настоящей статье внимание на критерии выбора моделей прогнозирования не обращается. Все же следует помнить, что используемый при прогнозировании подход зависит от рисунка временного ряда, от имеющихся в данных взаимосвязей, а также от уровня субъективности при прогнозировании.
Наивное прогнозирование Самый простой способ для получения краткосрочной оценки величины какого‑то показателя – это взять последнюю известную величину. Применение такого подхода, известного также под названием «наивное прогнозирование» (naive forecasting), может быть обосновано тогда, когда в движении прогнозируемого по-
казателя невозможно обнаружить никакой закономерности и для прогнозирования невозможно получить вводных данных. Конечно, в нашем случае – где мы имеем дело с длительно и устойчиво работающим предприятием – было бы нелогично предположить отсутствие закономерности во временном ряду доходов от продаж, но если названные выше условия не выполнены, то самым лучшим прогнозным ориентиром может оказаться последняя известная величина. Такой подход пригоден для проверки точности оценки более сложных моделей прогнозирования: если ошибка в прогнозе оценки, полученная при наивном подходе, будет меньше, чем ошибка в прогнозе оценки, полученная с помощью более сложной модели, то от использования более сложной модели можно отказаться. Естественно, точность прогнозирования нельзя сравнивать только на основании оценки одного значения. 7
налоги Примем полученные с помощью наивного прогноза оценки за основу при прогнозировании с помощью других методов. При этом учтем, что оценкой дохода от продажи за I квартал 2009 года является величина дохода от продаж за IV квартал 2008 года, оценкой дохода от продаж за II квартал 2009 года – доход от продаж за I квартал того же года и т. д. Одним из самых простых подходов к прогнозированию дохода от продаж, базирующихся на MS Excel, является использование функции TREND ()2 с синтаксисом TREND (known_y’s, known_x’s, new_x’s, const). Функция, основываясь на методе наименьших квадратов отображает прогнозируемую величину какого‑либо показателя при предположении, что временной ряд соблюдает линейный тренд. Это, конечно, не означет, что MS Excel не позволил бы использовать функцию тогда, когда моделируемый временной ряд не следует четко линейному изменению (как и в нашем случае), но тогда возникает вопрос об информативности таких оценок. Таким образом, фукция TREND () пригодна для прогнозирования доходов от продаж предприятий со стабильным линейным ростом3. В настоящем случае:
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
фактор (known_y’s) означает цепочку
8
значений исторических доходов от продаж; (known_x’s) означает цепочку предыдущих периодов; (new_x’s) – это тот период (или периоды), для которого требуется найти оценочные значения; 2 Точно такой же результат можно получить и в случае
использования функций FORECAST() и LINEST(), хотя они имеют несколько иной синтаксис. 3 Можно еще отметить, что функция TREND() похожа на функцию GROWTH(), имеющую такой же синтаксис. Функция GROWTH() отображает величины для временного ряда с экспоненциальным трендом. Поскольку в нашем примере во временном ряде отсутствует ясно выделяющийся экспоненциальный тренд, то эту функцию мы тоже не рассматриваем.
элемент (const) – это логический оператор,
который позволяет принять в расчет или игнорировать свободный член линейного уравнения, благодаря чему может быть получен более точный результат.
Посмотрим, насколько результативно использование функции TREND () в случае нашего примера. Поскольку мы имеем дело с квартальными данными, то согласно обычному подходу дадим оценку величины доходов от продаж на базе соответствующих кварталов (это значит, что оценку дохода от продаж за I квартал 2009 года получим, опираясь на величины доходов от продаж за первые кварталы 2003‑2008 годов, оценку дохода от продаж на II квартал 2009 года получим, учитывая доходы от продаж за II кварталы прошлых лет, и т. д.). Для этого представим доходы от продаж в компактном виде, рассортировав значения по кварталам – получится таблица 6x4 (6 лет по 4 квартала, рисунок 3). Как видно, оценки имеют сильное смещение вверх, то есть прогнозируемые величины намного выше фактических. Этот результат указывает на тот факт, что тренд следует оценивать на более длительный период; краткосрочный прогноз, базирующийся на тренде, особенно если в промежутке произошли существенные изменения в рыночной конъюнктуре, на самом деле не очень результативен. Если же требуется получить оценку о направлении краткосрочного тренда, то нет смысла опираться на все исторические величины временного ряда.
Экспоненциальное сглаживание Для краткосрочного прогноза из временного ряда можно удалить долгосрочный тренд. Перед тем как рассмотреть возможности удаления тренда, рассмотрим еще один способ, используемый для прогнозирования временных рядов, – им является экспоненциальное
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
Рисунок 3. Прогнозирование доходов от продаж на базе функций TREND () и FORECAST ().
сглаживание (exponential smoothing). Этот способ используется, если во временном ряду имеется сильная волатильность или информационный шум. Методом экспоненциального сглаживания при прогнозировании показателей наибольший вес присваивается последней величине временного ряда и наименьший – предшествующим ему значениям. В данном случае использование экспоненциального сглаживания может быть оправдано тем, что в 2008 году произошел спад доходов от продаж
по всем кварталам, и это снижение могло стать определяющим фактором также для прогнозирования доходов от продаж на 2009 год4. Экспоненциальное сглаживание можно провести по‑разному, ниже мы используем один из простейших способов. Прогнозируемый доход от продаж образуется следующим образом: 4 Можно сказать, что первоначально описанное в статье
наивное прогнозирование представляет из себя особый случай экспоненциального сглаживания, при котором последней величине присваивается вес, равный 1-му, а всем предыдущим значениям – веса, равные нулю.
Доход от продажt+1 = α . Доход от продажt + (1 – α ). t
t
i=1
Доход от продаж i N
Доход от продаж i
где i=1 означает исторический средний доход от продаж и α (0 < α < 1) означаN ет так называемый фактор сглаживания (smoothing factor). Одной из самых больших проблем при прогнозировании является оценка величины коэффициента сглаживания. Величина коэффициента сглаживания может быть оценочной (субъективной), но в то же время величину коэффициента сглаживания можно оценить на базе данных. Проведем экспоненциальное сглаживание в нашем примере, оценив также оптимальное значение коэффициента сглаживания, использовав для этого комплект команд Solver; за основу возьмем, как в прошлый раз, квартальные показатели (см. рисунок 4). Но поскольку мы хотим оценить точность оценки прогноза для 2009 года, то сначала разработаем модель для 2008 года. То есть первоначально мы оцениваем величину коэффициента так, чтобы с помощью приведенной выше формулы можно было бы по возможности более точно оценить квартальные доходы от продаж в 2008 году, а затем составим прогноз доходов от продаж на 2009 год, используя оценочную величину коэффициента сглаживания. 9
налоги Рисунок 4. Экспоненциальное сглаживание: исчисление оптимальной величины коэффициента сглаживания.
БУХГАЛТЕРСКИЕ НОВОСТИ 3 (130) 2011
Окончание статьи читайте в следующем номере.
10