Supply Chain Design

1
Supply Chain Design

• Merlion Golf Supplies operates three production
  plants in Sarasota, Florida Louisville,
  Kentucky and Ontario, California. The plant in
  Sarasota can produce the high-end professional
  line of golf clubs and the more moderate deluxe
  line. The plant in Louisville can produce the
  deluxe line and basic weekender line, while the
  plant in Ontario can produce all three models.

2
The amount of steel, aluminum, and wood required
to make a set of each line of clubs, the monthly
availability of these resources at each of the
three plants, and the gross profit per set are
given in the following table

Merlion has major distribution centers in
Riverside, California Dallas, Texas and Toledo,
Ohio.
3
The projected monthly demand and the unit
transportation costs for each line between the
manufacturing centers and distribution centers
are given in the following table. Lion must ship
between 90 and 100 of the demand for each line
to each distribution center.
Determine an optimal production/shipping pattern
for Merlion Golf Supplies using Solver in Excel.
4
Step 1. Setup the Profit Contribution Matrix.
5
Step 2. Compute the Profit Contribution, Each
plant, Each product line.
C19A20-C3
C24A25-C8
D29A30-D13
6
Part I. Resource Constraints Ignored.
7
Step 3. Setup the Decision Variable Matrix
This is the First Step in Solving the
Transportation Problem on Excel. The decision
variables (highlighted in yellow) are the
quantities of each product line to be produced in
each plant for each market.
F37SUM(C37,E37) F42SUM(C42E42) F47SUM(D47E47)
C40SUM(C37C39) C45SUM(C42C44) D50SUM(D47D49)
8
Step 4. Setup the Target for Optimization.
9
Step 5. Solver Menu.
In the following Solver Menu, we are maximizing
the total contribution margin by changing all the
decision variables (the cells highlighted in
yellow). The only constraints we are imposing is
that production must not exceed demand. We will
consider resource constraints later.
10
Step 6. Click on the Option tab to set option as
shown below
This is a linear model where the decision
variables are non-negative. The Use Automatic
Scaling tab is checked to minimize rounding
errors.
Click OK, then Solve.
11
The maximum contribution margin is
1,374,000. The production-distribution plan is
shown in the yellow-highlighted cells.
12
Part II. Resource Constraints
13
Step 7. Setup the Resource Needed Matrix
Notice that a column labeled 90 Demand has
been added.
14
Step 8. Add Constraints in Solver Menu
At least 90 of demand must be satisfied.
Resources needed must not exceed resources
available.
15
Step 9. Click Solve. Done!
16
(No Transcript)
17
(No Transcript)