This project answers the two required questions: how much cash may be withdrawn from four ATMs in May 2010, and how much residential electricity may be used in each month of 2014.

Part A: Forecasting ATM cash withdrawals in May 2010

A1. Read and check the ATM data

I first import the spreadsheet, convert dates and cash amounts, and remove rows that do not identify an ATM. The Cash values are in hundreds of dollars, so I simply multiply by 100 when reporting US dollars.

raw_atm <- read_excel("ATM624Data.xlsx", sheet="ATM Data")
stopifnot(all(c("DATE", "ATM", "Cash") %in% names(raw_atm)))

if (inherits(raw_atm$DATE, "Date") || inherits(raw_atm$DATE, "POSIXt")) {
  raw_atm$DATE <- as.Date(raw_atm$DATE)
} else if (is.numeric(raw_atm$DATE)) {
  raw_atm$DATE <- as.Date(raw_atm$DATE, origin="1899-12-30")
} else {
  raw_atm$DATE <- as.Date(raw_atm$DATE)
}

raw_atm$ATM <- as.character(raw_atm$ATM)
raw_atm$Cash <- as.numeric(raw_atm$Cash)
atm <- raw_atm[!is.na(raw_atm$ATM) &
               raw_atm$ATM %in% paste0("ATM", 1:4), ]
atm <- as.data.frame(atm[order(atm$ATM, atm$DATE), ])
row.names(atm) <- NULL

stopifnot(nrow(atm) == 1460, !anyDuplicated(atm[c("DATE", "ATM")]))
cat("Historical rows:", nrow(atm), "\n")

## Historical rows: 1460

cat("Excluded unlabeled rows:", nrow(raw_atm) - nrow(atm), "\n")

## Excluded unlabeled rows: 14

cat("Dates:", as.character(min(atm$DATE)), "to",
    as.character(max(atm$DATE)), "\n")

## Dates: 2009-05-01 to 2010-04-30

For each ATM, I count missing and zero withdrawals and calculate a few descriptive statistics. This helps avoid treating all four machines as if they behave the same way.

atm_summary <- do.call(rbind, lapply(split(atm, atm$ATM), function(d) {
  data.frame(ATM=d$ATM[1], Days=nrow(d),
             Missing=sum(is.na(d$Cash)),
             Zero_Days=sum(d$Cash == 0, na.rm=TRUE),
             Mean_USD=mean(d$Cash, na.rm=TRUE) * 100,
             Median_USD=median(d$Cash, na.rm=TRUE) * 100,
             Max_USD=max(d$Cash, na.rm=TRUE) * 100)
}))
row.names(atm_summary) <- NULL
kable(atm_summary, digits=0, row.names=FALSE,
      caption="Historical daily withdrawals by ATM (dollars)")

Historical daily withdrawals by ATM (dollars)
ATM	Days	Missing	Zero_Days	Mean_USD	Median_USD	Max_USD
ATM1	365	3	0	8389	9100	18000
ATM2	365	2	2	6258	6700	14700
ATM3	365	0	362	72	0	9600
ATM4	365	0	0	47404	40384	1091976

The source data include five missing withdrawal values for ATM1 and ATM2, 362 zero days for ATM3, and an exceptionally large ATM4 withdrawal on February 9, 2010. ATM3 has only three positive values, all of which match ATM1 on the same dates. The available data do not provide enough information to determine whether ATM3 was activated or whether these entries are incorrect.

A2. Look at historical patterns

Historical chart. I plot daily withdrawals in dollars. Each ATM has its own vertical scale because their withdrawal amounts differ significantly.

atm$Cash_USD <- atm$Cash * 100

ggplot(atm, aes(x=DATE, y=Cash_USD)) +
  geom_line(linewidth=0.35, na.rm=TRUE) +
  facet_wrap(~ATM, scales="free_y", ncol=2) +
  labs(title="Daily ATM withdrawals, May 2009 to April 2010",
       x="Date", y="Withdrawals (USD)") +
  theme_minimal()

Weekday chart. The chart compares typical withdrawals for each weekday over the most recent four weeks. ATM3 is excluded because its typical withdrawal is zero and its operational status cannot be confirmed from the available data.

recent_atm <- atm[atm$DATE >= as.Date("2010-04-03") &
                  atm$DATE <= as.Date("2010-04-30") &
                  atm$ATM != "ATM3", ]
