MGEB12 Excel Lab: Sample Test
Congratulation on completing the Excel Labs! I hope you found them practical and enjoyable
1. Scores for Topics in Macro are normally distributed with a mean of 70 and a standard
deviation of 10. Find the first quartile of the marks for the exam.
a. 76.7
b. 63.3
c. 65.2
d. 35.2
2. We want to know if the average number of sick days taken in a certain county is more
than 60. A sample of 45 people reveals an average of 65, with a standard deviation of 15.
What is the p-value of this test?
a. 0.03
b. 0.015
c. 0.01
d. 0.025
You are hired by UTSC to develop a model to decide who should be admitted to the university. You
hypothesize that determinants of student success is: a) high school grades b) SAT scores. You also
believe that in addition to those variables, participation in extracurricular activities is also likely help
to improve university performance. To further research this issue, you randomly sampled 100 of UTSC
students, all are fourth- year students. You collect this data.
1) University GPA: Sum of the first three years (ranging from 0 to 12)
2) High School GPA from high school (range: 0 to 12)
3) SAT score (range: 400 to 1600)
4) Extracurricular Activity: The average number of hours per week spent in extracurricular
activities in the last year of high school.
3. Which of the following variables has the strongest correlation with University GPA?
a. High School GPA
b. SAT score
c. Extracurriculars
4. Can we conclude that there is a difference in the population variance of High School and
University GPAs?
a. Yes, because the pvalue is 0.15
b. No, because the pvalue is 0.15
c. Yes, because the pvalue is 0.012
d. No, because the pvalue is 0.012
5. Create a histogram for SAT score with a bin width of 150, starting at bin of 800. Select the
correct one.
For the following questions, run a simple linear regression between University GPA and High
School GPA
6. What is the coefficient of determination?
a. 52.8%
b. 50.9%
c. 27.9%
d. 2.02%
7. What is the coefficient of correlation?
a. 52.8%
b. 27.9%
c. 27.1%
d. 2.02%
8. What is the equation of the simple linear regression?
a. ̂ = 5.02+3.05(HSgpa)
b. ̂ = 13.63-3.90(HSgpa)
c. ̂ =2.4+0.61(HSgpa)
d. ̂ = =53-17.5(HSgpa)
9. Since the p-value for the F test is ______, we ______ the null at 5% level of significance, and
conclude that the regression _______ significant
A. 2.75E-13/ reject / is
B. 4.98E-15/ reject/ is
C. 1.61e-08/reject/is
D. 1.61E-08/fail to reject/is not
10. The residual plot for the simple linear regression above is
Sub macro2()
Dim i As Integer
i = 1
Do While Cells(i, 1).Value <> ""
Cells(i, 2).Value = Cells(i, 1).Value + 10
Cells(i, 3).Value = Cells(i, 2).Value + 20
Cells(i, 4).Value = Cells(i, 3).Value + 30
i = i + 1
Loop
End Sub
Sub test()
Dim i, j, k As Integer
j = 0
i = 1
k = 0
Do While Cells(i, 1).Value <> ""
j = j + Cells(i, 1).Value
i = i + 1
k = k + 1
Loop
Cells(8, 1).Value = Int(j / k)
End Sub
11. Work with Sheet “VBA”. What would the following VBA code create?
a. do loop that assigns value of i to row i of the indicated column plus 10, 20, 30
b. do loop that assigns the value of the ith row to the 10th, 20th, and 30th rows of the
indicated column.
c. for loop that generates three columns, where each column is the value of the one
beside it plus 10, 20, and 30 respectively.
d. do loop that generates three columns, where each column is the value of the one
beside it plus 10, 20, and 30 respectively.
12. VBA question. What does the following code do?
a. a do loop that calculates the total number of observations to the last row
b. a do loop that calculates the sum of all the numbers in column 2
c. a do loop that calculates the sum of all numbers in column 1 divided by the total
number of observations in column 2
d. a do loop calculating the sum of all numbers in column 1 divided by the total
observations in column 1
If you would like more practice questions, please fill out this short evaluation form. I
will create and publish them as soon as we get 40 submissions!
Formula Sheet
Distributions Inverse Distributions
Distribution
Name Function Name Function
Normal
Distribution
=NORM.DIST(x, mean,
stdev, cumulative)
Normal CDF
Inverse
=NORM.INV(probability,
mean, stddev)
Standard
Normal
Distribution
=NORM.S.DIST(z_score,
cumulative)
Standard
Normal CDF
Inverse
=NORM.S.INV(probability)
Student t-
Distribution
=T.DIST(t_value,
deg_freedom,
cumilative)
t-
Distribution
Inverse
=T.INV(probability, df)
Chi-Square
Distribution
=CHISQ.DIST(x, df)
Chi-Square
Distribution
Inverse
=CHISQ.INV(probability,
df)
F-Distribution
=F.DIST(x, deg_num,
deg_denom,
cumilative)
F-
Distribution
Inverse
=F.INV(probability,
df_num, df_denom)
Extra
NAME FUNCTION
Standardizing Z
=STANDARDIZE(x,
mean, standard_dev)