Regression formula (calculating trends)

[Bourbon daddy]

Hi guys,

I have been tasked with the following question;

The company has set a fixed budget for Period 1 of the financial year using the following figures as being 100% of the budget:

Material costs £200 000
Labour costs £100 000
Variable overheads £50 000
Fixed overheads £50 000

Material cost, Labour costs and Variable overheads had favourable variances of:
£38 000 / £16 000 and £14 000 respectively, whilst the Fixed overheads had an adverse variance of £2 000.

a) Set out the fixed budget using the budgeted figures above, and calculate the actual figures, the variances and the trends.


Ok, so taking material costs as an example, I have to calculate the actual figures of the fixed budget (this is given as £200k).

Calculate the variance, this is a positive variance of £38k, therefore material cost was actually £200K-£38K=£162K.

Now, in calculating the trend.having done a bit of statistics in the past and am familiar with the regression formula, in which I would calculate the slope and the y intercept and insert the data into the formula;

y=a+bx

But in this instance that seems a bit long winded, am I overcomplicating this, or is it just a case of opening excel and getting on with it using the regression equation.

Regards

Joe

 

[Ray Vickson]

Hi guys,

I have been tasked with the following question;

The company has set a fixed budget for Period 1 of the financial year using the following figures as being 100% of the budget:

Material costs £200 000
Labour costs £100 000
Variable overheads £50 000
Fixed overheads £50 000

Material cost, Labour costs and Variable overheads had favourable variances of:
£38 000 / £16 000 and £14 000 respectively, whilst the Fixed overheads had an adverse variance of £2 000.

a) Set out the fixed budget using the budgeted figures above, and calculate the actual figures, the variances and the trends.


Ok, so taking material costs as an example, I have to calculate the actual figures of the fixed budget (this is given as £200k).

Calculate the variance, this is a positive variance of £38k, therefore material cost was actually £200K-£38K=£162K.

Now, in calculating the trend.having done a bit of statistics in the past and am familiar with the regression formula, in which I would calculate the slope and the y intercept and insert the data into the formula;

y=a+bx

But in this instance that seems a bit long winded, am I overcomplicating this, or is it just a case of opening excel and getting on with it using the regression equation.

Regards

Joe

Are you sure you have interpreted the data/question correctly? How can you possibly estimate a trend (i.e., time-behaviour) without having several data points spread out over different time periods?

Are you sure that "favourable variance", etc, is not speaking about time-behavour? That is, are you sure that material cost in period 2 will not be 200,000 - 38,000 (that is, 38,000 better next period)? If this is not what is meant I cannot make any sense of the problem's statement; I know quite a bit about Statistics, but have never heard the term "favourable variance", etc. Variance is just variance, favourable or not. If you have seen some other meanings in your course material you need to explain it here, because your terminology is non-standard.

 

[Bourbon daddy]

Are you sure you have interpreted the data/question correctly? How can you possibly estimate a trend (i.e., time-behaviour) without having several data points spread out over different time periods? That is exactly my query. I have done a bit of statistics, but like you have said you can't really spot a trend if you have only two points of data.

As for Favourable variance, this subject is completely new to me, I am an electrical engineer and have done no business studies in the past, so I can't say whether it is correct terminology or not. I did originally google the term and have seen it used on websites.

Taking n as 2

Are you sure that "favourable variance", etc, is not speaking about time-behavour? That is, are you sure that material cost in period 2 will not be 200,000 - 38,000 (that is, 38,000 better next period)? If this is not what is meant I cannot make any sense of the problem's statement;

That is what I have assumed, my apologies if I have not been clear in this. I know quite a bit about Statistics, but have never heard the term "favourable variance", etc. Variance is just variance, favourable or not. If you have seen some other meanings in your course material you need to explain it here, because your terminology is non-standard.

As for course material, I have absolutely none, not even a link to a website. It is a level 5 module, so I suppose that I am expected to just get on and research, however I have a lot of family life getting in the way at them moment and can't afford it as much time as I would like.

Item Budget Actual Variance Favourable/ Adverse? Difference (%) Trend
Material costs £200,000 £162,000 £38,000 Favourable 19% y=232000-32000(x)
Labour costs £100,000 £84,000 £16,000 Favourable 16% y=116000-16000(x)
Variable overheads £50,000 £36,000 £14,000 Favourable 28% y=64000-14000(x)
Fixed overheads £50,000 £52,000 -£2,000 Adverse -4% y=48000+2000(x)
Total £400,000 £334,000 £66,000 Favourable 16.50% y=64000-14000(x)

This is what I have cobbled together... whether it is correct or not is a different matter!

In terms of calculating the trend I used the standard linear regression equation...
a=(∑y-b(∑x) ))/n

b=(n∑xy-(∑x)(∑y))/(n∑x^2-(∑x)^2)

 

[Bourbon daddy]

Sorry, forgot to mention... obviously I was taking n to be 2

 

[Bourbon daddy]

part of my reply is contained within the quote (paragraphs 2,3,4 and 6 to be precise)