Question I

The materials manager for a billiard ball maker must manage the inventory for resin, one of the raw materials used in producing billiard balls. The demand is distributed with a mean of $16$ kilograms per day and a standard deviation of $2$ kilograms per day and the lead$-$time is distributed with a mean of $4$ days and a standard deviation of $1$ day. It is also known that the demand during the lead$-$time is normally distributed. Also, since resin is an important raw material in the manufacture of the billiard balls, the manufacturer would like to maintain a Cycle Service Level of $98\%.$ The manager also knows that it is expensive to carry the resin in inventory based on an annual inventory carrying cost of $20\%.$ Each kilogram of the resin is procured at $$3$ and the order costs for resin are $$50$ per order. Assume that the billiard ball manufacturer operates $365$ days a year.

Build a spreadsheet model to determine the reorder point $($s$),$ the safety stock $($SS$),$ and the order quantity $($Q$)$ for the resin.

$$

$$

Question II

We want to classify stock funds based on their annual expenses as a criterion. Question II sheet of the file Shell$\_$for$-$Take$-$Home$\_$Mid$-$Term Exam.xls $($shell$)$ on Brightspace has the information of $30$ stock funds including the annual expenses on each fund and the return on each fund. Based on the EXCEL spreadsheet shell, sort the funds in descending order of the expenses $$ i$.$e$.$ the fund with the highest expenses will be on top and the fund with the lowest expenses will be at the bottom. Complete column G$.$ In column H$,$ write the appropriate command$/$ formula to compute the cumulative $\%$ sum of the funds $($Hint: use the COUNTA function to do this$).$ In column I, write a nested IF statement to assign a fund grade to each fund based on the following grading scale:

$$

Cumulative $\%$ of Total Expenses

Stock Grade

Up to $80\%$ A

$80\%$ up to $95\%$ B

Above $95\%$ C