Описание таблицы Excel
«Анализ ассортимента, клиентов, поставщиков»

версия 2.1 от 25 июля 2021

Предназначена для выполнения ABC-анализа ассортимента, клиенов, поставщиков.

Не зависит от национального законодательства.

Таблица предназначена для:

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

Кроме того, методом ABC-анализа можно делать не только анализ ассортимента, а анализ по любому числовому показателю и анализировать:

Об авторе

В. Прохоров
Меня зовут Владимир Прохоров (ИП Прохоров В.В., ОГРНИП 311645410900040), я занимаюсь экономическими расчетами более 20 лет. С 1992 по 2008 год работал программистом и начальником отдела IT в банках и коммерческих организациях. Имею большой опыт в разработке программного обеспечения для банковских систем и бухгалтерии. С 2008 года разрабатываю Excel-таблицы экономическиой и управленческой тематики.
Со мной можно связаться по почте v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru), по телефону +7 927 055 9473, ВКонтакте vk.com/vvprokhorov

Таблица выполняет

По результатам ABC-анализа можно:

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

ABC-группы. Например, взяли список товаров, разбили на 3 группы: A — самые прибыльные, приносящие львиную долю дохода, B — средние, C — мелкие. Теперь можно дополнительно вычислить средние показатели по группам и отклонения от средних. Посмотреть какие товары тянут свою группу вверх, а какие тянут вниз.

Произвольные группы. Можно сделать разбиение на произвольные группы по основному показателю. ABC-анализ делит весь список на 3 большие группы. Таблица позволяет дополнительно делить на группы по вашему усмотрению. Например, по сумме выручки до 50'000 руб., от 50'000 до 100'000, от 100'000 до 200'000, от 200'000 до 300'000 и т.д. Также можно вычислять средние показатели и определять лидеров и отстающих.

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

Посмотрите подробнее в Демо-версии

Как получить таблицу

