Project Assignment
As a new marketing analyst for Ford Motor Company, your first assignment is to analyze sales data in the carbuyer data.xls file. Ford has given you
information on the cars their customers have purchased, how much they spent on options and how much profit they made from each sale. Using the
Internet, your trusty assistant has gathered demographic information on four customer attributes. These are (1) the customer’s estimated income (in
tens of thousands of dollars — e.g., 2 means $20,000), (2) whether the customer owns a house, (3) the number of cars the customers own and (4) the
age of the customer.
Note, you will have to clean the data before doing the analysis. The information on the data cleaning part of the assignment can be found at the end
of the assignment.
Specifically, you are to report on:
1. What is the profile of buyers for each of their car models?
For the next two questions, ignore the car model (i.e., do not perform a separate analysis for each car model.)
2. Which of the four demographic attributes (listed above) seem to have a relationship with the amount an individual customer spends on
options (and what is the nature of any relationships that you uncover — whether it is related to high or low amounts spent on options)?
3. Which of the four demographic characteristics seem to have a relationship with the profit made on an individual sale (and what is the nature
of any relationships that you uncover — whether it is related to high or low profit).
Be sure to analyze items 2 and 3 above on a per person basis. For example, if you use a pivot table, you should look at average profit (expected
profit per person) rather than the sum of profit (the sum of the profit for everyone within a given category).
Your report must be in the form of a Powerpoint file with a maximum of 20 slides. You will have to turn in an Excel file which you use to complete
the first step of the data cleaning. That is the only Excel file that I want you to turn in and it must only include step 1 for the data cleaning. Do not
include any analysis or graphs in this file.
Note, the following:
Ford is not at all interested in how some of the demographic information relates to other demographic information. For example, they do not
care about the average income of people who own houses.
All analysis should be done in Excel (Statpro add-in is acceptable). Do not use any other statistical package.
Be sure to address each of the three questions in order. Answer each question separately. Clearly delineate the break between each of the
questions. However, make sure that your presentation looks like it was all put together by the same team. It should not look like one person
did question 1, somebody else did question 2 and a third person did question 3 or points will be deducted.
All assertions must be supported by a table or graph that you include in your powerpoint presentation. Make sure the axes of the graphs
have labels. Give a title to each graph or table (this could be on the powerpoint slide rather than the graph or table you create in Excel)
Do not include tables or graphs that totally duplicate the information in other tables or graphs.
Be sure to include tables that support all conclusions reached in the report, but do not include tables that do not address the questions
with which you have been tasked.
After given the go-ahead by your instructor, create a group on Blackboard. Turn in one report per group via Blackboard.
Be aware that StatPro will sometimes overwrite its previous output. If you use Stapro, be sure to copy and paste (I recommend using the
snipping tool) each graph or chart made with StatPro before creating a new graph or chart.
Data Cleaning
The data has been given to you in a form that is less than ideal. You are to convert the data into an Excel list (one that you could use in constructing
a pivot table.)
1) On the following page, an excerpt from the first 35 lines of the data is shown on the left. You need to write the equations needed to make the
data look like the data on the right. Use an equation approach like to do this (as in the “database file example.xls” file that we covered as in
Excel tip.) Then you will need to get rid of the rows you do not want by filtering and copying. Do not perform the cleaning by just cutting
and pasting or by dragging values (sort of like we did in the “data cleaning by sorting.xls” file) to do this. You will need to turn in the file in
which you did this. Make sure that the process you followed is similar to that found in the “database file example.xls” file.
2) There are a couple more things that you might want to do before analyzing the data.
Remember to turn in the file that you used to complete step 1 above. Do not worry about turning in the file for step 2.
