EXHIBIT 3 A craft brewery is planning its production for the…
EXHIBIT 3 A craft brewery is planning its production for the summer season. It has assessed demand data for the next three months (see table below). With its current equipment, the company can brew between 18,000 and 26,000 gallons beer per month. The brewery has storage space that can hold maximum 10,000 gallons of beer. The cost of storing beer is $1.10 per gallon per month. The inventory is empty at the beginning and end of the planning period. The brewing cost is estimated at $3.20 per gallon. The brewery wants to find the optimal production schedule that minimizes its total cost. The decision variables are given as: B1 = Number of gallons brewed in Month 1 B2 = Number of gallons brewed in Month 2 B3 = Number of gallons brewed in Month 3 S1 = Number of gallons in inventory at end of Month 1 S2 = Number of gallons in inventory at end of Month 2 Demand Month 1 15,000 gallons Demand Month 2 21,000 gallons Demand Month 3 36,000 gallons Minimum brewing per month 18,000 gallons Maximum brewing per month 26,000 gallons Maximum storage capacity per month 10,000 gallons Brewing cost per gallon 3.20 $/gallon Storage cost per gallon 1.10 $/gallon Please solve the problem described in this Exhibit using the Excel Solver. You will find the template for this problem in the file Final Exam_Exhibit 3_DATA.xlsm. Important: When clicking on this link, if the file does not open you will find it in the “download” section of your browser (bottom left of the page) and you can open it from there. If it still does not open, make sure that you are not editing a formula in another Excel file (this sometimes prevents you from opening a new file). Please note that this file is protected against saving. Do not close this file until you submit the exam, otherwise you may loose its content. In case you inadvertently closed the file, please download it again.