Carol believes that reducing sales salaries to $29,000 per salesperson and adding a 6% commission (based on sales price) on sales of all products whose product number begins with a "4" will result in an 9% increase in sales volume for those products. What is the new contribution margin given these changes? Example with a made-up number: If there was a Product Number 4-100 that was 100 units sold to a customer, then in this new scenario there would be 109 units sold to the customer. This will hold for all Product Numbers that start with 4-XXX. Round the new sales volume up or down to the decimal places. Round your final answer to decimal places. Some hints: 1. you are given product numbers like 6-789. You need to separate the first 6 from the -789. There are a few excel functions to do that 2. IF is helpful in figuring out the new sales volume for each sale and for figuring out the commission for each sale. 3. Treat the separated number, 6, as text when using your IF statements Put in your final answer without the $ sign. Units 104 145 180 4 200 4 -4 226 270 410 460 490 0 1 2 3 14 35 36 7 18 39 30 31 12 13 34 35 36 97 98 19 4-104 4-145 4-180 4-200 4-226 4-270 4-410 4-460 4-490 4-535 4-580 4-625 4-660 4-715 4-740 4-870 4-890 4-935 4-970 109% 113.36 $ 158,05 $ 1962 $ 218 $ 246.34 $ 294.3 $ 446,9 $ 501.4 $ 534.1 S 583.15 $ 632.2 $ 681.25 $ 719.4 $ 779.35 $ 806.65 948.3 $ 970.15 1019.1515 1057.3 S Price per unit Sales revenue VC per unit 27.0018 3,060.72 5 12.00 63.00 $ 9,957.15 S 42.00 91.00 $ 17,854.20 $ 67.00 92.005 20,056.00 $ 72.00 31.00 $ 7,636.54 $ 18.00 87.00 $ 25,604.10 $ 50.00 37.00 $ 16,535.30 $ 15.00 54.00 $ 27,075.60 $ 26.00 25.0015 13,352.50 S 14.00 88.00 $ 51,317.20 $ 64.00 51.00 $ 32,242.205 28.00 32.00 $ 21,800.00 $ 13.00 97.005 69,781.80 $ 53.00 80.0015 62,348.00 $ 64.00 68.005 54,848.80 $ 31.00 100.00 $ 94,830.00 $ 59.00 10.00 $ 9,701.00 $ 6.00 12.00 $ 12,229.80 $ 4.00 10.00 S 10,573.00 $ 4.00 1nssms560.803.91 -4 535 580 625 660 715 740 870 890 935 970 -4 -4 -4 Carol believes that reducing sales salaries to $29,000 per salesperson and adding a 6% commission (based on sales price) on sales of all products whose product number begins with a "4" will result in an 9% increase in sales volume for those products. What is the new contribution margin given these changes? Example with a made-up number: If there was a Product Number 4-100 that was 100 units sold to a customer, then in this new scenario there would be 109 units sold to the customer. This will hold for all Product Numbers that start with 4-XXX. Round the new sales volume up or down to the decimal places. Round your final answer to decimal places. Some hints: 1. you are given product numbers like 6-789. You need to separate the first 6 from the -789. There are a few excel functions to do that 2. IF is helpful in figuring out the new sales volume for each sale and for figuring out the commission for each sale. 3. Treat the separated number, 6, as text when using your IF statements Put in your final answer without the $ sign. Units 104 145 180 4 200 4 -4 226 270 410 460 490 0 1 2 3 14 35 36 7 18 39 30 31 12 13 34 35 36 97 98 19 4-104 4-145 4-180 4-200 4-226 4-270 4-410 4-460 4-490 4-535 4-580 4-625 4-660 4-715 4-740 4-870 4-890 4-935 4-970 109% 113.36 $ 158,05 $ 1962 $ 218 $ 246.34 $ 294.3 $ 446,9 $ 501.4 $ 534.1 S 583.15 $ 632.2 $ 681.25 $ 719.4 $ 779.35 $ 806.65 948.3 $ 970.15 1019.1515 1057.3 S Price per unit Sales revenue VC per unit 27.0018 3,060.72 5 12.00 63.00 $ 9,957.15 S 42.00 91.00 $ 17,854.20 $ 67.00 92.005 20,056.00 $ 72.00 31.00 $ 7,636.54 $ 18.00 87.00 $ 25,604.10 $ 50.00 37.00 $ 16,535.30 $ 15.00 54.00 $ 27,075.60 $ 26.00 25.0015 13,352.50 S 14.00 88.00 $ 51,317.20 $ 64.00 51.00 $ 32,242.205 28.00 32.00 $ 21,800.00 $ 13.00 97.005 69,781.80 $ 53.00 80.0015 62,348.00 $ 64.00 68.005 54,848.80 $ 31.00 100.00 $ 94,830.00 $ 59.00 10.00 $ 9,701.00 $ 6.00 12.00 $ 12,229.80 $ 4.00 10.00 S 10,573.00 $ 4.00 1nssms560.803.91 -4 535 580 625 660 715 740 870 890 935 970 -4 -4 -4
