Выводим рекомендации по изменению меню для кафе на основе 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.

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

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

Примечание

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

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

Материал опубликован пользователем.
Нажмите кнопку «Написать», чтобы поделиться мнением или рассказать о своём проекте.

Написать
{ "author_name": "Павел Павлов", "author_type": "self", "tags": [], "comments": 3, "likes": 4, "favorites": 63, "is_advertisement": false, "subsite_label": "life", "id": 101741, "is_wide": false, "is_ugc": true, "date": "Sat, 18 Jan 2020 15:28:59 +0300", "is_special": false }
0
{ "id": 101741, "author_id": 200997, "diff_limit": 1000, "urls": {"diff":"\/comments\/101741\/get","add":"\/comments\/101741\/add","edit":"\/comments\/edit","remove":"\/admin\/comments\/remove","pin":"\/admin\/comments\/pin","get4edit":"\/comments\/get4edit","complain":"\/comments\/complain","load_more":"\/comments\/loading\/101741"}, "attach_limit": 2, "max_comment_text_length": 5000, "subsite_id": 199123, "last_count_and_date": null }
3 комментария
Популярные
По порядку
0

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

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

Ответить
0

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

Ответить
0

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

Ответить
{ "page_type": "article" }

Прямой эфир

[ { "id": 1, "label": "100%×150_Branding_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox_method": "createAdaptive", "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfl" } } }, { "id": 2, "label": "1200х400", "provider": "adfox", "adaptive": [ "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfn" } } }, { "id": 3, "label": "240х200 _ТГБ_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fizc" } } }, { "id": 4, "label": "Article Branding", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "cfovx", "p2": "glug" } } }, { "id": 5, "label": "300x500_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfk" } } }, { "id": 6, "label": "1180х250_Interpool_баннер над комментариями_Desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "h", "ps": "bugf", "p2": "ffyh" } } }, { "id": 7, "label": "Article Footer 100%_desktop_mobile", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjxb" } } }, { "id": 8, "label": "Fullscreen Desktop", "provider": "adfox", "adaptive": [ "desktop", "tablet" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjoh" } } }, { "id": 9, "label": "Fullscreen Mobile", "provider": "adfox", "adaptive": [ "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjog" } } }, { "id": 10, "disable": true, "label": "Native Partner Desktop", "provider": "adfox", "adaptive": [ "desktop", "tablet" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "clmf", "p2": "fmyb" } } }, { "id": 11, "disable": true, "label": "Native Partner Mobile", "provider": "adfox", "adaptive": [ "phone" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "clmf", "p2": "fmyc" } } }, { "id": 12, "label": "Кнопка в шапке", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "bscsh", "p2": "fdhx" } } }, { "id": 13, "label": "DM InPage Video PartnerCode", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox_method": "createAdaptive", "adfox": { "ownerId": 228129, "params": { "pp": "h", "ps": "bugf", "p2": "flvn" } } }, { "id": 14, "label": "Yandex context video banner", "provider": "yandex", "yandex": { "block_id": "VI-223676-0", "render_to": "inpage_VI-223676-0-1104503429", "adfox_url": "//ads.adfox.ru/228129/getCode?pp=h&ps=bugf&p2=fpjw&puid1=&puid2=&puid3=&puid4=&puid8=&puid9=&puid10=&puid21=&puid22=&puid31=&puid32=&puid33=&fmt=1&dl={REFERER}&pr=" } }, { "id": 15, "label": "Баннер в ленте на главной", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "p1": "byudx", "p2": "ftjf" } } }, { "id": 16, "label": "Кнопка в шапке мобайл", "provider": "adfox", "adaptive": [ "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "p1": "byzqf", "p2": "ftwx" } } }, { "id": 17, "label": "Stratum Desktop", "provider": "adfox", "adaptive": [ "desktop" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fzvb" } } }, { "id": 18, "label": "Stratum Mobile", "provider": "adfox", "adaptive": [ "tablet", "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fzvc" } } }, { "id": 19, "disable": true, "label": "Тизер на главной", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "p1": "cbltd", "p2": "gazs" } } }, { "id": 20, "label": "Кнопка в сайдбаре", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "cgxmr", "p2": "gnwc" } } } ] { "page_type": "default" }