Линия тренда в excel на разных графиках

Информационные функции

Данные формулы в основном являются средством для анализа данных. Прописать их довольно просто. Их назначение следующее:

• ЕПУСТО – проверка ячейки на наличие какого-нибудь значения;
• ЕНД – проверка ячейки на наличие ошибки #Н/Д;
• ЕЧИСЛО – проверка значения на соответствие числовому формату;
• ЕОШИБКА – проверка на наличие любой ошибки;
• ЕТЕКСТ – функция выдает истину, если в аргументе указано текстовое значение;
• ЕНЕТЕКСТ – аналогичная проверка, только наоборот;
• ЕОШ – функция вернет истинный результат, если в ячейке будет любая ошибка, отличная от #Н/Д;
• для проверки четного или нечетного значения используются формулы ЕЧЁТН и ЕНЕЧЁТ;
• ЕФОРМУЛА – проверка на наличие формулы в указанной ячейке.

Но есть и более сложная функция, о которой стоит поговорить отдельно.

ЯЧЕЙКА

Используя этот инструмент, вы сможете узнать всю нужную информацию об указанной клетке. При этом необходимо использовать обязательный параметр – «Тип сведений», при помощи которого вы сможете узнать:

• цвет;
• адрес;
• столбец;
• тип;
• и многое другое.

Более подробно можно узнать на сайте Microsoft.

Функции даты и времени

Данный раздел формул очень интересен и полезен. При помощи их можно проводить быстрый анализ какой-нибудь информации либо вытаскивать определенные данные из указанной даты.

• ДЕНЬ – определяется день в указанной дате;
• МЕСЯЦ – определяется месяц в указанной дате;
• ГОД – определяется год в указанной дате;
• СЕГОДНЯ – вывод текущей даты;
• НОМНЕДЕЛИ – вывод номера недели на основании указанной даты;
• ТДАТА – вывод текущей даты и текущего времени;
• ЧАС – определяется какой час указан в определенной дате;
• МИНУТЫ – определяется сколько минут указано в определенной дате;
• СЕКУНДЫ – определяется сколько секунд указано в определенной дате;
• ДЕНЬНЕД – вычисляется порядковый номер дня недели (отсчет начинается с воскресенья, а не с понедельника).

Кроме этого, есть и более сложные формулы. К ним относятся:

• РАЗНДАТ – происходит расчет количества лет, месяцев и дней между указанными датами;
• ДНИ – происходит расчет количества дней между указанными датами (функция появилась в 2013 году);
• ЧИСТРАБДНИ – происходит расчет количества рабочих дней между указанными датами;
• ДЕНЬНЕД – преобразование обычной даты в числовом формате в порядковый номер недели;
• РАБДЕНЬ – вывод даты, которая отстает или опережает указанное количество дней.

Более подробно о последней формуле можно прочитать на официальном сайте Microsoft.

Синтаксис данной функции следующий.

А примеры довольно простые.

Особенности использования функции ПРЕДСКАЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

Описание аргументов:

• x – обязательный для заполнения аргумент, характеризующий одно или несколько новых значений независимой переменной, для которых требуется предсказать значения y (зависимой переменной). Может принимать числовое значение, массив чисел, ссылку на одну ячейку или диапазон;
• известные_значения_y – обязательный аргумент, характеризующий уже известные числовые значения зависимой переменной y. Может быть указан в виде массива чисел или ссылки на диапазон ячеек с числами;
• известные_значения_x – обязательный аргумент, который характеризует уже известные значения независимой переменной x, для которой определены значения зависимой переменной y.

Примечания:

