Complete the following analytics problem using the Excel data set below. Prepare your answers in a new worksheet tab within that workbook and upload your Excel file. Be sure that your answers are clearly labeled.

3.47 CVP Analysis

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 a 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.

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

1. 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.
2. 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.
3. 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.
4. Which plan do you recommend Carol implement? Why?

**********I AM MISSING THE DATA FILE BECAUSE I AM HAVING TROUBLE UPLOADING BECAUSE IT SAYS THE FILE IS TOO LARGE TO INCLUDE AND UPLOAD CAN I SEND IT ANOTHER WAY?

Product List is below

Product number	Sales Price	Variable Cost per Unit
1-101	$ 31.00	$ 15.00
1-114	$ 62.00	$ 49.00
1-128	$ 66.00	$ 44.00
1-252	$ 47.00	$ 28.00
1-273	$ 55.00	$ 34.00
1-451	$ 38.00	$ 21.00
1-570	$ 26.00	$ 13.00
1-598	$ 15.00	$ 6.00
1-622	$ 63.00	$ 40.00
1-648	$ 72.00	$ 57.00
1-677	$ 69.00	$ 39.00
1-687	$ 73.00	$ 51.00
1-707	$ 56.00	$ 32.00
1-742	$ 33.00	$ 20.00
1-760	$ 25.00	$ 16.00
1-800	$ 38.00	$ 18.00
1-820	$ 15.00	$ 11.00
1-835	$ 33.00	$ 23.00
1-860	$ 74.00	$ 46.00
1-880	$ 77.00	$ 58.00
1-920	$ 58.00	$ 37.00
1-940	$ 7.00	$ 4.00
1-980	$ 43.00	$ 29.00
2-100	$ 60.00	$ 35.00
2-125	$ 70.00	$ 36.00
2-170	$ 46.00	$ 23.00
2-210	$ 28.00	$ 17.00
2-240	$ 84.00	$ 68.00
2-270	$ 100.00	$ 74.00
2-350	$ 42.00	$ 26.00
2-380	$ 55.00	$ 33.00
2-435	$ 37.00	$ 13.00
2-475	$ 45.00	$ 31.00
2-510	$ 77.00	$ 53.00
2-700	$ 98.00	$ 68.00
2-750	$ 51.00	$ 33.00
2-810	$ 8.00	$ 5.00
2-835	$ 25.00	$ 16.00
2-880	$ 42.00	$ 22.00
2-910	$ 71.00	$ 52.00
2-935	$ 8.00	$ 2.00
3-120	$ 91.00	$ 64.00
3-140	$ 75.00	$ 41.00
3-200	$ 91.00	$ 68.00
3-240	$ 51.00	$ 37.00
3-270	$ 37.00	$ 18.00
3-325	$ 12.00	$ 7.00
3-345	$ 18.00	$ 6.00
3-375	$ 58.00	$ 24.00
3-400	$ 28.00	$ 22.00
3-455	$ 61.00	$ 53.00
3-480	$ 36.00	$ 16.00
3-530	$ 7.00	$ 3.00
3-555	$ 25.00	$ 18.00
3-660	$ 6.00	$ 3.00
3-695	$ 8.00	$ 4.00
3-725	$ 45.00	$ 25.00
3-750	$ 100.00	$ 82.00
3-820	$ 71.00	$ 49.00
3-910	$ 59.00	$ 35.00
3-980	$ 60.00	$ 38.00
4-104	$ 27.00	$ 12.00
4-145	$ 63.00	$ 42.00
4-180	$ 91.00	$ 67.00
4-200	$ 92.00	$ 72.00
4-226	$ 31.00	$ 18.00
4-270	$ 87.00	$ 50.00
4-410	$ 37.00	$ 15.00
4-460	$ 54.00	$ 26.00
4-490	$ 25.00	$ 14.00
4-535	$ 88.00	$ 64.00
4-580	$ 51.00	$ 28.00
4-625	$ 32.00	$ 13.00
4-660	$ 97.00	$ 53.00
4-715	$ 80.00	$ 64.00
4-740	$ 68.00	$ 31.00
4-870	$ 100.00	$ 59.00
4-890	$ 10.00	$ 6.00
4-935	$ 12.00	$ 4.00
4-970	$ 10.00	$ 4.00