OOO AutoSave OFF Home Insert Draw Page Layout Formulas Assignment Tasks Candy Bar Solver Set up solver to determine how many of each type of candy bar to make to maximize total profit subject to the following constraints: 1 Instruction Task Sheet Guide Submit View Report 1 - The number of candy bars made should not exceed the demand for that type of candy bar. 2 - You must make at least 1 million of each type of candy bar. 3 - The ingredients consumed in making the candy bars should not exceed what is available. 4 - There is a national nut shortage and they are expensive. To avoid nuts spoiling, you must use all of the nuts. 5 - Make only whole number amounts of candy bars. [82 points] F H J A1 1 x fx A B D E H L M N 1 Ingredients Chocolate Nougat Nuts Caramel Milky Way 1 1 1 Candy Bars Snickers Nut Roll 1 0 1 1 1 1 1 1 3 Musketeers 1 1 Cost $0.15 $0.15 $0.20 $0.15 0 0 1 0 You are in charge of manufacturing for a candy bar plant. Your facility manufactures four types of candy bars (Milky Ways, Snickers, Nut Rolls, and 3 Musketeers). You must maximize the profit for your facility by making the right number of each type of candy bar. There are four main ingredients used in the candy bars: chocolate, nougat, nuts, and caramel. You have created a spreadsheet model on the "Candy" worksheet to help you in your decision-making. The table at the top of the "Candy" worksheet details how many units of each ingredient are used in making each type of candy bar and the costs for these ingredients. The demand and prices you can charge for each type of candy bar are also listed in the spreadsheet model. Finally, the model calculates the ingredient used and total profit for the candy bars you plan to make. Use solver to complete the assignment tasks. Demand Total to Make 2,000,000 2,500,000 1,000,000 1,000,000 1,000,000 1,000,000 1.500,000 1,000,000 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 en 20 21 21 22 23 3 24 25 Price/Bar Cost/Bar Gross Profit/Bar $0.90 $0.45 $0.45 $0.90 $0.65 $0.25 $1.05 $0.50 $0.55 $0.90 $0.30 $0.60 $2,700,000 Given the problem constraints which number in the drop-down list in cell N14 is closest to the maximum profit possible for this problem? Gross Profit/Type $450,000 $250,000 $550,000 $ $600,000 Total Gross Profit $1,850,000 Snickers Production would fall short of demand for which of the candy bars (select your answer from the drop-down list in cell 18)? Yes Material Totals Ingredients Used Available Chocolate 3,000,000 6,000,000 Nougat 4,000,000 7,000,000 Nuts 2,000,000 2,500,000 Caramel 3,000,000 5,500,000 If you could buy 1,000,000 more units of nuts for $100,000 (in addition to the variable cost of nuts already built into the model), should you (select your answer from the drop-down list in cell N21)