Problem 5 - Merchandising Company Financial Statements (MERCH FINANCIALS) Learning Objectives: Prepare financial statements for a merchandising firm. Compute basic ratios to assess company's performance Alter the Worksheet to rearrange the order of the financial statements Create a chart showing the amount of all selling expenses Problem Data The following information is for Everything's Roses, a floral shop, for the year ended 6/30/2017: Accounts Payable Accounts Receivable Accumulated Depreciation - Building Accumulated Depreciation - Office equipment Advertising Expense Allowance for Doubtful Accounts Building Cash Commission Expense Depreciation Expense - Building Depreciation Expense - Office Equipment Freight in Insurance Expense Interest Expense Interest Income Interest Payable Land Loans Payable (due within a year) Merchandise Inventory - Beginning Merchandise Inventory - Ending Mortgage Payable Mr. Red Rose, Capital, 7/1/2016 Mr. Red Rose, Drawing Note Receivable $ 10,000 21,250 11,360 10,728 60,000 213 56,800 50,200 45,000 5,680 5,048 27,000 2,400 508 350 152 70,000 3,500 35,400 51,000 40,000 178,705 36,000 3,500 Notes Payable (due in 3 years) Notes Payable (less than one year) Office Equipment Office Supplies Office Supplies Expense Office Wages Expense Payroll Taxes Expense Prepaid Insurance Property Tax Expense Purchases Purchases Discounts Purchases Returns and Allowances Sales Sales Discounts Sales Returns and Allowances Sales supplies expense Sales Travel & Entertainment Expense Telephone Expense Uncollectible Accounts Expense Utilities Expense 5,000 13,140 25,240 825 575 60,000 10,502 2,200 4,000 189,600 1,850 2,850 489,500 4,800 9,800 425 8,200 15,000 4,895 12,500 Requirements: 1. Review the worksheet called MERCH FINANCIALS. You have been asked by the owner, Mr. Red Rose, to prepare the company's financial statements using this file. Note that the data from the problem above has already been entered into the top section of the worksheet. As you enter the data, some calculations and cells that contain #VALUE will be replaced by values. 2. Open the file MERCH FINANCIALS. Enter all formulas and titles where indicated. For example, Title A is Sales Discounts, which can be referenced to cell =A44 and Formula 2 is =E44. Check your work. Put the appropriate header on your worksheet prior to printing. Print the worksheet, breaking it in appropriate places. (DO NOT SPLIT A FINANCIAL STATEMENT IN THE MIDDLE.) Check Figure: Total Assets (Cell G143) is $258,714. Save the file as MERCH FINANCIALS2. 3. RATIO ANALYSIS: The owner of the company is concerned about the company's performance. Compute the following ratios on ANOTHER WORKSHEET within the workbook. Show the formula in words and in dollars, labeling all parts of the computation. The new worksheet should be cell referenced to the other worksheets as much as possible...so that if any changes are made, your ratios will automatically be updated. COMMENT ON THE RESULTS OF THE RATIOS. (This section will be worth some substantial points.) Save the file as MERCH FINANCIALS3. Put the appropriate header on your worksheet prior to printing. Make the worksheet presentable using proper formats. Print the worksheet that you just created. It should fit neatly on one page. The ratios to be computed are: a. Current Ratio b. Quick or Acid-test Ratio c. Gross Profit percentage d. Net Profit percentage e. Accounts Receivable Turnover f. Inventory Turnover 4. MANIPULATE THE ORDER: Open up a new tab (within the same file) called "Manipulation." Copy the financial statement data from the original template over to the new tab. The owner would like to see the statement of owner's equity FIRST!!! Rearrange the order of the financial statements. (DO NOT JUST PRINT THEM IN THAT ORDER. Rather ADJUST THE WORKSHEET SO that the Statement of Owner's Equity is first. Then the income statement, and then the Balance Sheet. (Hint. Insert some lines in the new worksheet above the income statement...then CUT and PASTE.) Save your work as MERCH FINANCIALS4. 5. FORMULAS: Create a new tab within the file. Call it "Financials Formulas". Choose either file (either the normal order or the adjusted order). Copy the contents over to the new tab. Then use Control Tilde to show the formulas. Widen the columns as necessary. Re-Save the file. Also create another new tab within the file and call it "Ratio Formulas." Copy the contents of the Ratio analysis to this new tab. Then use Control Tilde to show the formulas. Widen any columns as necessary. I should be able to see all of your formulas. Re-save the file. 6. CHART: Open a new chart tab. Call it "Chart." Create a pie chart showing the breakdown of the selling expenses. Give the chart a 3-line title. Include the percentage breakdown by placing appropriate data labels on the project. (Don't forget the 3-line title.) Re-save your work Upload your File : After reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as MERCH FINANCIALS FINAL + Your last name. Upload the final file using Canvas to me for grading. Summary of What's Due: (I will be looking at your one submitted file. However, make sure that your work is set up to print out correctly. These are the "Printouts" that I will be reviewing in your final file.) Printout of completed template including all the financial statements. . Printout of formulas using Control of all workbooks. Printout of financial statements in adjusted order. Printout of answers of ration analysis in requirement #3. Printout of Chart