Regression and Time Series
Regression and Time Series
Regression and Time Series
In order to prepare budgets, forecasts of costs and revenues will need to be undertaken.
Regression analysis attempts to find the factors that bring about the change in results. For
example, what is the relationship between advertising expenditure and sales volume?
Linear regression can be used to determine the relationship between two variables
and then to forecast other levels of costs / sales
Variables
The dependent variable ("y") is influenced by the independent variable ("x"). A linear
relationship
y = a + bx
will be established with "a" being the value of "y" when "x" is equal to 0. (eg what would
sales be with no advertising expenditure?) and "b" the increase/(decrease) in "y" brought
about by a change in x by 1 unit (eg how much extra sales revenue would Rs 1 of advertising
expenditure generate?).
Example
Quarter 1 2 3 4 5 6 7 8
Advertising expt (Rs ’000) 22 15 7 44 29 18 10 37
Sales (‘000 units) 11 12 7 20 14 11 11 22
To calculate the values of a and b for the straight line expression y = a + bx, the following
formulae are used:
Required
(a) Calculate b and a, and the expression for sales.
(b) Forecast the level of sales if advertising is Rs 20,000.
Ans y = 5.415 + 0.3554x 12,523 units
Q1 Following information is extracted from the records of Mega Limited for the year ended
Aug June 30, 2014:
2014 Direct Labour Electricity Cost
Month (Hours ‘000’) (Rs. ‘000’)
July 2013 34 640
August 30 620
September 34 620
October 39 590
November 42 500
December 32 530
Jan 2014 26 500
February 26 500
March 31 530
April 35 550
May 43 580
June 48 680
1
Required:
Compute variable electricity rate and fixed cost per month under the least square
(simple regression) method : 08
Q2 The management of Good Luck Company has asked for help in selection of the appropriate
Sept activity measures to be used in estimating electricity cost while preparing budget for one of its
2013 plants at Lahore. The information as given below shows utility expenses incurred in the past
year with two potential activity measures.
Required:
(a) Compute the coefficient of correlation ‘r’ and the coefficient of determination ‘r2’ between
the cost of utility and each of the two activity measures. 08
(b) Identify which of the two activity measures should be used as a basis to estimate the
allowable cost of utility. 03
(c) Using the activity measure selected in requirement (b) above, compute an estimate of
fixed utility cost and the variable utility rate by the method of least squares. 04
2
Time series analysis
An analysis of past patterns of demand or sales which will be used to construct expected
patterns in the future.
Components of a time series
(a) Trend (T): the underlying increase or decrease in demand.
For example, a steady decline in the average sales of a national daily newspaper or a
steady increase in sales of Sony PlayStations.
(b) Seasonal variations (SV): short-term repeated fluctuations from the trend.
For example, sales of tabloid newspapers being higher on Mondays and Saturdays
than other days due to the extra sports coverage, or sales of ice cream being higher
in summer than in winter.
(c) Cyclical variations: recurring patterns over a longer period of time, not generally of a
fixed nature.
For example, changes in unemployment, movement from recession to economic growth.
(d) Random variations: these will be included in past data but could not be included in
future estimates.
For example, high sales of a tabloid newspaper due to exclusive photographs of a
member of the royals in a compromising position.
Ex-1 The following represents Trisaris Ltd's sales figures and trend figures over the last three years.
Time Period Time Series (sales)
Y1 Q1 18
Q2 60
Q3 90
Q4 102
3
Y2 Q1 30
Q2 72
Q3 99
Q4 120
Y3 Q1 36
Q2 90
Q3 114
Q4 135
Required
Use the multiplicative model to forecast quarterly sales in Year 4.
Ex-2 The management accountant at Ran Bay Sunglasses has determined the trend equation of sales
against time is
y = 1,000,000 + 80,000 x
where x denotes the quarter (x increases by one for each new quarter) and y is unit sales. The
average seasonal variations in demand are shown below:
Q1 Q2 Q3 Q4
Average S.V. -15% 10% 15% -10%
Required
Forecast sales for quarters 17 to 20, assuming the same pattern of seasonal variations to apply.
Q1 Al-Asar International manufactures and sales non-carbolic drinks. Demand for product is
Feb increasing approximately 10% per annum, but vary based on climatic conditions in
2014 different seasons of year. Quarterly sales data for last two years is as under:
Year Quarter Volume of Sales
(Million Bottles)
2012 Q-1 450
Q-2 750
Q-3 825
Q-4 625
2013 Q-1 1500
Q-2 825
Q-3 900
Q-4 675
Required:
You have been working as Financial Controller in Al-Asar International and asked by
managing partner to calculate the following:
(i) Quarter-wise trend (T) for 2012-13 (Q-3, Q-4, Q-1 and Q-2). 07
(ii) Seasonal variances (SV) for above quarters using Proportional (Multiplicative) Model
(Answer T and SV of Q2 2013 - 718.75 & 1.148)
5
The management accountant found following national quarterly seasonal index in a
natiomnal journal. He thought that this store has a product mix not too different from
aggregate mix on which the index was based.
Q1 94
Q2 98
Q3 96
Q4 112
Required:
a) Calculate the values of the deseasonalised data of past ten quarters.
b) Plot the actual sales figures and deseasonlised data on graph paper.
c) Use the method of least square to determine the equation of the straight line through
the deseasonlised date.
d) Estimate the gross sales figures for the third and fourth quarter of 2016.
Q4 Sauce Co manufactures and sells cartons of cooking sauces, which deteriorate over time and must
be used within three months. Over the last two years, Sauce Co has experienced all kinds of problems.
The financial and sales directors believe these to be a result of persistently unrealistic sales targets
imposed by the managing director, who makes forecasts based on his own subjective and overly
optimistic views about future sales. Whilst an incentive scheme is in place for employees, the company
has not hit its targets for the last three years, so no bonuses have been paid out. The financial director
has asked you to forecast the sales for the last two quarters of 2012, hoping to present these figures
to the managing director in an attempt to persuade him that the basis of forecasting needs to be
changed. Production volumes are also currently based on anticipated sales rather than actual orders.
The following sales figures are available for the last two years. All of the figures represent actual sales
except for quarter 2 of 2012, which is an estimate. The financial director is satisfied that this estimate
can be relied upon.
’000 units
Year Quarter One Quarter Two Quarter ThreeQuarter Four
2010 900 1100
2011 1200 1000 1050 1300
2012 1400 1150
The following centred moving averages have been calculated, using a base period of four quarters:
2011 1068.75 1112.5 1162.5 1206.25
2012 1243.75 1287.5
The average seasonal variations for 2010 have already been made available to you and are 0·908
for quarter 3 and 1·082 for quarter 4. The random component is negligible and can therefore be ignored.
Required:
Using the information provided above, and assuming a proportional (multiplicative) model, forecast the sales
of Sauce Co for the last two quarters of 2012. Calculate your seasonal adjustments to four decimal places.
(Answer sales Q1 & Q2 1,331 & 1,375) (10 marks)