Что делает таблица:

  1. ABC-анализ по любому числовому показателю. Иными словами – можно делать анализ ассортимента, клиентов, поставщиков, дебиторов и т.п.
  2. Границы можно задавать. Для ABC-анализа рекомендуют границы 80%-15%-5% по объему и 20%-30%-50% по количеству. Может быть, Вам лучше подойдут немного другие.
  3. Одновременно можно задавать еще два дополнительных показателя. Они не влияют на результат ABC-анализа, но могут нести важную информацию.

    Примеры:

    • При анализе ассортимента основной показатель сумма ежемесячных продаж, дополнительный показатель: средний остаток на складе.
    • При анализе клиентов основной показатель сумма ежемесячного заказа, дополнительный показатель: дебиторская задолженность клиента. Таким образом, можно быстро сравнивать «выгодность» клиента и его «дисциплинированность» в платежах.
  4. Делает разбиение на произвольные группы по основному показателю (до 50'000 руб., от 50'000 до 100'000, от 100'000 до 200'000, от 200'000 до 300'000 и т.д.).
  5. Считает количество элементов списка (товаров, клиентов и т.п.) в каждой ABC-группе. Если количество отличается от рекомендуемых 20%-30%-50%, оно выделяется цветом.
  6. Считает суммарное и среднее значение основного показателя по каждой ABC-группе.
  7. Считает суммарные и средние значения дополнительных показателей по каждой ABC-группе.
  8. Считает среднее значение основного показателя по всему списку.
  9. Считает медиану основного показателя по всему списку. Медиана очень полезная характеристика, поясню на примере что это такое. Пусть мы анализируем список товаров в зависимости от месячной выручки. Если медиана значений выручки равна 100 000 руб., значит половина позиций списка имеет выручку больше 100 000 каждая, а половина меньше 100 000. Иными словами, медиана делит упорядоченный по сумме выручки список товаров точно пополам.
  10. Считает отклонение основного показателя от среднего.Полезно знать какие позиции (товары, клиенты) тянут вас вверх, а какие вниз.
  11. Строит график основного показателя.
  12. Строит график отклонения основного показателя от среднего.
  13. Считает средние значения дополнительных показателей по всему списку.
  14. Считает медианы дополнительных показателей по всему списку.
  15. Считает отклонение дополнительных показателей от среднего.
  16. Строит графики дополнительных показателей.
  17. Строит графики отклонения дополнительных показателя от среднего.
  18. Считает количество элементов списка в каждой группе.
  19. Считает суммарные и средние значения основного показателя по группам.
  20. Считает суммарные и средние значения двух дополнительных показателей по группам.
  21. Строит графики указанных выше величин по группам.

Подробное описание таблицы

В исходном состоянии таблица содержит данные, которые могут служить примером и помогают понять логику работы таблицы. В качестве примера делается анализ ассортиметна по показателю выручка. Одновременно анализируются еще 2 показателя — прибыль и средняя сумма заказа поставщику. Посмотрите подробнее в Демо-версии

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

Листы с 8 по 12 аналогичны листам с 3 по 7 и предназначены для анализа по другому основному показателю или для анализа другого списка, например, клиентов.

Листы с 13 по 17 аналогичны листам с 3 по 7 и предназначены для анализа по другому основному показателю или для анализа другого списка, например, поставщиков.

Далее полностью показаны листы с 3 по 7.

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

Лист №3 «Суммарно»

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

Наименования показателей и единицы измерения

ABC - анализ ассортимента. 
Показатели

Введенные здесь наименования будут подставлены в заголовки столбцов и надписи на графиках. Основной показатель, по которому будет делаться ABC-анализ это выручка. Первый дополнительный показатель — прибыль, второй — средняя сумма заказа поставщику этого товара.

Суммарные значения показателей

ABC - анализ ассортимента. Суммарные значения показателей

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

Результаты ABC-анализа. Суммарные и средние значения по ABC-группам.

Здесь задаем доли ABC-групп по объему основного показателя (80%-15%-5%). На основе этих долей делается ABC-анализ списка товаров.

Для контроля задаем рекомендуемые доли ABC-групп по количеству наименований (20%-30%-50%). В реальной жизни попадание никогда не будет точным, поэтому задаем допустимое отклонение. То, что выходит за рамки выделяется красным.

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

ABC - анализ ассортимента. Суммарные значения по ABC-группам

Количество, суммарные и средние значения показателей по ABC-группам.

Здесь вводим только доли ABC-групп. Все остальное рассчитывает таблица по списку товаров с листа «Данные»

Разбиение на группы. Суммарные и средние значения по группам.

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

Например, по каким товарам выручка менее 500 тыс.руб. в месяц, по каким от 500 до 1000 и т.д. Таблица вычисляет суммарную выручку по группе, среднюю выручку и медиану. Одновременно вычисляются суммы и средние по дополнительным показателям.

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

ABC - Анализ ассортимента. Разбиение на группы. Суммарные значения

Результаты расчетов показаны на графиках на листах «Суммарно ABC Граф» и «Суммарно Граф». Эти листы показаны ниже.

Посмотрите подробнее в Демо-версии

Как получить таблицу

Лист №4 «Данные»

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

Суммарные значения показателей

В верхней части листа рассчитываются суммарные показатели. Они, естественно, совпадают с суммарными показателями с листа "Суммарно".

ABC - Анализ ассортимента. Суммарные значения показателей

Список наименований для ABC-анализа. В нашем случае — ассортимент товаров.

Здесь задаем список анализируемых товаров. Для каждого товара задаем:

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

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

ABC - Анализ ассортимента. Список товаров

Таблица определяет:

  1. Долю выручки по каждому товару в общей сумме выручки. По этой доле выполняется ABC-анализ.
  2. ABC-группу товара (cтолбец "Группа ABC") и соответствующий цвет. A — зеленый, B — синий, C — желтый.
  3. Группу товара по величине основного показателя. Столбец "Группа". По этому значению выполняется суммирование на листе "Суммарно".
  4. Для основного показателя (выручки): среднее, медиану и отклонение от среднего по каждому товару.
  5. Для дополнительных показателей (прибыли и заказа): среднее, медиану и отклонение от среднего по каждому товару.

Результаты расчетов показаны на графиках на листе «Данные Граф». Лист показан ниже.

Посмотрите подробнее в Демо-версии

Как получить таблицу

Лист №5 «Суммарно ABC Граф»

Этот лист содержит диаграммы суммарных показателей по ABC-группам.

ABC - Анализ ассортимента. Графики Суммарные значения показателей

На графиках показаны (слева направо и сверху вниз):

  1. Количество и объем по ABC-группам в долях (процентах) от общей суммы. Количество, выручка, прибыль, заказ.
  2. Количество наименований товаров по ABC-группам
  3. Выручка, суммарно и среднее по ABC-группам.
  4. Прибыль, суммарно и среднее по ABC-группам.
  5. Заказ, суммарно и среднее по ABC-группам.

Посмотрите подробнее в Демо-версии

Как получить таблицу

Лист №6 «Суммарно Граф»

Этот лист содержит диаграммы суммарных показателей по группам.

ABC - Анализ ассортимента. Графики Суммарные значения показателей по группам

На графиках показаны (слева направо и сверху вниз):

  1. Количество и объем по группам в долях (процентах) от общей суммы. Количество, выручка, прибыль, заказ.
  2. Количество наименований товаров по группам
  3. Выручка, суммарно и среднее по группам.
  4. Прибыль, суммарно и среднее по группам.
  5. Заказ, суммарно и среднее по группам.

Посмотрите подробнее в Демо-версии

Как получить таблицу

Лист №7 «Данные Граф»

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

ABC - Анализ ассортимента. Диаграммы показателей по товарам

На графиках показаны (слева направо и сверху вниз):

  1. Доля (%) выручки по каждому товару в общей сумме. Красная линия доля товара, синяя — доля нарастающим итогом.
  2. Абсолютное значение выручки по каждому товару. Синяя линия значение выручки, красная — среднее значение, зеленая — медиана.
  3. Абсолютное значение прибыли по каждому товару. Синяя линия значение прибыли, красная — среднее значение, зеленая — медиана.
  4. Абсолютное значение заказа по каждому товару. Синяя линия значение заказа, красная — среднее значение, зеленая — медиана.
  5. Отклонение выручки от среднего по каждому товару. Положительное значение — выручка превышает среднее значение по всему ассортименту, отрицательное — выручка меньше среднего значения.
  6. Отклонение прибыли от среднего по каждому товару. Положительное значение — прибыль превышает среднее значение по всему ассортименту, отрицательное — прибыль меньше среднего значения.
  7. Отклонение заказа от среднего по каждому товару. Положительное значение — заказ превышает среднее значение по всему ассортименту, отрицательное — заказ меньше среднего значения.



Как получить таблицу

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

Как получить таблицу

Таблица работает в Excel для Windows. В ОpenOffice не работает.Сейчас я переделываю таблицы для работы под Mac. Чтобы получать сообщения о выходе новых версий подпишитесь на рассылку внизу страницы. Вариант для Windows нельзя запускать на Mac, т.к. таблица будет испорчена.

Я гарантирую:

При выходе новой версии:


Таблица
«Анализ ассортимента, клиентов, поставщиков»
Стоимость: 1395 руб. (или в рассрочку 4 платежа по 497.5 руб.)

Таблица работает в Excel для Windows. В ОpenOffice не работает.Сейчас я переделываю таблицы для работы под Mac. Чтобы получать сообщения о выходе новых версий подпишитесь на рассылку внизу страницы. Вариант для Windows нельзя запускать на Mac, т.к. таблица будет испорчена.

Оформить заказ

Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)

