You are in charge of manufacturing for a trail mix production plant. Your facility manufactures four varieties of trail mixes (Not Nutts, Deluxe, Traditional, and Sweet Tooth). You must maximize the profit for your facility by making the optimal number of each type of trail mix. There are four main ingredients used in the mixes: chocolate chips, granola, nuts, and sunflower seeds. You have created a spreadsheet model on the "Trail" worksheet to help you in your decision-making. The table at the top of the "Trail" worksheet details how many units of each ingredient are used in making each type of trail mix and the costs for these ingredients. The demand and prices you can charge for each type of mix are also listed in the spreadsheet model. Finally, the model calculates the ingredients used and total profit for the combination of mixes you plan to make. Use solver to complete the assignment tasks.
Set up solver to determine how many of each variety of trail mix to make to maximize total profit subject to the following constraints:
a. The number of bags of trail mix made (range C10:F10) should not exceed the demand (range C9:F9) for that type of mix.
b. You must make at least 1 million of each type of trail mix.
c. Note that you should not use the comma separator when you create this contraint (use 1000000 instead of 1,000,000)
d. The ingredients consumed in making the mixes (range C22:C25) should not exceed what is available (range D22:D25).
e. There is a national nut shortage and they are expensive. To avoid nuts spoiling, you must use all of the nuts.
f. Make only whole number amounts of mixes.