If you need any additional information please ask but this is should be everything needed

EXERCISE: THE E-GAME COM PANY Late one Friday afternoon, George Heller was trying to formulate an approach to his job as nished goods warehouse manager for the E-Game Company. As nished goods warehouse manager, he had the responsibility for managing the inventories of the com- pany's entire line of computer games. He decided to study in detail a representative product, The Big Game, as the basis for his plans. Background One of the important elements of communication in the company was the Monday morning management meeting. During this time, the key people of the company got together and discussed current problems, production plans, and new product ideas. It was during these meetings that Mr. Heller was to place replenishment orders for prod- ucts that were getting low in inventory. These orders were given directly to the produc- tion manager, Roger Blake. The E-Game Company's production process was quite simple. The production manager received all replenishment orders at the Monday meeting and forwarded them to a nearby DVD duplication company where the DVDs were prepared during the early part of the week. During the latter part of the week and sometimes on the weekend, the games were assembled and boxed at the E-Game Company plant using part-time help. The completed games were packed in cases and transferred to the nished goods warehouse on Monday morning of the following week. In discussing the situation with the production manager, Mr. Heller was assured that, at least for the foreseeable future, there would be no limitations on production capacity. In his discussion with other people in the company, Mr. Heller found that when the company didn't have enough inventory to ll a customer's order, the amount by which it was short was lost. That is, the company was not able to backorder the shortage, and its customers apparently lled their requirements with competitive products. In discussing the nished goods inventory with other ofcers, Mr. Heller found that space was not a critical problem. The nished goods warehouse had been designed with space for expansion into new product lines should the company so desire. Capital, how- ever, was a continual problem for the company because of a rapid growth in the product line. Mr. Heller felt he could use the Friday night inventory balance to determine the inventory level for capital investment purposes. The Big Game Mr. Heller turned his attention to The Big Game as a representative company product. His predecessor had left him no information on the management of the inventories, but there were two years of demand history for The Big Game (see Figure 10-3). Two Years Two Years Week Ago Last Year Week Ago Last Year 25 19 26 22 19 18 26 27 19 26 21 17 28 26 22 21 18 29 18 28 21 26 30 18 28 17 17 31 19 18 19 17 32 21 17 18 17 33 18 16 20 22 34 19 16 10 19 22 35 20 17 11 25 18 36 18 18 12 24 17 37 19 17 13 20 19 38 23 21 14 19 18 39 23 26 15 19 19 40 18 24 16 17 18 41 17 18 17 18 22 42 17 18 18 23 20 43 23 19 19 20 17 44 20 23 20 19 22 45 19 18 21 23 26 46 17 25 22 18 17 47 27 23 23 19 17 48 20 18 24 19 18 49 28 16 25 19 22 50 22 17 FIGURE 10-3 The E-Game Company: Past Demand for The Big Game (Cases per week) In discussing The Big Game with the salesmen, Mr. Heller found that it was a relatively stable item in the company's product line and that it had no seasonal sales peaks. The salesmen agreed that conditions in the current year would not be different from those of past years and past demand would be a good indication of what to expect in the future. In reviewing the costs of The Big Game, Mr. Heller found that the DVD duplica tion company charged a fixed amount of $9 for each order to cover the costs of setting up their duplicating equipment and delivering the finished DVDs to the company. Therewere no comparable xed costs for the assembly of the completed games at the plant. The management of the company had estimated that it cost $1 per case for each case backordered on a customer's order. This represented both the loss of prot on that case and some measure of lost goodwill. An estimate of the opportunity cost of capital and direct costs of carrying inventory had been made, and for The Big Game this amounted to $0.10 per week per case. Since he had decided that the Friday night inventory was the relevant inventory, Mr. Heller decided to use that as the inventory level against which he would assess the $0.10 cost. There was a balance of 43 cases of The Big Game in inventory, and he hadn't placed a replenishment order in the last management meeting. He next turned his attention to investigating different methods for managing the inventories and to see if he needed to place an order on the following Monday. Simulation Mr. Heller thought one approach to the evaluation of different alternatives for managing the inventories would be simulation using a spreadsheet. He devised a spreadsheet on which he could evaluate different alternatives. He used last year's demand to test his spreadsheet (see Figure Iii-4). Week Number . ._ Monday Mon-ling 43' 53" 35 14 100\" 79 B2 43 25 40 Inventory Week! Demand 25 10 21 21 21 17 13 18 20 12 Friday Night 13 as 14 4 re 02 4a 25 s 21 Inventory Number Ordered 35 0 0 100 0 0 0 0 35 0 Setup Costs 0.002 0 0 9.00 0 0 0 0 3.00 0 {$9 per order) Inventory Coats 1.30II 3.50 1.40 0 7.30 5.20 4.30 2.50 .50 2.10 ($0.1 per case) Shortage Coats 0 0 0 7.00\" 0 0 0 0 0 0 {$1 per case) Total Goats for 10.30 3.50 1.40 15.00 130 5.20 4.30 2.50 3.50 2.10 Week Cumulatlve Goat -- 10.00 14.30 15.70 31 .?0 33.50 45.30 50.10 52.50 62.10 from L351 Week CLI'nuletlve Goat 10.00 14.30 15.70 31.70 39.00 45.30 50.10 52.50 52.10 54.20 to Date Notes: 1moaning balance as of next Monday: zcost of placing order for 35 cases; 3Friday night inventory at $0.10 per case; 'Friday night Inventory of 13 plus order of 35: \"Short 7' cases at $1 per case: HFThe order of 100 only since the Y cases short are not back ordered . FIGURE 10-4 Sample Evaluation Sheet for the E-Game Company Assignment: 1. Using Mr. Heller's spreadsheet, see how well you can manage inventory for the next 10 weeks. 1 Develop decision logic to aid Mr. Heller in making his Monday morning decision. One suggestion might be to determine the number of units that he would be comfortable with on Monday morning without placing an order. When an order is needed, determine how many cases should be ordered. 5. Program your decision logic into the spreadsheet, so that if demand is changed, orders are automatically initiated. 4. Test your logic by using the following demand stream: 18 18 17 25 21 19 18 25 20 19, with a starting inventory of 43 cases. What is the total cumulative cost over the 10 weeks