Оформление заказа, оплата и получение таблиц

  1. Нажать зеленую кнопку, соответствующую нужному варианты таблиц (если вариантов несколько).
  2. В новой вкладке откроется страница оформления заказа.
  3. Ввести код скидки (если он есть). Периодически проводятся акции скидок, поэтому советую подписаться на рассылку. Форма подписки внизу страницы.
  4. Ввести адрес своей электронной почты. Нажать кнопку [Оформить заказ]
  5. Откроется страница выбора способа оплаты. Формально способов оплаты три:
    • Robokassa.
    • Юр. лицам через банк. При оформлении заказа получите счет.
    • Физ. лицам через банк. При оформлении заказа получите квитанцию по форме ПД-4.

    Фактически, способов оплаты два: Robokassa и банк. Подробнее о способах оплаты см. ниже.

  6. Выбрать удобный способ и оплатить.

Способы оплаты:

  1. Robokassa. Сервис приема платежей в интернете. Можно оплачивать используя компьютер (телефон) или через терминалы приема платежей и салоны связи. Возможна оплата в рассрочку без переплаты четырьмя равными частями. Этот вид оплаты называется «Подели». Вы получаете таблицу сразу после первой оплаты. Вторая и последующие оплаты будут списаны каждая через 14 дней, таким образом, общий срок рассрочки составляет 42 дня.
    • Карты Visa, MasterCard, Maestro, Мир. В том числе карты иностранных банков.
    • Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
    • Электронные деньги ЮMoney (Яндекс.Деньги), QIWI, WebMoney, Wallet One (W1), Элекснет
    • Терминалы
    • Салоны связи Евросеть, Связной

    Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

  2. Банк. Можно оплачивать с расчетного счета организации, через Сбербанк Онлайн, сервисы интернет-клиент других банков или через кассу любого банка.
    На расчетный счет 40802810556000029171
    Индивидуальный предприниматель Прохоров Владимир Викторович,
    ИНН 645400330452,
    КПП 0  (у ИП нет реквизита КПП, если требуется ввести — вводите 0),
    ПОВОЛЖСКИЙ БАНК ПАО СБЕРБАНК,
    кор.сч. 30101810200000000607,
    БИК 043601607.
    Обратите внимание, что указание формы собственнсти (Индивидуальный предприниматель) является обязательным.

    После оформления заказа можно распечатать счет для юр. лиц
    или квитанцию по форме ПД-4 для физ. лиц.

    Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).

    В назначении платежа обязательно указывайте номер счета, например:
    По счету № ...  За электронные таблицы «Анализ ассортимента, клиентов, поставщиков». Без НДС.

    При платежах не из России в начале назначения платежа укажите код валютной операции {VO10100}
    Например, назначение платежа:
    {VO10100} По счету № ...  За электронные таблицы «Анализ ассортимента, клиентов, поставщиков». Без НДС.
    Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.

