Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

Скачать файл, используемый в видеоуроке:

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

С выходом релиза УТ 11.1.4.1 в программе появился функционал сезонных коэффициентов . Сезонные коэффициенты предназначены для возможности классификации номенклатуры по различным группам с целью дальнейшей автоматической корректировки планов продаж с учетом фактора сезонности.

В этой статье разберем, как данный функционал работает.

Применимость

Статья написана для редакции УТ 11.1 . Если вы используете эту редакцию, отлично – прочтите статью и внедряйте рассмотренный функционал.

Если Вы работаете со старшими версиями УТ 11, то данный функционал является актуальным . Наиболее заметным отличием УТ 11.3/11.4 от редакции 11.1 является интерфейс Такси. Поэтому, чтобы освоить материал статьи – воспроизведите представленный пример на своей базе УТ 11. Таким образом, Вы закрепите материал практикой:)

Создание и использование сезонных коэффициентов

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

Также в программе мы можем указать периодичность сезонных коэффициентов. Данная настройка устанавливается также на закладке «Администрирование» – «Маркетинг и планирование» – «Периодичность указания сезонных коэффициентов». Для примера установим периодичность «Месяц».

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

Давайте создадим новую сезонную группу. Перейдем на вкладку программы «Нормативно-справочная информация» – «Настройки и справочники» – «Сезонные группы». Например, назовем нашу новую группу «Мебель – сезонная группа».

Для увеличения нажмите на изображение.

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

Теперь заполним поле «Сезонна группа», например, в одной из наших номенклатурных позиций. У меня это будет номенклатура «Шкаф».

Для увеличения нажмите на изображение.

После этого перейдем обратно к списку сезонных групп и рассчитаем сезонные коэффициенты. Т.е. нажмем кнопку «Сезонные коэффициенты».

Для увеличения нажмите на изображение.

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

Перед нами откроется окно расчета/заполнения коэффициентов. Для каждого месяца мы можем указать вручную значение коэффициента. Но давайте рассчитаем автоматически коэффициенты в зависимости от статистики продаж – нажмем кнопку «Рассчитать по статистике продаж».

Для увеличения нажмите на изображение.

В открывшемся окне укажем, например, расчетный период 1 год. Значение поля «До» автоматически заполнится вчерашней датой. Установим переключатель «Рассчитывать по» на вариант «Количеству продаж».

Я получил вот такой результат:

Для увеличения нажмите на изображение.

«Но как получились такие цифры?», – спросите Вы.
Давайте рассчитаем их вручную.
Итак, по номенклатуре «Шкаф» у меня имеются следующие продажи:

• Январь – 4 шт.
• Февраль – 7 шт.
• Март – 3 шт.

Итого 14 шкафов. Всего в году 12 месяцев.

Рассчитаем среднемесячный показатель: 14 / 12 = 1,167.

Коэффициент сезонности рассчитывается по формуле:

Фактические продажи / Среднемесячные продажи

Наш результат:

• Январь: 4 / 1,167 = 3,429.
• Февраль: 7 / 1,167 = 6.
• Март: 3 / 1,167 = 2,571.

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

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

Для увеличения нажмите на изображение.

Запишем рассчитанные сезонные коэффициенты.

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

Для увеличения нажмите на изображение.

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

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

P.S. Это последняя статья из серии статей, посвященных обзору нового релиза УТ 11.1.4.1. До новых встреч!

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

Почему же так долго у них получалось жить без планирования? Всему виной, на мой взгляд, бурная докризисная экономика с изобилием кредитных ресурсов. В любой момент большинство компаний без проблем могло покрыть нехватку денежных средств с помощью банков. Однако времена изменились, и приходится «сводить концы с концами». Для этого и необходимо планирование. Как мы можем рассчитать ликвидность компании на определенный период, не планируя продажи, закупки, расходы, поступление выручки и т.п.? Без планирования – никак.

Итак, первый важный шаг в бюджете торговой компании – это планирование продаж . Хотя составлением плана продаж является задачей торгового отдела, я приверженец того, чтобы планы продаж составлялись двумя отделами: отделом продаж и отделом продукт-менеджмента (закупок), если такой имеется. Для чего так все усложнять? Это хорошая перепроверка плановых результатов, которые рассчитываются разными методами.

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

Так или иначе, ниже предоставляю к вашему рассмотрению практическую методику расчета плана продаж:

1. Расчет коэффициентов сезонности по прошлым периодам продаж;

2. Расчет плановых показателей продаж на основе:

