Spreadsheet Analysis with Excel

Putting the Link in the Supply Chain
Rick Eldridge is the new vice president for operations at The Golfer’s Link (TGL), a company specializing in the production of quality, discount sets of golf clubs. Rick was hired primarily because of his expertise in supply chain management (SCM). SCM is the integrated planning and control of all resources in the logistics process from the acquisition of raw materials to the delivery of finished products to the end user. While SCM seeks to optimize all activities in the supply chain, including transactions between firms, Rick’s first priority is ensuring that all aspects of production and distribution within TGL are
operating optimally.
TGL produces three different lines of golf clubs for men, women, and junior golfers at manufacturing plants in Daytona Beach, FL; Memphis, TN; and Tempe, AZ. The plant in Tempe produces all three lines of clubs, while the one in Daytona only produces Men’s and Women’s lines, and the plant in Memphis only produces the Women’s and Junior’s lines. Each line of clubs requires varying amounts of three raw materials that are sometimes in short supply: titanium, aluminum, and a distinctive rock maple wood that TGL uses in all of its drivers. The manufacturing process for each line of clubs at each plant is identical. Thus, the amount of each of these materials required in each set of the different lines of clubs is summarized in the following table:
Resources Required per Club Set (in lbs)
Men’s Women’s Junior’s
Titanium 2.9 2.7 2.5
Aluminum 4.5 4 5
Rock
Maple 5.4 5 4.8
The estimated amount of each of these key resources available at each plant during the coming month
is given as:
Estimated Resource Availability (in lbs)
Daytona Memphis Tempe
Titanium 4500 8500 14500
Aluminum 6000 12000 19000
Rock
Maple 9500 16000 18000
TGL’s reputation for quality and affordability ensures that the company can sell all the clubs it can make.
The Men’s, Women’s, and Junior’s lines generate wholesale revenues of $225, $195, and $165,
respectively, regardless of where they are produced. Club sets are shipped from the production plants
to distribution centers in Sacramento, CA. Each month, the distribution center orders the number of
club sets in each of the three lines that it would like to receive. TGL’s contract with this distributor

1 Adapted from Cengage Learning case 2013.
requires the company to fill at least 90% (but no more than 100%) of all distributor orders. Rick recently
received the following distributor orders for the coming month:
Number of Club Sets Ordered
Men’s Women’s Junior’s
Sacramento 2150 3100 3500
The cost of shipping a set of clubs to the distribution center from each production facility is summarized in the following table. Note again that Daytona does not produce Junior’s club sets, and Memphis does
not produce Men’s club sets.
Shipping Cost
Men’s Women’s Junior’s
ToFrom Daytona Tempe Daytona Memphis Tempe Memphis Tempe
Sacramento $51.00 $10.00 $49.00 $33.00 $9.00 $31.00 $8.00
Rick has asked you to determine an optimal production and shipping plan for the coming month. He hopes you can provide the answers to the following questions in your next meeting with him.
1. Create a spreadsheet model for this problem and solve it. What is the optimal solution?
2. If Rick wanted to improve this solution, what additional resources would be needed, and where would they be needed? Explain.
3. What would TGL’s optimal profit be if the company was not required to supply at least 90% of each distributor’s order?
4. Suppose TGL’s agreement included the option of paying a $10,000 penalty if the company cannot supply at least 90% of each distributor’s order but instead supply at least 80% of each distributor’s order. Comment on the pros and cons of TGL exercising this option.