Question

Eight Flags operates several amusement parks in the Midwest. The company stocks machine oil to service the machinery for the many rides at the parks. Eight Flags needs 30,000 gallons (D) of oil annually; the parks operate 50 weeks a year. Management is unsatisfied with the current supplier of oil and has obtained two bids from other suppliers. The data are contained in the following table. Annual Freight Costs Shipping Quantity (Q) Price/Unit Annual Holding Cost/Unit (H) $0.82 $0.78 Lead Time (L) Annual Administrative (wks) 5,000 $4,800 $5,000 10,000 15,000 $2,800 $3,100 (p) $4.10 Cost Supplier Sharps Winkler $3,300 $3,500 $4,000 $4,500 $3.90 Which supplier and which shipping quantity will provide the lowest costs for Eight Flags? is the lowest cost alternative, with annual total costs to Eight Flags of s. (Enter your response as an integer.) and a shipping quantity of Using Sharps Winkler

Please help! Thank you!

Answer 1

		Shipping Quantity
Sr. No.	Supplier	5,000	10,000	15,000	Price/Unit	Annual Holding Cost	Lead time	Annual Administrative Cost
1	Sharps	4800	3300	2800	4.1	0.82	3	4000
2	Winkler	5000	3500	3100	3.9	0.78	6	4500
d	30,000/50	600
Inventory Cost supplier quantity 5000				3526
Inventory Cost supplier quantity 10000				5576
Inventory Cost supplier quantity 15000				7626
Inventory Cost supplier quantity 5000				4758
Inventory Cost supplier quantity 10000				6708
Inventory Cost supplier quantity 15000				8658
Total Cost= Materials cost+Freight Costs+Inventory costs+Administrative costs
Material Cost = Price per unit * Annual Demand
Sharps
Shipping Quantity	Total Cost
5000	135326
10000	135876
15000	137426
Winkler
Shipping Quantity	Total Cost
5000	131258
10000	131708
15000	133258
From the calculation, the lowest cost is with Winkler and shipping quantity 5000

For your understanding, the screenshot of excel is attached

Shipping Quantity Annual Holding Lead 5.000 10.000 15.000 Price Unit Cost time 4800 3300 2800 4.11 0.821 5000 3500 3100 3.91 0.78 Sr. No. Supplier 1 Sharps 2 Winkler Annual Administrative Cost 31 4000 6 4500 d 30,000/50 | 600] Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 I=((C/2)+$C$6*SH$3)*SGS3 5576 7626

Sr. No. Supplier 1 Sharps 2 Winkler Shipping Quantity Annual Holding Lead 5,000! 10.000l 15.000 Price Unit Cost time 4800] 3300] 2800 4.11 0.821 5000 3500 31001 3.97 0.7876 Annual Administrative Cost 31 4000 4 5001 d 30,000/50 | 600] Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 3526 =(D22)+$C$6*SH$3)*SG$3 7626 Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 4758 6708 8658

Sr. No. Shipping Quantity Annual Holding Lead Supplier T 5,000l 10,000 15,000 Price Unit Cost time Sharpsl 48001 3300 28001 4 .11 0.82 2 Winkler 1 5000] 3500 3100] 3.91 0.78 Annual Administrativ le Cost 31 40001 6] 45001 d 30,000/50 600 Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 35261 5576 7626 Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 4758 6708 8658 Total Costo Materials cost-Freight Costs Inventory costs-Administrative costs Material Cost Price per unit * Annual Demand Sharps Shipping Quantity Total Cost 5000 =(30000*SF$3)+C3+E7-SIS3 10000 135876 15000 137426 Winkler

Sr. No. Supplier Shipping Quantity Annual Holding Lead 5,000l 10,000l 15,000 1 Price Unit ICost Itime 4800133001 28001 4.11 0.82 5000 3500] 3100 3.91 0.78 Administrativ le Cost 31 40001 6145001 Sharps 2 Winkler d 30,000/50 600 Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 I Inventory Cost supplier quantity 15000 3526 55761 7626 Inventory Cost supplier quantity 5000 Inventory Cost supplier quantity 10000 Inventory Cost supplier quantity 15000 6708 8658 Total Costo Materials cost+Freight Costs+Inventory costs Administrative costs Material Cost - Price per unit * Annual Demand Sharps Shipping Quantity Total Cost 5000 135326 10000-(3000b*SFS3)+D3+ES+SIS3 15000 137426