Oil Prices and the Canadian Dollar: December 2015

This has been a difficult year for the Canadian dollar. Ever since oil prices started falling in mid 2014, the Canadian dollar has traded lower. Currently, in this low interest rate environment, oil prices seem to be the main driver of the Canadian dollar. From an international investment perspective, Canada is seen as consisting of safe banks and companies specializing in extracting natural resources. As a result, our currency and stock market both tend to follow commodity prices. Right now, commodity prices are depressed (especially oil) and this has taken the Canadian dollar down to lows not seen for over ten years.

Here are some plots of oil prices ($US per barrel, WTI) and how many US dollars one Canadian dollar buys. The monthly data are sourced from FRED. Notice the close correlation between the two data sets over the last half of the sample period.



As a starting point, I estimate a linear relationship between the two series.

The plot shows a positive correlation between oil prices and the exchange rate, but there seems to be some nonlinear behaviour towards the beginning and ending period of the data set.

Next, I estimate a quadratic fit. This doesn't look too different from the linear fit.


Perhaps a cubic fit is more appropriate. The cubic fit looks much better! Notice how the cubic curve fits the curvature at the beginning and ending period of the data.



Here are the model fits. The cubic curve fits the best.

	linear	quadratic	cubic
Adjusted R squared	0.6511	0.6507	0.6923
Sigma	0.0660	0.0660	0.0620

Here are some forecasts for each of the three regression models.

oil price	linear	quadratic	cubic
30.00	0.77	0.77	0.75
35.00	0.79	0.78	0.75
40.00	0.80	0.80	0.77
45.00	0.82	0.81	0.78
50.00	0.83	0.83	0.80
55.00	0.85	0.84	0.82
60.00	0.86	0.86	0.85
65.00	0.87	0.87	0.87

Today, oil is currently trading around $37 per barrel. An oil price of  $35 per barrel produces an exchange rate forecast of 75 cents (using the cubic model). The actual exchange rate at time of writing is 74 cents. Overall, a fairly close fit.


The R script and data set are posted below.
#########################################################
#  Economic forecasting and analysis
#  Perry Sadorsky
#  December 2015
#  Oil prices and the Canadian dollar
##########################################################


rm(list=ls())
# load libraries
library(fpp)


as2_data <- read.csv("C:/econ 6210/6210f15/assignment 2/as2_data.csv")
View(as2_data)


df = as2_data
df =  ts(df, start=1986, frequency=12)


plot(df[,-1], main="Oil prices and exchange rates", ylab = "", xlab = "")


oil = df[,"oil"]
fx = df[,"fx"]


# 5 year rolling correlations
rollout1 = rollapply(df[,-1], 60 ,function(x) cor(x[,1],x[,2]), by.column=FALSE,align="right")
rollout1 = na.omit(rollout1)
plot(rollout1,main="Rolling 5 year correlations between FX and Oil prices")


## linear fit
lm1 = lm(fx ~ oil)
summary(lm1)
str(summary(lm1))

rr = matrix(0,nrow=2, ncol=3)
rr[1,1] = summary(lm1)$adj.r.squared
rr[2,1] = summary(lm1)$sigma

par(mfrow=c(2,2))
plot(lm1)
par(mfrow=c(1,1))

plot(fx ~ oil, main="Linear fit",
     ylab="$US/$C", xlab="Oil prices ($/bbl)")
abline(lm1)

oil_f = c(30, 35, 40, 45, 50, 55, 60, 65)

fcast1 <- forecast(lm1, newdata=data.frame(oil=oil_f))
fcast1
plot(fcast1, ylab="$US/$C", xlab="Oil prices ($/bbl)")


## quadratic fit
lm2 = lm(fx ~ oil + I(oil^2))
summary(lm2)
rr[1,2] = summary(lm2)$adj.r.squared
rr[2,2] = summary(lm2)$sigma

par(mfrow=c(2,2))
plot(lm2)
par(mfrow=c(1,1))


plot(fx ~ oil, main="Quadratic fit",ylab="$US/$C", xlab="Oil prices ($/bbl)")
curve( coef(lm2)[1] +  coef(lm2)[2]*x +  coef(lm2)[3]*x^2 , add=T   )


fcast2 <- forecast(lm2, newdata=data.frame(oil=oil_f))
fcast2



## cubic fit
lm3 = lm(fx ~ oil +I(oil^2) +I(oil^3) )
summary(lm3)
rr[1,3] = summary(lm3)$adj.r.squared
rr[2,3] = summary(lm3)$sigma

