Question

Transportation Configuration Question

Basket Makers international is the premier provider of home basket ball hoops and backboards.

Its factory is located in Farwest Missouri. Most orders come from individuals and small mom and pop shops.    They currently ship 11,000 hoops and backboards a year.

Today every hoop set goes separately. They ship the wheel sets LTL on Fedex and each shipment costs $100.00.

Because of competition from Amazon they are compelled to offer free shipping. Currently there is no middle man. The average hoop set sells for $750.00 and costs $450.00 to produce.

As the new manager of supply chain at baskets international you have been asked to attack the shipping costs, which are eating into their margins. You work with a 3PL to set up another option.

The 3PL will take full truck loads to their warehouses of 100 sets at a time, and ship them locally for $50.00. The full truck loads cost $5,000 each.

Altenatively, you have priced around and you can get Old Dominion to replace Fedex for $95.00 a hoop set.

Please show answer in Excel

Q1

How much is Basket Makers International currently making per year?

ANSWER :

Please show answer in Excel

Q2

How much will the 3PL option save you per year?

ANSWER :

Answer 1

Below is the screenshot of formula applied in excel to find the solution.

Below will be the desired result-

Total Units shipped	11000			Total Units shipped	11000
Selling Price	750			Selling Price	750
Total Sales Revenue	8250000			Total Sales Revenue	8250000
Unit Cost	450			Unit Cost	450
Total Unit Cost	4950000			Total Unit Cost	4950000
Unit Shipping cost	100			Number of Set in one full turck load	100
Total Shipping cost	1100000			Total Number of Full Truck Load	110
				Each Full Truck Load Cost	5000
Total Profit	2200000			Total Full Truck Load Cost	550000
				Shipping Cost of 100 sets	50
				Total Shipping cost	5500
				Total Profit	2744500
				Total Saving	544500

Below is the screenshot.