Please show the steps(formulas) in Excel, Thank you very much!
A B D E F MERCK, COST OF DEBT rD 2017 2018 2019 u AWNE Cash and marketable securities 4 Total financial debt 5 Net debt 6 7 Interest income 8 Interest expense 9 Net interest 10 11 Implied cost of debt, rD 12 13 Interest rate earned 14 Interest rate paid 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A C D E F 1 FY 2019 12/31/2019 2 Merck & Co Inc (MRK US) - Standardized 3 4 In Millions of USD except Per Share FY 2017 FY 2018 5 12 Months Ending 12/31/2017 12/31/2018 6 Total Assets 7 + Cash, Cash Equivalents & STI 8,498.0 8,864.0 8 + Cash & Cash Equivalents 6,092.0 7,965.0 9 + ST Investments 2,406.0 899.0 10 + Accounts & Notes Receiv 6,873.0 7,071.0 11 + Accounts Receivable, Net 6,873.0 7,071.0 12 + Notes Receivable, Net 0.0 0.0 13 + Inventories 5,096.0 5,440.0 14 + Raw Materials 3,516.0 3,587.0 15 + Work In Process 0.0 0.0 16 + Finished Goods 1,334.0 1,658.0 17 + Other Inventory 246.0 195.0 18 + Other ST Assets 4,299.0 4,500.0 19 + Derivative & Hedging Assets 128.0 454.0 20 + Misc ST Assets 4,171.0 4,046.0 21 Total Current Assets 24,766.0 25,875.0 22 + Property, Plant & Equip, Net 12,439.0 13,291.0 23 + Property, Plant & Equip 29,041.0 29,615.0 24 - Accumulated Depreciation 16,602.0 16,324.0 25 + LT Investments & Receivables 12,125.0 6,233.0 26 + LT Investments 12,125.0 6,233.0 27 + Other LT Assets 38,542.0 37,238.0 28 + Total Intangible Assets 32,467.0 29,684.0 29 + Goodwill 18,284.0 18,253.0 30 + Other Intangible Assets 14,183.0 11,431.0 31 + Derivative & Hedging Assets 2.0 0.0 32 + Prepaid Pension Costs 828.0 659.0 33 + Misc LT Assets 5,245.0 6,895.0 34 Total Noncurrent Assets 63,106.0 56,762.0 35 Total Assets 87,872.0 82,637.0 36 37 Liabilities & Shareholders' Equity 38 + Payables & Accruals 15,395.0 16,819.0 39 + Accounts Payable 3,102.0 3,318.0 40 + Accrued Taxes 708.0 1,971.0 41 + Interest & Dividends Payable 1,320.0 1,458.0 42 + Other Payables & Accruals 10,265.0 10,072.0 43 + ST Debt 3,057.0 5,308.0 10,450.0 9,676.0 774.0 6,778.0 6,778.0 0.0 5,978.0 4,170.0 0.0 1,772.0 36.0 4,277.0 273.0 4,004.0 27,483.0 16,126.0 33,812.0 17,686.0 1,469.0 1,469.0 39,319.0 33,621.0 19,425.0 14, 196.0 15.0 837.0 4,846.0 56,914.0 84,397.0 18,277.0 3,738.0 736.0 1,587.0 12,216.0 3,846.0 F 1,320.0 10,265.0 3,057.0 0.0 0.0 0.0 D 1,458.0 10,072.0 5,308.0 E 1,587.0 12,216.0 3,846.0 3,610.0 236.0 0.0 0.0 0.0 0.0 236.0 ooo 3,057.0 162.0 0.0 162.0 0.0 18,614.0 21,353.0 21,353.0 0.0 0.0 5,308.0 79.0 0.0 79.0 0.0 22,206.0 19,806.0 19,806.0 0.0 0.0 21 + interesi & Dividenas Payable 42 + Other Payables & Accruals 43 + ST Debt 44 + ST Borrowings 45 + ST Lease Liabilities 46 + ST Finance Leases 47 + ST Operating Leases 48 + Current Portion of LT Debt 49 + Other ST Liabilities 50 + Deferred Revenue 51 + Derivatives & Hedging 52 + Misc ST Liabilities 53 Total Current Liabilities 54 + LT Debt 55 +LT Borrowings 56 + LT Lease Liabilities 57 +LT Finance Leases 58 + LT Operating Leases 59 + Other LT Liabilities 60 + Accrued Liabilities 61 + Pension Liabilities 62 + Deferred Revenue 63 + Deferred Tax Liabilities 64 + Derivatives & Hedging 65 + Misc LT Liabilities 66 Total Noncurrent Liabilities 67 Total Liabilities 68 + Preferred Equity and Hybrid Capital 69 + Share Capital & APIC 70 + Common Stock 71 + Additional Paid in Capital 72 - Treasury Stock 73 + Retained Earnings 74 + Other Equity 75 Equity Before Minority Interest 76 + Minority/Non Controlling Interest 77 Total Equity 78 Total Liabilities & Equity 79 80 Source: Bloomberg 13,336.0 0.0 2,701.0 0.0 2,219.0 55.0 8,361.0 34,689.0 53,303.0 0.0 41,690.0 1,788.0 39,902.0 43,794.0 41,350.0 4,910.0 34,336.0 233.0 34,569.0 87,872.0 13,743.0 0.0 2,710.0 0.0 1,702.0 81.0 9,250.0 33,549.0 55,755.0 0.0 40,596.0 1,788.0 38,808.0 50,929.0 42,579.0 -5,545.0 26,701.0 181.0 26,882.0 82,637.0 97.0 0.0 97.0 0.0 22,220.0 23,504.0 22,736.0 768.0 0.0 768.0 12,672.0 0.0 3,379.0 0.0 1,470.0 0.0 7,823.0 36,176.0 58,396.0 0.0 41,448.0 1,788.0 39,660.0 55,950.0 46,602.0 -6, 193.0 25,907.0 94.0 26,001.0 84,397.0 D E F 1 2 Merck & Co Inc (MRK US) -GAAP 3 4 In Millions of USD except Per Share FY 2017 5 12 Months Ending 12/31/2017 6 Revenue 40,122.0 7 + Sales & Services Revenue 40,122.0 8 - Cost of Revenue 12,912.0 9 + Cost of Goods & Services 12,912.0 10 Gross Profit 27,210.0 11 + Other Operating Income 0.0 12 Operating Expenses 21,189.0 13 + Selling, General & Admin 10,074.0 14 + Research & Development 10,339.0 15 + Other Operating Expense 776.0 16 Operating Income (Loss) 6,021.0 17 - Non-Operating (Income) Loss -500.0 18 + Interest Expense, Net 369.0 19 + Interest Expense 754.0 20 - Interest Income 385.0 21 + Foreign Exch (Gain) Loss -11.0 22 + (Income) Loss from Affiliates 42.0 23 + Other Non-Op Income) Loss -816.0 24 Pretax Income 6,521.0 25 - Income Tax Expense (Benefit) 4,103.0 26 + Current Income Tax 6,724.0 27 + Deferred Income Tax -2,621.0 28 + Tax Allowance/Credit 29 Income (Loss) from Cont Ops 2,418.0 30 - Net Extraordinary Losses (Gains) 0.0 31 + Discontinued Operations 0.0 32 + XO & Accounting Changes 0.0 33 Income (Loss) Incl. MI 2,418.0 34 Minority Interest 24.0 35 Net Income, GAAP 2,394.0 36 Preferred Dividends 0.0 37 - Other Adjustments 0.0 38 Net Income Avail to Common, GAAP 2,394.0 39 40 Net Income Avail to Common, Adj 10,611.0 41 Net Abnormal Losses (Gains) 8,217.0 42 Net Extraordinary Losses (Gains) 0.0 43 FY 2018 12/31/2018 42,294.0 42,294.0 13,509.0 13,509.0 28,785.0 0.0 20,486.0 10,102.0 9,752.0 632.0 8,299.0 402.0 429.0 772.0 343.0 145.0 -127.0 -849.0 8,701.0 2,508.0 3,017.0 -509.0 FY 2019 12/31/2019 46,840.0 46,840.0 14,112.0 14,112.0 32,728.0 0.0 21,125.0 10,615.0 9,872.0 638.0 11,603.0 139.0 619.0 893.0 274.0 187.0 -170.0 -497.0 11,464.0 1,687.0 2,243.0 -556.0 6,193.0 0.0 0.0 0.0 6,193.0 -27.0 6,220.0 0.0 0.0 6,220.0 9,777.0 0.0 0.0 0.0 9,777.0 -66.0 9,843.0 0.0 0.0 9,843.0 11,803.0 5,583.0 0.0 13,049.0 3,206.0 0.0 C D E 0.0 0.0 2,418.0 24.0 2,394.0 0.0 0.0 2,394.0 0.0 0.0 6,193.0 -27.0 6,220.0 0.0 0.0 6,220.0 0.0 0.0 9,777.0 -66.0 9,843.0 0.0 0.0 9,843.0 31 + Discontinued Operations 32 + XO & Accounting Changes 33 Income (Loss) Incl. MI 34 - Minority Interest 35 Net Income, GAAP 36 - Preferred Dividends 37 - Other Adjustments 38 Net Income Avail to Common, GAAP 39 40 Net Income Avail to Common, Adj 41 Net Abnormal Losses (Gains) 42 Net Extraordinary Losses (Gains) 43 44 Basic Weighted Avg Shares 45 Basic EPS, GAAP 46 Basic EPS from Cont Ops 47 Basic EPS from Cont Ops, Adjusted 48 49 Diluted Weighted Avg Shares 50 Diluted EPS, GAAP 51 Diluted EPS from Cont Ops 52 Diluted EPS from Cont Ops, Adjusted 53 10,611.0 8,217.0 0.0 11,803.0 5,583.0 0.0 13,049.0 3,206.0 0.0 2,565.0 3.84 2,730.0 0.88 0.88 3.89 2,664.0 2.34 2.34 4.43 3.84 5.09 2,748.0 0.87 0.87 3.86 2,679.0 2.32 2.32 4.40 2,580.0 3.81 3.81 5.05 A B D E F MERCK, COST OF DEBT rD 2017 2018 2019 u AWNE Cash and marketable securities 4 Total financial debt 5 Net debt 6 7 Interest income 8 Interest expense 9 Net interest 10 11 Implied cost of debt, rD 12 13 Interest rate earned 14 Interest rate paid 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A C D E F 1 FY 2019 12/31/2019 2 Merck & Co Inc (MRK US) - Standardized 3 4 In Millions of USD except Per Share FY 2017 FY 2018 5 12 Months Ending 12/31/2017 12/31/2018 6 Total Assets 7 + Cash, Cash Equivalents & STI 8,498.0 8,864.0 8 + Cash & Cash Equivalents 6,092.0 7,965.0 9 + ST Investments 2,406.0 899.0 10 + Accounts & Notes Receiv 6,873.0 7,071.0 11 + Accounts Receivable, Net 6,873.0 7,071.0 12 + Notes Receivable, Net 0.0 0.0 13 + Inventories 5,096.0 5,440.0 14 + Raw Materials 3,516.0 3,587.0 15 + Work In Process 0.0 0.0 16 + Finished Goods 1,334.0 1,658.0 17 + Other Inventory 246.0 195.0 18 + Other ST Assets 4,299.0 4,500.0 19 + Derivative & Hedging Assets 128.0 454.0 20 + Misc ST Assets 4,171.0 4,046.0 21 Total Current Assets 24,766.0 25,875.0 22 + Property, Plant & Equip, Net 12,439.0 13,291.0 23 + Property, Plant & Equip 29,041.0 29,615.0 24 - Accumulated Depreciation 16,602.0 16,324.0 25 + LT Investments & Receivables 12,125.0 6,233.0 26 + LT Investments 12,125.0 6,233.0 27 + Other LT Assets 38,542.0 37,238.0 28 + Total Intangible Assets 32,467.0 29,684.0 29 + Goodwill 18,284.0 18,253.0 30 + Other Intangible Assets 14,183.0 11,431.0 31 + Derivative & Hedging Assets 2.0 0.0 32 + Prepaid Pension Costs 828.0 659.0 33 + Misc LT Assets 5,245.0 6,895.0 34 Total Noncurrent Assets 63,106.0 56,762.0 35 Total Assets 87,872.0 82,637.0 36 37 Liabilities & Shareholders' Equity 38 + Payables & Accruals 15,395.0 16,819.0 39 + Accounts Payable 3,102.0 3,318.0 40 + Accrued Taxes 708.0 1,971.0 41 + Interest & Dividends Payable 1,320.0 1,458.0 42 + Other Payables & Accruals 10,265.0 10,072.0 43 + ST Debt 3,057.0 5,308.0 10,450.0 9,676.0 774.0 6,778.0 6,778.0 0.0 5,978.0 4,170.0 0.0 1,772.0 36.0 4,277.0 273.0 4,004.0 27,483.0 16,126.0 33,812.0 17,686.0 1,469.0 1,469.0 39,319.0 33,621.0 19,425.0 14, 196.0 15.0 837.0 4,846.0 56,914.0 84,397.0 18,277.0 3,738.0 736.0 1,587.0 12,216.0 3,846.0 F 1,320.0 10,265.0 3,057.0 0.0 0.0 0.0 D 1,458.0 10,072.0 5,308.0 E 1,587.0 12,216.0 3,846.0 3,610.0 236.0 0.0 0.0 0.0 0.0 236.0 ooo 3,057.0 162.0 0.0 162.0 0.0 18,614.0 21,353.0 21,353.0 0.0 0.0 5,308.0 79.0 0.0 79.0 0.0 22,206.0 19,806.0 19,806.0 0.0 0.0 21 + interesi & Dividenas Payable 42 + Other Payables & Accruals 43 + ST Debt 44 + ST Borrowings 45 + ST Lease Liabilities 46 + ST Finance Leases 47 + ST Operating Leases 48 + Current Portion of LT Debt 49 + Other ST Liabilities 50 + Deferred Revenue 51 + Derivatives & Hedging 52 + Misc ST Liabilities 53 Total Current Liabilities 54 + LT Debt 55 +LT Borrowings 56 + LT Lease Liabilities 57 +LT Finance Leases 58 + LT Operating Leases 59 + Other LT Liabilities 60 + Accrued Liabilities 61 + Pension Liabilities 62 + Deferred Revenue 63 + Deferred Tax Liabilities 64 + Derivatives & Hedging 65 + Misc LT Liabilities 66 Total Noncurrent Liabilities 67 Total Liabilities 68 + Preferred Equity and Hybrid Capital 69 + Share Capital & APIC 70 + Common Stock 71 + Additional Paid in Capital 72 - Treasury Stock 73 + Retained Earnings 74 + Other Equity 75 Equity Before Minority Interest 76 + Minority/Non Controlling Interest 77 Total Equity 78 Total Liabilities & Equity 79 80 Source: Bloomberg 13,336.0 0.0 2,701.0 0.0 2,219.0 55.0 8,361.0 34,689.0 53,303.0 0.0 41,690.0 1,788.0 39,902.0 43,794.0 41,350.0 4,910.0 34,336.0 233.0 34,569.0 87,872.0 13,743.0 0.0 2,710.0 0.0 1,702.0 81.0 9,250.0 33,549.0 55,755.0 0.0 40,596.0 1,788.0 38,808.0 50,929.0 42,579.0 -5,545.0 26,701.0 181.0 26,882.0 82,637.0 97.0 0.0 97.0 0.0 22,220.0 23,504.0 22,736.0 768.0 0.0 768.0 12,672.0 0.0 3,379.0 0.0 1,470.0 0.0 7,823.0 36,176.0 58,396.0 0.0 41,448.0 1,788.0 39,660.0 55,950.0 46,602.0 -6, 193.0 25,907.0 94.0 26,001.0 84,397.0 D E F 1 2 Merck & Co Inc (MRK US) -GAAP 3 4 In Millions of USD except Per Share FY 2017 5 12 Months Ending 12/31/2017 6 Revenue 40,122.0 7 + Sales & Services Revenue 40,122.0 8 - Cost of Revenue 12,912.0 9 + Cost of Goods & Services 12,912.0 10 Gross Profit 27,210.0 11 + Other Operating Income 0.0 12 Operating Expenses 21,189.0 13 + Selling, General & Admin 10,074.0 14 + Research & Development 10,339.0 15 + Other Operating Expense 776.0 16 Operating Income (Loss) 6,021.0 17 - Non-Operating (Income) Loss -500.0 18 + Interest Expense, Net 369.0 19 + Interest Expense 754.0 20 - Interest Income 385.0 21 + Foreign Exch (Gain) Loss -11.0 22 + (Income) Loss from Affiliates 42.0 23 + Other Non-Op Income) Loss -816.0 24 Pretax Income 6,521.0 25 - Income Tax Expense (Benefit) 4,103.0 26 + Current Income Tax 6,724.0 27 + Deferred Income Tax -2,621.0 28 + Tax Allowance/Credit 29 Income (Loss) from Cont Ops 2,418.0 30 - Net Extraordinary Losses (Gains) 0.0 31 + Discontinued Operations 0.0 32 + XO & Accounting Changes 0.0 33 Income (Loss) Incl. MI 2,418.0 34 Minority Interest 24.0 35 Net Income, GAAP 2,394.0 36 Preferred Dividends 0.0 37 - Other Adjustments 0.0 38 Net Income Avail to Common, GAAP 2,394.0 39 40 Net Income Avail to Common, Adj 10,611.0 41 Net Abnormal Losses (Gains) 8,217.0 42 Net Extraordinary Losses (Gains) 0.0 43 FY 2018 12/31/2018 42,294.0 42,294.0 13,509.0 13,509.0 28,785.0 0.0 20,486.0 10,102.0 9,752.0 632.0 8,299.0 402.0 429.0 772.0 343.0 145.0 -127.0 -849.0 8,701.0 2,508.0 3,017.0 -509.0 FY 2019 12/31/2019 46,840.0 46,840.0 14,112.0 14,112.0 32,728.0 0.0 21,125.0 10,615.0 9,872.0 638.0 11,603.0 139.0 619.0 893.0 274.0 187.0 -170.0 -497.0 11,464.0 1,687.0 2,243.0 -556.0 6,193.0 0.0 0.0 0.0 6,193.0 -27.0 6,220.0 0.0 0.0 6,220.0 9,777.0 0.0 0.0 0.0 9,777.0 -66.0 9,843.0 0.0 0.0 9,843.0 11,803.0 5,583.0 0.0 13,049.0 3,206.0 0.0 C D E 0.0 0.0 2,418.0 24.0 2,394.0 0.0 0.0 2,394.0 0.0 0.0 6,193.0 -27.0 6,220.0 0.0 0.0 6,220.0 0.0 0.0 9,777.0 -66.0 9,843.0 0.0 0.0 9,843.0 31 + Discontinued Operations 32 + XO & Accounting Changes 33 Income (Loss) Incl. MI 34 - Minority Interest 35 Net Income, GAAP 36 - Preferred Dividends 37 - Other Adjustments 38 Net Income Avail to Common, GAAP 39 40 Net Income Avail to Common, Adj 41 Net Abnormal Losses (Gains) 42 Net Extraordinary Losses (Gains) 43 44 Basic Weighted Avg Shares 45 Basic EPS, GAAP 46 Basic EPS from Cont Ops 47 Basic EPS from Cont Ops, Adjusted 48 49 Diluted Weighted Avg Shares 50 Diluted EPS, GAAP 51 Diluted EPS from Cont Ops 52 Diluted EPS from Cont Ops, Adjusted 53 10,611.0 8,217.0 0.0 11,803.0 5,583.0 0.0 13,049.0 3,206.0 0.0 2,565.0 3.84 2,730.0 0.88 0.88 3.89 2,664.0 2.34 2.34 4.43 3.84 5.09 2,748.0 0.87 0.87 3.86 2,679.0 2.32 2.32 4.40 2,580.0 3.81 3.81 5.05
