Question

Do the following quiz in Excel. Use a single worksheet for each question. Set up the answer with the dates in columns and the income statement or balance sheet items in rows. On the balance sheet the assets and liabilities should be in the same column. Use links between worksheets so the system works even if you change earlier numbers. Assume you have started a new company on the first day of the year. You raise $100,000 from family and friends, and borrow $40,000 from the bank on a ten-year, 8% note, with 10% of the original principle due each year (on the morning of Dec. 31.) Your company sells only for cash, and holds no inventory. All labor and materials are paid for at delivery. Properly label all accounts. 1. Show the balance sheet for Jan. 1, 2002. 2. On Jan 2, 2002 you buy a machine for $75,000. It has a five-year life, and is depreciated straight-line. Show the balance sheet for Jan. 2, 2002. 3. Your sales for the 2002 are $200,000 and for 2003 they are $250,000. Cost of goods sold is 34% of sales, and labor is 38% of sales. Taxes are at 34%. Investors are paid 50% of earnings as dividends. Show the income statement and balance sheet for 2002 and 2003. Assume debt repayments are made on the morning of Dec. 31 of each year. Show details on GFA and Accumulated Depreciation. What is the cash flow available for each year? 4. Show the cash “T” account for 2002 and 2003. Show all cash expenses off the income statement. Explain how this ties into cashflow.

Answer 1

1. Balance sheet as on 01.01.2002

Particulars	Amount
Capital andLaibilities
Capital Fund	100000
Long term borrowings
Bank loan 8%	40000
Total	140000
Assets
Current Assets Cash	140000
Total	140000

2.Balance sheet as on 02.01.2002

Particulars	Amount
Capital andLaibilities
Capital Fund	100000
Long term borrowings
Bank loan 8%	40000
Total	140000
Assets
Fixed assets Plant property and equipment	75000
Current Assets Cash	65000
Total	140000

3. Cash flow statement (budgeted) for 2002 and 2003

Particulars	2002	2003
Opening cash	140000	88474
Revenue inflow	200000	250000
Total available cash	340000	338474
Disbursements
For inventory (34% of sales)	68000	85000
Labour (38% of sales)	76000	95000
Interest paid	(40000*8%) =3200	(36000*8%) =2880
Machine purchased	75000	0
Taxes paid (200000-68000-76000-3200-(75000/5))*34%	12852	17720
Loan paid	4000	4000
Dividends paid (200000-68000-76000-3200-12852-(75000/5))*50%	12474	17200
Total disbursement	251526	221800
Closing cash	88474	116674

4. Cash account for 2002 and 2003

Date	PArticulars	2002 amount (Dr)	2003 amount(Dr)	date	particulars	2002 amount(Cr)	2003 amount(Cr)
1 jan	To opening balance b/d	0	88474	2 jan	By machinery a/c	75000
1 jan	To capital a/c	100000		31st dec	By bank loan	4000	4000
1 jan	To bank loan	40000		31st dec	By material a/c	68000	85000
31st dec	To sales a/c	200000	250000	31st dec	By labour a/c	76000	95000
				31st dec	By interest a/c	3200	2880
				31st dec	By taxex a/c	12852	17720
				31st dec	By dividend a/c	12474	17200
				31st dec	By balance c/d	88474	116674
	Total	340000	338474		Total	340000	338474