Question

Ndonye ltd is concerned about the apparent fluctuation in efficiency and wants to determine how labour costs are related to volume of production. The following data presents results of the 1 most recent weeks:

Week No.	1	2	3	4	5	6	7	8	9	10	11	12
Units produced	34	44	31	36	30	49	39	21	41	47	34	24
Labour cost	340	346	287	262	220	416	337	180	376	295	215	275

Required:

i) Using the high low method and the ordinary least squares method determine the cost function.                                                                                

ii) Assuming that the company intends to produce:

• 34 units
• 45 units

Estimate the labour cost to be incurred in each case using the two methods generated in part (i) above.

iii) Draw a scatter diagram to represent this data and super-impose a straight-line trend lines generated from each of the two methods.

Answer 1

1)

We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of labor cost here and x_values have Units produced values.

Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.    

The equation comes out to be -
labour cost = 77.08 + 6.1*Units produced  

2)

When Units produced = 34

Labour cost = 77.08 + 6.1*34 = 284.48

When Units produced = 45

Labour cost = 77.08 + 6.1*45 = 351.58

3)