Please, solve using Excel Solver with step by step solution, excel tables, input in Solver and Explanations.

Broderick Crawford is the district commander for

the Catawba Valley highway patrol district in western

Pennsylvania. He is attempting to assign highway patrol

cars to different road segments in his district. The primary

function of the highway patrol force is to patrol roads

outside incorporated city and town limits in the district

to deter traffic violators and accidents. This objective is

typically achieved by maintaining a visible presence$$

letting motorists see patrol units on a regular basis and

giving out warnings, citations, and so forth. Secondary

activities of a patrol unit include providing assistance to

motorists, answering distress calls, handling emergencies

and accidents when called to the scene, and occasionally

apprehending criminals.

Commander Crawford has $23$ patrol cars that he wants

to assign to the following six major road segment areas:

road segment $1,$ interstate, north

road segment $2,$ urban area, north

road segment $3,$ four$-$lane highway, east

road segment $4,$ two$-$lane highway, west

road segment $5,$ interstate$/$four$-$lane highway, south

road segment $6,$ two$-$lane highway $($heavy truck traffic$),$

south Each of these road segments includes the primary

arteries, as indicated earlier, plus adjoining roads. All the

road segments have different levels of traffic density and

accident rates, which are key factors in determining how

many patrol units to assign. However, these factors do not

always coincide. For example, interstate highway segments

typically have high traffic density but low accident

rates, whereas some two$-$lane highways have low traffic

density but high accident rates. Differences often occur

because of variations in road conditions $($such as sharp

curves, visibility, and width$).$ Other conditions, such as

heavy truck traffic $($as on segment $6),$ also contribute to

high accident rates.

Each segment requires different operating costs,

including maintenance and repair, fuel, and so on because

of different operating conditions. The commander$$s most

pressing objective is to limit daily operating costs to $$450.$

The daily operating costs per road segment are as follows:

Road Segment Operating Cost

$1$ $$20$

$218$

$322$

$424$

$517$

$619$

The commander would like to reduce the accident rate

for the district as well as increase both physical and sight

contacts, which are deterrents to potential traffic violators.

The commander would also like to achieve a reasonable

average response time for a patrol unit to respond to a call

for each road segment. The average accident rate reduction

$($per million miles traveled$)$ and physical contacts and sight

contacts per car for each road segment are shown in the

following table:

Patrol Unit

Road

Segment

Physical

Accident Reduction

$($per million mi$.$

traveled$)$

Sight

Contacts

$($per day$)$

Contacts

$($per day$)$

$10\mathrm{.}27181,700$

$20\mathrm{.}2126900$

$30\mathrm{.}2810650$

$40\mathrm{.}1934230$

$50\mathrm{.}23251,600$

$60\mathrm{.}3317520$

The commander$$s second$-$most$-$important goal is to

reduce the average accident rate for the district by five

accidents per million miles traveled. The commander$$s

next goals $($in order$)$ are to achieve $350$ physical contacts

and $30,000$ sight contacts per day in the district.

If no patrol units are assigned in the district, the average

time to respond to a distress call anywhere in the district

from the main district headquarters and motor pool is

$28$ minutes. Each car assigned to a road segment reduces

the overall average response time in the district by the

following

amounts:

Road

Segment

Reduction in Average

Response Time $($min$.)$

$10\mathrm{.}32$

$20\mathrm{.}65$

$30\mathrm{.}43$

$40\mathrm{.}87$

$50\mathrm{.}55$

$60\mathrm{.}49$

The commander$$s last objective is to achieve an average

response time to distress calls of $15$ minutes. Because of

local and political pressure, the commander has to assign at

least two patrol units to each road segment. In addition, the

commander believes that a maximum of five patrol units is

sufficient for any particular road segment.

Formulate and solve a goal programming model to

determine the number of patrol units to assign to each road

segment to achieve the commander$$s goals