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. You will work individually on this assignment. Put your name on the FILE NAME of your Excel spreadsheet. In addition, you will need to submit this completed packet in Canvas as well as answer some questions in Canvas. SECTION! 1. Google the 10K (or annual report and 10K) for Fiscal year 2019 for PepsiCo or go to the company's website. 2. Locate the Table of Contents in the 10K (NOTE: For Pepsi, the 10K begins after 14 pages of the annual report, which is management's description of the highlights of the fiscal year). The Table of Contents for all 10Ks follow a standard format. (Answer the following Questions in Section 1 in Canvas). 3. What is "Item 1" in the 10K Table of Contents? 4 Go to "Item 1" i. For Pepsi, read through the subsections of "ItemI" that describes "Company Overview" and "Our operations". What are the 7 reportable segments (use their abbreviations)? Make a mental note about what each segment sells. Which Note to the Consolidated Financial Statements contains the financial information about Pepsi's divisions and geographic areas? Note# PEPSI: Go back to the Table of Contents for the entire 10k. What Item # are the Financial Statements in the 10k? What page do they begin on per the table of contents? Despite what it says, the financial statements begin on page 73. The Notes to the consolidated financial statements always follow IMMEDIATELY after the financial statements. What page does your Note about Divisions "Net Revenue" and "Operating Profit" begin on? Skim through the Note until you find the tables that contain the financial data on the 7 divisions you listed earlier. SECTION II 1. In an Excel spreadsheet, set up four column headings: Division; Assets; Operating Income; Revenue. Input the 2019 data (use division name abbreviations). (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 a title to your chart. Expand the chart size if needed. 5. Add a textbox ("Insert" on menu bar; "Textbox") and answer the following questions: 1. Briefly describe the segments. Give some examples of the products or describe 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% target rate of return). 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. division. 8. Use the "Copy" and "Paste" commands to copy the same formulas into the appropriate cell for each 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 decimals 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? 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? V. Allocation decision: 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? Grading Rubric Name: 40 pts total Possible 6 6 2 2 Requirements Using a 10(k) to find segment data (properly fill out first page) (6 pts) Table 1 - Proper column headings and data (4 pts) and formatting (2 pts) Graph 1 (2 pts) Brief descriptions of segments (2 pts) Comments about Graph 1 (2 pts) Table 2-proper calculations (4 pts) AND formatting as indicated in the directions (2 pts) Graph 2 (2 pts) ROI questions - Define; Compare; How to improve (3 pts) Sales margin questions- Define; Compare; How to improve (3 pts) 2 6 2 3 3 Capital turnover questions Define; Compare; How to improve (3 pts) 3 3 Residual income question: -Define; which fall short; How can you tell? (3 pts) Allocation of excess funds: Explanation of decision (2 pts) 2 Deductions Points will be deducted for sloppy work, spelling and punctuation errors, etc. Points will also be deducted if you don't follow the submission directions properly. Assume you are preparing this report for a client. TOTAL POINTS 40 1 2 3 7 5 5 7 Division Name FLNA QFNA PBNA LatAm Europe AMESA APAC Assests Operating Income Revenue $ 7,519 $ 5,258 $ 17,078 $ 941 $ 544 $ 2,482 $ 31,449 $ 2,179 $ 21,730 $ 7,007 $ 1,141 $ 7,573 $ 17,814 $ 1,328 $ 11,728 $ 3,672 $ 671 $ 3,651 $ 4,113 $ 477 $ 2,919 3 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. You will work individually on this assignment. Put your name on the FILE NAME of your Excel spreadsheet. In addition, you will need to submit this completed packet in Canvas as well as answer some questions in Canvas. SECTION! 1. Google the 10K (or annual report and 10K) for Fiscal year 2019 for PepsiCo or go to the company's website. 2. Locate the Table of Contents in the 10K (NOTE: For Pepsi, the 10K begins after 14 pages of the annual report, which is management's description of the highlights of the fiscal year). The Table of Contents for all 10Ks follow a standard format. (Answer the following Questions in Section 1 in Canvas). 3. What is "Item 1" in the 10K Table of Contents? 4 Go to "Item 1" i. For Pepsi, read through the subsections of "ItemI" that describes "Company Overview" and "Our operations". What are the 7 reportable segments (use their abbreviations)? Make a mental note about what each segment sells. Which Note to the Consolidated Financial Statements contains the financial information about Pepsi's divisions and geographic areas? Note# PEPSI: Go back to the Table of Contents for the entire 10k. What Item # are the Financial Statements in the 10k? What page do they begin on per the table of contents? Despite what it says, the financial statements begin on page 73. The Notes to the consolidated financial statements always follow IMMEDIATELY after the financial statements. What page does your Note about Divisions "Net Revenue" and "Operating Profit" begin on? Skim through the Note until you find the tables that contain the financial data on the 7 divisions you listed earlier. SECTION II 1. In an Excel spreadsheet, set up four column headings: Division; Assets; Operating Income; Revenue. Input the 2019 data (use division name abbreviations). (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 a title to your chart. Expand the chart size if needed. 5. Add a textbox ("Insert" on menu bar; "Textbox") and answer the following questions: 1. Briefly describe the segments. Give some examples of the products or describe 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% target rate of return). 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. division. 8. Use the "Copy" and "Paste" commands to copy the same formulas into the appropriate cell for each 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 decimals 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? 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? V. Allocation decision: 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? Grading Rubric Name: 40 pts total Possible 6 6 2 2 Requirements Using a 10(k) to find segment data (properly fill out first page) (6 pts) Table 1 - Proper column headings and data (4 pts) and formatting (2 pts) Graph 1 (2 pts) Brief descriptions of segments (2 pts) Comments about Graph 1 (2 pts) Table 2-proper calculations (4 pts) AND formatting as indicated in the directions (2 pts) Graph 2 (2 pts) ROI questions - Define; Compare; How to improve (3 pts) Sales margin questions- Define; Compare; How to improve (3 pts) 2 6 2 3 3 Capital turnover questions Define; Compare; How to improve (3 pts) 3 3 Residual income question: -Define; which fall short; How can you tell? (3 pts) Allocation of excess funds: Explanation of decision (2 pts) 2 Deductions Points will be deducted for sloppy work, spelling and punctuation errors, etc. Points will also be deducted if you don't follow the submission directions properly. Assume you are preparing this report for a client. TOTAL POINTS 40 1 2 3 7 5 5 7 Division Name FLNA QFNA PBNA LatAm Europe AMESA APAC Assests Operating Income Revenue $ 7,519 $ 5,258 $ 17,078 $ 941 $ 544 $ 2,482 $ 31,449 $ 2,179 $ 21,730 $ 7,007 $ 1,141 $ 7,573 $ 17,814 $ 1,328 $ 11,728 $ 3,672 $ 671 $ 3,651 $ 4,113 $ 477 $ 2,919 3