Objectives

•  To gain an understanding of simulation

•  To understand the concept of service level

•  To generate a simple simulation for an inventory management system

Learning outcomes

By the end of this tutorial the student should be able to:

•   create a simple inventory simulation;

•   demonstrate that changes to inventory policies can have large effects on the service level.

First things first

•    Download the file, “Inventory Simulation_StartFile”, from CECIL and load it onto your J Drive.

The problem statement

Kim is in charge of inventory at Jandal Retailer Ltd (JRL). Kim wants to guarantee a high service level, at least 95%, for the premium line of jandals, product J. This means that JRL needs a policy that will ensure there is sufficient stock on hand to meet variable demand and also deal with the variability in the delivery of the products to his retail store. Currently JRL places an order for 10 units of product J whenever the on- hand inventory drops to the reorder point of 15 units of stock, or less.

A University of Auckland OM student studied the operations at Jandal Retailer for a class project. The student looked at the sales records and the orders and deliveries for the past year for product J and has given Kim some data to work with. Kim knows that the data presented in Table 1 is for the demand:

The following table gives the lead times:

Kim now wants to know whether the student can develop an inventory policy that will help him maintain a high service level of at least 95% while using the least amount of inventory.

Getting started

We are going to use the spreadsheets ability to generate random numbers to perform the simulation. Two tables will be set up in the spreadsheet containing the data in Table 1 & Table 2 on the previous page and the simulation will use a random number and  then  refer  to  these  tables  to  get  the  appropriate  value  using  the  function

VLOOKUP.

You can see a screenshot of the working spreadsheet in the appendix.

Populating the Demand Table

The first step would be to set up the Demand Table – but this has already been completed for you. In cell D6 to cell D12 we have listed the different values demand can take, from 0-6 as shown in Table 1. Cells E6:E12 contain the corresponding probability from Table 1. Remember

that these probabilities are expressed as a fraction of 1 and you can multiply

them by 100 to get the percentage probability. To make sure that we have not

made any mistakes, we sum the column and it should give a total of 1.0 for

the column. These cells (D6:E12) have been highlighted (pale orange) to

remind your that these are cells you can change.

To the right in Figure 1 you can see the probabilities of units of demand and

the cumulative probability. The most common occurrence will be 3 units of

demand – the highest level that the red line reaches. It is generally common

for demand to be around 3, 4, or 5. The blue line takes the probability of

demand and adds it to a running sum to give the cumulative probability.

You will see that this reaches 1.0 or 100% for demand of 6 units.

Now notice the way that the Limits numbers in column B & C are increasing in a cumulative fashion. The value in the C12 should be equal to 1.0 and you might want to change the formatting in the cells which contain probabilities so that they show two decimal places.

The Demand table is going to be used in conjunction with a random number. Assume that a random number was generated and it was 0.15. Referring to Figure 2 the number 0.15 falls in the interval between 0.08 and 0.21 (this has been circled). This means that the corresponding level of demand would be 2 units. The final column, Pi, notes that there was a 13% chance that the demand would be 2 units. If the random number was 0.70 it would indicate that the demand was 5 units. If the random number was 0.95 it would mean that the demand was 6. The lead-time table will work in a similar fashion. In this simulation each day a random number will be generated and it will be used to figure out what the random level of demand is for the day.

Figure 2 Using the Demand Table

Because the Demand Table has been filled in for you, you can refer to this to understand what is happening when you now need to work out the Lead Time Table.

Populating the Lead Time Table

In much the same way as the Demand Table was done we are going to populate now the Lead Time Table. In I6 to I9 enter the number of days for the lead-time from Table 2.

In J6:J9 enter the corresponding probabilities from Table 2. This cell range (I6:J9) is also highlighted (pale orange) to remind you that these are cells that the user can change. From Figure 3 you can see that the most likely occurrence will be a lead-time of 2.

Use the information in Table 2 from these instructions to fill out the shaded cells in columns I & J of the lead-time table in the spreadsheet.

Figure 3 Probabilities for the lead-time

•   Setting the limits for the lead-time table

Enter the value 0 (zero) in G6. Next to it in H6 enter

