Reference no: EM13313874

DescriptionCase methods are used to enable you to apply concepts, use the tools you have mastered, improve your learning on the skills you have attained. Through these case works you will discover for yourself the usefulness of concepts, how to apply them in practice and their benefit to organizational decision-makers.In this case, you will use Excel’s Linear Programing and Excel’s Solver as tools to assist the decision maker in identifying the optimal solution for a business decision in maximizing their profit.Notice you need to produce 2 different and independent models for this scenario, even if the outcomes would be the same.Case Scenario:Business decisions are often subject to constraints or business rules to solve business problems. Excel Linear Programing and Solver can be used for decisions, such as, maximizing profit or reducing costs.ABCD is a Brewing company. They need to find out the ideal mix of ingredients in order to maximize their Profit. The available ingredients and other constraints are known as given in the followings:ABCD has 35 pounds of malt, 50 ounces of Hops, and 40 ounces of yeast (and unlimited water) available for brewing this week. How many gallons of their Light and Premium beers should they produce if:• each gallon of Light is to yield a profit of $30?• each gallon of Premium is to yield a profit of $20?

Knowing that each gallon of Light uses• 1 pound of Malt• 2 ounces of Hops• 1 ounce of Yeast

Also, each gallon of Premium uses• 3 pounds of Malt• 1 ounce of Hops• 4 ounces of Yeast

Case Analysis Criteria:STEP 1: Reach a decision through Excel’s Linear Programing methodSTEP 2: Reach a decision through Excel’s Solver Solution methodSTEP 3: Try to change your constraints to draw some discussion points, adding additional thoughts on your decision. In analyzing your solution, explore what happens when you increase, just slightly, each of the constraint values (one at a time). Recall that the current optimal solution has objective value of what you have found through your solutions for Maximum profit.Hints on Linear Programing part:• We can write this as a linear programming problem. We can let our decision variables be: • X1 gallons of Light beer to produce [Please use x-axis for this variable]• X2 gallons of Premium beer to produce [Please use y-axis for this variable]• We want to make as much money as we can, so our objective is:(We wish to maximize our profit)

 

NOTE: For this analysis, please write an introduction that outlines the case and what you hope to show, and a conclusion that clearly indicates your understanding of the findings of your analysis. Please relate both to course readings.To assist you with your solution, here are the necessary steps to follow:Steps to Solving (by Graphing):1. Label the horizontal axis X1 and the vertical axis X22. Plot the boundary line (the equality form of each inequality) of each constraint3. Note which side of each constraint is feasible4. Identify the feasible region5. Locate the optimal solution and find optimum corner by plotting trial linesFor Solver Solution:1) Load Solver into Excel (if you don’t already have it – first look for Solver under Tools) a) Go to Tools -> Add-Ins… (you may have to scroll down to find this)b) Scroll down and check the box for Solver Add-In and reply yes to all questions2) Load the information into Solver – the target cell, changing cells, and constraints. a) Invoke the solver (Tools -> Solver)b) Set the target cell by clicking on the target cell box or by clicking on cell on your spreadsheet.c) Set the sense of the objective function (max or min or goal seek to a specific value)d) Set the changing cells by clicking on the changing cells box and then highlighting both variables on the spreadsheete) Click on Add to add constraints. This brings up the Add constraint dialogue box. The LHS cells go in the Cell reference box and the RHS cells go in the Constraint box. Rather than having to add three separate constraints, we can add all 3 simultaneously by clicking on the cell reference box and then highlighting cells on the spreadsheet. Then select the inequality symbol on the dialogue box, and add the RHS cells by clicking on the constraint box and then highlighting cells on the spreadsheet. Click on the ok button to finish adding the constraints.f) From the Solver Parameters dialogue box, Click on Options to enter the Options dialogue box andcheck both Assume Linear Model and Assume Non-Negative. Then click okg) Back in the Solver Parameters dialogue box, click Solve and Solver goes to work!h) In the Solver Results dialogue box, click on Keep Solver Solution and then highlight Answer, Sensitivity, and Limits under the Reports heading. Then click OK.