Carol Mauldin, owner of Mauldin Mercantile Exchange, is considering  changing to a sales commission pay scale for her employees. Currently, her 20 salespeople are each paid  a flat salary of $45,000. She believes that by introducing sales commission on certain products, she can  increase sales volume and operating income. Carol has provided you with two data files: all sales transactions from the past year and a product list that contains sales price and variable cost for each item sold  by the company.

The Excel data files for answering this problem can be found in WileyPLUS.

Required 

(Evaluate each scenario independently and round dollar calculations to 2 decimal places.)

a. Calculate Mauldin’s current contribution margin using the sales data from the most current year. You will find Excel’s VLOOKUP function helpful in calculating the contribution margin of each  sales transaction.

b. Carol believes that reducing sales salaries to $30,000 per salesperson and adding a 5% commission (based on sales price) on sales of all products whose product number begins with a “2” will result  in an 8% increase in sales volume for those products. What will the new total contribution margin  be if this commission is implemented? What is the expected effect on operating income if this  commission is implemented? Round sales volumes to the nearest whole unit.

c. After discussing the potential sales commission plan with her salespeople, Carol learned that they would all prefer to be on a straight commission plan rather than a straight salary or salary and  commission. Based on market research, Carol and the salespeople believe that replacing the entire  $45,000 salary with a 6% commission will increase the sales volume of all products by 10%. What will  the new total contribution margin be if this new plan is implemented? What is the expected effect on operating income if this commission is implemented? Round sales volumes to the nearest whole unit.

d. Which plan do you recommend Carol implement? Why?