recent_atm$Weekday <- factor(weekday_number(recent_atm$DATE),
                             levels=1:7, labels=c("Mon", "Tue", "Wed",
                                                 "Thu", "Fri", "Sat", "Sun"))
weekday_typical <- aggregate(Cash_USD ~ ATM + Weekday,
                             data=recent_atm, FUN=median)

ggplot(weekday_typical, aes(x=Weekday, y=Cash_USD, group=ATM)) +
  geom_line() + geom_point() +
  facet_wrap(~ATM, scales="free_y", ncol=1) +
  scale_y_continuous(limits=c(0, NA)) +
  labs(title="Recent median withdrawals by weekday",
       subtitle="Four weeks ending April 30, 2010",
       x="Weekday", y="Median withdrawals (USD)") +
  theme_minimal()

A3. Compare simple forecasting methods

I consider five methods: repeat the last day, repeat the last seven days, use the mean of each weekday over the last four weeks, use the median over those four weeks, or use the mean for each weekday over the entire history. The median is less sensitive to extreme values. I do not use ARIMA or more complex methods because these simpler methods are easier to explain and can be compared directly on past dates.

Prediction function. This function uses only data up to a chosen cutoff date and predicts the requested future dates. Keeping the cutoff explicit prevents future information from being used during backtesting.

atm_methods <- c("last_day", "last_week", "weekday_mean_4w",
                 "weekday_median_4w", "weekday_mean_all")

predict_atm <- function(history, cutoff, dates, method) {
  stopifnot(method %in% atm_methods)
  d <- history[history$DATE <= cutoff & !is.na(history$Cash), ]
  stopifnot(nrow(d) > 0)

  if (method == "last_day") {
    return(rep(tail(d$Cash, 1), length(dates)))
  }

  if (method == "last_week") {
    last_week_dates <- seq(cutoff - 6, cutoff, by="day")
    last_week_values <- d$Cash[match(last_week_dates, d$DATE)]
    stopifnot(!anyNA(last_week_values))
    return(rep(last_week_values, length.out=length(dates)))
  }

  if (method != "weekday_mean_all") {
    d <- d[d$DATE > cutoff - 28, ]
  }

  target_weekdays <- weekday_number(dates)
  sapply(seq_along(dates), function(i) {
    same_weekday <- d$Cash[weekday_number(d$DATE) == target_weekdays[i]]
    stopifnot(length(same_weekday) > 0)
    if (method == "weekday_median_4w") median(same_weekday)
    else mean(same_weekday)
  })
}

Backtesting. I simulate forecasts for February, March, and April 2010, using only observations available before each month. I compare models using mean absolute error (MAE), which measures average daily forecasting error. For selecting the May model, I focus on the more recent March–April results.

atm_test_months <- as.Date(c("2010-02-01", "2010-03-01", "2010-04-01"))
atm_errors <- list()
k <- 1

for (a in c("ATM1", "ATM2", "ATM4")) {
  d <- atm[atm$ATM == a, ]
  # Index the Date vector so R keeps the Date class inside the loop.
  for (j in seq_along(atm_test_months)) {
    start_day <- atm_test_months[j]
    end_day <- seq(start_day, by="month", length.out=2)[2] - 1
    days <- seq(start_day, end_day, by="day")
    actual <- d$Cash[match(days, d$DATE)]
    stopifnot(all(is.finite(actual)))
    for (method in atm_methods) {
      forecast <- predict_atm(d, start_day - 1, days, method)
      atm_errors[[k]] <- data.frame(
        ATM=a, Month=format(start_day, "%Y-%m"), Method=method,
        Abs_Error=abs(actual - forecast))
      k <- k + 1
    }
  }
}

atm_backtest <- do.call(rbind, atm_errors)
recent_errors <- atm_backtest[atm_backtest$Month %in% c("2010-03", "2010-04"), ]
atm_scores <- aggregate(Abs_Error ~ ATM + Method, recent_errors, mean)
names(atm_scores)[3] <- "MAE_Hundreds"
atm_scores <- atm_scores[order(atm_scores$ATM, atm_scores$MAE_Hundreds), ]
row.names(atm_scores) <- NULL
kable(atm_scores, digits=2, row.names=FALSE,
      caption="March–April validation error (hundreds of dollars per day)")