par(mfrow=c(2,2))
plot(lm3)
par(mfrow=c(1,1))


plot(fx ~ oil, main="Cubic fit",ylab="$US/$C", xlab="Oil prices ($/bbl)")
curve( coef(lm3)[1] +  coef(lm3)[2]*x +  coef(lm3)[3]*x^2 +  coef(lm3)[4]*x^3, add=T  , col = "blue" )


fcast3 <- forecast(lm3, newdata=data.frame(oil=oil_f))
fcast3
fcast3$mean


tablef = cbind(oil_f, fcast1$mean, fcast2$mean, fcast3$mean)
colnames(tablef) = c("oil price", "linear", "quadratic", "cubic")
tablef

colnames(rr) = c("linear", "quadratic","cubic")
rownames(rr) = c("Adjusted R squared", "Sigma")
rr

date oil fx 1 1/1/1986 22.93 0.7107321 2 2/1/1986 15.46 0.7120986 3 3/1/1986 12.61 0.7138268 4 4/1/1986 12.84 0.7205130 5 5/1/1986 15.38 0.7269027 6 6/1/1986 13.43 0.7194762 7 7/1/1986 11.59 0.7242178 8 8/1/1986 15.10 0.7202017 9 9/1/1986 14.87 0.7208246 10 10/1/1986 14.90 0.7202017 11 11/1/1986 15.22 0.7213446 12 12/1/1986 16.11 0.7245852 13 1/1/1987 18.65 0.7349699 14 2/1/1987 17.75 0.7496252 15 3/1/1987 18.30 0.7579203 16 4/1/1987 18.68 0.7580352 17 5/1/1987 19.44 0.7456566 18 6/1/1987 20.07 0.7469934 19 7/1/1987 21.34 0.7540341 20 8/1/1987 20.31 0.7543754 21 9/1/1987 19.53 0.7602250 22 10/1/1987 19.86 0.7635336 23 11/1/1987 18.85 0.7594744 24 12/1/1987 17.28 0.7648184 25 1/1/1988 17.13 0.7779074 26 2/1/1988 16.80 0.7885192 27 3/1/1988 16.20 0.8005123 28 4/1/1988 17.86 0.8095200 29 5/1/1988 17.42 0.8082114 30 6/1/1988 16.53 0.8212878 31 7/1/1988 15.50 0.8281574 32 8/1/1988 15.52 0.8171938 33 9/1/1988 14.54 0.8151952 34 10/1/1988 13.77 0.8295313 35 11/1/1988 14.14 0.8206138 36 12/1/1988 16.38 0.8359806 37 1/1/1989 18.02 0.8394191 38 2/1/1989 17.94 0.8409722 39 3/1/1989 19.48 0.8365401 40 4/1/1989 21.07 0.8411844 41 5/1/1989 20.12 0.8385744 42 6/1/1989 20.05 0.8343067 43 7/1/1989 19.78 0.8409722 44 8/1/1989 18.58 0.8504848 45 9/1/1989 19.59 0.8454515 46 10/1/1989 20.10 0.8511363 47 11/1/1989 19.86 0.8549201 48 12/1/1989 21.10 0.8611039 49 1/1/1990 22.86 0.8532423 50 2/1/1990 22.11 0.8357710 51 3/1/1990 20.39 0.8474576 52 4/1/1990 18.43 0.8590327 53 5/1/1990 18.20 0.8512812 54 6/1/1990 16.70 0.8525149 55 7/1/1990 18.45 0.8643042 56 8/1/1990 27.31 0.8735150 57 9/1/1990 33.51 0.8633342 58 10/1/1990 36.04 0.8620690 59 11/1/1990 32.33 0.8594757 60 12/1/1990 27.28 0.8618461 61 1/1/1991 25.23 0.8650519 62 2/1/1991 20.48 0.8658758 63 3/1/1991 19.90 0.8641549 64 4/1/1991 20.83 0.8669267 65 5/1/1991 21.23 0.8696408 66 6/1/1991 20.19 0.8742023 67 7/1/1991 21.40 0.8700948 68 8/1/1991 21.69 0.8732099 69 9/1/1991 21.89 0.8795075 70 10/1/1991 23.23 0.8866034 71 11/1/1991 22.46 0.8847992 72 12/1/1991 19.50 0.8720677 73 1/1/1992 18.79 0.8642295 74 2/1/1992 19.01 0.8456660 75 3/1/1992 18.92 0.8383635 76 4/1/1992 20.23 0.8421762 77 5/1/1992 20.98 0.8339588 78 6/1/1992 22.39 0.8361204 79 7/1/1992 21.78 0.8386448 80 8/1/1992 21.34 0.8398421 81 9/1/1992 21.88 0.8179959 82 10/1/1992 21.69 0.8030194 83 11/1/1992 20.34 0.7890169 84 12/1/1992 19.41 0.7858546 85 1/1/1993 19.03 0.7825338 86 2/1/1993 20.09 0.7935248 87 3/1/1993 20.32 0.8018603 88 4/1/1993 20.25 0.7923302 89 5/1/1993 19.95 0.7875256 90 6/1/1993 19.09 0.7819220 91 7/1/1993 17.89 0.7800312 92 8/1/1993 18.01 0.7645260 93 9/1/1993 17.50 0.7567159 94 10/1/1993 18.15 0.7539772 95 11/1/1993 16.61 0.7590709 96 12/1/1993 14.52 0.7514277 97 1/1/1994 15.03 0.7591285 98 2/1/1994 14.78 0.7449344 99 3/1/1994 14.68 0.7329229 100 4/1/1994 16.42 0.7230658 101 5/1/1994 17.89 0.7242178 102 6/1/1994 19.06 0.7227522 103 7/1/1994 19.66 0.7232750 104 8/1/1994 18.38 0.7255315 105 9/1/1994 17.45 0.7385524 106 10/1/1994 17.72 0.7405762 107 11/1/1994 18.07 0.7327618 108 12/1/1994 17.16 0.7197869 109 1/1/1995 18.04 0.7076139 110 2/1/1995 18.57 0.7140307 111 3/1/1995 18.54 0.7103786 112 4/1/1995 19.90 0.7266386 113 5/1/1995 19.74 0.7348078 114 6/1/1995 18.45 0.7259528 115 7/1/1995 17.33 0.7346459 116 8/1/1995 18.02 0.7378985 117 9/1/1995 18.23 0.7402472 118 10/1/1995 17.43 0.7430525 119 11/1/1995 17.99 0.7388799 120 12/1/1995 19.03 0.7303002 121 1/1/1996 18.86 0.7315824 122 2/1/1996 19.09 0.7271670 123 3/1/1996 21.33 0.7322789 124 4/1/1996 23.50 0.7357269 125 5/1/1996 21.17 0.7303002 126 6/1/1996 20.42 0.7321716 127 7/1/1996 21.30 0.7300869 128 8/1/1996 21.90 0.7287567 129 9/1/1996 23.97 0.7302468 130 10/1/1996 24.88 0.7403020 131 11/1/1996 23.71 0.7473283 132 12/1/1996 25.23 0.7341066 133 1/1/1997 25.13 0.7410701 134 2/1/1997 22.18 0.7376807 135 3/1/1997 20.97 0.7285974 136 4/1/1997 19.70 0.7172572 137 5/1/1997 20.82 0.7244277 138 6/1/1997 19.26 0.7223868 139 7/1/1997 19.66 0.7259528 140 8/1/1997 19.95 0.7191658 141 9/1/1997 19.80 0.7208766 142 10/1/1997 21.33 0.7210325 143 11/1/1997 20.19 0.7078143 144 12/1/1997 18.33 0.7007217 145 1/1/1998 16.72 0.6940107 146 2/1/1998 16.06 0.6976420 147 3/1/1998 15.12 0.7059156 148 4/1/1998 15.35 0.6993985 149 5/1/1998 14.91 0.6919458 150 6/1/1998 13.72 0.6823610 151 7/1/1998 14.17 0.6725402 152 8/1/1998 13.47 0.6516356 153 9/1/1998 15.03 0.6571166 154 10/1/1998 14.46 0.6471654 155 11/1/1998 13.00 0.6491820 156 12/1/1998 11.35 0.6479622 157 1/1/1999 12.52 0.6581545 158 2/1/1999 12.01 0.6676905 159 3/1/1999 14.68 0.6589352 160 4/1/1999 17.31 0.6719979 161 5/1/1999 17.72 0.6844159 162 6/1/1999 17.92 0.6805036 163 7/1/1999 20.10 0.6715917 164 8/1/1999 21.28 0.6697027 165 9/1/1999 23.80 0.6770022 166 10/1/1999 22.69 0.6767731 167 11/1/1999 25.00 0.6814774 168 12/1/1999 26.10 0.6792555 169 1/1/2000 27.26 0.6903217 170 2/1/2000 29.37 0.6890849 171 3/1/2000 29.84 0.6845564 172 4/1/2000 25.72 0.6807815 173 5/1/2000 28.79 0.6685833 174 6/1/2000 31.82 0.6770481 175 7/1/2000 29.70 0.6766816 176 8/1/2000 31.26 0.6743998 177 9/1/2000 33.88 0.6727664 178 10/1/2000 33.11 0.6611570 179 11/1/2000 34.42 0.6482562 180 12/1/2000 28.44 0.6570734 181 1/1/2001 29.59 0.6652475 182 2/1/2001 29.61 0.6572029 183 3/1/2001 27.25 0.6415603 184 4/1/2001 27.49 0.6419309 185 5/1/2001 28.63 0.6488872 186 6/1/2001 27.60 0.6559528 187 7/1/2001 26.43 0.6532532 188 8/1/2001 27.37 0.6493928 189 9/1/2001 26.20 0.6377958 190 10/1/2001 22.17 0.6362537 191 11/1/2001 19.64 0.6280618 192 12/1/2001 19.39 0.6333924 193 1/1/2002 19.72 0.6251172 194 2/1/2002 20.72 0.6264094 195 3/1/2002 24.53 0.6298419 196 4/1/2002 26.18 0.6323111 197 5/1/2002 27.04 0.6450781 198 6/1/2002 25.52 0.6528267 199 7/1/2002 26.97 0.6469979 200 8/1/2002 28.39 0.6371862 201 9/1/2002 29.66 0.6344775 202 10/1/2002 28.84 0.6337136 203 11/1/2002 26.35 0.6363347 204 12/1/2002 29.46 0.6413545 205 1/1/2003 32.95 0.6487609 206 2/1/2003 35.83 0.6613319 207 3/1/2003 33.51 0.6774609 208 4/1/2003 28.17 0.6857770 209 5/1/2003 28.11 0.7225434 210 6/1/2003 30.66 0.7393715 211 7/1/2003 30.76 0.7235366 212 8/1/2003 31.57 0.7161785 213 9/1/2003 28.31 0.7334605 214 10/1/2003 30.34 0.7563724 215 11/1/2003 31.11 0.7616146 216 12/1/2003 32.13 0.7617307 217 1/1/2004 34.31 0.7717240 218 2/1/2004 34.69 0.7519362 219 3/1/2004 36.74 0.7526720 220 4/1/2004 36.75 0.7451565 221 5/1/2004 40.28 0.7252158 222 6/1/2004 38.03 0.7364855 223 7/1/2004 40.78 0.7561437 224 8/1/2004 44.90 0.7617887 225 9/1/2004 45.94 0.7763372 226 10/1/2004 53.28 0.8019889 227 11/1/2004 48.47 0.8355615 228 12/1/2004 43.15 0.8204118 229 1/1/2005 46.84 0.8164598 230 2/1/2005 48.15 0.8063866 231 3/1/2005 54.19 0.8223684 232 4/1/2005 52.98 0.8091270 233 5/1/2005 49.83 0.7964954 234 6/1/2005 56.35 0.8063216 235 7/1/2005 59.00 0.8177284 236 8/1/2005 64.99 0.8303579 237 9/1/2005 65.59 0.8491127 238 10/1/2005 62.26 0.8493290 239 11/1/2005 58.32 0.8463817 240 12/1/2005 59.41 0.8609557 241 1/1/2006 65.49 0.8641549 242 2/1/2006 61.63 0.8703978 243 3/1/2006 62.69 0.8640802 244 4/1/2006 69.44 0.8740495 245 5/1/2006 70.84 0.9009009 246 6/1/2006 70.95 0.8979079 247 7/1/2006 74.41 0.8854259 248 8/1/2006 73.04 0.8942944 249 9/1/2006 63.80 0.8959771 250 10/1/2006 58.89 0.8861320 251 11/1/2006 59.08 0.8803592 252 12/1/2006 61.96 0.8671523 253 1/1/2007 54.51 0.8501233 254 2/1/2007 59.28 0.8539710 255 3/1/2007 60.44 0.8560178 256 4/1/2007 63.98 0.8810573 257 5/1/2007 63.46 0.9131586 258 6/1/2007 67.49 0.9388790 259 7/1/2007 74.12 0.9521996 260 8/1/2007 72.36 0.9452689 261 9/1/2007 79.92 0.9739944 262 10/1/2007 85.80 1.0252204 263 11/1/2007 94.77 1.0339123 264 12/1/2007 91.69 0.9979044 265 1/1/2008 92.97 0.9901970 266 2/1/2008 95.39 1.0014020 267 3/1/2008 105.45 0.9971084 268 4/1/2008 112.58 0.9864852 269 5/1/2008 125.40 1.0007005 270 6/1/2008 133.88 0.9836711 271 7/1/2008 133.37 0.9871668 272 8/1/2008 116.67 0.9492169 273 9/1/2008 104.11 0.9450009 274 10/1/2008 76.61 0.8440956 275 11/1/2008 57.31 0.8216252 276 12/1/2008 41.12 0.8105698 277 1/1/2009 41.71 0.8164598 278 2/1/2009 39.09 0.8030838 279 3/1/2009 47.94 0.7908264 280 4/1/2009 49.65 0.8168600 281 5/1/2009 59.03 0.8674532 282 6/1/2009 69.64 0.8877841 283 7/1/2009 64.15 0.8905513 284 8/1/2009 71.05 0.9197940 285 9/1/2009 69.41 0.9245562 286 10/1/2009 75.72 0.9481369 287 11/1/2009 77.99 0.9440196 288 12/1/2009 74.47 0.9490367 289 1/1/2010 78.33 0.9580379 290 2/1/2010 76.39 0.9458948 291 3/1/2010 81.20 0.9776127 292 4/1/2010 84.29 0.9948269 293 5/1/2010 73.74 0.9612612 294 6/1/2010 75.34 0.9637625 295 7/1/2010 76.32 0.9595087 296 8/1/2010 76.60 0.9611688 297 9/1/2010 75.24 0.9680542 298 10/1/2010 81.89 0.9824148 299 11/1/2010 84.25 0.9872643 300 12/1/2010 89.15 0.9919651 301 1/1/2011 89.17 1.0061374 302 2/1/2011 88.58 1.0125557 303 3/1/2011 102.86 1.0239607 304 4/1/2011 109.53 1.0438413 305 5/1/2011 100.90 1.0330579 306 6/1/2011 96.26 1.0239607 307 7/1/2011 97.30 1.0467916 308 8/1/2011 86.33 1.0186411 309 9/1/2011 85.52 0.9975062 310 10/1/2011 86.32 0.9805844 311 11/1/2011 97.16 0.9758002 312 12/1/2011 98.56 0.9770396 313 1/1/2012 100.27 0.9871668 314 2/1/2012 102.20 1.0033109 315 3/1/2012 106.16 1.0062387 316 4/1/2012 103.32 1.0072522 317 5/1/2012 94.66 0.9903932 318 6/1/2012 82.30 0.9727626 319 7/1/2012 87.90 0.9859988 320 8/1/2012 94.13 1.0076582 321 9/1/2012 94.51 1.0221813 322 10/1/2012 89.49 1.0129660 323 11/1/2012 86.53 1.0030090 324 12/1/2012 87.86 1.0103051 325 1/1/2013 94.76 1.0079629 326 2/1/2013 95.31 0.9902951 327 3/1/2013 92.94 0.9761812 328 4/1/2013 92.02 0.9816433 329 5/1/2013 94.51 0.9807768 330 6/1/2013 95.77 0.9695559 331 7/1/2013 104.67 0.9613536 332 8/1/2013 106.57 0.9608917 333 9/1/2013 106.29 0.9669310 334 10/1/2013 100.54 0.9649715 335 11/1/2013 93.86 0.9536525 336 12/1/2013 97.63 0.9399380 337 1/1/2014 94.62 0.9140768 338 2/1/2014 100.82 0.9046499 339 3/1/2014 100.80 0.9003331 340 4/1/2014 102.07 0.9097525 341 5/1/2014 102.18 0.9179365 342 6/1/2014 105.79 0.9233610 343 7/1/2014 103.59 0.9311854 344 8/1/2014 96.54 0.9152480 345 9/1/2014 93.21 0.9081827 346 10/1/2014 84.40 0.8919015 347 11/1/2014 75.79 0.8830022 348 12/1/2014 59.29 0.8671523 349 1/1/2015 47.22 0.8249464 350 2/1/2015 50.58 0.8000640 351 3/1/2015 47.82 0.7925186 352 4/1/2015 54.45 0.8105698 353 5/1/2015 59.27 0.8212878 354 6/1/2015 59.82 0.8087343 355 7/1/2015 50.90 0.7774236 356 8/1/2015 42.87 0.7606298 357 9/1/2015 45.48 0.7538067 358 10/1/2015 46.22 0.7649939

