What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem.

Question 4. (16 points) OPTIMAL PRODUCT MIX

A brewery sells three types of beer: Ipa, Ale and Lager. Producing a gallon of Ipa brings a profit of \$40 and requires 3.5 lbs of hops, 5.5 lbs of barley and 2.5 lbs of yeast. Producing a gallon of Ale brings a profit of \$55 per gallon and requires 5 lbs of hops, 7 lbs of barley and 4 lbs of yeast. Producing a gallon of Lager brings a profit of \$45 per gallon and requires 3.5 lb of hops, 6 lbs of barley and 3 lbs of yeast. The brewery has 650 lbs of hops, 1000 lbs of barley and 500 lbs of yeast available in its inventory every week. You will formulate and solve a linear programming model to help the brewery determine the optimal mix of beer of each type (i.e., gallons of beer of each type to produce in order to maximize the total profit) in a week. Water is also used in producing all beer types, but water is plenty and available at no extra cost.

a)(3 points) What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem.

b)(2 points) What is the objective function? Clearly explain the objective function in English and provide the mathematical formulation.

c)(2 points) What are the constraints of the problem? Clearly explain each constraint in English and provide the mathematical formulation.

d)(1 points) Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions (if any).

e)Prepare a spreadsheet to determine the optimal production mix of using Excel Solver.

i.(2 points) Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image.

ii.(2 points) Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously.

iii.(2 points) Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window.

iv.(2 points) Solve the optimization problem using Excel Solver. What is the optimal solution, i.e. the optimal values of the objective function and the decision variables?

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.