March–April validation error (hundreds of dollars per day)
ATM	Method	MAE_Hundreds
ATM1	last_week	16.02
ATM1	weekday_mean_4w	17.47
ATM1	weekday_median_4w	20.35
ATM1	weekday_mean_all	34.76
ATM1	last_day	62.11
ATM2	last_week	15.18
ATM2	weekday_mean_4w	25.36
ATM2	weekday_median_4w	26.65
ATM2	last_day	41.49
ATM2	weekday_mean_all	52.75
ATM4	weekday_median_4w	241.63
ATM4	last_week	296.59
ATM4	weekday_mean_all	319.52
ATM4	weekday_mean_4w	443.43
ATM4	last_day	554.56

ATM1 and ATM2 have the lowest recent MAE with the seven-day method, while ATM4 does best with the recent weekday median. These models are simple, but they reflect the different behavior of the machines.

A4. Forecast May 2010

Daily forecasts. The selected forecasting methods are used to estimate withdrawals for all 31 days of May for ATM1, ATM2, and ATM4. ATM3 is handled separately: the conditional active scenario uses the mean of its three nonzero readings, while the inactive scenario assumes zero withdrawals.

may_dates <- seq(as.Date("2010-05-01"), as.Date("2010-05-31"), by="day")
chosen_atm <- c(ATM1="last_week", ATM2="last_week",
                ATM4="weekday_median_4w")

atm_forecast_parts <- lapply(names(chosen_atm), function(a) {
  d <- atm[atm$ATM == a, ]
  predicted <- predict_atm(d, as.Date("2010-04-30"),
                           may_dates, chosen_atm[[a]])
  data.frame(Date=may_dates, ATM=a, Method=chosen_atm[[a]],
             Forecast_USD=predicted * 100)
})

atm3 <- atm[atm$ATM == "ATM3" & atm$Cash > 0 & !is.na(atm$Cash), ]
stopifnot(nrow(atm3) == 3)
atm_forecast_parts[[4]] <- data.frame(
  Date=may_dates, ATM="ATM3", Method="conditional_active_scenario",
  Forecast_USD=mean(atm3$Cash) * 100)

atm_daily <- do.call(rbind, atm_forecast_parts)
atm_daily <- atm_daily[order(atm_daily$Date, atm_daily$ATM), ]
row.names(atm_daily) <- NULL
atm_monthly <- aggregate(Forecast_USD ~ ATM, atm_daily, sum)
atm_monthly$Method <- sapply(atm_monthly$ATM, function(a) {
  unique(atm_daily$Method[atm_daily$ATM == a])[1]
})
kable(atm_monthly, digits=2, row.names=FALSE,
      caption="May 2010 estimated withdrawals by ATM")

May 2010 estimated withdrawals by ATM
ATM	Forecast_USD	Method
ATM1	240800.0	last_week
ATM2	195700.0	last_week
ATM3	271766.7	conditional_active_scenario
ATM4	1162200.2	weekday_median_4w

cat("Total with ATM3 active:", usd(sum(atm_daily$Forecast_USD)), "\n")

## Total with ATM3 active: $1,870,467

cat("Total if ATM3 is inactive:",
    usd(sum(atm_daily$Forecast_USD[atm_daily$ATM != "ATM3"])), "\n")

## Total if ATM3 is inactive: $1,598,700

Forecast chart. This plot shows the estimated daily withdrawals. ATM3 is a conditional scenario, not a forecast supported by a long record of active operation.

ggplot(atm_daily, aes(x=Date, y=Forecast_USD)) +
  geom_line(linewidth=0.7) +
  facet_wrap(~ATM, scales="free_y", ncol=2) +
  labs(title="Estimated ATM withdrawals during May 2010",
       subtitle="ATM3 assumes the machine is active throughout May",
       x="Date", y="Forecast withdrawals (USD)") +
  theme_minimal()

Findings

ATM1 is forecast to withdraw $240,800 in May 2010, ATM2 $195,700, and ATM4 approximately $1,162,200. ATM3 adds approximately $271,767 only if the machine was genuinely active; the combined conditional estimate is $1,870,467, compared with $1,598,700 if ATM3 was inactive.

