I need the payback calculated and my income & balance sheet double checked for accuracy

finance_project_draft.xls

Home >Business & Finance homework help >I need the payback calculated and my income & balance sheet double checked for accuracy

Income Statement Simplified - T

																Cash Book
	Assumptions							Y1	Y2	Y3	Y4	Y5				Y1	Y2	Y3	Y4	Y5
			Cost of capital	12%											Opening Bal	$ - 0	$ 499,568	$ 1,011,089	$ 1,676,066	$ 2,540,534
			Year 1 Revenue	2,250,000	Revenue			2,250,000	2,925,000	3,802,500	4,943,250	6,426,225			By Cash	$ 2,065,068	$ 2,684,589	$ 3,489,966	$ 4,536,955	$ 5,898,042
			Yearly Revenue Growth	40%											To Personal Exp	$ 1,115,000	$ 1,115,000	$ 1,115,000	$ 1,115,000	$ 1,115,000
			COGS % of Sales	50%	COGS			1,125,000	1,462,500	1,901,250	2,471,625	3,213,113			Gasoline	$ 135,000	$ 175,500	$ 228,150	$ 296,595	$ 385,574
		Personnel													Rent Expense	$ 65,000	$ 65,000	$ 65,000	$ 65,000	$ 65,000
			Office Managers	1	Gross Margin			1,125,000	1,462,500	1,901,250	2,471,625	3,213,113			Insurance	$ 67,500	$ 87,750	$ 114,075	$ 148,298	$ 192,787
			Office Mgr Salary	$90,000											Advertising	$ 18,000	$ 18,000	$ 18,000	$ 18,000	$ 18,000
			Service Managers	10	Operating Expenses										Licensing Fees	$ 1,500	$ 1,500	$ 1,500	$ 1,500	$ 1,500
			Service Mgr Salary	$60,000		Personnel Expense		$ 1,115,000	$ 1,115,000	$ 1,115,000	$ 1,115,000	$ 1,115,000			Utilities	$ 157,500	$ 204,750	$ 266,175	$ 346,028	$ 449,836
			Mkt Managers	5		Gasoline		$ 135,000	$ 175,500	$ 228,150	$ 296,595	$ 385,574			Office Expense	$ 5,000	$ 5,000	$ 5,000	$ 5,000	$ 5,000
			Mkt Mgr Salary	$85,000		Rent Expense		$ 65,000	$ 65,000	$ 65,000	$ 65,000	$ 65,000			Other	$ 1,000	$ 1,000	$ 1,000	$ 1,000	$ 1,000
						Insurance		$ 67,500	$ 87,750	$ 114,075	$ 148,298	$ 192,787			To Cash	$ 499,568	$ 1,011,089	$ 1,676,066	$ 2,540,534	$ 3,664,345
		Gasoline % of Rev		6%		Depreciation		$ 20,600	$ 22,600	$ 53,000	$ 85,000	$ 115,000
		Rent Expense		$65,000		Advertising		$ 18,000	$ 18,000	$ 18,000	$ 18,000	$ 18,000
		Insurance % of Rev		3.0%		Licensing Fees		$ 1,500	$ 1,500	$ 1,500	$ 1,500	$ 1,500
		Utilities % of Rev		7%		Utilities		$ 157,500	$ 204,750	$ 266,175	$ 346,028	$ 449,836
		Advertising		$18,000		Office Expense		$ 5,000	$ 5,000	$ 5,000	$ 5,000	$ 5,000
		Office Expense		$5,000		Other		$ 1,000	$ 1,000	$ 1,000	$ 1,000	$ 1,000
		Licensing Fees		$1,500		Total Op Expense		$ 1,586,100	$ 1,696,100	$ 1,866,900	$ 2,081,421	$ 2,348,697
		Other		$1,000
					Profit Before Tax			(461,100)	(233,600)	34,350	390,204	864,416
		Tax Rate		35%
		Discount Rate		20%	Taxes			0	0	12,023	136,571	302,545
		Year 5 CF Multiple		?
					Net Income			(461,100)	(233,600)	22,328	253,633	561,870
								Y0	Y1	Y2	Y3	Y4	Y5
Capital Expenditures								(103,000)	(10,000)	(152,000)	(160,000)	(150,000)	(10,000)
CF from Operations									(440,500)	(211,000)	75,328	676,870	676,870
Change in NWC							AR
	AR	(pull off bal sheet)					AR		(184,932)	(55,479)	(72,123)	(93,760)	406,295
	AP						AP		668,490	110,342	150,300	193,536	(1,122,669)
	Inv						INV		$ (92,465.75)	$ (27,739.73)	$ (36,061.64)	$ (46,880.14)	$ 203,147.26
	Total						Total		391,093	27,123	42,115	52,896	(513,227)	0
Total Cash Flow								(103,000)	(59,407)	(335,877)	(42,557)	579,766	153,643
IRR								12%
NPV								(69,042)
Payback

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		$ 155,623	$ 378,666	$ 627,867	$ 1,088,408
	x		Accounts Receivable	$ 184,932	$ 240,411	$ 312,534	$ 406,295	$ 528,183
	x		Inventory	$ 92,466	$ 120,205	$ 156,267	$ 203,147	$ 264,091
	x		Total Short-Term Assets	$ 277,397	$ 516,239	$ 847,467	$ 1,237,309	$ 1,880,682
	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		$ 347,197	$ 685,039	$ 1,091,267	$ 1,516,109	$ 2,073,082
		Liabilities
		Short-Term Liabilities
	x		Accounts Payable	$ 668,490	$ 778,833	$ 929,133	$ 1,122,669	$ 1,371,405
		Long-Term Liabilities
(tool for financing gap)			Notes Payable
		Total Liabilities		$ 668,490	$ 778,833	$ 929,133	$ 1,122,669	$ 1,371,405
		Equity
			Common Stock	$ 139,807	$ 139,807	$ 139,807	$ 139,807	$ 139,807
(tool for financing gap)	x		Retained Earnings	$ (461,100)	$ (233,600)	$ 22,328	$ 253,633	$ 561,870
		Total Equity		$ (321,293)	$ (93,793)	$ 162,135	$ 393,440	$ 701,677
		Total Liabilities & Equity		$ 347,197	$ 685,040	$ 1,091,267	$ 1,516,108	$ 2,073,082
(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