text
Подпишитесь со скидкой 50% >> ✆ 8-800-550-07-98
Финансовый директор

Прогнозирование в Excel: как составить прогноз продаж в Excel

  • 16 января 2018
  • 10820
экономист
Прогнозирование в Excel: как составить прогноз продаж в Excel

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

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

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

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

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

Таблица 1. Пример продаж за три месяца

Месяц

1 (Обычный уровень)

2 (Полмесяца – растаможка)

3 (Крупный покупатель)

Продажи, шт

1000

400

1500

Рисунок 1. Линия тренда в Excel

Прогнозирование продаж в excel

Линия тренда в Excel

Лирическое отступление по поводу линии тренда в Excel. Данная программа может строить тренд по нескольким алгоритмам. К каждому виду функции подходит свой алгоритм. При подборе видов тренда нужно поставить галочку аппроксимации R, которая показывает, насколько верно подобрана линия тренда. Чем ближе R к 1, тем лучше подобран тренд. В случае, когда у функции имеются экстремумы нужно брать «Полиномиальную» функцию со степенью свободы = кол. экстремумов + 1. Однако экстремум включает в себя наличие и максимума, и минимума одновременно. В нашем примере есть только минимум, поэтому будем пользоваться линейным трендом, как самым простым.

Рисунок 2. Линия тренда в Excel

прогноз excel

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

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

Итак, мы видим, что при среднемесячном уровне продаж менее 1000 шт, нам предлагают запланировать более, чем в 1,5 раза больше. Это весьма не точный прогноз.

Таим образом, первое, что мы должны усвоить – нельзя строить прогноз продаж на неочищенной от случайных и посторонних факторов статистике! Прежде чем, начать прогнозирование в Excel, необходимо в отделе маркетинга узнать все крупные акции и рекламные компании, которые шли по отношению к какой-либо номенклатуре в прошлые периоды, узнать планируемые мероприятия, выставки и т.п. Уточнить у маркетологов, не появились ли у фирмы мощные конкуренты. Соотнести продажи прошлых периодов с данными отсутствия товара на складах. Отследить рост цен из-за увеличения цен поставщиков, резкие колебания валют за прошлый период и т.д.

В нашем примере понятно, что если товар ждал растаможики полмесяца, то, наверное, нужно его продажи умножить на два, чтобы получить адекватные данные за месяц. При этом в последнем месяце львиную долю забрал какой-то оптовик из регионов. Предположим, он купил 600 единиц. Тогда из 1500 нужно их вычесть. Данные о разовой крупной закупке доступны в статистике. Но если бы мы пошли в отдел продаж и спросили менеджеров, то выяснилось бы, что заезжий покупатель ранее покупал эту же продукцию через нашего регионального дистрибутора. И скажем, теперь у дистрибутора переизбыток наших товаров, и в следующем месяце он не планирует у нас закупаться, а планирует ли заезжий купец – пока не известно! То есть пользоваться просто статистикой без экспертных данных строго не рекомендуется.

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

Таблица 2. Очищенная статистика для построения прогноза в Excel

Месяц

1

2

3

Продажи, шт.

1000

800

900

Рисунок 3. Очищенная статистика для прогноза продаж в Excel (пример)

прогноз продаж в excel примеры

Подсчитаем, прогноз продаж на 4 месяц: -50 * 4 + 1000 = 800 шт.

Как видим, прогноз без очистки статистики почти в 2 раза превышает с очисткой.

Как сделать прогноз в Excel: от общего к частному

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

Таблица 3. Продажи в 2007–2010 годах

Продажи (шт.)

Месяц

2007 год

2008 год

2009 год

2010 год

1

8175

6423

10345

11523

2

5079

3991

6380

7528

3

4508

3542

5760

6253

4

2258

1774

2895

3452

5

3401

2673

4335

5387

6

2969

2333

3784

4869

7

2148

1688

2756

5364

8

1326

1043

1622

2456

9

2291

1801

2876

3256

10

2954

2321

3713

4526

11

4217

3313

5340

6981

12

8228

6465

10320

11568

Итого

47554

37367

60126

73163

Рисунок 4. Итоговые продажи с 2007 по 2010 год

Прогнозирование в Excel

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

Чтобы восстановить «нормальные» данные за 2008 год, самое простое, что мы можем сделать – это взять среднее арифметическое между 2007 и 2009 годами. Но при этом нужно иметь ввиду, что в 2009 году многие потребители приобрели то, что не смогли купить в 2008 (так называемый отложенный спрос), то есть в 2009 году уже содержится часть спроса 2008 года. Иными словами, данные за 2009 год мы берем, уменьшая процентов на 10% (в зависимости от рынка). Тогда, восстановленные данные за 2008 год равны: 

расчет прогноза продаж в excel

Итого продажи с 2007 по 2010 годы выглядят следующим образом:

Таблица 4. Скорректированные данные по продажам за 2007–2010 годы

Продажи (шт.)

Месяц

2007 год

2008 год

2009 год

2010 год

Итого

47554

50834

60126

73163

Рисунок 5. Продажи в 2007–2010 годах

как сделать прогноз в excel

Замечу, что для данного тренда более всего подошла Полиномиальная функция со степенью 3.

Из желтого уравнения, обозначенного на диаграмме, мы можем посчитать объем продаж на 2011 год: -377,98 * 53 + 5274,2 * 5 + 52555 = 87678

Теперь, эту цифру надо скорректировать с учетом планов компании. Предположим, что в 2011 году мы запускаем новый гипермаркет, который по расчетам доведет наши плановые продажи до 90000.

