Data Analysis - Regression Analysis utilizing EXCEL

2. Select Regression and Ok

3. Input Y data into the box

4. Input al X data (X1-X8) into the box

5. Click Label if you included the label

6. Click Confidence Level with default of 95%

7. Go to Residuals and tick all boxes

8. Tick Normal Probability box and click Ok

Output Analysis Guide:

1. Refer to the Regression Statistics table. Report R square value or adjusted R-square when you delete insignificant variables. The closer to 1, independent variables explain significantly the dependent variable. For example, R square is .94, implies that 94% of the variation in Y is explained by Xs (independent variables).

2. Refer to the ANOVA table. Look at the F statistics. The F value shows if the model brings a statistical significant explanation to the data. Refer to the Significance F, if the p-value is less than .05 significance level, then the model is significant.

3. Look at the Coefficient and P-value columns.

4. Coefficient values explain the amount of contribution of each X to Y.

5. P-value which is greater than .05 significance level implies insignificant result. The variable can be removed from the model.

6. Choose only variables that are significant for the final model if p-value is less than .05 (p<.05) level.

HATCO CASE INFO

The Excel file HATCO consists of data related to predicting the level of business (Usage Level) obtained from a survey of purchasing managers of customers of an industrial supplier, HATCO. The following are the independent variables.

· Delivery Speed – amount of time it takes to deliver the product once an order is confirmed.

· Price Level – perceived level of price charged by product suppliers.

· Price Flexibility – perceived willingness of HATCO representatives to negotiate price on all types of purchases.

· Manufacturing Image – overall image of the manufacturer or supplier.

· Overall Service – overall level of service necessary for maintaining of satisfactory relationship between supplier and purchaser.

· Sales Force Image – overall image of the manufacturer’s sales force.

· Product Quality - perceived level of quality of a particular product.

· Size of Firm – size relative to others in this market (0 = small; 1 = large).

Response to the first seven variables were obtained using a graphic rating scale, where a 10-cm line was drawn between endpoints labeled “poor” and “excellent”. Respondents indicated their perceptions using a mark on the line which was measured from the left endpoint. The result was a scale from 0 to 10 rounded to one decimal place.

Conduct a complete analysis to predict Usage Level. Be sure to investigate the impact of the categorical variable Size of Firm (coded as 0 for small firms and 1 for large firms) and possible interactions. Also stratify the data by firm size to account for any differences between small and large firms. Write up your results in a formal report to HATCO management.