monthly budget forecasting CASE STUDY.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total

2
3	Revenue
4	New	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$25,000	$300,000
5	Returning	0	21250	39313	64275	75884	85751	94139	101268	107328	112478	116857	120578	939120
6	Net Revenue	$25,000	$46,250	$64,313	$89,275	$100,884	$110,751	$119,139	$126,268	$132,328	$137,478	$141,857	$145,578	$1,239,120
7
8	COGS
9	Product Cost	$7,500	$13,875	$19,294	$26,783	$30,265	$33,225	$35,742	$37,880	$39,698	$41,244	$42,557	$43,673	$371,736
10	Merchant Services	750	1388	1929	2678	3027	3323	3574	3788	3970	4124	4256	4367	37174
11	Total COGS	$8,250	$15,263	$21,223	$29,461	$33,292	$36,548	$39,316	$41,668	$43,668	$45,368	$46,813	$48,041	$408,909
12
13	Gross Margin	$16,750	$30,988	$43,089	$59,814	$67,592	$74,203	$79,823	$84,599	$88,659	$92,111	$95,044	$97,537	$830,210
14	GM%	67%	67%	67%	67%	67%	67%	67%	67%	67%	67%	67%	67%	67%
15
16	Operating Expenses
17	Personnel	$0	$0	$0	$500	$0	$0	$0	$500	$0	$0	$0	$500	$1,500
18	Paid Advertising	7500	7500	7500	7500	7500	7500	7500	7500	7500	7500	7500	7500	90000
19	Consultants	1250	1250	1250	1500	750	500	5500	7000	5500	5500	5500	7000	42500
20	Travel	750	750	750	7000	3250	3250	5750	10250	5750	10750	10750	15250	74250
21	Lodging	600	600	600	1500	1500	1500	2900	2900	2900	2900	2900	2900	23700
22	Meals & Entertainment	2150	2150	2150	11000	44500	44500	68000	68000	68000	93000	93000	93000	589450
23	Internet	25000	25000	25000	5000	5000	5000	7500	7500	7500	7500	7500	7500	135000
24	Software	0	0	0	500	0	0	0	500	0	0	0	500	1500
25	Miscellaneous	0	0	0	500	0	0	0	500	0	0	0	500	1500
26	Total Operating Expenses	$37,250	$37,250	$37,250	$35,000	$62,500	$62,250	$97,150	$104,650	$97,150	$127,150	$127,150	$134,650	$959,400
27
28	Operating Income	-$20,500	-$6,263	$5,839	$24,814	$5,092	$11,953	-$17,327	-$20,051	-$8,491	-$35,039	-$32,106	-$37,113	-$129,190
29
30	Taxes													615190
31
32	Net Income	-$20,500	-$6,263	$5,839	$24,814	$5,092	$11,953	-$17,327	-$20,051	-$8,491	-$35,039	-$32,106	-$37,113	$486,000
33	NI%
34
35	ASSUMPTIONS
36
37	Revenue
38
39	Units
40	New	100	100	100	100	100	100	100	100	100	100	100	100
41	Returnings		85	157	257	304	343	377	405	429	450	467	482
42	Total	100	185	257	357	404	443	477	505	529	550	567	582
43
44	Price	$250	$250	$250	$250	$250	$250	$250	$250	$250	$250	$250	$250
45
46	COGS
47	Product Cost(%of revenue)	30%	30%	30%	30%	30%	30%	30%	30%	30%	30%	30%	30%
48	Merchant Services	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%
49
50
51	Marketing Aquisition
52
53	Customer Acquisition Cost	$75	$75	$75	$75	$75	$75	$75	$75	$75	$75	$75	$75
54	Churn Rate		15%	15%	15%	15%	15%	15%	15%	15%	15%	15%	15%
55
56	Tax Rate	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%
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