Аналіз асортименту, клієнтів, постачальників в Excel: ABC-аналіз, груповий аналіз, аналіз за середнім, аналіз по медіані.

1. Потужна і наочна таблиця аналізу по будь-якого показника Плюс порівняльний аналіз ще двох показників...
2. Детальний опис таблиці
3. Лист №3 «Сумарно»
4. Найменування показників і одиниці вимірювання
5. Сумарні значення показників
6. Результати ABC-аналізу. Сумарні і середні значення по ABC-групам.
7. Розбиття на групи. Сумарні і середні значення по групах.
8. Лист №4 «Дані»
9. Сумарні значення показників
10. Список найменувань для ABC-аналізу. У нашому випадку - асортимент товарів.
11. Лист №5 «Сумарно ABC Граф»
12. Лист №6 «Сумарно Граф»
13. Лист №7 «Дані Граф»

версія 2.1 від 25 липня 2018

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

виконує

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

ABC-аналіз дозволяє рішуче спростити роботу з великим списком, наприклад асортиментом товарів. Це досягається розбивкою довгого списку всього на три групи, які мають суттєво різний вплив на загальний результат (обсяг продажів).

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

• Клієнтів за сумами закупівель, кількості закуповуваних найменувань, періодичності закупівель.
• Постачальників за сумами поставок, кількості поставляються найменувань, термінів поставки.
• Дебіторів за сумами заборгованості або днях прострочення.
• і т.п.

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

• Виділити позиції, які роблять найбільший внесок в сумарний результат.
• Аналізувати три групи замість великого списку.
• Працювати подібним чином з позиціями однієї групи.

Мінус цього методу - великий обсяг утомливих розрахунків. Але завдяки тому, що основна маса розрахунків однотипна, ми все знаємо кращий інструмент для ABC-аналізу, це Excel.

Мене звуть Володимир Прохоров (ІП Прохоров В.В., ІН 311645410900040), я займаюся економічними розрахунками понад 20 років. З 1992 по 2008 рік працював програмістом і начальником відділу IT в банках і комерційних організаціях. Маю великий досвід в розробці програмного забезпечення для банківських систем і бухгалтерії. З 2008 року розробляю Excel-таблиці для економічних розрахунків.
Зі мною можна зв'язатися по пошті [email protected] ([email protected]), по телефону +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-груп за обсягом основного показника (80% -15% -5%). На основі цих часток робиться ABC-аналіз списку товарів.

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

Вводимо дані тільки в осередку з блакитним фоном, все інше розраховує таблиця.

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

Тут вводимо тільки частки ABC-груп. Все інше розраховує таблиця за списком товарів з листа «Дані»

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

Тут задаємо кордону груп за обсягом основного показника. Таблиця підраховує скільки товарів зі списку потрапляє за обсягом виручки в кожну групу.

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

Вводимо дані тільки в осередку з блакитним фоном, все інше розраховує таблиця.

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

Подивіться докладніше в Демо-версії таблиці

Лист №4 «Дані»

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

Сумарні значення показників

У верхній частині листа розраховуються сумарні показники. Вони, природно, збігаються з сумарними показниками з листа "Сумарно".

Список найменувань для ABC-аналізу. У нашому випадку - асортимент товарів.

Тут задаємо список аналізованих товарів. Для кожного товару задаємо:

• Найменування
• Скорочене найменування для графіків
• Значення основного показника (виручки)
• Значення першого додаткового показника (прибутку)
• Значення другого додаткового показника (замовлення)

На підставі часткою і кордонів груп, заданих на аркуші "Сумарно", таблиця визначає в які групи потрапляє товар. Для правильної роботи таблиці список повинен бути відсортований за основним показником. Це робиться кнопкою "Сортувати".

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

Таблиця визначає:

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

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

Подивіться докладніше в Демо-версії таблиці

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

Цей лист містить діаграми сумарних показників по ABC-групам.

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

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

Подивіться докладніше в Демо-версії таблиці

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

