Balance sheet formation to know working capital requirements

Mars Electronics is a distributor for the Global Electric Company [GEC], a large manufacturer of electrical and electronics products for consumer and institutional markets. Below are the annual financial statements of the company for the last 2 years .

Income Statements

$ thousands
12 months Ending
6/30/1999 12 Months Ending
6/30/2000

Net sales $10,655 $11,720
Cost of goods sold 8,940 9,834

Selling, general & administrative expenses 1,554 1,677
Depreciation expenses 44 76

Interest expenses 62 70

Corporate income tax 23 26

Earnings after tax $32 $37

Dividends to be paid 5 1

Balance Sheets

$ thousands
6/30/1999 6/30/2000
Assets
Cash $160 $70
Accounts Receivable 1,953 5,100
Inventories 1,986 2,085
Prepaid expenses 380 25
Net fixed assets 733 830
Total $5,212 $8,110

Liabilities & owners' equity
Short-term debt $50 $50
Accounts Payable 1,450 $1,650
Accrued Expenses 398 138
Long Term Debt 800 3,700
Owners' Equity 2,514 2,572
Total $5,212 $8,110

a. Compute Mars Electronics Working Capital Requirement (WCR) on June 30th 1999, and on June 30th 2000. Compute also the collection period and the inventories turnover for the period 6/30/99 and 6/30/00.

Working Capital - A company's current assets minus its current liabilities - considered a good measure of both a company's efficiency and its financial health. Also known as "net working capital".

1999: Current Assets - Current Liabilities = 5212 - 733 = 4479 - 1898 = 2581
2000: CA - CL = 8110 - 830 = 7280 - 1838 = 5442

1999: AR Turnover = Net Credit Sales / Avg AR = 10655 / 1953 = 5.46
2000: 11720 / 5100 = 2.30

1999: Inventory Turnover = Sales / Inventory = 10655 / 1986 = 5.37
2000: 11720 / 2085 = 5.62

b. Prepare Mars Electronics' managerial balance sheet on June 30th 1999,and on June 30th 2000.

Just do ITEM B only a managerial balance sheet in excel