SweetStudy (HomeworkMarket.com)

0

Revise built income statement and balance sheet and connect the 2 sheets

finance_project__1_2014.xls

Home >Business & Finance homework help >Revise built income statement and balance sheet and connect the 2 sheets

Income Statement Simplified - T


	Assumptions									Y1	Y2	Y3	Y4	Y5
			Year 1 Revenue	2,000,000			Revenue			2,000,000	2,600,000	3,380,000	4,394,000	5,712,200
			Yearly Revenue Growth	30%
			COGS % of Sales	20%			COGS			400,000	520,000	676,000	878,800	1,142,440
		Personnel
			Office Managers	1			Gross Margin			1,600,000	2,080,000	2,704,000	3,515,200	4,569,760
			Office Mgr Salary	$60,000
			Service Managers	10			Operating Expenses
			Service Mgr Salary	$50,000				Personnel Expense		635,000	635,000	635,000	635,000	635,000
			Mkt Managers	1				Gasoline		100,000	130,000	169,000	219,700	285,610
			Mkt Mgr Salary	$75,000				Rent Expense		72,000	72,000	72,000	72,000	72,000
								Insurance		40,000	52,000	67,600	87,880	114,244
		Gasoline % of Rev		5%				Depreciation		20,600	22,600	53,000	85,000	115,000
		Rent Expense		$72,000				Advertising		18,000	18,000	18,000	18,000	18,000
		Insurance % of Rev		2%				Licensing Fees		1,000	1,000	1,000	1,000	1,000
		Utilities % of Rev		10%				Utilities		200,000	260,000	338,000	439,400	571,220
		Advertising		$18,000				Office Expense		5,000	5,000	5,000	5,000	5,000
		Office Expense		$5,000				Other		500	500	500	500	500
		Licensing Fees		$1,000				Total Op Expense		1,092,100	1,196,100	1,359,100	1,563,480	1,817,574
		Other		$500
							Profit Before Tax			507,900	883,900	1,344,900	1,951,720	2,752,186
		Tax Rate		30%
							Taxes			152,370	265,170	403,470	585,516	825,656
		Year 5 CF Multiple		?
							Net Income			355,530	618,730	941,430	1,366,204	1,926,530
Capital Expenditures
CF from Operations
Change in NWC
	AR	(pull off bal sheet)
	AP
	Inv
	Total
Total Cash Flow
IRR
NPV
Payback
(No hard coding -should sync from income statment & bal sheets)

Balance Sheet - Table 1

Days of Accounts Receivable	30
Days Inventory on Hand	30
Days of Accounts Payable	90
(x's mean NO hard coding)
						Y1	Y2	Y3	Y4	Y5
		Assets
		Short-Term Liabilities
(tool for financing gap)			Cash & Equivalent			89262	89,262	98,349	313,154	871,905
	x		Accounts Receivable			164,384	2,600,000	3,380,000	4,394,000	5,712,200
	x		Inventory			400,000	520,000	676,000	878,800	1,142,440
	x		Total Short-Term Assets			653,646	3,209,262	4,154,349	5,585,954	7,726,545
	x		Long Term Assets			113,000	265,000	425,000	575,000	585,000
	x		Accum. Depreciation			43,200	96,200	181,200	296,200	392,600
	x		Net Long-Term Assets			69,800	168,800	243,800	278,800	192,400
	x	Total Assets				723,446	3,378,062	4,398,149	5,864,754	7,918,945
		Liabilities
		Short-Term Liabilities
	x		Accounts Payable			367,915	423,148	501,805	602,206	729,866
		Long-Term Liabilities
(tool for financing gap)			Notes Payable
		Total Liabilities					423,148	501,805	602,206	729,866
		Equity
			Common Stock			367,916	1,968,268	1,968,268	1,968,268	1,968,268
(tool for financing gap)	x		Retained Earnings			355,530	986646	1,928,076	3,294,280	5,220,810
		Total Equity				723,446	2,954,914	3,896,344	5,262,548	7,189,078
		Total Liabilities & Equity				723,446	3,378,062	4,398,149	5,864,754	7,918,945
(tool)		FINANCING GAP				(0)	0	(0)	(0)	0

Capital Expenditures - Table 1


	CAPITAL EXPENDITURES			Y0	Y1	Y2	Y3	Y4	Y5
		Shop Machinery
			Lathe				10,000
			CNC					40,000
		Office Furniture
			Copy Machine	1,000
			Server		5,000
			Computer	2,000
			Computer			2,000
		Trucks
			Truck1	50,000
			Truck2	50,000
			Truck3			50,000
			Truck4			50,000
			Truck5			50,000
			Truck6				50,000
			Truck7				50,000
			Truck8				50,000
			Truck9					55,000
			Truck10					55,000
		Trailer
			Trailer 1		5,000
			Trailer 2						10,000
			TOTAL CAPEX	103,000	10,000	152,000	160,000	150,000	10,000
	DEPRECIATION			Y0	Y1	Y2	Y3	Y4	Y5
		Shop Machinery
		5	Lathe				2,000	2,000	2,000
		5	CNC					8,000	8,000
		Office Furniture
		5	Copy Machine	200	200	200	200	200
		5	Server		1,000	1,000	1,000	1,000	1,000
		5	Computer	400	400	400	400	400
		5	Computer			400	400	400	400
		Trucks
		5	Truck1	10,000	10,000	10,000	10,000	10,000
		5	Truck2	10,000	10,000	10,000	10,000	10,000
		5	Truck3			10,000	10,000	10,000	10,000
		5	Truck4			10,000	10,000	10,000	10,000
		5	Truck5			10,000	10,000	10,000	10,000
		5	Truck6				10,000	10,000	10,000
		5	Truck7				10,000	10,000	10,000
		5	Truck8				10,000	10,000	10,000
		5	Truck9					11,000	11,000
		5	Truck10					11,000	11,000
		Trailer
		5	Trailer 1		1,000	1,000	1,000	1,000	1,000
		5	Trailer 2						2,000
			TOTAL DEPR	20,600	22,600	53,000	85,000	115,000	96,400