Question
The following information is for Everythings Roses, a floral shop, for the year ended 6/30/2017: Accounts Payable $ 10,000 Accounts Receivable 21,250 Accumulated Depreciation -
The following information is for Everythings Roses, a floral shop, for the year ended 6/30/2017: Accounts Payable $ 10,000 Accounts Receivable 21,250 Accumulated Depreciation - Building 11,360 Accumulated Depreciation - Office equipment 10,728 Advertising Expense 60,000 Allowance for Doubtful Accounts 213 Building 56,800 Cash 50,200 Commission Expense 45,000 Depreciation Expense - Building 5,680 Depreciation Expense - Office Equipment 5,048 Freight In 27,000 Insurance Expense 2,400 Interest Expense 508 Interest Income 350 Interest Payable 152 Land 70,000 Loans Payable (due within a year) 3,500 Merchandise Inventory - Beginning 35,400 Merchandise Inventory - Ending 51,000 Mortgage Payable 40,000 Mr. Red Rose, Capital, 7/1/2016 178,705 Mr. Red Rose, Drawing 36,000 Note Receivable 3,500 Notes Payable (due in 3 years) 5,000 Notes Payable (less than one year) 13,140 Office Equipment 25,240 Office Supplies 825 Office Supplies Expense 575 Office Wages Expense 60,000 Payroll Taxes Expense 10,502 Prepaid Insurance 2,200 Property Tax Expense 4,000 Purchases 189,600 Purchases Discounts 1,850 Purchases Returns and Allowances 2,850 Sales 489,500 Sales Discounts 4,800 Sales Returns and Allowances 9,800 Sales supplies expense 425 Sales Travel & Entertainment Expense 8,200 Telephone Expense 15,000 Uncollectible Accounts Expense 4,895 Utilities Expense 12,500 Requirements: 1. Review the worksheet called MERCH FINANCIALS. You have been asked by the owner, Mr. Red Rose, to prepare the companys 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 companys 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 possibleso 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 owners 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 Owners Equity is first. Then the income statement, and then the Balance Sheet. (Hint. Insert some lines in the new worksheet above the income statementthen 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.
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