Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Fill in the blanks, please include the excel equations for each blank! Thank you ADVANCED PROBLEM 2-2 FOR SPREADSHEET APPLICATION Balance sheet. Reach Manufacturing has
Fill in the blanks, please include the excel equations for each blank! Thank you
ADVANCED PROBLEM 2-2 FOR SPREADSHEET APPLICATION | ||||||||||
Balance sheet. Reach Manufacturing has lost its computer systems and must reconstruct the last two years of its balance sheet. The company has been able to get some information out of its crashed computer: a. Reconstruct the balance sheets for 2013 and 2014 using a spreadsheet, and find the change in each account and each category balance (current assets, long-term assets, total assets, current liabilities, total liabilities, total owners' equity, and total liabilities and owners' equity) from 2013 to 2014. The balance sheet has the same accounts in both years. Verify that the changes in the total assets equal the changes in the total liabilities and owners' equity. b. Find the cash flow from assets for the year 2013 if the OCF for 2013 is $389,000. Note: Treat intangible assets as a capital spending item. | ||||||||||
Solution | ||||||||||
a. Reconstruct the balance sheets for 2013 and 2014 using a spreadsheet, and find the change in each account and each category balance (current assets, long-term assets, total assets, current liabilities, total liabilities, total owners' equity, and total liabilities and owners' equity) from 2013 to 2014. The balance sheet has the same accounts in both years. Verify that the changes in the total assets equal the changes in the total liabilities and owners' equity. | ||||||||||
Reach Manufacturing | ||||||||||
Balance Sheet as of December 31, 2014, and December 31, 2013 | ||||||||||
ASSETS | 2014 | 2013 | Change | LIABILITIES | 2014 | 2013 | Change | |||
Current assets | Current liabilities | |||||||||
Cash | $ 26,000 | $ 23,000 | Accounts payable | $ 679,000 | $ 419,000 | |||||
Marketable securities | $ 58,000 | Notes payable | $ 210,000 | $ 390,000 | ||||||
Accounts receivable | $ 518,000 | Total current liabilities | $ 889,000 | |||||||
Inventory | $ 910,000 | $ 639,000 | Long-term debt | $ -889,000 | $ 3,540,000 | |||||
Total current assets | $ 1,242,000 | Total liabilities | ||||||||
Fixed assets | $ 4,975,000 | $ 4,387,000 | OWNERS' EQUITY | |||||||
Accumulated depreciation | $ -1,364,000 | Common stock | $ 330,000 | $ 330,000 | ||||||
Intangible assets | $ 431,000 | $ 465,000 | Retained earnings | $ 701,000 | ||||||
Total long-term assets | Total owners' equity | |||||||||
TOTAL ASSETS | $ 5,085,000 | TOTAL LIABILITIES AND OWNERS' EQUITY | $ 5,832,000 | |||||||
Does the change in the total assets equal the change in the total liabilities and owners' equity? | ||||||||||
b. Find the cash flow from assets for the year 2013 if the OCF for 2013 is $389,000. | ||||||||||
Operating cash flow (OCF) | $ 389,000 | |||||||||
Net working capital (NWC) for 2013 | ||||||||||
Net working capital (NWC) for 2014 | ||||||||||
- Change in NWC | ||||||||||
Change in fixed assets from 2013 to 2014 | $ - | |||||||||
Change in intangible assets from 2013 to 2014 | $ - | |||||||||
- Capital spending | ||||||||||
= Cash flow from assets | ||||||||||
Requirements | Points | |||||||||
1. | Start Excel. | 0 | ||||||||
2. | In cell D22, by using cell references, calculate the total long-term assets for year 2014. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
3. | In cell D23, by using a cell reference, calculate the total assets for year 2014. Refer to the relevant cell from the given data in the balance sheet. Note: The total assets must equal the total liabilities and owners' equity. | 1 | ||||||||
4. | In cell D18, by using cell references, calculate the total current assets for year 2014. Use the relevant cells in the balance sheet. | 1 | ||||||||
5. | In cell D16, by using cell references, calculate the accounts receivable for year 2014. Use the relevant cells in the balance sheet. | 1 | ||||||||
6. | In cell H14, by using cell references, calculate the accounts payable for year 2014. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
7. | In cell H22, by using cell references, calculate the total owners' equity for year 2014. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
8. | In cell H18, by using cell references, calculate the total liabilities for year 2014. Use the relevant cells in the balance sheet. | 1 | ||||||||
9. | In cell H17, by using cell references, calculate the long-term debt for year 2014. Use the relevant cells in the balance sheet. | 1 | ||||||||
10. | In cell E15, by using cell references, calculate the marketable securities for year 2013. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
11. | In cell E22, by using cell references, calculate the total long-term assets for year 2013. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
12. | In cell E20, by using cell references, calculate the accumulated depreciation for year 2013. Use the relevant cells in the balance sheet. | 1 | ||||||||
13. | In cell I16, by using cell references, calculate the total current liabilities for year 2013. Use the relevant cells from the given data in the balance sheet. | 1 | ||||||||
14. | In cell I18, by using cell references, calculate the total liabilities for year 2013. Use the relevant cells in the balance sheet. | 1 | ||||||||
15. | In cell I23, by using a cell reference, calculate the total liabilites and owners' equity for year 2013. Refer to the relevant cell from the given data in the balance sheet. | 1 | ||||||||
16. | In cell I22, by using cell references, calculate the total owners' equity for year 2013. Use the relevant cells in the balance sheet. | 1 | ||||||||
17. | In cell I21, by using cell references, calculate the retained earnings for year 2013. Use the relevant cells in the balance sheet. | 1 | ||||||||
18. | In cell F14, by using cell references, calculate the change in the cash from 2013 to 2014. | 1 | ||||||||
19. | In cell range F15:F23, by using cell references, calculate the change in other accounts of the firm's assets from 2013 to 2014. Copy the content from cell F14 and paste it to cell range F15:F23. | 1 | ||||||||
20. | In cell J14, by using cell references, calculate the change in the accounts payable from 2013 to 2014. | 1 | ||||||||
21. | In cell ranges J15:J18 and J20:J23, by using cell references, calculate the change in other accounts of the firm's liabilities and owners' equity from 2013 to 2014. Copy the content from cell J14 and paste it to cell ranges J15:FJ18 and J20:J23. | 1 | ||||||||
22. | In cell H25, type either Yes or No depending on whether the changes in the total assets equal the changes in the total liabilities and owners' equity. | 1 | ||||||||
23. | In cell F30, by using cell references, calculate the net working capital (NWC) for year 2013. Use the relevant cells from the balance sheet in part a. | 1 | ||||||||
24. | In cell F31, by using cell references, calculate the net working capital (NWC) for year 2014. Use the relevant cells from the balance sheet in part a. | 1 | ||||||||
25. | In cell F32, by using cell references, calculate the change in NWC from 2013 to 2014. Use cells F30 and F31. | 1 | ||||||||
26. | In cell F35, by using cell references, calculate the change in capital spending from 2013 to 2014. Use cells F33 and F34. | 1 | ||||||||
27. | In cell F36, by using cell references, calculate the cash flow from assets. Use cells F29, F32, and F35. | 1 | ||||||||
28. | Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started