You have been called in to consult Tammy Duncan, the operation manager and head beer meister of Black Warrior Brewing Co. which produces the very popular BW Ale. Tammy would like to develop a spreadsheet model to help her determine the best sales price for BW Ale which she currently sells for $45.00 per gallon. BWB can produce 5,400 gallons of ale per week at a production cost of $16.00 per gallon. The primary ingredients of BW Ale are barley and hops. Each gallon of BW Ale requires 1.30 pounds of barley which costs $4.25 per pound. Each gallon also requires 0.50 pounds of hops which costs $8.50 per pound. The fixed cost of operating the brewery is $4,900 per week. Tammy has done extensive market research to estimate the price-demand curve for BW Ale. She has estimated that at a sales price of $38.00 per gallon, BWB would sell 7,700 gallons of BW Ale per week and that a $0.01 increase/decrease in price would decrease/increase sales by 6 gallons per week.
Construct a spreadsheet model in excel to compute the total profit [Hint: If demand corresponding to some price exceeds capacity, only the capacity can be sold. Also if capacity exceeds demand, only the demand can be sold. The spreadsheet model needs to be flexible enough to handle either case.]