Theinventory control problem arises when it becomes necessary to create a stock of material resources or commodities with the purpose of meeting the demand within a given time span (finite or infinite.) The challenge in any inventory control problem is to determine the quantity of products to be ordered and the moment for placing the order, which both affect the amount of the costs. Solutions regarding the size of an order and the moment for its placement can be based upon the minimization of the corresponding overall costs function. The total costs of a inventory management system can be described as a function of its primary components as follows: Total cost of a inventory management system = Acquisition cost + Ordering cost + Holding cost + Deficiency losses Unfortunately, the general solution to the inventory control problem cannot be obtained on the basis of one model. Therefore, most diverse models, describing different particular cases have been developed. One of the determinatives in the development of a inventory management model is pattern of demand.
Dynamic Lot Sizing Model
Consider a situation where demand for an item is known in advance with certainty for a number of future periods, and the demand for the item varies from period to period.For many practical situations, the demand is known precisely for a certain number of periods. Such situation frequently arises in material management, that is, where the item is a raw material or a component part in a manufacturing process. Demand for the item for several periods in advance can be inferred from the master production schedule during the MRP process. We must plan a sequence of orders, or production batches, over a T period planning horizon. In each period, a single decision must be made: the size of the order or the production batch. To specify the problem and model, we will make use of the following notations: t= time period (e.g., day, week, month); we will considert = 1,2,…,T, whereTrepresents theplanning horizon. dt= demand for period t (in units,non-negative integers.) Ct= unit production cost, not including setup or inventory costs for period t. At= setup (order) cost to produce (purchase) a lot in period t. Ht= holding cost to carry a unit of inventory from period t to period t+1. It= inventory (in units) left over at the end of period t. Xt= lot size (in units, non-negative integers) in period t (the decision variable.) Using these variables, the problem can be formulated as follows:
It =It-1+ Xt- dt(*),
It, Xt>=0 (t=1..T)
Here constraints (*) are calledinventory-balance constraints.Note that the inventory can also be rewritten as: ∑( Xt-dt) (t=1..T), and therefore, the Itvariables can be eliminated from the formula.The basic problem is to meet all the demands at minimal cost. The only controls are the production quantities. We will also make the following standard assumptions: - Lead time is zero; orders arrive immediately. (Unit production time is negligible.) A different assumption is to be made for the real conditions; according to it, the unit production time can be determined with a negligible inaccuracy. Thus, assume it takes two weeks to produce a lot of units. If according to the production program the demand is met by the units produced in February, in fact, the manufacture of that lot is to be launched two weeks ahead; i.e. in the second half of January.) - Demand is needed and consumed on the first day of a period. Holding costs are therefore not charged on the items consumed during the period. (Also, it is possible to model a situation, where inventory charges are estimated from an average level
-Costs for each period depend on the current lot size and on the inventory rate at the end of the period; demand for each period completely is met.
There is a series of techniques for solving this problem. Some of them belong to the heuristic methods; i.e. they do not guarantee obtaining the optimum solution. Moreover, often the solutions can be very distant from the optimums. There are precise algorithms too. One of them is the Wagner-Whitin algorithm. However, in order to apply the Wagner-Whitin algorithm, one important restriction is to be observed: On stage I, the production (purchase) cost per unit and its holding costs are the concaved functions (constant or non-increasing) of the volume of produced (purchased) and held units respectfully. The dynamic programming method relieves us from such restriction and allows applying any functions of costs. However, it has high calculation difficulty, which increases very quickly with the increase of the number of possible actions. This peculiarity is to be taken into account when composing models. Since the values entered in the model are discrete (integer), each possible value represents an action choice. Therefore, we should take a point that actually affects the result of the managerial decision. For example, instead of describing a model in kilograms, it may be better to switch to larger points (a centner, 500 kg, a ton, carriage…), if there is no real need for such a small point. Thiswouldessentiallycutthecalculations.Also, limiting the top level of possible dimensions of the order will let us do without labor-intensive calculations.
This is an Excel-based dynamic order size problem solver, using the dynamic programming method and the Wagner-Whitin algorithm. Once Add-in dInventory is installed, Excel will get a respective toolbar. To create a new model table, click “Create”. That will create a new sheet with the model table that includes the title and one line. The number of lines in the table matches the number of periods – the planning horizon. In general, nothing binds you to lock periods to equal periods of time. Use the “+” and the “-“ buttons to increase or decrease the number of periods. For each period, we are to determine:
Order Cost function. It unites Setup Cost and Order Cost. These may depend on Order Quantity for current period (as a rule), any other cell outside the table, or remain constant. But directly or indirectly they cannot depend on other cells of the table.
HoldingCostfunction.May depend on inventory volume for current period (as a rule), any other cell outside the table, or remain constant.
Maximum order quantity for current period. Provided only for purposes of the dynamic programming algorithm. By default it’s equal to the total demand from current period until next one.
The remainder is calculated by the formula: Inventoryi= Inventoryi-1+ OrderQuantityi- Demandi. The “Run(DP)” button launches the search for a solution with the dynamic programming algorithm; the “Run(WW)” button does the same with the Wagner-Whitin algorithm. The solution for the model is the Order Quantity column.
Building the model in Excel imposes certain restrictions on the productivity of the calculation. You can order (firstname.lastname@example.org) the development of a custom application, which would run noticeably faster and probably with your information system.