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 optimal 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 ingredients used and total profit for the candy bars you plan to make. Use solver to complete the assignment tasks.
B D E G J K M N O 1 2 3 Candy Bars Milky Way Snickers Nut Roll 3 Musketeers 1 1 0 1 1 1 1 1 4 Ingredients Chocolate Nougat Nuts Caramel 5 Cost $0.15 $0.15 $0.20 $0.15 6 0 1 1 0 7 1 1 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. 8 9 Demand Total to Make 2,000,000 2,500,000 1,000,000 2,000,000 1,500,000 1,000,000 1,500,000 1,500,000 10 11 12 13 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 14 15 16 Gross Profit/Type $900,000 $375,000 $550,000 $900,000 Please install the Solver Add-On into your Excel environment. To please go to File/Options. Then click on "Add-Ins" on the pop-up window side bar. At he bottom, please click on the "Go" Button next to the selection box that should show "Excel Add-Ins". In the pop-up window that appears, please check "Solver Add-in" and click ok. You can now accesss the Solver on the Tab in the Analyze Group. 17 Total Gross Profit $2,725,000 18 19 20 21 22 Material Totals Ingredients Avai Chocolate 5,000,000 6,000,000 Nougat 6,000,000 7,000,000 Nuts 2,500,000 2,500,000 Caramel 4,500,000 5,500,000 Run the solver and set the objective to maximize Total Gross Profit. Specifit that you want the solver to change the Total to Make for all four Candy Bars. Add all demand constraints for the four Candy Bars. Add all Material constraints for all four Ingredients. Then run the solver. You will see the results in the spreadsheet on the left. 23 24 25 26 Please go back to WebCampus (Canvas) and enter the answers to the questions that are part of this assignment. 27 28 B D E G J K M N O 1 2 3 Candy Bars Milky Way Snickers Nut Roll 3 Musketeers 1 1 0 1 1 1 1 1 4 Ingredients Chocolate Nougat Nuts Caramel 5 Cost $0.15 $0.15 $0.20 $0.15 6 0 1 1 0 7 1 1 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. 8 9 Demand Total to Make 2,000,000 2,500,000 1,000,000 2,000,000 1,500,000 1,000,000 1,500,000 1,500,000 10 11 12 13 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 14 15 16 Gross Profit/Type $900,000 $375,000 $550,000 $900,000 Please install the Solver Add-On into your Excel environment. To please go to File/Options. Then click on "Add-Ins" on the pop-up window side bar. At he bottom, please click on the "Go" Button next to the selection box that should show "Excel Add-Ins". In the pop-up window that appears, please check "Solver Add-in" and click ok. You can now accesss the Solver on the Tab in the Analyze Group. 17 Total Gross Profit $2,725,000 18 19 20 21 22 Material Totals Ingredients Avai Chocolate 5,000,000 6,000,000 Nougat 6,000,000 7,000,000 Nuts 2,500,000 2,500,000 Caramel 4,500,000 5,500,000 Run the solver and set the objective to maximize Total Gross Profit. Specifit that you want the solver to change the Total to Make for all four Candy Bars. Add all demand constraints for the four Candy Bars. Add all Material constraints for all four Ingredients. Then run the solver. You will see the results in the spreadsheet on the left. 23 24 25 26 Please go back to WebCampus (Canvas) and enter the answers to the questions that are part of this assignment. 27 28
