Holt winter forecasting for Audio production data

refik-forecast.R

library(tidyverse)
library(forecast)
library(readxl)

tugce_file <- "/Users/refik/Desktop/her madde kodu için winters modeli.xlsx" 
output <- "/Users/refik/Desktop/holt-winters.csv" 
excel <- read_excel(tugce_file, col_names = FALSE)

products <- excel[5:nrow(excel), 1:30]
colnames(products) <- c(unlist(excel[4, 1:6]), 
                        paste0("month ", sprintf("%02d", 1:24)))

predict <- products %>%
  
  # Tidy sales values into time series
  select(`Ürün kodu`, starts_with("month")) %>%
  gather(month, sold, -`Ürün kodu`) %>%
  spread(`Ürün kodu`, sold) %>%
  select(-month) %>%
  mutate_all(funs(as.numeric)) %>%
  summarise_all(funs(list(ts(., start = c(2015, 1), frequency = 12)))) %>%
  gather(`Ürün kodu`, series) %>%
  right_join(select(products, -starts_with("month"))) %>%
  
  # Make forecasting
  mutate(winter = lapply(series, HoltWinters)) %>%
  mutate(predict = lapply(winter, forecast, h = 12)) %>%
  do({
    bind_cols(products, 
      bind_rows(lapply(.$predict, function(prediction) {
        as.data.frame(prediction) %>%
          rownames_to_column("Month") %>%
          select(Month, `Point Forecast`) %>%
          add_row(Month = "Total", `Point Forecast` = sum(.$`Point Forecast`)) %>%
          mutate(Month = factor(Month, Month)) %>%
          spread(Month, `Point Forecast`)
      }))
    )
  })

write_excel_csv(predict, output)