One of the main products that the Broadway Cafe sells are whole coffee beans.

One of the main products that the Broadway Cafe sells are whole coffee beans. The beans are shipped from Guatemala to three distribution centers in the United States (Denver, Colorado; Santa Fe, New Mexico; and Fort Worth, Texas) and are then shipped by truck to five roasting plants before sent to the cafe. The roasting plants are located in Salt Lake City, Utah; Chicago, Illinois; Albuquerque, New Mexico; Jackson Hole, Wyoming; and Phoenix, Arizona. Since shipping costs are a major expense, you have begun an analysis to determine ways to reduce them. You have created an estimate of what each distribution center needs to order and how much each roasting plant will require to satisfy your orders.

A workbook, SCM_AYK.xls, of the shipping costs from each distribution center to each roasting plant as a baseline analysis has been created for you. Some business rules and requirements you should be aware of include:

• The problem presented involves the shipment of goods from three distribution centers to five roasting plants.
• Goods can be shipped from any distribution center to any roasting plant, but it costs more to ship goods over long distances than over short distances.

Your goal is to minimize the costs of shipping goods from distribution centers to roasting plants, thereby meeting the demand from each metropolitan area while not exceeding the supply available from each distribution center. It is recommended that you use the Solver function in Excel to assist with the analysis.

Specifically you want to:

• Minimize the total shipping costs.
• Total shipped must be less than or equal to supply at a distribution centers.
• Total shipped to roasting plants must be greater than or equal to the roasting plants demand.
• Number to ship must be greater than or equal to 0.

I don't have any experience using Excel. I have watched tutorials about the solver function but get confused when trying to apply it to this question. I have spent hours trying to figure this out. Any assistance would be greatly appreciated.

• Attachment 1
• Attachment 2