Как сделать статистику продаж в excel?

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 (), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье Сводные таблицы.

В таблице имеются столбцы:

• Товар – наименование партии товара, например, «Апельсины»;
• Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен откорректируем имя таблицы на «Исходная_таблица» (см. файл примера).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции СУММЕСЛИ(), однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

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

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

Но, здесь для простоты воспользуемся вторым способом. Для этого:

• Перейдите на лист с исходной таблицей;
• Вызовите Расширенный фильтр ();
• Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи.

• Скопируйте полученный список на лист, в котором будет размещен отчет;
• Отсортируйте перечень товаров ().

Должен получиться следующий список.

• В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:
• =СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])
• Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel.
• Также можно легко подсчитать количество партий каждого Товара:
• =СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)

Отчет №2 Продажи Товаров по Регионам

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

Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530).

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

1. В ячейке B8 введем нижеследующую формулу:
2. =СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8;
3. Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8, в Регионе из ячейки В7. Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8;

Исходная_Таблица[Продажи];C$7)

Обойти это можно, скопировав формулу из ячейки B8, в Буфер обмена, затем вставить ее в диапазон С8:G8, нажав CTRL+V. В ячейки ниже формулу можно скопировать Маркером заполнения.

Отчет №3 Фильтрация Товаров по прибыльности

Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.

Создадим Выпадающий (раскрывающийся) список на основе Проверки данных со следующими значениями: (Все); Да; Нет. Если будет выбрано значение фильтра (Все), то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра «Да», то будут учтены только прибыльные партии Товаров, если будет выбрано «Нет», то только убыточные.

Суммарные продажи подсчитаем следующей формулой массива:

=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5))*

Исходная_Таблица[Продажи])

• После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL+SHIFT+ENTER.
• Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.
• =СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5)))
• Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.

Выбрав в фильтре значение Нет (в ячейке B5), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B.

1. Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА(), которую нужно ввести как формулу массива:
2. =ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)
3. Для ввода формулы выделите диапазон С6:С12, затем в Строке формул введите вышеуказанную формулу и нажмите CTRL+SHIFT+ENTER.
4. Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ(): =СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]=B9)* (Исходная_Таблица[Дата поставки]

Источник: https://excel2.ru/articles/otchety-v-ms-excel

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

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

• Однако, в модели необязательно наличие всех этих компонент сразу.
• Случайная компонента отражает влияние случайных возмущений на модель, которые по отдельности имеют незначительное воздействие, но суммарно их влияние ощущается.
• То есть, в общем случае временной ряд представляет из себя наличие четырех составляющих:

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

Циклическая компонента, по сравнению с сезонностью, имеет более длительный эффект и меняется от цикла к циклу. Поэтому, ее обычно объединяют с трендом.

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

1. Аддитивная модель 
2. 
3. 

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

1. 
2. Если  амплитуда  сезонных  колебаний  возрастает  или  уменьшается,  строят мультипликативную  модель  временного  ряда,  которая  ставит  уровни  ряда в зависимость от значений сезонной компоненты.
3. Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
2. Рассчитываем значение сезонной компоненты St.
3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.

5. Оцениваем качество модели.

• Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.
• 
• 

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что  в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

1. yi — фактическое значение i-го уровня ряда,
2. yt — значение скользящей средней в момент времени t,
3. 2p+1 — длина интервала сглаживания.
4. Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

• Сглаживаем наши уровни ряда и растягиваем формулу вниз:
• 
• Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:
• В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

1. Так как мы рассматриваем аддитивную модель вида: 
2. Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

Средняя оценка сезонной компоненты находится как сумма по столбцу, деленная на количество заполненных строк в этом столбце.

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

Если бы мы располагали периодом побольше, например с 2015, у нас бы добавилась еще одна строка и мы смогли бы полноценно найти среднее, поделив сумму на 2.

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

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

Далее, заполняем нашу таблицу значениями сезонной составляющей дублируя ряд каждые 12 месяцев, то есть три раза:

Шаг 3

• Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.
• T(t) = -23294+34114*t-1593*t^2+26,3*t^3
• Вместо t используем значения из столбца Период из соответствующей строки.

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

1. Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

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

Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

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

• yi — спрогнозированные уровни ряда,
• yi* — фактические уровни ряда,
• n — количество складываемых элементов.
• Модель может считаться адекватной, если:

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

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

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

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

Источник: https://thisisdata.ru/blog/prognozirovaniye-prodazh-v-excel-s-uchetom-sezonnosti/

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

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

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

Для того чтобы оценить некоторые возможности 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 месяца (следующего года) с учетом сезонности:

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

1. График прогноза продаж:
2. График сезонности:
3. Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:



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

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

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

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

Источник: https://exceltable.com/otchety/prognozirovanie-prodazh-v-excel

Прогноз продаж в Excel

Хитрости » 23 Март 2017       Дмитрий       72059 просмотров

• Скачать файл, используемый в видеоуроке:
•   Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)
• А в довершение мы построим красивый график с прогнозом.

Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом. Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:
Нам необходимо рассчитать прогноз продаж на будущий год: с сентября 2015 по август 2016 и отразить это на графике. Я специально беру рваный период посреди года, чтобы показать, что начало прогноза может быть с любой даты.