1. Второй и третий аргументы рассматриваемой функции должны принимать ссылки на непустые диапазоны ячеек или такие диапазоны, в которых число ячеек совпадает. Иначе функция ПРЕДСКАЗ вернет код ошибки #Н/Д.
2. Если одна или несколько ячеек из диапазона, ссылка на который передана в качестве аргумента x, содержит нечисловые данные или текстовую строку, которая не может быть преобразована в число, результатом выполнения функции ПРЕДСКАЗ для данных значений x будет код ошибки #ЗНАЧ!.
3. Статистическая дисперсия величин (можно рассчитать с помощью формул ДИСП.Г, ДИСП.В и др.), передаваемых в качестве аргумента известные_значения_x, не должна равняться 0 (нулю), иначе функция ПРЕДСКАЗ вернет код ошибки #ДЕЛ/0!.
4. Рассматриваемая функция игнорирует ячейки с нечисловыми данными, содержащиеся в диапазонах, которые переданы в качестве второго и третьего аргументов.
5. Функция ПРЕДСКАЗ была заменена функцией ПРЕДСКАЗ.ЛИНЕЙН в Excel версии 2016, но была оставлена для обеспечения совместимости с Excel 2013 и более старыми версиями.
6. Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.

Прогноз продаж с учетом роста и сезонности

Функцию ТЕНДЕНЦИЯ хорошо использовать для временного ряда, где данные увеличиваются или уменьшаются с постоянной скоростью.

Временной ряд товарооборота по месяцам с двумя переменными:

Сначала рассчитаем значения линейного тренда с помощью графика Excel. По оси Х – номера месяцев, по оси Y – объем товарооборота.

Добавим на график линию тренда и его уравнение.

Для прогнозирования будущих продаж нужно рассчитать показатели линейного тренда для анализируемых данных и для будущих периодов, используя уравнение тренда y = 490,26x + 37747.

Показатели линейного тренда будем считать для каждого месяца.

Получаем данные тренда и для будущих периодов: 16 – 19. Так, в 16 месяце спрогнозированное значение продаж – 45591,16 тыс. руб.

Теперь спрогнозируем товарооборот с помощью встроенной функции ТЕНДЕНЦИЯ. Вызываем «Мастер функций». В категории «Статистические» находим нужную. Заполняем аргументы:

Известные значения y – диапазон с объемами продаж. Данные необходимо зафиксировать (кнопка F4), чтобы при размножении формулы массив сохранился.

Известные значения x – номера месяцев, для которых функция рассчитает данные для линейного тренда. Данные тоже фиксируем.

Новые значения x – номера месяцев, для которых нужно спрогнозировать продажи.

Задаем аргумент «Конст»: 1. Функция при расчете значений тренда учтет коэффициент a.

Обратите внимание: диапазоны известных значений соразмерны

Функция ТЕНДЕНЦИЯ дала нам те же прогнозные показатели на 16-19 периоды.

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

Модель может считаться адекватной, если:

Итак, рассчитываем ошибку аппроксимации для нашего случая. Так как в основе нашего тренда лежит полином третьей степени, прогнозные значения начинают хорошо повторять фактические значения к концу 2016 года, думаю, я думаю, поэтому корректнее было бы рассчитать ошибку аппроксимации для значений 2017 года.

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении

Полезные ссылки:

• Ссылка на пример Google Sheets
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
• Бывшев В.А. Эконометрика

• Об авторе
• Свежие записи

Екатерина ШиповаМагистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

Статистические функции

Для подсчета и анализа данных в таблице или массиве могут использоваться следующие формулы:

• КОРРЕЛ – расчет коэффициента корреляции на основании двух массивов;
• ЛИНЕЙН – поиск параметров линейного тренда;
• МАКС – поиск наибольшего числа в указанном диапазоне ячеек;
• МИНА – поиск наименьшего значения (числа, текст и логические выражения) в указанном диапазоне ячеек;
• ПРЕДСКАЗ – функция пытается предсказать (вычислить) будущий результат на основании текущих данных;
• РОСТ – расчет прогнозируемого экспоненциального роста по текущим значениям в указанной области данных;
• СКОС – функция для определения асимметрии распределения;
• ТЕНДЕНЦИЯ – аппроксимация значений массива при помощи метода наименьших квадратов;
• ЧАСТОТА – распределение частот в виде вертикального списка;
• ЭКСЦЕСС – функция рассчитывает экспресс большого количества данных;

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

