205|EXCEL – Widget Wizard

Challenges
, , , , , , , ,
1

BYU Student Author: @Trent_Barlow
Reviewers: @Kyle_nilsen, @Carter_Lee
Estimated Time to Solve: 20 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.

Open the Excel template provided and follow the instructions below.

Instructions

  1. First determine the Reorder Point for each supplier. As a reminder, Reorder Point is (lead time * demand) + safety stock.
  2. 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.
    b. Order amount will be the lesser of: [the greater of the EOQ and the minimum order]; and the maximum order.
  3. 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).
  4. Next determine the number of Orders Made, and the Units Ordered:
    a. Use a roundup function to determine Orders Made. Hint: it is the annual demand divided by the order amount.
    b. Units Ordered are the Orders Made multiplied by the Order Amount.
  5. Calculate the Total Carrying, Order, and Unit Cost for each supplier. Sum these all up in the Total Cost column.
    a. Total Carrying Cost is equal to the Average Stock multiplied by Carrying Cost.
    b. Total Order Cost is equal to the Orders Made multiplied by Cost Per Order
    c. Total Unit Cost is Units Ordered multiplied by Unit Cost
    d. Sum these costs in the Total Cost column
  6. Next to Best Supplier Choice, use a MIN function within an XLOOKUP function to determine the lowest cost supplier.

Data Files

Solution

[details =“Solution”]

1. =($J$18*E3)+$J$16 
2a.  =ROUNDUP(SQRT((2*$J$19*B3)/$J$17),0)
2b. =MIN(MAX(H3,F3),D3) 
3. =((G3+J3-(E3*$J$18))+(G3-(E3*$J$18))/2)  
4a. =ROUNDUP($J$19/I3,0)
4b. =K3*I3  
5a. =J3*$J$17 
5b. =K3*B3  
5c. =L3*C3  
5d. =L3*C3  
6. =XLOOKUP(MIN(Q3:Q10),Q3:Q10,A3:A10)

[/details]
Challenge205_Solution.xlsx
Solution Video: Challenge 205|EXCEL – Widget Wizard