Lyndia Company is a merchandiser that sells a total of 15 products to its customers. The company provided the following information from last year Product 2 3 Variable Cost per Unit $12.95 $68.55 $42.50 305.00 $16.95 5 7 8 9 10 11 12 13 14 15 Unit Sales 9,000 15,500 6,000 19.500 4,500 27.000 3,000 7.500 9,000 15,000 10,500 1,500 3,000 6,000 12.000 150,000 Selling Price per Unit $29 5,95 35 $100 $19 5119 539 $79 $769 $95 $59 5.65 $ $ 589 514.30 $33.18 $30.5 577.60 $25.40 $29.00 $12.40 $13.45 $61.83 Last year, Lyndia's total fixed expenses and net operating income were $3,000,000 and $1,223,070, respectively. The company would like your assistance in developing some financial projections for this year, Click here to download the Excel template which you will use to answer the questions that follow. Click here for a brief tutorial on Goal Seckin Excel Click hereaftalan Chants in Click here for littoralon Conditional Formatting in Excel 6. Assume the company believes it can increase profits by shifting the sales mix away from products with the highest selling prices and toward the products with the highest contribution margins per unit. To accomplish this goal, the company plans to begin puying sales commissions based on total contribution margin rather than total sales. It also plans to adjust its advertising expenditures to better support the new sales mix strategy. The company hopes these actions will increase the sales mix percentages of the products with the four highest contribution margins per unit by 2% per product. The company also expects a 2% drop in the sales mix percentages of the four products with the highest selling prices. (Hint: Before proceeding, go to row 21 of the spreadsheet and input the 2% Increase or decrease for each of the elght affected products, using Conditional Formatting.) a. If the company's sales mix shifts as just described and it sells a total of 150,000 units, what would be the projected sales for this year? How does this amount compare to the sales from last year? b. If the company's sales mix shifts as just described and it sells a total of 150,000 units, what net operating income will it earn this year? How does the projected net operating Income for this year compare to the sales from last year? c. Why does the contribution margin ratio shown in cell R29 differ from the contribution margin ratio shown in cell R9? c. Why does the contribution margin ratio shown in cell R29 differ from the contribution margin ratio shown in cell R9? Complete this question by entering your answers in the tabs below. Reg 6A1 Req 6A2 Reg 681 Reg 682 Reg 6C If the company's sales mix shifts as just described and it sells a total of 150,000 units, what would be the projected sales for this year? Projected sales for this year 1 Req 6A2 > . If the company's sales mix shifts as just described and it sells a total of 150,000 units, wha year? How does the projected net operating income for this year compare to the sales from c. Why does the contribution margin ratio shown in cell R29 differ from the contribution mar Complete this question by entering your answers in the tabs below. Req 6A1 Req 6A2 Req 6B1 Reg 6B2 Req 6C How do this year's projected sales compare to last year's sales? This year's projected sales are last year's sales Complete this question by entering your answers in the tabs below. Reg 6A1 Req 6A2 Reg 681 Reg 6B2 Reg 60 If the company's sales mix shifts as just described and it sells a total of 150,000 units, what net operating income will it earn this year? Not operating income Complete this question by entering your answers in the tabs below. Req 6A1 Req 6A2 Req 6B1 Reg 682 Req 6C How do this year's projected net operating income compare to last year's net operating income? This year's projected net operating income is last year's net operating income c. Why does the contribution margin ratio shown in cell R29 differ from the contribution margin ratio shown in cell R9? Complete this question by entering your answers in the tabs below. Reg A1 Req 6A2 Reg 681 Req6B2 Req 6C Why does the contribution margin ratio shown in cell R29 differ from the contribution margin ratio shown in cell R97 The sales mex has shifted to products with the highest selling prices per unit The sales mix has shifted to products with the highest contribution margins per unit . 6 10 > 11 12 23 14 15 Total X SON KE OOS S 00 61 100% 150,000 S S S 1RN 27,000 119.00 92.005 27.00 $ 2 3.000 39.00 $ 14.305 24.70 S 5% 7,500 29.00 33.18$ 45.825 % 9,000 69.00$ 30.36 $ 38.64 $ 15.000 95.00 77.60 $ 12.00 S 7% 10,500 59.00 $ 25.105 33.60 $ 13 1,500 65.00$ 29.00 $ 36.00 2% 3,000 44.00 12.40 S 31.605 4% 6,000 19.00 5 13.485 35.525 6,355 12.555 12,000 89.00 6183 27.17 $ 85,500 28,525 50,925 5 3,213,000 2.484,000 729,000 $ 117,000 $ 42,900 74,100 5 592.500 $ 248,850 343,650 $ 621,000 S 1,425,000 $ 271,240 1.161.000 347,760 5 261,000 $ 619,500 266,700 352,800 97,500 5 43,500 54.000 $ 132,000 5 37,200 94,800 $ 294,000 5 1,068,000 5 12.195,000 80 80 741.10 8.571230 213.120 S 326.040 4,223,070 3,000,000 $1,223,070 100.0 67.03 33.0 5 onditional Formatting Requirement1 Requirement 2 Requirement Requirement 4