GrossMargin_Задание_06.xlsx

	B	C	D	E	F	H	I	J	K	L	M	N	O	P	Q	R	S	U	V
1
2	ЗАДАНИЕ 6 \| Часть 1 — P&L кофейни
3
4	Ваша задача — составить прогнозный P&L на один месяц для небольшой кофейни и затем «протянуть» его на 12 месяцев. Рассчитать все показатели вплоть до чистой прибыли, а также рассчитать показатели маржинальности. Структура есть в форме ниже. Вводные данные: Выручка: 35 000 руб. в день. В месяце 30 дней. Структура выручки: 65% — кофе, 25% — выпечка, 10% — зерна в пачках. Себестоимость (COGS) в % от выручки по категориям: кофе — 30%, выпечка — 50%, зерна в пачках — 70% Операционные расходы (OPEX): Переменные: комиссия за эквайринг — 2% от всей выручки. Постоянные (в месяц): аренда помещения — 120 000 руб., зарплата (ФОТ с налогами и взносами) — 280 000 руб., коммунальные платежи, интернет, вывоз мусора — 35 000 руб., маркетинг — 15 000 руб. Активы и долги: Стоимость оборудования для запуска кофейни: 1 200 000 руб., срок полезной службы 5 лет, амортизация линейная. Кредит на открытие: 1 000 000 руб. под 22% годовых, ежемесячные проценты, 360 дней в году, погашения основного долга пока не предусмотрено. Налоги: УСН "Доходы минус расходы", ставка 15%.									Подсказки, как строить P&L: 1. Выручка: нужно рассчитать целевую выручку за месяц на основании имеющихся данных (30 дней * Выручка в день) и поместить это значение в Параметр Итоговой выручки. Затем сделать ссылку на этот Параметр для итоговой строки выручки, а выручку категорий уже рассчитывать как % от итоговой выручки месяца. 2. Себестоимость: себестоимость категории будет равна соответствующему проценту от выручки категории. После расчета себестоимости для каждой категории, нужно просуммировать три значения в итоговой строке Себестоимости. 3. Переменные расходы: по аналогии с себестоимостью – поместить параметр в ячейку и через формулу связать значение в месяце с выручкой. 4. Постоянные расходы: нужно просто их просуммировать и взять эту сумму в качестве Параметра. 5. Амортизация: стоимость оборудования приведена для того, чтобы вы могли оценить ежемесячную амортизацию. Необходимо разделить сумму на срок полезного использования в месяцах, тогда вы получите ежемесячный расход на амортизацию. 6. Процента: Рассчитать ежемесячный расход по процентам можно по простой формуле: Сумма долга * Годовая ставка / 12. 7. Налоги: рассчитать EBT и взять 15% от этого показателя. Вычесть налоги из EBT и получить чистую прибыль.
5
6	GrossMargin \| Финансовая модель кофейни
7
8
9	1	P&L
10
11		Показатель	Ед. изм.	Параметр	Параметр	1	2	3	4	5	6	7	8	9	10	11	12	Проверка
12																		Результат	Формула
13		Выручка
14		Кофейные напитки	т.р.
15		Выпечка и десерты	т.р.
16		Зерна в пачках	т.р.
17		Итого выручка	т.р.
18
19		Себестоимость
20		Кофейные напитки	т.р.
21		Выпечка и десерты	т.р.
22		Зерна в пачках	т.р.
23		Итого себестоимость	т.р.
24
25		Валовая прибыль	т.р.
26		Валовая маржинальность	%
27
28		OPEX
29		Переменные	т.р.
30		Постоянные	т.р.
31		Итого OPEX	т.р.
32
33		EBITDA	т.р.
34		EBITDA margin	%
35
36		Амортизация	т.р.
37		EBIT	т.р.
38
39		Проценты	т.р.
40		EBT	т.р.
41
42		Налоги	т.р.
43		Чистая прибыль	т.р.
44		Рентабельность	%
45
46
47		Проверка результата за месяц
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100