Распределение по месяцам

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

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

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

В нашем примере это будет выглядеть так:

Таблица 5. Данные о продажах за 2007–2010 годы

2007

Скользящая
средняя
по 3

2008

Скользящая
средняя
по 3

2009

Скользящая
средняя
по 3

2010

Скользящая
средняя
по 3

1

8175

8175

1

6423

6214

1

10345

7730

1

11523

9790

2

5079

5921

2

3991

4652

2

6380

7495

2

7528

8435

3

4508

3948

3

3542

3102

3

5760

5012

3

6253

5744

4

2258

3389

4

1774

2663

4

2895

4330

4

3452

5031

5

3401

2876

5

2673

2260

5

4335

3671

5

5387

4569

6

2969

2839

6

2333

2231

6

3784

3625

6

4869

5207

7

2148

2148

7

1688

1688

7

2756

2721

7

5364

4230

8

1326

1922

8

1043

1511

8

1622

2418

8

2456

3692

9

2291

2190

9

1801

1722

9

2876

2737

9

3256

3413

10

2954

3154

10

2321

2478

10

3713

3976

10

4526

4921

11

4217

5133

11

3313

4033

11

5340

6458

11

6981

7692

12

8228

6289

12

6465

6708

12

10320

9061

12

11568

11568

На диаграмме (ниже) видно, как скользящая средняя (красным) округлила график

Рисунок 6. Продажи в 2007–2010 годы

прогноз продаж в excel примеры

Теперь на основе «скругленных» данных можно рассчитать коэффициенты сезонности и распределить их по 2011 году.

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

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

Таблица 6. Сезонность продаж

Продажи

2007

2008

2009

2010

Всего

%

Месяц

Скользящая
средняя
по 3

Скользящая
средняя
по 3

Скользящая
средняя
по 3

Скользящая
средняя
по 3

1

8175

6214

7730

9790

31909

14,5%

2

5921

4652

7495

8435

26502

12,0%

3

3948

3102

5012

5744

17807

8,1%

4

3389

2663

4330

5031

15413

7,0%

5

2876

2260

3671

4569

13377

6,1%

6

2839

2231

3625

5207

13902

6,3%

7

2148

1688

2721

4230

10786

4,9%

8

1922

1511

2418

3692

9542

4,3%

9

2190

1722

2737

3413

10062

4,6%

10

3154

2478

3976

4921

14530

6,6%

11

5133

4033

6458

7692

23315

10,6%

12

6289

6708

9061

11568

33626

15,2%

Итого

47984

39262

59234

74291

220771

100,0%

И в соответствии с полученными процентами распределяем продажи на 2011 прогнозный год

Таблица 7. Прогноз продаж в Excel

Месяц

%

2011

1

14,5%

13008

2

12,0%

10804

3

8,1%

7259

4

7,0%

6283

5

6,1%

5453

6

6,3%

5667

7

4,9%

4397

8

4,3%

3890

9

4,6%

4102

10

6,6%

5923

11

10,6%

9505

12

15,2%

13708

Итого

100,0%

90 000

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

Рисунок 7. Прогноз продаж

как рассчитать прогноз

Как видно из диаграммы, в целом оранжевый график 2011 года повторяет контуры предыдущих, но в Июле продажи за 2010 год выше, чем прогнозируются за следующий, несмотря на то, что общий рост 2011 года по отношению к 2010 году составляет

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

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

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

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

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

Таблица 8. Прогнозирование в Excel на основе вероятности

Прогноз
на 2011 год

Предпоследний месяц
 

Сумма сделки

Вероятность

Сумма с учетом вероятности

А

февраль

10505

0,4

4202

Б

май

56848

0,6

34109

В

март

65987

0,8

52790

Г

март

35880

0,5

17940

Д

август

85912

0,2

17182

Итого

255132

126223

Итак, на 2011 год предприятие может запланировать продажи на сумму 126 млн руб.

При этом план по месяцам распределяется следующим образом:

Таблица 9. Прогнозирование в Excel по месяцам

Месяц

Сумма с учетом вероятности

февраль

4202

март

70729,6

май

34108,8

август

17182,4

Общий итог

126222,8

Прогнозирование в эксель: игра в кошки-мышки

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

Шеф, получив от менеджеров план, зная каждого из них как облупленного, прекрасно понимает, что его подчиненные занизили план и накидывает 20–50 % (в зависимости от смелости фантазии). И спускает этот план вниз обратно менеджерам. То есть, увидев нарисованные начальником цифры, недоумевают: зачем, он, во-первых, просил их составлять и обосновывать планы, а во-вторых, как они его будут выполнять, ведь это не реально.

Тогда, понимая, что план не выполним (если начальство сильно его завысило), менеджер откладывает часть сделок на еще один период после планируемого. Потому что, он и так и так не выполнит план, то есть получит штраф за невыполнение или не получит премии, что одно и тоже. Но зато через период точно сделает план и получит свою премию.

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

ПС: в начале статьи, я писал, что прогноз – дело не благодарное, так вот, на самом деле очень даже благодарное – за точное прогнозирование в Excel и составление правильного плана продаж некоторые математические бюро получают неплохие деньги!

Методические рекомендации по управлению финансами компании

Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!

Внимание!
Вы читаете профессиональную статью для финансиста.
Зарегистрируйтесь на сайте и продолжите чтение!

Это бесплатно, и займёт менее 1 минуты! :)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
Присоединяйтесь к выбору Ваших коллег.
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.