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

Bama Burger has provided daily consumption of their Ground Beef, Buns, and Coffee Beans.

Bama Burger has asked you to calculate how many ounces of Ground Beef they should order at a time, Buns they should order at a time, and Coffee Beans they should order at a time. Furthermore, they would like to know how many times they should plan on ordering so they can ensure they have enough space.

They would like a short paragraph that describes how much money your ordering plan will save them and why Bama Burger should deploy your plan.

Assignment

Download the $$BBB $$ Inv Student file$$ from Blackboard to your computer. Rename the file $$BamaID$\_$Inv Extra Credit$($example: jenortham$\_$Inv Extra Credit$)$ before you submit your completed assignment. Please keep it as an Excel file $(.$xlsx$)$ or $(.$xlsm$).$ Other spreadsheet file types will not be accepted.

Do not change the names on the tabs$.$

$$Documentation$$ Sheet $$ Type your name in Cell B$3$ as the Author. Type the date in B$4.$

$$Inventory Data$$ Sheet $$ You may manipulate the data as you see fit on this page. Each column holds information you may find helpful in your analysis. The following is a data dictionary for the listed information:

$$Date$$ Date of Usage

$$Day$$ Day of the week $($Monday $$ Sunday$)$

$$Open$$ Bama Burger Buggy is not open $7$ days per week. $1=$Yes$,$ we are open.$,0=$Closed$$Ground Beef Consumed $($oz$.)-$ How many ounces of ground beef were used on this day

$$Buns Consumed$-$How many buns were used for Hamburgers and Cheeseburgers on this day $$Coffee Beans Consumed$-$How many pounds were used for Coffee Beans on this day

$$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.$