b. Use the MIN, MAX, and VLOOKUP functions in Excel to estimate the road repair cost function using the high-low method — city Brownswood trying understand its costs starting

The city of Brownswood is trying to understand its road repair costs. As a starting point, Mark Winston, the city’s finance manager, asked public works manager Rachel Morris to gather relevant data. Rachel provided Mark with a data set containing a list of completed pothole repair work orders and the related costs for a recent week. Mark has asked you to analyze this data set and provide some initial insights about the city’s road repair costs.

Required

a. Prepare a visualization of the data set using the CHART function in Excel to create an XY scatter graph chart. Comment on what the visualization reveals.

b. Use the MIN, MAX, and VLOOKUP functions in Excel to estimate the road repair cost function using the high-low method.

c. Use the SLOPE and INTERCEPT functions in Excel to estimate the road repair cost function using regression.

d. Based on your work so far, do you have any concerns about the data used to calculate the cost function estimates?

e. Think about factors that might affect pothole repairs and the associated costs. What additional data would you like to have available to help you prepare a better cost estimate?

The Excel data files for answering this problem can be found in WileyPLUS.

SOLUTION

a.

The dotted line is the linear estimation for pothole repair cost. It appears that as the number of potholes in a work order increases, there is more variability in the total cost to repair those potholes. This variability appears to occur around a volume of 40 potholes.