I need all there formulas EOQ on Excel first (2) -Excel Draw Page Layout Formula
ID: 413550 • Letter: I
Question
I need all there formulas EOQ on Excel first (2) -Excel Draw Page Layout Formulas DataReview View Help New Tab Tell me what you want to do File Home EOO Production Lot Size EoQ w 4 Ordering Cost s Annual Inventory Holding rate % 6 Cost per Unit Annual Demand Set-Up Cost Holding Rate Production Capacity Cost per unit Annual Demand Ordering Cost Holding Rate/Cost Back Order Cost Working Days per Year 8 Lead Time Lead Time Optimal Production Lot Size Anrvual Inw. Holding Cost Annual Set-Up Cost Total Annual Cost Maximum Inventory Level Number of days per run Idle Time between Runs Optimal Order Quantity Maximum # of Backorders Annual Holding Cost Annual Ordering Cost Annual Backordering cost Total Annual Cost #0f Orders per YR Cycle Time 70.71067812 4 Annual Inventory Holding Cost 1S Annual 16 Total Annual Cost 176.7766953 70.71067812 35.35533906 6.849315068 7.071067812 51.61879503 inventory Level 8 Average Inventory Level 0 Number of Orders per Year 21 Cycle Time Cycle Time
Explanation / Answer
1. Formula of economic order quantity (EOQ)
EOQ = Q = (2 * Annual Demand * Ordering cost/ Inventory holding cost)
Where,
Annual demand, D = 500
Ordering cost S = 25
Cost per unit P = 50
Annual Inventory holding cost rate H = 10% of cost = 10% * 50 = 5 per unit per year
Working days = 365 days per year
Lead time = 5 days
Therefore
EOQ = Q = (2 * 500 *25 / 5)
EOQ = Q = (5000) = 70.71067812
2. Annual inventory holding cost = (H*Q)/2
Where Q is economic order quantity
= (5 * 70.71067812)/2 = 176.7766953
3. Annual ordering cost = (D/Q)* S
= (500/70.71067812)*25 =176.7766953
4. Total Annual cost = Annual ordering cost + Annual inventory holding cost
= (D/Q)* S + (H*Q)/2
=176.7766953 + 176.7766953
= 353.5533906
5. Maximum inventory level = EOQ = Q =70.71067812
6. Average inventory level = Q/2 =70.71067812/2 =35.35533906
7. If demand and lead time are both constant then
The reorder point = Daily demand * lead time = (500/365) * 5 = 6.849315068
8. Number of order per year = Annual demand / Q = 500/70.71067812 = 7.071067812 orders
9. Cycle time (number of days between two orders) = No. of days per year / number of orders per year
= 365 / 7.071067812 = 51.61879503 days
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.