Forecasting P/S for Magna

Auto parts maker Magna (MGA) started the month of November trading around $53 and then, wham!, it lost $5 in one day on the announcement of the Trans Pacific Partnership (TPP). The TPP is
a proposed partnership agreement that would establish terms of trade between 12 Pacific Rim countries: Australia, Brunei, Canada, Chile, Japan, Malaysia, Mexico, New Zealand, Peru, Singapore, the U.S., and Vietnam. One of  the concerns for auto parts maker Magna is that under NAFTA, only auto parts containing 60% North American content could move duty free between Canada, Mexico, and the US. The TPP reduces the local content threshold to something in the range of 35% to 40%.




Here is a plot of Magna's quarterly sales. The Great Recession had a huge impact on slowing Magna's sales, but afterward, sales climbed steadily until late 2014.

One interesting question is how does Magna currently compare on a standard valuation measure like price to sales (P/S). The P/S ratio, like P/E, P/B, and P/CF, is a measure of valuation. Lower values are preferred (generally less than 1 for P/S), but it is important to take into account industry effects. Also, like other valuation measures, a low P/S could indicate a value stock or it could indicate something else is wrong with the company.

My approach is to compare price to trailing 12 month sales with price to forward 12 month sales. If the forward P/S is less than the trailing P/S the company may be undervalued. For this comparison I will need forecasts of future sales.

