Question

Spencer Lee plans to open a computer repair shop in his neighborhood in Iowa City, Iowa. He has scraped together $75,000 by getting his mother and sister to invest in his venture. The rest of the money he will borrow from a local bank that has encouraged him to apply for a loan. Spencer must maintain a minimum balance of at least $5,000. Assume any amount is borrowed at the beginning of the year and principal repayment occurs at the end of the year. Spencer found a great building that he can rent for only $1,000 per month. Also, utilities will run an additional $1,000 a month.

            Spencer expects sales to equal $100,000 in his first year, grow to $200,000 in the second year, and grow by 10% per year after that. He expects half his sales to be paid in cash, and the remainder will be paid with credit cards. Spencer deposits credit card slips with his regular bank deposit, and the bank credits his account with 95% of the face value of the slip.

            All computer technicians will earn a commission of 50% of the service revenue they generate, and the shop gets all the margin on parts sales (parts earn a margin of 50%). Parts will equal 20% of sales, and Spencer expects parts inventory to turn 12 times per year. Local suppliers offer thirty day terms.

            To conserve cash, Spencer will rent some of his equipment for $2,500 per month, and he will purchase another $120,000 of equipment for cash, when he opens the shop. He will depreciate this equipment over its five year life using the straight line method. In year 3 he will purchase addi­tional equipment, for cash, $75,000, and this will drop his equipment rental down to $1,500 per month.

            Use a tax rate of 30%, and assume the bank will charge 10% for loans to Spencer.

Required:

1.     Prepare proforma financials for the first five years of operation.

2.     Assume Spencer did not use his mother and sister to raise his original $75,000. Instead he got this money from some investors who want to earn a 20% return on their investment after year 1 operations. They are willing to be stockholders or creditors, whichever Spencer prefers. Which form of financing should Spencer use?

Answer 1

Sr.No.	Purticulers	1	2	3	4	5
A	Revenue
	Part sales-(20% of total sales)	20000	40000	44000	48400	53240
	Service revenue-(80%of total sales)	80000	160000	176000	193600	212960
	Rent on machinary	2500	2500	4000	4000	4000
	Total(A)	102,500	202,500	224,000	246,000	270,200
B	Expenses
	Parts Purchased(cost given 50% of part sales)	10000	20000	22000	24200	26620
	Rent & Utilities(1000 each per month)	24000	24000	24000	24000	24000
	charges of bank on credit sales(5% of face vaue) Refer working note	2500	5000	5500	6050	6655
	Commission to computer technitiens(50% of service revenue)	40000	80000	88000	96800	106480
	Depreciation	24000	24000	24000	24000	24000
	Depreciation on additional Equipment (considerd 5 years of life)	0	0	15000	15000	15000
	Interest on loan(refer working note)	5000	5000	6985	6985	6985
	Total(B)	105,500	158,000	185485	197035	209740
C	Profit Before tax(A-B)	(3000)	44500	38515	48965	60460
D	Tax@30%	0	13350	11554.30	14689.50	18138
E	Profit After tax(C-D)	(3000)	31150	26960.50	34275.50	42322
F	Add:Depreciation	24000	24000	39000	39000	39000
G	Net cash flow from operation	21000	55150	65960.50	73275.50	81322

1.Interest calculation

* Machine purchsed 1,20,000 - borrowed from mother and sister 75000, remaining 45000+5000 from bank

-machine puchased 75000 in third year - available cash 55150 at the end of second year, balance 19850 borrowed from bank.

interest on total borrowing (50000+19850)*10%=6985.

2.charges of bank

Sr.no.	purticulers	1	2	3	4	5
A	Sales	100000	200000	220000	242000	266200
B	on Credit-50%	50000	100000	110000	121000	133100
C	bank charges-5% of B	2500	5000	5500	6050	6655