there is an excel sheet but it wont let me upload it i would be happy to email it some how
D 1 2 3 TTT 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 A B Repair Date Number of Potholes Filled Final cost February 24 22 $ 2,291.00 February 25 2 $ 286.00 February 25 2 $ 283.00 February 25 2 $ 691.00 February 25 2 $ 181.00 February 25 2 $ 254.00 February 25 2 $ 241.00 February 25 4 $ 299.00 February 25 4 $ 424.00 February 25 4 $ 865.00 February 25 4 $ 422.00 February 25 4 $ 497.00 February 25 5 $ 650.00 February 25 5 $ 910.00 February 25 5 $ 552.00 February 25 7 $ 842.00 February 25 7 $ 1,026.00 February 25 7 $ 626.00 February 25 7 $ 690.00 February 25 7 $ 514.00 February 25 8 $ 733.00 February 25 9 $ 941.00 February 25 9 $ 785.00 February 25 9 $ 942.00 February 25 10 $ 1,109.00 February 25 10 $ 1,182.00 February 25 10 $ 1,192.00 February 25 11 $ 1,067.00 February 25 11 $ 1,400.00 February 25 11 $ 1,381.00 February 25 12 $ 1,297.00 February 25 12 $ 1,147.00 February 25 12 $ 1,274.00 February 25 12 $ 1,302.00 February 25 13 $ 1,277.00 February 25 13 $ 1,248.00 February 25 13 $ 1,515.00 February 25 14 $ 1,413.00 February 25 15 $ 1,455.00 February 25 15 $ 1,638.00 February 25 16 $ 1,721.00 February 25 16 $ 1,639.00 February 25 16 $ 1,656.00 February 25 17 $ 1,855.00 February 25 17 $ 1,751.00 February 25 17 $ 1,796.00 February 25 17 $ 1,918.00 February 25 17 $ 1,670.00 February 25 18 $ 1,662.00 February 25 18 $ 1,841.00 February 25 18 $ 1,713.00 February 25 18 $ 1,907.00 February 25 19 $ 2,071.00 February 25 20 $ 2,065.00 February 25 20 $ 2,114.00 February 25 20 $ 2,017.00 February 25 20 $ 1,844.00 February 25 21 $ 2,096.00 February 25 21 $ 2,206.00 February 25 22 $ 2,088.00 February 25 23 $ 2,159.00 February 25 23 $ 2,130.00 February 25 23 $ 2.259.00 February 25 23 $ 2,414.00 February 25 23 $ 2,324.00 February 25 24 $ 2,212.00 February 25 25 $ 2,492.00 February 25 27 $ 2.703.00 69 February 25 70 February 25 71 February 25 72 February 25 73 February 25 74 February 25 75 February 25 76 February 25 77 February 25 78 February 25 79 February 25 80 February 25 81 February 25 82 February 25 83 February 25 84 February 25 85 February 25 86 February 25 87 February 25 88 February 25 89 February 25 90 February 25 91 February 25 92 February 25 93 February 25 94 February 25 95 February 25 96 February 25 97 February 25 98 February 25 99 February 26 100 February 26 101 February 26 102 February 26 103 February 26 104 February 26 105 February 26 106 February 26 107 February 26 108 February 26 109 February 26 110 February 26 111 February 26 112 February 26 113 February 26 114 February 26 115 February 26 116 February 26 117 February 26 118 February 26 119 February 26 120 February 26 121 February 26 122 February 26 123 February 26 124 February 26 125 February 26 126 February 26 127 February 26 129 February 26 129 February 26 130 February 26 131 February 26 132 February 26 133 February 26 134 February 26 135 February 26 136 February 26 137 February 26 138 February 26 139 February 26 140 February 26 141 February 26 142 February 26 143 February 26 144 February 26 145 February 26 146 February 26 147 February 26 148 February 26 149 February 26 150 February 26 151 February 26 152 February 26 153 February 27 154 February 27 155 February 27 156 February 27 157 February 27 158 February 27 27 $ 2,703.00 27 $ 2,424.00 27 $ 2,702.00 30 $ 2,955.00 30 S 2,975.00 30 $ 2,773.00 31 S 2,993.00 32 S 2,945.00 32 $ 3,115.00 33 S 3,271.00 33 $ 3,269.00 35 S 3,261.00 36 S 2,508.00 36 s 3,503.00 37 $ 3,458.00 38 $ 3,570.00 44 S 4,224.00 45 $ 3,237.00 47 $ 4,202.00 47 $ 4,172.00 49 $ 3,388.00 52 S 4,564.00 69 S 7,152.00 78 $ 6,953.00 83 $ 7,314.00 110 $ 9,761.00 110 $ 8,479.00 117 S 10,302.00 121 S 12,565.00 157 S 14,519.00 2 $ 541.00 3 s 384.00 3 $ 517.00 3 $ 397.00 3 $ 396.00 6 $ 601.00 7 S 815.00 8 S 947.00 9 $ 840.00 10 $ 992.00 10 S 943.00 10 $ 955.00 11 S 1,389.00 12 s 1,240.00 12 S 1,353.00 12 s 1,151.00 14 $ 1,540.00 15 $ 1,626.00 15 $ 1,771.00 15 $ 1,700.00 16 $ 1,580.00 16 S 1,723.00 17 $ 1,709.00 17 $ 1,806.00 20 S 1,912.00 20 S 1,984.00 21 S 2,062.00 21 S 1.980.00 21 S 2,146.00 21 $ 2,044.00 22 $ 2,038.00 22 $ 2,202.00 24 $ 2,250.00 25 $ 2,385.00 26 S 2,348.00 27 $ 2,685.00 28 $ 2,863.00 29 $ 2,720.00 30 S 2,798.00 31 $ 3,025.00 31 $ 2,863.00 33 S 3,286.00 34 $ 3,058.00 35 $ 3,157.00 36 $ 3,453.00 38 $ 3,449.00 39 $ 3,745.00 42 S 3,961.00 43 $ 5,047.00 52 $ 4,545.00 56 $ 6,136.00 56 $ 5,030.00 80 $ 8,166.00 119 S 9,612.00 3 $ 379.00 13 $ 1,531.00 14 S 1,453.00 15 $ 1,517.00 30 $ 2,951.00 31 $ 3,133.00 158 February 27 159 February 27 160 February 28 161 February 28 162 February 28 163 February 28 164 February 28 165 February 29 166 February 29 167 February 29 168 February 29 169 February 29 170 February 29 171 February 29 172 February 29 173 February 29 174 February 29 175 February 29 176 February 29 177 February 29 178 February 29 179 February 29 180 February 29 181 February 29 182 February 29 183 February 29 184 February 29 185 February 29 186 February 29 187 February 29 18.8 February 29 189 February 29 190 February 29 191 February 29 192 February 29 193 February 29 194 February 29 195 February 29 196 February 29 197 February 29 198 February 29 199 February 29 200 February 29 201 February 29 202 February 29 203 February 29 204 February 29 205 February 29 206 February 29 207 February 29 208 February 29 209 February 29 210 February 29 211 February 29 212 February 29 213 February 29 214 February 29 215 February 29 216 February 29 217 February 29 218 February 29 219 February 29 220 February 29 221 February 29 222 February 29 223 February 29 224 February 29 225 February 29 226 February 29 227 February 29 228 February 29 229 February 29 230 February 29 231 February 29 232 February 29 233 February 29 234 February 29 235 February 29 236 February 29 237 February 29 238 February 29 239 February 29 240 February 29 241 February 29 242 February 29 243 February 29 244 February 29 245 February 29 246 February 29 247 February 29 31 S 36 $ 2 S 8 S 15 $ 32 $ 39 $ 1 S 2 S 2 s 2 $ 2 S 2 S 2 $ 2 s 2 $ 3 $ 3 S 3 $ 3 $ 3 $ 3 4 $ 4 $ 5 S 5 S 6 S 6 S 6 $ 7 $ 7 $ 7 S 7 S 7 S 7 $ 9 s 9 $ 9 $ 10 $ 10 $ 10 $ 10 $ 10 $ 10 $ 11 s 11 S 12 $ 12 S 12 s 12 s 12 $ 13 S 13 $ 14 s 14 $ 15 $ 15 $ 15 S 16 $ 16 s 17 S 17 S 17 $ 18 $ 18 $ 18 $ 18 $ 19 s 20 $ 21 $ 22 $ 24 S 24 S 25 S 29 $ 36 S 38 $ 39 $ 41 $ 46 S 47 S 47 S 49 $ 50 $ 51 $ 55 s 67 $ 69 $ 74 S 84 $ 91 $ 3,133.00 3,246.00 339.00 865.00 1,650.00 3,123.00 4,471.00 245.00 254.00 333.00 320.00 201.00 510.00 394.00 380.00 636.00 358.00 1,354.00 439.00 328.00 370.00 706.00 640.00 465.00 655.00 841.00 429.00 418.00 752.00 798.00 822.00 919.00 798.00 646.00 678.00 965.00 655.00 1,093.00 1,040.00 1,011.00 945.00 899.00 968.00 1,053.00 1,429.00 1,171.00 1,268.00 1,511.00 1,368.00 1,171.00 1,424.00 1,421.00 1,637.00 1,406.00 1,770.00 1,654.00 1,401.00 1,557.00 1,505.00 1,919.00 1,847.00 1,923.00 1,814.00 1,789.00 1,744.00 1,802.00 2,045.00 2,188.00 1,954.00 2,354.00 2.227.00 2,492.00 2,426.00 2,606,00 4,217.00 3.573.00 5,553.00 3,831.00 4,059.00 4,329.00 4,189.00 4,370.00 4,474.00 5,814.00 5,009,00 5,889.00 6,196.00 5,437.00 9,558.00 7,874.00 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 February 29 February 29 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 91 $ 7,874.00 95 $ 9,332.00 2 S 424.00 2 $ 802.00 2 S 604.00 2 $ 200.00 2 S 193.00 3 S 387.00 4 451.00 4 $ 279.00 4 $ 332.00 4 5 407.00 5 $ 561.00 5 $ 502.00 5 $ 591.00 5 S 478.00 6 $ 571.00 6 $ 643.00 6 $ 436.00 8 $ 821.00 8 $ 875.00 8 $ 846.00 8 $ 675.00 8 $ 842.00 8 $ 709.00 9 $ 802.00 9 S 842.00 10 S 1,002.00 10 S 1,019.00 10 S 808.00 10 S 803.00 10 S 1,034.00 10 S 1,010.00 11 $ 1,136.00 11 S 1,106.00 12 S 1,152.00 12 s 1,334.00 13 S 1,537.00 15 $ 1,607.00 15 $ 1,595.00 15 S 1,518.00 15 $ 1,449.00 16 $ 1,485.00 17 S 1,755.00 18 1,755.00 25 S 2,308.00 25 $ 2,559.00 29 S 2,965.00 30 S 2,948.00 39 $ 3,674.00 45 S 4,240.00 49 $ 4,651.00 61 $ 5,373.00 65 S 4,851.00 135 $ 13,822.00 LM HI 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 Required a. Prepare a visualization of the data set using the CHART function in Excel to create an XY scattergraph chart. Comment on what the visualization reveals. Here's one way you could solve this problem: 1. Highlight the data in the Excel data file (NOTE: Select B1: C301) 2. Click on the Insert menu and then click Scatter and choose the first scattergraph. Answer: Or solve your own way: b. Use the MIN, MAX, and VLOOKUP functions in Excel to estimate the road repair cost function using the high- low method. Solve: c. Use the SLOPE and INTERCEPT functions in Excel to estimate the road repair cost function using regression. Solve: d. Based on your work so far, do you have any concerns about the data used to calculate the cost function estima Answer: 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? Answer: 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. Click here to view the Excel dataset. (b) Identify the high and low points. Low point potholes, $ repair cost High point potholes, $ repair cost Using the high-low method to estimate the road repair cost function. (Round answers to 2 decimal places, e.g. 52.43.) Cost estimate = $ + ($ x number of potholes repaired) e Textbook and Media Save for Later Attempts: 0 of 3 used Submit Answer D 1 2 3 TTT 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 A B Repair Date Number of Potholes Filled Final cost February 24 22 $ 2,291.00 February 25 2 $ 286.00 February 25 2 $ 283.00 February 25 2 $ 691.00 February 25 2 $ 181.00 February 25 2 $ 254.00 February 25 2 $ 241.00 February 25 4 $ 299.00 February 25 4 $ 424.00 February 25 4 $ 865.00 February 25 4 $ 422.00 February 25 4 $ 497.00 February 25 5 $ 650.00 February 25 5 $ 910.00 February 25 5 $ 552.00 February 25 7 $ 842.00 February 25 7 $ 1,026.00 February 25 7 $ 626.00 February 25 7 $ 690.00 February 25 7 $ 514.00 February 25 8 $ 733.00 February 25 9 $ 941.00 February 25 9 $ 785.00 February 25 9 $ 942.00 February 25 10 $ 1,109.00 February 25 10 $ 1,182.00 February 25 10 $ 1,192.00 February 25 11 $ 1,067.00 February 25 11 $ 1,400.00 February 25 11 $ 1,381.00 February 25 12 $ 1,297.00 February 25 12 $ 1,147.00 February 25 12 $ 1,274.00 February 25 12 $ 1,302.00 February 25 13 $ 1,277.00 February 25 13 $ 1,248.00 February 25 13 $ 1,515.00 February 25 14 $ 1,413.00 February 25 15 $ 1,455.00 February 25 15 $ 1,638.00 February 25 16 $ 1,721.00 February 25 16 $ 1,639.00 February 25 16 $ 1,656.00 February 25 17 $ 1,855.00 February 25 17 $ 1,751.00 February 25 17 $ 1,796.00 February 25 17 $ 1,918.00 February 25 17 $ 1,670.00 February 25 18 $ 1,662.00 February 25 18 $ 1,841.00 February 25 18 $ 1,713.00 February 25 18 $ 1,907.00 February 25 19 $ 2,071.00 February 25 20 $ 2,065.00 February 25 20 $ 2,114.00 February 25 20 $ 2,017.00 February 25 20 $ 1,844.00 February 25 21 $ 2,096.00 February 25 21 $ 2,206.00 February 25 22 $ 2,088.00 February 25 23 $ 2,159.00 February 25 23 $ 2,130.00 February 25 23 $ 2.259.00 February 25 23 $ 2,414.00 February 25 23 $ 2,324.00 February 25 24 $ 2,212.00 February 25 25 $ 2,492.00 February 25 27 $ 2.703.00 69 February 25 70 February 25 71 February 25 72 February 25 73 February 25 74 February 25 75 February 25 76 February 25 77 February 25 78 February 25 79 February 25 80 February 25 81 February 25 82 February 25 83 February 25 84 February 25 85 February 25 86 February 25 87 February 25 88 February 25 89 February 25 90 February 25 91 February 25 92 February 25 93 February 25 94 February 25 95 February 25 96 February 25 97 February 25 98 February 25 99 February 26 100 February 26 101 February 26 102 February 26 103 February 26 104 February 26 105 February 26 106 February 26 107 February 26 108 February 26 109 February 26 110 February 26 111 February 26 112 February 26 113 February 26 114 February 26 115 February 26 116 February 26 117 February 26 118 February 26 119 February 26 120 February 26 121 February 26 122 February 26 123 February 26 124 February 26 125 February 26 126 February 26 127 February 26 129 February 26 129 February 26 130 February 26 131 February 26 132 February 26 133 February 26 134 February 26 135 February 26 136 February 26 137 February 26 138 February 26 139 February 26 140 February 26 141 February 26 142 February 26 143 February 26 144 February 26 145 February 26 146 February 26 147 February 26 148 February 26 149 February 26 150 February 26 151 February 26 152 February 26 153 February 27 154 February 27 155 February 27 156 February 27 157 February 27 158 February 27 27 $ 2,703.00 27 $ 2,424.00 27 $ 2,702.00 30 $ 2,955.00 30 S 2,975.00 30 $ 2,773.00 31 S 2,993.00 32 S 2,945.00 32 $ 3,115.00 33 S 3,271.00 33 $ 3,269.00 35 S 3,261.00 36 S 2,508.00 36 s 3,503.00 37 $ 3,458.00 38 $ 3,570.00 44 S 4,224.00 45 $ 3,237.00 47 $ 4,202.00 47 $ 4,172.00 49 $ 3,388.00 52 S 4,564.00 69 S 7,152.00 78 $ 6,953.00 83 $ 7,314.00 110 $ 9,761.00 110 $ 8,479.00 117 S 10,302.00 121 S 12,565.00 157 S 14,519.00 2 $ 541.00 3 s 384.00 3 $ 517.00 3 $ 397.00 3 $ 396.00 6 $ 601.00 7 S 815.00 8 S 947.00 9 $ 840.00 10 $ 992.00 10 S 943.00 10 $ 955.00 11 S 1,389.00 12 s 1,240.00 12 S 1,353.00 12 s 1,151.00 14 $ 1,540.00 15 $ 1,626.00 15 $ 1,771.00 15 $ 1,700.00 16 $ 1,580.00 16 S 1,723.00 17 $ 1,709.00 17 $ 1,806.00 20 S 1,912.00 20 S 1,984.00 21 S 2,062.00 21 S 1.980.00 21 S 2,146.00 21 $ 2,044.00 22 $ 2,038.00 22 $ 2,202.00 24 $ 2,250.00 25 $ 2,385.00 26 S 2,348.00 27 $ 2,685.00 28 $ 2,863.00 29 $ 2,720.00 30 S 2,798.00 31 $ 3,025.00 31 $ 2,863.00 33 S 3,286.00 34 $ 3,058.00 35 $ 3,157.00 36 $ 3,453.00 38 $ 3,449.00 39 $ 3,745.00 42 S 3,961.00 43 $ 5,047.00 52 $ 4,545.00 56 $ 6,136.00 56 $ 5,030.00 80 $ 8,166.00 119 S 9,612.00 3 $ 379.00 13 $ 1,531.00 14 S 1,453.00 15 $ 1,517.00 30 $ 2,951.00 31 $ 3,133.00 158 February 27 159 February 27 160 February 28 161 February 28 162 February 28 163 February 28 164 February 28 165 February 29 166 February 29 167 February 29 168 February 29 169 February 29 170 February 29 171 February 29 172 February 29 173 February 29 174 February 29 175 February 29 176 February 29 177 February 29 178 February 29 179 February 29 180 February 29 181 February 29 182 February 29 183 February 29 184 February 29 185 February 29 186 February 29 187 February 29 18.8 February 29 189 February 29 190 February 29 191 February 29 192 February 29 193 February 29 194 February 29 195 February 29 196 February 29 197 February 29 198 February 29 199 February 29 200 February 29 201 February 29 202 February 29 203 February 29 204 February 29 205 February 29 206 February 29 207 February 29 208 February 29 209 February 29 210 February 29 211 February 29 212 February 29 213 February 29 214 February 29 215 February 29 216 February 29 217 February 29 218 February 29 219 February 29 220 February 29 221 February 29 222 February 29 223 February 29 224 February 29 225 February 29 226 February 29 227 February 29 228 February 29 229 February 29 230 February 29 231 February 29 232 February 29 233 February 29 234 February 29 235 February 29 236 February 29 237 February 29 238 February 29 239 February 29 240 February 29 241 February 29 242 February 29 243 February 29 244 February 29 245 February 29 246 February 29 247 February 29 31 S 36 $ 2 S 8 S 15 $ 32 $ 39 $ 1 S 2 S 2 s 2 $ 2 S 2 S 2 $ 2 s 2 $ 3 $ 3 S 3 $ 3 $ 3 $ 3 4 $ 4 $ 5 S 5 S 6 S 6 S 6 $ 7 $ 7 $ 7 S 7 S 7 S 7 $ 9 s 9 $ 9 $ 10 $ 10 $ 10 $ 10 $ 10 $ 10 $ 11 s 11 S 12 $ 12 S 12 s 12 s 12 $ 13 S 13 $ 14 s 14 $ 15 $ 15 $ 15 S 16 $ 16 s 17 S 17 S 17 $ 18 $ 18 $ 18 $ 18 $ 19 s 20 $ 21 $ 22 $ 24 S 24 S 25 S 29 $ 36 S 38 $ 39 $ 41 $ 46 S 47 S 47 S 49 $ 50 $ 51 $ 55 s 67 $ 69 $ 74 S 84 $ 91 $ 3,133.00 3,246.00 339.00 865.00 1,650.00 3,123.00 4,471.00 245.00 254.00 333.00 320.00 201.00 510.00 394.00 380.00 636.00 358.00 1,354.00 439.00 328.00 370.00 706.00 640.00 465.00 655.00 841.00 429.00 418.00 752.00 798.00 822.00 919.00 798.00 646.00 678.00 965.00 655.00 1,093.00 1,040.00 1,011.00 945.00 899.00 968.00 1,053.00 1,429.00 1,171.00 1,268.00 1,511.00 1,368.00 1,171.00 1,424.00 1,421.00 1,637.00 1,406.00 1,770.00 1,654.00 1,401.00 1,557.00 1,505.00 1,919.00 1,847.00 1,923.00 1,814.00 1,789.00 1,744.00 1,802.00 2,045.00 2,188.00 1,954.00 2,354.00 2.227.00 2,492.00 2,426.00 2,606,00 4,217.00 3.573.00 5,553.00 3,831.00 4,059.00 4,329.00 4,189.00 4,370.00 4,474.00 5,814.00 5,009,00 5,889.00 6,196.00 5,437.00 9,558.00 7,874.00 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 February 29 February 29 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 March 01 91 $ 7,874.00 95 $ 9,332.00 2 S 424.00 2 $ 802.00 2 S 604.00 2 $ 200.00 2 S 193.00 3 S 387.00 4 451.00 4 $ 279.00 4 $ 332.00 4 5 407.00 5 $ 561.00 5 $ 502.00 5 $ 591.00 5 S 478.00 6 $ 571.00 6 $ 643.00 6 $ 436.00 8 $ 821.00 8 $ 875.00 8 $ 846.00 8 $ 675.00 8 $ 842.00 8 $ 709.00 9 $ 802.00 9 S 842.00 10 S 1,002.00 10 S 1,019.00 10 S 808.00 10 S 803.00 10 S 1,034.00 10 S 1,010.00 11 $ 1,136.00 11 S 1,106.00 12 S 1,152.00 12 s 1,334.00 13 S 1,537.00 15 $ 1,607.00 15 $ 1,595.00 15 S 1,518.00 15 $ 1,449.00 16 $ 1,485.00 17 S 1,755.00 18 1,755.00 25 S 2,308.00 25 $ 2,559.00 29 S 2,965.00 30 S 2,948.00 39 $ 3,674.00 45 S 4,240.00 49 $ 4,651.00 61 $ 5,373.00 65 S 4,851.00 135 $ 13,822.00 LM HI 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 Required a. Prepare a visualization of the data set using the CHART function in Excel to create an XY scattergraph chart. Comment on what the visualization reveals. Here's one way you could solve this problem: 1. Highlight the data in the Excel data file (NOTE: Select B1: C301) 2. Click on the Insert menu and then click Scatter and choose the first scattergraph. Answer: Or solve your own way: b. Use the MIN, MAX, and VLOOKUP functions in Excel to estimate the road repair cost function using the high- low method. Solve: c. Use the SLOPE and INTERCEPT functions in Excel to estimate the road repair cost function using regression. Solve: d. Based on your work so far, do you have any concerns about the data used to calculate the cost function estima Answer: 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? Answer: 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. Click here to view the Excel dataset. (b) Identify the high and low points. Low point potholes, $ repair cost High point potholes, $ repair cost Using the high-low method to estimate the road repair cost function. (Round answers to 2 decimal places, e.g. 52.43.) Cost estimate = $ + ($ x number of potholes repaired) e Textbook and Media Save for Later Attempts: 0 of 3 used Submit
