FM Columbia Core I FixTheModel v24

.xlsx

School

Western University *

*We aren’t endorsed by this school

Course

12

Subject

Business

Date

Apr 3, 2024

Type

xlsx

Pages

47

Uploaded by ChancellorLorisMaster1018 on coursehero.com

Training The Street 275 Madison Avenue Suite 1201 New York, NY 10016 USA © 1999-2024 Training The Street LLC All rights reserved. +1 (800) 887-1320 (in the United States) +1 (704) 927-9688 (outside the United States) info@trainingthestreet.com
57db96231e710f5c8c789bffb2934b45324eb42a.xlsx For training purposes only! Financial Modeling Training Core I Model x Subject Company Ticker: COLM Company name: Columbia Sportswear Sub-header #1: Dollars in millions, except per share Sub-header #2: Dollars in millions Last Fiscal Year End: 31-Dec-21 Number of days in a year 365 x Color Codes Input Reference Formula Black cells are formulas. Warning Red cells are "warnings". Something unique has been done in the cell and red formatting brin x Model Features Core statements Normalized income statement Supporting schedules Cash sweep Interest calculated using average balances Circularity switch Simple equity schedule Basic ratios DCF x END <== cell is named "name" <== cell is named "Subheader" <== cell is named "Subheader2" <== cell is named "FYE" <== cell is named "Days" Blue cells are for data input. Green cells are links to different worksheets.
57db96231e710f5c8c789bffb2934b45324eb42a.xlsx For training purposes only! ngs attention to it.
57db96231e710f5c8c789bffb2934b45324eb42a.xlsx For training purposes only! Income Statement for Columbia Sportswear Dollars in millions, except per share Historical Year Ending December 31, 2019 2020 2021 x Sales $3,042.5 $2,501.6 $3,126.4 Cost of sales 1,526.8 1,277.7 1,513.9 x Gross profit 1,515.7 1,223.9 1,612.5 SG&A expenses (excludes dep. and amort.) 1,073.4 950.6 1,133.0 Net licensing (income) (15.5) (12.1) (18.4) x EBITDA 457.8 285.3 497.8 Depreciation 59.8 60.9 54.2 Amortization 3.0 2.5 1.7 x EBIT 395.0 221.9 441.9 Interest expense 0.0 0.0 0.0 Interest (income) (8.3) (0.4) (1.4) Other non-operating (income) / expense (2.2) (2.0) 0.4 x Pretax income 405.4 224.4 442.9 Income taxes 81.5 52.4 95.1 x Normalized net income $323.9 $172.0 $347.8 x Diluted weighted average shares (in millions) 68.493 66.772 66.415 x Normalized diluted earnings per share $4.73 $2.58 $5.24 Ratios & assumptions Sales growth rate (17.8%) 25.0% Gross margin 49.8% 48.9% 51.6% SG&A expenses (as a % of sales) 35.3% 38.0% 36.2% Net licensing (income) ($15.5) ($12.1) ($18.4) EBIT Margin 13.0% 8.9% 14.1% Other non-operating (income) / expense ($ amount) ($2.2) ($2.0) $0.4 Effective tax rate 20.1% 23.3% 21.5% Diluted weighted average shares (in millions) 68.493 66.772 66.415 x Comments and Non-Recurring Items Adjustments 2019 2020 2021 x Benefit of lease terminations and settlements 2021 10-K, MD&A, p. 26 and p. 28; Note 9, p. 55 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) (8.6) Total pre-tax expense (income) ($8.6) Total pre-tax expense (income) ($8.6) Implied after-tax expense (income) (6.3) Tax adjustment ($2.3)
57db96231e710f5c8c789bffb2934b45324eb42a.xlsx For training purposes only! Estimated Tax Rate 26.2% x Impairment charge on prAna trademark 2021 10-K, MD&A, pgs. 27-28, pg.32 and p.53 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) 17.5 Total pre-tax expense (income) $17.5 Total pre-tax expense (income) $17.5 Implied after-tax expense (income) 13.2 Tax adjustment $4.3 Estimated Tax Rate 24.6% x Retail impairment and store closure charges 2021 10-K, MD&A, p. 26. and p. 28 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) 28.8 Total pre-tax expense (income) $28.8 Total pre-tax expense (income) $28.8 Implied after-tax expense (income) 21.7 Tax adjustment $7.1 Estimated Tax Rate 24.6% x Bad debt expense 2021 10-K, MD&A, p. 26 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) 19.7 Total pre-tax expense (income) $19.7 Total pre-tax expense (income) $19.7 Implied after-tax expense (income) 14.9 Tax adjustment $4.8 Estimated Tax Rate 24.6% x COVID related expenses 2021 10-K, MD&A, p. 27 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) 18.9 Total pre-tax expense (income) $18.9 Total pre-tax expense (income) $18.9 Implied after-tax expense (income) 14.3 Tax adjustment $4.6 Estimated Tax Rate 24.6% x Swiss tax reform package 2019 10-K, MD&A, p. 24 Pre-tax expense (income) (COGS) $0.0 Pre-tax expense (income) (SG&A) 0.0
57db96231e710f5c8c789bffb2934b45324eb42a.xlsx For training purposes only! Total pre-tax expense (income) $0.0 Total pre-tax expense (income) $0.0 Implied after-tax expense (income) (6.6) Tax adjustment $6.6 Estimated Tax Rate 22.6% x Sensitivity Analysis "One-way, vertical table" 2022 EPS Sensitivity Tables x Err:522 47.6% Steps to complete the one-way data table 48.1% #VALUE! 1) Link the red ''Output Cell'' in cell E122 to the cell yo 48.6% #VALUE! 2) Highlight the range D122:E131 2022 49.1% #VALUE! 3) Select Data Table (Alt A W T) Gross Margin 49.6% #VALUE! 4) Skip the row input cell. Leave it blank. For the colu 50.1% #VALUE! 5) Press ENTER then F9 to calculate 50.6% #VALUE! 6) Stress test your data! 51.1% #VALUE! 51.6% #VALUE! 0.5% Step function "Two-way table" Columbia Sportswear 2021 - 2026 EPS CAGR Sensitivity Table x 2023 Sales Growth Rate Err:522 5.0% 6.0% 7.0% 8.0% 9.0% 10.0% 48.3% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 48.8% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 49.3% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 2023 49.8% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Gross Margin 50.3% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 50.8% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 51.3% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 51.8% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 52.3% #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.5% Step function 10.0% conditional formatting cell Conditional formatting: (Alt H L N) x END
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help