The February 9 withdrawal for ATM4 was approximately $1,091,976 in one day. The selected recent median model is less influenced by such unusual transactions, but its forecast is not a recommended cash load. Replenishment planning must also consider existing cash, refill schedules, and the cost of running out. ATM3’s status and the ATM4 spike should be checked in the original transaction records. Only one year of ATM history is available, so I cannot reliably estimate a recurring annual May effect.

Part B: Forecasting residential electricity use for 2014

B1. Read and check the electricity data

Import and dates. The dataset contains 192 monthly observations from January 1998 through December 2013. The YYYY-MMM column is used to create the dates, while CaseSequence is treated as an identifier rather than a time variable. The KWH column represents monthly electricity consumption.

power_raw <- read_excel("ResidentialCustomerForecastLoad-624.xlsx",
                        sheet="ResidentialCustomerForecastLoad")
stopifnot(all(c("CaseSequence", "YYYY-MMM", "KWH") %in% names(power_raw)))

power <- data.frame(Period=as.character(power_raw[["YYYY-MMM"]]),
                    KWH=as.numeric(power_raw[["KWH"]]))
year <- as.integer(substr(power$Period, 1, 4))
month <- match(substr(power$Period, 6, 8), month.abb)
stopifnot(!anyNA(year), !anyNA(month))

power$Date <- as.Date(sprintf("%04d-%02d-01", year, month))
power$Month <- month
stopifnot(nrow(power) == 192,
          identical(power$Date, seq(as.Date("1998-01-01"),
                                    as.Date("2013-12-01"), by="month")))
cat("Months:", nrow(power), "\n")

## Months: 192

cat("Missing KWH:", sum(is.na(power$KWH)), "\n")

## Missing KWH: 1

The source has one missing value (September 2008) and one unusually low value (July 2010: 770,523 kWh). I leave the original spreadsheet unchanged, and will compare results with July 2010 included and with it excluded from regression fitting only.

B2. Examine seasonality and trend

Historical chart. This chart shows how monthly electricity use changes over the 16 years. The low July 2010 observation is marked in orange.

ggplot(power, aes(x=Date, y=KWH / 1e6)) +
  geom_line(colour="#17647A", linewidth=0.5, na.rm=TRUE) +
  geom_point(data=power[power$Date == as.Date("2010-07-01"), ],
             colour="#C15F3C", size=2.5) +
  labs(title="Residential electricity use, 1998–2013",
       subtitle="Orange point: unusually low July 2010 reading",
       x="Date", y="Monthly consumption (million kWh)") +
  theme_minimal()

Month-by-month averages. To investigate seasonality, I calculate the average for each calendar month during 2009–2013.

recent_power <- power[power$Date >= as.Date("2009-01-01"), ]
month_average <- sapply(1:12, function(m) {
  mean(recent_power$KWH[recent_power$Month == m], na.rm=TRUE)
})
stopifnot(length(month_average) == 12, all(is.finite(month_average)))

month_profile <- data.frame(
  Month=factor(month.abb, levels=month.abb),
  Average_KWH=month_average)

kable(month_profile, digits=0, row.names=FALSE,
      caption="Average monthly consumption, 2009–2013 (kWh)")

Average monthly consumption, 2009–2013 (kWh)
Month	Average_KWH
Jan	9102286
Feb	7979908
Mar	6454149
Apr	5733632
May	5482822
Jun	7277217
Jul	7175860
Aug	9054789
Sep	7974717
Oct	5676426
Nov	5559149
Dec	7546321

cat("September average:", format(month_average[9], big.mark=","), "kWh\n")

## September average: 7,974,717 kWh

ggplot(month_profile, aes(x=Month, y=Average_KWH / 1e6)) +
  geom_col(fill="darkslategray") +
  scale_x_discrete(drop=FALSE) +
  labs(title="Average electricity use by month, 2009–2013",
       subtitle="All twelve months shown; July 2010 remains in this descriptive average",
       x="Month", y="Average consumption (million kWh)") +
  theme_minimal()

The data show a repeating seasonal pattern, with relatively high demand in winter and late summer and lower consumption in spring and autumn. The overall level is also generally higher toward the end of the series.

B3. Test simple forecasting methods

