4. Use the Solver to obtain computer solutions in an Excel workbook. 5. Submit the Word and Excel workbook(s) separately as attachments. Problem 1 The daily production process at a soda bottling company produces regular orange-flavored (x1), regular lemon-flavored (X2), diet orange-flavored (x3), and diet lemon-flavored (x4) drinks. The company operates 18 hours (1,080 minutes) per day and uses two types of syrups with daily available inventories of 260 and 220 gallons for syrup types 1 and 2, respectively. The demand for the two orange- flavored drinks combined is no more than 250 bottles and the two lemon-flavored drinks have a combined daily demand of at least 300 bottles. The company wishes to choose the production level for each type of drink in order to maximize its total profit. Production Time (Minutes) Syrup 1 (Gallons) Syrup 2 (Gallons) Profit per Unit Regular Orange Drink 1.2 0.30 0.10 $1.20 Regular Lemon Drink 1 0.25 0.25 $1.50 Diet Orange Drink 1.4 0.25 0.20 $1.80 Diet Lemon Drink 2 0.40 0.30 $2.00 a) Write the mathematical formulation of the problem. b) Use the Excel Solver to solve the problem. Generate the answer and sensitivity reports in Excel, and submit your Excel workbook as a separate attachment. Provide the results obtained from the Solver. c) The optimal value of one of the decision variables is zero. What should the minimum profit per bottle be for this decision variable to change to a nonzero value? d) Management is considering adding two hours to the daily production time. Should the two hours be added? What will the contribution be to the optimal profit? e) Which syrup type do you recommend to be increased? Why? f) Suppose that the daily amount of syrup 1 is increased to 280 gallons. How much will this change contribute to the optimal profit? g) Suppose that the daily amount of syrup 2 is increased to 230 gallons. How much will this change contribute to the optimal profit