I use a variety of different uni-variate models to forecast Magna's quarterly sales.  Forecasting approaches include  simple averages, Holt, Holt-Winters, ETS, ARIMA, and ANN. Data from the first quarter of 2001 to the fourth quarter of 2012 are used for training. Models are tested on out-of-sample forecasts over the period 2013:1 to 2015:3.

Here is table of forecast accuracy measures ranked on MASE.

	ME	RMSE	MAE	MPE	MAPE	MASE
Holt Winters training	10.3605	416.6794	272.9042	-0.1470	5.7363	0.3097
ETS training	101.3534	432.8289	308.9523	1.6208	6.4568	0.3506
STL training	112.1135	451.0617	310.0710	1.9976	6.5669	0.3519
ARIMA training	-0.1629	447.0967	321.3737	-0.3155	6.3713	0.3647
ANN2 training	0.9323	498.2682	385.9291	-0.9804	7.5023	0.4379
ANN training	-0.3573	501.1293	387.6661	-1.0337	7.5453	0.4399
Holt linear training	3.4478	516.7124	389.2279	-0.4852	7.8051	0.4417
Holt ES training	32.4932	522.0256	394.1387	0.2721	7.8724	0.4472
Holt dampled training	49.4785	519.0834	396.3995	0.3593	7.8883	0.4498
ES training	107.7185	527.2579	408.3396	1.5849	7.9818	0.4634
Naive training	110.0000	532.8356	417.0213	1.6185	8.1515	0.4732
Holt linear test	-151.2323	765.2259	576.4225	-2.2910	6.9925	0.6541
Holt damped test	297.9467	656.7112	583.8959	3.0695	6.7486	0.6626
Holt Winters test	24.8474	691.3100	601.4257	-0.1406	7.1852	0.6825
Holt ES test	-274.5440	859.5667	625.6039	-3.7699	7.6591	0.7099
Naive test	510.9091	739.0499	626.0000	5.6066	7.1001	0.7104
ES test	510.9714	739.0929	626.0396	5.6074	7.1005	0.7104
ARIMA test	-298.4399	823.3481	638.6404	-4.0160	7.7919	0.7247
STL test	578.2432	789.4180	648.2347	6.4402	7.3382	0.7356
ETS test	649.8151	815.6195	685.7139	7.2909	7.7528	0.7781
S. Naive test	864.0909	966.5054	864.0909	9.8515	9.8515	0.9805
S. Naive training	450.2500	1132.3782	881.2500	6.6540	17.4009	1.0000
ANN test	1073.7787	1149.9015	1073.7787	12.3361	12.3361	1.2185
ANN2 test	1105.4155	1178.8549	1105.4155	12.7111	12.7111	1.2544
Mean training	0.0000	1550.3499	1302.3281	-11.0193	29.8710	1.4778
Mean test	3184.2841	3228.7508	3184.2841	37.0207	37.0207	3.6134

