FM Columbia Core I FixTheModel v24
.xlsx
keyboard_arrow_up
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
Related Questions
80) Freedom of association and the effective recognition of the right to collective bargaining are principles for doing business globally in the area of ________. A) anticorruption B) human rights C) labor D) the environment 81) The Global Compact principle that businesses should undertake initiatives to promote greater environmental responsibility, is a principle for doing business globally in the area of ________. A) anticorruption B) human rights C) labor D) the environment 82) Which of the following is nota way for management to reduce unethical behavior? A) select individuals with high ethical standards B) establish codes of conduct C) provide ethics training D) monitor employee telephone calls 83) A ________ is a formal statement of an organization’s primary values and the ethical rules it expects its employees to follow. A) mission statement B) statement of purpose C) code of ethics D) vision statement 84) The primary debate about ethics training programs is whether…
arrow_forward
In the BP Deepwater Horizon case, the company's executives made some unethical decisions that led to the explosion and destruction of the oil rig. Which of the following may have incentivized the executives to make these decisions?
a.
The Deepwater Horizon project was one of many rigs BP was operating in the Gulf of Mexico, as the company had 650 leases
b.
BP had a reputation as a pioneer in the industry and deepwater drilling in the Gulf of Mexico was considered the frontier
c.
BP had a diverse group of shareholders located around the world
d.
BP did not own the oil rig - they were renting it from Transocean
arrow_forward
In Ghana today, by a mere association with a person who has engaged in some
wrong doings or may be not all, you can easily be accused of being part. As an
engineer you have been taught not be judgmental on issues until you have all the
facts. With this background knowledge, explain conflict of interest and apparent
conflict of interest as related to a specific named issue.
arrow_forward
The difference between local NGOs and international NGOs is that the activities of international NGOs are not confined to a single country. True or False?
A. True
B. False
arrow_forward
Please see and determine if the link for news report below discusses an incompetent physician or dentist and action taken project
https://www.google.com/amp/s/amp.theguardian.com/us-news/2022/mar/16/wisconsin-dentist-guilty-damaging-patients-teeth-boost-profits
https://www.google.com/amp/s/amp.theguardian.com/us-news/2022/mar/16/wisconsin-dentist-guilty-damaging-patients-teeth-boost-profits
arrow_forward
50-
Among the following option, which one is the concept of Ethics?
a.
The wrongful act of children
b.
The financial status of human
c.
Right and wrong conduct
d.
The profitability of a business
arrow_forward
Subject: Management
Course: Managing Gender, Equality, Diversity and Inclusion
Topic: Cultural Differences in the Caribbean (Article by Geert Hofstede)
1. Select 3 other Caribbean countries, and compare the cultural dimesnsions of tresidents from those countries. What are the cultural dimensions of residents from those countries. What are the similarities and differencies in cultural dimensions?
How some of these similarities and differencies might influence how easy or difficult it will be to achieve social equity in those nations?
arrow_forward
32
“Government policy” is an example of the ( ) and “customers” is an example of the ( ) environment
A) specific environment; general environment
B)general environment; general environment
C)general environment; specific environment
D) outside environment; company environment
E) outside environment; inside environment
arrow_forward
Enviro is an international environmental engineering firm that is launching a project to market a new fuel-efficient car. In order to initiate the project, senior executives at headquarters must come to an agreement on the staffing framework to organize the overseas units that will take responsibility for international sales.
One executive, Sim, believes that this project calls for an ethnocentric staffing framework in which home-country nationals are sent as expatriates to countries around the world to establish market bases.
Another executive, Patil, thinks that a polycentric staffing framework, in which host-country nationals establish market bases in their own countries as local managers, would be more effective.The executives should consider which of the following questions before making a framework decision?
A) Which type of framework best serves the needs of the project?
B) What type of framework has the company historically used?
C) Will the marketing team operate…
arrow_forward
Sue has recently started working for a Swiss bank in London as a wealth manager. Through a business acquaintance, a lawyer, she has been introduced to a new client, Maria. Maria was born in Russia, lives in the UK and has recently inherited a significant amount of money from her father who is a Russian businessman. Maria is 27 years old, single and not working, spending a lot of money in socialising and charity work.
Sue has exchanged a number of emails with the client, and they have agreed to meet for a first introductory meeting at the bank’s offices in Mayfair.
Outline how Sue should prepare for the meeting and discuss the areas that Sue should aim to discuss with the client in their meeting.
arrow_forward
The Vale Mining Company Dam Collapse...
2. What ethical safeguards were present at Vale at the time of the dam collapse? Why did they not prevent the dam collapse?
arrow_forward
Consider the below scenario “ Mr. Ahmed the manager of CISCO protects personal privacy and proprietary of Mr. Ali who is an employee working in CISCO “ , Name this security service.
a.
Confidentiality
b.
Authentication
c.
Availability
d.
Integrity
arrow_forward
Mary discovers that her plant (factory) is discharging a substance into the river that is not regulated by the government. She decides to do some reading about the substance and finds that some of the studies suggest that it is carcinogen. As an engineer, she believes she has an obligation to protect the public, but she also wants to be a loyal employee. The substance will probably be very expensive to remove, and her boss advises, “Forget about it until the government makes us do something. Then all the other plants will have to spend money too, and we will not be at a competitive disadvantage.” What should Mary do?
arrow_forward
2.You have been asked by the CEO of your software organization to hire and manage a small group of software developers in an attempt to reverse engineer the latest release of the software by your leading competitor. The goal of the group will be to identify features that could be implemented into the next few releases of your firm's software. Would you consider hiring software developers from your competitors to start this group? Why or why not? What sort of legal and/or ethical questions might be raised by this reverse engineering effort?
arrow_forward
In 1979, law student in UK, Tim Martin decided he wanted to own a pub. Unlike most students with the same ambition, Martin actually went ahead and bought the pub he usually drank in. From the beginning, Martin decided that Wetherspoon’s was going to be different from the other pubs around.
For one thing, Wetherspoon’s has no music. There is no juke box, no live bands, and no piped music anywhere in any Wetherspoon’s pub. Secondly, Wetherspoon’s has a wider range of beers than do most pubs – and it is the beer that makes the profits. Wetherspoon’s operate by keeping the price of the beer relatively low, but offering a quiet atmosphere, no-smoking areas and all-day food. Each pub has its own name, but operates under the overall Wetherspoon brand: the pub name and the company name appear prominently on each of the 640 Wetherspoon pubs in Britain. The company was floated on the London Stock Exchange in 1992, and continues to expand throughout the UK. Each of these pubs have the same…
arrow_forward
1- what is the meaning of International human research management?
2- what is the definition of Resilience in International human research management?
3- How does International human research management use resilience in recruitment and selection?
4- How does International human research management use resilience in training and development?
5- How does International human research management use resilience in compensation and benefit?
6- How does International human research management use resilience in performance appraisal ?
arrow_forward
Examine the Labour Laws in Montserrat in the West Indies relating to termination of the employment contract. Doyou think that they are fair? (Cite at least 4 case laws to support your answer using the IRACprinciple). Case laws must be from any Country in the Caribbean.
arrow_forward
Management Ethics
The man was sentenced to the death penalty for killing a person who rape his sister
Based on the statement why it is illegal but ethical?
arrow_forward
3, Employees in most companies are allowed to take smoking breaks, historically for cigarettes, but what if now they want to include breaks to smoke pot, in states where it is legal? Assume you own the company, does that change your view?
arrow_forward
In some cases, especially when organizations expand into foreign countries, they experience a form of culture shock when the laws of their new host country conflict with their internal policies. Suppose that SLS has expanded its operations in France. Setting aside any legal requirement that SLS makes its policies conform to French law, does SLS have an ethical imperative to modify its policies to better meet the needs of its stakeholders in the new country?
Suppose SLS has altered its policies for all operations in France and that the changes are much more favorable to employees-such as a requirement to provide child and elder-care services at no cost to the employee. Is SLS under and ethical burden to offer the same benefits to employees in its original country?
arrow_forward
Headspace Pty Ltd is an IT company that believes in serving the local community. As part of its corporate social responsibility, Headspace has decided to recruit employees of refugee backgrounds, who have been struggling to find meaningful employment in Australia. Headspace consists of mostly white male staff, and it is their first time hiring refugees. These refugee employees speak an intermediate level of English, and have Bachelor degrees in IT obtained from universities in Afghanistan. All of the refugee employees have experience working in the IT industry back in their home country. The CEO of Headspace, Alan, knows that some existing employees have biased perceptions towards this decision to hire refugee employees. Alan asks you, the HR manager, to prepare a training program to help refugees integrate into the organisation.
Provide at least three recommendations on how you will design and deliver the training program. The targeted audience of the training program can be current…
arrow_forward
Penny is seen organizing racks at a local clothing store one day while she shops, and the manager offers her a job. The next day, Penny’s boss from the clothing store across the street comes in and accuses the manager of trying to interfere with Penny’s non-competition agreement. Which of the following is true?
The manager has done nothing wrong because non-competition agreements are never used in clothing retail.
The manager is guilty of interference with a business relationship.
The manager has interfered with a contractual relationship and will be liable for interfering with a contractual relationship.
The manager has done nothing wrong because she did not know about the non-competition agreement.
arrow_forward
Is Government Support for External Whistle-Blowing Effective?
A number of laws have been enacted to encourage members of organizations to report misconduct. While most firms support internal reporting of misconduct through anonymous hotlines, many organizations are concerned about employees going public or reporting misconduct to the government. Whistle-blowers are protected through the Sarbanes–Oxley Act and a number of other government agencies that deal with fraud, stock trading, and corrupt practices. In 2010 the Dodd–Frank Wall Street Reform and Consumer Protection Act gave additional incentives for whistle-blowers. Whistle-blowers are encouraged to turn themselves in if they were part of a team or group that engaged in misconduct, and doing so could result in monetary rewards. Despite these incentives, whistle-blowers in general do not get good treatment and often have trouble finding employment after they report misconduct. It has also been found that companies with good…
arrow_forward
Which hp laptops (within the last 5 years) faced any liability in tort and criminal penalties?
arrow_forward
1:- A situation that involves a choice between right action versus wrong action is called:
Option-
a. A moral temptation
b. A conundrum
c. A value judgement
d. An ethical dilemma
2:-Morality that is based on cultural beliefs and includes things such as laws and customs describes what type of morality?
Option-
a. Professional
b. Societal
c. Personal
d. Virtue
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Related Questions
- 80) Freedom of association and the effective recognition of the right to collective bargaining are principles for doing business globally in the area of ________. A) anticorruption B) human rights C) labor D) the environment 81) The Global Compact principle that businesses should undertake initiatives to promote greater environmental responsibility, is a principle for doing business globally in the area of ________. A) anticorruption B) human rights C) labor D) the environment 82) Which of the following is nota way for management to reduce unethical behavior? A) select individuals with high ethical standards B) establish codes of conduct C) provide ethics training D) monitor employee telephone calls 83) A ________ is a formal statement of an organization’s primary values and the ethical rules it expects its employees to follow. A) mission statement B) statement of purpose C) code of ethics D) vision statement 84) The primary debate about ethics training programs is whether…arrow_forwardIn the BP Deepwater Horizon case, the company's executives made some unethical decisions that led to the explosion and destruction of the oil rig. Which of the following may have incentivized the executives to make these decisions? a. The Deepwater Horizon project was one of many rigs BP was operating in the Gulf of Mexico, as the company had 650 leases b. BP had a reputation as a pioneer in the industry and deepwater drilling in the Gulf of Mexico was considered the frontier c. BP had a diverse group of shareholders located around the world d. BP did not own the oil rig - they were renting it from Transoceanarrow_forwardIn Ghana today, by a mere association with a person who has engaged in some wrong doings or may be not all, you can easily be accused of being part. As an engineer you have been taught not be judgmental on issues until you have all the facts. With this background knowledge, explain conflict of interest and apparent conflict of interest as related to a specific named issue.arrow_forward
- The difference between local NGOs and international NGOs is that the activities of international NGOs are not confined to a single country. True or False? A. True B. Falsearrow_forwardPlease see and determine if the link for news report below discusses an incompetent physician or dentist and action taken project https://www.google.com/amp/s/amp.theguardian.com/us-news/2022/mar/16/wisconsin-dentist-guilty-damaging-patients-teeth-boost-profits https://www.google.com/amp/s/amp.theguardian.com/us-news/2022/mar/16/wisconsin-dentist-guilty-damaging-patients-teeth-boost-profitsarrow_forward50- Among the following option, which one is the concept of Ethics? a. The wrongful act of children b. The financial status of human c. Right and wrong conduct d. The profitability of a businessarrow_forward
- Subject: Management Course: Managing Gender, Equality, Diversity and Inclusion Topic: Cultural Differences in the Caribbean (Article by Geert Hofstede) 1. Select 3 other Caribbean countries, and compare the cultural dimesnsions of tresidents from those countries. What are the cultural dimensions of residents from those countries. What are the similarities and differencies in cultural dimensions? How some of these similarities and differencies might influence how easy or difficult it will be to achieve social equity in those nations?arrow_forward32 “Government policy” is an example of the ( ) and “customers” is an example of the ( ) environment A) specific environment; general environment B)general environment; general environment C)general environment; specific environment D) outside environment; company environment E) outside environment; inside environmentarrow_forwardEnviro is an international environmental engineering firm that is launching a project to market a new fuel-efficient car. In order to initiate the project, senior executives at headquarters must come to an agreement on the staffing framework to organize the overseas units that will take responsibility for international sales. One executive, Sim, believes that this project calls for an ethnocentric staffing framework in which home-country nationals are sent as expatriates to countries around the world to establish market bases. Another executive, Patil, thinks that a polycentric staffing framework, in which host-country nationals establish market bases in their own countries as local managers, would be more effective.The executives should consider which of the following questions before making a framework decision? A) Which type of framework best serves the needs of the project? B) What type of framework has the company historically used? C) Will the marketing team operate…arrow_forward
- Sue has recently started working for a Swiss bank in London as a wealth manager. Through a business acquaintance, a lawyer, she has been introduced to a new client, Maria. Maria was born in Russia, lives in the UK and has recently inherited a significant amount of money from her father who is a Russian businessman. Maria is 27 years old, single and not working, spending a lot of money in socialising and charity work. Sue has exchanged a number of emails with the client, and they have agreed to meet for a first introductory meeting at the bank’s offices in Mayfair. Outline how Sue should prepare for the meeting and discuss the areas that Sue should aim to discuss with the client in their meeting.arrow_forwardThe Vale Mining Company Dam Collapse... 2. What ethical safeguards were present at Vale at the time of the dam collapse? Why did they not prevent the dam collapse?arrow_forwardConsider the below scenario “ Mr. Ahmed the manager of CISCO protects personal privacy and proprietary of Mr. Ali who is an employee working in CISCO “ , Name this security service. a. Confidentiality b. Authentication c. Availability d. Integrityarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you