Question

Case Study – Golf Clubs Supply Chain

A golf club production company wants to ensure that all aspects of its production and distribution processes are operating at optimal efficiency. The company produces three types of clubs, a line for men, a line for women, and a line for juniors. The company has manufacturing plants in Charlotte, Phoenix, and Dallas. The plant in Dallas produces all three types of clubs, while the one in Charlotte produces only Men’s and Women’s lines and the one in Phoenix produces only Women’s and Junior’s lines. Each club requires varying amounts of raw materials, including titanium, aluminum, and wood. The process at each plant is the same, but the amount of materials in the various lines differs. Specifically:

	Men’s	Women’s	Junior’s
Titanium	3.1	2.9	2.7
Aluminum	4.6	4.1	5.1
Wood	5.5	5	4.5

These resources are limited and the estimated amount available at each plant is as follows:

	Charlotte	Phoenix	Dallas
Titanium	4600	8400	14000
Aluminum	7000	11000	18000
Wood	9600	15000	17000

The company is confident it can sell all of the clubs it can produce. Revenue from each type of club is the same, regardless of where the clubs are produced:

	Revenue per set
Men’s	$250
Women’s	$220
Junior’s	$180

Once produced, clubs are shipped to distribution centers in Denver, Indianapolis and Newark. Each month, the distribution centers order the number of clubs of each type they would like to receive. The company’s policy is to ship at least 80% of the distribution center’s demand to it each month. Orders for each type of club for next month are:

	Men’s	Women’s	Junior’s
Denver	600	800	800
Indianapolis	500	900	1400
Newark	800	1100	1100

The cost of shipping one unit of each type of club from each production plant to each distribution center is as follows:

	Men’s			Women’s			Junior’s
	Charlotte	Phoenix	Dallas	Charlotte	Phoenix	Dallas	Charlotte	Phoenix	Dallas
Denver	$41	n/a	$10	$39	$23	$9	n/a	$21	$8
Indianapolis	$18	n/a	$33	$17	$12	$32	n/a	$11	$30
Newark	$26	n/a	$46	$24	$13	$23	n/a	$12	$42

Questions:

Create a spreadsheet model and solve it to determine an optimal production and shipping plan for the coming month, where your objective is to maximize profit.

If the company wanted to improve this solution, what additional resources would be needed and where would they be needed? Explain.

Answer 1

Spreadsheet model and solution using Solver is following

Formulas:

M3 =B17*$B3+E17*$C3+H17*$D3   copy to M3:O5

K17 =SUM(B17:D17) copy to K17:K19

L17 =SUM(E17:G17) copy to L17:L19

M17 =SUM(H17:J17) copy to M17:M19

K20 =SUM(K17:K19) copy to K20:M20

B22 =SUMPRODUCT(K20:M20,K22:M22)-SUMPRODUCT(B17:J19,B10:J12)