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?