4.3. Прогнозирование и планирование в рамках бюджетного процесса

Рубрика: 4. Финансы

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

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

Рассмотрим пример. В первый месяц было произведено 100 единиц продукции, а затраты составили $1200. Во второй месяц было произведено 150 единиц при затратах $1550. Спрогнозируйте затраты для третьего месяца при плановом объеме производства 120 единиц продукции.

Решение. Допуская, что затраты можно разделить на полностью постоянные и полностью переменные, можно вычислить модель поведения затрат, используя уравнение: y = a + bx, где у – ежемесячные суммарные затраты, а – ежемесячные постоянные затраты, b – переменные затраты на единицу продукции, х – объем выпуска. Если х повышается на 50, то y повышается на 350. Следовательно b = 7, а = 500. Таким образом мы вывели, что переменные затраты на единицу продукции составляют $7, а постоянные затраты в месяц – $500. Если планируется в третьем месяце выпустить 1200 единиц продукции, можно сделать прогноз, что суммарные затраты в третьем месяце составят $1340 (500 + 7*120).

Рассмотрим еще один пример. На рис. 1 показаны месячные объемы продаж продукта.

Рис. 1. Объем месячной реализации продукта

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

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

Решение. Данные показывают наличие определенной зависимости. Ежемесячные объемы реализации продукта снижаются, но не равномерно. Представим показатели графически (рис. 2).

Рис. 2. Графическое представление объема реализации с начала года

Зависимость очевидна, но кривая неравномерна. Вероятно, имеются случайные факторы, которые влияют на объем реализации. Это может быть количество осадков, или сюжет, показанный по телевидению, или еще что-либо…

Для прогнозирования продаж в октябре следует разработать математическую модель, связывающую объем реализации и время. Для этого используется регрессионный анализ. Звучит пугающе, но ничего сложного в его применении с помощью Excel нет. Выделите кривую с исходными данными (как на рис. 2) и вызовите контекстное меню, кликнув правой кнопкой мыши. Выберите опцию Добавить линию тренда. По умолчанию Excel использует линейный тренд, то есть находит такие коэффициенты a и b, которые описывают прямую y = a + bx, наиболее близкую к исходным данным (используется метод наименьших квадратов). Включите опции Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации R2 (рис. 3). Если R2 = 1, значит линия тренда на 100% описывает исходные данные. Чем меньше R2, тем менее достоверна аппроксимация. Например, в нашем случае R2 = 0,9733. Говорят, что на 97% данные описываются уравнением прямой: y = 319,67x + 953,89.

Рис. 3. Линейный тренд

Линейная аппроксимация дала неплохой результат, и на этом, в принципе можно было бы и остановиться, предложив линейную модель для вычисления объема продаж в октябре (10-й месяц):

у = 319,67*10 + 953,89 = 4151 – 3690 = 461

Покажем, как можно улучшить модель. Для этого последовательно выберем несколько альтернативных аппроксимирующих моделей, которые предлагает Excel (рис. 4). Видно, что логарифмическая модель (рис. 4б) показала чуть большую точность: R2 = 0,9751, чем линейная. Полиноминальная модель еще более повысила качество аппроксимации (R2 = 0,9911). А чемпионом оказалась степенная модель с R2 = 0,9942.[2] В рамках этой модели можно прогнозировать объем продаж в октябре на уровне:

у = 1042,1*100,5817 = 3977 – 3690 = 287

Всего лишь небольшая игра с моделями, лишь незначительное повышение R2 с 0,9751 до 0,9942, а насколько отличается прогноз!

Рис. 4. Сравнение аппроксимирующих моделей: а) линейная, б) лоагрифмическая, в) полиноминальная со степенью 2, г) степенная

Временной ряд

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

Обратимся к примеру. Следующие данные показывают квартальные продажи в единицах продукции:

Рис. 5. Квартальные продажи (в единицах продукции)

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

Рис. 6. Графическое изображение квартальных продаж (временной ряд)

