I need help in these 4 questions at the end of excel sheet. Please do them 100% correctly and do them in excel and show formulas. I also pasted the references to help
D15 X 258 B D 1 Notes A year ended December 31 2 Income statement as reported 3 Revenue 4 Cost of Sales (A) 5 Gross Profit C 2015 RMB million 403,616 -353,862 49,754 2014 RMB million 366,042 -326,770 39,272 E F G H 2013 2015 2014 2013 RMB million % % % 331,798 100.0% 100.0% 100.0% -297,860 -87.7% -89.3% -89.8% 33,938 12.3% 10.7% 10.2% Other income 3,000 2,414 2,054 0.7% 0.7% 0.6% 6 can be integrated in the gross operating profit mainly reflect gains on sales of assets 744 1,776 767 0.2% 0.5% 0.2% -696 Other gains -net 7 8 Selling and marketing expenses (A) 9 Administrative expenses (A) 10 Other expenses (B) 11 Opearting profit 12 Finance income 13 Finance cost, net 14 Share of profit of joint ventures 15 Share of profit of associates 16 Profit before income tax 17 Income tax expense 18 Profit for the year 19 -26,129 -875 25,798 3,701 -10,212 95 -528 -18,324 -825 23,785 3,588 -10,108 81 258 17,604 -3,721 13,883 -480 -0.2% -0.1% -0.1% -15,810 -6.5% 1-5.0% -4.8% -894 -0.2% -0.2% -0.3% 19,575 6.4% 6.5% 5.9% 2,428 0.9% 1.0% 0.7% -6,373 -2.5% -2.8% -1.9% 65 0.0% 10.0% 0.0% 157 0.1% 0.1% 0.0% 15,852 4.9% 4.8% 4.8% -3,580 -0.9% -1.0% -1.1% 12,272 3.9% 3.8% 3.7% 289 19,671 -3,758 15,913 20 21 (A) Expenses by nature (in RMB million) Notes 2015 Note 33 (AR- 2015) 117,259 121,025 38,360 16,815 10,168 5,978 2014 Note 33 (AR- 2015) 110,914 116,563 35,179 14,581 9,233 6,427 2013 Note 32 (AR- 2015) 108,670 99,363 30,580 14,273 8,642 6,479 7,732 7,248 7,573 307 22 23 RM and consumables used 24 Subcontracting costs 25 Employee benefits expenses 26 Rentals 27 Business Tax and other transaction taxes 28 Fuel Depreciation of PPE and investment 29 properties 30 Transportation costs 31 Amortisation of intangible assets 32 Amortisation of lease prepayments 33 Cost of goods sold 34 R&D costs 35 Repair and maintenance expense 36 Utilities 87 Insurance Provision for impairment of trade and other 38 receivables Provision for foreseeable losses on 89 construction contracts 281 188 1,029 561 222 9,000 7,265 3,886 1,328 1,055 1,046 535 214 7,483 3,324 1,986 1,427 1,016 4,437 3,381 1,876 1,256 844 3,163 328 1,158 1,010 762 235 B D E F G H 1 3,163 328 1,158 A Provision for impairment of trade and other 38 receivables Provision for foreseeable losses on 39 construction contracts 40 Write-down of inventories 41 Auditors' remuneration 1,010 762 235 203 59 40 328 40 40 Other expenses *In 2015 and 2014, other expenses mainly consist of survey and design expenses, , office expenses, and tendering expenses 34,732 4,732 27,113 24,239 380,687 345,622 314,150 42 Total cost of sales distribution costs and 43 admin expenses 44 45 46 (B) Other expenses (in RMB million) 2015 Note 32 (AR- 2015) 2014 Note 32 (AR- 2015) 2013 Note 31 (AR- 2015) 238 177 213 47 Depreciation and other costs relating to 48 assets being leased out Provision for impairment of available-for- 49 sale financial assets 50 Cost of sale of materials 51 Others 52 Total other expenses 150 126 127 233 298 511 521 825 875 894 298 511 875 521 825 894 51 Others 52 Total other expenses 53 54 Required: 55 a) With the help of the Notes, restate the income statement and prepare the statement of 56 intermediate balances (see Figure 14.2) in value and in percentage of sales. 57 58 b) Analyze the income statement on the basis of the format by function and subsequently 59 on the basis of the format by nature (statement of intermediate balances). Prepare an 60 analysis of the income statement and common-size income statement. Make sure that 61 you explain important changes over the years. Use ratios to support your analyses. 62 ) What are the the benefits of each analysis from the point of view of a decision-maker? 63 d) Compare China Communications Construction's performance based on the income 64 statement and common-size income statement to royal boskalis Westminster F.S.S 2013, 2014, & 2015 65 The common-size income statements for Lisinski d.o.o. are shown in Table 14.2, revealing the evolution of 2.3 Mustration the structure. Table 14.2 Common-size analysis X2 X1 X2 (in %) X1 (in %) 100.0 100.0 28,500 25,000 Net sales revenue (Change in sales) COGS (cost of goods sold) Gross margin Selling expenses General and administrative expenses Interest expenses Interest income Income before tax Income tax expense Net income -14,700 13,800 -5,500 -5,000 -745 50 2,605 -1,042 1,563 -12,000 13,000 -5,100 -5,000 -570 90 2,420 -847 1,573 14.0 -51.6 48.4 -19.3 - 17.5 -2.6 -0.2 - 48.0 52.0 -20.4 -20.0 -23 -0.4 9.1 -3.7 5.5 9.7 -3.4 6.3 "As a % of sales revenue Royal Boskalis Westminster Financial Statements 2013, 2014, &2015 64 CONSOLIDATED INCOME STATEMENT in thousands of EUR) Note 2014 2013 REVISED OPERATING INCOME Revenue Other Income 3,166,888 11,296 3,178,184 3,144,048 96,781 3,240,829 OPERATING EXPENSES Raw material, consumables, services and subcontracted work Personnel experses Other expenses Depreciation, amorfization and impairment losses - 1,774,745 -513,991 -2,034,791 -507,797 -4701 - 293,787 -2,841,076 [14/15 .293,514 2,582,250 Revaluation of stake in associated company prior to business combination Share in result of joint ventures and associated companies (ater trovation) 116 [16] 22,716 40,956 56,411 OPERATING RESULT 652,345 463 425 FINANCE INCOME AND COSTS Finance income Finance costs 110 [10 10,100 - 46,054 - 35,954 - 49,929 -45,388 Profit before laxation 616,391 418,037 Profit before taxation 616,391 418,007 Income tax experse - 124,163 - 52,756 NET GROUP PROFIT 492,228 365 281 Net group prolt atributable to: Shareholders Non controlling interests 490,290 1,938 492,228 365,691 -410 365281 Average number of shares 224 121,606,364 118,445,238 Earrings per share Diuted earnings per share 22.4 22.4 EUR 4.03 EUR 4.03 EUR 3.09 EUR 3.09 31 CONSOLIDATED INCOME STATEMENT in thousands of EUR) Note 2015 2014 OPERATING INCOME Revenue Other income 101 7 3,240,327 8,404 3,248,731 3,166,888 11,296 3,178,164 OPERATING EXPENSES Row materials, consumables, services and subcontracted work Personnel expenses Oher expenses Depreciation, amortization and impairment losses - 1,774,745 -513,991 181 19 7 [14/15 - 1,918,116 -513,088 - 4,557 - 293,091 -2,728,852 - 293,514 -2,582,250 56,411 Share in result of joint ventures and associated companies (after boxation Revaluation of stake in fugro N.V. Impoiment Fugro N.V. (161 (5) [16 43,260 28,478 - 28,824 NTS 2015 OPERATING RESULT 562.793 652,345 FINANCE INCOME AND COSTS Finance income Finance costs 110) (10) FINANCIAL STATEMENT 1,163 -32,974 - 31,811 10,100 - 46,054 -35,954 Profit before taxation 530,982 616,391 Income tax expense -87452 124,163 NET GROUP PROFIT 443,530 492 228 Net group prolt atributable to Shareholders Non controling interests 440,178 3,352 443,530 490,290 1,938 492,228 Average number of shares (224 124,181,528 121,606,364 Femings per share Diluted earnings per share 1224 224 EUR 3.54 EUR 3.54 EUR 4.00 EUR 4.00 D15 X 258 B D 1 Notes A year ended December 31 2 Income statement as reported 3 Revenue 4 Cost of Sales (A) 5 Gross Profit C 2015 RMB million 403,616 -353,862 49,754 2014 RMB million 366,042 -326,770 39,272 E F G H 2013 2015 2014 2013 RMB million % % % 331,798 100.0% 100.0% 100.0% -297,860 -87.7% -89.3% -89.8% 33,938 12.3% 10.7% 10.2% Other income 3,000 2,414 2,054 0.7% 0.7% 0.6% 6 can be integrated in the gross operating profit mainly reflect gains on sales of assets 744 1,776 767 0.2% 0.5% 0.2% -696 Other gains -net 7 8 Selling and marketing expenses (A) 9 Administrative expenses (A) 10 Other expenses (B) 11 Opearting profit 12 Finance income 13 Finance cost, net 14 Share of profit of joint ventures 15 Share of profit of associates 16 Profit before income tax 17 Income tax expense 18 Profit for the year 19 -26,129 -875 25,798 3,701 -10,212 95 -528 -18,324 -825 23,785 3,588 -10,108 81 258 17,604 -3,721 13,883 -480 -0.2% -0.1% -0.1% -15,810 -6.5% 1-5.0% -4.8% -894 -0.2% -0.2% -0.3% 19,575 6.4% 6.5% 5.9% 2,428 0.9% 1.0% 0.7% -6,373 -2.5% -2.8% -1.9% 65 0.0% 10.0% 0.0% 157 0.1% 0.1% 0.0% 15,852 4.9% 4.8% 4.8% -3,580 -0.9% -1.0% -1.1% 12,272 3.9% 3.8% 3.7% 289 19,671 -3,758 15,913 20 21 (A) Expenses by nature (in RMB million) Notes 2015 Note 33 (AR- 2015) 117,259 121,025 38,360 16,815 10,168 5,978 2014 Note 33 (AR- 2015) 110,914 116,563 35,179 14,581 9,233 6,427 2013 Note 32 (AR- 2015) 108,670 99,363 30,580 14,273 8,642 6,479 7,732 7,248 7,573 307 22 23 RM and consumables used 24 Subcontracting costs 25 Employee benefits expenses 26 Rentals 27 Business Tax and other transaction taxes 28 Fuel Depreciation of PPE and investment 29 properties 30 Transportation costs 31 Amortisation of intangible assets 32 Amortisation of lease prepayments 33 Cost of goods sold 34 R&D costs 35 Repair and maintenance expense 36 Utilities 87 Insurance Provision for impairment of trade and other 38 receivables Provision for foreseeable losses on 89 construction contracts 281 188 1,029 561 222 9,000 7,265 3,886 1,328 1,055 1,046 535 214 7,483 3,324 1,986 1,427 1,016 4,437 3,381 1,876 1,256 844 3,163 328 1,158 1,010 762 235 B D E F G H 1 3,163 328 1,158 A Provision for impairment of trade and other 38 receivables Provision for foreseeable losses on 39 construction contracts 40 Write-down of inventories 41 Auditors' remuneration 1,010 762 235 203 59 40 328 40 40 Other expenses *In 2015 and 2014, other expenses mainly consist of survey and design expenses, , office expenses, and tendering expenses 34,732 4,732 27,113 24,239 380,687 345,622 314,150 42 Total cost of sales distribution costs and 43 admin expenses 44 45 46 (B) Other expenses (in RMB million) 2015 Note 32 (AR- 2015) 2014 Note 32 (AR- 2015) 2013 Note 31 (AR- 2015) 238 177 213 47 Depreciation and other costs relating to 48 assets being leased out Provision for impairment of available-for- 49 sale financial assets 50 Cost of sale of materials 51 Others 52 Total other expenses 150 126 127 233 298 511 521 825 875 894 298 511 875 521 825 894 51 Others 52 Total other expenses 53 54 Required: 55 a) With the help of the Notes, restate the income statement and prepare the statement of 56 intermediate balances (see Figure 14.2) in value and in percentage of sales. 57 58 b) Analyze the income statement on the basis of the format by function and subsequently 59 on the basis of the format by nature (statement of intermediate balances). Prepare an 60 analysis of the income statement and common-size income statement. Make sure that 61 you explain important changes over the years. Use ratios to support your analyses. 62 ) What are the the benefits of each analysis from the point of view of a decision-maker? 63 d) Compare China Communications Construction's performance based on the income 64 statement and common-size income statement to royal boskalis Westminster F.S.S 2013, 2014, & 2015 65 The common-size income statements for Lisinski d.o.o. are shown in Table 14.2, revealing the evolution of 2.3 Mustration the structure. Table 14.2 Common-size analysis X2 X1 X2 (in %) X1 (in %) 100.0 100.0 28,500 25,000 Net sales revenue (Change in sales) COGS (cost of goods sold) Gross margin Selling expenses General and administrative expenses Interest expenses Interest income Income before tax Income tax expense Net income -14,700 13,800 -5,500 -5,000 -745 50 2,605 -1,042 1,563 -12,000 13,000 -5,100 -5,000 -570 90 2,420 -847 1,573 14.0 -51.6 48.4 -19.3 - 17.5 -2.6 -0.2 - 48.0 52.0 -20.4 -20.0 -23 -0.4 9.1 -3.7 5.5 9.7 -3.4 6.3 "As a % of sales revenue Royal Boskalis Westminster Financial Statements 2013, 2014, &2015 64 CONSOLIDATED INCOME STATEMENT in thousands of EUR) Note 2014 2013 REVISED OPERATING INCOME Revenue Other Income 3,166,888 11,296 3,178,184 3,144,048 96,781 3,240,829 OPERATING EXPENSES Raw material, consumables, services and subcontracted work Personnel experses Other expenses Depreciation, amorfization and impairment losses - 1,774,745 -513,991 -2,034,791 -507,797 -4701 - 293,787 -2,841,076 [14/15 .293,514 2,582,250 Revaluation of stake in associated company prior to business combination Share in result of joint ventures and associated companies (ater trovation) 116 [16] 22,716 40,956 56,411 OPERATING RESULT 652,345 463 425 FINANCE INCOME AND COSTS Finance income Finance costs 110 [10 10,100 - 46,054 - 35,954 - 49,929 -45,388 Profit before laxation 616,391 418,037 Profit before taxation 616,391 418,007 Income tax experse - 124,163 - 52,756 NET GROUP PROFIT 492,228 365 281 Net group prolt atributable to: Shareholders Non controlling interests 490,290 1,938 492,228 365,691 -410 365281 Average number of shares 224 121,606,364 118,445,238 Earrings per share Diuted earnings per share 22.4 22.4 EUR 4.03 EUR 4.03 EUR 3.09 EUR 3.09 31 CONSOLIDATED INCOME STATEMENT in thousands of EUR) Note 2015 2014 OPERATING INCOME Revenue Other income 101 7 3,240,327 8,404 3,248,731 3,166,888 11,296 3,178,164 OPERATING EXPENSES Row materials, consumables, services and subcontracted work Personnel expenses Oher expenses Depreciation, amortization and impairment losses - 1,774,745 -513,991 181 19 7 [14/15 - 1,918,116 -513,088 - 4,557 - 293,091 -2,728,852 - 293,514 -2,582,250 56,411 Share in result of joint ventures and associated companies (after boxation Revaluation of stake in fugro N.V. Impoiment Fugro N.V. (161 (5) [16 43,260 28,478 - 28,824 NTS 2015 OPERATING RESULT 562.793 652,345 FINANCE INCOME AND COSTS Finance income Finance costs 110) (10) FINANCIAL STATEMENT 1,163 -32,974 - 31,811 10,100 - 46,054 -35,954 Profit before taxation 530,982 616,391 Income tax expense -87452 124,163 NET GROUP PROFIT 443,530 492 228 Net group prolt atributable to Shareholders Non controling interests 440,178 3,352 443,530 490,290 1,938 492,228 Average number of shares (224 124,181,528 121,606,364 Femings per share Diluted earnings per share 1224 224 EUR 3.54 EUR 3.54 EUR 4.00 EUR 4.00
