Abc Анализ в Excel пример скачать

      Комментарии к записи Abc Анализ в Excel пример скачать отключены

Уважаемый гость, на данной странице Вам доступен материал по теме: Abc Анализ в Excel пример скачать. Скачивание возможно на компьютер и телефон через торрент, а также сервер загрузок по ссылке ниже. Рекомендуем также другие статьи из категории «Бланки».

Abc Анализ в Excel пример скачать.rar
Закачек 2809
Средняя скорость 6329 Kb/s

Abc Анализ в Excel пример скачать

Поиск на сайте

ABC-анализ и XYZ-анализ в Excel часто используются вместе, так как используют одни и те же исходные данные. Рассмотрим практический пример совместного использования двух видов анализа.

Торговая компания внедряет систему цен для покупателей, предусматривающую следующие категории цен (по увеличению скидки): базовую, оптовую, крупнооптовую, специальную.

Все покупатели должны быть ранжированы по двум показателям: объёму продаж и стабильности продаж.

По объёму продаж вводятся следующие категории:

— категория А — лучшие 20% покупателей по объёму;

— категория В — последующие 50%,

— категория С — оставшиеся 30%.

По стабильности продаж:

— категория X — лучшие по стабильности (коэффициент вариации от 0% до 10%)

— категория Y — средние по стабильности (от 10% до 25%)

— категория Z — худшие по стабильности (более 25%).

Обе категории будут использованы для определения ценовой категории следующим образом:

— Специальную цену могут получить только покупатели категории AX.

— Крупнооптовую цену могут получить покупатели категорий АY и BX.

— Оптовую цену могут получить покупатели категорий AZ и BY.

— Все остальные покупатели получают базовую категорию цен.

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

Скачайте и откройте файл с практическим примером. ABC-XYZ analiz Excel prostoi primer

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

На листе Данные и расчёты расположены исходные данные по продажам за период и колонки расчётов.

ABC-анализ в Excel

  • В ячейках столбца «Ранг в продажах» вычисляется процентный ранг покупателя, для этого используется функция ПРОЦЕНТРАНГ (Подробнее о функции ПРОЦЕНТРАНГ читайте в соответствующей статье учебника Excel). Данная функция вычисляет процентный ранг и вычитает его из единицы, соответственно формула в ячейках имеет вид «=1-ПРОЦЕНТРАНГ($N$2:$N$37;N2)» (обратите внимание на абсолютную ссылку в формуле).
  • В ячейках столбца Код АВС происходит присваивание поставщику категории по объёму продаж. Формула имеет вид «=ВПР(O2;диапазоны_авс;2)». Функция ВПР берёт численные значения процентного ранга из столбца «Ранг в продажах» и присваивает соответствующую категорию А, В или С, сравнивая ранг с пороговыми значениями в таблице «Диапазоны весов в продажах» листа «Категории».

XYZ-анализ в Excel

  • Вычисляется коэффициент вариации с помощью функций СТАНДОТКЛОНП и СРЗНАЧ. =СТАНДОТКЛОНП(B2:M2)/СРЗНАЧ(B2:M2).
  • В ячейках столбца Код вариации происходит присваивание поставщику категории по стабильности продаж. Функция ВПР сравнивает код вариации и с пороговыми значениями в таблице «Диапазоны коэффициента вариации» листа «Категории».

Объединение результатов АВС-анализа и XYZ-анализа

В столбце «Общий код» с помощью текстовой функции СЦЕП�?ТЬ объединяются соответствующие коды.

Ссылки на учебник Excel:

Статистические функции: СРЗНАЧ, СТАНДОТКЛОНП

Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).

В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.

ABC-анализ в Excel

Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.

Благодаря анализу ABC пользователь сможет:

  • выделить позиции, имеющие наибольший «вес» в суммарном результате;
  • анализировать группы позиций вместо огромного списка;
  • работать по одному алгоритму с позициями одной группы.

Значения в перечне после применения метода ABC распределяются в три группы:

  1. А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
  2. В – средние по важности (30% — 15%).
  3. С – наименее важные (50% — 5%).

Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.

Условия для применения ABC-анализа:

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

Для каких значений можно применять методику АВС-анализа:

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

Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.

Общая схема проведения:

  1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
  2. Выполнить сортировку параметров по убыванию.
  3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
  4. Найти долю каждого параметра в общей сумме.
  5. Посчитать долю нарастающим итогом для каждого значения списка.
  6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
  7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
  8. Для С – все, что ниже.
  9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
  10. Найти доли каждой категории в общем количестве.

АВС-анализ товарного ассортимента в Excel

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

  1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» — «По убыванию».
  2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
  3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
  4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
  5. Присваиваем позициям ту или иную группу. До 80% — в группу А. До 95% — В. Остальное – С.
  6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.

Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.

