MGT $335$Homework$2($Use Excel formulas$)$

$(7+7+6=20$ points$)$

Question $1$: $(7$ points$)$ Inventory Management $($Fixed Period Inventory Model$)$

ABC Company inventories and sells spark plugs. Weekly demand for spark plugs is characterized

by the following probability distribution. Use only the random numbers provided.

Weekly Demand $10203040$

Probability $0\mathrm{.}30\mathrm{.}10\mathrm{.}40\mathrm{.}2$

ABC uses a periodic review policy for stocking the spark plugs. The policy can be described as

follows:

The inventory level is checked on Friday evenings every week. If the inventory level $[$inventory

level is defined as the sum of on$-$hand physical inventory $($end of the week$)$ plus any outstanding

undelivered orders from the supplier$]$ falls below a specified minimum inventory level $($let us call

it $$s$),$ a new order is placed with the vendor. Order size is such that the inventory level is brought

up to a specified maximum inventory level $($let us call it $$S$).$ For example, suppose that our current

on$-$hand physical inventory on Friday $($end of the week$)$ is $10$ units, and the outstanding

undelivered order amount is $25$ units. If the minimum inventory level is specified to be s$=40$ units,

and the maximum inventory level is specified to be S$=100$ units, and therefore $35(25+10)$ is less

than the minimum specified inventory level s$=40,$ we release the order for $100-(10+25)=65$ units.

If the minimum inventory level is specified to be s$=18$ and the maximum inventory level is

specified to be S$=100,$ we don$$t place any additional orders since on$-$hand inventory plus

outstanding order amount exceeds the minimum inventory level $$s$($i$.$e$.,10+25>18).$ Whenever

we place an order with the supplier, the supplier delivers the spark plugs one week later $($lead

time is one week$)-$ i$.$e$.,$ an order placed on Friday evening is delivered one week later $($two

Sundays later$)$ on Monday morning. For simplicity of analysis, assume that all the weekly demand

occurs on Wednesday. Start with a beginning inventory of $25$ units and no outstanding orders.

As an inventory manager, you need to decide what combination of values for $$s$$ and $$S$$ you

should use to reduce inventory costs. Assume constraints for $0$ s $100$ and $0$ S $100$ and

that minimum inventory level $$s$$ is less than or equal to maximum inventory level $$S$($s $$ S$),$ and

both are integer values. You are provided with the following cost information. The cost of placing

an order is $$50.$ This cost is incurred only when an order is placed. Cost of carrying inventory $($cost

of keeping items in the warehouse $$ rent, insurance, working capital, etc.$)$ $$0\mathrm{.}25$ per unit per

week. This cost is based on the inventory on hand on Friday evening. For example, if you have $20$

units in stock on Friday, then the inventory carrying cost for that week is $20*0\mathrm{.}25=$$$5.$ It is possible

that the company may run out of stock due to excess demand. If that happens, the company

loses $$1$ in missed profits for every unit of lost demand. Clearly, the total cost consists of the cost

of orders, the cost of carrying inventory, and the cost of missed profits due to shortages. Use

Monte Carlo simulation in Excel $($using Excel$$s Solver$)$ to perform the analysis. Use initial s$=20$ and

$2$

S$=70$ as starting points before running the solver. Conduct the simulation analysis for $32$ weeks

to draw your conclusions.

Given the above information, what inventory policy would you use from your simulation $($i$.$e$.,$

the optimum values of $$s$$ and $$S$)$ so that the total cost of operating the inventory system is

minimum $($before solver and after solver$)?$ Provide two Excel solution sheets for Q$1($Q$1-$

Inventory Policy and Q$1-$Inventory Solver $[$after applying solver$])$