Tuesday 8 December 2015

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





Tuesday 24 November 2015

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%.


MGA Magna International Inc. daily Stock Chart

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