Анализ продаж и прибыли с примером в excel

Контроль за выполнением плана по объему реализации

Контроль продаж – еще одна задача анализа реализации товара.

Он предполагает проверку:

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

На основе этих данных принимается одно из следующих решений:

Принять корректирующие меры для устранения отклонений;
Изменить нормативные (плановые) показатели;
Ничего не менять.

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

XYZ анализ ассортимента продукции. Формула расчета

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

На первом этапе осуществляется расчет коэффициента вариации объема продаж (уровня спроса) по каждой товарной группе. Изменчивость продаж оценивается с помощью коэффициента вариации. Формула расчета коэффициента вариации представляет собой отношение стандартного отклонения к математическому ожиданию объема продаж. Формула имеет следующий вид:

где:

σ – стандартное отклонение объема продаж;

x_i – объем продаж в период в i-й период;

n – количество рассматриваемых периодов продаж товаров;

х* – среднеарифметическое значение продаж товаров.

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

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

Группа	Коэффициент вариации	Характеристика класса
Группа «X	0<V<10%	Товары с наиболее устойчивыми объемами продаж
Группа «Y	10%<V<25%	Товары с прогнозируемыми, но изменчивыми объемами продаж
Группа «Z	V>25%	Товары, обладающие случайным спросом

Влияние маркетинга на ассортиментную политику

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

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

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

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

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

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

Формирование товарного ассортимента не терпит ошибок стратегического характера.

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

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

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

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

Пример анализа ассортимента продукции предприятия

В таблице 1 представлены данные об ассортименте продукции военного назначения

Таблица 1 – Ассортимент продукции военного назначения

Таблица «Анализ ассортимента» в формате .docСкачать

В 2019 году уровень загрузки производственной мощности составил 49,7%. Общество планирует увеличить загрузку проектных мощностей, в связи с увеличением объемов производства продукции, востребованной на рынке и освоением в серийном производстве новых изделий. Наибольший объем выручки от реализации продукции военного назначения приходится на 30 мм патроны с БТ снарядом, а так же на 30 мм патроны с ОТ снарядом.

Таблица 2 — Распределение по группам на основе АВС-анализа

Наименование товара (услуги)	Выручка, тыс.руб.	Доля в обороте, %	Доля с накопительным итогом, %	Группы
Продукция военного назначения	1011234	96,31	96,31	А
Услуги (аренда, утилизация боеприпасов)	22667	2,16	98,47	В
Продукция гражданского назначения	16121	1,53	100	С
Всего	105022	100	100	—

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

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

Пример XYZ анализа объемов продаж продукции в Excel

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

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

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – «X»,»Y» или «Z». Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группа товара =ЕСЛИ(H5<10%;»X»;ЕСЛИ(H5<25%;»Y»;»Z»))

Группировка продукции по классам XYZ в Excel

Чтобы было наглядно видно структуру устойчивости продаж необходимо провести группировку по классам: главное меню Excel → «Данные» → «Сортировка».

XYZ анализ продаж продукции в Excel. Пример расчета

В результате мы получим следующую классификацию товаров. Телефоны «Lenovo» имеют самый устойчивый спрос, так как коэффициент вариации составляет всего 8%. То есть объем продаж в среднем по месяцам отклоняется на 8%

Компании следует уделить особое внимание складским запасам данных товаров. В группу «Y» попали довольно много различных видов продукции, компании следует оперативно отслеживать складские запасы

Товары группы «Z» продаются не регулярно, их запасы могут быть сокращены и фирма может работать по предзаказу. XYZ анализ хорошо себя зарекомендовал на практике управления запасами и ассортиментом товаров, также его часто объединяют с ABC анализом, который позволяет провести двухкритериальную группировку товаров. Более подробно о ABC анализе вы можете узнать в моей статье: «ABC анализ продаж. Пример расчета в Excel«.

Резюме

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Методы анализа ассортимента

В современной экономической литературе существует восемь известных методов анализа ассортимента: «ABC; XYZ; FSN/FNS/FMR; RFM; VED/VEN; QRS; HML; SDE».

Классический АВС-анализ позволяет распределить (проранжировать) все товарные позиции/категории/группы по значимости на три класса по какому- либо критерию. Это могут быть: «объем продаж; скорость оборота, выраженная коэффициентом оборачиваемости, коэффициентом использования или коэффициентом длительности оборота; рентабельность различного типа; цены за единицу товара; издержкоемкость и другие критерии».

XYZ-анализ – «математически-статистический метод, который позволяет проанализировать и спрогнозировать стабильность продаж отдельных видов товаров и колебания уровня потребления тех или иных товаров».

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

FSN/FNS/FMR-анализ «используется в розничной торговле для выявления ассортиментных позиций, наименее запрашиваемых и покупаемых потребителями, позиционирования этих позиций в ассортиментной матрице розничного предприятия».