Based on the MASE for the test measures, Holt linear trend ranks lowest. Notice, however, that Holt-Winters has the lowest absolute ME among the test measures. I will estimate the Holt-Winters approach on data from 2001:1 to 2015:3, and then forecast 6 quarters ahead.

Here is a plot of the forecasts.

Here are the forecasted values in table form.

         Qtr1     Qtr2     Qtr3     Qtr4
2015                            8385.564
2016 8185.603 8534.537 8029.078 8783.678
2017 8569.665 

Here is a comparison between the trailing P/S and the forward P/S.

     P/S ttm    P/S forward
[1,]  0.5509724   0.5480994

The forward P/S ratio is slightly less than the trailing P/S ratio indicating slight undervaluation.

It is important to compare company P/S ratios to industry averages. For this I use the auto parts P/S value of  0.69 from Damodaran, indicating that based on P/S, Magna is undervalued relative to the industry average.

As with any valuation exercise, it is important to compare these results for P/S with those of other valuation ratios like P/E, P/B, and P/CF.


The R code and data are posted below.

#########################################################
#  Economic forecasting and analysis
#  Fall 2015
#  Perry Sadorsky
#  Forecasting sales of Magna
#  with smoothing methods, ARIMA, and ANN
##########################################################


# load libraries
library(fpp)


