Managers use forecasting in budgeting time and resources. In this thesis, various advanced time series models are constructed, computed and tested for adequacy. This thesis serves as a practical guide to regression and time series analysis. It seeks to demonstrate how to approach problems according to scientific standards to students who are familiar with SPSS® but beginners in regression and time series analysis. Bibliographic notes of classical works and more recent academic advances in time series analysis are provided throughout the text.
The research question that this thesis seeks to answer can be formulated in its shortest version as: “How can the management of Dalian Chemson Chemical Products Co; Ltd. use existing company data to make short-term predictions about net sales, Cost of Goods Sold (COGS), and net contribution?” More specifically, this thesis seeks to provide different tools (models) for forecasting the P&L entries net sales, COGS, and net contribution a few months ahead. This author’s approach is based on various versions of two models: One model will forecast net sales and the other model will predict COGS. The expected net contribution is simply defined as the difference between the predictions of these two models.
In chapter 4.3 an ordinary least squares regression version of the two models has been computed. In chapter 4.6 a weighted least squares regression has been applied to the models. Autoregressions have been computed in chapter 4.7.1 and two Autoregressive Integrated Moving Average (ARIMA) versions have been constructed in chapter 4.7.6. The various versions of the models have then been compared against each other. The version that fits the data best will be used in forecasting. The statistical models in this thesis are computed using SPSS Base™, SPSS Regression Models™ and SPSS Trends™, versions 11.5.0. Each of the model versions constructed herein can be applied in a simple Excel spreadsheet. In the last chapter, a one-step-ahead forecast is produced via the in this thesis developed concept which consists of the most precise versions of the models to forecast net sales and COGS. The forecasting concept developed in this thesis is good in that it produces precise forecasts. Its simplified framework minimizes the effort and expertise required to obtain predictions.
TABLE OF CONTENTS
Table of Figures
Index of Notation
List of Abbreviations
Glossary
Preface
1 Introduction
1.1 Outline of the Historical Background of Forecasting
1.2 Motivation
1.3 Methodology
2 Review of Literature
3 Description of Data
4 Analysis
4.1 Building a Model for Forecasting Cost of Goods Sold
4.2 Building a Model for Forecasting Net Sales
4.3 Computation
4.4 Assumptions of the Classical Linear Regression Model
4.5 Validation of Assumptions
4.5.1 Assumption 1
4.5.2 Assumption 2
4.5.3 Assumption 3
4.5.4 Assumption 4
4.5.5 Assumption 5
4.5.6 Assumption 6
4.5.7 Assumption 7
4.6 Weighted Least Squares Regression
4.7 Time Series Analysis
4.7.1 The Autoregressive Process
4.7.2 The Moving Average Process
4.7.3 The Autoregressive Moving Average Process
4.7.4 The Autoregressive Integrated Moving Average Model
4.7.5 Model Identification
4.7.6 Model Estimation
4.7.7 Diagnosis
5 Conclusion
5.1 Forecasting
5.2 Outlook
6 Bibliography
TABLE OF FIGURES
Figure 1: Average cost of sales per unit
Figure 2: Average net sales per unit
Figure 3: Ranked composition of 90 % of the total raw material value used in production
Figure 4: Lagged lead price as a regressor
Figure 5: Effect of output and lagged lead price on the average cost per unit sold
Figure 6: Average raw material cost per category
Figure 7: ACF and PACF plots of average cost per unit sold
Figure 8: ACF and PACF plots for the average net sales per unit
Figure 9: Average sales price per category
Figure 10: Sales prices of 6 key products
Figure 11: Linear regression output
Figure 12: Regression output and Correlations for model 2
Figure 13: Regression output and Correlations for model 1
Figure 14: Assumptions of the Classical Linear Regression Model
Figure 15: Residual plots for model 1
Figure 16: Residual plots for model 2
Figure 17: Residual variance of model 1 per month
Figure 18: Checking residual autocorrelation
Figure 19: Normal probability plots
Figure 20: Shapiro-Wilk test
Figure 21: WLS regression output for model 1
Figure 22: WLS regression output for model 2
Figure 23: Original data series and predictions from the OLS and WLS models
Figure 24: Autoregression output of model 1
Figure 25: Autoregression output of model 2
Figure 26: Example characteristics of ARIMA processes
Figure 27: ARIMA(1,0,0) output of model 1
Figure 28: ARIMA(1,0,0) output of model 2
Figure 29: Residual autocorrelation for model 1
Figure 30: Residual autocorrelation for model 2
Figure 31: Predictions from the ARIMA versions
Figure 32: Forecast
Figure 33: Actual figures from the P&L from May 2007
“Statisticians are people whose aim in life is to be wrong 5 % of the time!” Quoted from Kempthorne and Doerfler (1969, p. 231)
INDEX OF NOTATION
illustration not visible in this excerpt
LIST OF ABBREVIATIONS
illustration not visible in this excerpt
GLOSSARY
Autocorrelation function
Correlation between observations as function of the time interval between them.
Ceteris paribus
Other things equal
Confidence level
The probability that a true population parameter is included in a confidence interval.
Constant
A value that does not vary.
Forecasting
The ability to extrapolate the future dynamics of a given system based on its past and current state.
Heteroscedasticity
Having an inconstant variance.
In praxi
Applied
Iterative estimation
Iterative techniques compute estimates via a series of repetitive steps which are built into the estimation algorithm.
Lag
A transformation that brings past values of a series up to the present. The lag order shows prior cases in order of progression from which the value is obtained.
Model
A replica of a real world data generating mechanism on which predictions can be based.
Noise
Random source of variation in a time series.
Ordinary least squares
An estimation algorithm generating the set of values of the parameters that minimizes the sum of squared residuals.
Parsimony
The rule that, ceteris paribus, models with few parameters are preferable to complicated ones. A model without excess parameters is parsimonious.
Polypolistic market
Perfect competition of small firms which share a market with many other similar firms. Each of the firms reacts relatively quickly to changes but cannot influence the market price on its own.
Probability distribution
The set of all possible values of a random variable and their probabilities.
R Square
Goodness-of-fit measure. An estimate of the proportion of the total variation in a stationary series that is explained by the model.
Stationarity
Strict stationarity requires that the probability distribution of a stochastic sequence of n observations does not depend on their time origin. Weak stationarity implies a constant mean and a constant variance.
Residual
The difference between an observed value and its prediction from a statistical model.
Significance level
The maximum probability of rejecting H0 when it is true.
Stochastic process
A sequence of random variables.
Time series
A sequence of equally spaced observations taken over time.
Variance
The average squared deviation of a random variable from its expectation.
White Noise
An uncorrelated stochastic process with a constant mean jt and a constant variance 2. More
specifically defined as weak white noise.
PREFACE
I want to warmly thank my parents for their support which has allowed me to finish this project. I also want to thank Ian Sewell, @ܝ and Brent Kigner, who gave considerable help with various aspects of the research. I am especially grateful to Louise Palmrich-Heouston for proofreading the final manuscript.
Arno Palmrich
1 INTRODUCTION
1.1 Outline of the Historical Background of Forecasting
Understanding nature and prediction of the future are ubiquitous desires of mankind. In the 18th century, science was characterized by determinism. In classical physics, determinism means that the trajectories of all particles can be calculated from their positions and velocity given at an initial time (Schelter et al., 2006). Leibniz, Kant and Newton propagated a fully deterministic view of the universe which implied complete predictability of the future. In the 19th century, Poincaré’s theory of deterministic chaos completely changed the picture. It showed that trajectories, defined as solutions of nonlinear differential equations, result from deterministic chaos. The theory tells us that even in classical mechanics predictability cannot be guaranteed as long as our knowledge of the precise configuration of the whole system is incomplete. If we knew more about the microscopic states of a system, we could predict the future development more precisely. For further information, see Schuster (1988), and the references therein.
In the 20th century, the deterministic principle was shaken again by the contributions of Planck, Einstein, Bohr et al. There is not enough space in this thesis to discuss these works. Refer to the academic literature, e.g. to Gutzwiller (1990) for more information on the theories of quantum mechanics.
Finally, probability theory and Heisenberg’s uncertainty principle put an end to the classical concept of determinism. Refer to Feller (1971) or Fine (1973) for an explanation of probability theory and to de Broglie (1982), who covers the still controversial uncertainty principle.
When Kolmogorov finalized the probability theory which had originally been developed by Pascal and Laplace, modern statistics was born. Sophisticated mathematical methods first appeared in late 19th century and computer software made it possible to improve these methods.
Nowadays, statistics provides a full range of mathematical tools to predict future values. Modern computer software enables scientists working in applied fields to solve complex problems within an instant. In fact, the time and mathematical expertise required to calculate advanced statistical models is no longer a prerequisite provided one can use a software program to compute them.
Although complex methods are nowadays quite easy to carry out thanks to the computer revolution, formulation and diagnosis of an appropriate model do require skill and experience. Furthermore, the computed results are sometimes difficult to interpret. A computer does not operate by itself just as impressive mathematics does not necessarily create correct results. As always, when working with a model which requires input in order to generate an output, one should remember a simple formula: GI = GO. This means garbage in produces garbage out (Ruppert, 2004).
1.2 Motivation
In 2006, the General Manager of Dalian Chemson Chemical Products Co; Ltd., (DCCP) invited this author to undertake an Assignment as part of his industrial experience. DCCP is a 60:40 joint venture between the Chemson Group and Dalian Shide Group. DCCP was set up to manufacture Polyvinyl chloride (PVC) Onepack stabilisers based on lead (Pb) using the Chemson Group technology and expertise where they are one of the leading companies in the world, with 9 factories in 8 countries. The Shide Group is one of the largest manufacturers of PVC window profile with 8 factories in 4 locations in China and therefore is a major consumer of PVC onepack stabilisers. In the last two years DCCP’s business has expanded to other countries and other markets and applications. DCCP currently has over 80 customers in 11 countries and sells onepack stabilisers based on Pb and non Pb technology for 7 different applications. Still, DCCP is a polypolist with only a small market share.
As a subsidiary of the Chemson Group, DCCP has to provide monthly Management Performance Reports to the Group Headquarters in Austria. The reports are used by the Group and local management to assess how each subsidiary is performing against their monthly forecast, annual budget and their previous financial year.
Managers use forecasting in budgeting time and resources. Forecasts provide the tools needed to achieve objectives, exercise control and remain competitive. DCCP’s management has to estimate budgets and provide monthly forecasts of key performance indicators. It is also important for DCCP’s management to know how changes in raw material prices affect the company’s performance in the profit & loss statement (P&L). Lagged sales price adjustments pose a serious problem to the company, because when raw material prices rise, most customers will not immediately accept sales price increases. The company, therefore, must bear the extra costs for the duration of a transition period. If management had a more accurate estimate of future costs, they could then initiate price negotiations in advance. In being able to pass increases in cost on to the customer at an earlier time, the company’s performance could be improved.
DCCP has no sophisticated method for forecasting in place. The group board has criticized the subsidiary’s inaccurate budgets and poor forecasts. All these problems are common in Chinese joint ventures (Fiebrandt, 2001, p. 159; Krokowski, 2001, p. 180). The situation could be improved by employing more advanced forecasting techniques. Prediction via a statistical model would ensure that forecasting is subject to definite mathematical rules instead of using forecasts based on mere assumptions. As a result, comparisons of actual against target values would provide more meaningful controlling tools. In addition, an advanced model should enable the company to react to changes in the market more quickly. Furthermore, by predicting total net contribution, management should ultimately be able to estimate another key performance indicator: profit.
The research question that this thesis seeks to answer can be formulated in its shortest version as: “How can the management of Dalian Chemson Chemical Products Co; Ltd. use existing company data to make short-term predictions about net sales, Cost of Goods Sold (COGS), and net contribution?” More specifically, this thesis seeks to provide different tools (models) for forecasting the P&L entries net sales, COGS, and net contribution a few months ahead.
1.3 Methodology
The basic procedure to answer economic questions quantitatively is the formation of models. A model consists of empirical assumptions from which predictions are made. Models generally simplify the underlying real situation. Even though a model is not an exact replica of reality, the predictions obtained from it often turn out to be close enough to reality. In this thesis, various advanced time series models are constructed, computed and tested for adequacy. The aim of the model versions is to predict these 3 key items of the profit & loss statement:
illustration not visible in this excerpt
This author’s approach is based on various versions of two models: One model is used to forecast net sales and the other model for predicting the Cost of Goods Sold. Net contribution is simply defined as the difference between the predictions of these two models. The various versions of the models will then be compared against each other. A model of the series that explains the past values is likely to predict the behavior of the next few values. The version that fits the data best will be used in forecasting. In chapter 4.3 an ordinary least squares regression version of the two models has been computed. In chapter 4.6 a weighted least squares regression has been applied to the models. Autoregressions have been computed in chapter 4.7.1 and two Autoregressive Integrated Moving Average versions have been constructed in chapter 4.7.6.
Among the common statistical computer programs available for model computation, Superior Performing Software Systems (SPSS® ) is a popular software package (Field, 2000). It consists of a base module and various add-ons for different applications. SPSS TrendsTM is an add-on enhancement that provides a set of procedures for analyzing and forecasting time series. The statistical models in this thesis are computed using SPSS BaseTM, SPSS Regression ModelsTM and SPSS Trends, versions 11.5.0. Each calculation, table, figure, chart etc. in this thesis was produced by this author personally. Thus, the sources of all figures herein are this author’s calculations based on company data.
This thesis also serves as a practical guide to regression and time series analysis. It seeks to demonstrate how to approach problems according to scientific standards to students who are familiar with SPSS but beginners in regression and time series analysis.
2 REVIEW OF LITERATURE
It is guaranteed that the research question of this thesis has never been answered before. Other authors presumably have constructed similar models for predicting net sales, COGS or net contribution for other organizations and some may even have published their works, but the chance that an identical thesis exists is null.
Given the significance of regression analysis in science, there is an abundance of publications on this subject. Standard statistics textbooks such as Anderson (2002), Lind (2003) and Schira (2005) give an introduction to regression analysis. The literature on time series analysis and forecasting is also very extensive. Keller’s textbook in English provides a practical orientation for economics students, including a short chapter on time series (Keller and Warrack, 2003). Bamberg’s statistics textbook in German, which focuses on economics and social sciences includes a short introduction to time series analysis (Bamberg and Baur, 2002). Much of the time series literature is based on old, classical works. However, this thesis seeks to refer readers to some of the more recent advances in time series, too. Kennedy (2003) or Brockwell and Davis (2002) are current standard textbooks which briefly summarize some of the recent progress in time series analysis. Pena et al. (2001) also present a good overview. Schelter et al. (2006) describe recent advanced developments in time series analysis but with a focus on neuroscience only.
Kerr et al. (2002) give an introduction to the SPSS base system. Field (2000) provides a more detailed guide to working with SPSS base, which he regards as the best statistical software on the market. Hafner and Waldl (2001) is a guide to SPSS in German.
3 DESCRIPTION OF DATA
The source data consist of a series of 6 quantitative variables observed over 40 consecutive months, from January 2004 to April 2007. NETSALES, COGS (Cost of Goods Sold), NET_CONT (net contribution) and PB_PRICE (lead price) are given in monetary values (all in identical units of currency). SALESVOL (sold quantity) and PRODVOL (output) are given in metric units of weight. This author calculated a few more series from this existing data using the SPSS Data Editor: AVGCOST (=cogs/salesvol), AVGSALES (=netsales/salesvol), LPRODVOL (=LN(prodvol)) and 3 lagged variables, where each observation contains the previous month’s value of the variable in brackets: LAGS(PB_PRICE,1) (=pb_pricet-1), LAGS(AVGCOST,1) (=avgcostt-1) and LAGS(AVGSALES,1) (=avgsales t-1). This author also calculated the composition of output and sold quantity as a percentage of the total for 3 groups of products: PIPVOL (=output of products for pipes and fittings/prodvol*100), PROVOL (=output of products for profiles/prodvol*100), CABVOL (output of products for cables/prodvol*100), PIPSALES (sold pipe grades/salesvol*100), PROSALES (sold products for profiles/salesvol*100) and CABSALES (sold cable grades/salesvol*100). Two dummy variables, STEPAU04 and STEPMR06, have binary values assigned by this author. Other reliable data such as raw material cost, sales prices and Non-Refundable Value-Added Tax were also investigated but do not directly influence the models in this thesis. This author may grant access to these additional data on request.
The source data can be characterized as follows: All variables are continuous. The sample size is very small (n=40). The data are longitudinal, which means that the observations have been measured over successive equally spaced periods of time. This could have serious consequences for the widely used linear regression analysis since the assumptions of ordinary least squares (OLS) regression sometimes do not hold with longitudinal data (Mendenhall et al., 1993, p. 610). More complex time series models must be considered. In time series analysis, certain characteristics of the data are of great importance. The dependent variables to be predicted are therefore plotted in a sequence chart on the following pages.
illustration not visible in this excerpt
Figure 1: Average cost of sales per unit
The average cost per unit sold varies considerably. The fact that the series wanders indicates that it is not stationary. A series is weakly stationary if its mean and variance are constant over time (West and Harrison, 1997). The chart above shows that neither mean nor variance of AVGCOST is constant. The short-term mean level varies over the course of the series and the variance is very small at the beginning. The series exhibits a short-term downward trend starting in April 2005 and a recent upward trend starting in September 2006. It also shows seasonality in winter whenever output is low. There is no evident outlier in the series. Another interesting phenomenon is that the value of each observation tends to be close to the value of the preceding observation. Each value tends to be positively correlated with the preceding value, as if the series had a memory. This common characteristic of time series data is known as positive autocorrelation.
The sequence chart of the average sales price net of Value-Added Tax (VAT) is shown in figure 2 below:
illustration not visible in this excerpt
Figure 2: Average net sales per unit
The average net sales price, too, is nonstationary and positively autocorrelated. The series shows two distinctive upward trends and one downward trend. It does not exhibit a distinct cyclical seasonality like AVGCOST does. The value of the observation February 2006 is exceptionally small1. The consequences of these findings will be discussed in detail in chapter 4.7.5.
The following examination of the reliability of the data used in this thesis concludes this chapter: All the data were obtained directly from official company reports by this author.
In DCCP, the accounts have to be completed by the end of the 26th of each month. Most of the reports are then sent to the Group Headquarters in Austria. The reports issued between January 2004 and September 2006 have been checked and approved by auditors of a renowned western CPA organization. All the data are very accurate, reliable and known with certainty.
However, this does not imply that the data in company reports are 100 % correct. In fact, one outlier revealed an obvious mistake in the observations January 2007 and February 2007 of COGS in the P&L. This author investigated and corrected the mistake so that a value that had mistakenly been allocated into January’s COGS is now assigned to February’s figure. Thus, the total of the two observations did not change. No other corrections have been made to the data.
The figures in the reports were calculated by the company accountant and the sales administrative according to Chinese accounting standards and Generally Accepted Accounting Principles (GAAP) directives. However, part of the information used to calculate the data was provided by third persons such as the cashier or the production manager. This author cannot totally rule out the possibility that random error and rare mistakes occur. From a scientific point of view, one should remain realistic and in this context rather be critical than naive.
Still, the data are highly reliable and have not been affected by accounting reforms. Uncertainty could have a small effect on the results obtained from a statistical model. However, it is unlikely that the uncertainty affects the range of results in such a way that they have to be considered unreliable. To sum up, this author places great confidence in predictions based upon models using the present data. Therefore, statistics in this thesis will be tested at a 5 % significance level. In other words, the level of confidence is 95 %.
Building a Model for Forecasting Cost of Goods Sold 19
4 ANALYSIS
4.1 Building a Model for Forecasting Cost of Goods Sold
Equation 1 shown below describes how the value of COGS reported in the P&L is calculated:
illustration not visible in this excerpt
According to Chinese legislation and GAAP directives, inventories are valuated using the weighted average cost method, as indicated by equation 1 (ARB 43, 1953, chapter 4.5). The value of raw material transferred to production is, therefore, a weighted average of the opening stock value and the total value of purchased material.
Note that at times the opening stock for a specific raw material is zero, and, for some raw materials, there may be no goods received within a given month. Even if we could estimate future raw material prices and the quantity to be purchased, we would have to know opening stock values and quantities as well as the amount of raw material to be transferred to production, in order to accurately predict the effect on the COGS in this first step of forecasting. In fact, we would need this information for each of the about 55 different materials that are currently used in production.
At this point, it is clear that the actual method for calculating COGS, namely equation 1, does not constitute an appropriate framework for forecasting COGS. It certainly would not make sense to formulate a regression model with hundreds of predictor variables that cannot even be estimated by the management. Besides, fitting a model to data with such a small n in relation to a large number of explanatory variables results in overfitting (Mendenhall et al., 1993, p. 609).
Instead, this author suggests designing a simplified model capable of producing timely and sufficiently accurate forecasts. The idea is to formulate a model which predicts the average cost per unit sold (= COGS / SALESVOL). In order to calculate COGS, the forecast obtained by the model will then be multiplied by the total quantity expected to be sold. The model should contain a few foreseeable key regressors, including one that explains the effect of raw material cost on COGS. This is of great importance since increasing material prices are a pressing issue and raw material cost accounts for 80 to 90 percent of the cost of finished goods. The difficulty in this approach is that there are a vast number of different raw materials and many have changing, irregular procurement and consumption patterns.
One way to tackle the problem is to focus on the materials with the biggest impact on COGS. By far the biggest business segment is the sale of stabilisers based on lead. A few key materials are major components of most of the lead type products in the portfolio. These raw materials are all derived from the metal lead. Traditionally, the market price of lead plays an important role in the business and is, therefore, closely monitored by the management. A thorough analysis shown in figure 3 on the following page revealed that the five raw materials that contain lead account for 62.4 % of the total monetary value of raw material transferred to production between September 2005 and March 2007.
Throughout the observed 19 months, the consumption values of the group of lead containing raw materials are quite stable, with a minimum of 54 % and a maximum of 70 % of the total value in a particular month.
illustration not visible in this excerpt
Figure 3: Ranked composition of 90 % of the total raw material value used in production within 19 months (raw materials made from lead are depicted in blue font)
The market price of lead should constitute a good leading indicator of how this significant fraction of raw material costs ultimately affects the COGS (via the cost of finished goods): Firstly, lead price and the cost of raw materials produced from lead are positively correlated. Secondly, the current month’s known lead market price acts as a predictor for next month’s cost of lead containing raw materials since suppliers do not immediately pass price changes on to DCCP. The price of lead, therefore, can serve as a regressor to predict the value of the average cost per unit sold one month later.
Figure 4 on page 22 shows that the contribution of the lagged lead price to the explanation of the average cost per unit sold is actually smaller than one would expect. At first sight, it seems as if there were no linear relationship at all in the two-dimensional depiction. This is due to other variables also affecting the average cost per unit sold, for example when the lead price remained constant at 8000 for some time.
illustration not visible in this excerpt
Figure 4: Lagged lead price as a regressor
In fact, it seems that a cubic function would outperform a linear model. However, the difference in goodness of fit is small. First and foremost, there is no underlying economic theory that could justify a cubic relationship. The observed cubic shape is to a certain extent a result of the introduction of cheaper raw materials and products by the R&D department at the time when the lead price went from 6000 to 10000. For these reasons, and for the sake of simplicity, it is assumed that the relationship is linear.
In reference to equation 1, the next step is to add a sum of manufacturing expenses to the total value of raw material used in production. The ME consist of 31 entries from the P&L, including direct and indirect personal expenses of production. Since rental and insurance fees, depreciation, salaries, and wages account for a large proportion of ME, they can be regarded as more or less fixed costs.
The total cost of raw material value and ME is then equally allocated onto each unit of inward produced goods. The larger the total manufacturing output PIQ, the smaller the amount of ME in the cost of each unit produced, and, consequently, total manufacturing output and unit cost of inward produced goods should exhibit a negative nonlinear relationship due to the fixed character of the manufacturing expenses. The scatterplot on the following page verifies this presumption:
illustration not visible in this excerpt
Figure 5: Effect of output and lagged lead price on the average cost per unit sold
The graph above displays how the lagged lead price and output affect the average cost per unit sold. The relationship between PRODVOL and AVGCOST seems to be of a cubic or logarithmic functional form. Similar reasoning as before leads to the rejection of a cubic function, for it is not justified by the underlying theory. It makes sense, however, to assume a logarithmic relationship of the form Y = b0 + b1* LN(X). It certainly fits the data better than a linear model. Consequently, a new series LPRODVOL defined as the base-e logarithm of the total manufacturing output is created. LPRODVOL will serve as the second regressor since it is relatively easy for the management to estimate the expected output one or two months ahead of time.
The cost of finished goods (one may picture these products as waiting for quality approval from the laboratory or ready for dispatch) is once more determined by the weighted average of their opening stock value and the value of possible inward finished goods coming from production. As pointed out before and indicated by equation 1, the unit cost of all of these inward finished products is identical regardless of their type and true cost. Consequently, the average cost per unit sold is not so much determined by the mix of sold products but instead by the mix of products that were manufactured in a given month, assuming that the output quantity is significant and that a significant number of these manufactured products is sold in the same month. If a higher proportion of expensive raw materials was used in production, then the average cost of all inward finished goods is higher in relation to the previous month. Even if the true cost of the majority of sold products were in fact low, the average cost per unit sold is still high, as long as the less expensive products were manufactured in the current month and, therefore, reflect the high average cost. Similarly, a sales mix of a majority of expensive products does not necessarily imply a high average cost per unit sold, for some expensive products could have been part of a production mix with many cheaper materials or some could have been manufactured at an earlier point in time when raw material prices were lower. Hence, the model should take account of how the composition of output affects the average cost per unit sold.
The following approach suits the idea of fitting an applicable, simplified model best: The product portfolio can be classified into 3 large groups based on the group of applications, for which the products have been designed, that is, pipe + fittings, profile and cable. The products within one category differ a little in terms of price and raw material cost. Still, on average they all have common characteristics that distinguish them from products of the other 2 categories. In this context, the relevant characteristic is the average raw material cost per unit:
True average raw material cost per unit
illustration not visible in this excerpt
Figure 6: Average raw material cost per category
Figure 6 shows the actual total raw material cost net of VAT of goods sold per category divided by the total quantity sold of each category on a monthly basis. The straight lines represent the total material cost of all observations divided by the total quantity of the period for each category.
Although the average raw material cost of the pipe mix fluctuates, it is almost always less than the average of the other categories. Since, on the average, pipe grades consist of significantly cheaper raw materials than the products in the other categories, output of pipe grades and the average cost per unit sold should be negatively correlated, assuming that the cost of inward finished goods significantly affects the average cost per unit sold. So the quantity of pipe grades expected to be manufactured (PIPVOL) will serve as the third regressor in the model. To make estimation as convenient and simple as possible, PIPVOL is expressed as a percentage of PRODVOL.
In order to conclude with equation 1, the term NVAT must briefly be discussed at this point. The Chinese Value-Added Tax legislation is similar to the VAT regulations in most of the Western world. Just like in Germany or in Austria, the amount of VAT-In paid by an enterprise is deducted from the amount of VAT-Out collected from its customers. As usual, this amount of VAT Payable to the tax bureau does not affect the enterprise’s P&L, as long as all sales transactions take place within the country. Since export sales are exempted from VAT, the Chinese tax authorities refund qualified enterprises part of the VAT-In paid to local suppliers. The remaining additional tax burden, called Non-Refundable VAT, ultimately affects the P&L. In the present case, however, the effect of the amount of Non-Refundable VAT on COGS is not only insignificant (NVAT usually accounts for less than 0.2 % of COGS, 1 % at most), it is unpredictable. The Non-Refundable Value-Added Tax will, therefore, be ignored when forecasting the cost of sales. Refer to China Briefing (2007, chapter 38) for a more detailed discussion of the Chinese VAT and export refund system in English.
So far, three predictor variables have been identified. The description of data in chapter 3 detected that the average cost per unit sold is positively autocorrelated. Displaying autocorrelations will confirm and clearly define this finding. The autocorrelation function (ACF) simply gives the correlations between observations of a series by lag. The partial autocorrelation function (PACF) gives the corresponding partial autocorrelations, that is, the correlation between values at different times after the effects of intervening lags have been removed. Plots of both ACF and PACF together are important identification and diagnosis tools:
[...]
1 Because 40% of the quantity sold in February 2006 were pipe grades.
- Quote paper
- Arno Palmrich (Author), 2007, Time Series Models for Short-Term Forecasting Performance Indicators, Munich, GRIN Verlag, https://www.grin.com/document/134834
-
-
-
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X. -
-
Upload your own papers! Earn money and win an iPhone X.