• СРЗНАЧ – подсчет среднего арифметического значения среди указанного массива ячеек;
• СРЗНАЧЕСЛИ – подсчет среднего арифметического значения только тех ячеек, которые соответствуют определённому условию;
• СЧЁТ – расчет количества чисел в указанном диапазоне;
• СЧЁТЕСЛИ – расчет количества чисел в указанном диапазоне, которые соответствуют определенному требованию;
• СЧЁТЕСЛИМН – расчет количества чисел в указанном диапазоне, которые соответствуют нескольким требованиям;

Возможности данных функций можно продемонстрировать следующей таблицей.

Визуализация данных по сравнению продаж с предыдущим периодом в Excel

Создайте новый лист с именем «График» и сделайте в нем таблицу:

Как видно выше на рисунке на этот раз на этапе создания таблица содержит 3 формулы, а точнее внешние ссылки на лист «Обработка» в ячейках: B2, B4и C4.

Диапазон ячеек таблицы B5:C10 заполняем следующей формулой:

Теперь создаем элемент управления графиком и таблицами – полоса прокрутка с помощью, которой мы будем прокручивать номера анализируемых неделей. Для этого выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Полоса прокрутки». Далее рисуем горизонтальную полосу прокрутки размером с диапазон двух ячеек B3:C3:

После чего кликаем по элементу управления правой кнопкой мышки и из появившегося контекстного меню выберите опцию: «Формат объекта». В появившемся окне «Форматирования объекта» на вкладке параметров «Элемент управления» вносим следующие изменения в 3 поля ввода:

1. Минимальное значение: 1.
2. Максимальное значение: 5 (так как в одном месяце 5 неполных недель).
3. Связь с ячейкой: Обработка!$D$2 – в эту ячейку полоса прокрутки будет передавать числовые значения от 1-го и до 5-ти (то есть порядковые номера недель в месяцах).

В результате мы теперь одним кликом мышки изменяем все значения в ячейках таблицы. Чтобы убедиться, что все сделано правильно – протестируйте.

Как построить график спроса и предложения в Excel

Спрос – это желание обладать товаром или услугой, подкрепленное возможностью. То есть «хочу и могу». Не просто потребность, а платежеспособность в отношении определенного продукта в существующих рыночных условиях.

Величина спроса – число товаров и услуг, которое человек готов купить в данный момент, в данном месте, за данную цену.

На величину объема сбыта влияют прямо и косвенно множество факторов:

1. активность рекламной кампании;
2. мода;
3. вкус покупателя, ожидания;
4. размер дохода потребителя;
5. полезность товара;
6. доступность;
7. стоимость схожих категорий товаров и т.д.

Зависимость между величиной спроса и факторами – это функция спроса. В экономической практике принято рассматривать функцию спроса от цены. В данном случае все определяющие величину спроса факторы считаются неизменными.

Графическая иллюстрация функции спроса от цены – кривая спроса. Основное свойство данного экономического параметра: уменьшение цены ведет к возрастанию сбыта продукта. И, напротив, высокая стоимость продукта ограничивает спрос на него.

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

1. Внесем данные по ценам на товар и по количеству проданных единиц в шкалу спроса:
2. Переходим на вкладку «Вставка», инструмент «Диаграммы» — выбираем тип графика.
3. Для настройки делаем график активным, чтобы появилось дополнительная группа закладок под названием «Работа с диаграмами». Выбераем закладку «Конструктор», а в ней инструмент «Выбрать данные».
4. В окне «Выбор источника данных» из левой колонки «Элементы легенды (ряды)» удаляем данные «Продано».
5. В этом же окне в правой колонке «Подписи горизонтальной оси (категории)» жмем «Изменить».
6. Выделяем диапазон ячеек B2:B6 чтобы автоматически заполнить параметрами поле в появившимся окне «Подписи оси».

Обратите внимание! Количество продукции – ось абсцисс (горизонтальная). Цена – ось ординат (вертикальная)

Полноценный анализ ситуации на рынке невозможен без рассмотрения предложения. Это совокупность продуктов и услуг, которые присутствуют на рынке и предлагаются продавцом покупателю за определенную цену.

У данной экономической категории есть величина (число товаров и услуг, предлагаемых в конкретный временной промежуток, в конкретном месте, по определенной цене).

Цена предложения – прогнозируемый показатель. Это минимальная сумма, за которую продавец согласен предложить потребителю свой товар.

