Question

Edman Company is a merchandiser that has provided the following balance sheet and income statement for this year.

		Beginning Balance			Ending Balance
Assets
Cash	$	62,800		$	150,000
Accounts receivable		160,000			180,000
Inventory		230,000			240,000
Property, plant & equipment (net)		833,000			793,000
Other assets		37,000			37,000
Total assets	$	1,322,800		$	1,400,000
Liabilities & Stockholders’ Equity
Accounts payable	$	70,000		$	80,000
Bonds payable		550,000			550,000
Common stock		410,000			410,000
Retained earnings		292,800			360,000
Total liabilities & stockholders’ equity	$	1,322,800		$	1,400,000

		This Year
Sales	$	2,500,000
Variable expenses:
Cost of goods sold		1,600,000
Variable selling expense		240,000
Total variable expenses		1,840,000
Contribution margin		660,000
Fixed expenses:
Fixed selling expenses		220,000
Fixed administrative expenses		300,000
Total fixed expenses		520,000
Net operating income		140,000
Interest expense (8%)		44,000
Net income before tax		96,000
Tax expense (30%)		28,800
Net income	$	67,200 To evaluate alternative 2, refer to the “Requirement 5 Financials” tab within your template. Assume the company purchases new equipment in an effort to grow sales with the following estimated impacts: Next year’s sales and variable expenses increase by 5%. Next year’s fixed expenses are the same as this year. Next year’s ending balances in accounts receivable, inventory, and accounts payable each increase by 5% compared to their respective beginning balances. Next year’s ending balance in property, plant, and equipment (net) increases by $110,000 compared to its beginning balance. This reflects the purchase of a $150,000 piece of equipment minus next year’s depreciation expense of $40,000. Next year’s ending balance in bonds payable decreases by $50,000 compared to its beginning balance. This reflects a bond issuance of $150,000 to purchase the equipment and a bond retirement of $200,000. Next year’s ending balances in other assets and common stock are the same as their beginning balances. a. Based on the above estimated impacts, use Excel formulas to calculate the revised sales and variable expenses as needed in column B. (Hint: Your formulas should refer to information contained in the Requirement 1 Financials tab.) What are the revised amounts of sales and the variable expenses? b. Based on the above estimated impacts, use Excel formulas to calculate ending balances as needed in column C. What is the ending balance in the following accounts? c. Create formulas within column D that calculate next year’s average balances for all balance sheet accounts (except Cash which will automatically be computed for you). What is the average balance in the following accounts? d. What is the company’s estimated average total liabilities and stockholders’ equity for next year?

Req 1

1. Sales _____

2.Cost of goods sold ______

3.Variable selling expense _____

Req 2

1. Accounts Receivable _____

2. Accounts Payable ____

3. Retained Earnings ____

Req 3

1. Accounts Receivable ___

2. Accounts Payable _____

3. Retained Earnings ____

Req 4

1. What is the company’s estimated average total liabilities and stockholders’ equity for next year?

Answer 1

Calculation of Esimated Net Income of next year Amount in $

Sales 2500000*1.05 2625000

Less: Variable expense  

COGS 1600000*1.05 1680000

Variable selling expense 240000*1.05 252000

Total Variable Cost 1932000

Contribution 693000

Less:Fixed Expense

Fixed selling expense 220000

Administrative expense 300000

Depreciation on new equipment purchase 40000

Total Fixed expense 560000

Net operating Income 133000

Interest expense(500000*8%) 40000

Net Income 93000

Tax 30% 27900

Net Income after tax 65100

Estimated Balance sheet $

Cash

Assets Opening Balance    Closing Balance Average

Cash 150000 38100 B.F. 94050

Account Recievable 180000 189000(18000*1.05)    184500

Inventory 240000 252000(240000*1.05) 246000

PPE 793000 903000(increased by 110000) 848000

Other Assets 37000   37000 37000

Total Assets 1400000 1419100 1409550

Liabilities

Account Payable 80000   84000(80000*1.05) 82000

Bonds payable 550000 500000 525000

Common Stock 410000   410000 410000

Retained earning 360000    425100(360000+65100) 392550

Total liablities and stockholderequity 1400000 1419100 1409550