View the step-by-step solution to:

Question

Linear Programming in Excel using the Solver function

Can someone help me with this problem?  I am looking

for the LP model in Excel.

Scenario:  The Anheuser Busch Brewery has long provided two types of low point (3.2%) beer to the five states that require its production (Oklahoma, Utah, Colorado, Kansas and Minnesota).

It produces this beer in 6 different brewery locations: Columbus, OH, Van Nuys, CA, Fairfield, CA, Fort Collins, CO., Houston TX.,  and St. Louis, MO.

ABB wishes to revisit its monthly distribution of two types of beer (Regular and Lime) from the breweries to the states under the assumption that Oklahoma will no longer need low point beer (assuming various laws are passed, and 3.2% beer becomes a thing of the past).

The following table provides most of the relevant information to help design the monthly flow of cases of beer from the breweries to the four states still requiring low point beer in this scenario.

Included in the table is the capacity available for production of low point beer at the six breweries (regardless of whether regular or lime), the demand per state for each type of beer, the shipping cost per case (regardless of type, and this is approximated) from brewery to state, the production cost (per case) of each type of beer at each brewery, and the quality of beer produced at each brewery (regardless of type).

Create a LP model that determines the least cost way of getting beer from where it is produced (breweries) to where it is demanded (states).  Your (one) model must differentiate between type of beer. 

Requirements for the distribution of beer:

  • All capacity at the breweries must be used in the production of beer. The capacity can be used in any way (any combination of regular and lime beer).
  • Demand (by type) must be met at each state. Since there is greater production capacity than demand, extra beer will be sent. For each beer type in each state, there is a 10% extra limit – no more than 110% of the demand for that beer type can be sent to that state.
  • The total cost is found by the distribution cost of the beer (cases x cost in table) added to the production cost of the beer (cases x cost also shown in the table). Note the distribution cost is the same regardless of beer type, while the production cost of regular vs. lime is different at each brewery.
  • There is a legal limit on how much beer can be sent into each state by each brewery.  This legal limit is based upon a variety of factors, and is found by 1.0* Reg cases of beer + 1.1*Lime cases of beer must be less than or equal to 1200.  That limit must be implemented for each combination of brewery and state.
  • Finally, each brewery produces beer with different quality.  That is shown in the table as well. The weighted quality average of regular beer at each state should be at least 8.9, whereas the weighted quality average of lime beer at each state should be at least 8.8.

Additionally, it makes sense for only whole number of cases of beer to be shipped. Do this requirement last

Background image of page 1
Sign up to view the entire interaction

Top Answer

View the full answer
solution72.xlsx

shipping
cost
per unit REG
LIME
productionproduction
cost
cost
Quality per unit per unit
8.8
1
1.1
OH
9.2
1.2
1.21 VNCA
9.3
1.15
1.21 FCA
8.2
1.4
1.5
FCCO
8.5
1.3
1.35 TX
9
0.95
1
STL UTAH
18
6.2...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online