Объем предложения зависит, соответственно, от цены. Только в данном случае наблюдается обратная зависимость (ср.: объем): чем ниже цена, тем меньше предлагаемой продукции. Продавец лучше придержит часть товара на складе, чем отдаст за бесценок. Хотя на объем предложения влияет не только стоимость.

Функция предложения от цены показывает зависимость величины предложения от его денежной оценки.

Добавим в демонстрационную табличку еще один столбец. Условно назовем его «Предложено»:

Теперь отобразим на графике сразу 2 показателя: «Спрос» и «Предложение». В одной области. Для этой цели подойдет точечная диаграмма.

Выделяем таблицу с исходными данными и выберем инструмент: «Вставка»-«Точечная»-«Точечная с гладкими кривыми и маркерами».

Снова выбираем «Конструктор»-«Выбрать данные» и задаем параметры в окне «Изменение ряда» для графиков:спрос:
предложение:

Следим, чтобы горизонталь показывала количество, а вертикаль – цену. Получаем результат:

Интерпретируем. Пересечение графиков иллюстрирует становление равновесной цены (50 рублей) и равновесного количества продаж (300 единиц). Область выше равновесной цены – избыток продукции. Производитель вынужден постепенно уменьшать стоимость. Область ниже равновесной цены – дефицит. Цены будут повышаться.

Как сделать интерактивный дашборд в Excel без VBA-макросов

Данный шаблон дашборда предназначен для выполнения визуального сравнительного анализа продаж по трем категориям товаров A, B, и C. Этот простой дашборд содержит все лишь только 4 блока. Для 3-х блоков применяется учетный период в одну неделю, а для четвертого блока – 1 месяц.

Описание диаграмм и графиков на простом дашборде Excel

Рассмотрим каждый блок по отдельности:

Первый блок «Соотношение продаж по товарам» визуализирует на c-образной диаграмме соотношение количества проданного товара за текущую неделю. Также он отображает соотношение количества продаваемых трех групп товаров A, B, и C относительно между собой.
В качестве второго блока «Доли продаж по дням» используется гистограмма с накоплением. С помощью нее прекрасно визуализируются доли каждой группы товаров в суммарном объеме общего количества проданного товара отдельно по каждому дню недели. Можно отслеживать тенденции в который день недели, какая группа товаров наиболее активно или пассивно продается. Исходя из данной информации строится тактики или целые маркетинговые стратегии для продвижения товаров в нужный период времени. Для этого используются: акции, скидочные купоны, дисконтные программы, конкурсы и т.д. и т.п. Существует много маркетинговых инструментов, которые не позволят вашим товарам залеживается на полках магазина или в остатках на складах

Важно лишь правильно поставить цель и определить нужное время для эффективного их применения.
Третий блок «Активность продаж по дням» представляет собой график с областями для сравнения активности продаж по трем группам товаров на протяжении текущей недели. Сразу важно отметить, что данный блок немного отличается от 3-х остальных блоков

Дело в том, что его исходные данные на листе «Processing» подготовлены таким образом, что группа с наибольшими показателями продаж за текущую неделю всегда смещается на задний план, а группа с наименьшими – всегда на переднем плане. Поэтому на нем важно читать легенду графика, так как не в каждую неделю группа «C» бывает самой активной в продажах. Соответственно ее цвет может быть другой, потому как оранжевый цвет только для типовой категории на заднем плане для данного графика. Такой подход к визуализации сделан для удобного чтения графических фигур при визуальном анализе. Но при необходимости всегда можно изменить формулы на листе «Processing», чтобы не использовать сортировку исходных данных перед выводом их на данный график в 3-м блоке дашборда.
И наконец 4-й блок под названием «Продажи в среднем за месяц». Данный блок дашборда также обладает своей особенностью. Он выводит кривые со средними показателями продаж по каждой группе товаров за 1 текущий месяц. Поэтому если все остальные 3 блока автоматически обновляют свои значения при изменении номера одной недели в году, то 4-й график с кривыми изменяет свои показатели только когда вместе с номером недели изменяется номер месяца анализируемого текущего года. Например: февраль начинается с 6-ой недели, а октябрь с 41-ой недели и т.д. Всего в году 52-53 недель.

