Introduction

This project forecasts daily withdrawals for four ATMs in May 2010, monthly residential electricity consumption for 2014, and hourly waterflow for one week. Each analysis includes data preparation, visualization, model evaluation, and forecasts.

Part A — ATM Forecasting

Import the Data

The ATM data are downloaded from my public GitHub repository. Cash amounts are recorded in hundreds of dollars.

atm_url <- paste0(
  "https://raw.githubusercontent.com/UR-71/",
  "DATA624_Project1/main/ATM624Data.xlsx"
)

atm_file <- tempfile(fileext = ".xlsx")

download.file(
  url = atm_url,
  destfile = atm_file,
  mode = "wb",
  quiet = TRUE
)

atm_raw <- readxl::read_excel(atm_file)

head(atm_raw)
## # A tibble: 6 × 3
##    DATE ATM    Cash
##   <dbl> <chr> <dbl>
## 1 39934 ATM1     96
## 2 39934 ATM2    107
## 3 39935 ATM1     82
## 4 39935 ATM2     89
## 5 39936 ATM1     85
## 6 39936 ATM2     90
dim(atm_raw)
## [1] 1474    3

Prepare the Dates

Excel stores dates as numbers. These are converted to calendar dates before analysis.

atm_data <- atm_raw
atm_data$DATE <- as.Date(atm_data$DATE, origin = "1899-12-30")

head(atm_data)
## # A tibble: 6 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-05-01 ATM1     96
## 2 2009-05-01 ATM2    107
## 3 2009-05-02 ATM1     82
## 4 2009-05-02 ATM2     89
## 5 2009-05-03 ATM1     85
## 6 2009-05-03 ATM2     90
range(atm_data$DATE)
## [1] "2009-05-01" "2010-05-14"

Check Data Quality

Before fitting models, I check for missing values and count the observations for each ATM.

colSums(is.na(atm_data))
## DATE  ATM Cash 
##    0   14   19
table(atm_data$ATM, useNA = "ifany")
## 
## ATM1 ATM2 ATM3 ATM4 <NA> 
##  365  365  365  365   14
atm_data[is.na(atm_data$ATM) | is.na(atm_data$Cash), ]
## # A tibble: 19 × 3
##    DATE       ATM    Cash
##    <date>     <chr> <dbl>
##  1 2009-06-13 ATM1     NA
##  2 2009-06-16 ATM1     NA
##  3 2009-06-18 ATM2     NA
##  4 2009-06-22 ATM1     NA
##  5 2009-06-24 ATM2     NA
##  6 2010-05-01 <NA>     NA
##  7 2010-05-02 <NA>     NA
##  8 2010-05-03 <NA>     NA
##  9 2010-05-04 <NA>     NA
## 10 2010-05-05 <NA>     NA
## 11 2010-05-06 <NA>     NA
## 12 2010-05-07 <NA>     NA
## 13 2010-05-08 <NA>     NA
## 14 2010-05-09 <NA>     NA
## 15 2010-05-10 <NA>     NA
## 16 2010-05-11 <NA>     NA
## 17 2010-05-12 <NA>     NA
## 18 2010-05-13 <NA>     NA
## 19 2010-05-14 <NA>     NA

Separate Historical Observations

Fourteen rows contain dates but no ATM identifier or withdrawal amount. I exclude these rows from the historical data. The five missing withdrawal amounts for identified ATMs remain for further examination.

atm_history <- atm_data[!is.na(atm_data$ATM), ]

atm_history <- atm_history[
  order(atm_history$ATM, atm_history$DATE),
]

dim(atm_history)
## [1] 1460    3
colSums(is.na(atm_history))
## DATE  ATM Cash 
##    0    0    5
range(atm_history$DATE)
## [1] "2009-05-01" "2010-04-30"
sum(duplicated(atm_history[c("ATM", "DATE")]))
## [1] 0

Visualize Daily Withdrawals

I plot each ATM separately because withdrawal levels and patterns may differ across machines. Cash remains in hundreds of dollars. Missing observations appear as gaps.

old_par <- par(mfrow = c(2, 2), mar = c(4, 4, 3, 1))

for (atm_name in sort(unique(atm_history$ATM))) {
  atm_subset <- atm_history[atm_history$ATM == atm_name, ]

  plot(
    atm_subset$DATE,
    atm_subset$Cash,
    type = "l",
    main = atm_name,
    xlab = "Date",
    ylab = "Cash (hundreds of dollars)",
    col = "steelblue",
    lwd = 1
  )
}

par(old_par)

Examine Unusual Observations

ATM3 shows a long period of zero withdrawals followed by recent activity. ATM4 contains a large isolated spike. I inspect these observations before deciding whether special treatment is appropriate. Neither pattern alone establishes a recording error.

# Display every positive withdrawal for ATM3.
atm3_positive <- atm_history[
  atm_history$ATM == "ATM3" &
    !is.na(atm_history$Cash) &
    atm_history$Cash > 0,
]

atm3_positive
## # A tibble: 3 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2010-04-28 ATM3     96
## 2 2010-04-29 ATM3     82
## 3 2010-04-30 ATM3     85
# Display the largest recorded withdrawal for ATM4.
atm4_data <- atm_history[atm_history$ATM == "ATM4", ]

atm4_data[which.max(atm4_data$Cash), ]
## # A tibble: 1 × 3
##   DATE       ATM     Cash
##   <date>     <chr>  <dbl>
## 1 2010-02-09 ATM4  10920.

Examine Missing Withdrawals

I inspect observations around each missing withdrawal before choosing how to fill the gaps.

missing_rows <- atm_history[is.na(atm_history$Cash), ]

for (i in seq_len(nrow(missing_rows))) {
  this_atm <- missing_rows$ATM[i]
  this_date <- missing_rows$DATE[i]

  cat(
    "\n", this_atm, "— missing on",
    format(this_date), "\n"
  )

  nearby <- atm_history[
    atm_history$ATM == this_atm &
      atm_history$DATE >= this_date - 3 &
      atm_history$DATE <= this_date + 3,
  ]

  print(nearby)
}
## 
##  ATM1 — missing on 2009-06-13 
## # A tibble: 7 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-06-10 ATM1     81
## 2 2009-06-11 ATM1     16
## 3 2009-06-12 ATM1    142
## 4 2009-06-13 ATM1     NA
## 5 2009-06-14 ATM1    120
## 6 2009-06-15 ATM1    106
## 7 2009-06-16 ATM1     NA
## 
##  ATM1 — missing on 2009-06-16 
## # A tibble: 7 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-06-13 ATM1     NA
## 2 2009-06-14 ATM1    120
## 3 2009-06-15 ATM1    106
## 4 2009-06-16 ATM1     NA
## 5 2009-06-17 ATM1    108
## 6 2009-06-18 ATM1     21
## 7 2009-06-19 ATM1    140
## 
##  ATM1 — missing on 2009-06-22 
## # A tibble: 7 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-06-19 ATM1    140
## 2 2009-06-20 ATM1    110
## 3 2009-06-21 ATM1    115
## 4 2009-06-22 ATM1     NA
## 5 2009-06-23 ATM1    108
## 6 2009-06-24 ATM1     66
## 7 2009-06-25 ATM1     13
## 
##  ATM2 — missing on 2009-06-18 
## # A tibble: 7 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-06-15 ATM2     66
## 2 2009-06-16 ATM2     82
## 3 2009-06-17 ATM2     24
## 4 2009-06-18 ATM2     NA
## 5 2009-06-19 ATM2    134
## 6 2009-06-20 ATM2     95
## 7 2009-06-21 ATM2     82
## 
##  ATM2 — missing on 2009-06-24 
## # A tibble: 7 × 3
##   DATE       ATM    Cash
##   <date>     <chr> <dbl>
## 1 2009-06-21 ATM2     82
## 2 2009-06-22 ATM2     90
## 3 2009-06-23 ATM2     99
## 4 2009-06-24 ATM2     NA
## 5 2009-06-25 ATM2      3
## 6 2009-06-26 ATM2    117
## 7 2009-06-27 ATM2     53

Fill Missing Withdrawals

The five missing withdrawal amounts are estimated using the median of observed withdrawals for the same ATM and weekday within 28 days on either side. Original observations are preserved in atm_history. ATM3’s zeros and ATM4’s large spike remain unchanged.

atm_clean <- atm_history

imputation_log <- atm_history[
  is.na(atm_history$Cash),
  c("DATE", "ATM", "Cash")
]
names(imputation_log)[3] <- "Imputed_Cash"

for (i in seq_len(nrow(imputation_log))) {
  this_atm <- imputation_log$ATM[i]
  this_date <- imputation_log$DATE[i]

  days_apart <- as.integer(atm_history$DATE - this_date)

  donors <- atm_history$Cash[
    atm_history$ATM == this_atm &
      !is.na(atm_history$Cash) &
      abs(days_apart) <= 28 &
      days_apart %% 7 == 0
  ]

  stopifnot(length(donors) > 0)

  replacement <- median(donors)

  target <- which(
    atm_clean$ATM == this_atm &
      atm_clean$DATE == this_date
  )

  atm_clean$Cash[target] <- replacement
  imputation_log$Imputed_Cash[i] <- replacement
}

imputation_log
## # A tibble: 5 × 3
##   DATE       ATM   Imputed_Cash
##   <date>     <chr>        <dbl>
## 1 2009-06-13 ATM1          99  
## 2 2009-06-16 ATM1         109  
## 3 2009-06-22 ATM1          96.5
## 4 2009-06-18 ATM2           7  
## 5 2009-06-24 ATM2          25.5
colSums(is.na(atm_clean))
## DATE  ATM Cash 
##    0    0    0

Examine Weekly Patterns

I compare average withdrawals by weekday for ATM1 and ATM2. This helps assess whether a weekly seasonal model could be useful. ATM3 and ATM4 will receive separate consideration because of their unusual patterns.

weekday_labels <- c(
  "Monday", "Tuesday", "Wednesday", "Thursday",
  "Friday", "Saturday", "Sunday"
)

weekday_data <- atm_clean[
  atm_clean$ATM %in% c("ATM1", "ATM2"),
]

weekday_data$Weekday <- factor(
  as.integer(format(weekday_data$DATE, "%u")),
  levels = 1:7,
  labels = weekday_labels
)

weekday_summary <- aggregate(
  Cash ~ ATM + Weekday,
  data = weekday_data,
  FUN = mean
)

weekday_summary <- weekday_summary[
  order(weekday_summary$ATM, weekday_summary$Weekday),
]