=J6

Now a similar procedure will be followed to generate the rest of the lead-time table. In cell G7 type

=H6

This makes sure that the new lower limit for a lead-time of 2 days is equal to the upper limit of the lead-time for 1 day.           In H7 the upper limit will be derived from the previous upper limit and the probability for a lead-time of 2 days, so it will be =SUM($J$6:J7)

The formulas for cells G7 & H7 can be replicated down to G9:H9. Note that the value in H9 should be 1.0. You might want to change the formatting in the cells which contain probabilities so that they show two decimal places.

In cell J10 sum the values of J6:J9 to show that these probabilities add up to one. This is a useful thing to show on the spreadsheet so a user can make some quick adjustments and be sure that they have not made any errors.

It is a good idea to retain the summation of the probabilities to double-check they equal one.

Populating the Output Table

This table is the ‘meat’ of the simulation spreadsheet. Each row will represent a different day in the simulation. Demand from the customers     will be decided using a random number. Changes will be made to the stock levels based on this demand. If new stock will be required an order will placed. This can be automated so that all the user is required to do is to refresh the spreadsheet (by pressing F9) and the table will generate new random numbers which will drive the actual demand for each period and the lead times for ordering if an order is placed.

•   First set up the table so there are 100 days in column B.

•   The beginning inventory levels

For Day 1 we need the value for our beginning inventory in cell D20. Take this value from the ‘order quantity’ listed at J13.

In Day 2 the beginning inventory (D21) is given by the inventory we started with on Day 1 minus the demand from Day 1 plus any new units received in Day 2. So in D21 we need

=D20+C21-F20   – This formula can be replicated all the way down to Day 100.

•   The units received levels

On the first day we will not receive any units so this cell (C20) can be left blank.

For subsequent days it is a more complicated. We want to see if the day is listed as a receipt day in column L. If the current day is listed as a day when an order should be received then the units received in the current day will equal the order quantity (J13). This can be expressed using the COUNTIF function and having it count the occurrence of times that day is listed in column L. If it is listed there then the units received will be equal to the order quantity. For example if it is Day 2 the COUNTIF function will reference the number of the day (2) and will check to see if the number ‘2’ is listed as a Receipt Day in column L. If it is listed then it will add the order quantity to the Units Received.

In C21 it will require a formula like this:

=COUNTIF(L$20:L20, B21)*J$13   (Remember to use the absolute referencing as appropriate.)

If it was listed as a receipt day several times then the units received will be equal to several orders. We will be replicating this formula down the column until Day 100 so we want the COUNTIF function to check the range from the start at L20 (which is locked using an absolute reference) to whichever period we are at now. It checks how many times the value for that Days Number is listed in that range, and then multiplies this by the Order Quantity. So for Day 4 it will check from L20:L22 and count the number of times the number ‘4’ is listed there and will then multiply this by the order quantity.

•   The random number column

Column E will contain random numbers generated by the spreadsheet. For Day 1 through to Day 100 the entry in column E should be =RAND( )

This function generates a random number between 0 & 0.99 which has many uses – some questions in your assignment contain numbers which we generate using this function!

•   The demand column

These entries will use the random number in column E and refer to the Demand Table at the top left of the spreadsheet. These values will be selected using the VLOOKUP function in the spreadsheet. Selecting F20 the following formula can be typed in:

=VLOOKUP(E20,$B$6:$D$12,3)

This will take the value from E20 (the ‘lookup_value’) and then it will compare this array in the Demand Table in B6:D12. Make sure you use absolute cell references for the table so that the formula can be replicated down the column. The final part of the VLOOKUP function, the ‘3’, refers to the 3rd  column of the array selected and this is what is returned. In this case it is the level of demand. So this function will take the random number generated in E20, it will then look down column B and find the value which the random number is higher than, and will then take the appropriate Demand level from that row (this was given in column 3 of the array we referenced with VLOOKUP) and return the value.

– Replicate this down to Day 100.

•   The ending inventory column

This column keeps track of how much inventory is left at the end of the day. It simply takes the starting inventory and the demand and returns the value of starting inventory minus demand if this value is above zero. If demand was greater than inventory then the ending inventory will

