The Economic Order Quantity (EOQ) model is a classical model used for controllin
ID: 2794230 • Letter: T
Question
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: only a single item is considered; the entire quantity ordered arrives at one time; the demand for the item is constant over time; no shortages are allowed.
Suppose we relax the first assumption and allow for multiple items which are independent except for a budget restriction. The following model describes this situation:
The decision variables are Qj, the amount of item j to order. The model is:
In the objective function, the first term is the annual cost of goods, the second is the annual ordering cost (Dj/Qj is the number of orders) and the last term is the annual inventory holding cost (Qj/2 is the average amount of inventory).
Set up a spreadsheet model for the following data:
B = $24,000
i = 0.2
Solve the problem using Excel Solver. Hint: You will need to start with decision variable values that are greater than 0 for Solver to find a solution.
If required, round your answers to two decimal places.
Optimal Solution:
Q1 =
Q2 =
Q3 =
If required, round your answer to the nearest dollar.
Total cost = $
Let Dj = annual demand for item j Cj = unit cost of item j Sj = cost per order placed for item j i = inventory carrying charge as a percentage of the cost per unit B = the maximum amount of investment in goods N = number of items ndlap-Cengage Learning-Microsoft Edge g cengage htmnbld 623622 5573052788756520501257600elSBN-978130586 1770 8parentid The Economic Onder Quantity (E0Q) model is a dlemicalel ed fo the objective furnton, the first berm the msa, cost of good, cove is the ouabe dnes) and the lat tre iste amani-try at Osas ness man onnern second is "na" ord ul Dem.and2,5002,580 1,000 9 943 50 si 70 6130 #115 $24,000 ve the problor using Excel Selver Hit: You will need to start tD @ew Search the web and WindowsExplanation / Answer
Below is an excel model for solving multiple eoq problem.
Final answers:
Q1 = 131
Q2 = 162
Q3 = 82
Total Cost = $406,552
(In case if you don't know how to use excel solver, then please watch any youtibe video regardind the same. Search for adding and using Excel solver).
Ordering Quantity is used as the decision variables while Constraint sum is for entering the constraint. Constraints are budget <= 24,000 and Q1, Q2 and Q3 are integers.
PS: This is a theoretical setup and hence decimal places are allowed in the number of orders. But in practical world you can ship products entirely and hence the decimals for either the Orderng quantity or number of orders will not be allowed.
B 24000 Inv. Carr. Chrg (i) 0.2 Item 1 Item 2 Item 3 Annual Demand(D) 2500 2500 1000 Item Cost ( C ) 90 45 60 Order Cost (S) 170 130 115 Ordering quantity (Q) 131 162 82 No. of orders(S/Q) 19.08397 15.4321 12.19512 Tot Cost of goods (C/D) 225000 112500 60000 Tot Ordering cost (Q*S) 3244.275 2006.173 1402.439 Inventory cost (i*C*Q/2) 1179 729 492 Sum (in $) 229423.3 115235.2 61894.44 406552.9 Constraint(C*Q) 11790 7290 4920 24000Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.