The city manager for a small town is its fiscal agent. In that capacity the manager...

 

Transcribed Image Text:

The city manager for a small town is its fiscal agent. In that capacity the manager oversees the outlays to various departments of the city, one of them is the sanitation department. The data set associated with this situation consists of monthly outlays over a ten year period. Outlays are measured in thousands of dollars. a. Use Data Analysis in Excel to run a simple linear trend model. The output is a computer deliverable worth 15 points. b. Based upon the computer output, please do the following: 1) write out the estimated equation. (4 pts) 2) predict the next value in the time series. (4 pts) 3) interpret the slope coefficient for time. (4 pts) 4) test whether or not there is an upward trend in the value of the Y variable. Use alpha 0.05. (15 pts) 5) identify the value of the p-value for the model test. (4 pts) 6) interpret the coefficient of determination for the model. (4 pts) 7) test whether or not the model has explanatory power. Use alpha=0.01. (15 pts) c. Now use Data Analysis in Excel to run a multiple regression, independent variables (twelve of them), are time variable and the monthly dummy variables. This output is a computer deliverable worth 15 points. YEAR MONTH TIME OUTLAYS (5000) 2009 jan feb luuuuluuduulubulunuglundu 2010 jan 2011 jan august 2012 jan 2013 jan 0 1 2 3 4 5 6 7 x 9 SE232S2CERNAAAAARRERASRARAS93399599855ES 10 11 12 13 14 15 16 17 18 19 20 21 24 26 27 30 31 34 36 37 40 41 42 43 44 45 46 47 48 49 50 51 54 56 57 295 333 252 274 245 377 291 273 266 **AAAA¤ÂäƤﯯ¯¯¯¯ˆAĦĦĦARABARA 286 285 259 276 310 238 270 292 289 289 273 271 272 284 271 241 301 265 255 301 278 262 246 249 246 221 251 231 252 293 278 447 216 247 267 281 297 288 298 283 315 287 301 185 368 310 313 312 325 326 dec 2014 jan feb march april may jane july august sept oct nov dec 2015 jan feb march april may june july august sept dec 2016 jan feb march april may august sept 130 dec 2017 jan feb march april may july august sept oct nov dec 2018 jan feb march april may jane july august sept oct nov dec 23323338633*************22222222=2 106 114 115 116 117 118 326 260 291 307 293 279 287 344 359 250 368 359 345 275 321 352 322 309 314 299 355 324 310 339 320 315 342 370 316 361 320 324 320 344 300 350 333 354 365 389 198 366 389 341 413 387 384 415 328 417 408 416 398 397 452 423 456 356 479 425 499 The city manager for a small town is its fiscal agent. In that capacity the manager oversees the outlays to various departments of the city, one of them is the sanitation department. The data set associated with this situation consists of monthly outlays over a ten year period. Outlays are measured in thousands of dollars. a. Use Data Analysis in Excel to run a simple linear trend model. The output is a computer deliverable worth 15 points. b. Based upon the computer output, please do the following: 1) write out the estimated equation. (4 pts) 2) predict the next value in the time series. (4 pts) 3) interpret the slope coefficient for time. (4 pts) 4) test whether or not there is an upward trend in the value of the Y variable. Use alpha 0.05. (15 pts) 5) identify the value of the p-value for the model test. (4 pts) 6) interpret the coefficient of determination for the model. (4 pts) 7) test whether or not the model has explanatory power. Use alpha=0.01. (15 pts) c. Now use Data Analysis in Excel to run a multiple regression, independent variables (twelve of them), are time variable and the monthly dummy variables. This output is a computer deliverable worth 15 points. YEAR MONTH TIME OUTLAYS (5000) 2009 jan feb luuuuluuduulubulunuglundu 2010 jan 2011 jan august 2012 jan 2013 jan 0 1 2 3 4 5 6 7 x 9 SE232S2CERNAAAAARRERASRARAS93399599855ES 10 11 12 13 14 15 16 17 18 19 20 21 24 26 27 30 31 34 36 37 40 41 42 43 44 45 46 47 48 49 50 51 54 56 57 295 333 252 274 245 377 291 273 266 **AAAA¤ÂäƤﯯ¯¯¯¯ˆAĦĦĦARABARA 286 285 259 276 310 238 270 292 289 289 273 271 272 284 271 241 301 265 255 301 278 262 246 249 246 221 251 231 252 293 278 447 216 247 267 281 297 288 298 283 315 287 301 185 368 310 313 312 325 326 dec 2014 jan feb march april may jane july august sept oct nov dec 2015 jan feb march april may june july august sept dec 2016 jan feb march april may august sept 130 dec 2017 jan feb march april may july august sept oct nov dec 2018 jan feb march april may jane july august sept oct nov dec 23323338633*************22222222=2 106 114 115 116 117 118 326 260 291 307 293 279 287 344 359 250 368 359 345 275 321 352 322 309 314 299 355 324 310 339 320 315 342 370 316 361 320 324 320 344 300 350 333 354 365 389 198 366 389 341 413 387 384 415 328 417 408 416 398 397 452 423 456 356 479 425 499