I need all there formulas EOQ on Excel first 2 Excel Draw Pa

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

Solution

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