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 | |
No comments:
Post a Comment