GrossMargin_Задание_08_v2.xlsx

	B	C	D	E	H	I	J	K	L	M	N	O	P	Q	R	S	U	V	W
1
2	ЗАДАНИЕ 8 \| Часть 1 — BS кофейни
3
4	У вас есть ОФР и ДДС кофейни, которые вы построили в предыдущих тренажерах. Задача текущего тренажера — построить Баланс, а также скорректировать некоторые статьи ОФР и ДДС. Под CF подготовлен шаблон Balance Sheet, который необходимо наполнить вводными и формулами по шагам, как это описано в теоретическом материале, тем самым связав три формы отчетности и дополнив модель недостающей информацией. Дополнительные вводные, которые вам потребуются для наполнения Баланса: 1. Входящий остаток в Балансе: Основные средства — 1 200 000 руб., Кредиты и займы — 1 000 000 руб., Уставный капитал — 200 000 руб. Все остальные статьи — 0. 2. Срок оборачиваемости: Запасов — 21 день, ДЗ — 1 день, КЗ — 14 дней. Ваша задача №1 — смоделировать статьи Баланса: ОС, Запасы, ДЗ, Деньги, Кредиты, КЗ, Уставные капитал, Нераспределенную прибыль. После этого просуммировать Активы, Обязательства, Капитал. ОНА остается как в шаблоне, моделировать его не нужно. Для целей моделирования Запасов, ДЗ и КЗ считаем, что в месяце 30 дней. Ваша задача №2 — внести изменения в ОФР и ДДС: скорректировать начисление Процентов в ОФР, смоделировать Изменение ЧОК в ДДС. Внимание! Несмотря на то, что инвестиционный поток и часть финансового потока в ДДС пустые, необходимо смоделировать статьи Баланса таким образом, чтобы при появлении в ДДС новых данных не потребовалось изменения формул в Балансе, относящихся к этим данным.								Подсказки, как делать задание Обратите внимание, что до тех пор пока вы не реализовали все пункты ниже, некоторые проверки могут показывать Ошибку. 1. Внесите новые вводные в ячейки Баланса: в Параметры — сроки оборачиваемости, во Вх. ост. — денежные вводные из условий. 2. В первом месяце концом предыдущего периода выступает Вх. ост. 3. Для моделирования статьи Денежные средства используйте формулу, ссылающуюся на Остаток ДС на конец [соответствующего периода] в ДДС. 4. Для моделирования Основных средств используйте формулу: ОС [на конец периода] = ОС на конец предыдущего периода + Приобретение имущества [в текущем периоде] – Продажа имущества [в текущем периоде] – Амортизация [в текущем периоде]. 5. Запасы рассчитать по формуле: Запасы [на конец периода] = Итого себестоимость [в текущем периоде] * Срок оборачиваемости Запасов [21 день] / 30 [дней в периоде]. Используйте Параметры там, где это возможно. 6. ДЗ рассчитать по формуле: ДЗ [на конец периода] = Итого выручка [в текущем периоде] * Срок оборачиваемости ДЗ [1 день] / 30 [дней в периоде]. Используйте Параметры там, где это возможно. 7. Кредиты и займы рассчитать по формуле: Кредиты и займы [на конец периода] = Кредиты и займы на конец предыдущего периода + Получение кредитов [в текущем периоде] – Возврат кредитов [в текущем периоде]. 8. КЗ рассчитать по формуле: КЗ [на конец периода] = Итого OPEX [в текущем периоде] * Срок оборачиваемости КЗ [14 дней] / 30 [дней в периоде]. Используйте Параметры там, где это возможно. Внимание! В зависимости от вида бизнеса, КЗ может опираться на любые расходные статьи. В данном тренировочном случае расчет идет от OPEX для упрощения. 9. Уставный капитал рассчитать по формуле: Уставный капитал [на конец периода] = Уставный капитал на конец предыдущего периода + Привлечение капитала [в текущем периоде]. 10. Нераспределенную прибыль рассчитать по формуле: Нераспределенная прибыль [на конец периода] = Нераспределенная прибыль на конец предыдущего периода + Чистая прибыль [в текущем периоде] – Выплата дивидендов [в текущем периоде]. 11. Просуммировать Активы, Обязательства и Капитал. 12. Проценты в ОФР перерассчитать по формуле: Проценты = Ставка / 12 [месяцев] * Среднее значение Кредитов и займов на конец предыдущего периода и конец текущего периода. Используйте формулу =СРЗНАЧ(;) или =AVERAGE(;). 13. Изменение ЧОК в ДДС рассчитать по формуле: Изменение ЧОК = ЧОК на конец текущего периода – ЧОК на конец предыдущего периода. ЧОК = Запасы + ДЗ – КЗ.
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36	GrossMargin \| Финансовая модель кофейни
37
38
39	1	P&L
40
41		Показатель	Ед. изм.	Параметр	1	2	3	4	5	6	7	8	9	10	11	12	Проверка
42																	Результат	Формула	Пусто
43		Выручка
44		Кофейные напитки	т.р.	65%	683	683	683	683	683	683	683	683	683	683	683	683	OK	OK	-
45		Выпечка и десерты	т.р.	25%	263	263	263	263	263	263	263	263	263	263	263	263	OK	OK	-
46		Зерна в пачках	т.р.	10%	105	105	105	105	105	105	105	105	105	105	105	105	OK	OK	-
47		Итого выручка	т.р.	1050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	1,050	OK	OK	-
48
49		Себестоимость
50		Кофейные напитки	т.р.	30%	205	205	205	205	205	205	205	205	205	205	205	205	OK	OK	-
51		Выпечка и десерты	т.р.	50%	131	131	131	131	131	131	131	131	131	131	131	131	OK	OK	-
52		Зерна в пачках	т.р.	70%	74	74	74	74	74	74	74	74	74	74	74	74	OK	OK	-
53		Итого себестоимость	т.р.		410	410	410	410	410	410	410	410	410	410	410	410	OK	OK	-
54
55		Валовая прибыль	т.р.		641	641	641	641	641	641	641	641	641	641	641	641	OK	OK	-
56		Валовая маржинальность	%		61%	61%	61%	61%	61%	61%	61%	61%	61%	61%	61%	61%	OK	OK	-
57
58		OPEX
59		Переменные	т.р.	2%	21	21	21	21	21	21	21	21	21	21	21	21	OK	OK	-
60		Постоянные	т.р.	450	450	450	450	450	450	450	450	450	450	450	450	450	OK	OK	-
61		Итого OPEX	т.р.		471	471	471	471	471	471	471	471	471	471	471	471	OK	OK	-
62
63		EBITDA	т.р.		170	170	170	170	170	170	170	170	170	170	170	170	OK	OK	-
64		EBITDA margin	%		16%	16%	16%	16%	16%	16%	16%	16%	16%	16%	16%	16%	OK	OK	-
65
66		Амортизация	т.р.	20	20	20	20	20	20	20	20	20	20	20	20	20	OK	OK	-
67		EBIT	т.р.		150	150	150	150	150	150	150	150	150	150	150	150	OK	OK	-
68
69		Проценты	т.р.	22%															-
70		EBT	т.р.		150	150	150	150	150	150	150	150	150	150	150	150			-
71
72		Налоги	т.р.	15%	22	22	22	22	22	22	22	22	22	22	22	22			-
73		Чистая прибыль	т.р.		127	127	127	127	127	127	127	127	127	127	127	127			-
74		Рентабельность	%		12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%			-
75
76
77
78	2	Cash Flow
79
80		Показатель	Ед. изм.	Параметр	1	2	3	4	5	6	7	8	9	10	11	12	Проверка
81																	Результат	Формула	Пусто
82		Операционный поток
83		Чистая прибыль	т.р.		127	127	127	127	127	127	127	127	127	127	127	127			-
84		Амортизация	т.р.		20	20	20	20	20	20	20	20	20	20	20	20			-
85		Изменение ЧОК	т.р.																-
86		Итого операционный поток	т.р.		147	147	147	147	147	147	147	147	147	147	147	147			-
87
88		Инвестиционный поток
89		Приобретение имущества	т.р.														-	-
90		Продажа имущества	т.р.														-	-
91		Итого инвестиционный поток	т.р.		-	-	-	-	-	-	-	-	-	-	-	-			-
92
93		Финансовый поток
94		Получение кредитов	т.р.														-	-
95		Возврат кредитов	т.р.	28	28	28	28	28	28	28	28	28	28	28	28	28			-
96		Привлечение капитала	т.р.														-	-
97		Выплата дивидендов	т.р.														-	-
98		Итого финансовый поток	т.р.		-28	-28	-28	-28	-28	-28	-28	-28	-28	-28	-28	-28			-
99
100		Остаток ДС на начало	т.р.			119	239	358	477	596	716	835	954	1,074	1,193	1,312			-