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