Выводим рекомендации по изменению меню для кафе на основе ABС анализа в Google Таблицах

В свое время мне бы очень пригодился подобный шаблон)

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

Что у нас получится​
Что у нас получится​

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

Толку мне от ваших ABC анализов, скажите конкретно, что нужно делать!

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

  1. Кол-во продаж блюда
  2. Сумма прибыли блюда = Сумма(Отпускная) — Сумма(Закупочная)
  3. Маржа с блюда = Цена(Отпускная) - Ср.Цена(Закупочная)
  4. Кост = Ср.Цена(Закупочная) / Цена(Отпускная)*100

Для получения этих значений нам понадобиться марочный отчет.

Наш план:

  1. Выгружаем марочный отчет из StoureHoues
  2. Конвертируем файл отчета из XLS в формат XLSX
  3. Подготавливаем наш марочный отчет для шаблона ABC таблицы
  4. Выделяем нужные блюда и настраиваем под себя параметры cost и ABC

Подготовка

1) Выгружаем марочный отчет

Мы пользуемся StoureHoues, и будем выгружать из него. Краткая инструкция, заходим в StoureHoues => Отчеты => Отчеты по реализации => Акт реализации => Список отчетов => Марочный отчет (Полный) => Скачать => Excel table (XML)

2) Конвертируем файл марочного отчета из XLS в формат XLSX

Так как Google таблицы не отрывают формат XLS, нам нужно конвертировать наш марочный отчет в формат XLSX, это можно сделать тут
https://onlineconvertfree.com/ru/convert-format/xls-to-xlsx/

3) Подготавливаем наш марочный отчет и копируем в шаблон таблицы ABC анализа

Загружаем сконвертированный марочный отчет на Google диск и открываем файл через Google таблицу. Пример марочного отчета.

  • Удаляем не нужные нам столбцы оставляем только: Наименование | Ед. изм. | Кол-во | Отпуск. цена (Отпускные) | Сумма в/н (Отпускные) | Ср. цена (Закупочные) | Сумма в/н (Закупочные)
  • Копируем диапазон из марочного отчета и переносим его в таблицу ABC анализа.

Смотрим что получилось

Готово, теперь мы просто выбираем нужные нам блюда, которые мы хотим проанализировать нажимая галочки на листе "Марочник", в колонке A

Эти блюда появятся на листе "ABC" с сортировкой по Кол-ву проданных блюд.

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

Как считается

Первые 3 переменные ABC_: Кол-во проданных блюд, сумма прибыли, Маржа с 1 блюда, рассчитывается через "Совокупный %".

"Совокупный %" похож на снежный ком, то есть мы сортируем значения выбранного товара и считаем накопительно с верхних строк, от большего к меньшему (в %) и катим наш снежный ком, прибавляя одно значение к следующему пока не дойдем до 50% это будет категория "A", далее от 50% до 80% у нас B, и все что больше 80% С. Пороги 50 / 30 / 20 можно поменять.

Пример расчета "Совокупного %" можно посмотреть на листе ABC, в колонках E и F). Изменить значения ABC(50 / 30 / 20) можно на листе "Справочник" в ячейках D3:E3 .

Кост(Себестоимость) ABC мы рассчитываем по фактическому значению коста блюда, если кост блюда менее 35(%) это категория А, если от 35 до 40 категория B, все что выше C. Параметры можно задать на листе "Справочник", в ячейках D5:E6 Границы коста Вам лучше настроить под себя, так как у Вас может быть другое представление какой Кост у вас категории A какой B, C.

Вывод рекомендаций к блюдам

В колонке L мы определяем группу блюда по методу "Menu engineering»", (Звезды, Рабочие лошадки, Загадки, Собаки), описание что означает каждая группа находится на листе "Справочник", на основании значений в этой группе мы будем давать рекомендации к блюдам, которые у нас отображаются в колонке N

Хорошая статья о методе "Menu engineering" и АВС-анализе.

В колонке M определяем группу блюда по методу Дэвида Павесика (Prime, Standarts, Sleepers, Problems), описание что означает каждая группа находится там же на листе "Справочник".

В колонке N отображается ссылка на подробную рекомендацию, что нам делать с этим блюдом, на основании значения из колонки L.

Выводим рекомендации по изменению меню для кафе на основе ABС анализа в Google Таблицах

В колонке O отображается рекомендация к блюду в виде инструкций к действиям, основываясь на первых 3 переменных, колонки G(Кол-во проданных блюд) / H(Cумма прибыли) / I(Маржа с 1 блюда).

На листе Справочник находится описание к рекомендаций, в идеале можно было бы еще учитывать кост и сделать более точную рекомендацию, для этого нужно прописать еще варианты с учетом 4 буквы(+кост). Как я это делал можно посмотреть на листе Исходник. Можно еще много чем дополнить эту таблицу, если у Вас есть замечания или интересные кейсы, пишите мне в личку =)

Примечание

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

Лучший телеграмм канал о Google таблицах с кучей классных кейсов
t.me/google_sheets

55
3 комментария

было б неплохо, если был пересчет при фильтрации

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

Ответить

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

Ответить

Первый скриншот... Правильное решение, вывести позиции "Staff" (8, 10 и 11 строки). Это же как раз поднимет лояльность, уронет себес и вообще поможет HR-бренду общепита стать лучше. Да и вообще, зачем разбираться, почему персональский кофе делает 25% всех продаж по количеству.

Ответить