Description
Please follow the instructions for this project and select the appropriate techniques that you have learned in this class in order to answer the following questions. Points will be deducted if the submission does not follow the following format:Use only clean sheets of good quality 8 1/2" x 11" white paper. Text should be typed on one side only. Do not put any perfume or cologne on the sheets, neither try to decorate the sheets of paper; remember, it's an academic piece of writing. A title page is essential. Pages should be consecutively numbered, with numbers put in the upper right hand corner, flush with the right margin and 1/2" from the top with 12 font size and 1.5 spacing. Please upload the copy of your project, using Excel output.
Problem-1
The data in Used Cars represent characteristics of cars that are currently
part of an inventory of a used car dealership. The variables included are car,
year, age, price ($), mileage, power(hp), fuel (mph), Region of origin
(manufactured in USA or in a foreign country), and single ownership (Yes=
owned by one or No= owned by more than one owner). The excel file for this
problem is stored under module called “Used Cars”.
You want to describe each of these variables, and you would like to predict
the price of the used cars. Make sure to take appropriate steps to analyze
this data set and write a mini report for the Car Dealer. Also, do you think
that the model is missing some important variables? If so, what are those
missing variables? Please explain. (50 points)
Use the UsedCars.xlsx dataset on Canvas to answer the following questions. This
dataset represents characteristics of cars that are currently part of the inventory
at a used car dealership. The variables included are car, year, age, price ($),
mileage, power (hp), fuel (mph), region of origin (manufactured in USA or elsewhere),
and single ownership (yes = owned by one owner, no = owned by more than one owner).
The owner of the dealership is interested in whether there is a relationship between
the price of a car and the age of the car. Specifically, he’s interested in whether
car age can predict car price.
1.
Based on the information above, develop the appropriate hypothesis regarding
the relationship between price and age.
2.
Write out the univariate regression equation designating price as the
dependent variable, estimate the equation from question 1, and report the
results. Based upon these results, what is the relationship between car price
and age?
In the provided dataset, there are other variables besides car price and age. The
dealership owner thinks that mileage, power and origin region may also predict price.
3.
Do you believe these other variables could be helpful in predicting price?
Determine the appropriate regression equation for predicting price using these
additional variables. Report the results of your multivariate regression
equation. Based upon your new results, what is the relationship between car
price and age?
Problem-2
The owner of a moving company typically has his most experienced manager
predict the total number of labor hours that will be required to complete an
upcoming move. This approach has proved useful in the past, but the owner has
the business objective of developing a more accurate method of predicting
labor hours. In a preliminary effort to provide a more accurate method, the
owner has decided to use the number of cubic feet moved and whether there is
an elevator in the apartment building as the independent variables. He has
collected data for 36 moves in which the origin and destination were the
borough of Manhattan in New York City and the travel time was an insignificant
portion of hours worked. The data are organized and stored in Moving. Follow
the appropriate steps, like hypothesis(s), test statistics, critical value/P-
value, decision and conclusion to complete the questions. Please insert Excel
outputs in your answer(s) wherever it is necessary.
(50 points)
a. Identify the problem and define the research question(s).
b. State the appropriate multiple regression equation for predicting labor
hours, using the number of cubic feet moved and whether there is an
elevator.
c. Interpret the regression coefficients in(a) and evaluate your
hypothesis(es).
d. Check the regression model validity
e. Is there a significant relationship between labor hours and the two
independent variables (cubic feet moved and whether there is an elevator
in the apartment building) at the 0.05 level of significance?
f. At the 0.05 level of significance, determine whether each independent
variable makes a contribution to the regression model. Indicate the most
appropriate regression model for this set of data.
g. Predict the mean labor hours for moving 500 cubic feet. What should you
tell the owner of the moving company about the relationship between
cubic feet moved and labor hours?
h. Construct 95% confidence interval estimate of the population slope for
the relationship between labor hours and cubic feet moved.
i. Compute and interpret adjusted r2.
j. Add an interaction term to the model, and at the 0.05 level of
significance, determine whether it makes a significant contribution to
the model.
k. On the basis of the results of (g) and (j), which model is most
appropriate? Explain.
l. As a business owner, what conclusions/solutions can you reach concerning
the effect of the number of cubic feet moved and whether there is an
elevator on labor hours?
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Attached.
Running Head: STATISTICAL ANALYSIS USING EXCEL
1
Statistical Analysis Using Excel.
Institution of Affiliation.
Student’s Name.
Date.
STATISTICAL ANALYSIS USING EXCEL.
2
Problem 1.
This research corresponds to the analysis of the characteristics and main features of cars whose data is a part of the inventory of a car
dealer company. The main variables in the dataset includes; car, year, age, price ($), mileage, power (hp), fuel (mph), region of origin
(manufactured in USA or elsewhere), and single ownership (yes = owned by one owner, no = owned by more than one owner). The
main objective of the study is to investigate the nature of association between the price of a car and the age of the car.
1. The Hypothesis
The following is the set of null and alternative hypothesis for the analysis
Null Hypothesis: There exists no significant relationship between the price of a car and its age.
Alternative hypothesis: There exists a significant relationship between the price of a car and its age.
2. Univariate regression analysis
The following is the regression equation model;
𝑃𝑟𝑖𝑐𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑐𝑎𝑟 = 𝛽0 + 𝛽1𝐴𝑔𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑐𝑎𝑟 + 𝜀1
The following are the regression analysis results;
SUMMARY
OUTPUT
Regression
Statistics
Multiple R
R Square
Adjusted R Square
0.964412592
0.930091648
0.92995127
STATISTICAL ANALYSIS USING EXCEL.
Standard Error
Observations
3
553.0145712
500
ANOVA
df
Regression
Residual
Total
Intercept
Age
1
498
499
Coefficients
12574.95135
420.6112589
SS
MS
F
2026278672 2.03E+09 6625.612
152300907.8 305825.1
2178579580
Standard
Error
49.78655278
t Stat
252.5773
P-value
0
5.167350035
-81.3979
7.2E-290
Significance
F
7.1865E-290
Lower
Upper
Lower 95%
Upper 95%
95.0%
95.0%
12477.13377 12672.76893 12477.13377 12672.76893
-430.7637529 410.4587648 430.7637529 410.4587648
The following is the result of the regression analysis model;
𝑃𝑟𝑖𝑐𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑐𝑎𝑟 = 12574.95 − 420.61 𝐴𝑔𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑐𝑎𝑟 + 𝜀1
The regression equation implies that the price of the car when the age of the car is zero is approximately 12574.95 and with any unit
increase of the price of the car, the age of the car decreases by 420.61 units. There is a very significant association between the age of
the car and the respective car prices which is clearly depicted by the immense amount of variation indicated by the R-squared statistic
0.93 hence implying that there exist approximately 93% amount of variation between the ...