I got the income statement portion correct but the only part I do not get correct is the retained earnings. I already tried adding the net income to the beginning retained earnings and its wrong. Please help me with this

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

Excel Analytics 11-1 (Static) Part 5

5. 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 years sales and variable expenses increase by 5%.
• Next years fixed expenses are the same as this year.
• Next years ending balances in accounts receivable, inventory, and accounts payable each increase by 5% compared to their respective beginning balances.
• Next years 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 years depreciation expense of $40,000.
• Next years 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 years 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 years 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 companys estimated average total liabilities and stockholders equity for next year?

C D E Ending Balance Average Balance (309,500) $ 189,000 252,000 903,000 37,000 1,071,500 $ (79,750) 184,500 246,000 848,000 37,000 1,235,750 84,000 $ 577,500 $ 410,000 $ 82,000 563,750 410,000 1,071,500 $ 1,055,750 B Edman Company Balance Sheets Beginning Balanced Sheet: Balance Assets Cash $ 150,000 $ Accounts receivable $ 180,000 Inventory $ 240,000 Property, plant & equipment (net) S 793,000 Other assets $ 37,000 $ Total assets $ 1,400,000 $ 1 2 Liabilities & stockholders' equity 3 Accounts payable $ 80,000 4 Bonds payable $ 550,000 5 Common stock $ 410,000 16 Retained earnings S 360,000 17 Total liabilities & stocholders' equity $ 1,400,000 $ 18 19 Edman Company 20 Income Statement 21 Sales $ 2,625,000 22 Variable expenses: 23 Cost of goods sold 1,680,000 24 Variable selling expense 252,000 25 Total variable expenses 1,932,000 26 Contribution margin 693,000 27 Fixed expenses: 28 Fixed selling expense 220,000 29 Fixed administrative expense 300,000 30 Total fixed expenses 520,000 31 Net operating income 173,000 32 Interest expense (8%) 45,100 33 Net income before tax 127,900 34 Tax expense (30%) 38,370 35 Net income $ 89,530 36 37 1 ... Requirement 4 DuPont Diagram Requirement 5 Financials C D E Ending Balance Average Balance (309,500) $ 189,000 252,000 903,000 37,000 1,071,500 $ (79,750) 184,500 246,000 848,000 37,000 1,235,750 84,000 $ 577,500 $ 410,000 $ 82,000 563,750 410,000 1,071,500 $ 1,055,750 B Edman Company Balance Sheets Beginning Balanced Sheet: Balance Assets Cash $ 150,000 $ Accounts receivable $ 180,000 Inventory $ 240,000 Property, plant & equipment (net) S 793,000 Other assets $ 37,000 $ Total assets $ 1,400,000 $ 1 2 Liabilities & stockholders' equity 3 Accounts payable $ 80,000 4 Bonds payable $ 550,000 5 Common stock $ 410,000 16 Retained earnings S 360,000 17 Total liabilities & stocholders' equity $ 1,400,000 $ 18 19 Edman Company 20 Income Statement 21 Sales $ 2,625,000 22 Variable expenses: 23 Cost of goods sold 1,680,000 24 Variable selling expense 252,000 25 Total variable expenses 1,932,000 26 Contribution margin 693,000 27 Fixed expenses: 28 Fixed selling expense 220,000 29 Fixed administrative expense 300,000 30 Total fixed expenses 520,000 31 Net operating income 173,000 32 Interest expense (8%) 45,100 33 Net income before tax 127,900 34 Tax expense (30%) 38,370 35 Net income $ 89,530 36 37 1 ... Requirement 4 DuPont Diagram Requirement 5 Financials