These are the part of problems of this questions that I tried to solve, This is most of the info I have

43 44 What criteria did you use to determine if the segment was unprofitable? 45 46 Do you have any recommendations for management? How could profitability be improved? How should they decide if collections should be discontinued? 47 48 49 50 What is the breakeven point in sales dollars for the company as a whole? 51 Breakeven point 52 53 Why should management know the breakeven point? A B C 3 Row Labels - Sum of contribution margin Sum of CMratio 4 Elements 9438793.057 34.59% 5 Jeffrey Alexander 50927391.87 39.88% 6 Grand Total 60366184.93 38.95% 7 3 Row Labels 4 Elements 5 Somerset 6 Strickland 7 Capri 8 Seaver 9 Hadley 10 Park 11 Verona 12. Watervale 13 Geneva 14 Madison 15 Calloway 16 Gatsby 17 Palisale 18 Vienna 19 Torino 20 Lindos Sum of contribution margin Sum of CMratio 9438793.057 34.59% 144019.59 36.90% 229127.81 36.87% 101880.74 36.81% 117956.32 36.64% 140120.57 36.51% 144878.46 36.41% 185697.09 36.19% 81212.58 36.14% 113045.93 35.92% 2580087.52 35.90% 313366.7 35.88% 201421.39 35.81% 41355.87 35.78% 65020.39 35.66% 182328.94 35.65% 215710.72 35.59% 86869.55 35.55% 78476.12 35.50% 70347.14 35.49% 422612.54 35.45% 1702270 25.100% 21 Kenner 22 23 Kingsport Syracuse Brenton Zachan 24 A B D 24 Brenton 25 Zachary 26 Edgefield 27 Cypress 28 Stanton 29 Tempo 30 Slade 31 Naples 32 Asher 33 Glendale 34 Arcadia 35 Florence 36 Drake 37 Sedona 38 Merryville 89 Cosgrove 10 Hammond 41 Westbury 42 Luxe 43 Belfast 44 Aiden 45 Jeffrey Alexander 16 Grand Total 422612.54 178237.93 430484.57 50971.79 557570.34 45087.68 245786.04 906428.01 280793.67 142208.78 131615.52 146483.33 168999.88 60920.42 76291.5 232281.65 63704.65 62136.76 45784.73 291598.12 -194128.2832 50927391.87 603661010 C 35.45% 35.43% 35.36% 35.27% 35.11% 35.00% 34.91% 34.90% 34.89% 34.86% 34.52% 34.51% 34.48% 34.14% 34.11% 34.02% 33.92% 33.87% 33.81% 33.57% -196.39% 39.88% 200 + B 49 A 45 Jeffrey Alexander 46 Hudson 47 Key West 48 Montclair Odessa 50 Symphony 51 Solana 52 Alvar 53 Kensington 54 Bienville 55 Latches Rae 57 Bella 58 Regency 59 Sutton 60 Padua 61 Sonoma 62 Escutcheons 63 Lexa 64 Durham 65 Prestige 66 Callie 50927391.87 699920.18 9052.37 1264746.53 165757.95 177175.5 227745.06 245219.6 149264.33 67868.96 57144.52 3057827.93 1341046.05 204771.12 565586.25 106901.48 749084.97 98689.24 518954.57 1568899.91 1871994.79 309323.74 5200 74 fixed cost data 39.88% 42.82% 42.49% 42.03% 41.75% 41.53% 41.49% 41.42% 41.36% 41.35% 41.29% 41.10% 41.03% 41.03% 40.98% 40.97% 40.81% 40.80% 40.74% 40.73% 40.71% 40.51% 56 607 Mirada 10707 Part 1 Part 2 Data 02 03 B A D 93 94 Marlo Lille Lafayette Ella 95 96 1397105.58 1156335.21 716660.33 1345238.96 756603.45 542234.78 106084.85 251736.88 248834.28 97 Modena 98 Leyton 99 Lenoir 100 Zurich 101 Encada 102 Backplates 103 Delgado 104 Duval 105 West 106 Annadale 107 Anwick 108 Tuscany 109 Venezia 110 Evangeline 111 Tahoe 112 Rochester 113 Grand Total 14 325264.39 2581128.16 1515765.41 203540.34 595093.13 844621.5 39.37% 39.33% 39.31% 39.29% 39.28% 39.24% 39.19% 39.18% 39.16% 39.12% 39.08% 39.05% 39.03% 38.97% 38.77% 38.71% 37.95% 37.66% 37.20% -116.829 38.95 354717.48 139056.15 115710 861614.46 -123097.5454 60366184.93 28 Question 5 Create a pivot table to answer the questions: "Within each brand, what was the most profitable collection in 2018, as measured by the contribution margin ratio? The least most profitable collection for each brand? Use the field "years" to filter the data to include just the year of 2018. You will need to add a calculated field to the pivot table to calculate the contribution margin ratio. Within each brand, sort the collections by contribution margin ratio, from the largest to the smallest. When creating the pivot table, select "new worksheet". Once the pivot table is 9 created, rename the worksheet to "Q5". 0 1 Year ended 2018 Elements Jeffrey Alexander 2 Most profitable collection Somerset Hudson 3 Least profitable collection Aiden Rochester 4 Interpret your findings. Are any collections unprofitable? Which one(s)? if there aren't four unprofitable brands, select N/A for the remaining answers. 6 7 Brand Unprofitable collections: Elements Jeffrey Alexander Brand 1 Alden Rochester Brand 2 IN/A N/A 1 Brand 3 IN/A N/A 2 Brand 4 IN/A IN/A Part 1 Part 2 fixed cost data Data Q2 Q3 05 Q6 Group Member Evaluati 1 Fixed cost data 2 For year of 2018 3 4 Central East coast International Midwest Northeast Northwest South West 5 Traceable fixed expenses $5,278,312 $ 7,513,545 $ 690,587$ 9,874,330$ 3,965, 187 $ 4.968,774 $ 6,849,541 $ 9,065,478 6 Common allocated costs $ 1,219,464 $ 1,589,821S 217,290 $ 2,018,055 814,835 $ 1,075,209 $ 980,076 $ 1,665,250 7 8