Чтобы дальше в статье не запутать вас столбцами и где они должны быть добавлены, сразу приведу конечную структуру:

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

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

  Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)

В файле два листа:

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

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

Расчет прогноза
Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
Сама функция требует указания следующих входных данных:

• х — Дата, значение для которой необходимо спрогонозировать (A34)
• Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
• Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

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

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

Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года).

Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

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

24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=((56 769+68 521)/ 1 542 293)*12
=((сумма за янв.2013 + сумма за янв.

2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д.

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

Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
=CONFIDENCE(0.

05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

• алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
• станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
• размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: =$C34+$G$2
Пессимистичный: =$C34-$G$2

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

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts) —График(Line). И получим такую картину:

Наглядно и сразу понятно что к чему и чего можно ожидать.

• Синим – фактические продажи
• Оранжевый – прогноз
• Серый – Оптимистичный прогноз
• Желтый – Пессимистичный

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

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

Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast) —Лист прогноза(Forecast Sheet):

В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:

• Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
• Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
• Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
• Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
• Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
• Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
• Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
• Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
• Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

1. После нажатия кнопки Создать(Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:

   если при создании был отмечен пункт Включить статистические данные прогноза(Include Forecast Statistics), то правее таблицы основных данных будет так же создана таблица статистических данных:

2. Скачать файл:
3.   Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)
4. Так же см.:
   Как быстро подобрать оптимальный вариант решения
   Автообновляемая сводная таблица

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/prognoz-prodazh-v-excel/

​3 крутых Excel отчета для продуктивного планирования продаж для малого бизнеса

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

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

Честно говоря, я так много провел в Excel, что начал думать в терминах таблиц и даже мечтать в колонках и строчках. Много моделей назад, когда я бы аналитиком хэдж-фондов, мой босс, один из двух гениев, которых я когда-либо встречал, дал мне небольшой совет, который определил мое будущее с финансовыми моделями: “Всегда следуй за деньгами”. Я анализировал потенциальные инвестиции в фонд и пытался понять некоторые последние стратегические решения компании. Комментарий моего босса только усилил мое новое увлечение и желание хорошо проработать финансовую модель, которая бы отражала, сколько денег проходит через организацию и насколько эффективно работают менеджеры по продажам, — делится в своей статье Тим Бранк.

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

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

Какими показателями вы будете руководствоваться, чтобы достичь такого роста? Какую вы должны будете иметь CAC (Customer Acquisition Cost — стоимость привлечения клиента) в связи с такой задачей?

Учитывая, какими были эти затраты в прошлом, как много денег вам необходимо потратить на маркетинг/рекламу, чтобы обеспечить заданный рост доходов? Есть ли у вас вообще столько денег, которые вы можете вложить в маркетинг? Кроме того, каким образом остальная организация будет справляться с увеличивающимся потоком новых клиентов?

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

Три следующие модели помогут избежать таких ситуаций:

1. Отчет по прогнозированию на три года.
2. Модель последних четырех месяцев.
3. Когортный анализ доходов.

Прогнозирование на три года

• О чем:
• Позволяет подсчитать простой доход и модель прогнозирования стоимости.
• Описание:

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

Что нужно делать:

1. Поставить стратегические цели, которых ваш бизнес должен достичь.
2. Просчитать план роста.
3. Провести стресс-тест (увеличить и уменьшить запланированную прибыль и посмотреть на способность вашего бизнеса справиться в новых условиях)/
4. Установить ключевые метрики (KPI).
5. Спланировать потребность в персонале, исходя из разных сценариев (обычно на 12 месяцев).
6. Установить черту, сколько минимум/максимум капитала вы планируете привлечь.

Оговорки:

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

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

Модель последних четырех месяцев

1. О чем:
2. Траектория для дохода, с учетом ретроспективного анализа последних 4 месяцев.
3. Что делать:

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

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

Когорный анализ доходов

• О чем:
• Статистика роста доходов, распределенная по когортам, и анализ самых результативных источников прибыли (ежемесячный текущий доход, количество клиентов и рост ACV (стоимость годового контракта).
• Описание:

Эта модель позволяет глубже разобраться в росте прибыли, посмотрев на прошлые и ожидаемые изменения в ACV, количестве клиентов и MRR (ежемесячный текущий доход) в виде когорт. Когортный анализ оценить, что будет катализатором доходности — постепенное увеличение цены контракта или за счет роста объема клиентов (что может быть проблематично в определенном масштабе).

Что нужно делать:

1. Распределить свой доход (прошлый и будущий) по когортам.
2. Разобраться в показателях оттока клиентов и LTV.
3. Расставить приоритеты в работе отдела продаж: необходимый объем контрактов или рост величины стоимости годового контракта.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

Выводы

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

Не нужно быть финансовыми директором, чтобы понять каким образом добиться роста, если хорошо разобраться в этих трех моделях. Уверены, что если вы уделите этим Excel-таблицам достаточно времени, то скоро вы сможете строить комплексные бизнес-процессы и финансовые траектории. А если достигните настоящих высот в прогнозировании, то обязательно доберетесь и до моделирования в 3D-симуляторах.

Если вам интересна аналитика продаж, рекомендуем также прочитать статью на эту тему на блоге Ringostat.

Источник: https://spark.ru/startup/ringostat/blog/16027/3-krutih-excel-otcheta-dlya-produktivnogo-planirovaniya-prodazh-dlya-malogo-biznesa

Планирование продаж в Excel с учётом сезонности

Планирование продаж — отправная точка создания бюджета в Excel. Практически все коммерческие компании начинают планирование в Excel с этого шага.

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

�?ндекс сезонности показывает, насколько конкретный период «выбивается» из основной тенденции повышения или понижения продаж.

Планирование будет состоять в следующем:

1. Расчёт основной линии тренда (подробно о линии тренда – в предыдущем примере простого планирования) и продолжение тренда на прогнозный период.
2. Расчёт индекса сезонности для каждого периода.
3. Расчёт прогнозных данных на основе линии тренда и индекса сезонности.
4. Отображение фактических и прогнозных данных на одном графике.

Скачайте и откройте файл примера Planirovanie-prodazh-s-uchetom-sezonnosti. В диапазоне С6:С17 расположены фактические данные продаж за 3 года (разбитые на 12 кварталов, это упрощённо; в реальности лучше оперировать месячными данными). Необходимо рассчитать значения продаж на следующие два года.

Расчёт и построение линии тренда

Расчёт линии тренда производится следующим образом. На основе данных диапазона В6:С17 строится обычная диаграмма типа график (можно также использовать гистограмму). На диаграмме можно увидеть колебания выручки, явно привязанные к сезонам.

Затем нужно нажать правой клавишей мыши на линии графика, в открывшемся контекстном меню выберите Добавить линию тренда… В открывшемся окне нужно выбрать параметры: Линейная, отметить Показывать уравнение на диаграмме. На графике появится прямая линия тренда и уравнение, её описывающее.

Planirovanie-prodazh-s-uchetom-sezonnostiС помощью этого уравнения рассчитываются данные тренда по каждому периоду. В ячейку А6 записана соответствующая формула «=A6*4359+117264», аналогично рассчитываются все значения столбца Е, включая плановые значения линии тренда в диапазоне Е18:Е25.

Расчёт фактического и планового индекса сезонности

Фактический индекс сезонности в Excel рассчитывается как отношение выручки за период к соответствующему значению линии тренда. В ячейке F6 записана формула «=C6/E6», аналогично рассчитаны значения ячеек F7:F17.

Плановый индекс сезонности рассчитывается несколько иначе.

В ячейке F18 это значение рассчитано формулой «=СРЗНАЧ(F6;F10;F14)/СРЗНАЧ($F$6:$F$17)»: взято усреднённое значение фактических индексов сезонности за несколько одинаковых периодов (1 квартал) и разделено на среднее по всем индексам сезонности за весь период. Аналогичным образом рассчитываются плановые индексы сезонности по остальным периодам.

• Расчёт прогнозных данных
• Прогноз выручки рассчитывается на основе линии тренда и плановых индексов сезонности, эти величины нужно просто перемножить: в ячейке D18 формула «=E18*F18».
• Отображение данных на одном графике

Таким образом сформирован план продаж с учётом индекса сезонности в Excel. Теперь можно продолжать планирование бюджета в Excel, переходя от плана продаж к плану производства и административных расходов.

Источник: http://finexcel.ru/planirovanie-prodazh-v-excel-s-uchyotom-sezonnosti/