• коэффициентов сезонности;
• тенденции роста (падения);
• внутреннего плана развития предприятия;
• экспертная корректировка рассчитанных плановых показателей.

Расчет коэффициентов сезонности по прошлым периодам продаж

Коэффициенты сезонности определяются по формуле:

k сезонности = Si / sS

где Si – фактическая сумма продаж определенного месяца,

sS – среднемесячная сумма продаж за год.

В таблице №1 приведен практический пример расчета.

Таблица 1. Расчет коэффициентов сезонности

Как видно из таблицы №1, для расчета коэффициента сезонности были взяты фактические ежемесячные продажи за три года. В конце таблицы для каждого года были рассчитаны среднемесячные продажи по формуле MS Excel: СРЗНАЧ(). Затем в нижнем блоке таблицы были рассчитаны коэффициенты сезонности для каждого месяца в году. Возьмем, например, расчет коэффициента для первого месяца 2005 года, который был найден следующим образом: 18 500 / 30 725 = 0,60.

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

Расчет плановых показателей продаж

На основе: коэффициентов сезонности, тенденции роста (падения), внутреннего плана развития предприятия.

Тенденция роста (падения) экономики

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

Внутренний план развития предприятия

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

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

Таблица 2. Расчет ежемесячных сумм плановых продаж

Сначала рассчитываем общую плановую сумму продаж на новый период (строка «Продажи (план) – 2008 год» в таблице №2). Коэффициент роста продаж на 2008 год руководство компании определило на уровне 1,2 – 20% прироста к фактическим продажам прошедшего года. Так как фактические продажи за 2007 год составили 495 545 у.е., то плановый уровень на 2008 год составит: 551 454 у.е. (495 545 у.е. + 20%).

Затем в строке «k тенденции» определяем коэффициенты для каждого месяца. В указанном примере (таблица 2) видно, что во втором квартале мы ожидаем прирост деловой активности в стране на 5% (коэффициент 1,05), а в третьем и четвертом кварталах – на 10%.

В строке «k сезонности» мы вставляем рассчитанные ранее (таблица 1) усредненные коэффициенты сезонности.

Чтобы определить суммы плановых продаж, необходимо произвести следующий расчет для каждого месяца: среднемесячную сумму плановых продаж (551 454 у.е. / 12 мес. = 45 955 у.е.) умножить на k сезонности и на k тенденции.

Экспертная корректировка рассчитанных плановых показателей

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

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

Для чего планировать продажи? Грамотно составленный план продаж позволяет:

• Предотвратить потери прибыли, связанные с отсутствием на складе товаров;
• Минимизировать убытки от «замораживания» средств компании в неликвидных и избыточных запасах товаров и сырья;
• Планировать производство продукции, потребность в сырье и материалах;
• Планировать финансовые потоки, повысить эффективность использования финансовых ресурсов;
• Повысить рентабельность бизнеса.

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

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

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

Для анализа мы взяли данные продаж за период 2011-2014 гг. в разрезе месяцев (таблица 1.). Все расчеты проводятся с изпользованием MS Excel.

Таблица 1.

Данные по продажам компании в 2011-2014 гг.

Анализ тренда

На первом этапе прогнозирования необходимо определить тренд - тенденцию роста или спада продаж. Для начала рассчитаем продаж компании в рассматриваемом периоде.

Таблица.2

Темпы роста продаж в 2011-2014 гг.

Как видно из таблицы 2., в анализируемом периоде темпы роста продаж замедляются. Если в 2012 году оборот вырос по сравнению с 2011 годом в два раза, то в 2014 году темп роста по отношению к предыдущему году составил 159%.

Рисунок 1. Тенденция роста продаж компании в 2011-2015гг.

Как видно на рисунке 1., темп роста можно спрогнозировать по линии тренда. Мы видим, что в 2015 году темп роста продаж составит около 147%. Чтобы определить продажи 2015 года, умножим оборот 2014 года на прогнозируемый темп роста в 2015 году:

56 781 115 * 1,47 = 83 468 239

При анализе 239 у.е. можно пренебречь. Таким образом, в 2015 году оборот компании составит около 83 468 000 у.е.

Сезонность продаж

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

Таблица 3.

Расчет коэффициентов сезонности

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

• Отсутствие на складе товаров, сырья, материалов;
• Простои на производстве;
• Транспортная и складская логистика;
• Проведение маркетинговых акций;
• Изменения в ценовой политике;
• Изменения в ассортименте продукции;
• Конкуренция и т.д.

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

