3.2 Excel Solver Assignment
Economic Order Quantity
Global manufacturing and services require the ordering, transportation, and logistical activities to provide adequate inventories to meet customer demand. One of the programming tools in the MBA’s Management Science toolbox is the Economic Order Quantity or EOQ model. The EOQ model in an Excel-based formula template can solve for least cost, ordering costs, maximum inventory levels, reorder points, and number of reorders per year, just to name a few.
Instructions
In this assignment, you will use the Excel data analysis tool known as EOQ Solver. Read the example in Chapter 10 on The Bub Beer EOQ Inventory Model Problem.
To get started, download this Excel file Download download this Excel file. There is no need to set up anything! The Excel file has all the formulas included. You need only to plug in the different values called out below to solve this EOQ problem.
Assume the following data is used:
Annual demand = 200000
Ordering cost = 45
Annual Inventory holding rate = 25
Cost per unit = 10
Working days per year = 365
Lead-time = 7 days
Now, answer the following questions:
What is the EOQ?
What is the annual inventory holding cost?
What is the annual ordering cost?
What is the total annual cost?
What is the maximum inventory level?
What is the average inventory level?
What are the reorder points?
What is the number of reorders per year?
What is the cycle time in days?
How would adding additional reorder points change the annual cost? What implications does this have for the business?
Provide a brief narrative on your EOQ analyses, and describe the importance of the use of this tool for business and industry. Insert this narrative as a text box in the excel file