Как рассчитать суммарный объем продаж в excel

Обновлено: 07.01.2025

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

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
2. Расчет сезонности;
3. Расчет прогноза;
После изучения данного материала вы сможете выбрать оптимальный способ расчета значений линейного тренда, который будет удобен для решения вашей задачи, а в последствии, и для расчета прогноза наиболее удобным для вас способом.

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

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

Уравнение линейного тренда y(x)=a+bx, где

y — это объёмы продаж

x — номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

1-й способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У - объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

При расчете значений линейного тренде нам будут известны:
1. Время - значение по оси Х;
2. Значение "a" и "b" уравнения линейного тренда y(x)=a+bx;
Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

27-го y=135134*27+4594044=8242686

2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

известные значения y (объёмы продаж за периоды),

известные значения x (номера периодов),

вместо константы ставим 1,

вместо статистики 0,

Получаем 135135 - значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

Получаем 135135, 4594044 - значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов - x, для которых хотим рассчитать значения линейного тренда.

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также быстрее.

3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу
1. известные значения y - это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
2. известные значения x - это номера периодов x для известных значений объёмов продаж y;
3. новые значения x - это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
4. константа - ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;
Для того чтобы рассчитать значения тренда для всего временного диапазона, в "новые значения x" вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

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

Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

Вместо "известные значения y" - объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

"известные значения x" - это номера периодов для каждого выделенного объёма продаж.

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

5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO
1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:
- Что рассчитываем - значения тренда;
- Тренд - Линейный тренд;
- Временной ряд - месячный;
и сохраняем;
2. Заходим в меню программы и нажимаем "Start_Forecast". Значения линейного тренда рассчитаны.

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

В следующих статье "Как самостоятельно сделать прогноз продаж с учетом роста и сезонности" мы:

О том, что еще важно знать о линейном тренде, вы можете узнать в статье "Что важно знать о линейном тренде".

Точных вам прогнозов!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Статья представляет собой подборку заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.

Упражнение 1. «Магазин»

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

где F2:F11 — диапазон стоимости продукта из первой таблицы.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.

Упражнение 2. «Студенческие стипендии» [1]
- стипендия не назначается, если среди оценок есть хотя бы одна двойка;
- 3,0 < средний балл < 3,5 — 1000р.
- 3,5 < средний балл < 4,0 — 1200р.
- 4,0 < средний балл < 4,5 — 1500р.
- 4,5 < средний балл < 5,0 — 1800р.
- средний балл = 5,0 — 2000р.
Определить общую сумму назначенных стипендий.

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.
- Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
- Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:
Упражнение 3. «Завод железобетонных изделий»

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

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

Из данной статьи вы узнаете, как в Excel рассчитать прогноз продаж с учетом роста и сезонности.

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

Процесс расчета прогноза разделим на 3 части:
1. Расчет значение тренда;
2. Определение коэффициентов сезонности;
3. Прогнозирование продаж;
Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).

Для расчета значений тренда:
1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). Выделяем 2 ячейки, в левой - формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.
2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).
О различных вариантах расчета значений линейного тренда вы можете почитать в статье "5 способов расчета значений линейного тренда" и выбрать для себя максимально удобный.
Для расчета коэффициентов сезонности:
1. Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
2. Для каждого месяца определяем среднее отклонение за последние 5 лет.
3. Определяем общий индекс сезонности — среднее значение коэффициентов, рассчитанных в 4 пункте;
4. Рассчитываем коэффициенты сезонности; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);
Более подробно о коэффициентах сезонности вы можете почитать в статье "Как рассчитать коэффициенты сезонности, очищенные от роста?"
Рассчитываем прогноз продаж с учетом роста и сезонности:
1. Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
2. Рассчитываем значения трена для будущих периодов. В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
3. Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.
Прогноз с учетом роста и сезонности готов.

Вы также можете руками корректировать прогноз, изменяя коэффициенты a и b линейного тренда y=bx+a, об этом подробно вы можете почитать в статье «О линейном тренде».

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

мероприятия по стимулированию сбыта,

ввод новых продуктов,

открытие новых направлений продаж,

спец. клиенты с разовыми значительными закупками

и т.д., но об этом в следующих статьях.

Точных вам прогнозов!

С помощью программы Forecast4AC PRO вы сможете рассчитывать прогноз с учетом роста и сезонности для более, чем 5000 строк одновременно одним нажатием клавиши. Легко и быстро!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Читайте также: