Question

Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units.

The manager of QuikStop (open 365 days a year) sells 40 cases of water each day. Order costs are $30 per order, and water costs $0.10 per bottle (each case of water contains 24 bottles). Daily holding costs are equal to 3 percent of the cost of the water. What is the economic order quantity for water?

Answer 1

Solution : Economic order Quantity refers to the quantity of a commodity that should be ordered at a time so that the total cost of the annual inventory policy is the minimum . EOQ can be found using the formula :

EOQ = , where

D= Annual Demand or Consumption of bottles

S= Setup or Ordering cost per order

H = Holding cost per bottle per year

Now, using the information given in the question,

D = 40 (cases)* 24(bottles per case)*365 (working days in a year) = 350400 bottles

S = 30$ per order

H = 0.10 * 3% * 365 = 1.095 $ per bottle per annum ( Daily cost is 3% of purchase price and hence , multiplied by 365 to find per annum holding cost per bottle)

The calculation of EOQ in excel sheet using the above formula is shown as follows :