Five forecasting methods are compared: the corresponding month from the previous year (seasonal naïve), the same-month average from the previous three years, and seasonal linear regression using either the full history or the most recent eight years. The final regression method excludes July 2010 during model fitting. More complex methods, such as ARIMA, ETS, and machine learning, are not included because their performance has not been evaluated.

Prediction function. The regression model predicts electricity consumption using a time index (t) and a month factor (MonthFactor). The time index captures long-term changes, while the month factor accounts for seasonal differences in electricity consumption.

power_methods <- c("seasonal_naive", "monthly_mean_3y",
                   "regression_all", "regression_8y",
                   "regression_8y_screened")

predict_power <- function(history, method) {
  stopifnot(method %in% power_methods, nrow(history) %% 12 == 0)

  if (method == "seasonal_naive") {
    return(as.numeric(tail(history$KWH, 12)))
  }

  if (method == "monthly_mean_3y") {
    d <- tail(history, 36)
    return(sapply(1:12, function(m) mean(d$KWH[d$Month == m], na.rm=TRUE)))
  }

  if (method == "regression_all") d <- history
  else d <- tail(history, 96)  # latest eight years = 96 months

  d$t <- seq_len(nrow(d))
  d$MonthFactor <- factor(d$Month, levels=1:12)

  if (method == "regression_8y_screened") {
    d$KWH[d$Date == as.Date("2010-07-01")] <- NA_real_
  }

  model <- lm(KWH ~ t + MonthFactor, data=d, na.action=na.exclude)
  future <- data.frame(t=nrow(d) + 1:12,
                       MonthFactor=factor(1:12, levels=1:12))
  as.numeric(predict(model, newdata=future))
}

Backtesting. For each test year (2011, 2012, and 2013), I make twelve predictions using data only through the preceding December. Then I calculate MAE, RMSE, and MAPE.

power_test_rows <- list()
k <- 1

for (test_year in 2011:2013) {
  history <- power[power$Date < as.Date(paste0(test_year, "-01-01")), ]
  actual <- power$KWH[format(power$Date, "%Y") == as.character(test_year)]
  for (method in power_methods) {
    predicted <- predict_power(history, method)
    stopifnot(length(predicted) == 12, all(is.finite(predicted)))
    power_test_rows[[k]] <- data.frame(
      Year=test_year, Month=1:12, Method=method,
      Actual_KWH=actual, Forecast_KWH=predicted,
      Error_KWH=actual - predicted)
    k <- k + 1
  }
}

power_backtest <- do.call(rbind, power_test_rows)
power_scores <- do.call(rbind, lapply(power_methods, function(m) {
  d <- power_backtest[power_backtest$Method == m, ]
  data.frame(Method=m,
             MAE=mean(abs(d$Error_KWH)),
             RMSE=sqrt(mean(d$Error_KWH^2)),
             MAPE=100 * mean(abs(d$Error_KWH / d$Actual_KWH)))
}))
power_scores <- power_scores[order(power_scores$MAE), ]
row.names(power_scores) <- NULL
kable(power_scores, digits=2, row.names=FALSE,
      caption="Forecast error across 2011–2013 (36 months)")

Forecast error across 2011–2013 (36 months)
Method	MAE	RMSE	MAPE
regression_8y_screened	694956.8	937071.8	8.71
regression_8y	787626.9	1100585.3	9.66
regression_all	846064.1	1160845.4	10.27
monthly_mean_3y	868982.0	1317366.5	10.39
seasonal_naive	999090.7	1868538.4	11.84

best_power_method <- power_scores$Method[1]
cat("Selected method:", best_power_method, "\n")

## Selected method: regression_8y_screened

The best result is the eight-year seasonal regression with July 2010 omitted from model fitting. Its MAE is about 694,957 kWh, compared with about 999,091 kWh for seasonal naïve forecasting. This only establishes that it performed better in the three historical test years, not that it is guaranteed to be best in 2014.

B4. Forecast January–December 2014

Monthly forecast. I fit the selected method to all the observations available through December 2013 and forecast the next twelve months. The full-precision numbers are retained for Excel, the report rounds them for readability.

power_forecast <- data.frame(
  Date=seq(as.Date("2014-01-01"), by="month", length.out=12),
  Month=month.abb,
  Forecast_KWH=predict_power(power, best_power_method),
  Model=best_power_method)

