Prysmian Cables and Systems sells cables to the auto manufacturer Oyak

$-$

Renault. Prysmian contracts it

$$

s AS

$-$

$450$

$$cable manufacturing to its supplier TZK

$.$

$$In this question, we will simulate Prysmian

$$

s AS

$-$

$450$

$$inventory and decide when

$($

reorder point

$)$

$$and how much to order

$($

order quantity

$)$

$.$

$$Here is some relevant information:

$$

Prysmian can place an order for AS

$-$

$450$

$$up to once a day. Once Prysmian places an order, it takes some time for the supplier

$($

TZK

$)$

$$to deliver the requested cables to Prysmian; this time between the order and delivery is called the lead time. The lead time for the supplier is given in the

accompanying spreadsheet

Download accompanying spreadsheet.

$$

The daily demand distribution for the AS

$-$

$450$

$$cable is also given in the accompanying spreadsheet.

$$

Prysmian sells each cable for a per unit price of $

$45$

$$and incurs

$3$

$$types of costs: inventory, ordering, and opportunity costs. All these costs are based on per unit or per order and are applied in the following cases:

oInventory costs are applied to each unit of product held in inventory for each day and are calculated based on the beginning inventory.

oEvery time Prysmian requests an order, it incurs an ordering cost.

oFinally, a per unit opportunity cost applies when Prysmian cannot satisfy the demand fully.

$$

Prysmian uses a reorder point inventory policy where the firm orders the same order quantity each time the inventory position reaches a certain point, i

$.$

e

$.$

$,$

$$the reorder point. Inventory position is the amount on hand which includes the cables that were ordered previously and are in transit. Inventory position for a day can be calculated as the inventory position from the previous day minus the demand satisfied that day plus the amount that is ordered the previous day.

Prysmian would like to know how much

$($

order quantity

$)$

$$and when they should order

$($

reorder point

$)$

$$so that it can satisfy the demand.

$1.$

$$Without changing the order quantity and reorder point on the spreadsheet, create a

$30$

$-$

day simulation and report the average inventory, service levels, and profit in the spaces below. Service level is calculated as the percentage of the demand that is satisfied. You do not need data tables to answer this question, just create the

$30$

$-$

day simulation. To generate any random variables do not use the rand

$($

$)$

$$function on Excel, instead use the values given on the RAND

$($

$)$

$$column$.$