ADVANCED SUPPLY CHAIN MANAGEMENT

Use Excel Solver to provide answers to the following problems.

1. A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, per-unit labor usage, monthly demand, and per-unit machine time usage are given in the table below.

   	Mice	Keyboards	Joysticks
   Profit/unit	$8	$11	$9
   Labor usage/unit	.2 hour	.3 hour	.24 hour
   Machine time/unit	0.4 hour	.055 hour	.04 hour
   Monthly Demand	15,000	27,000	11,000

   
   Each month, a total of 13,000 labor hours and 3000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?
2. SC Consulting , a supply chain consulting firm, must decide on the location of its home offices. Its clients are located primarily in the 16 states listed in the table below. There are four (4) potential sites for home offices: Los Angeles, Tulsa, Denver, and Seattle. The annual fixed cost of locating an office in Los Angeles is $165,428; in Tulsa $131,230; in Denver $140,000; and in Seattle $145,000. The expected number of trips that will be taken to each state along with the associated travel costs from each potential site (variable costs) are shown below in the table.
   
   Each consultant is expected to take 25 trips each year.
   a.) If there are no restriction on the number of consultants at a site and the goal is to minimize costs, where should the home offices be located and how many consultants should be assigned to each office? What are the total annual costs for each location in terms of fixed costs and travel costs (variable costs)?
   HINT: Lay out the decision variables for Solver vertically. Assume 25 trips per consultant. Use Roundup function in excel.
3. The Top Speed Bicycle Company manufactures and markets a line of 10-speed bicycles nationwide. The firm has final assembly plants in two cities where labor costs are low, New Orleans and Omaha. Its three major warehouses are located near the large market areas of New York, Chicago, and Los Angeles.
   The sales requirements for next year at the New York warehouse are 10,000 bicycles, at the Chicago warehouse 8,000 bicycles, and at the Los Angeles warehouse 15,000 bicycles. The factory capacity at each location is limited. New Orleans can assemble and ship 20,000 bicycles; the Omaha plant can produce 15,000 bicycles per year. The cost of shipping one bicycle from each factory to each warehouse differs, and these unit shipping costs are as follows:

   	New York	Chicago	Los Angeles
   New Orleans	$2	$3	$5
   Omaha	$3	$1	$4

   
   The company wishes to develop a shipping schedule that will minimize its total transportation costs. What would you advise?
4. Matrix M anufacturing Company is considering where to locate its warehouse in order to service its four stores located in four Ohio cities: Cleveland, Columbus, Cincinnati, and Dayton. Two possible site for the warehouse are being considered. One in Mansfield, Ohio and the other is in Springfield, Ohio. Mileage The distances between the locations can be seen in the figure below which shows the cities with the grid coordinates. The loads between the cities and the warehouses are: Cleveland 15, Columbus 10, Cincinnati 12, and Dayton 4. What is the best location?

Price: $13.28

Solution: The downloadable solution consists of 6 pages, 728 words and 7 charts.
Deliverable: Word Document