RFM-анализ разбивает потребителей компании по их лояльности к какому — либо товару. Recency (R) – давность последнего заказа – рассчитывается как разность, выраженная в днях, между текущей датой и датой последнего заказа. Frequency (F) – общее количество заказов, сделанных клиентом за всю историю наблюдения за клиентом. Monetary (M) – общее количество денег, потраченных клиентом за всю историю.

VEN/VED-анализ чаще всего используется в специализированных розничных предприятиях (например, аптеки) или в узкоспециализированных промышленных предприятиях. Основополагающим критерием данного метода является значимость товаров, которые подразделяются на жизненно важные, необходимые и второстепенные. «Метод позволяет оценить рациональность (разумность) расходования финансовых средств».

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

HML-анализ основан на соотношении цена/вес товара за штуку. «Данный метод похож на АВС-анализ, только критерием выступает «цена/вес», а не значение. Элементы анализа делятся на три группы: высокий, средний и низкий показатель». Управление определяет частоту отсечки линий или цены/веса на три категории. Этот анализ помогает сохранить контроль над потреблением в соответствии с ценой/весом, оценить затраты на хранение и обеспечить определенные требования для продажи товаров.

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

Структурный ABC-анализ продаж

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

Проводя ABC-анализ, ассортимент фирмы можно разделить на 3 группы:

Группа “A” – товары, приносящие больше всего прибыли;
Группа “B” – позиции, не имеющие значимой ценности для компании, но приносящие достаточную прибыль;
Группа “C” – продукты, которые приносят либо низкий доход, либо не приносят его вообще.

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

Числовые показатели каждой группы приведены в таблице.

	По прибыли (в общем объеме прибыли, %)	По доли товарной категории в продуктовом портфеле, (%)
Группа “A”	80	20
Группа “B”	15	30
Группа “C”	5	50

Данные в таблице приведены с применением закона Парето, согласно которому 80 % прибыли компании обеспечивает 20 % товаров.

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

Этапы ABC-анализа:

Формулируем объект анализа: продукция, поставщики, покупатели, каналы сбыта и прочее.
Определяем KPI, по которым будет рассматриваться объект. Это может быть выручка от конкретного товара и ее процент в общем объеме продукции предприятия, закупки, поставки и т.д. В качестве параметра оценки можно использовать объем продаж, рентабельность или другие значения в зависимости от целей исследования.
Собираем данные по каждому KPI, обозначаем долю каждого объекта, рассчитываем показатель нарастающим итогом и ранжируем объекты.
Заполняем группы, подводим итоги.

Только до 12.12

Как за 3 часа разбираться в IT лучше, чем 90% новичков и выйти надоход в 200 000 ₽?

Приглашаем вас на бесплатный онлайн-интенсив «Путь в IT»! За несколько часов эксперты
GeekBrains разберутся, как устроена сфера информационных технологий, как в нее попасть и
развиваться.

Интенсив «Путь в IT» поможет:

За 3 часа разбираться в IT лучше, чем 90% новичков.
Понять, что действительно ждет IT-индустрию в ближайшие 10 лет.
Узнать как по шагам c нуля выйти на доход в 200 000 ₽ в IT.

При регистрации вы получите в подарок:

«Колесо компетенций»

Тест, в котором вы оцениваете свои качества и узнаете, какая профессия в IT подходит именно вам

«Критические ошибки, которые могут разрушить карьеру»

Собрали 7 типичных ошибок, четвертую должен знать каждый!

Тест «Есть ли у вас синдром самозванца?»

Мини-тест из 11 вопросов поможет вам увидеть своего внутреннего критика

Хотите сделать первый шаг и погрузиться в мир информационных технологий? Регистрируйтесь и
смотрите интенсив:

Только до 12 декабря

Получить подборку бесплатно
pdf 4,8mb
doc 688kb

Осталось 17 мест

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

Пристального изучения требуют позиции, попавшие в группу “А”

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

Разберем ABC-анализ в действии на примере торговых позиций ресторана быстрого питания «Макдоналдс».

Номенклатурные группы или наименования продукции	Объем продаж, млн. штук	Удельный вес в объёме продаж, нарастающим итогом, %	Объём прибыли, млн. рублей	Выручка нарастающим итогом	Группа
Сандвичи	5,184	20	522,08	24,8	А
Картофель	3,856	35	306,216	39,4	А
Соусы	3,791	49	305,216	53,9	А
Напитки	3,452	62	236,16	65,2	А
Десерты	3,279	75	229,53	76,1	В
Наборы	2,532	84	221,76	86,6	В
Молочные коктейли	2,356	93	200,26	96,2	С
Салаты	1,722	100	80,564	100	С

Пример иллюстрирует, что больше всего прибыли приносят сэндвичи, картофель, соусы и напитки – это группа “А”. Логично, что производство этих товаров нужно развивать и расширять ассортимент.

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

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

Стратегии ассортиментной политики предприятия

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

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

. Стратегия увеличения границ рынка (экстенсивный метод).

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

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

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

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

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

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

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