please fast in excel...............

Inventory Management via Simulation (Please Submit the Excel Spreadsheet Model) SI Introduction of Inventory Management Inventory Management (IM) is to the process of ordering, storing, and using a company's/firm's inventory. These include the management of (1) raw materials, (2) components, and (3) finished products as well as warehousing and processing such items. IM uses a variety of data to keep a record of the goods as it moves through the process, including lot numbers, serial numbers, cost of goods, quantity of goods and the dates when they move through the process. IM helps in minimizing cost, optimize fulfillment, provide better customer service, and avoid loss from theft, damage, and return. In this sense, Inventory Management is the science of purchasing, supervising, controlling and dispensing of Stock for Sale which is stored in a facility. It covers all aspects of stock management across the supply chain from the manufacturer to the point of sale manned by the retailer. The process of inventory management begins from the time you raise a purchase order and ends (for every order) when an order is fulfilled/ delivered to the end customer. As an inventory manager, your job is to manage inventory smoothly and at as low a cost as possible so that you can Sell the right products Block the least possible working capital at all times Fulfill orders on time so as to build customer loyalty Sustain your sales model. Effective inventory management affects every aspect of the businessfrom warehousing costs to the ability to fulfill orders accurately and on time. The inventory manager wants to be on top of everything from raw materials to finished goods. Unfortunately, inventory management is a difficult business process to do by hand. It takes time, and if you make a mistake, it could impose ripple or disruptive effects that negatively impact the business for months or years. 1 Simulation Practice for Inventory Management A local company stocks product BMW001 each week. The company's cost for BMW001 is $ 100.00 per unit and it sells the product for $300.00 per unit. Units not sold in one week are held over and sold the next week at a cost of $ 30.00 per unit, charged on beginning inventory. The company charges itself $20.00 per unit for lost sales. The distribution of demand is shown below. There are 10 units in beginning inventory at the start of week one. Demand 200 230 240 250 260 300 350 Probability 0.05 0.10 0.30 0.30 0.10 0.10 0.05 3. You are to add/adjust constraint(s)/variables(s)/parameter(s)/objective function for Ex- ercise 3 from the second practice problem set (a copy of the problem is also on page 2). Specifically, update the parameterized model to include the following: (a) Facilities has concerns about over-stressing our machines. They indicated that each machine should be used at most 40% of the total time spent to produce all projects, e.g., If machines 1-4 run for 45, 35, 15, and 10 hours respectively, then this would be problematic since machine 1 runs for 45% of the total 100 hours used. Update the model accordingly. (b) While we are supposed to provide di of each product, our contract allows us to produce less at a penalty of p; for each missing unit of product i. Update the model accordingly. (c) Each machine can be run after hours for an increased cost. Specifically, it costs an additional V, for each extra hour (beyond the initial limit of a;) to run machine j. Update the model accordingly. A production manager is planning the scheduling of three products on four machines. Each product can be manufactured on ench of the machines. The unit production costs (in $) are summarized in Table 1 and the time (in hours) required to produce a unit of each product on each of the machines is in Table 2. Table 1. Production cost for each product on each machine. Machine Product 3 1 4 6 12 2. 4 5 7 5 10 8 4 7 6 11 2 3 Table 2. Hours required to produce 1 unit of each machine on each product. Machine Product 1 2 3 4 1 0.3 0.25 0.2 0.2 2 0.2 0.3 0.2 0.25 3 0.8 0.6 0.6 0.5 Suppose that 3000, 6000, and 4000 units of the products are required, and that the available machine hours are 1500, 1200, 1500, and 2000 respectively. To determine the optimal assignments of products to machines, we solve the following LP: Assumptions: Fractional units are allowed. Parameters: ni muumber of products, m: number of machines. tiji manufacturing time of product i on machine ; for i = 1, ..., n and j = 1,...,m