BONUS* Final Prob 5.) C-TPAT is an anti-terrorism program operated by US Customs and Border Protection (USCBP). You are manager of a small, but vitally important port in Freeport, TX. The port has seen a substantial increase in container traffic in recent years. USCBP has asked you to react to a proposed schedule for container inspection. You are particularly interested in the additional effort the C-TPAT inspections will generate. You would like to simulate an inspection schedule and associated time needed to perform the inspections for the coming year. The inspection plan will randomly select days of operation for inspection using a Bernoulli distribution with a p=0.20 for a 365 day schedule of port operation; thus, approximately 73 days will be selected. The average arrival rate per day varies very little and has an average arrival rate of 5.7 port calls per day. A port call occurs when a ship requests entry into port, and arrivals can be modeled with a poisson distribution. Each inspection requires a thorough inspection of the ships manifest and associated documents. C-TPAT has provided a range of time to perform this task-- a low of 120 minutes and a high of 300 minutes. When you ask how these times might be distributed, the C-TPA representative says that she does not believe that any time is any more likely than any other time. The actual container inspection, according to the C-TPAT representative, is normally distributed with mean of 225 minutes with a standard deviation of 35 minutes. This distribution comes from a large empirical data base of inspection times, and it can be assumed that every container inspected on a particular day will have the same time for inspection (a simplifying assumption).

1) Use the random number generation capability of the Data Analysis Tools in Excel to create a list of selected days and port calls for inspection, for a 365 day year and the related time required for each selected inspection. Finally, the number of containers to be inspected is a RV that is uniformly distributed from 9 to 23.

2) What is the average theoretical time expected for manifest and container inspection for the year given the information above?

Hint: The process should be as follows--1) determine if a day has inspection, 2) determine port calls for inspected day, 3) determine time to inspect each manifest (assume same for all port calls in a day) for inspected day, 4) determine the number of containers per port call (assume same for all port calls in a day) for inspected day, 5) determine total time for all containers for all port calls on an inspection day., and 6) add the inspection of manifest and containers for total time for the year.