Division Evaluation Project using a 10K

Name: __________________________________

In this assignment you will analyze the performance of actual company divisions. FASB ASC 280 (formerly 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.

Due Date: Tuesday, May 1, 2018. Submit as an attachment in Blackboard in the Module 24 Assignment.

SECTION I

Obtain the 2017 10K for Starbucks. It is available at: http://d18rn0p25nwr6d.cloudfront.net/CIK-0000829224/f8c7bf9b-da8f-4ebd-bd2b-55f4abaa08fe.pdf

Locate the Table of Contents in the 10K. The Table of Contents is ALWAYS the same for all 10Ks.

What is Item 1 in the 10K Table of Contents? _________________________________

Go to Item 1 and read through the subsection of Item I that describes Segment Information. Which Note to the Consolidated Financial Statements contains the information about the segments for Starbucks? Note #_______________.

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.

Go to the financial statements.Scroll through the financial statements until you come to the Note on Segment Reporting.What page does the Note you are looking for (segment reporting) begin on? _________ . Skim through the Note until you find the tables that contain the segment data you need to perform ROI and residual income calculations.

SECTION II

In an Excel spreadsheet, set up four column headings: one column for each Division (use their abbreviations). Set up rows for: Assets; Operating Income; Revenue. Input the 2017 data. (Do not include unallocated corporate or the total of all divisions.) Expand column widths as needed.

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.

Use Format as a Table or shading to improve the visual look of your data table. Play around with the different design options.

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.

Add a textbox (Insert on menu bar; Textbox) and answer the following questions:

Briefly describe the segments. Give some examples of the products or geographical territory.

Comment on the graph: What stands out to you?

SECTION III

Add a new table somewhere below the first section. The second data table should have the following column headings: Division, ROI and Residual Income. For the rows Copy and paste the division names into the new table.

For the first company division listed, insert formula commands to calculate ROI 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.

Use the Copy and Paste commands to copy the same formulas into the appropriate cell for each division.

Use the % on the Number section of the menu bar to format the ROI as a percentage. Format the residual income as a dollar amount.

Add a column graph that compares the ROI of each division. Add a title and size the graph as needed.

Add one textbox for EACH of the following:

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 an example of a specific action that could be taken to improve ROI.

Residual Income:

What does Residual Income tell management?

Do any of the divisions fall short of managements expectations?How can you tell?

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

Requirements

Using a 10(k) to find segment data(properly fill out first page)(3 pts)

Table 1 Propercolumn headings and dataand formatting (4 pt)

Graph 1(3 pts)

Brief descriptions of segments (3 pts)

Comments about Graph 1 (3 pts)

Table 2 proper calculations AND formatting as indicated in the directions (4 pts)

Graph 2 (3 pts)

ROI questions Define; Compare; How to improve (5 pts)

Residual income question:-Define; which fall short; How can you tell? (5 pts)

Allocation of excess funds:Explanation of decision (3 pts)

Assignment is well presented. Used proper grammar. No spelling or typo errors. (4 pts)

TOTAL POINTS 40 points