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.

Nutrients Food Daily Limits

$1234$ Lower Upper

A $1\mathrm{.}416\mathrm{.}23\mathrm{.}46\mathrm{.}46265$

B $5\mathrm{.}947\mathrm{.}720\mathrm{.}361\mathrm{.}11459999$

C $1080651409999$

D $117\mathrm{.}914\mathrm{.}842\mathrm{.}10225$

Cost per serving $$1\mathrm{.}01$ $$0\mathrm{.}97$ $$0\mathrm{.}56$ $$1\mathrm{.}28$

Preference rating $1841758$

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.