Segment (Division) Evaluation In this assignment you will analyze the performance of PepsiCo's divisions. SFAS 131...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Segment (Division) Evaluation In this assignment you will analyze the performance of PepsiCo's divisions. SFAS 131 requires publicly traded companies to disclose division 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. Be sure to follow all of the instructions carefully to maximize the number of points available. SECTIONI Google the 10k (or annual report and 10K) for Fiscal year 2018 for PepsiCo. You should be able to download it from the Investor Relations section of Pepsi's website. 1. Locate the Table of Contents in the 10K. The Table of Contents for all 10Ks follow a standard format. What is "Item 1" in the 10K Table of Contents? Put your answer in the box to the right. 2. Go to "Item 1. What are the six reportable segments? List them in the answer box to the right [use their abbreviations). Next, skim through the detailed description of each division (they start on the next page). In column N to the right), give a brief description, along with a few examples of their notable products. The next series of questions can be answered by looking at some of the main headings in Item 1 until you find the associated topics. 3. What three methods does Pepsi use to distribute its products? List the three (column M). In Column N, use your own words to summarize the business reasons for each distribution method. Finally, how else are the products distributed (iv)? 4. What are some of the primary raw materials used in the products? ii. What are some of the primary raw materials for the packaging? 5. Does the business experience seasonality? If so, when are sales generally the highest? 6. List three of the company's primary competitors. 7. Go to the table of contents. i. What Item number contains "Management's Discussion and Analysis of Financial Condition and Results of Operations"? il. What page number does it start on? Go to that page. 8. Look down the list of Notes to the Financial Statements (Note 1 through Note 15). i. Which Note will give more information about the Divisions? ii. What page does it start on? Go to that page and scroll through the note until you come to the tables that give the financial information that you'll need to calculate ROI, Sales Margin, and Capital Turnover. SECTION !! Reg. 1 2 iii. iv. 3 vi. 4 & i. ii. V. ii. iii. TV. 5 6 7 1. 8 i. For products: ii. For packaging: F ii. i. ii. Answer 1. II. Additional information/description/business reason SECTION II 1. In a new spreadsheet (name it "Segment Analysis"), set up four column headings: Division; Assets; Operating Income; Revenue. Your rows should list the six reportable segments. 2. Input the 2018 data for the six segments. Expand column widths as needed. Keep the amounts as shown (in millions); don't add zeros. 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. 5. Next, add a column chart to visually display this data. To do this, highlight the entire data table. Then click on the "insert" tab on the ribbon. Then click on the "Column" Chart. Add a title to your chart. Expand the chart size if needed. 6. Add a textbox ("Insert" on ribbon; "Textbox") to comment on the graph. What observations about the graph stand out to you? SECTION III 1. Add a new table a few lines below the first table. 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. 2. For the first company segment listed, insert formulas to calculate ROI, Sales Margin, Capital Turnover, and Residual income (assume the company has a 25% target rate of return). 3. Use the "Copy" and "Paste" commands to copy the same formulas into the appropriate cell for each division. Alternatively, drag the autofill handle to fill in the formulas for the other segments. 4. Use the "%" on the "Number" section of the ribbon 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. 6. Add a column chart that compares the ROI of each segment. Add a title and size the chart as needed. (Only the chart of the ROI is required. You do NOT need to make charts of the other ratios.) Section IV i. ii. Section IV Add one textbox for EACH of the following: 1. ROI: What does ROI tell management? Which segments have the best and worst ROI or are they roughly the same (how do they compare)? How could a segment of this company improve its ROI? Give me an example of a specific action that could be taken to improve ROI. 2. Sales Margin: What does Sales Margin tell management? Which segments have the best and worst or are they roughly the same (how do they compare)? How could a segment of this company improve its sales margin? Give me an example of a specific action that could be taken to improve sales margin. 3. Capital Turnover: What does Capital Turnover tell management? Which divisions have the best and worst? How do they compare? How could a segment of this company improve its capital turnover? Give me an example of a specific action that could be taken to improve capital turnover. 4. Residual Income: What does Residual Income tell management? Do any of the segments fall short of management's expectations? How can you tell? 5. Alloca 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? 6. Responsibility Accounting What type of Responsibility centers are i) the 6 divisions, ii) corporate? 7. Measurement issues When you did the ROI, sales margin, capital turnover, and residual income calculations, using the segment information provided in the footnotes, did you use the 1) gross book value (GBV) of the assets, or 2) net book value (NBV) of the assets? How can you tell? (HINT: Look at the Consolidated Balance Sheet on page 81 of the 10k) What is the risk when companies use the NBV for assessing ROI. 8. Check grading rubric to make sure you have done everything required. Using a 10(k) - Correct information listed on "Directions" worksheet. Table 1-Proper column/row headings and data Table 1 -Formatted per directions Graph 1 with a reasonable title Observations/comments about Graph 1 Table 2-proper calculations Table 2 -formatted per directions Graph 2 Correct ROI column chart ROI questions - Define; Compare; How to improve (1 pt each) Sales margin questions- Define; Compare; How to improve (1 pt each) Capital turnover questions- Define; Compare; How to improve (1 pt each) Residual income question:-Define; which fall short; How can you tell (1 pt each) Allocation of excess funds: Explanation of decision Responsibility center questions Gross book value or net book value. How can you tell? Risk when NBV is used? Points will be deducted for sloppy work, spelling and punctuation errors, etc. Assume you are preparing this report for a client. TOTAL POINTS Pts Possible 10 W N N w w w w N NaN NNSO 5 2 2 2 2 3 3 3 3 3 50 Segment (Division) Evaluation In this assignment you will analyze the performance of PepsiCo's divisions. SFAS 131 requires publicly traded companies to disclose division 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. Be sure to follow all of the instructions carefully to maximize the number of points available. SECTIONI Google the 10k (or annual report and 10K) for Fiscal year 2018 for PepsiCo. You should be able to download it from the Investor Relations section of Pepsi's website. 1. Locate the Table of Contents in the 10K. The Table of Contents for all 10Ks follow a standard format. What is "Item 1" in the 10K Table of Contents? Put your answer in the box to the right. 2. Go to "Item 1. What are the six reportable segments? List them in the answer box to the right [use their abbreviations). Next, skim through the detailed description of each division (they start on the next page). In column N to the right), give a brief description, along with a few examples of their notable products. The next series of questions can be answered by looking at some of the main headings in Item 1 until you find the associated topics. 3. What three methods does Pepsi use to distribute its products? List the three (column M). In Column N, use your own words to summarize the business reasons for each distribution method. Finally, how else are the products distributed (iv)? 4. What are some of the primary raw materials used in the products? ii. What are some of the primary raw materials for the packaging? 5. Does the business experience seasonality? If so, when are sales generally the highest? 6. List three of the company's primary competitors. 7. Go to the table of contents. i. What Item number contains "Management's Discussion and Analysis of Financial Condition and Results of Operations"? il. What page number does it start on? Go to that page. 8. Look down the list of Notes to the Financial Statements (Note 1 through Note 15). i. Which Note will give more information about the Divisions? ii. What page does it start on? Go to that page and scroll through the note until you come to the tables that give the financial information that you'll need to calculate ROI, Sales Margin, and Capital Turnover. SECTION !! Reg. 1 2 iii. iv. 3 vi. 4 & i. ii. V. ii. iii. TV. 5 6 7 1. 8 i. For products: ii. For packaging: F ii. i. ii. Answer 1. II. Additional information/description/business reason SECTION II 1. In a new spreadsheet (name it "Segment Analysis"), set up four column headings: Division; Assets; Operating Income; Revenue. Your rows should list the six reportable segments. 2. Input the 2018 data for the six segments. Expand column widths as needed. Keep the amounts as shown (in millions); don't add zeros. 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. 5. Next, add a column chart to visually display this data. To do this, highlight the entire data table. Then click on the "insert" tab on the ribbon. Then click on the "Column" Chart. Add a title to your chart. Expand the chart size if needed. 6. Add a textbox ("Insert" on ribbon; "Textbox") to comment on the graph. What observations about the graph stand out to you? SECTION III 1. Add a new table a few lines below the first table. 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. 2. For the first company segment listed, insert formulas to calculate ROI, Sales Margin, Capital Turnover, and Residual income (assume the company has a 25% target rate of return). 3. Use the "Copy" and "Paste" commands to copy the same formulas into the appropriate cell for each division. Alternatively, drag the autofill handle to fill in the formulas for the other segments. 4. Use the "%" on the "Number" section of the ribbon 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. 6. Add a column chart that compares the ROI of each segment. Add a title and size the chart as needed. (Only the chart of the ROI is required. You do NOT need to make charts of the other ratios.) Section IV i. ii. Section IV Add one textbox for EACH of the following: 1. ROI: What does ROI tell management? Which segments have the best and worst ROI or are they roughly the same (how do they compare)? How could a segment of this company improve its ROI? Give me an example of a specific action that could be taken to improve ROI. 2. Sales Margin: What does Sales Margin tell management? Which segments have the best and worst or are they roughly the same (how do they compare)? How could a segment of this company improve its sales margin? Give me an example of a specific action that could be taken to improve sales margin. 3. Capital Turnover: What does Capital Turnover tell management? Which divisions have the best and worst? How do they compare? How could a segment of this company improve its capital turnover? Give me an example of a specific action that could be taken to improve capital turnover. 4. Residual Income: What does Residual Income tell management? Do any of the segments fall short of management's expectations? How can you tell? 5. Alloca 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? 6. Responsibility Accounting What type of Responsibility centers are i) the 6 divisions, ii) corporate? 7. Measurement issues When you did the ROI, sales margin, capital turnover, and residual income calculations, using the segment information provided in the footnotes, did you use the 1) gross book value (GBV) of the assets, or 2) net book value (NBV) of the assets? How can you tell? (HINT: Look at the Consolidated Balance Sheet on page 81 of the 10k) What is the risk when companies use the NBV for assessing ROI. 8. Check grading rubric to make sure you have done everything required. Using a 10(k) - Correct information listed on "Directions" worksheet. Table 1-Proper column/row headings and data Table 1 -Formatted per directions Graph 1 with a reasonable title Observations/comments about Graph 1 Table 2-proper calculations Table 2 -formatted per directions Graph 2 Correct ROI column chart ROI questions - Define; Compare; How to improve (1 pt each) Sales margin questions- Define; Compare; How to improve (1 pt each) Capital turnover questions- Define; Compare; How to improve (1 pt each) Residual income question:-Define; which fall short; How can you tell (1 pt each) Allocation of excess funds: Explanation of decision Responsibility center questions Gross book value or net book value. How can you tell? Risk when NBV is used? Points will be deducted for sloppy work, spelling and punctuation errors, etc. Assume you are preparing this report for a client. TOTAL POINTS Pts Possible 10 W N N w w w w N NaN NNSO 5 2 2 2 2 3 3 3 3 3 50
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
Writing and Presenting a Research Report As Brown, Suter, and Churchill (2014) indicate in the Basic Marketing Research textbook, a marketing researcher will almost always be expected to develop a...
-
Use turtle graphics to create an interactive forest drawing application. Start by creating a module utilities.py which should contain four drawing functions as listed below. draw_triangle(centre_x,...
-
The following total cost and inverse demand functions (in dollars) are given for a firm: 1 TC (q) = 9-q +39 and_p(q) = 84 - q. (a) For what value(s) of q is the profit maximized? What is the maximum...
-
The task in this design project is to design an actuation system to power the ram's reciprocating motion in a small-size shaper. The power source of the actuation system is an AC motor with 0.75 hp...
-
An aluminum fuel tank has a cylindrical middle section and a semi-spherical ends. The outside diameter is 10 in., and the length of the cylindrical section is 24 in. The wall-thickness of the...
-
In what situation does total stockholders equity decrease? a. When a cash dividend is declared. b. When a stock dividend is declared. c. When a stock split is announced. d. When a previously declared...
-
Write the formulas for the Gauss-Seidel algorithm (similar to (8.57)) for the central difference scheme applied to the three-dimensional Poisson equation (8.14).
-
American International Automotive Industries (AIAI) manufactures auto and truck engine, transmission, and chassis parts for manufacturers and repair companies in the United States, South America,...
-
Dorothy??s Design is a relatively new landscaping business. Lastyear (2022) has been very profitable, so Dorothy is planning topurchase a new tractor, implements, trailer and truck for herbusiness 2...
-
Gibson Agency Case: 1. Calculate and present the budgeted profit for each of Gibson's clients for each of the years 2016 through 2019, using the current costing system (i.e., the one described in the...
-
7. A 70 kg astronaut orbits the moon at a distance of 7.2 x 106 m from its center. (Mass of moon = 7.35 x 102 kg) a. What is the force acting on the astronaut by the moon? b. How fast is the...
-
Special Order Carson Manufacturing, Inc., sells a single product for $37 per unit. At an operating level of 8,000 units, variable costs are $18 per unit and fixed costs $10 per unit. Carson has been...
-
What mechanisms have been devised to foster stakeholder engagement and garner requisite support for the agenda's implementation ?
-
Dropping Unprofitable Department Thomas Corporation has four departments, all of which appear to be profitable except department 4. Operating data for the year are as follows: Departments 1-3...
-
A particle of mass m moves along a curve given in the table below At any time t, discuss the following points of the system in detail. a) An appropriate figure. b) Are there any constraints in the...
-
Write a Python program to first take an integer number (x), where 1 x 255, from the user by using the prompt "Number of registers in the CPU:". Use try- except block to wait indefinitely until the...
-
Parker Co. has the following cash balances:First Bank:Second Bank:Third Bank:Fourth Bank:$150,000(10,000)25,000(5,000)Required:1. Prepare the current assets and current liabilities section o 2 answers
-
Match each of the key terms with the definition that best fits it. _______________ A record of the sequence of data entries and the date of those entries. Here are the key terms from the chapter. The...
-
How should Santiago prepare for the negotiation?
-
How can Code C avoid future sourcing disruptions?
-
Since Hudson builds more than 100 cabinets similar to the INE chemical cabinet, how should Josh make a decision on what to do about planning and control at Hudson?
-
Develop a reality T.V. show involving accountants (e.g., forensic accountants).
-
How may misappropriations be accomplished?
-
You are hired to perform a forensic audit of a movie (e.g., College). Prepare a short term paper on production accounting for a movie or T.V. production.
Study smarter with the SolutionInn App