weekday_summary$Cash <- round(weekday_summary$Cash, 2)

weekday_summary
##     ATM   Weekday   Cash
## 1  ATM1    Monday  86.20
## 3  ATM1   Tuesday  89.94
## 5  ATM1 Wednesday  82.15
## 7  ATM1  Thursday  31.69
## 9  ATM1    Friday  98.64
## 11 ATM1  Saturday  96.65
## 13 ATM1    Sunday 102.65
## 2  ATM2    Monday  58.73
## 4  ATM2   Tuesday  73.25
## 6  ATM2 Wednesday  43.39
## 8  ATM2  Thursday  25.17
## 10 ATM2    Friday  92.02
## 12 ATM2  Saturday  75.98
## 14 ATM2    Sunday  67.15

Create Training and Test Sets

The final 31 observed days are held out to evaluate forecast accuracy. Models will be trained using earlier observations and compared against these held-out values before forecasting May.

ATM3 is considered separately because it has only three positive observations.

atm_train <- atm_clean[
  atm_clean$DATE < as.Date("2010-03-31"),
]

atm_test <- atm_clean[
  atm_clean$DATE >= as.Date("2010-03-31"),
]

# Verify the dates and number of observations.
range(atm_train$DATE)
## [1] "2009-05-01" "2010-03-30"
range(atm_test$DATE)
## [1] "2010-03-31" "2010-04-30"
table(atm_train$ATM)
## 
## ATM1 ATM2 ATM3 ATM4 
##  334  334  334  334
table(atm_test$ATM)
## 
## ATM1 ATM2 ATM3 ATM4 
##   31   31   31   31
stopifnot(
  all(table(atm_train$ATM) == 334),
  all(table(atm_test$ATM) == 31),
  max(atm_train$DATE) < min(atm_test$DATE)
)

Evaluate Baseline Forecasts

Three baseline methods are compared using the 31-day test period. Lower MAE and RMSE indicate better forecast accuracy. Both measures are expressed in hundreds of dollars.

baseline_results <- data.frame()

for (atm_name in c("ATM1", "ATM2")) {
  train_values <- atm_train$Cash[atm_train$ATM == atm_name]
  test_values <- atm_test$Cash[atm_test$ATM == atm_name]

  h <- length(test_values)

  predictions <- list(
    Mean = rep(mean(train_values), h),
    Naive = rep(tail(train_values, 1), h),
    Seasonal_Naive = rep(tail(train_values, 7), length.out = h)
  )

  for (method_name in names(predictions)) {
    errors <- test_values - predictions[[method_name]]

    baseline_results <- rbind(
      baseline_results,
      data.frame(
        ATM = atm_name,
        Method = method_name,
        MAE = mean(abs(errors)),
        RMSE = sqrt(mean(errors^2))
      )
    )
  }
}

baseline_results$MAE <- round(baseline_results$MAE, 2)
baseline_results$RMSE <- round(baseline_results$RMSE, 2)

baseline_results
##    ATM         Method   MAE  RMSE
## 1 ATM1           Mean 20.17 32.24
## 2 ATM1          Naive 63.29 67.20
## 3 ATM1 Seasonal_Naive 13.16 15.86
## 4 ATM2           Mean 32.39 37.19
## 5 ATM2          Naive 62.71 72.91
## 6 ATM2 Seasonal_Naive 19.84 26.82

Fit ETS and Seasonal ARIMA Models

Weekly time series are created using a seasonal period of seven days. ETS and seasonal ARIMA models are fitted using only the training observations.

atm_fits <- list()

for (atm_name in c("ATM1", "ATM2")) {
  train_values <- atm_train$Cash[atm_train$ATM == atm_name]

  train_ts <- ts(train_values, frequency = 7)

  atm_fits[[atm_name]] <- list(
    ETS = forecast::ets(train_ts),
    ARIMA = forecast::auto.arima(
      train_ts,
      seasonal = TRUE,
      stepwise = FALSE,
      approximation = FALSE
    )
  )
}

for (atm_name in names(atm_fits)) {
  cat("\n", atm_name, "\n")
  print(atm_fits[[atm_name]]$ETS)
  print(atm_fits[[atm_name]]$ARIMA)
}
## 
##  ATM1 
## ETS(A,N,A) 
## 
## Call:
## forecast::ets(y = train_ts)
## 
##   Smoothing parameters:
##     alpha = 0.0171 
##     gamma = 0.3273 
## 
##   Initial states:
##     l = 78.6767 
##     s = -65.8788 4.1496 15.7681 4.4473 17.5111 9.4304
##            14.5724
## 
##   sigma:  24.8638
## 
##      AIC     AICc      BIC 
## 4098.357 4099.039 4136.469 
## Series: train_ts 
## ARIMA(0,0,1)(1,1,1)[7] 
## 
## Coefficients:
##          ma1    sar1     sma1
##       0.1884  0.2134  -0.7842
## s.e.  0.0583  0.0881   0.0644
## 
## sigma^2 = 592.4:  log likelihood = -1508.53
## AIC=3025.06   AICc=3025.18   BIC=3040.22
## 
##  ATM2 
## ETS(A,N,A) 
## 
## Call:
## forecast::ets(y = train_ts)
## 
##   Smoothing parameters:
##     alpha = 1e-04 
##     gamma = 0.3692 
## 
##   Initial states:
##     l = 72.892 
##     s = -44.9811 -25.2817 19.0331 0.1025 4.601 13.6449
##            32.8813
## 
##   sigma:  25.7001
## 
##      AIC     AICc      BIC 
## 4120.457 4121.138 4158.568 
## Series: train_ts 
## ARIMA(2,0,2)(0,1,1)[7] with drift 
## 
## Coefficients:
##           ar1      ar2     ma1     ma2     sma1   drift
##       -0.4255  -0.9307  0.4622  0.8006  -0.7980  -0.074
## s.e.   0.0451   0.0387  0.0774  0.0546   0.0447   0.041
## 
## sigma^2 = 605.3:  log likelihood = -1511.12
## AIC=3036.24   AICc=3036.59   BIC=3062.77

Compare Model Accuracy

ETS and ARIMA forecasts are evaluated against the same held-out observations as the baseline methods. Model selection is based on test-set accuracy rather than comparing AIC values across ETS and ARIMA.

model_results <- data.frame()

for (atm_name in c("ATM1", "ATM2")) {
  actual <- atm_test$Cash[atm_test$ATM == atm_name]

  for (method_name in c("ETS", "ARIMA")) {
    fc <- forecast::forecast(
      atm_fits[[atm_name]][[method_name]],
      h = length(actual)
    )

    errors <- actual - as.numeric(fc$mean)

    model_results <- rbind(
      model_results,
      data.frame(
        ATM = atm_name,
        Method = method_name,
        MAE = mean(abs(errors)),
        RMSE = sqrt(mean(errors^2))
      )
    )
  }
}

atm_accuracy <- rbind(baseline_results, model_results)

atm_accuracy <- atm_accuracy[
  order(atm_accuracy$ATM, atm_accuracy$MAE),
]

atm_accuracy$MAE <- round(atm_accuracy$MAE, 2)
atm_accuracy$RMSE <- round(atm_accuracy$RMSE, 2)
rownames(atm_accuracy) <- NULL

atm_accuracy
##     ATM         Method   MAE  RMSE
## 1  ATM1            ETS  9.86 12.74
## 2  ATM1          ARIMA 11.43 14.96
## 3  ATM1 Seasonal_Naive 13.16 15.86
## 4  ATM1           Mean 20.17 32.24
## 5  ATM1          Naive 63.29 67.20
## 6  ATM2            ETS 14.70 19.41
## 7  ATM2          ARIMA 18.67 23.21
## 8  ATM2 Seasonal_Naive 19.84 26.82
## 9  ATM2           Mean 32.39 37.19
## 10 ATM2          Naive 62.71 72.91

Check ETS Residuals

ETS produced the lowest errors on the 31-day test period for both ATMs. Before finalizing the models, I inspect their training residuals for remaining autocorrelation, unusual observations, and changes in variability.

