Question

1 Approved Answer

Posted on Sep 01, 2024

please help 1. Generate line sparklines for the main items in the income statement. 2. Calculate a common size income statement and balance sheet on

image text in transcribed please help

1. Generate line sparklines for the main items in the income statement. 2. Calculate a common size income statement and balance sheet on a table. 3. Generate line sparklines for all of the items from the common size income statement and balance sheet. 4. Based on the trends, indicate which accounts could be considered the more important and why. 5. If you were to do further analysis, which company accounts would you focus on? Why? 6. Calculate and Interpret the following ratios from Lesson 16 and 17. 7. Generate a one year income statement forecast assuming a sales growth of 5% and keeping the same income statement ratios as in the previous year. McDonald's Corp (MCD US) - GAAP 3 FY 2013 FY 2014 FY 2015 FY 2016 FY 2017 FY 2018 FY 2019 Last 12M FY 2020 Est FY 2021 Est 4 In Millions of USD except Per Share 12 Months Ending 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 03/31/2020 12/31/2020 12/31/2021 20,870.0 6 Revenue 24,621.9 21,025.2 17,836.2 21,076.5 21,076.5 7 + Sales & Services Revenue 22,820.4 22,820.4 12,199.6 24,621.9 21,025.2 - Cost of Revenue 14,417.2 10.239.2 9,961.2 + Cost of Goods & Services 12,199.6 9,961.2 14,417.2 10,204.7 10.239.2 10,786.0 0 Gross Profit 10,620.8 11,115.3 9,313.41 11,141.91 1 + Other Operating Income 0.0 0.0 0.0 0.0 1,068.1 12/31/2014 27,441.3 27.441.3 16,985.61 16,985.6 10,455.7 0.0 2,506.5 2,467.9 0.0 18.6 7,949.2 577.2 556.4 576.4 1.963.4 2,045.5 12/31/2013 28,105.71 28,105.71 17,203.0 17,203.01 10,902.7 0.0 2,138,4 2,385.6 0.0 -2472 8,764.3 559.8 512.6 527.8 15.2 0.0 2.231.3 2,200.2 2,229.4 25,413.0 25,413.0 15,623.8 15,623.8 9,789.2 0.0 2,643.7 2,434.3 0.0 209.4 7,145.5 589.8 628.8 638.3 9.5 0.0 2,460.2 2.384.5 0.0 75.7 7,744.5 0.0 0.0 0.01 236.8 -183.9 -1,163.2 9,552.7 979.2 8,822.6 9,069.8 6,562.2 8,919.71 878.5 1,051.7 2 -Operating Expenses 3 + Selling, General & Admin 4 + Research & Development 5 + Other Operating Expense -6 Operating Income (Loss) -7 - Non-Operating (Income) Loss -8 + Interest Expense, Net 9 + Interest Expense CO - Interest Income 1 + Foreign Exch (Gain) Loss 2 + (Income) Loss from Affiliates 3 + Other Non-Op (Income) Loss -4 Pretax Income 5 - Income Tax Expense (Benefit) 881.2 914.3 1.006.5 9772 981.2 1,084.9 884.8 921.3 1,121.9 20.0 3.6 7.0 37.0 0.0 0.0 26.0 5.0 48.0 -151.5 -153.8 - 2.7 38.9 175.8 168.6 6,866.0 8,573.5 7,816.1 8,018.1 5,402.4 7,704.7 2,179.5 -39.0 6,555.7 2,026.4 2,027.8 -1.4 3,381.2 1,891.8 1,992.7 6 + Current Income Tax 3,417.6 1,789.2 1.843.0 20.8 7,372.0 2,614.2 2,704.9 -90.7 0.0 4,757.8 0.0 47.2 8,204.5 2,618.6) 2,593.41 25.2 0.0 5,585.9 0.0 0.0 2.718.1 538.61 7 -36.4 102.6 + Deferred Income Tax + + Tax Allowance/Credit 149.7 8 4,529.3 4,686.5 5,192.3 4,167.00 5,926.9 5,924.3 0.01 6,025.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 9 Income (Loss) from Cont Ops 0 - Net Extraordinary Losses (Gains) -1 + Discontinued Operations 2 + XO & Accounting Changes 3 Income (Loss) Incl. MI 4 - Minority Interest 0.0 0.0 0.0 4,529.3 0.01 0.0 0.0 0.0 4,686.5 5,192.3 5,924.3 6,025.4 0.0 4,757.8 0.0 4,757.8 0.0 0.0 0.0 0.0 0.0 0.0 6,025.4 5 Net Income, GAAP 4,529.3 4,686.5 5,192.3 4,167.0 5,585.9 0.0 5,585.9 0.0 0.0 5,585.9 5,926.9 5,924.3 0.00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 4,529.3 4,757.8 4,686.5 5,192.3 5,924.3 6,025.4 4,167.0 5,926.9 4,783.1 5,435.4 5,782.7 5,938.4 4,124.0 5,585.9 0.0 5,792.0 5,843.3 4,908.51 222.0 25.3 4,682.1 152.8 0.0 243.1 6 - Preferred Dividends 7 - Other Adjustments 8 Net Income Avail to Common, GAAP , 09 o Net Income Avail to Common, Adj 1 Net Abnormal Losses (Gains) -2 Net Extraordinary Losses (Gains) 3 4 Basic Weighted Avg Shares 5 Basic EPS, GAAP 6 Basic EPS from Cont Ops -7 Basic EPS from Cont Ops, Adjusted 87.0 -141.6 0.00 0.0 0.0 0.0 0.0 0.0 998.4 939.4 807.4 778.2 758.1 744.8 5.59 6.43 7.61 7.95 5.48 980.5 4.851 4.85 4.88 854.4 5.49 5.49 5.75 4.82 4.82 4.98 5.59 6.43 7.61 7.95 5.48 7.86 7.86 7.871 5.59 6.73 7.43 7.83 5.52 8 944.6 861.2 815.5 785.6 764.9 750.7 986.3 4.82 4.80 5.44 6.37 7.86 5.48 7.86 -9 Diluted Weighted Avg Shares O Diluted EPS, GAAP , 1 Diluted EPS from Cont Ops 2 Diluted EPS from Cont Ops, Adjusted 1,006.0 5.55 5.55 5.55 7.54 7.54 4.82 5.44 6.37 7.88 5.48 7.86 4.80 4.96 4.851 5.70 6.67 7.36 7.77 5.52 7.87 3 US GAAP US GAAP 8,70121 US GAAP 9,261.0 US GAAP 10,916.1 US GAAP 10,304.6 US GAAP 12,294.5 8,330.3 10,662.7 34.24 37.61 47.83 49.01 58.33 55.31 46.70 51.09 US GAAP 10,349.4 36.82 8,850.6 8.764.3 38.79 9,688.6 9.001.7 7,263.2 7.145.5 7.870.31 7.744.5 10.902.3 9,069.8 9,552.7 8,822.6 6,562.2 8,919.7 38.52 41.45 46.54 51.30 52.74 9,593.7 34.96 8,054.4 7,949.2 38.10 28.97 17.34 65,336.43 3.28 3,216.0 31.18 28.12 31.45 41.86 41.96 43.03 36.79 4 Reference Items 5 Accounting Standard -6 EBITDA -7 EBITDA Margin (T12M) 8EBITA -9 EBIT co Gross Margin 1 Operating Margin 2 Profit Margin 3 Sales per Employee 4 Dividends per Share 5 Total Cash Common Dividends 6 Capitalized Interest Expense 7 Depreciation Expense 8 Rental Expense 9 Source: Bloomberg 42.74 19.87 28.18 28.59 23.12 28.00 17.82 60,507.14 19.03 65,658.40 22.75 97.108.09 102.812.20 3.12 3.44 3.61 3.83 100,120.00 4.191 3,256.01 4.73 5.071 5.60 3,230.0 3,058.0 3,100.0 3,582.0 14.8 9.4 7.1 5.3 5.6 7.4 3,114,6 15.6 1,498.8 1.892.6 1,390.7 1,227.5 1,302.9 1,392.2 1,539.3 1,932.31 1,438.0 1,839.00 1,814.2 1,644.5 1,521.8 1,606.8 1.606.B 1. Generate line sparklines for the main items in the income statement. 2. Calculate a common size income statement and balance sheet on a table. 3. Generate line sparklines for all of the items from the common size income statement and balance sheet. 4. Based on the trends, indicate which accounts could be considered the more important and why. 5. If you were to do further analysis, which company accounts would you focus on? Why? 6. Calculate and Interpret the following ratios from Lesson 16 and 17. 7. Generate a one year income statement forecast assuming a sales growth of 5% and keeping the same income statement ratios as in the previous year. McDonald's Corp (MCD US) - GAAP 3 FY 2013 FY 2014 FY 2015 FY 2016 FY 2017 FY 2018 FY 2019 Last 12M FY 2020 Est FY 2021 Est 4 In Millions of USD except Per Share 12 Months Ending 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 03/31/2020 12/31/2020 12/31/2021 20,870.0 6 Revenue 24,621.9 21,025.2 17,836.2 21,076.5 21,076.5 7 + Sales & Services Revenue 22,820.4 22,820.4 12,199.6 24,621.9 21,025.2 - Cost of Revenue 14,417.2 10.239.2 9,961.2 + Cost of Goods & Services 12,199.6 9,961.2 14,417.2 10,204.7 10.239.2 10,786.0 0 Gross Profit 10,620.8 11,115.3 9,313.41 11,141.91 1 + Other Operating Income 0.0 0.0 0.0 0.0 1,068.1 12/31/2014 27,441.3 27.441.3 16,985.61 16,985.6 10,455.7 0.0 2,506.5 2,467.9 0.0 18.6 7,949.2 577.2 556.4 576.4 1.963.4 2,045.5 12/31/2013 28,105.71 28,105.71 17,203.0 17,203.01 10,902.7 0.0 2,138,4 2,385.6 0.0 -2472 8,764.3 559.8 512.6 527.8 15.2 0.0 2.231.3 2,200.2 2,229.4 25,413.0 25,413.0 15,623.8 15,623.8 9,789.2 0.0 2,643.7 2,434.3 0.0 209.4 7,145.5 589.8 628.8 638.3 9.5 0.0 2,460.2 2.384.5 0.0 75.7 7,744.5 0.0 0.0 0.01 236.8 -183.9 -1,163.2 9,552.7 979.2 8,822.6 9,069.8 6,562.2 8,919.71 878.5 1,051.7 2 -Operating Expenses 3 + Selling, General & Admin 4 + Research & Development 5 + Other Operating Expense -6 Operating Income (Loss) -7 - Non-Operating (Income) Loss -8 + Interest Expense, Net 9 + Interest Expense CO - Interest Income 1 + Foreign Exch (Gain) Loss 2 + (Income) Loss from Affiliates 3 + Other Non-Op (Income) Loss -4 Pretax Income 5 - Income Tax Expense (Benefit) 881.2 914.3 1.006.5 9772 981.2 1,084.9 884.8 921.3 1,121.9 20.0 3.6 7.0 37.0 0.0 0.0 26.0 5.0 48.0 -151.5 -153.8 - 2.7 38.9 175.8 168.6 6,866.0 8,573.5 7,816.1 8,018.1 5,402.4 7,704.7 2,179.5 -39.0 6,555.7 2,026.4 2,027.8 -1.4 3,381.2 1,891.8 1,992.7 6 + Current Income Tax 3,417.6 1,789.2 1.843.0 20.8 7,372.0 2,614.2 2,704.9 -90.7 0.0 4,757.8 0.0 47.2 8,204.5 2,618.6) 2,593.41 25.2 0.0 5,585.9 0.0 0.0 2.718.1 538.61 7 -36.4 102.6 + Deferred Income Tax + + Tax Allowance/Credit 149.7 8 4,529.3 4,686.5 5,192.3 4,167.00 5,926.9 5,924.3 0.01 6,025.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 9 Income (Loss) from Cont Ops 0 - Net Extraordinary Losses (Gains) -1 + Discontinued Operations 2 + XO & Accounting Changes 3 Income (Loss) Incl. MI 4 - Minority Interest 0.0 0.0 0.0 4,529.3 0.01 0.0 0.0 0.0 4,686.5 5,192.3 5,924.3 6,025.4 0.0 4,757.8 0.0 4,757.8 0.0 0.0 0.0 0.0 0.0 0.0 6,025.4 5 Net Income, GAAP 4,529.3 4,686.5 5,192.3 4,167.0 5,585.9 0.0 5,585.9 0.0 0.0 5,585.9 5,926.9 5,924.3 0.00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 4,529.3 4,757.8 4,686.5 5,192.3 5,924.3 6,025.4 4,167.0 5,926.9 4,783.1 5,435.4 5,782.7 5,938.4 4,124.0 5,585.9 0.0 5,792.0 5,843.3 4,908.51 222.0 25.3 4,682.1 152.8 0.0 243.1 6 - Preferred Dividends 7 - Other Adjustments 8 Net Income Avail to Common, GAAP , 09 o Net Income Avail to Common, Adj 1 Net Abnormal Losses (Gains) -2 Net Extraordinary Losses (Gains) 3 4 Basic Weighted Avg Shares 5 Basic EPS, GAAP 6 Basic EPS from Cont Ops -7 Basic EPS from Cont Ops, Adjusted 87.0 -141.6 0.00 0.0 0.0 0.0 0.0 0.0 998.4 939.4 807.4 778.2 758.1 744.8 5.59 6.43 7.61 7.95 5.48 980.5 4.851 4.85 4.88 854.4 5.49 5.49 5.75 4.82 4.82 4.98 5.59 6.43 7.61 7.95 5.48 7.86 7.86 7.871 5.59 6.73 7.43 7.83 5.52 8 944.6 861.2 815.5 785.6 764.9 750.7 986.3 4.82 4.80 5.44 6.37 7.86 5.48 7.86 -9 Diluted Weighted Avg Shares O Diluted EPS, GAAP , 1 Diluted EPS from Cont Ops 2 Diluted EPS from Cont Ops, Adjusted 1,006.0 5.55 5.55 5.55 7.54 7.54 4.82 5.44 6.37 7.88 5.48 7.86 4.80 4.96 4.851 5.70 6.67 7.36 7.77 5.52 7.87 3 US GAAP US GAAP 8,70121 US GAAP 9,261.0 US GAAP 10,916.1 US GAAP 10,304.6 US GAAP 12,294.5 8,330.3 10,662.7 34.24 37.61 47.83 49.01 58.33 55.31 46.70 51.09 US GAAP 10,349.4 36.82 8,850.6 8.764.3 38.79 9,688.6 9.001.7 7,263.2 7.145.5 7.870.31 7.744.5 10.902.3 9,069.8 9,552.7 8,822.6 6,562.2 8,919.7 38.52 41.45 46.54 51.30 52.74 9,593.7 34.96 8,054.4 7,949.2 38.10 28.97 17.34 65,336.43 3.28 3,216.0 31.18 28.12 31.45 41.86 41.96 43.03 36.79 4 Reference Items 5 Accounting Standard -6 EBITDA -7 EBITDA Margin (T12M) 8EBITA -9 EBIT co Gross Margin 1 Operating Margin 2 Profit Margin 3 Sales per Employee 4 Dividends per Share 5 Total Cash Common Dividends 6 Capitalized Interest Expense 7 Depreciation Expense 8 Rental Expense 9 Source: Bloomberg 42.74 19.87 28.18 28.59 23.12 28.00 17.82 60,507.14 19.03 65,658.40 22.75 97.108.09 102.812.20 3.12 3.44 3.61 3.83 100,120.00 4.191 3,256.01 4.73 5.071 5.60 3,230.0 3,058.0 3,100.0 3,582.0 14.8 9.4 7.1 5.3 5.6 7.4 3,114,6 15.6 1,498.8 1.892.6 1,390.7 1,227.5 1,302.9 1,392.2 1,539.3 1,932.31 1,438.0 1,839.00 1,814.2 1,644.5 1,521.8 1,606.8 1.606.B