Edman Company is a merchandiser that has provided the following balance sheet and income statement for this
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:
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
What is the company’s estimated average total liabilities and stockholders’ equity for next year?