График дает четкое представление о том, как изменялась выручка от реализации в течение 4-летнего периода. Можно заметить, что выручка от реализации увеличивалась на всем протяжении периода анализа, но с сезонными колебаниями. К концу года выручка от реализации увеличивается, затем в середине года снижается. Явно изучаемый продукт имеет сезонную структуру продаж (например, это зимние сапоги).

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

Изменения временного ряда могут вызвать различные факторы:

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

b)      Циклические колебания. Это фактор, посредством которого долгосрочные циклы в торговле вызывают повышение и снижение спроса. Например, британская экономика долгое время имела склонность к 5-летним торговым циклам, в силу чего общий уровень спроса в экономике имел тенденцию колебаться вокруг долгосрочной тенденции роста. Некоторые наблюдатели утверждали, что этот цикл связан с частотой парламентских выборов и тенденцией правительства стимулировать экономику в период перед выборами.

c)       Сезонные колебания. Это фактор, посредством которого тенденции от года к году повторяется в соответствии со структурой сезонных колебаний спроса. В примере выше спрос летом сокращается, а зимой растет.

d)      Случайные (стохастические) изменения. В этом случае на значения выручки от реализации влияют совершенно случайные и непредсказуемые факторы, например, стихийные бедствия, эпидемии и т.п.

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

Если вы хотите постигнуть тонкости прогнозирования, рекомендую изучить Анализ временных рядов. Здесь же мы рассмотрим применение методов, описанных в упомянутой заметке. Итак, для начала подготовим исходные данные для регрессионного анализа временного ряда с сезонной компонентой (рис. 7). Создадим столбец (D), в котором вычислим десятичный логарифм объема продаж; столбец (E) для сквозного порядкового номера квартала и столбцы (F, G и H), в которых проставим единицу, если заголовок столбца совпадает с номером квартала.

Рис. 7. Подготовка исходных данные для регрессионного анализа временного ряда с сезонной компонентой

Запустим Анализ данных (на вкладке Данные), и выберем опцию Регрессия. В открывшемся окне зададим параметры регрессионного анализа (рис. 8):

Рис. 8. Окно Регрессия Анализа данных

Результаты регрессии Excel выводит в виде таблицы (рис. 9).

Рис. 9. Результаты регрессионного анализа

Отметим, что выбранная модель обладает отличной точностью: R2 = 0,9817 (ячейка К5). Для предсказания значений переменной в будущих периодах используются коэффициенты, расположенные в ячейках К17:К21. Так, например, для предсказания объема продаж в Q4-2013, воспользуемся формулой: logYi = b0 + b1Хi, где Yi – объем продаж в i-ый период (только для четвертых кварталов), b0 = 1,4135, b1 = 0,0326, Хi – индекс квартала. Получаем:

logY16 = 1,4135 + 0,0326*16 = 1,9351

Y16 = 101,9351 = 86

Таким образом, модель предсказывает объем продаж в четвертом квартале 2013 г. на уровне 86 (рис. 10).

Рис. 10. Прогноз объема продаж в Q4–2013

Анализ чувствительности. В отношении многих элементов, включенных в план и бюджет, всегда присутствует значительная степень неопределенности. Специалист по бюджетным вопросам должен информировать о такой неопределенности лиц, принимающих решения. Существует различные подходы к решению этой проблемы, и одним из наиболее широко используемых является анализ чувствительности. Подробнее см. Анализ чувствительности в Excel (анализ «что–если», таблицы данных). Анализ чувствительности должен завершать подготовку любых прогнозов. Без него планирование будет попыткой попасть пальцем в небо…


[1] Заметка подготовлена на основании материалов CIMA

[2] Надо отметить, что наилучший показатель (R2 = 0,9998) демонстрирует полиноминальная модель 5-й степени. Вот, правда, наполнить столь сложную модель хоть каким-то смыслом, вряд ли удастся. На практике предпочитают использовать относительно простые модели. Наиболее часто – линейную, реже логарифмическую и степенную, еще реже – полиноминальную.


Прокомментировать