MS 251 Statistical Analysis for Business Decisions Excel Empirical Project Hi,There are 4 tasks required to be completed using the attached excel file (ran

MS 251 Statistical Analysis for Business Decisions Excel Empirical Project Hi,There are 4 tasks required to be completed using the attached excel file (rand data). All the required instructions are in the word file. Please follow all instructions and show all steps. I need to score at least 85% in this assignment.Thanks MS 251: STATISTICAL ANALYSIS FOR BUSINESS DECISIONS
SUMMER 2020
Excel Empirical Project
You are required to conduct data analysis using the rand data. It is a cross-sectional data extract
from the Rand Health Insurance Experiment, prepared by Deb and Trivedi (2002).1 This social
experiment was designed to be representative of US families with adults under the age of 62.
Different insurance policies were randomly assigned to families. For some history on this
experiment, see https://www.youtube.com/watch?v=HtoUs_4OYVI. The aim was to determine the
effect of coinsurance rate on the use of health services.
You are expected to complete each part below as soon as we have covered the respective topics.
Videos will be provided on Blackboard for demonstrating how to conduct each part in Excel.
1. Random sample: Take a simple random sample of 5000 persons.
2. Interval estimation: Construct a 99%
confidence interval for the mean number
of outpatient visits to health providers
(totvis). Create a table in Word like the
figure to the right and record the Excel
confidence interval results. According to
a 2012 US Census Bureau report, adults
made an average of 3.9 visits to doctors,
nurses or other medical providers.2 Does
your estimated interval support this?
Explain.
3. Hypothesis testing: Test whether there
is sufficient evidence to conclude the
mean number of outpatient visits (totvis)
for all persons is 3.9. Create a table in
Word like the figure to the right and
record the Excel hypothesis testing
results. Provide a clear contextual
conclusion. Comment on whether the
conclusion is the same as in part 2.
1
1
2
3
4
5
6
7
8
9
10
11
1
2
3
4
5
6
7
8
9
10
A
Sample size
Point estimate
B
Level of Significance
Critical value
Standard Error
Margin of Error
Lower Limit
Upper Limit
A
Sample Size
Sample Mean
B
Population Std. Dev.
Hypothesized Value
Standard Error
Test Statistic
p-value (Two Tail)
Deb, P. & Trivedi, P. K. (2002). The structure of demand for health care: latent class versus two-part models. Journal
of health economics, 21(4), 601-625.
2
https://www.census.gov/newsroom/releases/archives/health_care_insurance/cb12-185.html
4. Regression analysis:
(i) Estimate a simple linear regression showing the effect of coinsurance rate (coins) on the
natural log of health care utilization, i.e. natural log of (1 + totvis). Copy the table below
and report the results of the regression in columns 2-3. Complete table with a title. Also,
provide data source and definitions of variables beneath the table.
(ii) Now consider a multiple linear regression.
o Consider three independent variables:
a) coins and sex must be included.
b) select one from the following: ndisease, age, famsize, educ, physlim
o Estimate the regression coefficients for the three independent variables on the natural log
of (1+ totvis). report the regression results in columns 4-5 of the table below. Ensure to
replace Var3 with the name of the variable selected in part (b) above.
o Using the multiple linear regression results, interpret the R2, F-statistic, and the effect of
each independent variable (in each case comment on statistical significance). Are the
coefficient signs in line with your expectations? Explain. Provide responses in Word.
o Did the coefficient for coins under the simple linear regression change upon adding more
variables via the multiple linear regression? Does that make sense? Explain. Provide
responses in Word.
Simple linear regression
Coef.
SE
coins
sex
Var3
Intercept
R-square
F-stat
Sample size
Multiple linear regression
Coef.
SE
Variable name
id
coins
income
age
sex
educ
totadm
mentvis
mdvis
notmdvis
famsize
mhi
ndisease
physlim
ghindx
child
hlthg
hlthf
hlthp
xghindx
anyvis
totvis
Variable label
person id number
coinsurance rate
family income
age in years
1 if female
education of household head in years
number of hosp. admissions
number psychotherapy visits
number of outpatient visits to an MD
numbr of outpatient visits to other health providers
family size
mental health index
count of chronic diseases
1 if has physical limitations
general health index
1 if age is less than 18
1 if good health
1 if fair health
1 if poor health
ghi with imputation
1 if visit MD at least once
number of outpatient visits to MD or other health professionals
totvis
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
Margin of Error
3.4466
0.086216
2
0
6.096374
37.16578
41.48096
5.186667
87
0
87
17233
5000
0.222162
Lower Bound
Upper Bound
3.224438
3.668762
t-Test: Two-Sample Assuming Unequal Variances
Mean
Variance
Sample Size
Hypothesized Mean
t Stat
P(T
Purchase answer to see full
attachment