Question

Calculate all materials and labor variances in a spreadsheet by using a program like Excel. Be sure to include price, quantity, wage rate, and labor efficiency variances.

Answer 1

Calculation of All Material variances with the help of following example,

Example:1

The standard chemical mixture of enterprise is as follows,

Chemical	Standard Mix	Standard Price $ per kg
PQR	80%	$50
XYZ	20%	$100

Standard yield in production is 75%.

Actual production is 1800 kgs of output from the following,

Chemical	Standard Mix	Standard Price $ per kg
PQR	1400	60
XYZ	600	90

Calculate All material variances,indicating whether they are Favorable or Adverse.

Answer:

Calculation of Standard quantity ( SQ)

Yield=75%

Actual output=1800 kgs

SQ=1800 kgs/75%=2400 kgs

Standard Mix of Chemical=

Chemical	Standard Mix	Standard Mix Quantity
PQR	80%	2400 kg* 80%=1920kgs
XYZ	20%	2400 kg*20%=480kgs

Calculation of Revised Actual Quantity

total AQ=1400kgs +600 kgs= 2000 kgs of Chemical

Standard Mix of Material ( RAQ)=

Chemical	Standard Mix	Standard Mix Quantity ( RAQ )
PQR	80%	2000 kg* 80%=1600kgs
XYZ	20%	2000 kg*20%=400kgs

Terms used ,

SQ	Standard Quantity
AQ	Actual Quantity
RAQ	Revised Actual Quantity i.e rewritthen in standard proportion
SP	Standard Price per unit of material consumed
AP	Atucal Price per unit of material consumed

Chemical	SQ X SP	AQ X AP	AQ X SP	RAQ XSP
PQR	1920 kgs X $ 50= $96000	1400 kgs X $ 60=$84000	1400 kgs X $ 50=$70000	1600 kgs X $50= $ 80000
XYZ	480kgs X $100=$48000	600 kgs X $ 90= $ 54000	600 kgs X $ 100= $60000	400 kgs X $100=$40000
Total	$144,000	$138,000	$130,000	$120,000

Calculation of Material Variances,

Material PQR,

Material	Variance	Formula	Calculation	Variances
PQR	Matrial Cost Variance	SQ XSP-AQ XAP	96000-84000	12000 F
PQR	Matrerial Price Variance	AQXSP-AQX AP	70000-84000	14000 A
PQR	Material Usage Variance	SQXSP-AQXSP	96000-70000	26000 F
PQR	Material Yield Variance	SQXSP-RAQXSP	96000-80000	16000 F
PQR	Material Mix Variance	RAQXSP-AQXSP	80000-70000	10000 F

Material XYZ,

Material	Variance	Formula	Calculation	Variances
XYZ	Matrial Cost Variance	SQ XSP-AQ XAP	48000-54000	6000 A
XYZ	Matrerial Price Variance	AQXSP-AQX AP	60000-54000	60000 F
XYZ	Material Usage Variance	SQXSP-AQXSP	48000-60000	12000 A
XYZ	Material Yield Variance	SQXSP-RAQXSP	48000-40000	8000 F
XYZ	Material Mix Variance	RAQXSP-AQXSP	40000-60000	20000 A

Calculation of All Labour variances with the help of following example,

Example 2:

Compute Labour Variances from the following data

Particulars	Skilled	Semi skilled	Unskilled
Number in fstandard gang ( for 40 hour week)	16	6	3
Standard Rate per hour ($)	3	2	1
actual number in gang ( for 42 hour week )	14	9	2
Actual rate of pay ( $)	4	3	2

In a week, the gang as whole produced 900 standard hours. However, 4 hours per worker is considered idle time due to machine breakdown.

Answer:

Calculation of Standard Hours (SH)

Total standard Hours= 900 hours

Labour	Skilled	Semi skilled	Unskilled
Standard Mix	16	6	3
Standard Hours	576 hours	216 hours	108 hours

Calculation of revised Actual hours ( RAH)

Total Actual Hours (AH )=(14X42)+(9X42)+(2X42)=1050hours

Labour	Skilled	Semi skilled	Unskilled
Standard Mix	16	6	3
Standard Mix RAH	672 hours	252 hours	126 hours

Particulars	SH XSR	RAH X SR	AH X SR	AH X AR
Skilled	576 X $3=1728	672 X $2=$2016	(14X42)X$3=$1764	(14X42)X$4=2352
Semi skilled	216 X $2=$432	252 X$2=$504	(9X42)X$2=$756	(9X42)X$3=$1134
Unskilled	108 X $1=$108	126 X$1=$126	(2X42)X$1=$84	(2X42)X$2=$168
	$2,268	$2,646	$2,604	$3,654

Terms used ,

SH	Standard Hours
AH	Actual Hours
RAH	Revised Actual Hours i.e rewritthen in standard proportion
SR	Standard Rate per Labour Hour
AR	Atucal Rate per labour hour

Calculation of All labour Variances,

Particulars	Formula	Skilled	Variances	Semi skilled	Variances	Unskilled	Variances
Labour cost variance	SH XSR-AHXAR	1728-2352	624 A	432-1134	702 A	108-168	60 A
Labour Rate variance	AHXSR-AHXAR	1764-2352	588 A	756-1134	378A	84-168	84A
Labour Efficiency Variance	SHXSR-AHXSR	1728-1764	36 A	432-756	324A	108-84	24F
Labour Mix Variance	RAHXSR-AHXSR	2016-1764	252 F	504-756	252A	126-84	42F
Labour Sub Efficiency variance	SHXSR-RAHXSR	1728-2016	288 A	432-504	72A	108-126	18A

Please feel free to reach or comment back if you need further clarity r something is missing.