I NEED AN EXCEL FORMAT OR THE ANSWER INN EXCEL.

Catawba Valley Highway Patrol 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 presenceletting 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 commanders most pressing objective is to limit daily operating costs to $450. The daily operating costs per road segment are as follows: Road Segment 1 2 3 4 5 6 Operating Cost $20 18 22 24 17 19 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 1 2 3 4 5 6 Physical Accident Reduction (per million mi. traveled) 0.27 0.21 0.28 0.19 0.23 0.33 Sight Contacts (per day) 18 26 10 34 25 17 Contacts (per day) 1,700 900 650 230 1,600 520 The commanders second-most-important goal is to reduce the average accident rate for the district by five accidents per million miles traveled. The commanders 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 aver-age response time in the district by the following amounts: Road Segment 1 2 3 4 5 6 Reduction in Average Response Time (min.) 0.32 0.65 0.43 0.87 0.55 0.49 The commanders 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 commanders goals. Need help with the formula's needed in the cells to get the function working in solver