Name: _______________________________ QSO 320 Introduction to Management Science through Spreadsheets Linear Programming Applications Week 6 Homework Assignment Instructions Use Excel Solver to find the optimal solution to the linear programming problems given below. Copy and paste the Excel Spreadsheets and the Answer Reports into this document. Taylor11 Problem 2-9 A hospital dietitian prepares breakfast menus every morning for the hospital patients. Part of the dietitian's responsibility is to make sure that minimum daily requirements for vitamins A and B are met. At the same time, the cost of the menus must be kept as low as possible. The main breakfast staples providing vitamins A and B are eggs, bacon, and cereal. The vitamin requirements and vitamin contributions for each staple follow: Vitamin Contributions mg/Eg g mg/Bacon Strip mg/Cereal Cup Minimum Daily Requirements Vitamin A 2 4 1 16 Vitamin B 3 2 1 12 An egg costs $0.04, a bacon strip costs $0.03, and a cup of cereal costs $0.02. The dietitian wants to know how much of each staple to serve per order to meet the minimum daily vitamin requirements while minimizing total cost. Source: Taylor, Bernard. Introduction to Management Science. Ninth Edition. Prentice Hall. 2007. a) Write the Linear Programming Model for the problem below. Variables E = Number of eggs B = Number of bacon strips C = Number of cups of cereal Objective Function Minimize Total Cost Z = Constraints b) Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below. c) Copy and paste the Answer report below. d) Write the Optimal Solution below. Note: The Optimal Solutions should include the optimal values for the variables as well as the objective function. E = Number of eggs = B = Number of bacon strips = C = Number of cups of cereal = Total Cost Z = Taylor11 Problem 2-11 The Pyrotec Company produces three electrical productsclocks, radios, and toasters. These products have the following resource requirements: Cost/Uni Labor t Hours/Unit Clock $7 2 Radio 10 3 Toaste r 5 2 The manufacturer has a daily production budget of $2,000 and a maximum of 660 hours of labor. Maximum daily customer demand is for 200 clocks, 300 radios, and 150 toasters. Clocks sell for $15, radios for $20, and toasters for $12. The company wants to know the optimal product mix that will maximize profit. Source: Taylor, Bernard. Introduction to Management Science. Ninth Edition. Prentice Hall. 2007. a) Write the Linear Programming Model for the problem below. Note: unit profit equals sales price minus cost per unit Variables E = Number of clocks B = Number of radios C = Number of toasters Objective Function Maximize Total Profit Z = Constraints b) Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below. c) Copy and paste the Answer report below. d) Write the Optimal Solution below. Note: The Optimal Solutions should include the optimal values for the variables as well as the objective function. E = Number of clocks B = Number of radios C = Number of toasters Total Profit Z =