Assignment -
In order to maintain quality and reduce production and inventory costs, the owner, Taylor, has built Wonder-Grow Soil production/packaging facilities in Kearney, Seattle, and Boston. Each facility is able to package a variety of soil mixtures for different demands and needs. All products are shipped in 30 pound containers whether it is garden soil, potting mix soil, organic rich soil, or Wonder-for-All planting soil.
The soil containers are shipped from the production/packaging facilities to regional distribution centers. The regional distribution centers are able to supply local garden centers with newly packaged soils in small batch sizes, thus, reducing the costs of the local garden centers and improving variability in the system. There are four regional distribution facilities: Louisville (KY), Fairfax(VA), Houston(TX), and San Francisco(CA). The cost table below shows the price of shipping a single 30 pound container from each production/packaging facility to each of the regional distribution facilities.
|
|
Shipping Costs per 30 pound bag
|
|
From / To
|
Louisville
|
Fairfax
|
Houston
|
San Francisco
|
|
Kearney
|
9
|
8
|
6
|
5
|
|
Seattle
|
9
|
10
|
7
|
4
|
|
Boston
|
5
|
3
|
4
|
10
|
Each of the Green Acre Getaway Gardens production/packaging facilities has a different capacity to produce Wonder-Grow Soil. The Kearney location can supply 650 containers per month, the Seattle location can supply 400 containers per month, and the Boston location can supply 630 containers per month. The research analyst at the Green Acre Getaway Gardens corporate office estimated that next month Louisville needs 500 containers, Fairfax needs 600 containers, Houston needs 280 containers, and San Francisco needs 300 containers.
Use the Excel Solver to determine the number of Wonder-Grow containers each Green Acre Getaway Gardens production/packaging facility should send to each distributional facility to minimize shipping costs. You must choose the Simplex method for the correct solution.
Facility Location Problem
The Green Acre Getaway Gardens Board of Directors is considering adding a new distribution center to improve the shipping costs. The Houston distribution center is currently supplying stores in the upper midwest. The CEO, Taylor Duncan, has suggested that the 'centroid method' should be used to find the optimal location for the new Green Acre Getaway Gardens distribution center. Information regarding the current stores and the local demand is given on the Centroid Method spreadsheet.
Transportation Method - Linear Programming
1. On the Transport worksheet enter the proper supplies, demands, and transportations costs in the Costs table.
2. Use the =Sum() function to determine the actual amount supplied and received.
3. Use the Solver method to determine the least cost solution to the transportation problem.
4. Use Sumproduct() in determining the Total Cost (objective function)
Facility Location -
1. On the Centroid Method Sheet, use the centroid method to find the location for a new distribution center.
2. Using GOOGLE.COM, determine for each city the population, latitude, and longitude for each store. Carry all work to 4 decimal places. Do not use other search engines for this task as they will return different results. Use population as a proxy for store sales. This assumes larger communities have more sales than smaller communities. This can occur when large cities have multiple Green Acre Getaway Gardens locations.
3. Use MapQuest or a similar program to locate the closest city (with a population over 7,000).
Attachment:- Assignment Files.rar