forecast::checkresiduals(
  atm_fits[["ATM1"]][["ETS"]],
  lag = 14
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ETS(A,N,A)
## Q* = 28.806, df = 14, p-value = 0.0111
## 
## Model df: 0.   Total lags used: 14
forecast::checkresiduals(
  atm_fits[["ATM2"]][["ETS"]],
  lag = 14
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ETS(A,N,A)
## Q* = 35.396, df = 14, p-value = 0.001284
## 
## Model df: 0.   Total lags used: 14

Check ARIMA Residuals

The ETS residuals show statistically significant autocorrelation for both ATMs. I also examine the ARIMA residuals to assess whether those models capture the remaining dependence more effectively, despite their higher test errors.

forecast::checkresiduals(
  atm_fits[["ATM1"]][["ARIMA"]],
  lag = 14
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ARIMA(0,0,1)(1,1,1)[7]
## Q* = 16.944, df = 11, p-value = 0.1095
## 
## Model df: 3.   Total lags used: 14
forecast::checkresiduals(
  atm_fits[["ATM2"]][["ARIMA"]],
  lag = 14
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ARIMA(2,0,2)(0,1,1)[7] with drift
## Q* = 9.1683, df = 9, p-value = 0.4219
## 
## Model df: 5.   Total lags used: 14

Forecast May for ATM1 and ATM2

ETS had the lowest MAE and RMSE for both ATMs on the held-out month. ARIMA had better short-lag residual diagnostics but higher forecast errors. I select ETS based on test accuracy, while acknowledging that remaining autocorrelation and extreme residuals may affect prediction interval reliability.

The selected ETS(A,N,A) structure is refitted using all 365 historical days before forecasting May 1–31, 2010.

may_dates <- seq.Date(
  as.Date("2010-05-01"),
  as.Date("2010-05-31"),
  by = "day"
)

atm_final_fits <- list()
atm_final_forecasts <- list()

for (atm_name in c("ATM1", "ATM2")) {
  full_values <- atm_clean$Cash[atm_clean$ATM == atm_name]

  atm_final_fits[[atm_name]] <- forecast::ets(
    ts(full_values, frequency = 7),
    model = "ANA"
  )

  atm_final_forecasts[[atm_name]] <- forecast::forecast(
    atm_final_fits[[atm_name]],
    h = length(may_dates),
    level = c(80, 95)
  )
}

# Preview point forecasts in the original units.
atm12_preview <- data.frame(
  DATE = may_dates,
  ATM1 = as.numeric(atm_final_forecasts[["ATM1"]]$mean),
  ATM2 = as.numeric(atm_final_forecasts[["ATM2"]]$mean)
)

head(atm12_preview, 7)
##         DATE       ATM1       ATM2
## 1 2010-05-01  86.980573  68.388716
## 2 2010-05-02 100.682676  74.228857
## 3 2010-05-03  73.070856  11.075955
## 4 2010-05-04   5.635037   2.105405
## 5 2010-05-05 100.078226 101.661085
## 6 2010-05-06  79.373916  92.444079
## 7 2010-05-07  85.539134  69.117172

Visualize ATM1 and ATM2 Forecasts

The plots show the final eight weeks of observed withdrawals followed by May forecasts. Shaded areas represent model-based 80% and 95% prediction intervals. These intervals should be interpreted cautiously because the ETS residuals retained some autocorrelation.

for (atm_name in c("ATM1", "ATM2")) {
  history <- atm_clean[atm_clean$ATM == atm_name, ]
  history <- tail(history, 56)

  fc <- atm_final_forecasts[[atm_name]]

  lower95 <- as.numeric(fc$lower[, "95%"])
  upper95 <- as.numeric(fc$upper[, "95%"])
  lower80 <- as.numeric(fc$lower[, "80%"])
  upper80 <- as.numeric(fc$upper[, "80%"])

  plot(
    history$DATE,
    history$Cash,
    type = "l",
    xlim = range(c(history$DATE, may_dates)),
    ylim = range(c(history$Cash, lower95, upper95)),
    main = paste(atm_name, "— May 2010 Forecast"),
    xlab = "Date",
    ylab = "Cash (hundreds of dollars)",
    lwd = 1.5
  )

  polygon(
    c(may_dates, rev(may_dates)),
    c(lower95, rev(upper95)),
    col = "#DBEAFE",
    border = NA
  )

  polygon(
    c(may_dates, rev(may_dates)),
    c(lower80, rev(upper80)),
    col = "#93C5FD",
    border = NA
  )

  lines(may_dates, as.numeric(fc$mean), col = "#1D4ED8", lwd = 2)
  abline(v = as.Date("2010-05-01"), lty = 2, col = "gray50")

  legend(
    "topleft",
    legend = c("Observed", "Forecast", "80% interval", "95% interval"),
    col = c("black", "#1D4ED8", "#93C5FD", "#DBEAFE"),
    lty = c(1, 1, NA, NA),
    lwd = c(1.5, 2, NA, NA),
    pch = c(NA, NA, 15, 15),
    bty = "n",
    cex = 0.8
  )
}

## Forecast ATM3

ATM3 recorded zero withdrawals until its final three observed days. The reason for this change is unknown. Assuming the recent activity continues throughout May, I use the average of those three positive withdrawals as the daily forecast.

This is a judgment-based forecast supported by very limited data. A seasonal pattern and reliable prediction intervals cannot be estimated from only three active days. The original zero observations remain unchanged.

atm3_active <- atm_clean$Cash[
  atm_clean$ATM == "ATM3" & atm_clean$Cash > 0
]

stopifnot(length(atm3_active) == 3)

atm3_daily_mean <- mean(atm3_active)

atm3_forecast <- data.frame(
  DATE = may_dates,
  ATM = "ATM3",
  Forecast_Hundreds = rep(atm3_daily_mean, length(may_dates))
)

data.frame(
  Active_Days = length(atm3_active),
  Daily_Forecast_Dollars = round(atm3_daily_mean * 100, 2),
  May_Total_Dollars = round(
    sum(atm3_forecast$Forecast_Hundreds) * 100, 2
  )
)
##   Active_Days Daily_Forecast_Dollars May_Total_Dollars
## 1           3                8766.67          271766.7

Evaluate ATM4 Models

ATM4 contains an unusually large withdrawal on February 9, 2010. Since there is no evidence that this observation is incorrect, it is retained in this initial comparison.

Mean, naïve, seasonal naïve, ETS, and ARIMA forecasts are evaluated against the same 31-day test period used for ATM1 and ATM2.

atm4_train_values <- atm_train$Cash[atm_train$ATM == "ATM4"]
atm4_actual <- atm_test$Cash[atm_test$ATM == "ATM4"]

atm4_train_ts <- ts(atm4_train_values, frequency = 7)
atm4_h <- length(atm4_actual)

atm4_fits <- list(
  ETS = forecast::ets(atm4_train_ts),
  ARIMA = forecast::auto.arima(
    atm4_train_ts,
    seasonal = TRUE,
    stepwise = FALSE,
    approximation = FALSE
  )
)

atm4_predictions <- list(
  Mean = rep(mean(atm4_train_values), atm4_h),
  Naive = rep(tail(atm4_train_values, 1), atm4_h),
  Seasonal_Naive = rep(
    tail(atm4_train_values, 7),
    length.out = atm4_h
  ),
  ETS = as.numeric(
    forecast::forecast(atm4_fits$ETS, h = atm4_h)$mean
  ),
  ARIMA = as.numeric(
    forecast::forecast(atm4_fits$ARIMA, h = atm4_h)$mean
  )
)

atm4_accuracy <- do.call(
  rbind,
  lapply(names(atm4_predictions), function(method_name) {
    errors <- atm4_actual - atm4_predictions[[method_name]]

    data.frame(
      Method = method_name,
      MAE = mean(abs(errors)),
      RMSE = sqrt(mean(errors^2))
    )
  })
)

atm4_accuracy <- atm4_accuracy[order(atm4_accuracy$MAE), ]
rownames(atm4_accuracy) <- NULL

atm4_accuracy$MAE <- round(atm4_accuracy$MAE, 2)
atm4_accuracy$RMSE <- round(atm4_accuracy$RMSE, 2)

atm4_accuracy
##           Method    MAE   RMSE
## 1          ARIMA 241.42 288.72
## 2           Mean 241.42 288.72
## 3 Seasonal_Naive 260.18 329.58
## 4            ETS 319.62 404.41
## 5          Naive 378.19 466.06

Examine the ATM4 ARIMA Model

ARIMA and the historical mean produced the lowest test errors at the displayed precision. I inspect the selected ARIMA specification and residuals before choosing the final forecasting method.

print(atm4_fits$ARIMA)
## Series: atm4_train_ts 
## ARIMA(0,0,0) with non-zero mean 
## 
## Coefficients:
##           mean
##       481.0634
## s.e.   36.8722
## 
## sigma^2 = 455456:  log likelihood = -2649.28
## AIC=5302.55   AICc=5302.59   BIC=5310.18
# Check whether ARIMA produces the same forecasts as the mean.
max(abs(
  atm4_predictions$ARIMA - atm4_predictions$Mean
))
## [1] 1.705303e-13
forecast::checkresiduals(
  atm4_fits$ARIMA,
  lag = 14
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ARIMA(0,0,0) with non-zero mean
## Q* = 4.2935, df = 14, p-value = 0.9934
## 
## Model df: 0.   Total lags used: 14

Assess ATM4 Sensitivity to the Large Withdrawal

The selected ARIMA model is equivalent to forecasting the historical mean. I compare this mean with and without the largest observation to assess its influence. Excluding that observation here is only a sensitivity check, not a claim that it is incorrect.

atm4_values <- atm_clean$Cash[atm_clean$ATM == "ATM4"]
atm4_largest <- which.max(atm4_values)

atm4_sensitivity <- data.frame(
  Scenario = c(
    "All observations retained",
    "Largest observation excluded"
  ),
  Daily_Mean_Dollars = c(
    mean(atm4_values),
    mean(atm4_values[-atm4_largest])
  ) * 100
)

atm4_sensitivity$May_Total_Dollars <-
  atm4_sensitivity$Daily_Mean_Dollars * length(may_dates)

atm4_sensitivity$Daily_Mean_Dollars <- round(
  atm4_sensitivity$Daily_Mean_Dollars, 2
)

atm4_sensitivity$May_Total_Dollars <- round(
  atm4_sensitivity$May_Total_Dollars, 2
)

atm4_sensitivity
##                       Scenario Daily_Mean_Dollars May_Total_Dollars
## 1    All observations retained           47404.33           1469534
## 2 Largest observation excluded           44534.63           1380574

Forecast ATM4

The mean forecast tied with automatic ARIMA for the lowest test errors. Automatic ARIMA selected an ARIMA(0,0,0) model with a mean, confirming that the two approaches are equivalent here.

I retain all recorded withdrawals, including the unusually large observation, and estimate the final daily forecast using the full historical mean. Excluding the largest observation would reduce the forecast by approximately 6.4% relative to the excluded-observation estimate.

The large spike and skewed residuals make normal-theory prediction intervals potentially unreliable.

atm4_daily_mean <- mean(atm4_values)

atm4_forecast <- data.frame(
  DATE = may_dates,
  ATM = "ATM4",
  Forecast_Hundreds = rep(atm4_daily_mean, length(may_dates))
)

data.frame(
  Daily_Forecast_Dollars = round(atm4_daily_mean * 100, 2),
  May_Total_Dollars = round(
    sum(atm4_forecast$Forecast_Hundreds) * 100, 2
  )
)
##   Daily_Forecast_Dollars May_Total_Dollars
## 1               47404.33           1469534

Export May ATM Forecasts

The export contains one forecast per ATM per day for May 2010: 124 rows in total. Forecasts are provided in both the original hundreds-of-dollars units and dollars.

atm_may_forecasts <- rbind(
  data.frame(
    DATE = may_dates,
    ATM = "ATM1",
    Forecast_Hundreds = as.numeric(
      atm_final_forecasts[["ATM1"]]$mean
    )
  ),
  data.frame(
    DATE = may_dates,
    ATM = "ATM2",
    Forecast_Hundreds = as.numeric(
      atm_final_forecasts[["ATM2"]]$mean
    )
  ),
  atm3_forecast,
  atm4_forecast
)

atm_may_forecasts <- atm_may_forecasts[
  order(atm_may_forecasts$ATM, atm_may_forecasts$DATE),
]
rownames(atm_may_forecasts) <- NULL

atm_may_forecasts$Forecast_Dollars <- round(
  atm_may_forecasts$Forecast_Hundreds * 100, 2
)

stopifnot(
  nrow(atm_may_forecasts) == 124,
  all(table(atm_may_forecasts$ATM) == 31),
  !anyDuplicated(atm_may_forecasts[c("ATM", "DATE")]),
  all(is.finite(atm_may_forecasts$Forecast_Hundreds)),
  all(atm_may_forecasts$Forecast_Hundreds >= 0)
)

write.csv(
  atm_may_forecasts,
  file = "ATM_May_2010_forecasts.csv",
  row.names = FALSE
)

# Monthly totals calculated before rounding daily forecasts.
atm_monthly_totals <- aggregate(
  Forecast_Hundreds ~ ATM,
  data = atm_may_forecasts,
  FUN = sum
)

atm_monthly_totals$May_Total_Dollars <- round(
  atm_monthly_totals$Forecast_Hundreds * 100, 2
)

atm_monthly_totals[c("ATM", "May_Total_Dollars")]
##    ATM May_Total_Dollars
## 1 ATM1          238617.6
## 2 ATM2          182977.9
## 3 ATM3          271766.7
## 4 ATM4         1469534.4
# Show where the CSV was saved.
normalizePath("ATM_May_2010_forecasts.csv")
## [1] "/Users/ummayrukiya/Desktop/ATM_May_2010_forecasts.csv"

Part A Findings and Limitations

ETS produced the lowest test MAE and RMSE for ATM1 and ATM2, outperforming mean, naïve, seasonal naïve, and ARIMA forecasts. The selected ETS models were refitted using all historical observations to forecast May. Their residuals retained some autocorrelation, so prediction intervals should be interpreted cautiously.

ATM3 had only three positive observations. Its forecast uses their average, conditional on continued activity throughout May. This assumption cannot be reliably validated with the available data.

For ATM4, automatic ARIMA selected a model equivalent to the historical mean. This approach tied for the lowest test errors. The unusually large February withdrawal was retained because it could not be confirmed as an error. A sensitivity analysis showed that excluding it would lower the daily forecast from approximately $47,404 to $44,535.

These forecasts estimate withdrawals, not required replenishment amounts. Operational cash planning would also require information about existing balances, refill schedules, machine capacity, and acceptable cash-shortage risk.

Part B — Residential Power Forecasting

Import the Data

The dataset contains monthly residential electricity consumption from January 1998 through December 2013. Consumption is measured in kilowatt-hours (KWH). The goal is to forecast each month of 2014.

power_url <- paste0(
  "https://raw.githubusercontent.com/UR-71/",
  "DATA624_Project1/main/",
  "ResidentialCustomerForecastLoad-624.xlsx"
)

power_file <- tempfile(fileext = ".xlsx")

download.file(
  url = power_url,
  destfile = power_file,
  mode = "wb",
  quiet = TRUE
)

power_raw <- readxl::read_excel(power_file)

head(power_raw)
## # A tibble: 6 × 3
##   CaseSequence `YYYY-MMM`     KWH
##          <dbl> <chr>        <dbl>
## 1          733 1998-Jan   6862583
## 2          734 1998-Feb   5838198
## 3          735 1998-Mar   5420658
## 4          736 1998-Apr   5010364
## 5          737 1998-May   4665377
## 6          738 1998-Jun   6467147
dim(power_raw)
## [1] 192   3
str(power_raw)
## tibble [192 × 3] (S3: tbl_df/tbl/data.frame)
##  $ CaseSequence: num [1:192] 733 734 735 736 737 738 739 740 741 742 ...
##  $ YYYY-MMM    : chr [1:192] "1998-Jan" "1998-Feb" "1998-Mar" "1998-Apr" ...
##  $ KWH         : num [1:192] 6862583 5838198 5420658 5010364 4665377 ...

Prepare Dates and Check Data Quality

Month labels are converted to the first day of each month. I then check that the observations form a complete monthly sequence and inspect any missing consumption values.

power_data <- as.data.frame(power_raw)

# Match English month abbreviations without relying on system locale.
month_labels <- power_data[["YYYY-MMM"]]

month_numbers <- match(
  substr(month_labels, 6, 8),
  month.abb
)

stopifnot(!anyNA(month_numbers))

power_data$DATE <- as.Date(
  sprintf(
    "%s-%02d-01",
    substr(month_labels, 1, 4),
    month_numbers
  )
)

power_data <- power_data[order(power_data$DATE), ]

expected_months <- seq.Date(
  as.Date("1998-01-01"),
  as.Date("2013-12-01"),
  by = "month"
)

range(power_data$DATE)
## [1] "1998-01-01" "2013-12-01"
colSums(is.na(power_data))
## CaseSequence     YYYY-MMM          KWH         DATE 
##            0            0            1            0
sum(duplicated(power_data$DATE))
## [1] 0
# TRUE means every expected month appears in the correct order.
identical(power_data$DATE, expected_months)
## [1] TRUE
# Display any missing consumption observations.
power_data[
  is.na(power_data$KWH),
  c("DATE", "KWH")
]
##           DATE KWH
## 129 2008-09-01  NA

Visualize Monthly Electricity Consumption

The time plot helps identify trend, seasonal variation, and unusual observations. The missing consumption value is left unchanged at this stage.

plot(
  power_data$DATE,
  power_data$KWH,
  type = "l",
  col = "steelblue",
  lwd = 1.5,
  main = "Monthly Residential Electricity Consumption",
  xlab = "Year",
  ylab = "Electricity consumption (KWH)"
)

# Identify the missing month.
power_data[
  is.na(power_data$KWH),
  c("DATE", "KWH"),
  drop = FALSE
]
##           DATE KWH
## 129 2008-09-01  NA
# Inspect the five lowest observed values.
observed_power <- power_data[
  !is.na(power_data$KWH),
  c("DATE", "KWH")
]

head(
  observed_power[order(observed_power$KWH), ],
  5
)
##           DATE     KWH
## 151 2010-07-01  770523
## 95  2005-11-01 4313019
## 24  1999-12-01 4419229
## 17  1999-05-01 4426794
## 23  1999-11-01 4436269

Inspect Missing and Unusual Consumption

September 2008 is missing, and July 2010 is unusually low. I compare these observations with the same calendar months in nearby years before deciding how to handle them.

power_year <- as.integer(format(power_data$DATE, "%Y"))
power_month <- as.integer(format(power_data$DATE, "%m"))

# Compare September observations around the missing month.
power_data[
  power_month == 9 & power_year >= 2006 & power_year <= 2010,
  c("DATE", "KWH"),
  drop = FALSE
]
##           DATE     KWH
## 105 2006-09-01 7296355
## 117 2007-09-01 7666970
## 129 2008-09-01      NA
## 141 2009-09-01 7583146
## 153 2010-09-01 7819472
# Compare July observations around the unusually low value.
power_data[
  power_month == 7 & power_year >= 2008 & power_year <= 2012,
  c("DATE", "KWH"),
  drop = FALSE
]
##           DATE      KWH
## 127 2008-07-01  7643987
## 139 2009-07-01  7713260
## 151 2010-07-01   770523
## 163 2011-07-01 10093343
## 175 2012-07-01  8886851

Fill the Missing Consumption Value

September 2008 is estimated using the average consumption for September in the preceding and following years. This preserves the annual seasonal comparison. The original data remain unchanged.

The unusually low July 2010 observation is retained for the initial analysis because its cause is unknown.

power_clean <- power_data

missing_index <- which(
  power_clean$DATE == as.Date("2008-09-01")
)

donor_values <- power_data$KWH[
  power_data$DATE %in% as.Date(
    c("2007-09-01", "2009-09-01")
  )
]

stopifnot(
  length(missing_index) == 1,
  is.na(power_clean$KWH[missing_index]),
  length(donor_values) == 2,
  all(is.finite(donor_values))
)

power_clean$KWH[missing_index] <- mean(donor_values)

power_clean[
  missing_index,
  c("DATE", "KWH"),
  drop = FALSE
]
##           DATE     KWH
## 129 2008-09-01 7625058
sum(is.na(power_clean$KWH))
## [1] 0

Create Training and Test Sets

Observations from January 1998 through December 2012 are used for training. The 12 months of 2013 are held out to compare forecast accuracy before forecasting 2014.

The September 2008 replacement uses only observations within the training period.

power_train <- power_clean[
  power_clean$DATE < as.Date("2013-01-01"),
]

power_test <- power_clean[
  power_clean$DATE >= as.Date("2013-01-01"),
]

power_train_ts <- ts(
  power_train$KWH,
  start = c(1998, 1),
  frequency = 12
)

range(power_train$DATE)
## [1] "1998-01-01" "2012-12-01"
range(power_test$DATE)
## [1] "2013-01-01" "2013-12-01"
c(
  Training_Months = nrow(power_train),
  Test_Months = nrow(power_test)
)
## Training_Months     Test_Months 
##             180              12
stopifnot(
  nrow(power_train) == 180,
  nrow(power_test) == 12,
  max(power_train$DATE) < min(power_test$DATE),
  !anyNA(power_train$KWH),
  !anyNA(power_test$KWH)
)

Evaluate Baseline Forecasts

Mean, naïve, and seasonal naïve forecasts are evaluated against the 12 months of 2013. Seasonal naïve uses consumption from the corresponding month of 2012.

Lower MAE and RMSE indicate better accuracy. Both measures are expressed in KWH.

power_actual <- power_test$KWH
power_h <- length(power_actual)

power_baseline_predictions <- list(
  Mean = rep(mean(power_train$KWH), power_h),
  Naive = rep(tail(power_train$KWH, 1), power_h),
  Seasonal_Naive = rep(
    tail(power_train$KWH, 12),
    length.out = power_h
  )
)

power_baseline_results <- do.call(
  rbind,
  lapply(names(power_baseline_predictions), function(method_name) {
    errors <- power_actual -
      power_baseline_predictions[[method_name]]

    data.frame(
      Method = method_name,
      MAE = mean(abs(errors)),
      RMSE = sqrt(mean(errors^2))
    )
  })
)

power_baseline_results <- power_baseline_results[
  order(power_baseline_results$RMSE),
]
rownames(power_baseline_results) <- NULL

print(
  power_baseline_results,
  digits = 8
)
##           Method        MAE      RMSE
## 1 Seasonal_Naive  618605.58 1035537.6
## 2          Naive 1501420.00 1861178.6
## 3           Mean 1544514.52 1961764.4

Fit and Evaluate ETS and ARIMA

ETS and seasonal ARIMA are fitted using only the training period. Their 12-month forecasts are compared with the observed consumption for 2013 and the baseline forecasts.

power_fits <- list(
  ETS = forecast::ets(power_train_ts),
  ARIMA = forecast::auto.arima(
    power_train_ts,
    seasonal = TRUE,
    stepwise = FALSE,
    approximation = FALSE
  )
)

power_model_results <- do.call(
  rbind,
  lapply(names(power_fits), function(method_name) {
    fc <- forecast::forecast(
      power_fits[[method_name]],
      h = power_h
    )

    errors <- power_actual - as.numeric(fc$mean)

    data.frame(
      Method = method_name,
      MAE = mean(abs(errors)),
      RMSE = sqrt(mean(errors^2))
    )
  })
)

power_accuracy <- rbind(
  power_baseline_results,
  power_model_results
)

power_accuracy <- power_accuracy[
  order(power_accuracy$RMSE),
]
rownames(power_accuracy) <- NULL

print(power_accuracy, digits = 8)
##           Method        MAE      RMSE
## 1 Seasonal_Naive  618605.58 1035537.6
## 2            ETS  691602.39 1068359.1
## 3          ARIMA 1006845.76 1541077.2
## 4          Naive 1501420.00 1861178.6
## 5           Mean 1544514.52 1961764.4
print(power_fits$ETS)
## ETS(M,N,M) 
## 
## Call:
## forecast::ets(y = power_train_ts)
## 
##   Smoothing parameters:
##     alpha = 0.1482 
##     gamma = 1e-04 
## 
##   Initial states:
##     l = 6139225.6768 
##     s = 0.9347 0.7516 0.8776 1.1947 1.2714 1.2015
##            0.9939 0.7683 0.8063 0.9156 1.0747 1.2097
## 
##   sigma:  0.1171
## 
##      AIC     AICc      BIC 
## 5813.678 5816.605 5861.573
print(power_fits$ARIMA)
## Series: power_train_ts 
## ARIMA(1,0,0)(2,1,0)[12] with drift 
## 
## Coefficients:
##          ar1     sar1     sar2     drift
##       0.1942  -0.8463  -0.6236  7120.818
## s.e.  0.0762   0.0679   0.0769  2803.504
## 
## sigma^2 = 6.784e+11:  log likelihood = -2532.6
## AIC=5075.19   AICc=5075.56   BIC=5090.81

Check Sensitivity to July 2010

July 2010 is unusually low compared with surrounding years. For a sensitivity analysis only, I replace it with the average of July 2009 and July 2011, then refit ETS and ARIMA.

All replacement values come from the training period. The original training data and the 2013 test observations remain unchanged.

power_train_adjusted <- power_train

july_index <- which(
  power_train_adjusted$DATE == as.Date("2010-07-01")
)

july_donors <- power_train$KWH[
  power_train$DATE %in% as.Date(
    c("2009-07-01", "2011-07-01")
  )
]

stopifnot(
  length(july_index) == 1,
  length(july_donors) == 2,
  all(is.finite(july_donors))
)

power_train_adjusted$KWH[july_index] <- mean(july_donors)

power_adjusted_ts <- ts(
  power_train_adjusted$KWH,
  start = c(1998, 1),
  frequency = 12
)

power_adjusted_fits <- list(
  ETS_Adjusted = forecast::ets(power_adjusted_ts),
  ARIMA_Adjusted = forecast::auto.arima(
    power_adjusted_ts,
    seasonal = TRUE,
    stepwise = FALSE,
    approximation = FALSE
  )
)

power_adjusted_results <- do.call(
  rbind,
  lapply(names(power_adjusted_fits), function(method_name) {
    fc <- forecast::forecast(
      power_adjusted_fits[[method_name]],
      h = power_h
    )

    errors <- power_actual - as.numeric(fc$mean)

    data.frame(
      Method = method_name,
      MAE = mean(abs(errors)),
      RMSE = sqrt(mean(errors^2))
    )
  })
)

power_sensitivity_accuracy <- rbind(
  power_accuracy,
  power_adjusted_results
)

power_sensitivity_accuracy <- power_sensitivity_accuracy[
  order(power_sensitivity_accuracy$RMSE),
]
rownames(power_sensitivity_accuracy) <- NULL

print(power_sensitivity_accuracy, digits = 8)
##           Method        MAE       RMSE
## 1 ARIMA_Adjusted  663570.99  973296.73
## 2 Seasonal_Naive  618605.58 1035537.61
## 3   ETS_Adjusted  675490.90 1047930.44
## 4            ETS  691602.39 1068359.10
## 5          ARIMA 1006845.76 1541077.16
## 6          Naive 1501420.00 1861178.59
## 7           Mean 1544514.52 1961764.43

Diagnose the Adjusted ARIMA Model

The adjusted ARIMA model has the lowest test RMSE, while seasonal naïve has the lowest MAE. The adjusted model depends on treating July 2010 as an exceptional observation rather than a typical seasonal value.

I inspect its residuals before deciding whether this modeling assumption is appropriate for the final forecast.

print(power_adjusted_fits$ARIMA_Adjusted)
## Series: power_adjusted_ts 
## ARIMA(0,0,3)(2,1,0)[12] with drift 
## 
## Coefficients:
##          ma1     ma2     ma3     sar1     sar2     drift
##       0.3628  0.0334  0.2336  -0.6786  -0.4243  8061.612
## s.e.  0.0762  0.0826  0.0704   0.0764   0.0812  3005.122
## 
## sigma^2 = 3.402e+11:  log likelihood = -2469.88
## AIC=4953.75   AICc=4954.45   BIC=4975.62
forecast::checkresiduals(
  power_adjusted_fits$ARIMA_Adjusted,
  lag = 24
)

## 
##  Ljung-Box test
## 
## data:  Residuals from ARIMA(0,0,3)(2,1,0)[12] with drift
## Q* = 11.534, df = 19, p-value = 0.9046
## 
## Model df: 5.   Total lags used: 24

Forecast Electricity Consumption for 2014

The adjusted ARIMA model achieved the lowest test RMSE and satisfactory residual autocorrelation diagnostics through lag 24. Seasonal naïve achieved the lowest MAE and remains a useful benchmark.

For the primary forecast, July 2010 is treated as an exceptional observation and replaced with the average of July 2009 and July 2011. This is a modeling assumption, not a verified data correction. The original observations remain preserved in power_data.

The selected ARIMA structure is refitted using the full historical period.

power_final_data <- power_clean

power_final_data$KWH[
  power_final_data$DATE == as.Date("2010-07-01")
] <- power_train_adjusted$KWH[july_index]

power_final_ts <- ts(
  power_final_data$KWH,
  start = c(1998, 1),
  frequency = 12
)

power_final_fit <- forecast::Arima(
  power_final_ts,
  order = c(0, 0, 3),
  seasonal = list(
    order = c(2, 1, 0),
    period = 12
  ),
  include.drift = TRUE,
  method = "ML"
)

power_final_fc <- forecast::forecast(
  power_final_fit,
  h = 12,
  level = c(80, 95)
)

power_2014_forecasts <- data.frame(
  DATE = seq.Date(
    as.Date("2014-01-01"),
    by = "month",
    length.out = 12
  ),
  Forecast_KWH = as.numeric(power_final_fc$mean),
  Lower80_KWH = as.numeric(power_final_fc$lower[, "80%"]),
  Upper80_KWH = as.numeric(power_final_fc$upper[, "80%"]),
  Lower95_KWH = as.numeric(power_final_fc$lower[, "95%"]),
  Upper95_KWH = as.numeric(power_final_fc$upper[, "95%"])
)

print(power_2014_forecasts, digits = 9)
##          DATE Forecast_KWH Lower80_KWH Upper80_KWH Lower95_KWH Upper95_KWH
## 1  2014-01-01  10410443.06  9608282.41 11212603.71  9183644.21 11637241.91
## 2  2014-02-01   8498503.38  7655574.64  9341432.12  7209355.12  9787651.64
## 3  2014-03-01   7250735.11  6407692.48  8093777.74  5961412.67  8540057.55
## 4  2014-04-01   6018370.77  5152810.51  6883931.02  4694610.59  7342130.94
## 5  2014-05-01   5950849.60  5085289.35  6816409.85  4627089.43  7274609.77
## 6  2014-06-01   8186803.42  7321243.17  9052363.67  6863043.25  9510563.59
## 7  2014-07-01   9406564.12  8541003.87 10272124.37  8082803.95 10730324.29
## 8  2014-08-01   9915220.53  9049660.28 10780780.79  8591460.36 11238980.70
## 9  2014-09-01   8450940.39  7585380.14  9316500.64  7127180.22  9774700.56
## 10 2014-10-01   5869716.94  5004156.68  6735277.19  4545956.77  7193477.11
## 11 2014-11-01   6126234.40  5260674.15  6991794.66  4802474.23  7449994.57
## 12 2014-12-01   8501216.10  7635655.85  9366776.35  7177455.93  9824976.27

Visualize the 2014 Forecast

The plot shows the final three years of recorded consumption, the ARIMA forecasts, and model-based 80% and 95% prediction intervals. The seasonal naïve benchmark repeats the corresponding monthly values from 2013.

Intervals are conditional on the model and data adjustments; they do not include uncertainty about whether July 2010 should have been adjusted.

power_recent <- tail(power_data, 36)

power_2014_benchmark <- tail(power_clean$KWH, 12)

plot(
  power_recent$DATE,
  power_recent$KWH,
  type = "l",
  xlim = range(c(power_recent$DATE, power_2014_forecasts$DATE)),
  ylim = range(c(
    power_recent$KWH,
    power_2014_forecasts$Lower95_KWH,
    power_2014_forecasts$Upper95_KWH,
    power_2014_benchmark
  )),
  main = "Residential Electricity Consumption: 2014 Forecast",
  xlab = "Year",
  ylab = "Electricity consumption (KWH)",
  lwd = 1.5
)

forecast_dates <- power_2014_forecasts$DATE

polygon(
  c(forecast_dates, rev(forecast_dates)),
  c(
    power_2014_forecasts$Lower95_KWH,
    rev(power_2014_forecasts$Upper95_KWH)
  ),
  col = "#DBEAFE",
  border = NA
)

polygon(
  c(forecast_dates, rev(forecast_dates)),
  c(
    power_2014_forecasts$Lower80_KWH,
    rev(power_2014_forecasts$Upper80_KWH)
  ),
  col = "#93C5FD",
  border = NA
)

lines(
  forecast_dates,
  power_2014_forecasts$Forecast_KWH,
  col = "#1D4ED8",
  lwd = 2
)

lines(
  forecast_dates,
  power_2014_benchmark,
  col = "#B45309",
  lty = 2,
  lwd = 1.5
)

abline(v = as.Date("2014-01-01"), lty = 3, col = "gray50")

legend(
  "topleft",
  legend = c(
    "Observed", "ARIMA forecast", "Seasonal naive",
    "80% interval", "95% interval"
  ),
  col = c("black", "#1D4ED8", "#B45309", "#93C5FD", "#DBEAFE"),
  lty = c(1, 1, 2, NA, NA),
  lwd = c(1.5, 2, 1.5, NA, NA),
  pch = c(NA, NA, NA, 15, 15),
  bty = "n",
  cex = 0.8
)

Export the 2014 Power Forecast

The export contains forecasts for all 12 months of 2014, along with model-based 80% and 95% prediction intervals. A seasonal naïve benchmark is included for comparison. All values are in KWH.

power_2014_forecasts$Seasonal_Naive_KWH <-
  as.numeric(power_2014_benchmark)

numeric_columns <- setdiff(
  names(power_2014_forecasts),
  "DATE"
)

stopifnot(
  nrow(power_2014_forecasts) == 12,
  !anyDuplicated(power_2014_forecasts$DATE),
  all(is.finite(
    as.matrix(power_2014_forecasts[numeric_columns])
  )),
  all(power_2014_forecasts$Forecast_KWH > 0),
  all(
    power_2014_forecasts$Lower95_KWH <=
      power_2014_forecasts$Lower80_KWH &
    power_2014_forecasts$Lower80_KWH <=
      power_2014_forecasts$Forecast_KWH &
    power_2014_forecasts$Forecast_KWH <=
      power_2014_forecasts$Upper80_KWH &
    power_2014_forecasts$Upper80_KWH <=
      power_2014_forecasts$Upper95_KWH
  )
)

write.csv(
  power_2014_forecasts,
  file = "Power_2014_forecasts.csv",
  row.names = FALSE
)

data.frame(
  ARIMA_Annual_Total_KWH = round(
    sum(power_2014_forecasts$Forecast_KWH), 2
  ),
  Seasonal_Naive_Annual_Total_KWH = round(
    sum(power_2014_forecasts$Seasonal_Naive_KWH), 2
  )
)
##   ARIMA_Annual_Total_KWH Seasonal_Naive_Annual_Total_KWH
## 1               94585598                        91420024
normalizePath("Power_2014_forecasts.csv")
## [1] "/Users/ummayrukiya/Desktop/Power_2014_forecasts.csv"

Part B Findings and Limitations

Residential electricity consumption shows a recurring annual seasonal pattern. September 2008 was missing and was estimated using the average of September 2007 and September 2009.

Seasonal naïve produced the lowest test MAE of 618,606 KWH. After adjusting the unusually low July 2010 observation, ARIMA produced the lowest test RMSE of 973,297 KWH. The adjusted ARIMA model was selected because RMSE places greater weight on large forecast errors.

The selected ARIMA(0,0,3)(2,1,0)[12] model with drift showed no significant residual autocorrelation through lag 24 in the training-period Ljung–Box test (p = 0.9046). It was refitted using the full history and forecasts approximately 94.59 million KWH for 2014, compared with 91.42 million KWH from seasonal naïve.

The July 2010 adjustment is a modeling assumption, not a confirmed correction. Prediction intervals are conditional on this assumption and the fitted model. Model comparisons use only one held-out year, so performance may differ in other years. The forecasts also do not explicitly incorporate weather, customer counts, or changes in electricity prices.

Part C — Hourly Waterflow Forecasting

Import the Waterflow Data

The two pipe datasets are imported separately because their timestamps may follow different patterns. Each will be organized into hourly observations, averaging multiple readings within the same hour, before evaluating stationarity and forecasting the following week.

water_base_url <- paste0(
  "https://raw.githubusercontent.com/UR-71/",
  "DATA624_Project1/main/"
)

pipe1_file <- tempfile(fileext = ".xlsx")
pipe2_file <- tempfile(fileext = ".xlsx")

download.file(
  url = paste0(water_base_url, "Waterflow_Pipe1.xlsx"),
  destfile = pipe1_file,
  mode = "wb",
  quiet = TRUE
)

download.file(
  url = paste0(water_base_url, "Waterflow_Pipe2.xlsx"),
  destfile = pipe2_file,
  mode = "wb",
  quiet = TRUE
)

pipe1_raw <- readxl::read_excel(pipe1_file)
pipe2_raw <- readxl::read_excel(pipe2_file)

cat("\nPIPE 1\n")
## 
## PIPE 1
print(head(pipe1_raw))
## # A tibble: 6 × 2
##   `Date Time` WaterFlow
##         <dbl>     <dbl>
## 1      42300.     23.4 
## 2      42300.     28.0 
## 3      42300.     23.1 
## 4      42300.     30.0 
## 5      42300.      6.00
## 6      42300.     15.9
str(pipe1_raw)
## tibble [1,000 × 2] (S3: tbl_df/tbl/data.frame)
##  $ Date Time: num [1:1000] 42300 42300 42300 42300 42300 ...
##  $ WaterFlow: num [1:1000] 23.4 28 23.1 30 6 ...
cat("\nPIPE 2\n")
## 
## PIPE 2
print(head(pipe2_raw))
## # A tibble: 6 × 2
##   `Date Time` WaterFlow
##         <dbl>     <dbl>
## 1      42300.      18.8
## 2      42300.      43.1
## 3      42300.      38.0
## 4      42300.      36.1
## 5      42300.      31.9
## 6      42300.      28.2
str(pipe2_raw)
## tibble [1,000 × 2] (S3: tbl_df/tbl/data.frame)
##  $ Date Time: num [1:1000] 42300 42300 42300 42300 42300 ...
##  $ WaterFlow: num [1:1000] 18.8 43.1 38 36.1 31.9 ...

Convert Waterflow Timestamps

Excel timestamps are converted from days since December 30, 1899, into date-time values. UTC is used only as a clock convention; the actual recording timezone is unknown.

pipe1_data <- data.frame(
  DateTime = as.POSIXct(
    pipe1_raw[["Date Time"]] * 86400,
    origin = "1899-12-30",
    tz = "UTC"
  ),
  WaterFlow = pipe1_raw$WaterFlow
)

pipe2_data <- data.frame(
  DateTime = as.POSIXct(
    pipe2_raw[["Date Time"]] * 86400,
    origin = "1899-12-30",
    tz = "UTC"
  ),
  WaterFlow = pipe2_raw$WaterFlow
)

pipe1_data <- pipe1_data[order(pipe1_data$DateTime), ]
pipe2_data <- pipe2_data[order(pipe2_data$DateTime), ]

cat("\nPIPE 1\n")
## 
## PIPE 1
print(head(pipe1_data))
##              DateTime WaterFlow
## 1 2015-10-23 00:24:06 23.369599
## 2 2015-10-23 00:40:02 28.002881
## 3 2015-10-23 00:53:51 23.065895
## 4 2015-10-23 00:55:40 29.972809
## 5 2015-10-23 01:19:17  5.997953
## 6 2015-10-23 01:23:58 15.935223
print(range(pipe1_data$DateTime))
## [1] "2015-10-23 00:24:06 UTC" "2015-11-01 23:35:43 UTC"
print(colSums(is.na(pipe1_data)))
##  DateTime WaterFlow 
##         0         0
cat("\nPIPE 2\n")
## 
## PIPE 2
print(head(pipe2_data))
##              DateTime WaterFlow
## 1 2015-10-23 01:00:00  18.81079
## 2 2015-10-23 01:59:59  43.08703
## 3 2015-10-23 03:00:00  37.98770
## 4 2015-10-23 04:00:00  36.12038
## 5 2015-10-23 04:59:59  31.85126
## 6 2015-10-23 06:00:00  28.23809
print(range(pipe2_data$DateTime))
## [1] "2015-10-23 01:00:00 UTC" "2015-12-03 16:00:00 UTC"
print(colSums(is.na(pipe2_data)))
##  DateTime WaterFlow 
##         0         0

Aggregate Readings by Hour

Timestamps are rounded to the nearest second to remove Excel conversion precision artifacts, then assigned to the beginning of their hour. Multiple readings within an hour are averaged.

A complete hourly sequence is created for each pipe. Hours without readings remain missing, not zero.

make_hourly <- function(pipe_data) {
  rounded_seconds <- round(as.numeric(pipe_data$DateTime))

  hour_start <- as.POSIXct(
    floor(rounded_seconds / 3600) * 3600,
    origin = "1970-01-01",
    tz = "UTC"
  )

  hourly_mean <- aggregate(
    list(WaterFlow = pipe_data$WaterFlow),
    by = list(DateTime = hour_start),
    FUN = mean
  )

  hourly_count <- aggregate(
    list(Readings = rep(1L, nrow(pipe_data))),
    by = list(DateTime = hour_start),
    FUN = sum
  )

  hourly_observed <- merge(
    hourly_mean, hourly_count,
    by = "DateTime"
  )

  complete_hours <- data.frame(
    DateTime = seq(
      min(hour_start),
      max(hour_start),
      by = "hour"
    )
  )

  result <- merge(
    complete_hours, hourly_observed,
    by = "DateTime",
    all.x = TRUE,
    sort = TRUE
  )

  result$Readings[is.na(result$Readings)] <- 0L
  result
}

pipe1_hourly <- make_hourly(pipe1_data)
pipe2_hourly <- make_hourly(pipe2_data)

for (pipe_name in c("Pipe1", "Pipe2")) {
  hourly <- if (pipe_name == "Pipe1") pipe1_hourly else pipe2_hourly

  cat("\n", pipe_name, "\n")
  print(head(hourly))
  print(c(
    Hours = nrow(hourly),
    Missing_Hours = sum(is.na(hourly$WaterFlow)),
    Total_Readings = sum(hourly$Readings)
  ))
}
## 
##  Pipe1 
##              DateTime WaterFlow Readings
## 1 2015-10-23 00:00:00  26.10280        4
## 2 2015-10-23 01:00:00  18.85202        5
## 3 2015-10-23 02:00:00  15.15857        2
## 4 2015-10-23 03:00:00  23.07886        5
## 5 2015-10-23 04:00:00  15.48219        2
## 6 2015-10-23 05:00:00  22.72539        9
##          Hours  Missing_Hours Total_Readings 
##            240              4           1000 
## 
##  Pipe2 
##              DateTime WaterFlow Readings
## 1 2015-10-23 01:00:00  18.81079        1
## 2 2015-10-23 02:00:00  43.08703        1
## 3 2015-10-23 03:00:00  37.98770        1
## 4 2015-10-23 04:00:00  36.12038        1
## 5 2015-10-23 05:00:00  31.85126        1
## 6 2015-10-23 06:00:00  28.23809        1
##          Hours  Missing_Hours Total_Readings 
##           1000              0           1000

Examine Hourly Waterflow

Each pipe is plotted over its own recording period. Missing hours appear as gaps. Waterflow units were not specified in the assignment.

plot(
  pipe1_hourly$DateTime,
  pipe1_hourly$WaterFlow,
  type = "l",
  col = "steelblue",
  main = "Pipe 1 — Hourly Mean Waterflow",
  xlab = "Date",
  ylab = "Waterflow (source units)"
)

plot(
  pipe2_hourly$DateTime,
  pipe2_hourly$WaterFlow,
  type = "l",
  col = "steelblue",
  main = "Pipe 2 — Hourly Waterflow",
  xlab = "Date",
  ylab = "Waterflow (source units)"
)

# Display the missing hours for Pipe 1.
pipe1_hourly[
  is.na(pipe1_hourly$WaterFlow),
  ,
  drop = FALSE
]
##                DateTime WaterFlow Readings
## 114 2015-10-27 17:00:00        NA        0
## 121 2015-10-28 00:00:00        NA        0
## 222 2015-11-01 05:00:00        NA        0
## 226 2015-11-01 09:00:00        NA        0

Fill Missing Hours in Pipe 1

Four hourly slots contain no readings. For modeling, these gaps are filled by linear interpolation between surrounding observed hourly means. These estimates are not actual measurements.

Pipe 2 requires no imputation. Missing Pipe 1 hours will be excluded from forecast-accuracy calculations if they fall in the test period.

pipe1_clean <- pipe1_hourly
pipe2_clean <- pipe2_hourly

missing_hours <- is.na(pipe1_hourly$WaterFlow)
observed_hours <- !missing_hours

pipe1_clean$WaterFlow <- approx(
  x = as.numeric(pipe1_hourly$DateTime[observed_hours]),
  y = pipe1_hourly$WaterFlow[observed_hours],
  xout = as.numeric(pipe1_hourly$DateTime),
  method = "linear",
  rule = 1
)$y

pipe1_imputation_log <- data.frame(
  DateTime = pipe1_hourly$DateTime[missing_hours],
  Estimated_WaterFlow = pipe1_clean$WaterFlow[missing_hours]
)

print(pipe1_imputation_log)
##              DateTime Estimated_WaterFlow
## 1 2015-10-27 17:00:00            22.49977
## 2 2015-10-28 00:00:00            18.79852
## 3 2015-11-01 05:00:00            16.83043
## 4 2015-11-01 09:00:00            15.38992
c(
  Pipe1_Missing = sum(is.na(pipe1_clean$WaterFlow)),
  Pipe2_Missing = sum(is.na(pipe2_clean$WaterFlow))
)
## Pipe1_Missing Pipe2_Missing 
##             0             0
stopifnot(
  all(is.finite(pipe1_clean$WaterFlow)),
  all(is.finite(pipe2_clean$WaterFlow))
)

Assess Stationarity

The KPSS-based differencing check assesses whether differencing is needed to stabilize the series’ level. Results are interpreted alongside the time plots and autocorrelation plots.

Pipe 1 includes four interpolated values, which may slightly affect these diagnostics.

waterflow_ndiffs <- data.frame(
  Pipe = c("Pipe1", "Pipe2"),
  Recommended_Differences = c(
    forecast::ndiffs(
      pipe1_clean$WaterFlow,
      alpha = 0.05,
      test = "kpss"
    ),
    forecast::ndiffs(
      pipe2_clean$WaterFlow,
      alpha = 0.05,
      test = "kpss"
    )
  )
)

print(waterflow_ndiffs)
##    Pipe Recommended_Differences
## 1 Pipe1                       0
## 2 Pipe2                       0
acf(
  pipe1_clean$WaterFlow,
  lag.max = 48,
  main = "Pipe 1 — Hourly Waterflow ACF"
)

acf(
  pipe2_clean$WaterFlow,
  lag.max = 48,
  main = "Pipe 2 — Hourly Waterflow ACF"
)

## Create Waterflow Training and Test Sets

The final 168 hourly slots of each pipe are reserved for evaluation, matching the required one-week forecast horizon.

The split uses the original hourly data. Any training gaps will be filled using training observations only, and missing test values will not be scored. This prevents future observations from influencing training-data preparation.

water_h <- 168L

pipe1_cutoff <- nrow(pipe1_hourly) - water_h
pipe2_cutoff <- nrow(pipe2_hourly) - water_h

pipe1_train <- pipe1_hourly[seq_len(pipe1_cutoff), ]
pipe1_test <- pipe1_hourly[
  seq.int(pipe1_cutoff + 1L, nrow(pipe1_hourly)),
]

pipe2_train <- pipe2_hourly[seq_len(pipe2_cutoff), ]
pipe2_test <- pipe2_hourly[
  seq.int(pipe2_cutoff + 1L, nrow(pipe2_hourly)),
]

water_split_summary <- data.frame(
  Pipe = c("Pipe1", "Pipe2"),
  Training_Hours = c(nrow(pipe1_train), nrow(pipe2_train)),
  Test_Hours = c(nrow(pipe1_test), nrow(pipe2_test)),
  Missing_Training = c(
    sum(is.na(pipe1_train$WaterFlow)),
    sum(is.na(pipe2_train$WaterFlow))
  ),
  Observed_Test_Hours = c(
    sum(!is.na(pipe1_test$WaterFlow)),
    sum(!is.na(pipe2_test$WaterFlow))
  )
)

print(water_split_summary)
##    Pipe Training_Hours Test_Hours Missing_Training Observed_Test_Hours
## 1 Pipe1             72        168                0                 164
## 2 Pipe2            832        168                0                 168
stopifnot(
  nrow(pipe1_test) == water_h,
  nrow(pipe2_test) == water_h,
  max(pipe1_train$DateTime) < min(pipe1_test$DateTime),
  max(pipe2_train$DateTime) < min(pipe2_test$DateTime)
)

Evaluate Waterflow Baselines

Baseline forecasts are evaluated over the held-out week. Only observed test values are scored; interpolated test values are not used.

Daily seasonal naïve repeats the final 24 training hours. It is included to test whether repeating a daily pattern improves accuracy.

water_training_sets <- list(
  Pipe1 = pipe1_train,
  Pipe2 = pipe2_train
)

water_test_sets <- list(
  Pipe1 = pipe1_test,
  Pipe2 = pipe2_test
)

water_baseline_results <- data.frame()

for (pipe_name in names(water_training_sets)) {
  train_values <- water_training_sets[[pipe_name]]$WaterFlow
  actual <- water_test_sets[[pipe_name]]$WaterFlow

  observed <- !is.na(actual)

  stopifnot(
    all(is.finite(train_values)),
    sum(observed) > 0
  )

  predictions <- list(
    Mean = rep(mean(train_values), length(actual)),
    Naive = rep(tail(train_values, 1), length(actual)),
    Daily_Seasonal_Naive = rep(
      tail(train_values, 24),
      length.out = length(actual)
    )
  )

  for (method_name in names(predictions)) {
    errors <- actual[observed] -
      predictions[[method_name]][observed]

    water_baseline_results <- rbind(
      water_baseline_results,
      data.frame(
        Pipe = pipe_name,
        Method = method_name,
        Scored_Hours = sum(observed),
        MAE = mean(abs(errors)),
        RMSE = sqrt(mean(errors^2))
      )
    )
  }
}

water_baseline_results <- water_baseline_results[
  order(water_baseline_results$Pipe, water_baseline_results$RMSE),
]
rownames(water_baseline_results) <- NULL

print(water_baseline_results, digits = 6)
##    Pipe               Method Scored_Hours      MAE     RMSE
## 1 Pipe1                Naive          164  3.25800  4.18726
## 2 Pipe1                 Mean          164  3.27470  4.21544
## 3 Pipe1 Daily_Seasonal_Naive          164  4.75913  6.04837
## 4 Pipe2                 Mean          168 13.74957 16.73292
## 5 Pipe2                Naive          168 13.76086 16.73593
## 6 Pipe2 Daily_Seasonal_Naive          168 17.60502 21.57002

Compare Additional Waterflow Models

Simple exponential smoothing and nonseasonal ARIMA are compared with the baselines. No seasonal pattern is imposed because the plots did not show clear repeating seasonality.

All models use training observations only, and missing test hours are excluded from scoring.

water_model_fits <- list()
water_model_results <- data.frame()

for (pipe_name in names(water_training_sets)) {
  train_values <- water_training_sets[[pipe_name]]$WaterFlow
  actual <- water_test_sets[[pipe_name]]$WaterFlow
  observed <- !is.na(actual)

  train_ts <- ts(train_values, frequency = 1)

  fits <- list(
    SES = forecast::ets(train_ts, model = "ANN"),
    ARIMA = forecast::auto.arima(
      train_ts,
      seasonal = FALSE,
      stepwise = FALSE,
      approximation = FALSE
    )
  )

  water_model_fits[[pipe_name]] <- fits

  for (method_name in names(fits)) {
    fc <- forecast::forecast(
      fits[[method_name]],
      h = length(actual)
    )

    errors <- actual[observed] -
      as.numeric(fc$mean)[observed]

    water_model_results <- rbind(
      water_model_results,
      data.frame(
        Pipe = pipe_name,
        Method = method_name,
        Scored_Hours = sum(observed),
        MAE = mean(abs(errors)),
        RMSE = sqrt(mean(errors^2))
      )
    )
  }
}

water_accuracy <- rbind(
  water_baseline_results,
  water_model_results
)

water_accuracy <- water_accuracy[
  order(water_accuracy$Pipe, water_accuracy$RMSE),
]
rownames(water_accuracy) <- NULL

print(water_accuracy, digits = 6)
##     Pipe               Method Scored_Hours      MAE     RMSE
## 1  Pipe1                Naive          164  3.25800  4.18726
## 2  Pipe1                 Mean          164  3.27470  4.21544
## 3  Pipe1                ARIMA          164  3.27470  4.21544
## 4  Pipe1                  SES          164  3.27477  4.21553
## 5  Pipe1 Daily_Seasonal_Naive          164  4.75913  6.04837
## 6  Pipe2                 Mean          168 13.74957 16.73292
## 7  Pipe2                  SES          168 13.74979 16.73297
## 8  Pipe2                Naive          168 13.76086 16.73593
## 9  Pipe2                ARIMA          168 13.84148 16.82356
## 10 Pipe2 Daily_Seasonal_Naive          168 17.60502 21.57002

Diagnose the Selected Waterflow Methods

Naïve achieved the lowest test MAE and RMSE for Pipe 1. Mean achieved the lowest values for Pipe 2. The differences between several methods were small, and Pipe 1’s evaluation used only 72 training hours, so the rankings should be interpreted cautiously.

Residual diagnostics assess whether the selected methods leave detectable autocorrelation.

water_selected_training <- list(
  Pipe1 = forecast::naive(
    ts(pipe1_train$WaterFlow),
    h = water_h
  ),
  Pipe2 = forecast::meanf(
    ts(pipe2_train$WaterFlow),
    h = water_h
  )
)

cat("\nPIPE 1 — NAIVE\n")
## 
## PIPE 1 — NAIVE
forecast::checkresiduals(
  water_selected_training$Pipe1,
  lag = 24
)

## 
##  Ljung-Box test
## 
## data:  Residuals from Naive method
## Q* = 61.1, df = 24, p-value = 4.46e-05
## 
## Model df: 0.   Total lags used: 24
cat("\nPIPE 2 — MEAN\n")
## 
## PIPE 2 — MEAN
forecast::checkresiduals(
  water_selected_training$Pipe2,
  lag = 24
)

## 
##  Ljung-Box test
## 
## data:  Residuals from Mean
## Q* = 34.342, df = 24, p-value = 0.07877
## 
## Model df: 0.   Total lags used: 24

Check the Pipe 1 Mean Forecast

The naïve method narrowly outperformed the mean on the held-out week, but its residuals showed strong negative lag-1 autocorrelation. This suggests that treating each latest fluctuation as a persistent level may be inappropriate.

The mean forecast had only slightly higher test error. I examine its training residuals as an alternative.

pipe1_mean_training <- forecast::meanf(
  ts(pipe1_train$WaterFlow),
  h = water_h
)

forecast::checkresiduals(
  pipe1_mean_training,
  lag = 24
)

## 
##  Ljung-Box test
## 
## data:  Residuals from Mean
## Q* = 22.161, df = 24, p-value = 0.5697
## 
## Model df: 0.   Total lags used: 24

Forecast Waterflow One Week Ahead

Mean forecasts are selected for both pipes. Pipe 2’s mean had the lowest test RMSE. For Pipe 1, the mean’s RMSE was only about 0.7% above naïve, while its residual diagnostics were substantially better.

These forecasts estimate the expected hourly level, not individual random fluctuations. Prediction intervals assume independent, approximately normally distributed errors and do not account for imputation uncertainty or varying numbers of readings per hour.

water_final_fc <- list(
  Pipe1 = forecast::meanf(
    ts(pipe1_clean$WaterFlow),
    h = 168,
    level = c(80, 95)
  ),
  Pipe2 = forecast::meanf(
    ts(pipe2_clean$WaterFlow),
    h = 168,
    level = c(80, 95)
  )
)

water_full_data <- list(
  Pipe1 = pipe1_clean,
  Pipe2 = pipe2_clean
)

water_forecast_tables <- list()

for (pipe_name in names(water_final_fc)) {
  fc <- water_final_fc[[pipe_name]]
  history <- water_full_data[[pipe_name]]

  future_hours <- seq(
    from = max(history$DateTime) + 3600,
    by = "hour",
    length.out = 168
  )

  water_forecast_tables[[pipe_name]] <- data.frame(
    DateTime = future_hours,
    Pipe = pipe_name,
    Forecast = as.numeric(fc$mean),
    Lower80 = as.numeric(fc$lower[, "80%"]),
    Upper80 = as.numeric(fc$upper[, "80%"]),
    Lower95 = as.numeric(fc$lower[, "95%"]),
    Upper95 = as.numeric(fc$upper[, "95%"])
  )
}

for (pipe_name in names(water_forecast_tables)) {
  cat("\n", pipe_name, "\n")
  print(head(water_forecast_tables[[pipe_name]], 3))
  print(range(water_forecast_tables[[pipe_name]]$DateTime))
}
## 
##  Pipe1 
##              DateTime  Pipe Forecast  Lower80  Upper80  Lower95  Upper95
## 1 2015-11-02 00:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## 2 2015-11-02 01:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## 3 2015-11-02 02:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## [1] "2015-11-02 00:00:00 UTC" "2015-11-08 23:00:00 UTC"
## 
##  Pipe2 
##              DateTime  Pipe Forecast  Lower80  Upper80  Lower95  Upper95
## 1 2015-12-03 17:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## 2 2015-12-03 18:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## 3 2015-12-03 19:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## [1] "2015-12-03 17:00:00 UTC" "2015-12-10 16:00:00 UTC"

Visualize One-Week Waterflow Forecasts

Each plot shows the final 72 historical hours followed by 168 forecast hours. Shaded regions represent model-based prediction intervals for individual future hourly values.

for (pipe_name in names(water_forecast_tables)) {
  history <- tail(water_full_data[[pipe_name]], 72)
  fc <- water_forecast_tables[[pipe_name]]

  plot(
    history$DateTime,
    history$WaterFlow,
    type = "l",
    xlim = range(c(history$DateTime, fc$DateTime)),
    ylim = range(c(
      history$WaterFlow, fc$Lower95, fc$Upper95
    )),
    main = paste(pipe_name, "— One-Week Waterflow Forecast"),
    xlab = "Date",
    ylab = "Waterflow (source units)",
    lwd = 1.2
  )

  polygon(
    c(fc$DateTime, rev(fc$DateTime)),
    c(fc$Lower95, rev(fc$Upper95)),
    col = "#DBEAFE",
    border = NA
  )

  polygon(
    c(fc$DateTime, rev(fc$DateTime)),
    c(fc$Lower80, rev(fc$Upper80)),
    col = "#93C5FD",
    border = NA
  )

  lines(
    fc$DateTime, fc$Forecast,
    col = "#1D4ED8", lwd = 2
  )

  abline(
    v = as.numeric(fc$DateTime[1]),
    lty = 2,
    col = "gray50"
  )

  legend(
    "topleft",
    legend = c(
      "History (gaps interpolated)",
      "Mean forecast", "80% interval", "95% interval"
    ),
    col = c("black", "#1D4ED8", "#93C5FD", "#DBEAFE"),
    lty = c(1, 1, NA, NA),
    lwd = c(1.2, 2, NA, NA),
    pch = c(NA, NA, 15, 15),
    bty = "n",
    cex = 0.8
  )
}

## Export Waterflow Results

Each forecast file contains 168 hourly predictions with 80% and 95% prediction intervals. The hourly-data export preserves observed hourly means, reading counts, and flags identifying interpolated hours.

Timestamps follow the UTC clock convention used throughout this analysis; the original recording timezone is unknown.

for (pipe_name in names(water_forecast_tables)) {
  fc <- water_forecast_tables[[pipe_name]]

  stopifnot(
    nrow(fc) == 168,
    !anyDuplicated(fc$DateTime),
    all(diff(as.numeric(fc$DateTime)) == 3600),
    all(is.finite(as.matrix(fc[, 3:7]))),
    all(
      fc$Lower95 <= fc$Lower80 &
      fc$Lower80 <= fc$Forecast &
      fc$Forecast <= fc$Upper80 &
      fc$Upper80 <= fc$Upper95
    )
  )

  export_fc <- fc
  export_fc$DateTime <- format(
    fc$DateTime,
    format = "%Y-%m-%d %H:%M:%S",
    tz = "UTC"
  )

  write.csv(
    export_fc,
    file = paste0(pipe_name, "_168_hour_forecasts.csv"),
    row.names = FALSE
  )
}

hourly_export <- rbind(
  data.frame(
    Pipe = "Pipe1",
    DateTime = pipe1_hourly$DateTime,
    Observed_Hourly_Mean = pipe1_hourly$WaterFlow,
    Modeling_Value = pipe1_clean$WaterFlow,
    Readings = pipe1_hourly$Readings,
    Imputed = is.na(pipe1_hourly$WaterFlow)
  ),
  data.frame(
    Pipe = "Pipe2",
    DateTime = pipe2_hourly$DateTime,
    Observed_Hourly_Mean = pipe2_hourly$WaterFlow,
    Modeling_Value = pipe2_clean$WaterFlow,
    Readings = pipe2_hourly$Readings,
    Imputed = is.na(pipe2_hourly$WaterFlow)
  )
)

hourly_export$DateTime <- format(
  hourly_export$DateTime,
  format = "%Y-%m-%d %H:%M:%S",
  tz = "UTC"
)

write.csv(
  hourly_export,
  file = "Waterflow_Hourly_Data.csv",
  row.names = FALSE,
  na = ""
)

output_files <- c(
  "Pipe1_168_hour_forecasts.csv",
  "Pipe2_168_hour_forecasts.csv",
  "Waterflow_Hourly_Data.csv"
)

print(data.frame(
  File = output_files,
  Saved = file.exists(output_files)
))
##                           File Saved
## 1 Pipe1_168_hour_forecasts.csv  TRUE
## 2 Pipe2_168_hour_forecasts.csv  TRUE
## 3    Waterflow_Hourly_Data.csv  TRUE

Part C Findings and Limitations

Pipe 1’s irregular readings were averaged into 240 hourly slots. Four hours had no readings and were interpolated for the final modeling dataset. Pipe 2 contained 1,000 consecutive hourly observations and required no imputation.

The KPSS-based check recommended no differencing for either series. Together with the time plots and mostly small autocorrelations, this supports treating the series as approximately stationary over their observed periods, rather than proving stationarity.

Mean forecasts were selected for both pipes. For Pipe 1, naïve had slightly lower test RMSE, but its residuals showed strong negative lag-1 autocorrelation. The mean’s RMSE was only about 0.7% higher, and its training residuals showed no significant autocorrelation through lag 24 (p = 0.5697). For Pipe 2, the mean had the lowest test RMSE; its corresponding diagnostic p-value was 0.07877.

The expected hourly waterflow is approximately 19.87 for Pipe 1 and 39.56 for Pipe 2, in the source units. Each forecast covers 168 hours beyond that pipe’s own endpoint. These forecasts estimate stable expected levels, not individual random fluctuations.

Pipe 1’s short history limits confidence: only 72 hours were available for training when holding out one week. Missing test hours were excluded from scoring. Prediction intervals do not account for imputation uncertainty or varying reading counts within each hour. Recording units and the actual timezone were unspecified.