STATISTICS (Excel)

DUE TOMORROW BY 5:00PM 

You are working as a data analyst for the XYZ car company. Your boss has given you an assignment looking at the fuel economy of cars and the profit a company makes on each car sold. The boss has given you data on 30 different cars and wants you to analyze it. The first thing your boss would like to know is what affects a car’s fuel economy (measured as the average number of miles per gallon or MPG). Your boss has been told the following things affect a car’s fuel economy:

Number of cylinders- the boss believes more cylinders lead to lower MPG

Transmission- your boss believes manual transmissions have higher MPG than all other types of transmissions.

Weight- your boss believes heavier cars have lower MPG.

Powered axles- your boss believes front-wheel drive is superior to rear-wheel drive, rear-wheel drive is superior to four-wheel drive, and four-wheel drive is superior to all-wheel drive when it comes to MPG.

In addition the boss want to know if cars produced by XYZ company have better or worse fuel economy compared to cars made by other companies.

The boss also wants you to determine if fuel economy affects the cost of producing the car and the profit the company makes from the car. The boss says the following increase the cost of the car:

Number of cylinders- the boss believes more cylinders lead to a higher cost.

Transmission- your boss believes manual transmissions are the cheapest. Automatic transmissions are the second cheapest. An ATMC transmission is the third cheapest, and a DCT transmission is the most expensive.

Weight- your boss believes heavier cars cost more to produce.

Powered axles- your boss believes two-wheel drive cars cost less to produce.

Your boss also wants to know if having a higher MPG increase the cost of producing the car.

Finally, your boss wants to know if better fuel economy affects the profit they make on a car. The boss does not have any good information on what affects per-car-profit and is leaving it up to you to figure that out.

Assignment:

You must decide what analyses to run to answer the boss’s questions. Remember the boss is not trained in statistical analysis so you will need to explain what you are doing and why chose to do it that way. This is does not just mean method, but also how you recoded any variables or anything else you have done (assume the boss is an idiot and you must carefully explain your work). You must also report what you find. The boss wants a report that is typed, double-spaced, with one inch margins, in 12 point Times New Roman font. The report can be no more than 5 typed pages. The boss also wants your analysis attached as tables (one analysis per page). These do not count against the page limit.

(Data provided as attachment)

Codebook:

Car number: identifier

Number of cylinders: number of cylinders the car has

Country: 1=American, 2=Japanese, 3= German, 4=Italian, 5=other

Milespergallon: the car’s MPG

Weightinlbs: car’s weight

Price: the car’s price

Transmission: 1=manual, 2=automatic, 3=ATMC, 4=DCT

Cost: how much a car cost to make

Profit: the amount of profit per car

Poweredaxels: 1=frontwheel drive, 2=rearwheel drive, 3= four wheel drive, 4= all-wheel drive

XYZ car: 1=yes, 0=no