$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 $(soas$ 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 $(ProductAis$ the most complex and Product

$Cis$ 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,orC).$ Because the products all require careful handling, there will $bean$

inventory holding cost that will apply $as$ soon $as$ the order $is$ placed. $An$ annual holding cost rate $of30\%is$

assumed. The table below 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,orC)$

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\%to40\%(varyin$ increments $of5\%).$ 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 $Lastnam{e}_{3.}$

Attached $is$ a table $of$ data for problem set $3$: