Formulate the model and solve it using Solver.

NutriFood is a catering company that serves lunch to students in a local middle school. NutriFood

wants to offer a nutritional, well$-$balanced diet for students. It identified and analyzed $4$ different

food groups $(1,2,3$ and $4)$ and determined the contributions a serving from each food group makes

to $4$ different nutritional categories $($A$,$ B$,$ C and D$),$ shown in the following table. The last two

columns of the table show the minimum and maximum nutrients required daily for each student.

Maximum values of $9999$ indicate that no maximum value applies to that particular nutritional

requirement.

NutriFood uses the above information to design a diet $($servings of each food group$)$ that meets the

indicated nutritional requirements. The last two rows of the table list the cost and the preference

rating per serving of each food group, which represent two different objectives that can be pursued

in creating a diet. First, we may want to identify the diet that meets the nutritional requirements at

a minimum cost. Although such a diet might be very economical, it might also be very

unsatisfactory to the tastes of the students. Another objective that could be pursued would be that

of determining the diet that meets the nutritional requirements and produces the highest total

preference rating. However, this solution is likely to be quite expensive.

Questions:

a$)$ Formulate an MOLP model for this problem, and implement the model in a spreadsheet

"Prob$3$a$".$ Determine the best possible value for each objective in the problem. Record both

solutions. What is the lowest possible cost and the corresponding preference rating? What is

the highest possible preference rating and the corresponding cost?

b$)$ Determine the solution that minimizes the maximum weighted percentage deviation from the

optimal objective function values. Suppose NutriFood considers minimizing the cost twice as

important as maximizing the preference rating. Write the LP formulation. Solve it using Solver

in sheet "Prob$3$b$".$ Describe your solution including the actual percentage deviation for each

objective.

Show step by step in Excel Solver. Thank you