Instructions:
• Submit an excel file with neatly labeled worksheets • One worksheet per question
• Keep saving the file so you do not lose your work
• Use the Data Analysis toolpak for this assignment. Make sure that you do not click on
“Labels” within the Regression window if you have not selected the column labels Car rental has turned out to be one of the most important services operating in the rural and
urban sectors in the United States. Car sharing, growth in the urban population, and alternate
modes of public transportation are critical components to cities (and smart cities) that adversely
affect the business of car rentals. On the other hand, the structural constitution, distances from
cities, and population density of smaller cities and towns have contributed to a sustained revenue
stream for car rental agencies. The leading car rental companies operating in various pockets
around the country have, time and again, added cars to their fleet with technological solutions
to help with navigation, parking, and even entertainment Consider Avalon Car Company that operates out of a college town in Michigan, about 40 miles
away from a major city. The town has no public transportation system in place except for a
college-run bus service for students. There are two taxi services that run through the city and
mostly serve students and people of the town to get to airport and back. However, these taxi
services are expensive and offer no carpooling facilities. Hence, students or town dwellers avoid
using them. In addition to Avalon there is another car agency operating from about 10 miles away
called Crusader Car Company. Within a 10-mile radius of the college town there are two colleges
and a large university (total population: 98,000 students). In terms of fleet size, Avalon has an
advantage. It has 1120 economy cars and 410 luxury cars available to lease for various durations,
while Crusader has 1000 economy cars and 690 luxury cars. Avalon has a better-quality fleet (high
mileage and sturdy condition) and benefits from proximity, however, it is threatened by the lower
rates that Crusader charges. The student population is more sensitive to the rates than quality
but the rest of the people of the town, the faculty and staff do care about the quality. Both
agencies have pick up/drop off facilities at the airport. Avalon wishes to dominate the market and is looking for ways to establish a stronger foothold in
the area by attracting customers. Online reservations have been a boon to this business for
hassle-free and convenient bookings. The business is affected by multiple factors: Break time,
holidays, flights, volatility of crude oil prices, mileage, accidents, weather conditions,
business/leisure/student travel, age of the renters, discounts, advertising expenditure, rates of
Avalon, rate of Crusader, and so on. Please refer to the data sheet provided for the complete set
of data.. Consider yourself the owner of Avalon. You are trying to get a sense of the business and looking
for the factors that really affect your customer traffic. You would like to perform a regression
analysis: a. What is your dependent variable? Choose a variable that you can predict and is
under your purview as the owner of Avalon. For instance, you have no control over
variables like weather or accidents. So, you would not want to predict that. b. Conduct a correlation analysis. Make sure the dependent variable is a part of that
analysis. -i. Highlight (in blue) two independent variables with the strongest
correlation with the dependent variable -ii. Are there independent variables that show signs of multicollinearity?
Highlight (in red) those numbers in your correlation output. c. Select 3 independent variables that give the highest adjusted R squared. Make
sure that the output/combination of 3 variables you finally select are statistically
significant for 90% confidence level
