I NEED THE EXCEL SOLUTION WITH THE FORMULAS TO COMPARE WITH MINE PLEASE!!!!

CASE STUDY Managing Inventories at ALKO Inc. ALKO began in 1943 in a garage workshop set up by John Williams at his Cleveland home. John had always enjoyed tinkering, and in February 1948 he obtained a patent for one of his designs for lighting fixtures. He decided to produce it in his workshop and tried marketing it in the Cleveland area. The product sold well, and by 1957 ALKO had grown to a $3 million company. Its lighting fixtures were well known for their outstanding quality. By then, it sold a total of five products it was structured. Fisher realized that the key was in the operating performance. Although the company had always been outstanding at developing and producing new products, it had historically ignored its dis- tribution system. The belief within the company was that once you make a good product, the rest takes care of itself. Fisher set up a task force to review the company's current distribution system and come up with recommendations. In 1963, John took the company publ ic. Since been very successful, and the company then, ALKO has has started distributing its products nationwide. As com- petition intensified in the 1980s, ALKO introduced The task force noted that ALKO had 100 products in its many new lighting fixture designs. The company's 2009 line. All production occurred at three facilities profitability, however, began to worsen despite the fact located in the Cleveland area. For sales purposes, the that ALKO had taken great care to ensure that product contiguous United States was divided into five regions, quality did not suffer. The problem was that margins had as shown in Figure 12-7. A DC owned by ALKO operated begun to shrink as competition in the market intensified. in each of these regions. Customers placed orders with At this point, the board decided that a complete reorgan the DCs, which tried to supply them from product in ization was needed, starting at the top. Gary Fisher was inventory. As the inventory for any product diminished, hired to reorganize and restructure the company The Current Distribution System the DC in turn ordered from the plants. The plants scheduled production based on DC orders. Orders were transported from plants to the DCs in TL quantities because order sizes tended to be large. On the other When Fisher arrived in 2009, he found a company teetering on the edge. He spent his first few months trying to understand the company business and the way Region 2 Region Region 5 Region 3 Region 4 FIGURE 12-7 Sales Regions for ALKO hand, shipments from the DC to the customer were LTL ALKO used a third-party trucking company transportation legs. In 2009, TL costs from the plants to DCs averaged $0.09 per unit. LTL shipping costs from a DC to a customer averaged $0.10 per unit. On average, five days were necessary between the time a DC placed an order with a plant and the time the order was deliv- ered from the plant. The task force identified that plant capacities for both allowed any reasonable order to be produced in four days. Thus, a plant shipped out an order four days after receiving it. After one day in transit, the order reached the DC. The replenishment lead time was thus five days The DCs ordered using a periodic review policy with a reorder interval of six days. The holding cost incurred was $0.15 per unit per day whether the unit was in The policy in 2009 was to stock each item in every transit or in storage. All DCs carried safety inventories DC. A detailed study of the product line had shown that there were three basic categories of products in terms of the volume of sales. They were categorized as types High, Medium, and Low. Demand data for a representa- to ensure a CSL of 95 percent. Alternative Distribution Systems High, Medium, and Low. Demand data for a representa- tive product in each category is shown in Table 12-8. The task force recommended that ALKO build a Products 1, 3, and 7 are representative of High, Medium, national distribution center (NDC) outside Chicago and Low demand products, respectively. Of the 100 The task force recommended that ALKO close its five products that ALKO sold, 10 were of type High, 20 of DCs and move all inventory to the NDC. Warehouse type Medium, and 70 of type Low. Each of their capacity was measured in terms of the total number of demands was identical to those of the representative units handled per year (i.e., the warehouse capacity products 1, 3, and 7, respectively Alternative Distribution Systems r a representa was given in terms of the annual demand supplied Table 12-8 Distribution of Daily Demand at ALKO Part 1 M Part 1 SD Part 3 M Part 3 SD Part 7 M Part 7 SD Region 1 35.48 6.98 2.48 Region 2 22.61 6.48 Region 3 17.66 5.26 6.15 6.39 0.80 2.39 Region 4 11.81 3.48 6.16 6.76 1.94 3.76 Region 5 3.36 4.49 7.49 3.56 2.54 3.98 6.20 0.73 1.42 0.48 1.98 1400000 1200000 800000 400000 200000 0 200 400 600 800 1000 1200 Thousands of Units Sold Through DC FIGURE 12-8 Construction Costs for NDC from the warehouse). The cost of constructing a warehouse is shown in Figure 12-8. However, ALKO expected to recover $50,000 for each warehouse that it closed. The CSL out of the NDC w be 95 percent Fisher's Decision Gary Fisher pondered the task force report. It had not detailed any of the numbers supporting the decision. He decided to evaluate the numbers before making his decision. ould continue to Given that Chicago is close to Cleveland, the QUESTIONS inbound transportation cost from the plants to the NDC would fall to $0.05 per unit. The total replenishment lead time for the Chicago NDC would still be five days (four days for production + one day in transit). Given the increased average distance, however, the outbound transportation cost to customers from the NDC would increase to $0.24 per unit 1. What is the annual inventory and distribution cost of the current distribution system? 2. What are the savings that would result from following the task force recommendation and setting up an NDC? Evaluate the savings as the correlation coefficient of demand in any pair of regions varies from 0 to 0.5 to 1.0. Do you recommend setting up an NDC? Other possibilities the task force considered include building a national distribution center while keeping the regional DCs open. In this case, some products would be stocked at the regional DCs, whereas others would be stocked at the NDC. 3. Suggest other options that Fisher should consider Evaluate each option and recommend a distribution system for ALKO that would be most profitable. How dependent is your recommendation on the correlation coefficient of demand across different regions? CASE STUDY Managing Inventories at ALKO Inc. ALKO began in 1943 in a garage workshop set up by John Williams at his Cleveland home. John had always enjoyed tinkering, and in February 1948 he obtained a patent for one of his designs for lighting fixtures. He decided to produce it in his workshop and tried marketing it in the Cleveland area. The product sold well, and by 1957 ALKO had grown to a $3 million company. Its lighting fixtures were well known for their outstanding quality. By then, it sold a total of five products it was structured. Fisher realized that the key was in the operating performance. Although the company had always been outstanding at developing and producing new products, it had historically ignored its dis- tribution system. The belief within the company was that once you make a good product, the rest takes care of itself. Fisher set up a task force to review the company's current distribution system and come up with recommendations. In 1963, John took the company publ ic. Since been very successful, and the company then, ALKO has has started distributing its products nationwide. As com- petition intensified in the 1980s, ALKO introduced The task force noted that ALKO had 100 products in its many new lighting fixture designs. The company's 2009 line. All production occurred at three facilities profitability, however, began to worsen despite the fact located in the Cleveland area. For sales purposes, the that ALKO had taken great care to ensure that product contiguous United States was divided into five regions, quality did not suffer. The problem was that margins had as shown in Figure 12-7. A DC owned by ALKO operated begun to shrink as competition in the market intensified. in each of these regions. Customers placed orders with At this point, the board decided that a complete reorgan the DCs, which tried to supply them from product in ization was needed, starting at the top. Gary Fisher was inventory. As the inventory for any product diminished, hired to reorganize and restructure the company The Current Distribution System the DC in turn ordered from the plants. The plants scheduled production based on DC orders. Orders were transported from plants to the DCs in TL quantities because order sizes tended to be large. On the other When Fisher arrived in 2009, he found a company teetering on the edge. He spent his first few months trying to understand the company business and the way Region 2 Region Region 5 Region 3 Region 4 FIGURE 12-7 Sales Regions for ALKO hand, shipments from the DC to the customer were LTL ALKO used a third-party trucking company transportation legs. In 2009, TL costs from the plants to DCs averaged $0.09 per unit. LTL shipping costs from a DC to a customer averaged $0.10 per unit. On average, five days were necessary between the time a DC placed an order with a plant and the time the order was deliv- ered from the plant. The task force identified that plant capacities for both allowed any reasonable order to be produced in four days. Thus, a plant shipped out an order four days after receiving it. After one day in transit, the order reached the DC. The replenishment lead time was thus five days The DCs ordered using a periodic review policy with a reorder interval of six days. The holding cost incurred was $0.15 per unit per day whether the unit was in The policy in 2009 was to stock each item in every transit or in storage. All DCs carried safety inventories DC. A detailed study of the product line had shown that there were three basic categories of products in terms of the volume of sales. They were categorized as types High, Medium, and Low. Demand data for a representa- to ensure a CSL of 95 percent. Alternative Distribution Systems High, Medium, and Low. Demand data for a representa- tive product in each category is shown in Table 12-8. The task force recommended that ALKO build a Products 1, 3, and 7 are representative of High, Medium, national distribution center (NDC) outside Chicago and Low demand products, respectively. Of the 100 The task force recommended that ALKO close its five products that ALKO sold, 10 were of type High, 20 of DCs and move all inventory to the NDC. Warehouse type Medium, and 70 of type Low. Each of their capacity was measured in terms of the total number of demands was identical to those of the representative units handled per year (i.e., the warehouse capacity products 1, 3, and 7, respectively Alternative Distribution Systems r a representa was given in terms of the annual demand supplied Table 12-8 Distribution of Daily Demand at ALKO Part 1 M Part 1 SD Part 3 M Part 3 SD Part 7 M Part 7 SD Region 1 35.48 6.98 2.48 Region 2 22.61 6.48 Region 3 17.66 5.26 6.15 6.39 0.80 2.39 Region 4 11.81 3.48 6.16 6.76 1.94 3.76 Region 5 3.36 4.49 7.49 3.56 2.54 3.98 6.20 0.73 1.42 0.48 1.98 1400000 1200000 800000 400000 200000 0 200 400 600 800 1000 1200 Thousands of Units Sold Through DC FIGURE 12-8 Construction Costs for NDC from the warehouse). The cost of constructing a warehouse is shown in Figure 12-8. However, ALKO expected to recover $50,000 for each warehouse that it closed. The CSL out of the NDC w be 95 percent Fisher's Decision Gary Fisher pondered the task force report. It had not detailed any of the numbers supporting the decision. He decided to evaluate the numbers before making his decision. ould continue to Given that Chicago is close to Cleveland, the QUESTIONS inbound transportation cost from the plants to the NDC would fall to $0.05 per unit. The total replenishment lead time for the Chicago NDC would still be five days (four days for production + one day in transit). Given the increased average distance, however, the outbound transportation cost to customers from the NDC would increase to $0.24 per unit 1. What is the annual inventory and distribution cost of the current distribution system? 2. What are the savings that would result from following the task force recommendation and setting up an NDC? Evaluate the savings as the correlation coefficient of demand in any pair of regions varies from 0 to 0.5 to 1.0. Do you recommend setting up an NDC? Other possibilities the task force considered include building a national distribution center while keeping the regional DCs open. In this case, some products would be stocked at the regional DCs, whereas others would be stocked at the NDC. 3. Suggest other options that Fisher should consider Evaluate each option and recommend a distribution system for ALKO that would be most profitable. How dependent is your recommendation on the correlation coefficient of demand across different regions