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: Inventory costs are applied to each unit of product held in inventory for each day and are calculated based on the beginning inventory. Every time Prysmian requests an order, it incurs an ordering cost. Finally, 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. Average Inventory (Round to 2 decimals.) [Fill in the blank] Service Level (Round to 2 decimals. Report it as a %. Answer format should be ##.##) [Fill in the blank] % Profit (Round to 2 decimals.) [Fill in the blank] 2. We now must consider the warehouse capacity. If the ending inventory is larger than the warehouse capacity (given on the spreadsheet), Prysmian pays a per unit fee to store these items at a rental warehouse. The per unit warehouse fee is given on the spreadsheet. To generate any random variables do not use the rand() function on Excel, instead use the values given on the RAND() column. What is the profit? (Round your answers to 2 decimals.) [Fill in the blank] 多项填空题