Segment Performance Evaluation using a TUR In this assignment you will analyze the performance of actual company divisions. SFAS 131 requires publicly traded companies to disclose segment information in the notes 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. The report is due by 11:59 pm on Tuesday, November 26, 2019. Please submit this file as well as your excel spreadsheet to the Case Study #6 dropbox folder. SECTION I 1. Google the 2015 10K for either 1) Starbuck, or 2) PepsiCo. 2. Locate the Table of Contents in the 10K. The Table of Contents is ALWAYS the same for all 10Ks. 3. What is Item I in the 10K Table of Contents? 4. Go to "Item 1 and read through the subsection of "Item 1 that describes "Segment Information" (for Starbucks). Which Note to the Consolidated Financial Statements contains the information about the segments for Starbucks? Note # 5. Go back to the Table of Contents. What "Item #" are the Financial Statements? What page do the Financial Statements start on? _ The Notes to the financial statements ALWAYS follow immediately after the financial statements. They will be marked as Note 1", etc., in sequential order. STARBUCKS: Go to that page. Scroll through the financial statements until you come to the Table of Contents for the Notes. What page does the Note you are looking for (segment reporting) begin on (note whether you are using the electronic 10-K or PDF)? Go to that page. Skim through the Note until you find the tables that contain the segment data you will need to perform ROI, sales margin, capital turnover and residual income calculations. PEPSI: Despite what it says, the financial statements actually start on page 73 of the pdf (they have an error in their link). What page does the Note you are looking for (segment reporting) begin on? Go to that page. Skim through the Note until you find the tables that contain the segment data you need to perform ROI, sales margin, capital turnover and residual income calculations. SECTION II 1. In an Excel spreadsheet, set up four column headings: Division Name (use their abbreviations); Assets; Operating Income; Revenue. Input the 2015 data. (Do not include the total of all divisions.) Expand column widths as needed. 2. 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. 3. Use "Format as a Table or shading to improve the visual look of your data table. Play around with the different design options. 4. 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 the title "Segment Data" to your chart. Expand the chart size if needed. 5. Add a textbox ("Insert" on menu bar; "Textbox") and answer the following questions: i. Briefly describe the segments. Give some examples of the products or geographical territory. ii. Comment on the graph: What stands out to you? SECTION III 6. Add a new table somewhere below the first section. The second data table should have the following column headings: Division, ROI, Sales Margin, Capital Turnover, and Residual Income. "Copy" and "paste" the division names into the new table. 7. For the first company division listed, insert formula commands to calculate ROI, Sales Margin, Capital Turnover, and Residual income (assume the company has a 25% cost of capital percentage). To insert formulas, highlight the cell where you want to place the answer. Then, for example, type in "=B2/B3" to divide the figure in cell B2 by the figure in cell B3. i. Remember, Investment = Investment Assets ii. ROI = Operating Income/Assets iii. Sales Margin = Operating Income/Revenue iv. Capital Turnover = Revenue/Assets v. Residual Income = Operating Income - (Assets x Cost of Capital Percentage) 8. Use the "Copy and Paste" commands to copy the same formulas into the appropriate cell for each division. 9. 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). Format the Capital Turnover as a plain number. Format the residual income as a dollar amount. 10. Add a column graph that compares the ROI of each division. Add a title and size the graph as needed. (Only the graph of the ROI is required. You do NOT need to make graphs of the other ratios.) 11. Add one textbox for EACH of the following: I. ROI: What does ROI tell management? Which divisions have the best and worst or are they roughly the same? How do they compare? 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. ii. Sales Margin: What does Sales Margin tell management? Which divisions have the best and worst or are they roughly the same? How do they compare? 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. iii. Capital Turnover: What does Capital Turnover tell management? iii. Capital Turnover: What does Capital Turnover tell management? Which divisions have the best and worst? How do they compare? 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. iv. Residual Income: What does Residual Income tell management? Do any of the divisions fall short of management's expectations? How can you tell? Segment Performance Evaluation using a TUR In this assignment you will analyze the performance of actual company divisions. SFAS 131 requires publicly traded companies to disclose segment information in the notes 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. The report is due by 11:59 pm on Tuesday, November 26, 2019. Please submit this file as well as your excel spreadsheet to the Case Study #6 dropbox folder. SECTION I 1. Google the 2015 10K for either 1) Starbuck, or 2) PepsiCo. 2. Locate the Table of Contents in the 10K. The Table of Contents is ALWAYS the same for all 10Ks. 3. What is Item I in the 10K Table of Contents? 4. Go to "Item 1 and read through the subsection of "Item 1 that describes "Segment Information" (for Starbucks). Which Note to the Consolidated Financial Statements contains the information about the segments for Starbucks? Note # 5. Go back to the Table of Contents. What "Item #" are the Financial Statements? What page do the Financial Statements start on? _ The Notes to the financial statements ALWAYS follow immediately after the financial statements. They will be marked as Note 1", etc., in sequential order. STARBUCKS: Go to that page. Scroll through the financial statements until you come to the Table of Contents for the Notes. What page does the Note you are looking for (segment reporting) begin on (note whether you are using the electronic 10-K or PDF)? Go to that page. Skim through the Note until you find the tables that contain the segment data you will need to perform ROI, sales margin, capital turnover and residual income calculations. PEPSI: Despite what it says, the financial statements actually start on page 73 of the pdf (they have an error in their link). What page does the Note you are looking for (segment reporting) begin on? Go to that page. Skim through the Note until you find the tables that contain the segment data you need to perform ROI, sales margin, capital turnover and residual income calculations. SECTION II 1. In an Excel spreadsheet, set up four column headings: Division Name (use their abbreviations); Assets; Operating Income; Revenue. Input the 2015 data. (Do not include the total of all divisions.) Expand column widths as needed. 2. 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. 3. Use "Format as a Table or shading to improve the visual look of your data table. Play around with the different design options. 4. 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 the title "Segment Data" to your chart. Expand the chart size if needed. 5. Add a textbox ("Insert" on menu bar; "Textbox") and answer the following questions: i. Briefly describe the segments. Give some examples of the products or geographical territory. ii. Comment on the graph: What stands out to you? SECTION III 6. Add a new table somewhere below the first section. The second data table should have the following column headings: Division, ROI, Sales Margin, Capital Turnover, and Residual Income. "Copy" and "paste" the division names into the new table. 7. For the first company division listed, insert formula commands to calculate ROI, Sales Margin, Capital Turnover, and Residual income (assume the company has a 25% cost of capital percentage). To insert formulas, highlight the cell where you want to place the answer. Then, for example, type in "=B2/B3" to divide the figure in cell B2 by the figure in cell B3. i. Remember, Investment = Investment Assets ii. ROI = Operating Income/Assets iii. Sales Margin = Operating Income/Revenue iv. Capital Turnover = Revenue/Assets v. Residual Income = Operating Income - (Assets x Cost of Capital Percentage) 8. Use the "Copy and Paste" commands to copy the same formulas into the appropriate cell for each division. 9. 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). Format the Capital Turnover as a plain number. Format the residual income as a dollar amount. 10. Add a column graph that compares the ROI of each division. Add a title and size the graph as needed. (Only the graph of the ROI is required. You do NOT need to make graphs of the other ratios.) 11. Add one textbox for EACH of the following: I. ROI: What does ROI tell management? Which divisions have the best and worst or are they roughly the same? How do they compare? 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. ii. Sales Margin: What does Sales Margin tell management? Which divisions have the best and worst or are they roughly the same? How do they compare? 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. iii. Capital Turnover: What does Capital Turnover tell management? iii. Capital Turnover: What does Capital Turnover tell management? Which divisions have the best and worst? How do they compare? 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. iv. Residual Income: What does Residual Income tell management? Do any of the divisions fall short of management's expectations? How can you tell