Palm City Police Department Goal Seeking

The Palm City Police Department has eight defined precincts. The police station in each precinct has primary responsibility for all activities in its precinct area. The table lists the current population of each precinct, the number of violent crimes committed in each precinct, and the number of officers assigned to each precinct. The department has established a goal of equalizing access to police services. Ratios of population per police officer and violent crimes per police officer should be calculated for each precinct. These ratios for the city as a whole are shown at right.

a. Build a spreadsheet to perform this analysis and print it out.

b. Currently, no funds are available to hire additional officers. On the basis of the citywide ratios, the department has decided to develop a plan to shift resources as needed to ensure that no precinct has more than 1,100 residents per police officer and no precinct has more than seven violent crimes per police officer. The department will transfer officers from precincts that easily meet these goals to precincts that violate one or both of these ratios. Use "goal seeking" on your spreadsheet to move police officers between precincts until the goals are met. You can use the goal-seek tool to see how many officers would be required to bring each precinct into compliance and then judgmentally reduce officers in precincts that are substantially within the criteria. Print out a set of results that allow the departments to comply with these ratios and a memorandum to your instructor summarizing your results and the process you used to develop them.