be zero. (We are not going not include ‘backorders’ in this model.) In G20 the formula will look like this:

=IF(D20-F20>0,D20-F20,0)

Replicate this down to Day 100.

•   The New Level column

Currently it is possible for this spreadsheet to generate too many orders. This can be prevented by tracking the on-hand stock levels and the scheduled receipts. This column needs to add scheduled receipts to the ending-inventory levels.

In Day 1 (cell H20) the value will equal the End Inventory for Day 1.

In subsequent days it needs to check whether there is an order placed in the previous day. If there is an order an amount equal to the Order Quantity is added to the Day 1 New Level and the Day 2 demand is subtracted. If there was no order placed in Day 1 then the Day 1 New Level

minus the Day 2 Demand is entered. This means the formula in H21 will look like:

=H20-F21+IF(J20="Yes",J$13,0)

Replicate this down to Day 100.

•   The Order? Column

This column keeps track of whether or not a new order needs to be placed. This is performed by checking the New Level of stock against the Reorder level specified in J12. If the New Level for stock has slipped below the Reorder level then a new order will need to be placed. So in J20 this formula should be entered:

=IF(H20<J$12,"Yes","No")

Replicate this down to Day 100.

•   The Lead Time column

This is the part of the spreadsheet which will check to see if an order is placed and then refer to the Lead-Time Table to determine what the lead time will be for this order. Again we will use the random number in column E and the VLOOKUP function to refer to the Lead-Time table. Use the VLOOKUP function to refer to the random number in column E. Compare this to the array in the Lead-Time table and return the appropriate

number. This is only done if the entry is “Yes” in the Order column for the current day. So the formula in K20 will look like: =IF(J20="Yes",VLOOKUP(E20,$G$5:$I$9,3),"")

Don’t forget to use absolute referencing using the $ signs and then you can replicate the formula down to Day 100.

•   The Receipt Day column

This column will refer to the Lead Time column. If there is an entry for “Yes” for the

current  day in the  Order?  column then the  current  day will be taken  and the

appropriate number of days from the Lead Time column will be added to this. You

will need to add an extra day as well. At the end of each day the inventory is counted

and if the inventory has fallen below the reorder point an order is placed the next

day. So if the inventory levels fall below the reorder point on Day 5 the order will

be placed on Day 6 and it has a Lead Time of 2 days so it will be scheduled to be

received on Day 8. If there is no order being placed that day then the cell can be left

blank. You will need to type a formula like:

=IF(J20="Yes",B20+K20+1,"")

Replicate this down to Day 100.

•   The Lost Sales column

This column records the number of lost sales for that day. If the beginning inventory

for that day is less than demand for that day then the lost sales will be the difference.

If beginning inventory for that day is greater than demand then all of the demand

can be met and there are no lost sales so a zero will be entered. This means that the

formula will look like:

=IF((D20-F20)<0,F20-D20,0)

Replicate this down to Day 100.

Working out the Service Level

The simulation is almost complete. Now all that needs to be done is work out the service levels so that a user (Bob) is able to figure out if the reorder point policy or ordering quantity policies need to be changed.

•   The totals for Lost Sales and for Demand

To work out the service level the sum of the Demand (from the Output Table) should be calculated in J15. The sum of Lost Sales (from the Output Table) should be calculated in J16.

•   Calculating the Service Level

Service level calculations are quite varied but in their simplest form they measure the proportion of time a customer will be served. So in this case Bob will want to know how often the customer demand was satisfied. The sum of lost sales divided by the total demand can be subtracted from 1 and this will give the proportion of time the customer demand was satisfied. Simply in J17 the formula should be:

=1-J16 / J15

•   Finally the formatting for this cell (J17) should be changed to show a percentage.

Using this spreadsheet

Generally a specialist package will be used for a simulation. However this spreadsheet presents a simple simulation that can be used to evaluate reorder point and order quantity policies. The model can be updated with any change to the spreadsheet or by hitting F9.

To start the simulation, you need to specify numbers for both Reorder Level (J12) and Order Quantity&nbs