I need help with Question #3. Please show all work on excel and show all the formulas. Thank you!!

CASE STUDY Should Packing Be Postponed to the DC? Penang Electronics (PE) is a contract manufacturer that the previous month when Best Buy did not get its entire produces and packages private-label products for several order could have been avoided through postponement. If retail chains, including Target, Best Buy, Staples, and packaging was shifted to the DC, the lead time of manufac- Office Max. In each case, the basic products are identi- turing and transporting the basic product from Malaysia cal, with the only difference being the labeling and the would continue to be about nine weeks. Labeling and pack- packaging. Thus, the labeled and packed version of the aging were relatively quick steps and the response time product destined for Target cannot be sent to Best Buy. from the DC to the customer was not expected to change. Currently, a production facility in Malaysia is used The DC management was opposed to this idea to manufacture, label, and pack all products. The manu- because it would create additional work that was differ- facturing facility replenishes a DC in St. Louis, from ent from what they had done so far. A detailed study of which the contract manufacturer fills all customer the production process showed that labeling and packag- orders. The manufacturing and transportation lead time ing at the DC cost $2 per unit more than the cost of from Penang to St. Louis is nine weeks. PE uses a con- labeling and packaging in Malaysia. DC management tinuous review policy to manage inventories at its DC believed that this increase in cost would be held against and aims to provide a cycle service level of 95 percent them once the process was changed, and they would be for each product to every customer. under constant pressure to lower cost. They also believed The previous month had been very challenging it would complicate the work they did when filling an because Best Buy requested 5,000 additional units beyond order and could adversely impact customer service. what was available at the DC, whereas Target ordered 3,500 fewer units and Staples ordered 4,000 fewer units. Evaluating the Two Options Even though there was sufficient product inventory avail- To evaluate the two options, a team from both manufac- able at the DC (in the form of the basic product), PE could turing and the DC was set up. The team decided to focus not meet the Best Buy request because the excess inven- its analysis on three major product categoriescomput- tory available was labeled and packed for other custom- ers. The DC had leftover inventory from Target and ers, printers, and scanners, and four major customers- Target, Best Buy, Staples, and Office Max. Weekly Staples, which unfortunately could not be used to serve Best Buy. PE had lost business while carrying surplus demand for each product and customer is shown in Table 12-9. In each case, "Mean" indicates the average inventory, all because of the wrong labels and packaging. weekly demand, and "SD" indicates the standard devia- Labeling and Packaging at the DC tion of weekly demand. All demand was assumed to be normally distributed. PE incurred a total cost of $1,000 The vice president of supply chain at PE proposed post- per computer, $300 per printer, and $100 per scanner. poning the final labeling and packaging to the DC. Her Given the short life cycle of these products, PE used an logic was that postponing labeling and packaging to the annual holding cost of 30 percent when making its DC would allow PE to use all available inventories to inventory decisions. It was assumed that batch sizes and serve any customer. In particular, the situation that arose in cycle inventories would be unchanged irrespective of TABLE 12-9 Distribution of Weekly Demand by Product and Customer Computers Printers Scanners Mean SD Mean SD Mean SD 700 Target Best Buy Office Max 1,000 700 1,000 800 1,000 900 600 2,000 1,500 1,200 900 4,000 4,500 2,000 1,400 800 600 600 700 Staples 500 400 500 500 where packaging and labeling were done. Thus, the team analyzed the impact of postponement on safety invento- ries before making a final recommendation. Questions 2. How would the holding cost of safety inventory change if labeling and packaging were moved to the DC? Evaluate the change in inventory costs as the correlation coefficient of demand between any pair of customers varies from 0 to 0.5 to 1.0. 3. How should PE set up its production, labeling, and pack- aging processes? Does your answer change if the addi- tional cost of labeling and packaging at the DC is reduced to $1 (from the current value of $2)? 1. What is the annual holding cost of safety inventory for the current system in which product is produced, labeled, and packed in Malaysia before being shipped to the DC