Question 9. Formulate the problem in a textbox alongside the spreadsheet model. Using Excel Solver, solve for the optimal solution and optimal value and place your answers in a separate textbox.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Question 8. Create an Excel spreadsheet model and use Solver to determine the optimal solution and optimal value. Include the LP formulation (with labels) in a textbox. Create a separate textbox and indicate what the optimal solution and optimal value are.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Question 2. The formulation for this LP is given in the problem description. Create a spreadsheet model of this LP using the formulation provided and solve for the optimal solution and optimal value. Include the LP formulation (with labels) in a textbox. Create a separate textbox and indicate what the optimal solution and optimal value are. Also, generate an Answer Report.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

9. Jessica Kirst is the new owner of Coffee Country Roasters. They roast and blend coffee to sell on the internet. Their best sellers are "Best Blend" and "Economy Blend" coffee. Both are blended from three basic grades of coffee: Best blend: 50% grade A,20% grade B, and 30% grade C Economy: 20% grade A,50% grade B, and 30% grade C The firm is given the option of buying up to 100 tons of grade A, 100 tons of grade B, and 75 tons of grade C. The profit on the Best Blend is $150 per ton and the profit on the Economy Blend is $120 per ton. Jessica would like to maximize her profit. a. Formulate this problem as a linear program. David has learned that he can have no more than 2,000 calories per day to avoid repeating the "freshman fifteen" weight gain from each of the last 3 years. David would like to keep the following nutritional content: - less than or equal to 55 grams of fat per day - at least 300 grams of carbohydrates - at least 45 grams of protein David plans to consume at least 4 beers at Harry's and requires a food consumption plan to allow for that. David has gotten used to consuming either 3 coffees or 4 diet sodas per day in order to stay awake in Accounting class. He also has learned from Accounting class that it is critical to minimize his expenses in order to fulfill this routine every Friday. Decision variables: B= number of beers consumed per Friday P= number of slices of pizza consumed per Friday C= number of cups of coffee consumed per Friday D= number of cans of diet soda consumed per Friday S= number of sandwiches consumed per Friday Design a spreadsheet model for this problem with well-defined Title, Data Table, Decision Variables, Constraints, and Objective Function. The LP model is given as follows: 2. David Holloway, a senior in management, has devised a way to get through Fridays just eating the 5 basic food groups: beer, pizza, coffee, diet soda, and sandwiches. Consider the following information regarding his 5 basic food groups: MIN1.75B+1.50P+0.95C+0.60D+3.25Ss.t.84.5B+338P+67C+4D+245S3007.5P+15S>451B410C+7.5D>30B,P,C,D,S0(Calories)(Fat)(Carbohydrates)(Protein)(BeerConsumption)(Caffeine)(Non-negativity) Turn in printouts showing the formulas and values in each cell. 3. Julie North is starting her new business, Queen of the North Vehicle Rental specializing in winter fun machines for use on the snow and ice of northern Minnesota. She has a budget of $1,000,000. She plans to buy snowmobiles, tankchairs, and Track and Go trucks to satisfy client's demands. The estimated costs are as follows: Price of a snowmobile: $12,000 Price of a tankchair: $22,000 Price of a Track and Go truck: $45,000 She expects to earn $2,000 profit from a snowmobile, $3,600 from a tankchair and $4,500 from a Track and Go truck per month during the winter season. She wants to buy at least two vehicles of each type. In addition, because tankchairs and Track and Go trucks can be used year round, snowmobiles should comprise at most 1/3 of her fleet. Formulate a linear program model to help Queen of the North maximize their monthly winter profit. (Define decision variables and write the objective function and constraints.)