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)
