Problem $4[25$ points$]$: In $2012,$ Dan Li$,$ after receiving his MBA degree from IIT Stuart, returned to Aberdeen, Hong Kong where his family has extensive business holdings. Dan$$s first assignment was to stabilize and develop the newly formed, family$-$owned transport business company $$ Shelters Transport.

An immediate problem Dan faces is the determination of the number of trucks needed to handle the forecasted freight volume. Before now, trucks were added on an $$as needed$$ basis, without comprehensive capacity planning. This approach created problems in driver recruitment, truck service and maintenance, and excessive demurrage $($i$.$e$.,$ port fees$)$ because of delays in unloading docks.

Dan forecasts that Shelters$$ freight volume will average $160,000$ tons per month, with a standard deviation of $30,000$ tons. Freight is unloaded evenly throughout the $20$ business days of a month $($i$.$e$.,$ the company operates five days per week$).$ Based on past experience the amount handled per month is assumed to be normally distributed.

After extensive investigation, Dan concludes that the fleet should be standardized using $40-$foot Mercedes $26242$ X $4$ tractor$-$trailer rigs, which are each suitable for carrying two $20-$foot containers, or either one $30-$foot container or one $40-$foot container. Each tractor$-$trailer unit is estimated to cost

$$240,000.$ Historical evidence suggests that these Mercedes rigs are operational $($that is$,$ not in need of repair$)$ between $95\%$ and $100\%$ of the time, following a uniform distribution.

Approximately $75\%$ of the freight handled by these tractor$-$trailer rigs is received already containerized in $20-$foot, $30-$foot, or $40-$foot containers; the balance of the freight $(25\%)$ is not containerized. The $20-$ foot containers arrive holding approximately $20$ tons of cargo, the $30-$foot containers hold $45$ tons, and the $40-$foot containers hold $60$ tons.

Approximately, $60\%$ of the containerized freight received is shipped in $40-$foot units, $20\%$ is shipped in $30-$foot units, and $20\%$ is transported in $20-$foot units. The cargo capacity of each rig for non$-$ containerized cargo is approximately $60$ tons.

Shelters Transport picks up freight at the dock and delivers it directly to customers or to warehouses for later delivery. Based on his study of truck routing and scheduling patterns, Dan concludes that each rig should pick up freight at the dock three times each day.

$[4$ points$]$ Using Spreadsheet Engineering concepts, create a well$-$design base case analysis for this case. Assume monthly volume is $160,000,$ and tractor$-$trailer reliability is $95\%.$

i$.[4$ points$]$ What is the daily volume $($in tons$)$ for $20-$ft$,30-$ft$,40-$ft containers?

ii$.[3$ points$]$ What is the daily volume for non$-$containerized freight?

iii. $[6$ points$]$ How many containers of each type are needed?

iv$.[8$ points$]$ Assuming the rigs pick up containers three times per day, how many rigs $($total$)$ are needed per day for containerized and non$-$containerized freight?