The current inventory policy for hamburger patties is to place an order for $750$ patties to be made when the inventory levels get below $200$ units. However, they would like to move to an inventory policy that would minimize their holding and ordering costs.

Project Description

Bama Burger has noticed that their inventory costs are high after they benchmarked their performance against Aubie Roadkill, a rival food truck company. Bama Burger did an ABC Analysis on the entire inventory they order. They have found that Ground Beef, Buns, and Coffee Beans account for $78\%$ of their overall Annual Dollar Volume. Therefore, they want to focus on these three products.

Their current policy has their total inventory costs around $$67,807$ for the ground beef, buns, and coffee beans. This is broken down into $$20,932$ in holding costs and $$46,875$ in ordering costs. These two costs are their only inventory costs.

Bama Burger believes there is a better way to order their inventory to minimize their annual inventory costs. They have hired you, an outside consultant, to find this better way.

The inventory holding costs and ordering costs for each item are below.

Holding costs for their Ground Beef consists of refrigeration costs of $$2\mathrm{.}30$ per oz$.$ per year, operating costs of $$0\mathrm{.}75$ per oz$.$ per year, and $$0\mathrm{.}60$ for warehousing. The ordering costs for the Ground Beef is $$20\mathrm{.}00$ per order.

Holding costs for their Buns consists of operating costs of $$0\mathrm{.}68$ per bun per year and $$0\mathrm{.}50$ for warehousing. The ordering costs for the Buns is $$4\mathrm{.}50.$

Holding costs for their Coffee Beans consists of operating costs of $$1\mathrm{.}47$ per pound of coffee per year and $$0\mathrm{.}89$ for warehousing. The ordering costs for the Coffee Beans is $$35\mathrm{.}00.$

You have the daily demand data for the past $3$ years.

Deliverables

$$Deliverables$$ Sheet $$

$1.$ Calculate the Annual Demand $($C$6,$ C$18,$ and C$30)$ for each item

$$ Must be done by having all demands added then dividing by number of years.

$2.$ Calculate the Ordering Cost $($C$7,$ C$19,$ and C$31)$ for each item

$3.$ Input the Holding Cost $($C$8,$ C$20,$ and C$32)$ for each item

$4.$ Calculate the optimal ordering quantity $($C$10,$ C$22,$ and C$34)$ for each item

$5.$ Calculate the expected number of orders $($C$11,$ C$23,$ C$35)$ per year

$6.$ Calculate the annual cost of holding $($C$12,$ C$24,$ C$36)$

$7.$ Calculate the annual cost of ordering $($C$13,$ C$25,$ C$37)$

$8.$ Calculate the annual cost of inventory $($C$14,$ C$26,$ C$38)$

$9.$ Input the original total inventory costs prior to your analysis $($F$6)$

$10.$ Calculate the new total inventory costs with what your analysis came up with $($F$7)$

$11.$ Calculate the total savings with the new inventory ordering quantities in terms of Total Inventory Cost. $($F$10)$

$12.$ Give your Observation and Recommendation in the merged cells E$19$:O$37.$