Show the formulas to question one in excel and clearly state the answers.

Heinkel-Fishbein is a large importer and distributor of robotic toys. The toys are stored in the warehouse and are shipped to a several large retail chains at a standard price.. There is almost no possibility in the near future of changing the prices at which Heinkel-Fishbein supplies the retail stores. Thus there is a need to increase profits by managing total costs. Considering one toy, identified as SKU 2600 , the process consists of receiving the boxes as shipped by the manufacturer, and storing in the warehouse to be divided and shipped to the stores as per shipment schedule. The cost factors are described as follows. The suppliers have a discount schedule, and the prices are lower for a higher volume of order, or shipment. Two suppliers are considered here. Their discount schedules are the same as shown in Tables 1. The inventory holding cost remains the same for both suppliers at 0.25(25%) of the purchase price. The Ordering Cost (Setup Cost) is also the same at $60. The annual demand for SKU 2600 is 24,000 units. The reason for having alternate suppliers is that the contract is up for review for the next year, and the company needs to determine the best policy not only for their ordering schedules but also for the best implementation of a JIT policy. Under the current contract, the toys are produced and supplied by Schneider Gmbh in Germany, with an annual contract for regular and timely supply. . The lead- time for delivery from Germany is typically 10-12 working days, or 2 weeks. They are considering an alternate supplier Yamaguchi from Japan, and the supplier may be willing to negotiate prices, but there are some concerns. The lead-time for delivery from Japan is at least 4 weeks. This places a pressure on Heinkel-Fishbein to stock a larger number of units to account for the variability of demand in lead-time and the possibility of a stockout. The contract requirements with the retail stores state that in the case of a stockout, Heinkel-Fishbein must pay a penalty of $150 per unit of stockout. Typically, a stockout occurs in periods of high demand, such as holidays and special demand periods. Thinking of the high cost of stockout, Heinkel-Fishbein is planning on moving to a JIT (Just-in-Time) policy where prices can be negotiated on annual demand quantities but the supplier must supply in small and frequent lots making short lead times more attractive. Table 1. Questions 1. Considering costs alone, what are the respective costs of the different ordering policies? 2. In a JIT environment, a typical approach is to consider annual demand as the quantity of an order, with prices that apply to this quantity. Comment on this approach in this context. 3. Comment on the cost of stockouts, and the need for avoiding stockouts in terms of the costs. What does it do to inventory policy? A quick reading of the problem indicates that the main question is about inventory and inventory management. Inventory Management is Chapter 13 in the text book. If it has been a while since you read that chapter, it may be a good idea to go back and glance at the chapter again. Specifically the question is about the optimal policy for ordering and inventory, which translates into calculating the optimal order quantities. The book describes three inventory models, respectively Economic Order Quantity, Economic Production Quantity, and Quantity Discount. When you look at the given data and the price discounts that go with different order volumes, the problem has a clear fit with the Quantity Discount model and no other. Refer again to the book. The Quantity Discount model requires you to calculate the total cost for each order policy using the formula below: TC=PD+(Q/2)H+(D/Q)S The Purchase Price (P) varies according to the order quantity Q, and Holding Cost (H) is expressed as a fraction of the Purchase Price. The first step is to calculate the Order Quantity Q for each discount level, or each purchase price bracket. This is done by using the basic EOQ formula (refer to book). Please do not be confused by the word "infeasible" in the book. All that it means is that you cannot get the discount if the order quantity is less than the threshold for the discount. You must bump the Order Quantity up to the minimum level required to get the discounted price, and then calculate total cost using this Order Quantity. In the end, you will have a Total Cost for each discount price for each supplier. The best (lowest) Total Cost will be the best policy to use