Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please help solve this with excel formulas no hardcoding Income Statement Historicals 12/31/18 12/31/19 12/31/20 12/31/21 Revenue $11,662.4 $11,831.1 $12,453.2 $12,848.2 Growth Rate (%) (7.4%)

Please help solve this with excel formulas no hardcoding

Income Statement
Historicals
12/31/18 12/31/19 12/31/20 12/31/21
Revenue $11,662.4 $11,831.1 $12,453.2 $12,848.2
Growth Rate (%) (7.4%) 1.4% 5.3% 3.2%
COGS 6,862.6 6,869.5 7,228.8 7,485.5
Gross Profit $4,799.8 $4,961.6 $5,224.4 $5,362.7
Gross Margin (%) 41.2% 41.9% 42.0% 41.7%
Selling, General & Administrative Expense 2,102.8 2,101.1 2,088.0 2,080.2
Legal Settlement (Income) - - - - (82.7) - -
Amortization and Impairment of Intangible Assets 202.7 194.9 179.2 164.4
Operating Income $2,494.3 $2,665.7 $3,039.8 $3,118.1
Interest Expense 196.6 206.2 226.2 223.6
Other Income (Expense) 67.9 70.5 31.3 58.3
Income Before Taxes $2,365.5 $2,530.0 $2,844.9 $2,952.8
Income Taxes 713.4 759.5 1,377.2 723.0
Tax Rate (%) 30.2% 30.0% 48.4% 24.5%
Net Income $1,652.1 $1,770.5 $1,467.7 $2,229.8
Net Income Margin (%) 14.2% 15.0% 11.8% 17.4%
SG&A % Revenue 18.0% 17.8% 16.8% 16.2%
CapEx % Revenue 1.0% 2.4% 2.7% 1.9%
Balance Sheet
Historicals
12/31/18 12/31/19 12/31/20 12/31/21
ASSETS
Cash and Equivalents $2,688.3 $2,150.6 $2,691.8 $1,308.5
Accounts Receivable 2,086.3 2,116.7 2,452.5 2,435.1
Inventory 944.8 936.1 1,061.4 1,146.7
Other Current Assets 127.0 123.5 126.2 136.6
Total Current Assets $5,846.4 $5,327.0 $6,331.9 $5,026.9
Property, Plant & Equipment, Net $1,372.0 $1,437.2 $1,546.9 $1,558.2
Long-Term Investments 184.4 63.5 46.1 44.4
Goodwill 3,861.9 3,965.5 4,134.2 4,030.7
Other Intangibles 1,357.2 1,272.8 1,106.6 943.1
Deferred Tax Assets, LT 301.0 390.6 439.4 482.0
Other Long-Term Assets 761.3 768.2 993.5 851.7
Total Assets $13,684.2 $13,224.9 $14,598.6 $12,936.9
LIABILITIES
Accounts Payable $390.6 $444.6 $513.3 $455.9
Accrued Expenses 801.3 850.0 877.0 879.6
Short-Term Borrowings 456.8 1.7 738.6 0.9
Current Maturities of Long-Term Debt 217.5 565.5 217.5 1,174.5
Other Current Liabilities 410.6 539.4 526.4 570.7
Total Current Liabilities $2,276.8 $2,401.2 $2,872.7 $3,081.5
Long-Term Debt $5,926.4 $6,823.4 $7,389.8 $7,172.3
Other Non-Current Liabilities 1,076.2 874.4 1,444.2 1,775.7
Total Liabilities $9,279.4 $10,099.0 $11,706.7 $12,029.5
EQUITY
Common Stock $5.2 $5.2 $5.2 $5.2
Additional Paid-In Capital 987.5 1,033.6 1,059.7 1,090.1
Retained Earnings 15,791.4 16,389.9 16,482.2 16,531.7
Treasury Stock (11,074.2) (12,735.1) (13,538.9) (15,264.2)
Comprehensive Income and Other (1,308.5) (1,572.1) (1,119.7) (1,459.0)
Total Common Equity $4,401.3 $3,121.6 $2,888.4 $903.9
Minority Interest 3.5 4.4 3.5 3.5
Total Equity $4,404.8 $3,125.9 $2,891.9 $907.4
Total Liabilities and Equity $13,684.2 $13,224.9 $14,598.6 $12,936.9
Check - - - - - - - -
Ratio Analysis
Historicals
12/31/18 12/31/19 12/31/20 12/31/21
Operating Lease $4.4 $4.4 $4.4
Tax Expense
Capital Expenditures
Increase / (Decrease) in Working Capital 213.2 (381.1) (88.7)
Interest Expense
PPE Schedule
BOP $1,372.0 $1,437.2 $1,546.9
Depreciation
CapEx
EOP $1,372.0 $1,437.2 $1,546.9 $1,558.2
Profitability and Margin Analysis
12/31/19 12/31/20 12/31/21
Revenue Growth
Gross Margin
Operating Margin
EBITDA
EBITDA Margin
Net Income Margin
Return on Assets (ROA)
Return on Equity (ROE)
Return on Invested Capital (ROIC) Exclude Short-Term Borrowings as they are temporary, not core
Working Capital Analysis
12/31/19 12/31/20 12/31/21
Revenue
COGS
Accounts Receivable
Inventory
Accounts Payable
Days 365 days 365 days 365 days
Days Sales Outstanding (DSO)
Days Inventory Outstanding (DIO)
Days Payables Outstanding (DPO)
Cash Conversion Cycle
Leverage and Debt Analysis
12/31/19 12/31/20 12/31/21
Total Debt Total Debt: Exclude ST Borrowings as they are temporary, not core
Total Debt / Assets
Total Debt / Equity
Total Debt / EBITDA
FCCR
Liquidity Analysis
12/31/19 12/31/20 12/31/21
Current Ratio
Quick Ratio

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting concepts and applications

Authors: Albrecht Stice, Stice Swain

11th Edition

978-0538750196, 538745487, 538750197, 978-0538745482

More Books

Students also viewed these Accounting questions

Question

Engage everyone in the dialogue

Answered: 1 week ago