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 4 5 6 7 B 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 Sening Price per Unit $29 599 $ 85 $109 $19 $119 $ 39 $ 79 $ 69 $95 $ 59 $ 65 $44 $ 49 $ 89 Variable Cost per Unit $12.95 568.55 $42.5@ $85.00 $ 6.35 $92.00 514.30 $35.11 $10:36 $77.60 $25.40 $29.00 $12.40 $13.48 $61.83 10 11 12 13 14 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 fona battutorial on Goal Seek In Excel Click here for a brief tutorial on Charts In Excel 2. Regarding projections for this year assume that total unit sales increase by 10%, and everything else holds constant. (Hint: The template contains dynamic formulas, therefore you will only need to modify the input in cell 015 to complete requirements 2a through 2e.) a. What would be the projected net operating Income for this year? b. How are the projected unit sales in cells 822 and Q22 calculated? c. Why are the percentages in cells R28 and 29 the same as the corresponding percentages in cells R8 and R9 even though the projected sales of 165,000 units is 15,000 units greater than last year's sales of 150,0000 units? d. What is the percent increase in total contribution margin (as shown in cell Q29) compared to last year's total contribution margin (as shown in cell Q9)? What is the percent increase in net operating Income (as shown in cell 031) compared to last year's net operating income as shown in cell Q11? Is your answer greater than, less than or equal to your answer in requirement 20? Complete this question by entering your answers in the tabs below. Reg 2A Reg 26 Reg 20 Reg 2D Reg 21 Reg 22 What would be the projected net operating income for this year? Projected net operating income Reg 2 Reg 28 > Complete this question by entering your answers in the tabs below. Reg 2A Req 2B Reg 2C Req 2D Reg 21 Req 2E2 How are the projected unit sales in cells B22 and Q22 calculated? 2b-1. How are the projected unit sales in cell B22 calculated? Total unit sales (cell Q22) - Product 1's sales mix percentage (cell B21) Total unit sales (cell Q22) + Product 1's sales mix percentage (cell B21) Total unit sales (cell Q22) Product 1's sales mix percentage (cell B21) Total unit sales (cell Q22) + Product 1's sales mix percentage (cell B21) 2b-2. How are the projected unit sales in cell Q22 calculated? Last year's unit sales - (Last year's unit sales 17 projected sales of 165.000 units is 15,000 units greater than last years sales of 150,000 units d. What is the percent increase in total contribution margin (as shown in cell Q29) compared to last year's total contribution ma (as shown in cell Q9)? e. What is the percent increase in net operating income (as shown in cell 031) compared to last year's net operating income tas shown in cell Q11)? Is your answer greater than, less than or equal to your answer in requirement 2d? Complete this question by entering your ans in the tabs below. Reg 2A Reg 28 Req 2c Req 2D Reg 21 Req 2E2 Why are the percentages in cells R28 and R29 the same as the corresponding percentages in cells R8 and R9 even though the projected sales of 165,000 units is 15,000 units greater than last year's sales of 150,0000 units? Because the sales mix percentages have not changed, but each product's seling price per unit and variable expense per unit have changed Because the sales mix percentages have not changed and each product's soling price per unit and variable expenso per unit have also not changed Complete this question by entering your answers in the tabs below. Req 2A Reg 28 Reg 20 Reg 2D Reg 21 Reg 2E2 What is the percent increase in total contribution margin (as shown in cell Q29) compared to last year's total contribution margin (as shown in cell 09)? D Porcent increase in total contnbution margin % Complete this question by entering your answers in the tabs below. Req 2A Reg 2B Req 20 Reg 2D Reg 21 Req 2E2 What is the percent increase in net operating income (as shown in cell 031) compared to last year's net operating income (as shown in cell Q11)? (Round percentage answer to 1 decimal place.) Percent increase in not operating income % Complete this question by entering your answers in the tabs below. Req 2A Req 2B Reg 2c Reg 2D Reg 21 Reg 2E2 Which of the following statements is true? The percent increase in net operating income is the percent increase in contribution margin because 20 15.000 DOS 15 9.00 00 500 SO 9000 DOS 12.95 165 S HO DO 00 JOOS 500S 45 100 15 27.000 119.00 9.00 31.00 ODE 5 O SO 100 BE 26 0056 SOY 05 23.00 2.00 5 IS 25 5 YOS 11S 5W 12.45 13 Variable 2 Contro per 5 Requirement kere e C Share is I Coor Dew Post Format Data Vip A BOA SED Son $ -% 9 Comfort Och Set ht Sales 1 N M H O 10 IL 5 12 24 Weighted Average CM per un 7 23 15 TO Last Year: Variable expert 5 2005 5111 S. 05 23.00 120 5 VEE 11.7.2005 SOOS $19.500 5 05 10.000 200 DON 5 000 $ 24000 729005 Sales va Corsi Fees Nagcom 1200 STANOS 2724 LO 2005 2015 5 2010 60 5 2 BOSS 168.000 2.000 2010 25 71110525 020 100 DOO S1222300 sor 0 Projections for This Year: year Chante Chic Changes Chanesinde 505 . > T? 30 * 11 51 13 Weight 14 To NE Sale 5 EN SOS SON 14.000 15.00 30.00 58.00 1 2.000 119.00 2005 2005 1.000 00 Seine er | ia Variable Catari BOD 13.00 5 3.000 5 145 2005 200 05 15 25 3.300 00 20 3005 SO SKO 1000 44005 17/05 3100 5 5 705 17405 SO OTE ESSE 22:11 145 1000 117.000 300 SOOS 2.54000 $22.000 90.00 24 3210 5.07 OOST 7 sales Variable Cotton Galat 1000 540.000 1164.000 30.05 1.000 5 2723 $19.500 5 NO NO 132,000 5 1,000 000 5 13,75.000 870 9400521 00 21 O S QOL * of 3:58 PM W10/2001 1. 13 14 13 CM per Unit 266,700 352,800 $ 43,500 54,000 $ 37,200 94,800 $ 80,880 213,120 $ 741,960 326,040 67.0% 33.0% 28.15 8,571,930 4,223,070 3,000,000 $ 1,223,070 + 150,000 0% 096 0% 0% 11 12 13 14 15 Total % Weighted-Average CM per Unit 100% 150,000 7% 10,500 59.00 $ 25.40 $ 33.60 $ 1% 1,500 65.00 $ 29.00 $ 36.00 $ 2% 3,000 44.00 $ 12.40 $ 31.60 $ 4% 6,000 49.00 $ 13.48 $ 35.52 S 8% 12,000 89.00 61.83 27.17 619,500 $ 266,700 352,800 $ 97,500 $ 43,500 54,000 $ 132,000 $ 37,200 94,800 $ 100.0% 67.0% 294,000 $ 1,068,000 $ 12,795,000 80,880 741,960 8,571,930 213,120 $ 326,040 4,223,070 3,000,000 $ 1,223,070 33.0% 28.15 Requirement 4 ***