Bama Burger Buggy Inventory Analysis Case

Assignment

Download the “BBB – Inv Student file” from Blackboard to your computer. Rename the file “BamaID_Inv Extra Credit” (example: nechilcutt_Inv Extra Credit) before you submit your completed assignment. Please keep it as an Excel file (.xlsx). Other spreadsheet file types will not be accepted.

Do not change the names on the tabs.

“Documentation” Sheet – Type your name in Cell B3 as the Author. Type the date in B4.

“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 –

Calculate the Annual Demand (C6, C18, and C30) for each item

Calculate the Ordering Cost (C7, C19, and C31) for each item

Input the Holding Cost (C8, C20, and C32) for each item

Calculate the optimal ordering quantity (C10, C22, and C34) for each item

Calculate the expected number of orders (C11, C23, C35) per year

Calculate the annual cost of holding (C12, C24, C36)

Calculate the annual cost of ordering (C13, C25, C37)

Calculate the annual cost of inventory (C14, C26, C38)

Input the original total inventory costs prior to your analysis (F6)

Calculate the new total inventory costs with what your analysis came up with (F7)

Calculate the total savings with the new inventory ordering quantities in terms of Total Inventory Cost. (F10)

Give your Observation and Recommendation in the merged cells E19:O37.