Цей лист містить діаграми сумарних показників по групам.

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

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

Подивіться докладніше в Демо-версії таблиці

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

Цей лист містить результат аналізу асортименту, діаграми показників по товарах.

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

1. Частка (%) виручки по кожному товару в загальній сумі. Червона лінія частка товару, синя - частка наростаючим підсумком.
2. Абсолютне значення виручки по кожному товару. Синя лінія значення виручки, червона - середнє значення, зелена - медіана.
3. Абсолютне значення прибутку по кожному товару. Синя лінія значення прибутку, червона - середнє значення, зелена - медіана.
4. Абсолютне значення замовлення по кожному товару. Синя лінія значення замовлення, червона - середнє значення, зелена - медіана.
5. Відхилення виручки від середнього по кожному товару. Позитивне значення - виручка перевищує середнє значення по всьому асортименту, негативне - виручка менше середнього значення.
6. Відхилення прибутку від середнього по кожному товару. Позитивне значення - прибуток перевищує середнє значення по всьому асортименту, негативне - прибуток менше середнього значення.
7. Відхилення замовлення від середнього по кожному товару. Позитивне значення - замовлення перевищує середнє значення по всьому асортименту, негативне - замовлення менше середнього значення.

Таблиці працюють на Excel для Windows (2007, 2010, 2013, 2016). На ОpenOffice не працюють. В налаштуваннях Windows повинен бути встановлений мову системи (мова програм не підтримують юнікод) - російський. Зараз я переробляю таблиці для інших мов і для роботи під Mac. Щоб отримувати повідомлення про вихід нових версій підпишіться на розсилку внизу сторінки.

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

Якщо ви вважаєте, що це дорого, подивіться які варіанти у вас є:

1. Зробити таку таблицю самому. Подумайте, скільки це займе часу і зумієте ви.
2. Замовити розробку. Прикиньте, скільки це буде коштувати, скільки займе часу. І скільки особисто вашого часу ви витратите на пояснення програмісту того, що вам треба.
3. Купити готову таблицю у мене за 1 395 руб. і швидко її отримати. Швидкість отримання залежить від способу оплати. Можна укластися в 15 хвилин.

Перейти до способів оплати >>>

Я знаю про що багато хто подумав: добре б дізнатися думку покупців цієї таблиці.

Відгуки, питання, побажання і інша листування з приводу таблиць в гостьовій книзі сайту »»

Я гарантую:

• Доставка протягом декількох годин після приходу платежу. Терміни проходження платежу для різних способів оплати див. Нижче.
• Всі таблиці заповнені даними, які служать прикладом і допомагають зрозуміти логіку роботи.
• Таблиці забезпечені докладними поясненнями щодо заповнення та роботі.
• Відповідаю на будь-які питання по роботі з таблицями. Звичайно, передбачається що ви знайомі з предметною областю і маєте базові навички роботи з Excel.

При виході нової версії:

• Вартість поновлення залежить від того, наскільки збільшується функціональність нової версії таблиці.
• Повідомлення про нові версії отримують тільки передплатники розсилки. Форма підписки внизу сторінки.

Якщо Вам потрібні ці таблиці, раджу замовити їх прямо зараз, тому що ціна періодично підвищується, в березні 2012 року вони коштували всього 495 руб.

Таблиця «Аналіз асортименту, клієнтів, постачальників»
Вартість: 1395 руб.

Таблиці працюють на Excel для Windows (2007, 2010, 2013, 2016). На ОpenOffice не працюють. В налаштуваннях Windows повинен бути встановлений мову системи (мова програм не підтримують юнікод) - російський. Зараз я переробляю таблиці для інших мов і для роботи під Mac. Щоб отримувати повідомлення про вихід нових версій підпишіться на розсилку внизу сторінки.

Телефон для запитань оплати і доставки +7 927 055 9473
пошта [email protected] ([email protected])

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

