BYU Student Author: @Trent_Barlow @Michael_Barney
Reviewers: @Saul_Esplin, @Jason_Nguyen @Kyle_Nilsen @Carter_Lee
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
You work as an analyst in the Purchasing Department of a large tractor manufacturer called Scott-Elk. You oversee selecting and maintaining relationships with multiple different suppliers of mechanical parts needed for manufacturing the tractors. Recently, a long-time supplier, Acme widgets, of a critical component communicated to you that they would be unable to continue producing the same capacity as before for you, leaving you with an annual shortfall of 7,200 widgets! Your firm practices Just-In-Time inventory and widgets are used daily. You choose to see this as an opportunity to strengthen your supply chain through diversification. You have rounded up a handful of potential suppliers who will have the supply to meet your needs (with a little extra baked in, just in case). You are given order cost, unit cost, max order, lead time in days, and minimum order for each supplier and must calculate the annual cost of doing business with each one to determine which one makes the most economic sense. For your firm, you are given safety stock, annual carry cost per unit, daily demand, and annual demand.
Load the Excel template provided into a Pandas data frame and follow the instructions below.
Instructions
- First determine the Reorder Point for each supplier. As a reminder, Reorder Point is (lead time * demand) + safety stock.
df[“Reorder Point”] = (df[“Lead Time”] * daily_demand) + safety_stock
- Next determine the economic order quantity and order amount.
a. Economic order quantity (EOQ) is equal to the square root of ((2demandcost per order)/carrying cost). Hint: use a function to round up.
df[“EOQ”] = np.ceil(np.sqrt((2 * annual_demand * df[“Cost Per Order”]) / annual_carrying_cost))
b. Order amount will be the lesser of: [the greater of the EOQ and the minimum order]; and the maximum order.
df[“Order Amount”] = df[[“EOQ”, “Min Order”]].max(axis=1).clip(upper=df[“Max Order”])
- Determine the Average Stock we will be holding. This is found by taking the average of the maximum and minimum stock we would be holding. Hint: maximum will be the order amount + safety stock – (daily demandlead time). The minimum will be the reorder point– (daily demandlead time).
df[“Max Stock”] = df[“Order Amount”] + safety_stock - (daily_demand * df[“Lead Time”])
df[“Min Stock”] = df[“Reorder Point”] - (daily_demand * df[“Lead Time”])
df[“Average Stock”] = (df[“Max Stock”] + df[“Min Stock”]) / 2
- Next determine the number of Orders Made, and the Units Ordered:
a. Manipulate the datafram to determine Orders Made. Hint: it is the annual demand divided by the order amount; the “ceil” method found in the numpy library can be helpful to round up to a whole number.
df[“Orders Made”] = np.ceil(annual_demand / df[“Order Amount”])
b. Units Ordered are the Orders Made multiplied by the Order Amount.
df[“Units Ordered”] = df[“Orders Made”] * df[“Order Amount”]
- Calculate the Total Carrying, Order, and Unit Cost for each supplier. Put the sum into the Total Cost column.
a. Total Carrying Cost is equal to the Average Stock multiplied by Carrying Cost.
df[“Total Carrying Cost”] = df[“Average Stock”] * annual_carrying_cost
b. Total Order Cost is equal to the Orders Made multiplied by Cost Per Order
df[“Total Order Cost”] = df[“Orders Made”] * df[“Cost Per Order”]
c. Total Unit Cost is Units Ordered multiplied by Unit Cost
df[“Total Unit Cost”] = df[“Units Ordered”] * df[“Cost Per Unit”]
d. Sum these costs in the Total Cost column
df[“Total Cost”] = df[“Total Carrying Cost”] + df[“Total Order Cost”] + df[“Total Unit Cost”]
- Next to Best Supplier Choice, use the “loc” method in pandas to determine the lowest cost supplier.
best_supplier = df.loc[df[“Total Cost”].idxmin(), “Supplier”]
Data Files
Solution
Challenge205_Solution.ipynb
Solution Video: Challenge 205|EXCEL – Widget Wizard