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.