stopifnot(nrow(power_forecast) == 12,
          all(is.finite(power_forecast$Forecast_KWH)))
kable(power_forecast[c("Date", "Forecast_KWH")], digits=0,
      row.names=FALSE, caption="Forecast electricity consumption in 2014 (kWh)")

Forecast electricity consumption in 2014 (kWh)
Date	Forecast_KWH
2014-01-01	9616418
2014-02-01	8621356
2014-03-01	7281282
2014-04-01	6468681
2014-05-01	6157129
2014-06-01	7830051
2014-07-01	9129661
2014-08-01	9578787
2014-09-01	8742292
2014-10-01	6500708
2014-11-01	6168474
2014-12-01	8009852

actual_2013 <- sum(power$KWH[format(power$Date, "%Y") == "2013"])
forecast_2014 <- sum(power_forecast$Forecast_KWH)
cat("2013 actual:", format(actual_2013, big.mark=","), "kWh\n")

## 2013 actual: 91,420,024 kWh

cat("2014 forecast:", format(round(forecast_2014), big.mark=","), "kWh\n")

## 2014 forecast: 94,104,693 kWh

cat("Change:", round(100 * (forecast_2014 / actual_2013 - 1), 2), "%\n")

## Change: 2.94 %

Forecast chart.The chart shows actual electricity consumption from 2011 to 2013 in blue and the forecasted monthly consumption for 2014 in orange.

recent_actual <- power[power$Date >= as.Date("2011-01-01"), ]

ggplot() +
  geom_line(data=recent_actual, aes(x=Date, y=KWH / 1e6),
            colour="#17647A", linewidth=0.7) +
  geom_line(data=power_forecast, aes(x=Date, y=Forecast_KWH / 1e6),
            colour="#C15F3C", linewidth=0.7) +
  geom_vline(xintercept=as.numeric(as.Date("2014-01-01")),
             linetype="dashed") +
  labs(title="Residential power: actual 2011–2013 and forecast 2014",
       subtitle="Blue: actual | Orange: forecast",
       x="Month", y="Consumption (million kWh)") +
  theme_minimal()

## Warning in scale_x_date(): A <numeric> value was passed to a Date scale.
## ℹ The value was converted to a <Date> object.

B5. Save the Excel results

Excel output. A single workbook is created containing the ATM daily and monthly forecasts, the electricity consumption forecast, and the validation tables.

atm_for_excel <- atm_daily
atm_for_excel$Date <- as.character(atm_for_excel$Date)
power_for_excel <- power_forecast
power_for_excel$Date <- as.character(power_for_excel$Date)

write_xlsx(list(
  "ATM Daily May 2010"=atm_for_excel,
  "ATM Monthly Summary"=atm_monthly,
  "ATM Model Tests"=atm_scores,
  "Power Forecast 2014"=power_for_excel,
  "Power Model Tests"=power_scores,
  "Power Historical Tests"=power_backtest
), path="Part_A_B_Forecasts_from_R.xlsx")

cat("Created: Part_A_B_Forecasts_from_R.xlsx\n")

## Created: Part_A_B_Forecasts_from_R.xlsx

Findings

The selected regression model forecasts 94,104,693 kWh of electricity consumption in 2014, approximately 2.94% higher than the 2013 total of 91,420,024 kWh. The historical data show clear monthly seasonal patterns and a general increase in consumption over time. The September 2008 observation is missing, while July 2010 has an unusually low value. July 2010 is excluded from model fitting but remains unchanged in the original dataset. Historical backtesting supports this decision for the evaluation period.

These forecasts are estimates and do not guarantee actual electricity consumption. Factors such as temperature, extreme weather, and customer counts could affect demand but are not included in the available data. Comparing actual monthly consumption in 2014 with the forecasts could help improve future predictions.

Project 1: ATM Withdrawals and Residential Electricity

Madina Kudanova

Part A: Forecasting ATM cash withdrawals in May 2010

A1. Read and check the ATM data

A2. Look at historical patterns

A3. Compare simple forecasting methods

A4. Forecast May 2010

Findings

Part B: Forecasting residential electricity use for 2014

B1. Read and check the electricity data

B2. Examine seasonality and trend

B3. Test simple forecasting methods

B4. Forecast January–December 2014

B5. Save the Excel results

Findings