Вариант 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).
Ввиду того, что из всех приведенных трендовых моделей степенная модель имеет наилучший коэффициент детерминации (он ближе всех к 1), в качестве альтернативного сценария проведем расчет прогнозных значений остатка денежных средств с помощью соответствующего уравнения:
Y = 5 494 889,57 × X0,31.
Подставляя в качестве Х порядковое значение очередного прогнозного периода, можно определить прогнозную величину остатка денежных средств:
Yянварь2019 = 5 494 889,57 × 370,31 = 16 830 674;
ДС forecastянварь2019 = 16 830 674.
Сведем для удобства последующих расчетов все фактические и прогнозные значения в формат таблицы (табл. 17).
Для модели линейного тренда средний остаток денежных средств за январь определяется следующим образом:
Для модели степенного тренда средний остаток денежных средств за январь будет другим:
По аналогии проводим расчет средних значений для остальных месяцев.
Рассчитаем остаток денежных средств за год для модели линейного тренда:
А так будет выглядеть его значение для модели степенного тренда:
Так, для модели линейного тренда индекс сезонности за январь определяется следующим образом:
А вот он же для модели степенного тренда:
Проводим расчет индекса сезонности для остальных месяцев. В расчете индексов будет меняться только значение числителя. Обратите внимание: среднее значение индексов сезонности за весь период (в данном случае – год) должно составлять 1.
Для модели линейного тренда скорректированный остаток денежных средств за январь равен:
ДСTR = ДСn+1forecast × IS = 18 365 748 × 0,71 = 12 975 243.