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






No comments:

Post a Comment