Question

You have collected the following data for your company, in an attempt to determine a way to predict future Indirect Labor expenses.

week	Indirect Mfg. Labor Exp	Machine hours	DL hrs	3 of Production batches
1	2800	700	120	450
2	6700	1200	110	520
3	3200	300	340	500
4	1500	200	160	110
5	1500	400	740	220
6	3600	600	310	380
7	7800	1300	800	700
8	8400	1400	430	730
9	4300	1500	700	600
10	4500	600	500	350
11	3000	700	150	300
12	5300	1500	560	700
13	8400	1300	530	710
14	4600	300	600	400
15	6900	1100	350	660

Run multiple regression analysis in Excel using the two best choices for your independant variables (possible drivers of cost). Answer the following questions:

How much of the variablility around Indirect Labor is explained by your independant variables? (enter percent with one decimal place, such as 12.3%)________

Adj Coefficient of Determination: ___________%

Construct a cost equation. Round fixed cost to dollars and variable costc to 2 decimal places:

Y = ________ +_______ mach hrs + _______ batches

If the company estimates there will be about 1600 machine hours, 300 DL hrs, and 600 production batches next week, what might the company anticipate in Indirect Labor Expense (round to the nearest dollar)

$_______________

Answer 1

Firstly enter this data in the excel file.

Go to 'Data Analysis' on the top left corner and click, you get a dialogue box where you got to select 'Regression' as shown below.

select Indirect Mfg. Labor Exp column as your 'Input Y Range' (Dependent variable) and Machine hrs, DL hrs and 3 of Production batches colums as your 'Input X range' (Independent variables) . Check right 'Labels' and the click on 'OK'. This is shown below

After clicking 'OK' you get the following sheet which containf the output. The Above table is known as Rgression Table and the below table is known as Annova Table . This is shown below

Lets answer you question.

Q )How much of the variablility around Indirect Labor is explained by your independant variables?

R square tells us the variation in the dependent variable by the variation in the independent variable. from the table R square is 74.56%

Q ) Adj Coefficient of Determination: ___________%

adj co-efficient of determination is Adjusted R square which is 67.62%

Q) Construct a cost equation.

The cost Equation is derived from the ANOVA table , from 'coefficients' column

Y = -68.6728 + 0.408 mach hrs + 9.66 batches

Q) If the company estimates there will be about 1600 machine hours, 300 DL hrs, and 600 production batches next week, what might the company anticipate in Indirect Labor Expense (round to the nearest dollar)

The equation from ANOVA table is

Y= -68.6728 + 0.408 mach hrs - 0.414 DL hrs + 9.66 batches

substitute respective values

Y= -68.6728 + 0.408 (1600) - 0.414 (300) + 9.66 (600) = 6255.93

Rounding to the next nearest dollar

therefore  Indirect Labor Expense = 6256 dollars