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 :

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.

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).

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:

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: _____________________

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