# import data
as1_data <- read.csv("C:/econ 6210/6210f15/week 10/as1_data.csv")
View(as1_data)

df = as1_data


# define as time series
df = ts(df, start=2000, frequency=4)
df

# extract sales
 y = df[,"MGA"]
# y = df[,"SPLS"]
y

                      
# some graphs
par(font.axis = 2)
par(font.lab = 2)
plot(y, main = "MGA quarterly sales ($ millions)", xlab="", ylab="" , col ="blue", lwd=2)
tsdisplay(y)
par(mfrow = c(1,1))


# generate some returns
y.ret = diff(log(y)) * 100
tsdisplay(y.ret)
par(mfrow = c(1,1))                    


# training period
train <- window(y,start=c(2001, 1),end=c(2012, 4))
train

# test period
test <- window(y, start=2013)

# number of steps to forecast
h = length(test)

# out of sample forecast
y5 <- window(y,start=c(2001, 1)  )
h2 = 6

##########################################################
# forecast using simple methods
##########################################################


yfit1 <- meanf(train, h=h)
yfit2 <- naive(train, h=h)
yfit3 <- snaive(train, h=h)

plot(yfit1)
plot(yfit2)
plot(yfit3)

# make a nice plot showing the forecasts
plot(yfit1, plot.conf=FALSE,
main="Forecasts for quarterly TGT sales")
lines(yfit2$mean,col=2)
lines(yfit3$mean,col=3)
legend("topleft",lty=1,col=c(4,2,3),
legend=c("Mean method","Naive method","Seasonal naive method"))


