The project is to be done individually by each student. You can discuss this project with other students in the class, but you cannot copy another student's work.

The name of the individual student should be in cell A1 of the first tab in the spreadsheet.

This project is based on segment data. The name of the company is Snow White and it has seven business segments (Doc, Sleepy, Grumpy, Sneezy, Happy, Bashful, Dopey). Data on segment sales, assets and profit for ten years on each of these seven segments is given in an Excel file that is posted on iCollege in the same folder as this document.

Required:

1. Prepare 3 pie charts (one each for segment revenue, profits and assets), showing the average percentage (over the 10 years) that each segment contributes to total company revenue, profits and the allocation of assets. The pie chart should be properly labeled and the use of colors to indicate the segments is encouraged. The formulas for calculation of the contribution by each segment to segment revenue, profits and assets should be in the spreadsheet.

2. The management of Snow White wants to evaluate the performance of each segment and make a decision as to which segments to keep and which ones to dispose of. It wants to use Return on Assets (ROA) as the criterion to make this business decision. ROA is defined as Profit/Assets. Calculate the average ROA for each of the segments over the ten years and present the information in the form of a bar graph. The formula for calculating the average ROA for each segment should be in the spreadsheet.

3. Snow White management wants to get rid of any segment that did not have an average ROA of at least 10.5% over the last 10 years. Based on the ROA you calculated in (2) above, identify which segment(s) are candidates for being disposed off.

4. For each segment that has been identified by the top management of Snow White in (3) above as one to be disposed off, imagine you are the manager of that segment. As the manager of that segment make a case as to why your segment should not be disposed off. You need to make your case solely based on the performance of your segment (Hint: Use other performance measures). You need to be creative in doing this section of the assignment. All calculations should be shown in the spreadsheet and the narrative for making your case should also be in the spreadsheet.

The entire assignment should be submitted as one Excel spreadsheet. All calculations, charts, graphs, narratives should be in the spreadsheet itself. You can use as many tabs in the spreadsheet as you wish. Please make sure all tabs are properly labeled.

Ended Segment Name SALES ASSETS PROFIT

12/31/07 Doc 42688 57670 7883

12/31/08 Doc 41233 51863 7460

12/31/09 Doc 18494 20377 3923

12/31/10 Doc 17464 21175 3304

12/31/11 Doc 18442 23567 3512

12/31/12 Doc 19322 25144 3747

12/31/13 Doc 21411 32272 4345

12/31/14 Doc 23298 33716 4973

12/31/15 Doc 24242 34524 5507

12/31/16 Doc 25530 38899 6115

12/31/07 Sleepy 30347 31466 4817

12/31/08 Sleepy 41948 36973 6497

12/31/09 Sleepy 40015 36663 7105

12/31/10 Sleepy 37198 38606 7271

12/31/11 Sleepy 43369 54389 6650

12/31/12 Sleepy 6925 9253 131

12/31/13 Sleepy 6721 9962 110

12/31/14 Sleepy 14812 15536 677

12/31/15 Sleepy 15329 21587 944

12/31/16 Sleepy 14074 17858 311

12/31/07 Grumpy 33219 310412 12243

12/31/08 Grumpy 65937 627501 8063

12/31/09 Grumpy 47437 597877 1325

12/31/10 Grumpy 45214 565337 3158

12/31/11 Grumpy 44658 552514 6549

12/31/12 Grumpy 44327 539351 7401

12/31/13 Grumpy 42917 516829 8258

12/31/14 Grumpy 10283 502204 1209

12/31/15 Grumpy 9650 316069 -7983

12/31/16 Grumpy 9617 187804 -1251

12/31/07 Sneezy 13343 25303 2039

12/31/08 Sneezy 14442 26515 2240

12/31/09 Sneezy 15975 27163 2420

12/31/10 Sneezy 16867 27784 2741

12/31/11 Sneezy 18018 27981 2803

12/31/12 Sneezy 18253 28458 2920

12/31/13 Sneezy 18186 27956 3048

12/31/14 Sneezy 18293 29227 3047

12/31/15 Sneezy 17632 28162 2882

12/31/16 Sneezy 18276 28639 3161

12/31/07 Happy 12520 5351 1034

12/31/08 Happy 10049 4908 365

12/31/09 Happy 8410 4955 370

12/31/10 Happy 8599 4280 457

12/31/11 Happy 8407 4645 300

12/31/12 Happy 7944 4133 311

12/31/13 Happy 8313 4237 381

12/31/14 Happy 8425 4340 387

12/31/15 Happy 8840 4432 395

12/31/16 Happy 9653 4495 414

12/31/07 Bashful 8134 8045 1100

12/31/08 Bashful 8542 8132 1245

12/31/09 Bashful 9043 9005 1394

12/31/10 Bashful 9227 9340 1406

12/31/11 Bashful 13306 18855 1660

12/31/12 Bashful 14927 20099 1924

12/31/13 Bashful 16970 26250 2357

12/31/14 Bashful 18683 27329 2758

12/31/15 Bashful 16063 26126 2427

12/31/16 Bashful 12515 24615 1392

12/31/07 Dopey 9022 8794 576

12/31/08 Dopey 6385 3572 694

12/31/09 Dopey 6261 8726 431

12/31/10 Dopey 10040 9423 845

12/31/11 Dopey 12300 11045 1345

12/31/12 Dopey 27180 27174 5422

12/31/13 Dopey 23777 29526 4992

12/31/14 Dopey 19802 26698 4486

12/31/15 Dopey 20728 51674 4502

12/31/16 Dopey 26187 55474 4979