Question

Create a model from an empty spreadsheet. Use the information below You must project Cleo's Nail Salon's income statement for 5 years. Here is the historical information you have received from Cleo's. Previous year's income statement for Cleo's Sales COGS SG&A EBIT Interest Expense EBT Taxes Net Income 500,000 300,000 100,000 100,000 10,000 90,000 28,800 61,200 Cleo's assumptions for the next five years at Sales growth COGS % of Sales SG&A % of Sales Interest Expense S Tax Rate Year 1 4% 60% Year 2 5% 60% Year 3 5% 61% 21% 12,500 Year 4 6% 62% 20% 2,500 Year 5 6% 63% 20% 12.500 11,000 32% 11,000 32%

Answer 1

A	B	C	D	E	F	G	H	I	J	K	L
2
3		Assumptions:
4
5			Year0	Year1	Year2	Year3	Year4	Year5
6		Sales Growth		4%	5%	5%	6%	6%
7		COGS % of Sales		60%	60%	61%	62%	63%
8		SG&A % of Sales		22%	22%	21%	20%	20%
9		Interest Expense		$11,000	$11,000	$12,500	$12,500	$12,500
10		Tax rate		32%	32%	32%	32%	32%
11
12
13		Income Statement
14
15			Year0	Year1	Year2	Year3	Year4	Year5
16		Sales	$500,000	$520,000	$546,000	$573,300	$607,698	$644,160	=H16*(1+I6)
17		COGS	$300,000	$312,000	$327,600	$349,713	$376,773	$405,821	=I16*I7
18		SG&A	$100,000	$114,400	$120,120	$120,393	$121,540	$128,832	=I16*I8
19		EBIT	$100,000	$93,600	$98,280	$103,194	$109,386	$109,507	=I16-I17-I18
20		Interest Expense	$10,000	$11,000	$11,000	$12,500	$12,500	$12,500	=I9
21		EBT	$90,000	$82,600	$87,280	$90,694	$96,886	$97,007	=I19-I20
22		Taxes	$28,800	$26,432	$27,930	$29,022	$31,003	$31,042	=I21*I10
23		Net Income	$61,200	$56,168	$59,350	$61,672	$65,882	$65,965	=I21-I22
24