Can you help with the first Question and show how its suppose to look with Formulas in excel using the Vlook function 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 ) 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 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 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 Which plan do you recommend Carol implement Why

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 29, 2024

Can you help with the first Question and show how its suppose to look with Formulas in excel using the Vlook function. 3.47 CVP Analysis

Can you help with the first Question and show how its suppose to look with Formulas in excel using the Vlook function.

3.47CVP 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.)

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