ENGR200 Term Project — Excel ToolPak Version (Statics & GPA Dataset)

0) Open the ENGR200 project dataset

Download the Excel file ENGR200_Term_Project_Data.xlsx from Blackboard / course website and open the sheet ENGR200_Term_Project_Data_GPA.

From now on we treat Statics_Grade (H) as a natural response variable Y and the academic/effort variables (B–G) as predictors X. For your own project dataset, you may choose a different Y.

1) Descriptive statistics (ToolPak)

Goal: get a table of mean, median, std dev, min, max, etc. for the numeric variables (HS_GPA, SAT, Study, Attendance, Participation, Statics_Grade).

1. Turn on the Analysis ToolPak if needed: File → Options → Add-ins → Manage: “Excel Add-ins” → Go… → check Analysis ToolPak → OK.
2. On the Data tab, click Data Analysis… → choose Descriptive Statistics → OK.
3. Input Range: $B$1:$H$151 (HS_GPA through Statics_Grade, including headers in row 1).
   Grouped by: Columns. Check Labels in first row.
4. Output options: choose New Worksheet Ply (e.g., name it “Descriptives_ToolPak”) and check Summary statistics. Then click OK.

You may also want to copy a few key numbers (mean, median, std dev) into a clean summary table that you will paste into your Word report.

2) Histograms for GPA and Statics grades

Goal: visualize the distribution of HS_GPA and Statics_Grade. Are they symmetric? Skewed? Any clear cutoffs?

A) Histogram for HS_GPA (ToolPak)

1. Choose some bin cutpoints (for example) in an empty column:
   2.0, 2.3, 2.6, 2.9, 3.2, 3.5, 3.8, 4.1.
2. Data → Data Analysis → Histogram → OK.
3. Input Range: $B$2:$B$151 (HS_GPA values).
   Bin Range: the cells where you typed the GPA bin edges. Check Chart Output.
4. Choose an output location or New Worksheet Ply → OK. Format the column chart nicely and label axes.

B) Histogram for Statics_Grade (ToolPak)

1. Pick bins such as: 65, 70, 75, 80, 85, 90, 95, 100.
2. Repeat the same Histogram steps using Input Range $H$2:$H$151.

In your report, comment on the shape: “Statics grades are roughly [symmetric / right-skewed / left-skewed], with most students between ~[80–90], etc.”

3) Scatter plots (Statics_Grade vs predictors)

Goal: see visually how Statics_Grade relates to the predictors. We’ll make at least three scatterplots:

• Statics_Grade vs HS_GPA
• Statics_Grade vs Study_Hours_per_week
• Statics_Grade vs Attendance_% (or SAT_Verbal)

1. Select the Y column and X column together (e.g., click H1, then Ctrl-click B1 to select both headers; then drag to select down to row 151).
2. Insert → Scatter → first option (Scatter with only markers).
3. Add a trendline if you like: click a point → right-click → “Add Trendline” → Linear; check “Display Equation on chart” and “Display R-squared on chart”.

Save each scatter plot in a clean worksheet (or as separate charts) with clear titles and labeled axes. You will paste a few into your Word report.

4) Correlation matrix (ToolPak)

Goal: compute the correlation matrix for HS_GPA, SAT_Math, SAT_Verbal, Study_Hours_per_week, Attendance_%, Class_Participation, and Statics_Grade.

1. Data → Data Analysis → Correlation → OK.
2. Input Range: $B$1:$H$151 (HS_GPA through Statics_Grade, with labels).
   Grouped by: Columns. Check Labels in first row.
3. Output: New Worksheet Ply (e.g., “Corr_ToolPak”) → OK.

Also look at correlations among the X’s. Here they are all small (about −0.1 to 0.2), so we do not expect serious multicollinearity.

5) Simple Linear Regression (ToolPak): Statics_Grade ~ HS_GPA

Goal: run a simple regression using the ToolPak with Statics_Grade as Y and HS_GPA as X.

1. Data → Data Analysis → Regression → OK.
2. Input Y Range: $H$1:$H$151 (Statics_Grade).
   Input X Range: $B$1:$B$151 (HS_GPA only).
3. Check Labels. Leave “Constant is Zero” unchecked.
4. Output: New Worksheet Ply (e.g., “SLR_GPA”) → OK.

In your report, write one short paragraph explaining what β₀ and β₁ mean in context, and whether the slope is statistically significant (look at its p-value).

6) Multiple Linear Regression (ToolPak): Statics_Grade ~ HS_GPA + SAT + Study + Attendance + Participation

Now we let Statics_Grade depend on all the academic/effort variables at once.

1. Data → Data Analysis → Regression → OK.
2. Input Y Range: $H$1:$H$151 (Statics_Grade).
   Input X Range: $B$1:$G$151 (HS_GPA, SAT_Math, SAT_Verbal, Study_Hours_per_week, Attendance_%, Class_Participation).
3. Check Labels. Output: New Worksheet Ply (e.g., “MLR_ToolPak”) → OK.

In your report, you might say something like: “When all predictors are included simultaneously, the model explains only about 5% of the variation in Statics grades; none of the slopes are statistically significant at the 5% level. This suggests that other unmeasured factors (e.g., study quality, prior coursework, motivation) may be driving performance.”

7) Multicollinearity check (simple version)

For this project, you do not need to compute full VIF formulas by hand. A simpler check:

• Look at the correlation matrix among the predictors (HS_GPA, SAT_Math, SAT_Verbal, Study, Attendance, Participation).
• If many correlations are above about |0.7|, multicollinearity is a concern.
• In this dataset, all predictor–predictor correlations are small (about −0.1 to 0.2).

For your own dataset in the term project, you should at least inspect the correlation matrix and comment on whether multicollinearity seems to be a problem.

8) What to submit (Term Project structure)

1. Excel workbook including:
   • Original data sheet (either this ENGR200 file or your own approved dataset).
   • ToolPak Descriptive Statistics output.
   • Histograms and scatter plots (can be on one or more chart sheets).
   • ToolPak Correlation matrix output.
   • ToolPak Regression output for:
     • At least one simple linear regression (Y on one X).
     • At least one multiple regression (Y on several X’s).
2. 5–8 page Word report that includes:
   • Brief description of the dataset and variables (what is Y, what are the X’s).
   • Clean tables of descriptive statistics (mean, median, std dev) and the correlation matrix.
   • At least two histograms and two scatter plots with captions.
   • Regression results: one simple and one multiple model (state the model, report key coefficients, R², and main conclusions).
   • Interpretation of at least one numeric slope in context (“holding other variables fixed…”).
   • Short comment on multicollinearity (based on correlation matrix and/or VIF, if you compute it).

Tip: Use the Statics/GPA example here to debug your Excel workflow. Once it works and your numbers match the “Expected” boxes roughly, repeat the exact same steps on your own project dataset.