$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 below $is$ a table $of$ data for doing problem set $3$:

$\begin{array}{cc}A& B\\ C& D\\ ?& \text{P}\text{r}\text{o}\text{d}\text{u}\text{c}\text{t}A\\ \text{P}\text{r}\text{o}\text{d}\text{u}\text{c}\text{t}B& \text{P}\text{r}\text{o}\text{d}\text{u}\text{c}\text{t}C\\ \text{L}\text{e}\text{a}\text{d}\text{T}\text{i}\text{m}\text{e}(Weeks)& 35\\ 20& 10\\ \text{P}\text{r}\text{o}\text{d}\text{u}\text{c}\text{t}\text{C}\text{o}\text{s}\text{t}& \text{\$}400\\ \text{\$}375& \text{\$}300\\ \text{P}\text{r}\text{o}\text{d}\text{u}\text{c}\text{t}\text{S}\text{e}\text{l}\text{l}\text{i}\text{n}\text{g}\text{P}\text{r}\text{i}\text{c}\text{e}& \text{\$}800\\ \text{\$}700& \text{\$}550\\ \text{S}\text{a}\text{l}\text{v}\text{a}\text{g}\text{e}\text{V}\text{a}\text{l}\text{u}\text{e}& \text{\$}200\\ \text{\$}105& \text{\$}55\\ \text{A}\text{n}\text{n}\text{u}\text{a}\text{l}\text{H}\text{o}\text{l}\text{d}\text{i}\text{n}\text{g}\text{R}\text{a}\text{t}\text{e}(\%)& 30\%\\ 30\%& 30\%\\ \text{F}\text{o}\text{r}\text{e}\text{c}\text{a}\text{s}\text{t}CV& 0\mathrm{.}30\\ 0\mathrm{.}25& 0\mathrm{.}20\\ \text{F}\text{o}\text{r}\text{e}\text{c}\text{a}\text{s}\text{t}(Units)& 1200\\ 1400& 1600\end{array}$