803 3. Overall, what does your analysis mean? Is Charles correct to be concerned about these numbers? Explain. 21-2 Metro Mix Studio: Financial Statements ULU The first year doing business in a new space as Metro Mix Studio, LLC, has gone better than the owner, Mark Meddings, could have ever expected. As a studio engi- neer, Mark specializes in the recording, mixing, and mastering of commercial-use drum and sound packs for use by other recording artistsand sales have been booming. After initially starting as a home-based business, Mark is now planning an expansion of his full-time professional recording studio in Minneapolis. But before he can do so he needs to secure a loan from a local bank, and the bank has asked Mark to provide year-end financial statements. 1. Create the financial statements for December 31, 2016; depict them in vertical format; and compute the current and acid test ratios. Based on the ratio analysis, is Mark correct to feel good about the financial condition of his recording studio? Account title Amount Account title Amount Cash $4,230 Accounts payable $3,475 Depreciation 2,210 Merchandise inventory 15,800 Mark, equity 37,745 Accounts receivable 6,470 Cost of goods sold 85,980 Net sales 121,480 Rent expense 15,000 Insurance payable 520 Wages payable Equipment 16,800 Utilities 6,650 Wages 8,370 Miscellaneous expenses 1,480 1,560 OVERVIEW The purpose of this project is to give you an opportunity to create your own original Excel spreadsheets for an income statement and balance sheet. You will also calculate some important ratios for a business and provide analysis based on the ratios. INSTRUCTIONS 1. First, review how to use cell references in Module 3 Exploration 1. Review the cell reference video which shows how to reference cells from another sheet in the same file. 2. Use Case Study 21-2 from page 803 in the eText. This case study asks you to prepare a balance sheet, income statement, and ratio analysis of the Metro Mix Studio. 3. Create three sheets from this case study in one Excel file: a balance sheet, an income statement sheet, and a ratio analysis sheet. Download Excel Project 5 for a template for the ratio analysis sheet. The income statement and balance sheets must be included in this file. All calculations must be done using Excel formulas. 4. For the balance sheet, show assets, liabilities, and owner's cquity. Include a column with the vertical analysis, rounding to 1 decimal place. Use formatting to make the sheet readable. Figure 21-3 on page 760 of the eText is a good example of how the balance sheet should be formatted. Use formulas to calculate the remaining items in the balance sheet. 5. For the income statement show revenue, operating expenses and net income. Include a column with the vertical analysis, rounding the percentages to 1 decimal place. Use formatting to make the sheet readable. Figure 21-8 on page 771 of the eText is a good example how the income statement should be formatted. Use formulas to calculate the remaining items in the income statement. 6. For the ratio analysis sheet, first calculate the Quick Current Assets using sheet cell references from your balance sheet. Then show the formulas for the current ratio and the acid-test ratio in the cells under the Formula column. Use Excel formulas to calculate the ratios in the Ratios column. Use references to the appropriate cells from the the balance sheet when calculating the ratios. Finally, include an analysis of the business; is the business in good financial shape based on these ratios and the income statement? 7. Upload your completed file to the Project 5 assignment folder by the due date. See the Schedule and Course Rubrics in the Syllabus Module for due dates and grading information