Выводим рекомендации по изменению меню для кафе на основе ABС анализа в Google Таблицах
В свое время мне бы очень пригодился подобный шаблон)
Вот таблицы которые мы будем использовать:
Пример марочного отчета.
В большинстве сервисов учета есть функция ABC анализа, но мне хотелось сделать систему с конкретными рекомендациями, что нужно делать по каждому блюду и инструкцией как это сделать.
Толку мне от ваших ABC анализов, скажите конкретно, что нужно делать!
Для вывода рекомендаций и построению ABC анализа, нам нужно получить 4 значения
- Кол-во продаж блюда
- Сумма прибыли блюда = Сумма(Отпускная) — Сумма(Закупочная)
- Маржа с блюда = Цена(Отпускная) - Ср.Цена(Закупочная)
- Кост = Ср.Цена(Закупочная) / Цена(Отпускная)*100
Для получения этих значений нам понадобиться марочный отчет.
Наш план:
- Выгружаем марочный отчет из StoureHoues
- Конвертируем файл отчета из XLS в формат XLSX
- Подготавливаем наш марочный отчет для шаблона ABC таблицы
- Выделяем нужные блюда и настраиваем под себя параметры 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.
В колонке O отображается рекомендация к блюду в виде инструкций к действиям, основываясь на первых 3 переменных, колонки G(Кол-во проданных блюд) / H(Cумма прибыли) / I(Маржа с 1 блюда).
На листе Справочник находится описание к рекомендаций, в идеале можно было бы еще учитывать кост и сделать более точную рекомендацию, для этого нужно прописать еще варианты с учетом 4 буквы(+кост). Как я это делал можно посмотреть на листе Исходник. Можно еще много чем дополнить эту таблицу, если у Вас есть замечания или интересные кейсы, пишите мне в личку =)
Примечание
Что бы принять окончательно решению по блюду, по мимо ABC анализа, Вам нужно знать когда было введено блюдо его стратегию продвижения, ценовую политику конкурентов.
Классный телеграмм чат про Google таблицы
t.me/google_spreadsheets_chat
Лучший телеграмм канал о Google таблицах с кучей классных кейсов
t.me/google_sheets
было б неплохо, если был пересчет при фильтрации
например, отобрал ты только чаи, и нужно видеть анализ среди них
Эта функция есть, ставите галочки на тех блюдах которые вам нужны и все пересчитывается
Первый скриншот... Правильное решение, вывести позиции "Staff" (8, 10 и 11 строки). Это же как раз поднимет лояльность, уронет себес и вообще поможет HR-бренду общепита стать лучше. Да и вообще, зачем разбираться, почему персональский кофе делает 25% всех продаж по количеству.