If you are an analyst, and your manager asked you to analyse company XYZ using the following financial analysis techniques. The income statement and balance sheet of company XYZ are provided in the Excel document. Round off to two (2) decimal points for the financial statement analysis calculation. On top of correctness of the resulting computation and analysis, the tidiness and clarity of your presentation will also be considered in evaluating your answers. (a) Construct a vertical common-size income statement to evaluate the changes in profitability of company XYZ over the three years. Specifically, present the vertical common-size income statement of XYZ in Cell F1:I13 with year 2018, 2017, and 2016 in Column G, H, and I, respectively. Display the formula for Net Income in Cell G14:I14. Based on the vertical common-size income statement, illustrate what contributed to the changes in company XYZs profitability in 2018. From the information, which items on the income statement need further investigation before we can elaborate the change in profitability of XYZ? (10 marks) (b) Based on the balance sheet, calculate the cash ratio, quick ratio and current ratio for 2018, 2017, and 2016. Display the results in cell F17:I20. According to the liquidity ratios above, how well is the ability of company XYZ to pay its short-term debt? (8 marks) (c) For company XYZ, how do the receivables turnover, days of sales outstanding, inventory turnover, and days of inventory on hand change from 2017 to 2018? If the industry norm for receivables turnover and inventory turnover is 1.5 and 1.4, respectively, how efficiently XYZ is managing its assets? Based on the results, determine the possible actions that XYZ can take to boost sales. (7 marks)

Income Statement of XYZ (in million USD) 2018 2017 2016 Balance Sheet of XYZ 2018 2017 2016 2,890 1.205 1,685 1.888 672 50 2,015 173 3,452 1,490 1,962 745 134 21 261 801 123 678 3,023 1.278 1,745 731 185 92 198 539 2 Revenues 3 Cost of goods sold 4 Gross profit 5 Selling, general & administrative expenses 6 Depreciation 7 Amortization 8 Other operating expenses 9 Operating profit 10 Interests 11 Income before taxes 12 Taxes 13 Net income 1.800 300 2.340 1,108 5,548 415 5,963 652 4,605 2,020 50 1.803 568 4,441 380 4,821 79 Assets Cash and cash equivalents Marketable securities Accounts receivable Inventories Total current assets Property, plant and equipment Total assets Liabilities Short-term debts Notes payable Accounts payable Total current liabilities Long-term debts 399 189 572 5,004 145 161 324 223 427 189 238 378 201 177 120 548 289 141 537 967 127 274 156 498 455 992 928 142 130 Total liabilities 1,134 1,094 1,058 Contributed capital Retained earnings Total equity Total equity and liabilities 1,776 3,053 4,829 5,963 1.129 2.598 3,727 4,821 1,586 2,360 3,946 5,004 Income Statement of XYZ (in million USD) 2018 2017 2016 Balance Sheet of XYZ 2018 2017 2016 2,890 1.205 1,685 1.888 672 50 2,015 173 3,452 1,490 1,962 745 134 21 261 801 123 678 3,023 1.278 1,745 731 185 92 198 539 2 Revenues 3 Cost of goods sold 4 Gross profit 5 Selling, general & administrative expenses 6 Depreciation 7 Amortization 8 Other operating expenses 9 Operating profit 10 Interests 11 Income before taxes 12 Taxes 13 Net income 1.800 300 2.340 1,108 5,548 415 5,963 652 4,605 2,020 50 1.803 568 4,441 380 4,821 79 Assets Cash and cash equivalents Marketable securities Accounts receivable Inventories Total current assets Property, plant and equipment Total assets Liabilities Short-term debts Notes payable Accounts payable Total current liabilities Long-term debts 399 189 572 5,004 145 161 324 223 427 189 238 378 201 177 120 548 289 141 537 967 127 274 156 498 455 992 928 142 130 Total liabilities 1,134 1,094 1,058 Contributed capital Retained earnings Total equity Total equity and liabilities 1,776 3,053 4,829 5,963 1.129 2.598 3,727 4,821 1,586 2,360 3,946 5,004