Answered step by step
Verified Expert Solution
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started