Global Supply Chains
Problem Set: Optimization
You have been hired by a vendor who is preparing for an upcomingExpo 2018. The vendor hopes to capitalize on the event's theme of "Future Energy" by selling an innovative solar-powered product. Three products are under consideration (so as not to disclose their products ahead of time, the vendor refers to the three products are Products A, B, and C). The products vary in their sophistication (Product A is the most complex and Product C is the least complex), and each product is made by a different manufacturer (each with a different lead time). Once the Expo ends, products not sold will be sold to a discounter at a heavily reduced price. Financially, differences exist in salvage value, selling price (i.e., revenue per unit), and production cost. The vendor will sell just one of the products (A, B, or C). Because the products all require careful handling, an annual holding cost rate of 30% is assumed. The table below summarizes the important information regarding the products under consideration.
|
|
Product A
|
product b
|
PRODUCT c
|
|
selling price/UNIT
|
$800
|
$700
|
$550
|
|
PRODUCTION cost/UNIT
|
$400
|
$375
|
$300
|
|
lead time (weeks)
|
35
|
20
|
10
|
|
salvage PRICE/UNIT
|
$200
|
$105
|
$55
|
|
DEMAND forecast (UNITS)
|
1200
|
1400
|
1600
|
|
forecast cv
|
30%
|
25%
|
20%
|
Given the information presented, create a spreadsheet for the vendor that determines what product (A, B, or C) should be sold at the Expobased on expected profits, and how many units the vendor should order from the manufacturer.Clearly organize and label the spreadsheet so that the vendor can see inputs and results (i.e., the vendor may wish to modify inputs later as more information becomes available). The assessment of this work will be based on the accuracy and clarity of your spreadsheet.