In: Operations Management
In this assignment you’ll solve the basic economic order quantity (EOQ) model in Excel using two methods: (A) a trial-and-error approach to find a very good but approximate answer, and (B) the EOQ formula to find the “exact” answer. Set up your spreadsheet similar to that shown below.
The situation: Roxie is responsible for purchasing the paper used in all copiers and laser printers at Budco. After looking at her records, Roxie has found that demand for paper averages 600 boxes per month. The price of a box of paper is $20 (regardless of the number ordered). Placing and handling an order costs $66. Annual unit holding costs per box are 25% of the unit price. Last year, Roxie ordered paper once every two months, but she wants to know if another ordering policy would be cheaper.
A |
B |
C |
D |
E |
F |
|
1 |
Annual Demand |
boxes/year |
B. EOQ Formula |
|||
2 |
Unit Price |
per box |
Optimal |
Annual |
||
3 |
Ordering Cost |
per order |
Order |
Order+Holding |
||
4 |
Annual Holding Cost |
per box/year |
Quantity Q* |
Costs at Q* |
||
5 |
||||||
6 |
|
|||||
7 |
Annual |
Annual |
Annual |
|||
8 |
Order |
Ordering |
Holding |
Ordering+Holding |
||
9 |
Quantity (Q) |
Costs |
Costs |
Costs |
||
10 |
25 |
|||||
11 |
50 |
|||||
12 |
75 |
|||||
: |
: |
Make a scatter (XY) chart of the Annual Ordering + Holding Costs (y-axis) vs. Order Quantity (x-axis).
A1. Approximately what order quantity minimizes total annual ordering + holding costs? ____ boxes
B1. What order quantity Q* minimizes total annual order + holding costs? _____ boxes
B2. What Excel cell formula is required for Annual Ordering + Holding Costs in cell F5? _____
B3. At Q*, what are the total annual ordering + holding costs? _____
B4. How many times per year will Roxie place an order of size Q*? _____
B5. Last year, Roxie ordered every 2 months. How much were her total annual ordering+holding costs?
_____________ (Hint: First ask yourself how many boxes would be ordered each time if you ordered exactly once every 2 months?)
B6. What is the percentage reduction in annual ordering + holding costs achieved by Roxie in following the optimalinventory policy instead of last year’s ordering policy? _______
****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please****
(A)Trial-and-Error method
1. As per current ordering policy, order quantity is equal to two months demand, Q = 600*2 = 1200 boxes
Annual demand, D = 600*12 = 7200 boxes
Setup cost, S = 70
Holding cost, H = 20*25% = 5
Total annual Cost = (D/Q)*S + (Q/2)*H = (7200/1200)*70+(1200/2)*5 = 420+3000 = $ 3420
At this order quantity, holding cost is substantially higher. So decreasing the order quantity might result in cost reduction
Trial #1: Q = 600, Total annual cost = (7200/600)*70+(600/2)*5 = 840+1500 = $ 2340
Trial #2: Q = 400, Total annual cost = (7200/400)*70+(400/2)*5 = 1260+1000 = $ 2260
Order quantity of 400 yields ordering cost and holding cost very close. So this is a fairly good estimate of lot size to use.
B)
2. Using EOQ formula, Optimal Order quantity, Q* = SQRT(2DS/H) = SQRT(2*7200*70/5) = 449 boxes
3. If cell F5 calculates total annual cost, then formula is =(B1/E5)*B3+(E5/2)*B4
4. At Q*, total annual ordering +holding cost =(7200/449)*70+(449/2)*5 = $ 2245
5. Number of times she will place order of size Q* per year = D/Q = 7200/449 = 16
6. Last year total annual ordering +holding cost = (7200/1200)*70+(1200/2)*5 = $ 3420
7. Percentage reduction in annual ordering+holding cost by following optimal ordering policy = (3420-2245)/3420 = 34.36 %
****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please****