#### treystasio

##### New Member
Currently, I'm trying to create a regression equation that can help my boss predict employee hours for the upcoming month. Basically, if we already know how much we're going to produce, how do we determine how many hours this would take.

We have historical data from the past nine months that is broken down by material, size, and hours it took to produce that specific piece. Let's say we have products that come in many sizes and are made of one of four different kinds of metal (X1,X2,X3,X4). Currently, I have a linear regression equation for each size of each material. But we would like one equation that can cover everything.

If I know we will produce 700 units of product 1, 400 units of product 2, 200 of product 3, and 350 of product 4, how do I create coefficients that will give us a good estimate of hours.

hours = B1(700) +B2(400) + B3(200) + B4(350)

Do the coefficients come from historical averages? Could I somehow consolidate many simple linear equations (y=mx + b) to create a more general equation?

Any suggestions are greatly appreciated.

#### Masteras

##### TS Contributor
let's make it simple, you have data for the hours and the 4 variables, right? try a regression as you said, hours=a+b1x1+b2x2+b3x3+b4x4. but then check for correlations (i.e. collinearity) between the Xs, if they are significantly large, delete some Xs.

#### treystasio

##### New Member
First off, thanks for the response. After looking through my data, I think before I try what you suggested, I’m going to try something else.

I hypothesize that there is a positive linear relationship between output and man hours, as well as material size and man hours. Then my formula is like this --- Man Hours = a + X(Output) + X(Material size)

How do I determine the coefficients and intercept?

Here is a similar sample of data, sorted by size. Initially, I'm just trying to do this for each individual product.

PRODUCT One

Man hours, Output, Material Size

27.75, 1217.5, 2
12.25, 764.25, 2
9, 558, 2
9, 401, 2
18.5, 425, 3
8.5, 562.5, 3
12.5, 1034, 3
4, 251, 3
38.5, 1763, 4
13.5, 651.5 , 4
25, 2110, 4
5.5, 346.5, 4

PRODUCT Two

Man Hours, Output, Material Size
12, 91, 1
13.5, 78, 2
44.67, 278, 2
9.75, 57, 2
8, 20, 3
16.5, 53, 3
14.25, 53, 3
10, 183, 3
32, 83, 3
30.75, 132, 4
45, 127, 6
20, 38, 8

Last edited:

#### treystasio

##### New Member
When I ran a regression analysis for Product One in Excel, here are the coefficients I was given:

intercept: 4.427731388
ouput: 0.014746338
material size: -0.495514783

Do these look right?

I'm not sure why one of them is negative.