AD680 Global Supply Chains
Problem Set #3: Stochastic Inventory Optimization You have been hired by a vendor who is preparing for an upcoming Expo 2023. 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 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, there will be an inventory holding cost that will apply as soon as the order is placed. An annual holding cost rate of 10% is assumed. The excel sheet summarizes the important information regarding the products under consideration. Given the information presented, create a spreadsheet for the vendor that determines what product (A, B, or C) should be sold at the Expo based 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). Vary the annual holding cost percentage and show how the expected profits change for a range of holding rates from 10% to 40% (vary in increments of 5%). Show these results in tabular format.
The assessment of this work will be based on the accuracy and clarity of your spreadsheet. Submit one Excel file, using a file name Lastname_3.