Доставка:

Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Придет письмо с темой «Вы оплатили счет №…». Если его долго нет, проверьте папку СПАМ.

Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты. Сервис Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег от нескольких часов до 2 рабочих дней, обычно, в пределах рабочего дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.

Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.

Дальнейшая переписка по поводу полученных таблиц

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

Оформить заказ

Владимир Прохоров, 25 июля 2021

P.S.
Если вы хотите получить рабочую версию одной таблицы и демо-версии всех остальных моих таблиц, подпишитесь на ознакомительную серию из 9 писем с кратким описанием таблиц

Вы получаете:
  1. Рабочую версию таблицы «Учет доходов и расходов». Ссылка на скачивание в первом письме.
  2. Демо-версии всех моих таблиц: Оценка стоимости бизнеса, Платежный календарь, Себестоимость, Инвестиции, Фин.анализ МСФО, Фин.анализ РСБУ, Точка безубыточности, Анализ ассортимента, Диаграмма Ганта, Личное планирование. Ссылка на скачивание в первом письме.
  3. Скидку 40% (4706 руб.) на комплект из пяти моих таблиц. Как получить скидку — во втором письме.
  4. Скидку порядка 20% на отдельные таблицы. Как получить скидку — в письме с описанием конкретной таблицы.
  5. Скидку 20% на новые таблицы. Срок действия скидки трое суток с момента отправки письма о новой таблице.
  6. Сообщения о новых таблицах.
  7. Сообщения о новых версиях таблиц.
  8. Сообщения об акциях скидок. Обычно срок действия скидки трое суток с момента отправки письма о скидке.
  9. Сообщения об исправленных вариантах при обнаружении ошибок.

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

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

Не рекомендую подписываться на корпоративные почтовые ящики, т.к. есть вероятность что почтовый сервер посчитает рассылку спамом.

Вводите адрес способом копирования/вставки. При ручном вводе велика вероятность ошибки. Я это вижу по адресам подписчиков ***@gmai.com, ***@gmail.ru, ***@mall.ru и подобным. Сколько опечаток делают в той части адреса, которая стоит до @, трудно представить.

Если письмо с подтверждением подписки не пришло в течение нескольких минут — адрес с ошибкой или письмо попало в спам.


Введите свой E-mail и нажмите на кнопку
Потом проверьте свой почтовый ящик и папку СПАМ

Нажимая на кнопку «Получить …» вы даете согласие
на обработку персональных данных

Посмотрите полный список таблиц

Финансовый анализ. Главная страница