# plot with forecasts and actual values
plot(yfit1, plot.conf=FALSE,
     main="Forecasts for quarterly TGT sales")
lines(yfit2$mean,col=2)
lines(yfit3$mean,col=3)
lines(y)
legend("topleft",lty=1,col=c(4,2,3),
       legend=c("Mean method","Naive method","Seasonal naive method"),bty="n")



##########################################################
# exponential smoothing approaches
##########################################################

# simple exponential moving averages
yfit4 <- ses(train, h = h)
summary(yfit4)
plot(yfit4)


# holt's linear trend method
yfit5 <- holt(train,  h=h)
summary(yfit5)
plot(yfit5)


# holt's exponential trend method
yfit6 <- holt(train, exponential=TRUE, h=h)
summary(yfit6)
plot(yfit6)


# holt's damped trend method
yfit7 <- holt(train, damped=TRUE, h=h)
summary(yfit7)
plot(yfit7)


# holt winter's  method
yfit8 <- hw(train, seasonal="multiplicative", h=h)
summary(yfit8)
plot(yfit8)


# ETS  method
y.ets <- ets(train, model="ZZZ")
summary(y.ets)
yfit9 <- forecast(y.ets, h=h)
summary(yfit9)
plot(yfit9)


# STL  method
y.stl <- stl(train, t.window=15, s.window="periodic", robust=TRUE)
summary(y.stl)
yfit10 <- forecast(y.stl, method="naive",h=h)
summary(yfit10)
plot(yfit10)


##########################################################
# arima method
##########################################################

y.arima <- auto.arima(train)
yfit11 <- forecast(y.arima, h=h)
plot(yfit11)


##########################################################
# ANN
##########################################################

fit.ann <- nnetar(train)
yfit12 = forecast(fit.ann,h=h)
plot(yfit12)


