Вам нужно сделать анализ Ассортимента?
Или клиентов?
Или поставщиков?
Посмотрите, возможно Вам подойдет Excel таблица

«Анализ ассортимента»

Мощная и наглядная таблица
анализа по любому показателю
Плюс сравнительный анализ еще двух показателей
Результаты показаны на 17 графиках

версия 2.0 от 23 октября 2015

Выполняет

  • ABC-анализ
  • Групповой анализ
  • Анализ по среднему
  • Анализ по медиане

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

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

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

Минус этого метода — большой объем утомительных расчетов. Но благодаря тому, что основная масса расчетов однотипна, мы все знаем лучший инструмент для ABC-анализа, это Excel.

В. Прохоров
Меня зовут Владимир Прохоров (ИП Прохоров В.В., ОГРНИП 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-анализа ассортимента, но в процессе работы понял:

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

Вот к чему я пришел:

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

Произвольные группы. Можно сделать разбиение на произвольные группы по основному показателю. ABC-анализ делит весь список на 3 большие группы. Но может оказаться полезным дополнительно делить на группы по Вашему усмотрению.

Например, знать какие товары продаются в месяц на сумму до 50'000 руб., какие на сумму от 50'000 до 100'000, какие от 100'000 до 200'000 и т.д. Опять-же можно вычислять средние показатели и определять лидеров и отстающих.

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

Получите Демо-версию таблицы

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

  1. ABC-анализ по любому числовому показателю. Иными словами – можно делать анализ ассортимента, покупателей, поставщиков, дебиторов и т.п.
  2. Границы можно задавать. Рекомендуемые границы 80%-15%-5% по объему и 20%-30%-50% по количеству не являются законом природы. Может быть, Вам лучше подойдут немного другие.
  3. Одновременно можно задавать еще два дополнительных показателя. Они не влияют на результат ABC-анализа, но могут нести важную информацию.
      Примеры:
    • При анализе ассортимента основной показатель сумма ежемесячных продаж, дополнительный показатель: средний остаток на складе.
    • При анализе клиентов основной показатель сумма ежемесячного заказа, дополнительные показатели: дебиторская задолженность клиента. Таким образом, можно быстро сравнивать «выгодность» клиента и его «дисциплинированность» в платежах. А главное — делать выводы и быстро принимать соответствующие решения.
  4. Делает разбиение на произвольные группы по основному показателю. ABC-анализ делит весь список на 3 группы. Но может оказаться полезным делить на группы по Вашему усмотрению. Например, знать какие товары продаются в месяц на сумму до 50 000 руб., какие на сумму от 50 000 до 100 000, какие от 100 000 до 200 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. Отклонение заказа от среднего по каждому товару. Положительное значение — заказ превышает среднее значение по всему ассортименту, отрицательное — заказ меньше среднего значения.

Таблицы работают на Excel для Windows (Excel 2003, 2007, 2010, 2013, 2016). Нужна поддержка макросов. На ОpenOffice не работают.

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

Если Вам нужен удобный инструмент для анализа ассортимента, клиентов или поставщиков, Вы можете получить эту Excel-таблицу «Анализ ассортимента», заплатив 1395 руб. одним из указанных ниже способов.

Если Вы считаете, что это дорого, посмотрите какие варианты у Вас есть:

  1. Сделать такую таблицу самому. Подумайте, сколько это займет времени и сумеете ли Вы.
  2. Заказать разработку. Прикиньте, сколько это будет стоить, сколько займет времени. И сколько лично Вашего времени Вы потратите на объяснение программисту того, что Вам надо.
  3. Купить готовую таблицу у меня за 1395 руб. и быстро ее получить. Быстрота получения зависит от способа оплаты. Можно уложиться в 15 минут.

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

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

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

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

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

Чтобы своевременно узнавать о выходе новых версий подпишитесь на рассылку. Форма подписки внизу страницы.

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

Таблицы работают на Excel для Windows (Excel 2003, 2007, 2010, 2013, 2016). Нужна поддержка макросов. На ОpenOffice не работают.

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

на расчетный счет, карту Сбербанка, Яндекс.Деньги, QIWI

После оплаты таблицы будут доставлены электронной почтой, подробности ниже в разделе Как получить после оплаты. Телефон для вопросов по оплате и получению таблиц +7 927 055 9473. Электронная почта v_v_prokhorov@mail.ru

Способы оплаты из России (не из России по этой ссылке)

Оплата на расчетный счет

Перевести 1395 руб. на расчетный счет. Деньги доходят за 1 - 2 рабочих дня. Обычно в течение дня.

Юридическим лицам и ИП высылаю pdf-копии счета и накладной по форме Торг 12. Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ. Оригиналы документов высылаются по запросу, в запросе укажите, пожалуйста, почтовый адрес.

Частным лицам — cейчас не нужно квитанций, просто дайте реквизиты операционисту.

Прохоров Владимир Викторович (ИП)
ИНН 645400330452
р/с 40802810711010053426
Филиал «Бизнес» ПАО «Совкомбанк» г. Москва
К/с 30101810045250000058
БИК 044525058
Назначение платежа: За электронные таблицы «Анализ ассортимента». Без НДС.

Обратите внимание, что указание формы собственности (ИП) в наименовани получателя является обязательным.
Распечатать реквизиты платежа

Другие способы оплаты

Деньги доходят за время от несколько минут до нескольких часов.

Терминалы, банкоматы, Сбербанк Онлайн, Альфа-клик, интернет-банки, пластиковые карты, эл.кошельки, платежные системы.

Распечатать реквизиты платежа

 

Способы оплаты не из России (из России по этой ссылке)

Оплата на расчетный счет

Перевести 1395 руб. на расчетный счет. Деньги доходят за время от 1 до 3 рабочих дней.

Прохоров Владимир Викторович (ИП)
ИНН 645400330452
р/с 40802810711010053426
Филиал «Бизнес» ПАО «Совкомбанк» г. Москва
К/с 30101810045250000058
БИК 044525058
Назначение платежа:
{VO10100} За электронные таблицы «Анализ ассортимента». Без НДС.

Где {VO10100} код валютной операции. VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.

Обратите внимание, что указание формы собственнсти (ИП) является обязательным.

Другие способы оплаты

Распечатать реквизиты платежа

.
Как получить после оплаты

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

При оплате на расчетный счет укажите В ТЕКСТЕ ПИСЬМА:

При оплате другими способами укажите В ТЕКСТЕ ПИСЬМА:

Не пишите номера и адреса терминалов, банкоматов, номера квитанций, номера карт, коды операций и т.п. Эта информация мне не передается.

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

Быстрота получения

После поступления денег я вышлю Вам таблицы в ответном письме. Обычные платежи на расчетный счет (вид платежа «ЭЛЕКТРОННО») доходят за время от 1 до 3 рабочих дней. Платежи на счета электронных денег доходят за минуты.

Если Вы хотите получить таблицы срочно:

С 9:00 до 21:00 московского времени таблицы будут высланы в срок от нескольких минут до нескольких часов после поступления денег и получения письма с реквизитами оплаты. При поступлении денег и писем с реквизитами ночью таблицы высылаются утром.

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

При использовании корпоративных почтовых ящиков

При использовании корпоративных почтовых ящиков указывайте запасной адрес на бесплатном почтовом сервере (mail.ru, gmail, yandex). Системные администраторы корпоративной почты часто блокируют почту с mail.ru и письма с прикрепленными файлами. Если корпоративный сервер сообщит, что отклонил письмо с таблицами как нежелательное, таблицы будут отправлены на запасной адрес.

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

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

Владимир Прохоров, 23 октября 2015

P.S.


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

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