C D E G H T J 1 N 0 P 0 R 5 T L 1 2 E 4 5 1 7 DE 11 12 11 15 Tata % Wight Argo OM purune 9.00u no's 4,50 COSE 2,000 20.SUD 3.200 28 15,000 20% 1,500 34 " 150,000 1104 48 AF W Last Year: 2 Untu 3 Scheme 4 Selling print 5 War III G 7 Sales 29.SUD 13% *112 1115 . 5 son 1bs02 11% 9.03 RSS S E 27,000 98 19.01$ 92 $2.00 6.100 4% 10.00 3,020 29 14.045 -2.40 CONTI 4 CUS 25.15 S . 5 3 29.03 13.95 5 19.10 6.35 $ 78.113 11.15 5 SULCP 599. 10.16 SUGE ?! S UE I $ 5! 5 ouw $ $ 50.00 5 75 ans . 6181 3 > 77.63 79.00 0 HET BE $ 100. 261.000 $ 1.6.550 144,450 $ 1.833,303 $ 1.131,075 502425 S 510.000 2.125.500 5 255.000 1.667.900 255.000 468.000 $ $ 85,500 $ 3213,000 1.7.500 5 25,57 2,484,000 42.000 55,925 S 729,000 $4,100 $ $ 592 303 248, 259 243,650 $ 621,000 $ 3,425.000 6.9.500 $ $ 5 273,240 2,154,000 266.700 347,750 S 250.000 352.200 $ 97,500 48,500 54,000 $ $ 152,000 $294.000 $ 1.068.000 $ 12,795,000 $ 37,200 20.30 741962 94,800 $ 2.3.120 $ 226,042 4223,070 OGT25'8 $ $ 33.3 28.5 000'COD'S $ 1223,070 9 Comaribuniemand 10 Feense 11 Net Operacing income . 13 Projections for This Year: 14 Layernhales 15 things in niti 16 charysin 17 change in variable expersia 16 inlal 19 000 CS 09 04 04 2 E 4 5 . 7 7 D DE 11 12 13 14 15 Tate % Wild Aware OM pur Unit 4% 54 na's 13% -9.00 302.00 $ COEU 2,000 ' 100% 259.000 11% 16.30 9.013 $ 6:55 $ CONTI 64 9.00 20.00 $ 13959 1605 S 22 4,500 19.00 01 12.65 -0% 15,0cu 95.cu 770115 $ 18% 22,020 139.00 5 27.00 $ 20.30D 52.00 $ 2% 1,020 14.00 2.40 5 25.cu 17.511 5 42 103 5 3,000 39.90 143 2475 3 $ USHI DOES 1,500 A.DOS 79.15 96.00 $ 79.03 11.15 S US $ SUCI 10.1 19.01$ 6.200 49.00 1140 3552 9.09 51.11 27.11 UV $ $ 9 t $ SPO 7109 'BE SUV 2. SDITE 20 21 Salema pe cele 22 Uno 23 Seling print 24 PREMIO PRU- 25 Camini mingin perunit 26 27 Sales 28 Variable 29 combuilom. 30 Fans 31 Net operating corre 32 $ $ 1 261.000 $ 1.6.15 144,450 $ 100.04 1.533,500 $ 1.31075 302.425 5 510.000 2.125.500 $ 255,000 1.667.100 255.000 468.COD 85,500 $ 3,213,000 $ 25,575 2,484,00 55,925 729,000 1.7.2008 $ 42.600 74.100 $ 592.500 5 248.853 245,650 $ 621,000 $2,425.000 $ 279,240 -,154,000 347,750 S 25,000 $ 5.9.500S 206.200 97,500 $ 48,500 54,00 140 29 $ $ $ CORTE 132,0505 294.000 $ 1.068,000 S 12,795,000 37,220 80,00 741963 8,971,930 94,800 $ 2.3,120 $ 226,042 4223,070 3,000,000 $ 1,223,070 ? 23.04 28.5 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: Product 1 2 3 TITT 1456789BnNBH Unit Sales 9,000 16,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 $ 99 $ 85 $109 $ 19 $119 $ 39 $ 79 $ 69 $ 95 $ 59 $ 65 $ 44 $ 49 $ 89 Variable Cost per Unit $12.95 $68.55 $42.50 $85.00 $ 6.35 $92.00 $14.30 $33.18 $30.36 $77.60 $25.40 $29.00 $12.40 $13.48 $61.83 10 11 12 13 15 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 Seek in Excel. Click here for a a brief tutorial on Charts in Excel. Click here for a brief tutorial on Conditional Formatting in Excel. 3. Refer to the original data (in other words, return cell Q15 to its original value of 0%) and assume the sales mix percentages (as shown in rows 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 Q31, by changing the unit sales in cell Q14.) 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 Q14 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 Q16 to input this projection.) a. 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 requirement 3a? b. How is the amount in cell B23 calculated? Why does the contribution marain ratin chown in coll D20 differ from the correcnandina narrontare from last voar ac chown in coll 3. Refer to the original data (in other words, return cell Q15 to its original value of 0%) and assume the sales mix percentages (as shown in rows 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 Q31, 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 Q14 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 Q16 to input this projection.) a. 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 requirement 3a? b. How is the amount in cell B23 calculated? c. Why does the contribution margin ratio shown in cell R29 differ from the corresponding percentage from last year, as shown in cell R9? d. Should the company increase its selling prices by 5% this year? Req 3A Req 3B Req 3C Req 4A1 Req 4A2 Req 4B Req 4C Req 4D 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 Q31, by changing the unit sales in cell Q14.) Unit sales to break even 106,558 units Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D What are the dollar sales required to break even? Dollar sales to break even 9,089,359 Req Req 3B Req 3C Req 4A1 Req 4A2 Req 4B Req 4C Req 4D What was the company's margin of safety last year? Margin of safety last year Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D Using Goal Seek, calculate the total unit sales required to break even. Unit sales to break even units Req 3C Req 4A2 > Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D Which of the following statements is true? The answer in Requirement 4a1 is the unit sales to break even in requirement 3a because C D E G H T J 1 N 0 P 0 R 5 T L 1 2 E 4 5 1 7 DE 11 12 11 15 Tata % Wight Argo OM purune 9.00u no's 4,50 COSE 2,000 20.SUD 3.200 28 15,000 20% 1,500 34 " 150,000 1104 48 AF W Last Year: 2 Untu 3 Scheme 4 Selling print 5 War III G 7 Sales 29.SUD 13% *112 1115 . 5 son 1bs02 11% 9.03 RSS S E 27,000 98 19.01$ 92 $2.00 6.100 4% 10.00 3,020 29 14.045 -2.40 CONTI 4 CUS 25.15 S . 5 3 29.03 13.95 5 19.10 6.35 $ 78.113 11.15 5 SULCP 599. 10.16 SUGE ?! S UE I $ 5! 5 ouw $ $ 50.00 5 75 ans . 6181 3 > 77.63 79.00 0 HET BE $ 100. 261.000 $ 1.6.550 144,450 $ 1.833,303 $ 1.131,075 502425 S 510.000 2.125.500 5 255.000 1.667.900 255.000 468.000 $ $ 85,500 $ 3213,000 1.7.500 5 25,57 2,484,000 42.000 55,925 S 729,000 $4,100 $ $ 592 303 248, 259 243,650 $ 621,000 $ 3,425.000 6.9.500 $ $ 5 273,240 2,154,000 266.700 347,750 S 250.000 352.200 $ 97,500 48,500 54,000 $ $ 152,000 $294.000 $ 1.068.000 $ 12,795,000 $ 37,200 20.30 741962 94,800 $ 2.3.120 $ 226,042 4223,070 OGT25'8 $ $ 33.3 28.5 000'COD'S $ 1223,070 9 Comaribuniemand 10 Feense 11 Net Operacing income . 13 Projections for This Year: 14 Layernhales 15 things in niti 16 charysin 17 change in variable expersia 16 inlal 19 000 CS 09 04 04 2 E 4 5 . 7 7 D DE 11 12 13 14 15 Tate % Wild Aware OM pur Unit 4% 54 na's 13% -9.00 302.00 $ COEU 2,000 ' 100% 259.000 11% 16.30 9.013 $ 6:55 $ CONTI 64 9.00 20.00 $ 13959 1605 S 22 4,500 19.00 01 12.65 -0% 15,0cu 95.cu 770115 $ 18% 22,020 139.00 5 27.00 $ 20.30D 52.00 $ 2% 1,020 14.00 2.40 5 25.cu 17.511 5 42 103 5 3,000 39.90 143 2475 3 $ USHI DOES 1,500 A.DOS 79.15 96.00 $ 79.03 11.15 S US $ SUCI 10.1 19.01$ 6.200 49.00 1140 3552 9.09 51.11 27.11 UV $ $ 9 t $ SPO 7109 'BE SUV 2. SDITE 20 21 Salema pe cele 22 Uno 23 Seling print 24 PREMIO PRU- 25 Camini mingin perunit 26 27 Sales 28 Variable 29 combuilom. 30 Fans 31 Net operating corre 32 $ $ 1 261.000 $ 1.6.15 144,450 $ 100.04 1.533,500 $ 1.31075 302.425 5 510.000 2.125.500 $ 255,000 1.667.100 255.000 468.COD 85,500 $ 3,213,000 $ 25,575 2,484,00 55,925 729,000 1.7.2008 $ 42.600 74.100 $ 592.500 5 248.853 245,650 $ 621,000 $2,425.000 $ 279,240 -,154,000 347,750 S 25,000 $ 5.9.500S 206.200 97,500 $ 48,500 54,00 140 29 $ $ $ CORTE 132,0505 294.000 $ 1.068,000 S 12,795,000 37,220 80,00 741963 8,971,930 94,800 $ 2.3,120 $ 226,042 4223,070 3,000,000 $ 1,223,070 ? 23.04 28.5 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: Product 1 2 3 TITT 1456789BnNBH Unit Sales 9,000 16,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 $ 99 $ 85 $109 $ 19 $119 $ 39 $ 79 $ 69 $ 95 $ 59 $ 65 $ 44 $ 49 $ 89 Variable Cost per Unit $12.95 $68.55 $42.50 $85.00 $ 6.35 $92.00 $14.30 $33.18 $30.36 $77.60 $25.40 $29.00 $12.40 $13.48 $61.83 10 11 12 13 15 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 Seek in Excel. Click here for a a brief tutorial on Charts in Excel. Click here for a brief tutorial on Conditional Formatting in Excel. 3. Refer to the original data (in other words, return cell Q15 to its original value of 0%) and assume the sales mix percentages (as shown in rows 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 Q31, by changing the unit sales in cell Q14.) 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 Q14 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 Q16 to input this projection.) a. 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 requirement 3a? b. How is the amount in cell B23 calculated? Why does the contribution marain ratin chown in coll D20 differ from the correcnandina narrontare from last voar ac chown in coll 3. Refer to the original data (in other words, return cell Q15 to its original value of 0%) and assume the sales mix percentages (as shown in rows 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 Q31, 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 Q14 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 Q16 to input this projection.) a. 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 requirement 3a? b. How is the amount in cell B23 calculated? c. Why does the contribution margin ratio shown in cell R29 differ from the corresponding percentage from last year, as shown in cell R9? d. Should the company increase its selling prices by 5% this year? Req 3A Req 3B Req 3C Req 4A1 Req 4A2 Req 4B Req 4C Req 4D 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 Q31, by changing the unit sales in cell Q14.) Unit sales to break even 106,558 units Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D What are the dollar sales required to break even? Dollar sales to break even 9,089,359 Req Req 3B Req 3C Req 4A1 Req 4A2 Req 4B Req 4C Req 4D What was the company's margin of safety last year? Margin of safety last year Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D Using Goal Seek, calculate the total unit sales required to break even. Unit sales to break even units Req 3C Req 4A2 > Req Req 3B Req 30 Req 4A1 Req 4A2 Req 4B Req 4C Req 4D Which of the following statements is true? The answer in Requirement 4a1 is the unit sales to break even in requirement 3a because