Homework #2 (Fall 2006): Suzuki's Laundry Detergent Co. Ltd.
This assignment is related to your simulation game. Suzuki's Laundry Detergent Co. Ltd. is the name of
the company which you operate in your simulation game. Your task is to make the optimal transportation
plan for the coming month that minimizes the sum of transportation, production, warehouse handling, and
inventory carrying costs for the whole logistics network. The relevant information (e.g., initial inventory)
is given in the Excel template. Each plant is expected to produce 5,000 units of laundry detergents during
the next month. As in your game, your can ship goods to any warehouse from any plant, and perform
"transshipment", but you cannot ship goods from a plant to another plant, or from a warehouse to a plant.
For this problem, you must consider the production cost, warehouse handling cost, and inventory carrying
cost, in addition to the transportation cost. You must consider the warehouse handling costs because if
goods are shipped from a plant to warehouse 1 by going through warehouse 2, for example, the cost of
materials handling at warehouse 2 must be added to the total cost of this shipment. You must consider
the inventory carrying cost too, because your transportation plan determines how much ending inventory
you will carry at each facility, which in turn determines the inventory carrying cost. The relevant cost
information is shown in the Excel template. For this assignment you must maintain a safety stock of
1,000 units in each warehouse, and 500 units in each manufacturing plant.
Your objective is to minimize the overall logistics cost of your network. Your cost includes all the costs
which you must incur to have all the goods "ready for sale" at warehouses (production, transportation, and
materials handling costs), as well as inventory carrying costs at all the facilities. (You can ignore the
warehouse handling cost that you incur when you ship goods to customers, as this cost is fixed.) Keeping
these points in mind, formulate a network LP model in Excel, and answer the following questions.
Ignoring the production decisions for the time being, solve the LP model by using Excel's solver. Give
recommendation as to how many units should be produced at each plant, and how many should be
shipped in each of the origin-destination pair routes.
Now incorporate the production decisions and re-solve the LP model. This means that you now have to
consider production units at each plant as decision variables (cells H8 to H10). Make sure that the
production at each plant does not exceed the plant capacity (you must put additional constraints).
Do question 2 again by assuming that you are not allowed to do any transshipment (shipment among
warehouses). How does your solution change?
(Ignore question 3) A strong earthquake took place in Plant 2 area. Because of this disaster, Plant 2 is
now closed (no production can be performed). Do question 2 again under this scenario.
Note: You must provide the followings: (1) a sheet of paper showing your answers (recommendations) to
each question, (2) a hard copy of the spreadsheet for each question with correct numbers filled in, (3) a
hard copy of the spreadsheet with cell formulas shown (only one copy is needed), and (4) a sheet showing
your solver specifications. If you do not submit all of these as a package, you will not get full credits.