Методы прогнозирования в Excel – примеры и расчеты – Блог SF Education

Формула для расчета среднегодового темпа роста в экономике, Тр=Птек/Пбаз. Темп роста определяет интенсивность изменения (динамики) определенного явления

Примеры решения задач

Пример 1

В 2010 году на предприятии было выпущено продукции на 3650 тыс. руб., а в 2017 — на 4251 тыс. руб. Найти среднегодовой темп роста выпуска продукции.

Решение

Tp‾=425136508−1∗100%=102,2%overline{T_p}=sqrt[8-1]{frac{4251}{3650}}*100%=102,2%Tp​​=8−136504251​​∗100%=102,2%

В среднем за год выпуск продукции возрастал на 2,2%.

Ответ: Tp‾=102,2%overline{T_p}=102,2%Tp​​=102,2%

Пример 2

Рассчитать среднегодовой темп роста с использованием коэффициентов роста.

Решение

Решим данную задачу средствами MS Excel.

Коэффициент роста определяется как отношение показателя текущего года к предыдущему.

Получаем

Далее необходимо получить произведение этих коэффициентов.

Извлекаем корень 7-й степени. Это возможно двумя путями

Или

Также среднегодового темпа роста при помощи средней геометрической

Получили

Ответ: 105,26%.

Формула расчета показателя

CAGR рассчитывается по формуле:

[ CAGR=(frac{EV}{BV})^frac{1}{n}-1,где ]

​( EV )​ – значение параметра на конец периода;

​( EV )​ – значение параметра на начало периода;

​( n )​ – количество лет, за которые проводится исследование.

Что вам понадобится

Постановка задачи

Исходные данные

Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

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

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

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

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

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

• Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
• Мультипликативная модель: Уровень временного ряда = Тренд * Сезонность * Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

• Смешанная модель: Уровень временного ряда = Тренд * Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: “А когда какую модель лучше использовать?”

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная — если амплитуда колебаний зависит от значения сезонной компоненты.

Пример:

Основные показатели матрицы

Для разработки актуальной матрицы БКГ фирмы необходимо определить три основных показателя: 

• Относительная доля рынка; 
  Классический расчет этого фактора — это деление объема продаж продукта (ПП), для которого проводится анализ, на объем продаж у вашего основного конкурента (ППК). Это позволяет оценить позицию компании и ее конкурентоспособность в данной отрасли. 
  Относительная доля = ПП / ППК

• Темп роста рынка;
  Для получения этих цифр надо знать объем рынка в прошлом (А) и текущем периодах (В), а также промежуток времени (t). Это характеристика зрелости и насыщенности площадки, на которой происходит реализация товара или услуг. 
  Темп роста рынка = А — В/В*t12 +1
• Объем продаж в модели БКГ;
  Информация берется из внутренней статистики компании и демонстрирует точки концентрации ресурсов фирмы. В график вносится в виде кругов, где больший радиус соответствует более высоким продажам. 

Диаграмма темпов роста в Excel

Как в Excel создать диаграмму с динамикой темпов роста, где изменения показателей показаны стрелками?
Все просто — рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй график — уровень подписи.

1. Исходные данные
Предположим, нам нужно показать динамику темпов роста выручки:

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения. И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду. В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.

2. Вставляем гистограмму с накоплением
Выделяем таблицу с выручкой и новыми строками. Добавляем гистограмму с накоплением: меню Вставка → Гистограмма → Гистограмма с накоплением.

3. Рост и подписи превращаем в график с накоплением

Выделяем гистограмму мышкой, переходим в меню Конструктор → Изменить тип диаграммы → выбираем вид диаграммы Комбинированная, для данных «подписи» и «рост» выбираем тип диаграммы — график с накоплением. Благодаря этому график с малыми значениями «наложится» на график с большими значениями.

4. Добавляем подписи линии роста

Добавляем подписи для линии роста: выделяем на диаграмме линию роста, в меню переходим на вкладку Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.

Делаем линию роста на диаграмме невидимой: выделяем линию правой кнопкой мышки, нажимаем Формат ряда данных, назначаем тип линии = Нет линий.

5. Добавляем линии ряда данных, удаляем легенду

Выделяем столбцы гистограммы, переходим в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных (если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением).
Удаляем легенду.

6. Задаем тип стрелки

Задаем тип стрелки — выделяем линию правой кнопкой мышки → Формат линий ряда → задаем тип стрелки.
Готово! Подписи и эффекты добавлять по вкусу.

Об этой статье