An electronics manufacturer located in Charlottesville, Virginia, has requested a recommended course of action transporting a toxic chemical waste from Charlottesville to an appropriate chemical waste treatment facility. The company will generate about 10 tons of waste per week. Federal regulations require that the material be stored no longer than 7 days at the Charlottesville site.

There are five facilities capable of handling this type of waste. One facility is located in Magnolia, Arkansas; one is in Trenton, New Jersey; one facility is located in Hornell, New York; one is in Macon, Georgia; and the last is in Terre Haute, Indiana. You are assigned (i) to develop a model that can be used to estimate the risks involved in shipping to the five facilities, and (ii) to make recommendations regarding which treatment facility to employ. Unfortunately, the cost analyst is away from the office for vacation and you have been assigned to estimate the costs of transportation. Preliminary recommendations are requested. Note: Use the following accident data?no need to collect additional accident data; main data needs will be map-type data.

Accident Data The following table lists the accident rates for some states in the eastern United States. The numbers give the accident rates for trucks carrying hazardous material in accidents per 10,000,000 miles traveled.

State Accident Rates NY 70 PA 95 NJ 30 DE 44 MD 38 WV 64 VA 36 NC 46 TN 32 KY 52 SC 58 GA 40 AL 52 MO 64 AR 30 LA 50 IN 60 OH 55

These numbers can be used as a baseline rate. You may wish to use the following adjustment factors to consider how the accident rate changes depending on the type of road:

(Rate as percentage of baseline rate)

Rural interstate: 0.85 Rural highway: 0.95 Urban highways/interstate: 1.75 Small town/city roads (

No Spillage: The material was not released into the environment. The required emergency response to secure the accident scene can be expected to cost $90,000.

Minor Spill: Some material is released into the environment. Because of the very toxic nature of the material, 0.015% of the people within a 5-mile radius will be killed and another 5% injured and requiring hospitalization. Cleanup is expected to cost $950,000.

Major Spill: All material is released into the environment. 0.1% of the people within a 10-mile radius will be killed and another 20% injured. Cleanup is expected to cost $8.5 million.

The probabilities of the three occurrences given an accident are as follows:

No spill: P?=?0.74 Minor spill: P?=?0.25 Major spill: P?=?0.01 Cost Assessment: Trucking the material will cost $1.75/ton-mile. One truck can handle the 10-ton weekly amount.

Processing and disposal costs will be $475/ton in New York, $400/ton in Arkansas, $350/ton in Georgia, $250/ton in New Jersey, and $300/ton in Indiana.

QUESTION: How do I calculate Miles, State Accident Rate (No Adjustment), State Accident Rate (Rural Interstate), State Accident Rate (Rural Highway), State Accident Rate (Urban Highways), and State Accident Rate (Small Town) in Excel? I only want to understand the Excel formula and logic behind it. Thank you!

State Accident Rate State Accident Rate State Accident Rate State Accident Rate State Accident Rate Miles (No Adjustment) (Rural Interstate) (Rural Highway) (Urban Highways) (Small Town) Charlottesville, VA Virginia Maryland Pennsylvania New Jersey Total Miles Trucking cost per ton mile $1.75 Disposal fee per ton $250.00 Tons of material 10