dolution

Eagle Chocolates produce three types of chocolates: ruby, dark and bittersweet. Three important resources (milk, cacao, and sugar) are used to manufacture each type of chocolate. Other resources are needed for each chocolate type, but those resources are abundant so in this problem we will only focus on milk, cacao, and sugar. The table below gives the amount of each resource required to produce a pack of each chocolate type:

Table-Q4. Amount of resources required to produce each product and profits.

Due to Covid-19, Eagle Chocolates experiences supply chain disruptions; the amount of each resource it can purchase from its suppliers is limited. Eagle Manufacturing may obtain only 5000 lbs. of milk and 3000 lbs. of cacao and 1000 lbs. of sugar, from the suppliers each month. Ruby, Dark and Bittersweet contribute $30, $32, and $34 in profit per package, respectively. The company is interested in determining its production plan for the next month. You will formulate and solve a linear programming model to help the company determine the optimal mix of each product type (i.e., amount of each product type to produce to maximize the total profit) in a month.

NOTE: SHOW ALL YOUR WORK.

1. (3 points) What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem

Decision variables:

1. (2 points) What is the objective function? Clearly explain the objective function in English and provide the mathematical formulation.

2. (2 points) What are the constraints of the problem? Clearly explain each constraint in English and provide the mathematical formulation.

3. (1 points) Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions (if any).

4. Prepare a spreadsheet to determine the optimal production mix of using Excel Solver.

5. (2 points) Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image.

6. (2 points) Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously.

1. (2 points) Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window.

1. (2 points) Solve the optimization problem using Excel Solver. What is the optimal solution, i.e., the optimal values of the objective function and the decision variables?

2.