В левой части дашборда расположена шкала для визуализации выбранного текущего периода в году:

Управляется дашборд с помощью элемента управления «счетчик» или вводом числовых значений 1-53 в ячейку B22. При изменении значения в этой ячейке изменяются все показатели на дашборде соответственно. А также перемещается стрелка на шкале. Сама шкала разбита на 12 месяцев напротив каждого месяца указан номер недели с которой начинается этот же месяц. Таким образом номера на шкале служат подсказкой для быстрого перехода по месяцам с помощью ввода соответственного ему номера недели в ячейку B22.

В целом получился удобный шаблон для интерактивной визуализации Ваших данных на дашборде:

Исходные данные как всегда заполняются на листе «Data». Шаблон не содержит макросов, поэтому редактировать его сможет даже пользователь ниже среднего уровня освоения универсальной программой Excel.

Логические функции

Данные конструкции используются для построения больших и сложных формул.

• И – истина, если все условия истинные;
• ИЛИ – истина, если хотя бы одно условия истинное;

Для анализа различных условий используются следующие функции:

• ЕСЛИ – для проверки одного события;
• УСЛОВИЯ – то же самое, только с огромным количеством условий.

Последняя из указанных выше появилась только в редакторе Excel 2016. Ранее использовался вариант «ЕСЛИМН».

В качестве примера можно привести следующую таблицу.

В данном случае использовались сразу две функции: «ЕСЛИ» и «ИЛИ».

=ЕСЛИ(ИЛИ(D3=»Первая»;D3=»Вторая»);100;0)

Для проверки работы формулы можно использовать конструкцию с «ЕСЛИОШИБКА». Если всё составлено корректно, то вы увидите результат вычислений. В противном случае увидите введенное значение в текстовом виде.

Можете ли вы анализировать тенденции данных с помощью спарклайнов?

Да, вы определенно можете! Спарклайны — это небольшие диаграммы, которые вы видите внутри отдельных ячеек рабочего листа, которые можно использовать для отображения анализа тенденций в Excel или для визуального представления ваших данных и отображения тенденции в данных

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

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

Вот как вы можете сделать это:

1. Нажмите на пустую ячейку рядом с данными, которые вы хотите проиллюстрировать на спарклайне.
2. Затем вам нужно нажать на кнопку Вставить; оттуда вы направляетесь к набору Sparklines, а затем либо на столбец, линию или выигрыш / проигрыш.
3. Перейдите в поле «Диапазон данных», где вам нужно будет ввести диапазоны ячеек, которые содержат данные, которые вы хотите отобразить в спарклайне. Например, если ваши данные расположены в A, B, C и D строки 3, вам нужно ввести A3: D3.
4. Кроме того, вы также можете выбрать диапазон ячеек на листе напрямую. Вы можете временно свернуть диалоговое окно, а затем снова открыть его.
5. Как только вы нажмете на OK, вы увидите раздел Sparkline Tools на ленте. Вы можете воспользоваться рекомендациями на вкладке «Дизайн», чтобы адаптировать спарклайны в соответствии со своими предпочтениями.

Как вы настраиваете свои спарклайны?

Закончив создание спарклайнов, вы можете в любое время изменить его формат, стиль и тип:

1. Сначала вам нужно выбрать спарклайны, которые вы хотите настроить.
2. Для этого вам нужно перейти на вкладку «Дизайн» и выбрать параметры, которые вы хотите выбрать.
3. Здесь вы можете изменить формат или стиль спарклайнов, а также настройки оси.
4. При нажатии на параметр «Тип оси даты» в Excel откроется новое диалоговое окно «Спарклайн». здесь вы можете выбрать диапазон вашей рабочей книги, в котором есть все значения данных, которые вы ищете в своих данных спарклайна.
5. После завершения настройки вы увидите результаты в своем листе Excel.

Заключение по анализу трендов в Excel

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

Использование скользящих средних в Excel

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

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

Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.

Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.

Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.

1. По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
2. Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
3. По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
4. Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
5. Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:

