Считать выручку по продуктам через СУММПРОИЗВ, доставать цену из прайса по названию (ВПР с точным поиском, ИНДЕКС+ПОИСКПОЗ) и находить точку безубыточности, запас прочности и прибыль при плане.
Разобранный пример: эспрессо «Зерна»
Разобранный пример: эспрессо «Зерна»
Прайс кофейни «Зерно» — в таблице ниже: цена, сколько продаётся за обычный день и переменные затраты на единицу. Постоянные затраты — 340 000 руб. в месяц (аренда, зарплаты, прочее). Разберём одну строку — эспрессо.
Выручка эспрессо за день: =B2*C2 → 150 × 30 = 4 500 руб. Выручку всех строк одной формулой даёт =СУММПРОИЗВ(цены; количества): функция перемножает пары по строкам и складывает. Маржа на чашку эспрессо: =B2-D2 → 150 − 40 = 110 руб. Если бы постоянные затраты покрывал один эспрессо, точка безубыточности была бы 340 000 / 110 ≈ 3 091 чашка: дробную чашку округляют вверх.
Цену по названию товара достают поиском: =ВПР(название; таблица; номер столбца; 0) или =ИНДЕКС(столбец цен; ПОИСКПОЗ(название; столбец названий; 0)). Ноль (ЛОЖЬ) — точное совпадение.
Дальше считаем по капучино — главной позиции. Упрощение: каждая чашка — капучино. Оно оптимистично: у капучино маржа самая большая. В настоящей модели берут среднюю маржу по смеси продаж: СУММПРОИЗВ(маржи; количества) / СУММ(количества).
• Точка безубыточности, чашек = постоянные затраты / маржа на чашку.
• В деньгах = чашки × цена (то же — постоянные / доля маржи).
• Запас прочности = (план − точка) / план.
• Прибыль = план × маржа на чашку − постоянные.
Прайс «Зерна», обычный день
| Товар | Цена, руб. | Продано за день | Перем. затраты на ед., руб. |
|---|---|---|---|
| Эспрессо | 150 | 30 | 40 |
| Капучино | 250 | 90 | 80 |
| Круассан | 200 | 20 | 90 |
Конспект урока
Разобранный пример: эспрессо «Зерна»
Прайс кофейни «Зерно» — в таблице ниже: цена, сколько продаётся за обычный день и переменные затраты на единицу. Постоянные затраты — 340 000 руб. в месяц (аренда, зарплаты, прочее). Разберём одну строку — эспрессо.
Выручка эспрессо за день: =B2*C2 → 150 × 30 = 4 500 руб. Выручку всех строк одной формулой даёт =СУММПРОИЗВ(цены; количества): функция перемножает пары по строкам и складывает. Маржа на чашку эспрессо: =B2-D2 → 150 − 40 = 110 руб. Если бы постоянные затраты покрывал один эспрессо, точка безубыточности была бы 340 000 / 110 ≈ 3 091 чашка: дробную чашку округляют вверх.
Цену по названию товара достают поиском: =ВПР(название; таблица; номер столбца; 0) или =ИНДЕКС(столбец цен; ПОИСКПОЗ(название; столбец названий; 0)). Ноль (ЛОЖЬ) — точное совпадение.
Дальше считаем по капучино — главной позиции. Упрощение: каждая чашка — капучино. Оно оптимистично: у капучино маржа самая большая. В настоящей модели берут среднюю маржу по смеси продаж: СУММПРОИЗВ(маржи; количества) / СУММ(количества).
• Точка безубыточности, чашек = постоянные затраты / маржа на чашку.
• В деньгах = чашки × цена (то же — постоянные / доля маржи).
• Запас прочности = (план − точка) / план.
• Прибыль = план × маржа на чашку − постоянные.