Instructions Read the problem below and refer to the spreadsheet provided. The spreadsheet values highlighted in yellow can be manipulated in order to determine possible outcomes and answer the questions. You must answer the follow-up questions (a-e) correctly to receive full credit. Problem: Franco Welles, sales manager for Nanek, Inc., is trying to decide whether to pay a sales rep for a new territory with straight commission or a combination plan. He wants to evaluate possible plans-to compare the compensation costs and profitability of each. Welles knows that sales reps in similar jobs at other firms make about $36,000 a year The sales rep will sell two products. Welles is planning a higher commission for Product B because he wants it to get extra effort. From experience with similar products, he has some rough estimates of expected sales volume under the different plans and various ideas about commission rates. The details are found in the spreadsheet. The program computes compensation and how much the sales rep will contribute to profit. "Profit contribution" is equal to the total revenue generated by the sales rep minus sales compensation costs and the costs of producing the units. Spreadsheet The spreadsheet values outlined in yellow can be changed in order to determine possible outcomes. You can find the initial values in the corresponding blue cells in columns E and F. Start by entering the initial values into columns B and C. Then review the questions below and adjust the values in columns B and C to determine the correct answers Initial Initial Commission Combination Plan Commission Combination Plan 3 Salary Component of Pay 15,000.00 5 Product A : Price 6 Commission Percent (Of Sales S) 7 Expected Sales (Units of A) 8 Unit Cost-ProductA 110.00 $ 110.00 3% 3,600 75.00 5% 4,000 75.00$ 10 Product B: Price 11 Commission Percent (Of Sales S) 12 Expected Sales (Units of B) 13 Unit Cost-Product B 150.00 5% 1,250 105.00 150.00 $ 8% 1,000 105.00 $ 15 Total (Sales) Revenue (TR) 16 Total Compensation (TC) 17 Profit Contribution 0.00 $ 0.00 $ 0.00 $ 590,000.00$ 0.00 0.00 0.00 4000.00$ 151,000.00 583,500.00 36,255.00 145,995.00 Questions a. For the initial values shown in the spreadsheet, which plan-commission or combination-would give the rep the highest Commission Compensation is equal with both plans b. Which plan would give the greatest profit contribution to Nanek, Inc.? Commission Profit contribution is equal with both plans c. Welles thinks a sales rep might be motivated to work harder and sell 1,100 units of Product B if the commission rate (under the commission plan) were increased to 10 percent. If Welles is right (and everything else stays the same), would the higher commission rate be a good deal for Nanek? O Yes, it will result in a higher profit contribution No, it will result in a lower profit contribution O It makes no difference, profit contribution doesn't change d. Instead of changing the commission rate for Product B, Welles wonders if a temporary price discount might be more effective. If he discounts the price by 10% he believes he can boost sales by 25% using the commission plan. What effect would that have on profit? Profit contribution would decrease by $9,000.00 Profit contribution would decrease by $3,250.00 Profit contribution would be unchanged Profit would increase by $3,300.00 O Profit would increase by $10,546.88 e. A sales rep interested in the job is worried about making payments on her new car. She asks if Welles would consider paying her with a combination plan but with more guaranteed income (an $18,000 base salary) in return for taking a 3 percent commission on Products B and A. If this arrangement results in the same unit sales as Welles originally estimated for the combination plan, would Nanek, Inc. and the sales rep, be better off or worse off under this arrangement? This would be better for both Nanek and the sales rep This would be worse for Nanek but better for the sales rep This would be better for Nanek but worse for the sales rep This would be worse for both Nanek and the sales rep This would make no difference for either Nanek or the sales rep