Распределение прогнозируемого оборота

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

Таблица 4.

Прогноз продаж компании в 2015 году

Таким образом, оборот компании в 2015 году составит около 83 468 000 у.е. При этом пики продаж прогнозируются в 5-м и 7-м месяцах - 9 276 629 у.е. и 9 798 156 у.е. соответственно.

Точность прогноза

Точность — самая важная характеристика прогноза. Прогноз является точным, если отклонение фактического показателя от прогнозируемого составляет не больше 5%. Допустимым считается отклонение в пределах 10%.

На точность прогноза влияют:

• Период времени, на который разрабатывается прогноз. Например, среднесрочный прогноз (на 1 год) будет более точным, чем краткосрочный (месяц, квартал).
• Детализация прогноза — прогноз по компании в целом будет точнее, чем по отдельным областям, филиалам, отделам. Продажи по направлениям прогнозировать легче, чем по отдельным категориям и товарным позициям.
• Данные, которые используются при составлении прогноза. На работу компании влияют факторы, которые могут искажать статистику продаж. Это так называемые «шумы». Чтобы сгладить влияние шумов на точность прогноза, при анализе используют данные минимум за три года.

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

Таблица 5.

Расчет коэффициентов вариации для сезонности

Из таблицы 5. видно, что наибольшее отклонение фактического коэффициента сезонности от среднего будет в 12-м месяце - 20,58%. Давайте рассчитаем погрешность прогноза.

Для начала умножим коэффициент вариации на средний коэффициент сезонности для 12-го месяца:

(0,80 * 20,58%) / 100% = 0,164

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

(83 468 000 / 12) * 0,164 = 1 140 729 у.е.

Относительно прогноза продаж на 12-й месяц это составит:

(1 140 729 / 5 586 612) * 100% = 20,4%

Таким образом, наибольшая погрешность прогноза будет в 12-м месяце. Она будет находиться в пределах 1 140 729 у.е. или 20,4%.

Сезонность

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

Показатель сезонности представляет собой отношение уровня заболеваемости каждого месяца к количеству случаев заболевания за год (годы) и выражается в процентах:

С = М/r * 100, где

С - сезонность;

М - количество случаев заболевания за определённый месяц;

r - общее количество случаев заболевания за взятые годы;

100 - пересчёт на проценты.

Коэффициент сезонности

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

К остальным месяцам относятся те, в которые заболеваемость была равна или ниже среднемесячной. Среднемесячная определяется путём деления суммы случаев заболевания за год на 12 в абсолютных числах или процентах. Чем выше коэффициент сезонности, тем отчетливее выражена сезонность болезни. Сезонность выражена слабо при показателях коэффициента сезонности 51 - 60%, в средней степени - при коэффициенте сезонности от 61 до 75% и четко выраженная сезонность при Кс более 75%.

Среднемес. = =

Коэффициент сезонности :

Коэффициент сезонности равен 59,86%, что входит в пределы 51 - 60%, что говорит о слабой выраженности сезонности. Трихофития плотоядных встречается в любое время года, но больше число заболевших собак и кошек отмечается в летний период.

Индекс сезонности

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

Ис = Зсез / З м сез, где

Ис - индекс сезонности;

Зсез - заболеваемость в периоды сезонного подъёма;

З м сез - средняя межсезонная заболеваемость.

В месяцы сезонного подъёма зарегистрировано в 1,5 раза больше случаев заболевания трихофитией, чем за остальные месяцы.

Таблица 9. Динамика заболеваемости собак и кошек трихофитией плотоядных по данным клиники ИП Зубкова (г.Благовещенск) в 2007-2011гг.

	У инфицированных животных по годам	Всего за 5 лет	Сезонность, %	Сезонность,%
					Количество заболевших животных, гол.	В % к общему количеству заболевших животных
Сентябрь

Построение диаграммы сезонности

R - радиус в диаграмме соответствует среднему значению, т.е.:

563/12 = 46,92 голов

R окружности = 46,92 / 563 * 100 = 8,3%

Масштаб 1:2 R=4,15%

Рис. 39.

Вывод: Исходя из произведённых расчетов, графика сезонности и данных таблицы 9, я могу сделать вывод, что сезонность заболевания животных трихофитией плотоядных в ветеринарной клинике ИП Зубкова выражена слабо (Кс = 59,86%); заболевание в 1,5 раза чаще проявляется в тёплое время года в месяцы сезонного подъема (с мая по сентябрь), чем в остальное время.