1. Robokassa
   • Картки Visa, MasterCard, Maestro, Світ
   • Інтернет-клієнт Альфа-Банк, Російський Стандарт, Промсвязьбанк та ін.
   • термінали
   • Салони зв'язку Евросеть, зв'язковий
   • Електронні гроші Яндекс.Деньги, QIWI, WebMoney, Wallet One (W1), Елекснет
2. банк
   на розрахунковий рахунок Прохоров Володимир Вікторович (ІП), ІПН 645400330452,
   р / с 40802810711010053426, Філія «Бізнес» ПАТ «Совкомбанк» м.Москва,
   К / с 30101810045250000058, БІК 044525058.
   Зверніть увагу, що вказівка ​​форми собственнсті (ІП) є обов'язковим.
   Після оформлення замовлення можна роздрукувати рахунок для юр. осіб
   або квитанцію за формою ПД-4 для фіз. осіб.
   Також можна отримати рахунок, написавши на [email protected] ([email protected]).
   При платежах не з Росії в призначенні платежу вкажіть код валютної операції {VO10100}
   Наприклад, призначення платежу:
   {VO10100} За рахунку № ... За електронні таблиці «Аналіз асортименту, клієнтів, постачальників». Без НДС.
   Де VO - великі латинські літери, 10100 - цифри. Фігурні дужки обов'язкові. Прогалини всередині фігурних дужок не допускаються.
   

доставка:

Посилання на скачування таблиць буде відправлено на вказану в замовленні e-Mail після надходження грошей. Якщо листи довго немає, перевірте папку СПАМ.

Для способу 1 (Robokassa) лист з посиланням відправляється автоматично, відразу після надходження грошей. Звичайне час надходження грошей - хвилини.

Для способу 2 (Банк) лист з посиланням відправляється після надходження платежу на розрахунковий рахунок. Час надходження грошей 1-2 робочих дня. Юридичним особам та ВП висилаю pdf-копії накладної за формою Торг 12. Якщо потрібні ще якісь документи, прошу погодити це питання ДО ОПЛАТИ. Оригінали документів надсилаються на запит, в запиті вкажіть, будь ласка, поштову адресу.

Подальша листування з приводу отриманих таблиць

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

Володимир Прохоров 25 липня 2018

PS
Якщо ви хочете отримати 2 робочі таблиці і демо-версії всіх інших моїх таблиць, підпишіться на ознайомчу серію з 9 листів з коротким описом таблиць

1. Робочі версії двох моїх таблиць: «Облік доходів і витрат» і «Відсотки, ПДВ, депозити, кредити». Посилання на скачування в першому листі.
2. Демо-версії інших таблиць: Оцінка вартості бізнесу, Платіжний календар, Собівартість, Інвестиції, Фін.аналіз МСФЗ, Фін.аналіз РСБУ, Точка беззбитковості, Аналіз асортименту, Приватне планування. Посилання на скачування в першому листі.
3. Знижку 40% (5264 руб.) На комплект з шести моїх таблиць. Як отримати знижку - у другому листі.
4. Знижку близько 20% на окремі таблиці. Як отримати знижку - в листі з описом конкретної таблиці.
5. Знижку 20% на нові таблиці. Термін дії знижки три доби з моменту відправлення листа про нову таблиці.
6. Повідомлення про нові таблицях.
7. Повідомлення про нові версії таблиць.
8. Повідомлення про акції знижок. Зазвичай термін дії знижки три доби з моменту відправлення листа про знижку.
9. Повідомлення про виправлені варіантах при виявленні помилок.

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

Деякі листи розсилки будуть містити пропозиції з обмеженим терміном, тому рекомендую підписатися на ту адресу, яку ви регулярно переглядаєте.

Не рекомендую підписуватися на корпоративні поштові скриньки, тому що є ймовірність що поштовий сервер вважатиме розсилку спамом. Тобто не ви, а системний адміністратор буде вирішувати якісь листи ви можете отримувати.

Перегляньте повний перелік таблиць

Фінансовий аналіз. Головна сторінка