Reference no: EM131077147

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.
<second factor(s)>
<third ... factor(s)>
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