Question

In: Operations Management

In this assignment you’ll solve the basic economic order quantity (EOQ) model in Excel using two...

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

  1. Trial-and-Error Method

7

Annual

Annual

Annual

8

Order

Ordering

Holding

Ordering+Holding

9

Quantity (Q)

Costs

Costs

Costs

10

25

11

50

12

75

:

:

  1. Trial-and-Error (Approximate) Method

  • Order Quantity: type in 25 for cell A10; then use the formula =25+A10 for cell A11 and copy this formula down, until Q reaches 1200.
  • Annual Ordering Costs (DS/Q): for cell B10, use =$B$1*$B$3/A10; then copy formula down.
  • Annual Holding Costs (HQ/2): for cell C10, use =$B$4*A10/2; then copy formula down.
  • Annual Ordering + Holding Costs: for cell D10, use =B10+C10; then copy formula down.

Make a scatter (XY) chart of the Annual Ordering + Holding Costs (y-axis) vs. Order Quantity (x-axis).

  1. EOQ (Exact) Formula

  • Find the optimal order quantity Q* in cell E5 using the EOQ formula discussed in lecture and Ch. 12 of the text. You can find the square root of x in Excel with the function SQRT(x). In your E5 and F5 cell formulas you should reference other cells that contain the relevant data rather than typing the numbers themselves into the cell formula.
  1. Based on the Trial-and-Error (Approximate) Method

A1. Approximately what order quantity minimizes total annual ordering + holding costs? ____ boxes

  1. Based on the EOQ (Exact) Method

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? _______


Solutions

Expert Solution

****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****


Related Solutions

q1 The continuous review economic order quantity (EOQ) inventory model operates using two model parameters. These...
q1 The continuous review economic order quantity (EOQ) inventory model operates using two model parameters. These two parameters are: Select one: a. Q, which represents a fixed order quantity and R, which represents a variable reorder point quantity of inventory. b. Q, which represents a variable order quantity and R, which represents a fixed reorder point quantity of inventory. c. Q, which serves as the amount of inventory that triggers the release of an order and R, which is the...
Consider a basic economic order quantity (EOQ) model with the following characteristics: Item cost: $15 Item...
Consider a basic economic order quantity (EOQ) model with the following characteristics: Item cost: $15 Item selling price: $20 Monthly demand: 500 units (constant) Annual holding cost: $1.35 per unit Cost per order: $18 Order lead time: 5 working days Firm's work year: 300 days (50 weeks @ 6 days per week) Safety stock: 15% of monthly demand For this problem, determine the values of: Q* the optimal order quantity and reorder point. Select one: a. 400 and 100 b....
Which of the following is NOT an assumption of the traditional economic order quantity (EOQ) model?...
Which of the following is NOT an assumption of the traditional economic order quantity (EOQ) model? a. Holding and ordering costs are stable and known. b. Demand is constant and known. c. Supply lead time is constant and known. d. Quantity discounts are possible. Kim’s Nail Salon uses a weighted moving average method to forecast demand. She assigns a weight of 5 to the previous month’s demand, 3 to demand two months ago, and 2 to demand three months ago....
the primary purpose of the Economic Order Quantity (EOQ) model is to: A. Maximize the customer...
the primary purpose of the Economic Order Quantity (EOQ) model is to: A. Maximize the customer service level B. Calculate the reorder point, so that replenishment take place at the proper time C. Calculate the optimum safety stock level D. None of these answers
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying...
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying demand. Costs included in the model are holding cost per unit, ordering cost and the cost of goods ordered. The assumptions for that model are that only a single item is considered, that the entire quantity ordered arrives at one time, that the demand for the item is constant over time, and that no shortages are allowed. Suppose we relax the first assumption and...
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying...
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying demand. Costs included in the model are holding cost per unit, ordering cost and the cost of goods ordered. The assumptions for that model are that only a single item is considered, that the entire quantity ordered arrives at one time, that the demand for the item is constant over time, and that no shortages are allowed. Suppose we relax the first assumption and...
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying...
The Economic Order Quantity (EOQ) model is a classical model used for controlling inventory and satisfying demand. Costs included in the model are holding cost per unit, ordering cost and the cost of goods ordered. The assumptions for that model are that only a single item is considered, that the entire quantity ordered arrives at one time, that the demand for the item is constant over time, and that no shortages are allowed. Suppose we relax the first assumption and...
Distinguish between basic Economic Order Quantity (EOQ) and Quantity Discount Models. Your answer should be supported...
Distinguish between basic Economic Order Quantity (EOQ) and Quantity Discount Models. Your answer should be supported with relevant examples and calculations
The simple economic order quantity (EOQ) model shows how much to order (i.e., lot size) in...
The simple economic order quantity (EOQ) model shows how much to order (i.e., lot size) in terms of holding costs, setup (ordering) costs, and total inventory cost.  In terms of this model, why is it necessary to reduce setup cost if we desire smaller lot sizes? Draw the inventory cost curves diagram and fully explain.
1. Consider the basic EOQ model (no uncertainty). Optimal order quantity (Q ∗) is 1,800 units....
1. Consider the basic EOQ model (no uncertainty). Optimal order quantity (Q ∗) is 1,800 units. What is the average inventory level (I ∗)? a. 900 units b. 1,800 units c. 600 units d. 1,000 units e. 3,600 units 2. Consider the basic EOQ model (no uncertainty). Annual holding cost (AHC) is $2,400. Find annual ordering cost (AOC). a. Cannot be answered because there is missing information b. AOC=$0, since there is no uncertainty c. $1,200, since AOC=AHC/2 at economic...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT