Business Analytics AG1

Submission Instruction:

 Individual or Group Submission (groups of 2-4),

 Only one word document (.doc or .docx) by one student from each group,

 Pay attention to the due date and try to make timely submissions (penalty for late submissions)

 Put a table in the first page and include names, student IDs, and a group photo for verification.

 The solution will be briefly discussed in class (in the first session after the due date).

Chapter 2: Descriptive Statistics

ABC Real Estate Company renovates and sells houses in different areas of Los Angeles. A sample of 50

houses was selected from their previous year’s final sales (Date File: CH2). Selling price, size, location

(name, population, jobless rate), and type of the houses are included in the dataset. The company would

like to use the sample data to determine the average house price in different regions, and whether or not

area population and jobless rate play any role in the prices.

Use the methods of descriptive statistics to learn about the ABC Real Estate Company’s activities.

Include the following in your report.

1. Graphical and numerical summaries to display the scope of company’s activities in terms of house

sizes, prices, and locations (tables and histograms). Discuss what you learn about the company in terms of

its variety of operations.

2. Summarize the frequency, selling price, size (ft2), and price per ft2 for different house types in different

neighborhoods. Try to come up with valuable insights. For example, which neighborhood is more

expensive; are detached houses on average larger than condominiums, any exceptions, etc. Is there any

limitation in extracting insights?

3. Develop scatter diagrams to explore the relationship between the average price per ft2 and area’s

characteristics (e.g. population and jobless rate). Use the vertical axis for price per ft2. Compute the

sample correlation coefficients to support your observations.

4. Is there any linear relationship observable between house sizes and per-ft2 prices? Does it make sense?

Chapter 3: Data Visualization

The file TaxData contains information from federal tax returns filed in 2007 for all counties in the United

States (3,142 counties in total). Use the data-visualization methods presented in this chapter to explore

these data on CH3 file and discover relationships between the variables. Include the following in your

report:

1. First add two new columns to compute Average Adjusted Gross Income and Average Wages and

Salaries Income per return (report/family), both in thousands, for each county (Hint: average income

would be total income divided by total number of tax returns).

2. Create a frequency distribution, cumulative relative frequency distribution, and histogram for Average

Adjusted Gross Income per return in U.S. counties. Use bin sizes of 5. Interpret the results. Do any data

points appear to be outliers in this distribution (Hint: if you find any row with measurement errors, you

should remove them and update the data set)?

3. Create a PivotTable in Excel to answer the questions below. The PivotTable should have State

Abbreviation as Row Labels. The Values in the PivotTable should be the sum of adjusted gross income

for each state.

a. Sort the PivotTable data to display the states with the smallest sum of adjusted gross income on

top and the largest on the bottom. Which states had the smallest and largest sum of adjusted gross

income, respectively? What is the total adjusted gross income for federal tax returns filed in these

two states? (Hint: To sort data in a PivotTable in Excel, right-click any cell in the PivotTable that

contains the data you want to sort, and select Sort.)

b. Show the values in your Pivot Table as percentage of grand total. How many percent of the

total adjusted gross income in the United States was provided by the state of California? What

percentage by Texas?

c. Add the County Name to the Row Labels in the PivotTable. Sort the County Names by Sum of

Adjusted Gross Income with the lowest values on the top and the highest values on the bottom.

Filter the Row Labels so that only the state of Arizona is displayed. Which county had the

smallest sum of adjusted gross income in the state of Arizona? Which county had the largest sum

of adjusted gross income in the state of Arizona?

4. Create a bar chart to compare the top 10 U.S. states in terms of their Total Adjusted Gross Income (in

thousands). Sort the data for a nicer demonstration.

Chapter 6: Statistical Inference

The CSUSM Car Wash Company has recently implemented a new technology package in order to decrease

customers’ total waiting time in the system. Even though most employees believe this is a great move, no

study has been conducted yet to measure the actual effectiveness of the new system.

As a new employee of the company who is able to perform statistical (inferential) analyses, you have been

asked to help analyze the performance of the new system. As the first step, you have selected an anonymous

sample of 400 customers who have work with both the previous and the new systems, and collected their

waiting time, satisfaction score, and the number of times they came for car wash services in one year (arrival

rate) under both settings (refer to CH6 data file).

1. Develop 95% confidence intervals for the following parameters for before and after the new system was

implemented:

 Average waiting time of customers in the system,

 Average satisfaction score,

 Proportion of customers with satisfaction score of 4 and above,

2. Develop null and alternative hypotheses for the following claims:

 In the new system, people wait less than 14 min on average,

 More than 35% of the member customers are from Region 2,

Then, conduct hypothesis tests at the 0.05 level of significance to make your conclusion (regarding rejecting

or not rejecting the null hypothesis).

3. Implementing the new system required a great deal of money from the company. Major shareholders

have asked the company to justify this cost. Would you be able to help the company present the benefits of

the new system? [Hint: you should test for favorable changes in mean satisfaction score, mean waiting time,

and mean number of car wash service provided].

4. Test to see if members from different regions had equal average number of car wash purchases. Test it

for both before and after the new system was implemented.