XYZ-анализ: пример расчета в Excel

Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.

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

Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…

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

  1. Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
  2. Сортировка товарного ассортимента по коэффициенту вариации.
  3. Классификация позиций по трем группам – X, Y или Z.

Критерии для классификации и характеристика групп:

  1. «Х» — 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
  2. «Y» — 10-25% — товары с изменчивым объемом продаж.
  3. «Z» — от 25% — товары, имеющие случайный спрос.

Составим учебную таблицу для проведения XYZ-анализа.

  1. Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
  2. Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3 <=10%;"X";ЕСЛИ(I3<=25%;"Y";"Z")).

В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.

Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.

Из статьи вы узнаете о бесплатной надстройке Excel для ABC- XYZ- анализа.

Эта надстройка поможет вам одним нажатием клавиши сделать ABC — анализ и XYZ — анализ.

Данная надстройка когда-то стала идеей для создания Forecast4AC PRO.

В статье мы расскажем:

  • О возможностях надстройки для ABC XYZ анализа;
  • Как с её помощью сделать ABC анализ;
  • Как сделать ABC XYZ анализ;
  • И где можно бесплатно скачать надстройку.

Скачать надстройку вы можете на странице сайта Клуб Закупщиков скачать

После скачивания открываем надстройку и нажимаем кнопку «Добавить A&Z анализ в меню». У вас появляется в меню Excel «Надстройки» кнопка «A-Z анализ».

Если кнопка не нажимается и меню не появляется, то включаем макросы. Как включить макросы в Excel вы можете прочитать в статье «Как включить макросы в Excel»

Меню добавили, рассмотрим возможности.

Начнем с настроек АВС анализа.

  1. Дополнительно к группам ABC можно добавить группы AA, D и E. Для этого ставим галочки для соответствующей группы.
  2. Задать границы для каждой из групп

Если в группе «AA» стоит 15%, в группе «А» 50%, в «В» 80%, «С» 95%, «D» 99%, то

В группу «AA» попадут позиции, которые делают значительную часть объема продаж (или другого анализируемого показателя) больше заданной границы, в нашем случае — больше 15% от общего объема. Если вы используете эту группу, то товары, которые в нее попадают, исключаются из ABCDE анализа, и анализ сделается по оставшимся товарам.

  • В группу «А» попадают позиции, которые делают 50% от общего объема продаж (или другого анализируемого показателя).
  • Группа «B» — позиции, которые по объему продаж (или другому показателю) делают от 50% до 80% от общего объема продаж .
  • Группа «C» — позиции, которые по объему продаж (или другому показателю) делают от 80% до 95% от общего объема продаж.
  • Группа «D» —от 95% до 99% от общего объема продаж.
  • Группа «E» —оставшийся 1% от общего объема продаж.

Теперь рассмотрим настройки для XYZ — анализа:

О применении XYZ — анализа в прогнозировании мы писали в статье «XYZ — анализ — коэффициент вариации — подготовка данных к прогнозу»

Перейдем к настройкам.

1. Как и в ABC анализе, есть возможность задать границы групп XYZ;

2. А также вместе ABC XYZ анализом, возможно вывести сигму, среднее, коэффициент вариации:

Перейдем к рассмотрению примеров расчетов анализов с помощью надстройки.

1. ABC – анализ.

Для этого данные должны иметь следующее представление (в нашем примере наименование товаров и объемы продаж за 2012 год в столбец, во вложенном файле лист «ABC анализ»):

Устанавливаем курсор в первую ячейку столбца и нажимаем на кнопку A-Z анализ:

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

2. ABC XYZ анализ.

Для этого данные должны иметь следующее представление (1 строка Excel — 1 временной ряд, количество рядов не ограничено, во вложенном файле — лист «ABC_XYZ анализ»):

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

Если данных много и мышкой обводить их неудобно, то для быстрого выделения можно воспользоваться сочетанием клавиш ctrl+shift+end.

Устанавливаем курсор в левую верхнюю ячейку с данными:

и нажимаем сочетание клавиш ctrl+shift+end, Excel автоматически выделит область, начиная с левой верхней ячейки и заканчивая правой нижней, как на картинке ниже:

Теперь нажимаем кнопку «A-Z анализ», и надстройка для каждого временного ряда делает XYZ — анализ, а также автоматически суммирует данные по каждому ряду и делает ABC — анализ.

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

  • в первом столбце — группы ABC,
  • во втором — группы XYZ,
  • в третьем (если стоит галочка) — сигму,
  • в 4 — среднюю,
  • 5 — коэффициент вариации.

Итак, одним нажатием клавиши, используя надстройку от Клуба Закупщиков, вы сможете сделать ABC- и XYZ — анализ.

Надстройку вы можете скачать с сайта Клуб Закупщиков скачать

После скачивания открываем надстройку и нажимаем кнопку «Добавить A&Z анализ в меню». У вас появляется в меню Excel «Надстройки» кнопка «A-Z анализ».

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.


Статьи по теме