Относительные отклонения:

Средние квадратичные отклонения:

При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.

После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).

Прогнозное значение выручки на 12 месяц – 9 430 у.е.

Как составить план продаж на месяц: пример

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

Реализация за прошлый год:

Бюджет продаж на месяц будет тем точнее, чем больше фактических данных берется для анализа. Поэтому мы взяли цифры за 12 предыдущих периодов (месяцев).

Так как предприятие развивающееся, для прогнозирования продаж можно использовать линейный тренд. Математическое уравнение: y = b*x + a. Где

• y – продажи;
• х – порядковый номер периода;
• а – минимальная граница;
• b – повышение каждого следующего значения во временном ряду.

Значение линейного тренда в Excel рассчитывается с помощью функции ЛИНЕЙН. Составим табличку для определения коэффициентов уравнения (y и х нам уже известны).

Для быстрого вызова функции нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Ins. А комбинацией SHIFT+F3 открываем окно с аргументами функции ЛИНЕЙН (курсор стоит в ячейке G 2, под аргументом b). Заполняем:

Выделяем сразу 2 ячейки: G2 и H2 (значения аргументов b и а). Активной должна быть ячейка с формулой. Нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Enter. Получаем значения для неизвестных коэффициентов уравнения:

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

Следующий этап – расчет отклонений значений фактических продаж от значений линейного тренда:

Это значение нам необходимо для расчета сезонности. Далее находим средний показатель реализации за все периоды с помощью функции СРЗНАЧ.

Рассчитаем индекс сезонности для каждого периода. Формула расчета: объем продаж за период / средний объем.

С помощью функции СРЗНАЧ найдем общий индекс сезонности:

Спрогнозируем уровень продаж на будущий месяц. Учтем рост объема реализации и сезонность.

Сначала найдем значение тренда для будущего периода. Для этого в столбце с номерами периодов добавим число 13 – новый месяц. Продлим формулу в столбце «Значение тренда» на одну ячейку вниз:

Умножим значение тренда на индекс сезонности соответствующего месяца (в примере – «января»). Получим рассчитанный объем реализации товара в новом периоде:

По такому же принципу можно спрогнозировать реализацию на 2, 3, 4 и последующие месяцы.

График прогноза с линией тренда:

При построении финансового плана продаж используется понятие «сечения». Это детализации плана в определенном «разрезе»: по времени, по каналам реализации, по покупателям (клиентам), по товарным группам, по менеджерам. Такая детализация позволяет проверить реалистичность прогноза, а в дальнейшем – проверить выполнение.

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

Уравнение линейного тренда:

y = bx + a

• y — объемы продаж;
• x — номер периода;
• a — точка пересечения с осью y на графике (минимальный порог);
• b — увеличение последующих значений временного ряда.

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

1. Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a. В ячейке D15 Используем функцию ЛИНЕЙН:
2. Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
3. Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
4. Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
5. Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
6. Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
7. В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
8. Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
9. Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
10. На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:

Общая картина составленного прогноза выглядит следующим образом:

График прогноза продаж:

График сезонности:

Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

1. Выделяем трендовую составляющую, используя функцию регрессии.
2. Определяем сезонную составляющую в виде коэффициентов.
3. Вычисляем прогнозные значения на определенный период.

Нужно понимать, что точный прогноз возможен только при индивидуализации модели прогнозирования. Ведь разные временные ряды имеют разные характеристики.

бланк прогноза деятельности предприятия

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Результат расчетов:

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

Полученные результаты:

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

Выводы

Построение линии тренда – это довольно простая процедура. А вот анализ данных с ее использованием – уже задачка посложнее. Но с помощью встроенных средств Excel можно составлять эффективные прогнозы развития самых разных показателей из разных сфер. И хотя использование трендовой линии по большей части автоматизировано в современных программах, иногда может потребоваться использование Excel для этой цели. 

Вообще, для составления эффективных прогнозов одной трендовой линии недостаточно. Это не панацея, и не стоит надеяться, что настолько элементарная математическая модель способна творить чудеса. Тем не менее, это один из самых простых элементов статистического анализа. И теперь вы знаете, как его использовать в реальной работе.