Profitability & 5 Forces Assignment
The output for this exercise should be createdindividually, but you should feel free to seek technical help (Excel) and feedback on your thinking. Please turn in:
• Hardcopy of your summary tables (Step 3), your Porter's Five Force Analysis (Step 5), and your summary note. (You may alter tables & charts, and add your analysis below so that there is less formatting involved.)
• Digital copy (via email) of your hardcopy submission and also your Excel spreadsheet.
Assignment is due on Friday, June3rd. Please bring hardcopies for your project teammates to that class.
In this exercise, you will analyze various measures of profitability across firms and industries over time. The goals of the exercise are (1) to develop an understanding of what constitutes average, below-average and above-average performance at both the firm and industry level; (2) to build your familiarity with the North American Industrial classification (NAICS) system; (3) to give you a better sense of the overall distribution of various measures of economic performance; (4) to improve your data analysis skills and understanding of the DuPont formulas; (5) understand and apply five forces analysis framework; and (6) get started on the Team Project.
We have created a custom data set for this assignment. The data is in an Excel workbook within the same syllabus entry. The sample consists of 134,024 firm-year observations for all US public companies in the Compustat database between 1990 and 2010 with Sales of at least $5 million (in 2012 US dollars) and no missing data on Sales, Net Income, Total Assets or Shareholders Equity. There are 14 variables in the data set, as described in the following table (shown on next page)
Assignment
Step 1: Identify a Target Industry
Your analysis will be on a single industry, based on either a 4-digit or 6-digit NAICS code. It is likely easiest to start by finding a firm of interest to discover its NAICS code. The choice of 4-digit or 6-digit code depends on how specific you may need to be in order to have reasonably similar companies. 4-digits gives you more firms, but they may be in noticeably different businesses. 6-digits yields fewer firms, but they are more likely to be relevant competitors. Example: 4451 is sufficient to identify grocery stores and 4811 for airlines. 325620 may be needed for Cosmetics (3256 includes P&G).
When you get to Steps 5&6 (Five Forces), you may want to further narrow the industry. If so, please explain your definition of the industry you are analyzing.
Field Descriptions :
|
Name
|
Type
|
Description
|
|
id
|
string
|
Unique company identifier
|
|
ticker
|
string
|
Ticker symbol
|
|
year
|
numeric
|
Fiscal Year
|
|
sales
|
numeric
|
Total Sales ($millions)
|
|
netinc
|
numeric
|
Net Income - After Tax ($millions)
|
|
assets
|
numeric
|
Total Assets ($millions)
|
|
equity
|
numeric
|
Shareholders Equity ($millions)
|
|
naics
|
numeric
|
NAICS code - 6 digit or best available
|
|
naics2
|
string
|
2-digit NAICS category*
|
|
INDUSTRY ID FOR CLASS
|
numeric
|
Code 1-19 to assign teams for exercise
|
|
naics3
|
numeric
|
NAICS code - 3 digit
|
|
naics4
|
numeric
|
NAICS code - 4 digit
|
|
name
|
string
|
Company Name (may change within id)
|
|
salcpi12
|
numeric
|
Sales in 2012 dollars (based on CPI)
|
Note: the Filter feature under the Tools menu is a good way to select the set of firms you will use in your analysis. While you can do your further analysis in the original spreadsheet, we recommend you copy and paste the subset of data for your analysis into a new (and smaller) spreadsheet. Include all the years for all the firms in your selected industry.
Step 2: Calculate Performance Measures for the Industry
Create the three profitability measures used in the DuPont formula -- Return on Sales (ROS), Return on Assets (ROA) and Return on Equity (ROE) - and the two "conversion factors" -- Asset Turnover and Leverage - for each row in your spreadsheet (row=data for single firm/year). Also calculate the Industry average ROS, ROA and ROE, Asset Turnover and Leverage for every year. (Hint: the Excel SUBTOTAL function might be useful.)
Summarize your industry findings in a table of the form and highlight any noticeable variations over time.
|
Year
|
ROS
|
ROA
|
ROE
|
Asset Turn.
|
Leverage
|
|
2010
|
|
|
|
|
|
|
...
|
|
|
|
|
|
|
1990
|
|
|
|
|
|
Step 3: Examine the Distribution of Profits for 2010 using ROS and ROA
Now look only at the data for 2010 (hint: Filter)!.If you did not already do it in Step 2, calculate the ROS and ROA for each for your industry. Evaluate the range of profitability within the industry, find the mean, maximum and minimum ROS and ROA in your industry; note the names of the firms with these max and min parameters.
Put this information into a table (e.g. below).
|
NAICS
(4/6 digit)
|
Mean ROS 2010
|
Max ROS 2010
|
Min ROS 2010
|
Mean ROA 2010
|
Max ROA 2010
|
Min ROA 2010
|
|
4451
|
0.65%
|
5.3%
|
-7.4%
|
1.92%
|
14.1%
|
-22.6%
|
|
|
|
PUBLIX SUPER MARKETS INC
|
GREAT ATLANTIC & PAC TEA CO
|
|
ARDEN GROUP INC -CL A
|
GREAT ATLANTIC & PAC TEA CO
|
Step 4:Examine the Industry Leaders (by Sales in 2010)
Using only 2010 data, create a table of the top 5 (can include more than 5) firms by Sales, showing Sales, Net Income, Assets, Equity, ROS and ROA. Example for 4451:
|
|
Sales
|
Net Inc
|
Assets
|
Equity
|
ROS
|
ROA
|
|
KROGER CO
|
82,189
|
1,116
|
23,505
|
5,296
|
1.36%
|
4.75%
|
|
SAFEWAY INC
|
41,050
|
590
|
15,148
|
4,993
|
1.44%
|
3.89%
|
|
SUPERVALU INC
|
37,572
|
(1,510)
|
13,758
|
1,340
|
-4.02%
|
-10.98%
|
|
PUBLIX SUPER MARKETS INC
|
25,328
|
1,338
|
10,159
|
7,261
|
5.28%
|
13.17%
|
|
WHOLE FOODS MARKET INC
|
9,006
|
246
|
3,987
|
2,373
|
2.73%
|
6.17%
|
You may include other columns you think are relevant to your industry. For 4451 (Grocery Stores), the Asset Turnover might be a relevant column to include.
Step 5: Porter Five Forces Analysis:
Perform a Five Forces analysis for your firm's industry. (Note: I am testing a new format for this assignment)
For each of the Five Forces, create a table with the structure below to show your assessment of the factors with the most significant impact on industry profitability. Focus on 3 to 5 factors for each force. You may also wish to include factors that are widely assumed to limit profitability but that you think are actually weak.
Force: _____________________
|
External Pressure
|
Industry counter-pressure
|
Net effect on Profitability
|
|
General factors from Porter with details specific to your industry.
Example:
Price sensitivity, low switching costs should put price pressure on branded soft drinks, given lower cost generics
|
Actions/position taken to reduce the cost/price impact
Advertising - lifestyle image associated with product
Pricing - "reasonable" compared to other purchases, but high margin
|
Which party or parties has the strongest claim on value?
Industry is able to sustain high profitability as long as brand/experience is valuable.
|
|
|
|
|
|
|
|
|
|
Overall
|
|
Aggregate net effect on industry profitability
|
Step 6: Summarize your understanding of the Industry
In 1 to 3 pages (or so), summarize your understanding of the industry and its profitability. Suggested outline:
• How does the industry make money? (Simple story for how it sources inputs, transforms them into products/services, and then sells them)
• Five Forces summary:
- What are the most important forces that currently limit industry profitability?
- What are the most important industry actions that sustain profitability?
- What trends or changes might have a significant effect on industry profitability (positive or negative)?
• Are there any interesting performance outliers? What might explain them?
• What topics/questions might be interesting for a Team Project.
https://www.dropbox.com/s/caxnq30utm60zf3/si422profitabiltyexcercisedata.rar?dl=0