A B D E F Question 4: Assessing Profitability (10 marks + 2 BONUS marks) G A company has provided the following data from their information system. 1. Calculate the total sales revenue (column K), total variable cost (column (column M). (3 marks) and total contribution margin 2. Create a pivot table of the dataset so that you may answer the questions below (place the table on the sheet titled Q4 pivot" If you can't place it on that worksheet, just select new worksheet!) (2 marks) 3. Answer the following questions: a. The most profitable collection as determined by contribution margin in dollars (1 mark) A Cover Sho B D E F 3. Answer the following questions: a. The most profitable collection as determined by contribution margin in dollars (1 mark) b. The least profitable collection as determined by contribution margin in dollars (1 mark) c. The region with the highest amount of sales (1 mark) d. When comparing the different collections, what is the better measure of performance? Contribution margin in dollars, or contribution margin ratio? Explain your reasoning. (2 marks) > A Cover Sheet Q1 Q2 Q3 alculation Mode: Automatin Q4 Q4 pivot Q5 Formular BONUS 1: Add a calculated field in the pivot table to calculate CM ratio of each brand (1 mark) BONUS 2: What is the most profitable brand as determined by CM ratio? (1 mark) Requirement 1: total sales customer_number region brand collection quantity_s old total variable cost total contrib margin revenue 546713 Northwest Elements Bremen 185 562341 Central Lyon Elements Jeffrey Alexander list_price cost $ 126.00 $77.79 $ 8.29 $ 4.89 $ 7.89 $ 4.21 $ 19.50 $11.74 285 573767 East coast Strickland 776 589890 West date_of_sale item 1/4/2015 537-12DBAC 1/4/2015 3991NI 1/4/2015 771-96BNBDL 1/4/2015 286-192BNBDL 1/4/2015 918-160BNBDL 1/4/2015 PE06-SN 1/4/2015 984-160SN 1/4/2015 867-128NI 1/4/2015 8233NI Elements 417 592125 Northwest description 2 12" CC app. Pull Knob 96" CC pull 192 mm CC pull 160 mm CC pull Pull Escutcheon 160" CC pull 128 mm CC pull 3" CC pull Leyton Prestige Escutcheons Elements 656853 South $ $ 30.05 $18.22 1.78 $ 1.02 7.58 $ 4.41 426 410 Elements Jeffrey Alexander Elements Slade 683610 West 710669 Northeast 298 $ $ Anwick 17.25 $11.23 412 716647 Northeast Elements Lyon $ 6.95 $ 4.47 519