Problem $2(30pts)$

You're an intern at a cereal bar manufacturing company that produces three types of

bars: bar $1,$ bar $2,$ and bar $3.$ Each cereal bar is composed of varying proportions of four

ingredients: honey, peanuts, chocolate, and pistachios. Bar $1$ is a mix in weight of $88\%$

honey, $9\%$ peanuts, and $3\%$ pistachios. Bar $2$ is a mix in weight of $92\%$ honey, $3\%$

chocolate, and $5\%$ pistachios. Bar $3$ is a mix in weight of $87\%$ honey, $4\%$ peanuts, $6\%$

chocolate, and $3\%$ pistachios. The manufacturing company sells bar $1$ for $$2$ apiece,

bar $2$ for $$2\mathrm{.}50$ apiece, and bar $3$ for $$3\mathrm{.}00$ apiece. Each bar weighs about the same at

$0\mathrm{.}01$ kg $.$ The maximum daily production is $200$ bars $1,350$ bars $2,$ and $250$ bars $3.$ The

suppliers can provide up to $5$ kg of honey, $2$ kg of peanuts, $1$ kg of chocolate, and $1$ kg of

pistachios each day, at a cost of $$100$ per kg of honey, $$120$ per kg of peanuts, $$110$ per

kg of chocolate, and $$200$ per kg of pistachios.

a$)$ Based on the complete LP given for this problem in Hw$1-$ Solutions, solve it using

Excel Solver. Be sure to make the appropriate change to reflect that the maximum daily

production for bars $2$ is now $350$ and not $300.$ Provide a screenshot of the solved LP$,$ as

well as of the Excel Solver window $($pre$-$solved$).(10$ pts $)$

b$)$ In the optimal solution found in part a$,$ it says not to produce any bar $1$s$.$ Without re$-$

solving the LP$,$ what needs to be bar $1\text{'}$s profit contribution in order for it to be worthwhile

to start producing bar $1?$ Explain and show work $($including screenshots of any reports

used$).(10$ pts$)$

c$)$ Without re$-$solving the LP$,$ only based on the solution found in part a$,$ what's the

minimum amount of chocolate the supplier can provide to the manufacturing company

that will not change the current optimal production solution? Explain and show work

$($including screenshots of any reports used$).(10$ pts$)$