Please find the attached file

please send to me the word answer as will as excel sheet

Comparing B/C Analysis and CEA of Traffic Accident Reduction Background This case study compares benefit/cost analysis and cost effectiveness analysis on the same information about highway lighting and its role in accident reduction. Poor highway lighting may be one reason that proportionately more traffic accidents occur at night. Traffic accidents are categorized into six types by severity and value. For example, an accident with a fatality is valued at approximately $4 million, while an accident in which there is property damage (to the car and contents) is valued at $6000. One method by which the impact of lighting is measured compares day and night accident rates for lighted and unlighted highway sections with similar characteristics. Observed reductions in accidents seemingly caused by too low lighting can be translated into either monetary estimates of the benefits B of lighting or used as the effectiveness measure E of lighting. Information Freeway accident data were collected in a 5-year study. The property damage category is commonly the largest based on the accident rate. The number of accidents recorded on a section of highway is presented here Accident Type Property damage Number of Accident Recorded Unlighted Lighted Day Night Day Night 379 199 2069 839 The ratios of night to day accidents involving property damage for the unlighted and lighted freeway sections are 199/379 = 0.525 and 839/2069 = 0.406, respectively. These results indicate that the lighting was beneficial. To quantify the benefit, the accident rate ratio from the unlighted section will be applied to the lighted section. This will yield the number of accidents that were prevented. Thus, there would have been (2069)(0.525) = 1086 accidents instead of 839 if there had not been lights on the freeway. This is a difference of 247 accidents. At a cost of $6000 per accident, this results in a net annual benefit of B = (247)($6000) = $1,482,000 For an effectiveness measure of number of accidents prevented, this results in E = 247. To determine the cost of the lighting, it will be assumed that the light poles are center poles 67 meters apart with 2 bulbs each. The bulb size is 400 watts, and the installation cost is $3500 per pole. Since these data were collected over 87.8 kilometers of lighted freeway, the installed cost of the lighting is (with number of poles rounded off): Installation cost = $3500 (87.8 / 0.067) = 3500 (1310) = $4,585,000 There are a total of 87.8/0.067_1310 poles, and electricity costs $0.10 per kWh. Therefore, the annual power cost is Annual power cost = 1310 poles (2 bulbs/pole)(0.4 kilowatt/bulb) x (12 hours/day)(365 days/year) x ($0.10/kilowatt-hour) = $459,024 per year The data were collected over a 5-year period. Therefore, the annualized cost C at i = 6% per year is Total annual cost = $4,585,000( A/P ,6%,5) + 459,024 = $1,547,503 If a benefit/cost analysis is the basis for a decision on additional lighting, the B/C ratio is B/C = 1,482,000 / 1,547,503 = 0.96 Since B/C