Division Performance Evaluation Project Using Excel
In this assignment you will analyze the performance of PepsiCo or Marriott's company divisions. SFAS 131 requires publicly traded companies to disclose division information in the footnotes to the financial statements. You will use Excel to create visually appealing data tables and bar charts to analyze division performance, and then comment on the results. See me if you need help on any step! One of the goals of this project is for you to continue to improve your Excel skills.
SECTION I -
1. Download the 2013 annual report for either Marriott International or PepsiCo. Locate the "Divisions" or "Business Segment" information in the footnotes following the financial statements. For Marriott, you'll find the information in Footnote 14. For PepsiCo, it's in Footnote 1. I've posted the annual reports on Blackboard.
2. In an Excel spreadsheet, set up four column headings: Division; Assets; Operating Income; Revenue. Input the 2013data for each of the company's operating divisions. (Do not include "unallocated corporate".) Expand column widths as needed.
3. Use the "Number" section of the menu bar to format the data as "currency" (dollar signs and commas). Use the "decimal remove" button to delete unnecessary decimals.
4. Use "Format as a Table" or shading to improve the visual look of your data table. Play around with the different design options.
5. Next, you will be adding a graph to visually display this data. To do this, highlight the entire table. Then click on the "insert" tab on the menu bar. Then click on the "Column" Chart. Choose an appropriate layout from those available. Add a title to your chart. Expand the chart size if needed.
6. Comment on the Division information by using the "Insert textbox" command from the "Insert" menu tab:
a. Use bullet points, or a few short sentences, to briefly describe the primary products and brands operated/sold by each division. You'll find this information in the footnote itself (Marriott- FN 14) or in Part 1 of the 10k that is imbedded in the annual report (pages 3-5 of the 10k for Pepsi) b. Next, comment on anything that seems to be interesting in the data set that you would like to point out to the reader of this report. Drag your textbox and graph around the worksheet as needed to create a visually appealing layout.
SECTION II -
7. Add a new table somewhere below the first section. The second data table should have the following column headings: Division, ROI, Sales Margin, and Capital Turnover. "Copy" and "paste" the division names into the new table.
8. For the first company division listed, insert formula commands tocalculate ROI, Sales Margin, and Capital Turnover. To insert formulas, highlight the cell where you want your answer to be. Then, for example, type in "=B2/B3" to divide the figure in cell B2 by the figure in cell B3.
9. Use the "Copy" and "Paste" commands to copy the same formulas into the appropriate cell for each division.
10. Use the "%" on the "Number" section of the menu bar to format the ROI and Sales Margin as percentages. Use the "decimal remove" button to decrease the number of decimals on the Capital Turnover statistic (two decimal should be sufficient).
11. Add a columnar graph that compares the ROI of each division. Add a title and size the graph as needed.
12. Add one or more text boxes to comment on the following: (Do NOT answer these questions in one long paragraph. Divide up your paragraphs/comments/boxes in a visually appealing manner to make it easy for the reader to absorb the information.)
a. What does ROI tell management?
b. Interpret the ROI statistics found through your analysis. Which divisions have the best and worst? How do they compare?
c. How could a division of this company improve its ROI? Give me an example of a specific action that could be taken to improve ROI.
d. What does Sales Margin tell management?
e. Interpret the Sales margin statistics found through your analysis. Which divisions have the best and worst? How do they compare?
f. How could a division of this company improve its sales margin? Give me an example of a specific action that could be taken to improve sales margin.
g. What does Capital Turn over tell management?
h. Interpret the Capital turnover statistics found through your analysis. Which divisions have the best and worst? How do they compare?
i. How could a division of this company improve its capital turnover? Give me an example of a specific action that could be taken to improve capital turnover.
j. If you were upper management (CEO of the whole corporation), and had excess funds to allocate among divisions, which division would you allocate those funds to and why?