Top of Form
Bottom of Form
WINTER 2007 MGTSC 352 LEC B1DOCUMENTSLABS > LAB 8 - INVENTORY MANAGEMENT (A&E NOISE)Lab 8 - Inventory Management (A&E Noise)
/ Course DocumentsLab8_AENoise (1.33 Mb)
Lab 8 - Inventory Management (AENoise)
March 22, 2007
Agenda
- Simulated Inventory Model
- 2-Way Data Tables
- EOQ
- EOQ and Fill Rates
- Extra Practice Problem
- Demand Data Analysis
1. Simulated Inventory Model
Remember the inventory holding cost that was a part of Mountain Wear? What are those costs, how are we able to minimize them and what are the optimal levels of inventory???
A&E Noise is an electronics wholesaler. In this lab we will be determining an inventory management plan to help them minimize their inventory holding costs.
The Daily Demand worksheet is there for you to practice your data analysis skills on the demand data provided. We will leave this for you to work through on your own. Please see the end of the lab for the steps to recreate the completed tab.
Beginning Inventory
Beginning inventory is equal to the ending inventory of the previous period plus the amount ordered 5 days ago, since we have a 5 day lead time. In cell C13:
Inventory position
Inventory position is equal to the beginning inventory plus any inventory in transit. In cell D13:
Order
If the inventory position is less than or equal to the reorder point then it is necessary to place an order.
It should be noted that we have named some of the cells in this workbook. Named cells do not need to have absolute referencing applied to them. The name is enough to fix the cell in a formula when it is propagated. If you want to learn how to name cells check the help menu or ask someone during office hours.
Sales
If demand is less than or equal to beginning inventory than sales are equal to demand. If demand is greater than beginning inventory than sales are equal to beginning inventory. We can use the MIN function to chose the correct value.
This screen shot shows you how to accomplish the same result using an IF statement. Feel free to use whichever method you are most comfortable with.
Shortage
Shortage is equal to demand less sales.
Ending inventory
Ending inventory is beginning inventory less any sales.
Net revenue
Net revenue is equal to sales times net revenue per unit.
Order cost
Order cost is equal to $30 if an order is placed. Therefore if the amount ordered is greater than 0 than the order cost is $30 if not it is 0. This is calculated using an IF statement to determine if an order was placed.
Holding Cost
Holding cost is the average of the beginning and the end inventory, times the cost of holding an item per day.
Total Net Revenue and Total Cost
We calculate these values as the sum of each of the corresponding columns of daily information.
Net profit
Net profit is equal to the net revenue less order cost and holding cost.
Fill rate
Lastly we will calculate the fill rate which is the amount of demand that is being satisfied by inventory. This can be calculated by dividing the total sales by the total demand.
What would happen if we changed the lead time to 8 days??? Attempt to adjust the model for this. Try this one on your own. A completed worksheet has been included for you.
2. Constructing a 2-Way Data Table
Consider ROP and Q and assume that ROP can vary between 50 and 90 days and that Q can vary between 60 and 100 units. We would like to "see" how the Net Profit behaves as a function of both ROP and Q.
Enter the values that ROP and Q can take (use an increment of 10 to start). In the top left corner, refer to the Net Profit cell. Highlight the entire table and select Table in the Data menu. We arranged ROP in column format and Q in row format; thus, set "row input cell" to cell B4 and set "column input cell" to cell B3. Once you click "OK" the function computes the net profit using the different values of ROP and Q. Calculate the maximum profit to the right and use conditional formatting to easily identify this cell in the table.
We can build the same table to analyze the fill rate. Instead of profit as the corner cell of your table, reference the Fill Rate. You can reference the blue cells to the table you just created. This way if you want to perform any sensitivity analysis you only need to change the values in one table and both will adjust.
Finally, an important thing to remember is that once the table is set up, the values of the parameters can be changed manually and the results of the table will change automatically. This, of course, assuming that the values of the parameters that are changed are within the cell range of the table. Your calculation settings may also prevent it from automatically updating so quickly press F9 and all should be well again!
3. EOQ
When using the EOQ model it is important that before you start calculations you ensure that you are looking at similar time frames for each input. If they are not the same, make sure you make the necessary adjustments to reflext this accurately. If the costs are shown annually, but demand is daily you will need to make some changes before you begin the calculations. For this lab there are no adjustments needed.
In the EOQ template enter Order Quantities from 10 to 200 in increments of 10 in Column A. Then compute the Ordering Costs, Carrying Costs, and Total Costs as shown below.
Total Order Cost = (demand/order quantity)*order cost
Total Carrying Cost = (order quantity/2)*holding cost
Total Inventory Cost = ordering cost + carrying cost
Now let's calculate the minimum in the Total Cost column to find out what our optimal order quantity should be. We can use conditional formatting to find that the optimal Q is 80.
We could now expand the quantities in smaller increments to see if we can find a better Q, just like with a data table. Or, we can use the MAGIC formula for finding the optimal Q in one step.
Q* = sqrt(2DS/H) = 76.8895
4. EOQ and Fill Rates
The demand during leadtime and shortage per cycle have already been calculated for you in columns D and E. You are not responsible for knowing the formulas in these cells, however, you are responsible to know how those values are derived.
Basic stats are done on the demand during leadtime the same way basic stats were done on the original data set.
Next, we calculate the average shortage per cycle by simply taking the average of all our shortages.
Demand/Year is the sum of all the demand from our daily demand sheet.
Cycles/Year is the number of times we place an order in a year. A complete cycle is the entire time between one order and the next. To calculate we take our total demand and divide it by our order size to get the number of cycles per year.
Shortage multiplies our total number of cycles per year by the average shortage per cycle.
Fill Rate is the percentage of demand that we are able to satisfy. So we take our total demand, less the shortages and divide that by our total demand to get the percentage of demand satisfied.
As we did with simulation, you can play around using a 2-way and 1-way data table to determine the effects on the fill rate.
5. Extra Practice Problem
A tour operator in Alberta employs a large number of tour guides for its tours accross the province. Every month about five guides resign to pursue other careers. To have a steady supply of tour guides, the operator recruits university graduates and puts them through a month-long course in the fine art of tour guiding. The course is custom-designed and offered by a community college at a cost of $9,400 per course. The maximum enrolment is 30 due to the hands-on nature of the course. The tour operator recruits five new candidates every month, and puts them through the course together. At the end of the month, the new guides receive their certificate and are sent to work for the company, as soon as there is an opening (due to a resignation).
A newly hired B.Com. has suggested that larger class size would reduce the cost of educating the new guides. The manager of operations points out that the contract requires the company to pay a monthly salary of $2,000 to each guide after the completion of the course regardless of whether they work or not, and does not think the company can save money by paying its guides for not working. The B.Com. insists that there is nothing wrong with paying people for no work, and goes back to her office to produce some evidence. What is the optimal class size?
Hint: Use the EOQ model.
The tricky part here is to see what corresponds to what:
Inventory: Tour guides that have finished training but are not working yet
Order: training course
Order size: class size
Order cost: cost of offering training course
Holding cost: wages of tour guides before they start working for the company
Conclusion:
Putting 7 people through the course once every 12/8.6 = 1.4 months would lower total cost by over $8,000 / year.
Note that we could have also found the optimal class size with the EOQ formula:
Q* = sqrt(2*60*9,4000/24,000) = 6.9, which we would round to 7.
6. Demand Data Analysis
If you have any questions about the steps in this section please use the discussion board or come to lab/office hours for assistance. This section is only included to help you build up your data analysis skills.
We would like to create a histogram of the daily sales data.
The summary statistics are using calculations discussed earlier in the course.
Average: Take an average of all daily sales information with =average(Demandsample)
Standard Deviation: =stdev(Demandsample)
Min: =min(DemandSample)
Max: =max(DemandSample)
Frequency Graph (bar graph)
The first thing to do is to set up your 'bins'. The bin values specify what the benchmark values are that you wish to count up to. Excel will count all occuances that a value is between the previous bin value and up to and including the value specified. For example we first want to calculate all values from 0 - 2 including 2 so our bin value starts with 2. We then would like to calculate the number of times we have sales between 3 and 4 inclusive so we put a bin value of 4. The first incident will count the number of days there are 0 - 2 sales (inclusive) and the second will count from anything >2 to 4 up to and including 4 which happens to be 12 times in this case.
In column E insert the greatest value for each range you would like to calculate the frequency for. You will have 2 - 20 in increments of 2.
Before typing in the formula in the first cell (F10), you have to have the entire selection highlighted of where you would like the frequency data to be starting with the top cell as shown below:
Without clicking into the first cell of the highlighted range, type =frequency(DemandSample,E10:E19) BEFORE pressing enter, you MUST hold down ctl + shift and then press enter so as to fill the formula down.
Using the chart wizard, insert a column graph. To get the values of the bins on the x-axis while in step 2 of 4 under the series tab there is an input box specifying the category (x) axis labels which is where you can input the array. Make sure to make all necessary changes such as including a title, and labelling of both axes.