fit.ann2 <- nnetar(train, repeats= 100)
yfit13 = forecast(fit.ann2,h=h)
plot(yfit13)



##########################################################
# accuracy measures
##########################################################


a1 = accuracy(yfit1, test)
a2 = accuracy(yfit2, test)
a3 = accuracy(yfit3, test)
a4 = accuracy(yfit4, test)
a5 = accuracy(yfit5, test)
a6 = accuracy(yfit6, test)
a7 = accuracy(yfit7, test)
a8 = accuracy(yfit8, test)
a9 = accuracy(yfit9, test)
a10 = accuracy(yfit10, test)
a11 = accuracy(yfit11, test)
a12 = accuracy(yfit12, test)
a13 = accuracy(yfit13, test)


#Combining forecast summary statistics into a table with row names
a.table<-rbind(a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13)

row.names(a.table)<-c('Mean training','Mean test', 'Naive training', 'Naive test', 'S. Naive training', 'S. Naive test' ,
                      'ES training','ES test', 'Holt linear training', 'Holt linear test', 'Holt ES training', 'Holt ES test' ,
                      'Holt dampled training','Holt damped test', 'Holt Winters training', 'Holt Winters test', 'ETS training', 'ETS test' ,     
                  'STL training','STL test', 'ARIMA training','ARIMA test', 'ANN training', 'ANN test','ANN2 training', 'ANN2 test' )

# order the table according to MASE
a.table<-as.data.frame(a.table)
a.table<-a.table[order(a.table$MASE),]
a.table

# write table to csv file
# write.csv(a.table, "C:/econ 6210/6210f15/week 10/atable.csv")


## forecast 6 periods into the future

plot(hw(y5, seasonal="multiplicative", h=h2))
par(mfrow = c(1,1))

y_forc =hw(y5, seasonal="multiplicative", h=h2)$mean
# y_forc = holt(y5, h=h2)$mean


# forecasted sales for 2016
# sales_f = y_forc[3] + y_forc[4] + y_forc[5] + y_forc[6]

sales_f = 0
for (i in 1:4){
sales_f = sales_f + y_forc[i]
  }
sales_f





# calculate price to forward sales
# data on November 21, 2015
price  = 44.94    # current stock prices
shares = 404.12   # millions of shares outstanding


ptos_f = price/(sales_f/shares)
ptos_f




# calculate price to trailing sales
last = tail(y,4)
sales_t = 0
for (i in 1:4){
  sales_t = sales_t + last[i]
}
sales_t


ptos_t = price/( sales_t /shares)
ptos_t

ps = cbind(ptos_t, ptos_f)
colnames(ps) = cbind("P/S ttm   ", "P/S forward")
ps

# compare with industry average
# http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/psdata.html
# auto parts 0.69


Data

datacqtr MGA 1 2000Q1 2808 2 2000Q2 2610 3 2000Q3 2354 4 2000Q4 2741 5 2001Q1 2863 6 2001Q2 2817 7 2001Q3 2517 8 2001Q4 2829 9 2002Q1 3121 10 2002Q2 2896 11 2002Q3 2962 12 2002Q4 3443 13 2003Q1 3496 14 2003Q2 3660 15 2003Q3 3566 16 2003Q4 4623 17 2004Q1 5103 18 2004Q2 5113 19 2004Q3 4784 20 2004Q4 5653 21 2005Q1 5718 22 2005Q2 5858 23 2005Q3 5381 24 2005Q4 5854 25 2006Q1 6019 26 2006Q2 6369 27 2006Q3 5424 28 2006Q4 6368 29 2007Q1 6423 30 2007Q2 6731 31 2007Q3 6077 32 2007Q4 6836 33 2008Q1 6622 34 2008Q2 6713 35 2008Q3 5533 36 2008Q4 4836 37 2009Q1 3574 38 2009Q2 3705 39 2009Q3 4669 40 2009Q4 5419 41 2010Q1 5512 42 2010Q2 6050 43 2010Q3 5942 44 2010Q4 6598 45 2011Q1 7189 46 2011Q2 7338 47 2011Q3 6970 48 2011Q4 7251 49 2012Q1 7666 50 2012Q2 7727 51 2012Q3 7411 52 2012Q4 8033 53 2013Q1 8361 54 2013Q2 8962 55 2013Q3 8338 56 2013Q4 9174 57 2014Q1 8455 58 2014Q2 8911 59 2014Q3 8820 60 2014Q4 9396 61 2015Q1 7772 62 2015Q2 8133 63 2015Q3 7661