SX Question 9 FLAG QUESTION (This relies on the file Sample - Superstore.xlsx and postal-code-delivery-data.xlsx that can be found in LumiNUS. Use those files to avoid problems associated with different versions.)

(This question might be viewed as the second of two independent parts of the Delivery from the Customer Perspective question. As the ordering of questions may not be sequential, you may want to check if the other independent part was included in the assessment this semester.) You are managing fulfilment at a nationwide retailer in the USA. You are interested in the customer experience but have limited data on fulfilment due to late adoption of supply chain management methodologies and technology. If an order ships the same day, we take the gap between ordering and shipping to be 0.25; if an order ships the day after it was ordered, we take the gap between ordering and shipping to be 1.25; if an order ships two days after it was ordered, we take the gap between ordering and shipping to be 2.25; and so on. There is data on average timings between orders being shipped and arrival at the customer's address in postal-code-delivery-data.xlsx. If there is missing data on the Average Delivery Lead Time (Days) (time from shipment to arrival at a customer's address) for a given Postal Code, you will fill in the missing value with the current average across all orders in the state for which there is data (this has to be weighted against the number of orders). If there is no data for the entire state, then use 3.5, a conservative value for the entire country. Calculate the average End to End lead time between orders being placed and deliveries arriving at the customer address (in days) for the following states: • Alabama: • Arkansas: (2) • California: • Colorado: • Mississippi: 5 Give your answers to 2 decimal places. Note: In this question, Ship Mode does not matter. Hint: Postal Codes can be tricky to work with. There is at least some postal code data for New Jersey and New Hampshire. Hint: Be careful to avoid double counting since there might be multiple rows from the same order.