Question
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
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?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started