+ The name of the individual student should be in cell A1 of the first tab in the spreadsheet.+ Any questions or any help that you need on this project should be addressed to the Teaching Assistant for this class Gianna Tascione. She can be contacted via iCollege email (use only iCollege email). + + 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.+K18 X V fx A C D E G Year Ended - Segment Name SALES . ASSETS PROFIT * 2007/12/31 Doc 42688 57670 7883 2008/12/31 Doc 41233 51863 7460 2009/12/31 Doc 18494 20377 3923 2010/12/31 Doc 17464 21175 8304 2011/12/31 Doc 18442 23567 3512 2012/12/31 Do 19322 251 44 3747 2013/12/31 Doc 21 411 32272 4345 2014/12/31 Doc 23298 33716 4973 10 2015/12/31 Doc 24242 34524 5507 11 2016/12/31 Doc 25530 38899 6115 12 2007/12/31 Sleepy 30347 31 466 4817 13 2008/12/31 Sleepy 41948 36973 6497 14 2009/12/31 Sleepy 40015 36663 7105 15 2010/12/31 Sleepy 37198 38606 7271 16 2011/12/31 Sleepy 43369 54389 3650 17 2012/12/31 Sleepy 6925 9253 131 18 2013/12/31 Sleepy 6721 9962 110 19 2014/12/31 Sleepy 14812 15536 677 20 2015/12/31 Sleepy 15329 21587 944 21 2016/12/31 Sleepy 14074 17858 311 22 2007/12/31 Grumpy 33219 310412 12243 23 2008/12/31 Grumpy 65937 627501 8063 24 2009/12/31 Grumpy 47437 597877 1325 25 2010/12/31 Grumpy 45214 565337 3158 26 2011/12/31 Grumpy 44658 552514 6549 27 2012/12/31 Grumpy 44327 539351 7401 28 2013/12/31 Grumpy 42917 516829 8258 29 2014/12/31 Grumpy 10283 502204 1209 30 2015/12/31 Grumpy 9650 316069 -7983 31 2016/12/31 Grumpy 9617 187804 -1251 32 2007/12/31 Sneezy 13343 25303 2039 33 2008/12/31 Sneezy 14442 26515 2240 34 2009/12/31 Sneezy 15975 27163 2420 35 2010/12/31 Sneezy 16867 27784 2741 36 2011/12/31 Sneezy 18018 27981 2803 37 2012/12/31 Sneezy 18253 28458 2920 38 2013/12/31 Sneezy 18186 27956 3048 39 2014/12/31 Sneezy 18293 29227 3047 40 2015/12/31 Sneezy 17632 28162 2882 41 2016/12/31 Sneezy 18276 28639 3161 42 2007/12/31 Happy 12520 5351 1034 43 2008/12/31 Happy 10049 4908 365 Snow White Segment Data +K18 X B C D E F G 1 Year Ended +t Segment Name SALES * ASSETS - PROFIT 41 2016/12/31 Sneezy 18276 28639 3161 42 2007/12/31 Happy 12520 5351 1034 43 2008/12/31 Happy 10049 1908 365 44 2009/12/31 Happy 8410 4955 370 45 2010/12/31 Happy 8595 4280 457 46 2011/12/31 Happy 8407 1645 300 47 2012/12/31 Happy 7944 4133 311 48 2013/12/31 Happy 8313 4237 381 49 2014/12/31 Happy 8425 4340 387 50 2015/12/31 Happy 8840 1432 395 51 2016/12/31 Happy 9653 4495 41 4 52 2007/12/31 Bashful 8134 3045 1100 53 2008/12/31 Bashful 8542 8132 1245 54 2009/12/31 Bashful 3043 9005 1394 55 2010/12/31 Bashful 9227 9340 1406 56 2011/12/31 Bashful 13306 18855 1660 57 2012/12/31 Bashful 14927 20099 1924 58 2013/12/31 Bashful 16970 26250 2357 59 2014/12/31 Bashful 18683 27329 2758 60 2015/12/31 Bashful 16063 26126 2427 61 2016/12/31 Bashful 12515 24615 1392 62 2007/12/31 Dopey 9022 8794 576 63 2008/12/31 Dopey 6385 3572 694 64 2009/12/31 Dopey 261 8726 431 65 2010/12/31 Dopey 10040 9423 845 66 2011/12/31 Dopey 12300 11045 1345 67 2012/12/31 Dopey 27180 27174 5422 68 2013/12/31 Dopey 23777 29526 4992 69 2014/12/31 Dopey 19802 26698 4486 70 2015/12/31 Dopey 20728 51674 4502 71 2016/12/31 Dopey 26187 55474 4979 72 73 74 75 76 77