can you answer me this quastion in Excel please CASE STUDY Laura Smith owns a br

Question

can you answer me this quastion in Excel please

CASE STUDY Laura Smith owns a brand name clothing store. The business has been doing well and making good profit. Following is the balance sheet of the store on 31 December 2014. Laura's Clothing Store Balance Sheet 31 December 2014 Cash Shop equipment (net) Inventory Accounts receivable Total Assets Accounts payable Owner's equity, capital Total Liabilities and Owner's Equity S149,800 $17,500 44.800 66,500 21,000 S149,800 S31.500 118.300 On 2 January 2015 Laura went to her bank to get a loan for her business. The bank agreed to give her $35,000 as a three-year loan with an annual interest of $3,500. The business would repay the bank $35,000 plus $10,500 interest on 31 December 2017. Laura has come to you with the following information to help here prepare the 2015 financial statements: (a) Sales for 2015 were $560,000. Of these, half was cash sales and half credit sales. Of the credit sales, 10% were not collected until 2016. The accounts receivable on 31 December 2014 were collected in 2015. (b) Purchases of inventory for 2015 were $350,000. All purchases are on credit. Of the purchases, 12% were not paid until 2016. The accounts payable on 31 December 2014 were paid in 2015. (c) The business's gross profit percentage has been 40% of sales, this continued to be in 2015 (d) The shop rents space in a local shopping center. The rent is $1,400 per month; the rent for the whole year was paid in January 2015. (e) The shop equipment that was originally purchased for $56,000 at the beginning of 2013 has a 10-year estimated life, after which it is expected to have no value. (f) Laura pays her one salesperson a basic salary of $49,000 per year, plus 10% of gross sales. The total salary for 2015 was paid in cash by the end of the year. (g) Laura withdrew $56,000 during 2015 to cover personal living expenses (h) Other operating expenses were $11,200 in 2015; these were paid in cash by the end of the year. You determined that the information Laura has gathered is reasonable' and includes her best estimates. Required: 1-Prepare a classified income statement for 2015 and show supporting calculations. (7 marks) 2-Prepare a cash flow statement for 2015. Use the direct method for operating cash flows and show supporting calculations. (7 marks) 3-Prepare a classified balance sheet as of 31 December 2015 and show supporting calculations. (7 marks) 4-Calculate the below applicable ratios and briefly interpret each ratio. (9 marks) Ratios: 1-Current Ratio (1.5 marks) 2-Debt Ratio (1.5 marks) 3-Profit Margin (1.5 marks) 4-Return on Owner's Equity (1.5 marks) 5-Operating Cash Flow Margin (1.5 marks) 6-Cash Return on Owner's Equity (1.5 marks)

Explanation / Answer

1. Laura's Clothing Store

Income Statement

For the year ended December 31, 2015

2. Statement of Cash Flows

For the year ended December 31, 2015

3. Balance Sheet

December 31, 2015

4. Current Ratio = Current Assets / Current Liabilities = $ 182,000 / $ 42,000 = 4.33 : 1

Debt Ratio = Total Liabilities / Total Assets = $ ( 42,000 + 35,000) / $ 221,200 = 34.81 %

Profit Margin = Net Income / Sales = $ 81,900 / $ 560,000 = 14.625 %

Return on Owner's Equity = Net Income / Average Owner's Equity = $ 81,900 / $ 131,250 = 62.4 %

$ $ Income from Operations Sales 560,000 Cost of Goods Sold ( 60% of Sales) 336,000 Gross Profit 224,000 Operating Expenses: Rent Expense ( $ 1,400 x 12) 16,800 Depreciation Expense 5,600 Sales Salaries Expense 105,000 Other Operating Expenses 11,200 Total Operating Expenses 138,600 Operating Income 85,400 Other Expenses Interest Expense 3,500 Net Income 81,900

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.