Selecting Transportation Modes for China Imports
Desired cycle service level	98%
Holding cost	25%
Transportation Options
Air freight		Ocean shipping
Shipping cost / kg.	$ 10	Shipping cost / container	$ 1,200
Minimum shipping quantity (kg.)	50	Container size (Maximum kg.)	15,000
Mean lead time (weeks)	1	Mean lead time (weeks)	9
SD of lead time (weeks)	0.2	SD of lead time (weeks)	3
Ordering cost per order S=	$ 500	Ordering cost per order S=	$ 1,200
Product Characteristics
Smartphones		Decorative hardware
Mean weekly demand	1,000	Mean weekly demand	5,000
SD of weekly demand	400	SD of weekly demand	1,000
Weight / unit (kg.)	0.1	Weight / unit (kg.)	1
Cost / unit	$ 300	Cost / unit	$ 20
Annual demand D=	52,000	Annual demand D=	260,000
Unit holding cost H=	$ 75	Unit holding cost H=	$ 5
Total Cost for Decorative Hardware
	Air freight	Ocean Shipping
Optimal batch size			EOQ
Order quantity per batch
Cycle inventory
Safety inventory	2,904	31,416	Given
Transit inventory
Total inventory
Annual holding cost
Annual shipping cost
Total Costs

Please solve in excel