Аналитическая фабрика. Как настроить финансовую аналитику под задачи бизнеса - Владимир Волнин

3. По данным о величинах среднемесячных остатков определяем среднее значение остатка за все рассматриваемые периоды.

4. Определяем индекс сезонности для каждого периода как отношение среднего значения остатка денежных средств за соответствующий месяц к среднему значению по всем периодам.

5. Проводим корректировку полученных прогнозных значений остатка денежных средств на коэффициент сезонности.

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

Рассмотрим пример прогнозирования поведения остатка денежных средств. В качестве шага периода используем месяц (табл. 14).

Данные представлены за три года помесячно. Таким образом, общее число периодов, относимых к выборке, составляет 36. Последующие прогнозные значения начнутся с 37-го периода.

Выполним действия по описанному выше алгоритму.

1. Построим уравнение регрессии.

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

Вариант 1

Используем формулы метода наименьших квадратов, которые были приведены выше. Для упрощения расчетов дополним таблицу с исходными данными промежуточными результатами (табл. 15).

Тогда:

В результате уравнение регрессии имеет вид:

Y = 7 910 668,65 + 282 569,71 × X.

Вариант 2

Свободный член (b) и параметр, стоящий при независимой переменной (a), можно найти стандартными средствами MS Excel. Для этого следует использовать функции ОТРЕЗОК и НАКЛОН соответственно. Иными словами, результат выполнения функции ОТРЕЗОК позволит получить значение свободного члена в уравнении регрессии:

ОТРЕЗОК(известные значения y;известные значения x),

где известные значения y – зависимое множество наблюдений или данных;

известные значения x – независимое множество наблюдений или данных,

или

ОТРЕЗОК(диапазон данных, характеризующих значения остатков денежных средств; диапазон данных, характеризующих значения порядковых номеров периодов) = 7 910 668,65.

Результат выполнения функции НАКЛОН позволит получить значение параметра, стоящего при независимой переменной в уравнении регрессии:

НАКЛОН(известные значения y;известные значения x)

или

НАКЛОН(диапазон данных, характеризующих значения остатков денежных средств; диапазон данных, характеризующих значения порядковых номеров периодов) = 282 569,71.

В результате уравнение регрессии также имеет вид:

Y = 7 910 668,65 + 282 569,71 × X.

С помощью функции КВПИРСОН можно определить коэффициент детерминации R2 найденного уравнения, чтобы оценить, насколько точно оно позволяет описать имеющийся массив исходных данных:

КВПИРСОН(известные значения y;известные значения x)

или

КВПИРСОН(диапазон данных, характеризующих значения остатков денежных средств; диапазон данных, характеризующих значения порядковых номеров периодов) = 0,6125.

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

Стандартная ошибка уравнения вычисляется с помощью функции СТОШYX:

СТОШYX(известные значения y;известные значения x)

или

СТОШYX(диапазон данных, характеризующих значения остатков денежных средств; диапазон данных, характеризующих значения порядковых номеров периодов) = 2 402 158,64.

В соответствии с методом наименьших квадратов 68 % точек должны располагаться в пределах одной стандартной ошибки найденного уравнения регрессии от линии уравнения и примерно 95 % – в пределах двух стандартных ошибок. Точка (фактическое значение остатка денежных средств), находящаяся в более чем 2 стандартных ошибках от линии наименьших квадратов, называется выбросом и требует первостепенного исследования.

Вариант 3

Используем для построения уравнения регрессии пакет MS Excel «Анализ данных» (рис. 2.15).

В параметрах для расчета уравнения регрессии необходимо задать входной интервал Y, который содержит зависимую переменную (значения остатка денежных средств за исследуемый период), входной интервал Х, который содержит независимую переменную (порядковые значения номеров периодов), выбрать место, где должны будут появиться результаты расчетов (рис. 2.16).

После нажатия «ОК» система выдаст таблицу (табл. 16).

В столбце «Коэффициенты» показано, что наилучшим образом описываемый массив данных характеризует уравнение:

Y = 7 910 668,65 + 282 569,71 × X.

При этом коэффициент R2 составил 0,6125.

Таким образом, результат абсолютно идентичен полученным ранее.

Вариант 4

Еще один вариант – трендовый анализ, который позволяет не только получить уравнение регрессии, но и визуализировать результат.

Для этого на основании массива исходных данных строим график, где в качестве Х выступают номера периодов, а в качестве Y – значения остатка денежных средств. Кликнув на построенном графике правой кнопкой мыши, выбираем команду «Добавить линию тренда» (Add Trendline) (рис. 2.17).

В появившемся окне выбираем интересуемый формат линии тренда (Format Trendline), например линейный (Linear), отмечаем флажки «Показывать уравнение на диаграмме» (Display Equationon Chart) и «Поместить на диаграмму величину достоверности аппроксимации R2» (Display R-Squared Value on Chart). Итогом этих действий станет появление в указанном месте графика (рис. 2.18).

Полученное уравнение имеет вид:

Y = 282 570 × X + 8E + 06.

Такого рода значения довольно часто можно увидеть на графике при выводе уравнения тренда. Выполнить с его помощью последующее прогнозирование не представляется возможным. В таких случаях необходимо левой кнопкой мыши кликнуть на рамку с выведенным уравнением и выбрать «Формат» в редакторе «Работа с диаграммами». Далее нажать «Формат выделенного фрагмента» (Format Selection), щелкнуть на параметре «Число» (Number) и указать числовой формат (например, с 2 знаками после запятой) (рис. 2.19).

В результате уравнение регрессии приобретает необходимый для последующей аналитики вид (рис. 2.20).

Найденное уравнение полностью соответствует полученным ранее и имеет вид:

Y = 7 910 668,65 + 282 569,71 × X.

При этом если задать в параметрах тренда число периодов, на которое нужно сделать прогноз (например, на 12), то можно увидеть фактический результат расчетов по найденному уравнению (рис. 2.21).

Подставляя в качестве Х порядковое значение очередного прогнозного периода, можно определить прогнозную величину остатка денежных средств:

Yянварь2019 = 7 910 668,65 + 282 596,71 × 37 = 18 365 748;

ДС forecastянварь2019 = 18 365 748.

Построим трендовые модели для степенной, логарифмической, полиномиальной (4 степени) и экспоненциальной функций (рис. 2.22).