Required information Lyndia Company is a merchandiser that sells a total of 15 products to its customers. The company provided the following Information from last year: Selling Price Variable Cost Product Unit Sales pee Unit per Unit 1 9,000 $ 29 $12.95 2 16,500 $.99 $68.55 3 6,000 $85 $42.50 19,500 $109 385.00 4,500 $ 19 $5.35 27,000 $119 $92.00 3,000 $ 39 $14.30 7,500 $ 79 $33.18 9 9,000 $ 69 $30.36 10 15,000 $95 377.60 11 10,500 $ 59 $25.40 12 1,500 $65 $29.00 13 3.000 $ 44 $12.40 14 6.000 $.49 $13.48 15 12.000 5 89 $61.8) 150,000 Last yearLyndia'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 tutorialon Coal Seek in Excel Click here for a brief tutorial.on Charts in Excel 3. Refer to the original data (in other words, return cell 015 to its original value of 0%) and assume the sales mix percentages (as shown in tows 3 and 21) hold constant a. Using Goal Seek. calculate the total unit sales required to break even. (Hint: Instruct Goal Seek to obtain a net operating income of $0, as shown in cell 031, by changing the unit sales in cell 014.) b. What are the dollar sales required to break even? c. What was the company's margin of safety last year? 4. Refer to the original data (in other words, return cell (14 to its original value of 150,000 units). Assume the sales mix holds constant and the company plans to increase the selling prices of all products by 5%. (Hint: Focus on cell 016 to input this projection) 6. Using Goal Seek. calculate the total unit sales required to break even. Is your answer greater than, less than or equal to the answer you obtained in requlrement 3a? b. How is the amount in cel B23 calculated? c. Why does the contribution margin ratio shown in cell R29 differ from the corresponding percentage from lost year, as shown in cell R9? d. Should the company increase its selling prices by 5% this year? Req3 RA Reg ReG41 Req4C Reg A2 Reg 45 Reg 40 Using Goal Seek, calculate the total unit sales required to break even. Hint: Instruct Goal Seek to obtain a net operating income of $0, as shown in cell 031, by changing the unit sales in cell 014.) Untus to break oven units Reg Reg 3B > Reg 3A Req 3B Reg 3C Red 4A1 Reg 4A2 Reg 4B Reg 40 Reg 4D What are the dollar sales required to break even? Dollar sales to break even Reg 3A Req 3B Reg 30 Reg 4A1 Req 4A2 Reg 4B Reg 40 Red What was the company's margin of safety last year? Margin of safoty last year Req 3A Req 3B Reg 3C Req 4A1 Req 4A2 Req 4B Reg 4C Using Goal Seek, calculate the total unit sales required to break even. Unit sales to break even units Reg 3A Reg 3B Reg 3C Req 4A1 Req 4A2 Reg 48 Reg 4C Reg 4D Which of the following statements is true? The answer in Requirement 481 is the unit sales to break even in requitement 3a because Req Req 3B Req 30 Req 4A1 Req 4A2 Reg How is the amount in cell B23 calculated? Selling price per unit - (Selling price per unit x Change in selling prices) Selling price per unit + (Selling price per unit * Change in selling prices) Selling price per unit x (Selling price per unit Change in selling prices) Selling price per unit - (Selling price per unit Change in selling prices) Reg 3A Req 3B Reg 30 Reg 4A1 Req 4A2 Reg 4B Reg 40 Reg 4D Which of the following statements is true? Olt the company increases its selling prices by 5%, it will increase profits because the break-even point in units) at these higher paces is less than the break-even point at the original prices Of the company increases its selling prices by 5%, it will decrease profits even though the break even point (in units) at these higher prices is less than the break-even point at the original prices Of the company increases its selling prices by 5%, the impact on profits will depend on how the price hike affects customer demand Reg 4C Reg 4D E 1 1 2 3 4 5 6 7 8 9 10 Last Year: Unit Sales ales mi percentages cling price per unit Variable expense per unit 9,000 6% 29.00 $ 12.955 16 500 111 99.00 68.555 6,000 6% 85.00 S 42.50 S 19,500 13% 109.00 85.00 5 4,500 3% 19.00 S 6.355 27,000 18% 119.00 $ 92.00 S 3,000 2 39.00 $ 14.30 S 7,500 5 29.00 $ 33.18 9,000 GX 69.00 $ 10.365 S S 15,000 10% 95.00 77.60 $ Sales Variable expenses Contribution man Fed expenses Net operating income 261.000 $ 116,550 164 ASOS 1.633.500 $ 1,131,075 502.425 S 510,000 $2,125,500 $ 255,000 1.657.500 255,000 $ 468,000 $ 85.500 S 3,213,000 S 28.575 2.484,000 56,925$ 729,000 $ 117.000 42.900 74,100 $ 592,500 5 248.850 343.650 S 621,000S 1.425.000 273,240 1.164 000 347,7605 261.000 5 Projections for This Year: 4. Last year un cales 5 Change in sales Changin selling prices Change in watiable expenses 13. Change in fixed expenses 19 1 2 3 4 5 6 7 8 9 9 2 $ 5 20 21 Sales Deres 32 Unit sales 23 Selling price per unit 24 Variable expense per unit 25 Contribution margin per unit 26 27 Sales 78 Variable expenses 25. Contulion man 6X 9.000 29.00 $ 12.95 S 16.055 11 16,500 99.00 68.555 30.455 5,000 85.00 5 42.50 $ 42.50 $ 12% 19,500 109.00 $ 35.00 $ 24.00 $ 3 4.500 19.00 6.35 $ 12.655 185 27,000 119.00 5 92.00 5 27.00 5 2% 3.000 29.00 S 16.10 20.70 5 5 7,500 79.00 $ 185 *5.82 9,000 100$ 30.365 2864 10 15.000 95.00 5 22.00 5 17.00 5 5 261.000 5 116550 144.450 1,671,300 $ 1,181,075 5024255 510,000 $2,125,500 5 85,500 $ 1.211.000 5 255,000 1657,500 28.575 2,486,000 255.000 5 468.000 56.925 729.000 5 Benuirement 2 Rout Requirement 4 1170005 42.900 74,100 $ 592.500 $ 28SO HAOS 621,000 $ 1.425.000 5 273340 1.154,000 347,250 $ 2015 S M H N P a R O 9 8 6 7 10 11 15 ZT ET 14 Total X Weighted Average CM per Unit 100N 18% 119,00 $ 92.00 5 S S 2 39.00 S 1430S 5% 29.00 33.18 S 6% 69.00 $ 30.365 101 05.00 77.60S 3% 59,00 5 25.40$ 1% 65.00 29.00 $ 2 14.00 S 12.40 $ 8% 89.00 61.33 49.00 11485 $ 3.213.000$ 2,484.000 5 729.000 5 117,000 $ 42.900 74.100 5 592,500 248,850 343,650 $ 21,000 1,425,000 $ 273,240 1.154,000 117,760 S 261 000 $ 619.500 5 266,700 352,800 97,500 43,500 50.000 $ 132,000 $ 37,200 1,800 $ 294,000 1,000 $12.795,000 80,80 261,960 3,571,90 213,1205 4,223,070 3,000,000 $1,223,070 10006 07.01 LON : 150.000 ON on on UN 14 13 15 Total Weighted Average CM per un 11 9 12 10 8 7 NE 100 150,000 18% 27.000 119,00 5 92.00 S 27.00 2N 3,000 39.005 1430S 24.705 5% 2,500 2900 5 33.15 05 82 S 196 9,000 69.00 M.365 38.64 $ 10% 15,000 9500 17.60S 17.40S 7 10.500 59.00 $ 25.40 S 31605 1% 1,500 65.00 5 20.00$ 36.00 S 3,000 44.00 5 12.405 31.05 6,000 49.00 $ 12.48 35.525 S $ 5 17.000 89.00 6183 27.17 $1,2130005 2484000 S 729.000 592,500 5 248,850 341,650 $ 117.000 42.900 74,1005 619,500 5 266,700 3525 51.000 51.425,000 5 27,740 1,164,000 147,2605 261.000 $ 97,500 5 1,500 54.000 5 2.15 100 ON 2.0 33.0 187,000 37,200 9400 5 294,000 5 16 DO 512,790,000 880 741950 R,571,90 311120S 326 010 4,221,070 3.000.000 Rent Requirement 2 - o Requirements Requirement 4