You own the Mayer Electrical Distribution Company, and your goal is to maximize your before- tax profits. You currently pay salespeople a base salary and a flat 4 percent commission on sales. You sell three products. Following this question is an Excel spreadsheet printout titled "Current Situation" that gives the starting information. 4. Unit Costs do not include sales commission costs. You have 10 salespeople. You pay them $30,000 salary and $10,000 benefits each. This gives you $400,000 fixed sales costs. You have annual fixed costs of $1,400,000 (including fixed sales costs). Unfortunately, your firm keeps losing the best salespeople. You are considering several options to retain your best salespeople. ase the commission to 6 percent or (2) alter the commission structure to reflect the fitability of each product. You think that if the total compensation plan is increased for the ro sales force your sales will increase 10 percent next year due to retaining the best people and increased motivation. Without this increase in sales compensation, you think that sales wil increase 4 percent. You might want to look at the potential financial impact of (a) doing nothing (b) increasing the commission from a flat 4 percent to a flat 6 percent, or (c) increasing the commissions in a manner that reflects the profitability of each product. 1. What was your total profit before taxes this year? 2. What is your expected total profit before taxes next year if the sales force payment plan remains the same (and sales increase 4 percent)? 3. What is your expected total profit before taxes next year if you increase commissions to 6 percent? A quick woay to complete this exercise is to copy the first spreadsheet and then adjust the copied tables for the new information given above. Note that you will have to change the formulas in the cells titled "Commissions per Unit" and "Sales Commissions" to reflect the new adjustment on the commission increase. For example, to change the commission increase to percent, you just multiply the unit sales by the new increase and change the sales commission percentage. Also, remember to change the units sold formula. The program automotically recomputes all of the other calculations 2 Current Situation ($30000 & 4% commission) 4 Price Per Unit 5 Cost Per Unit 6 Commissions per Unit 7 Contribution per Unit Product 3 355.00 $195.25 $14.20 $145.55 $117.00 $29.00 $17.40 $1.16 $10.44 $501.00 ! $267.65 $20.04 $213.31 $55.00 $4.68 S57.32 10 Units Soid 1 Dollar Sales 12 Dollar Costs 13 Sales Commissions 14 Net Contribution 15 16 Total Net Contribution 17 Total Pretax Profit 18 19 15674 12334 9123 37131 $1,833,858.00 357,6600 3.238,665.00 $5,430,209.00 S862,070.00 $73.354.32 $898 433.68$128,766.96 1,327,852 65 2,355,053.29 $214.611.60 $1,781,265.75 $2,857 947 35 $14,30744 $129,546.60 $217,208.36 $2,355,053 29 955,053 29 24 25 26 Problem 2