You are a new Global Bike intern assigned to the sales department. Global Bike Incorporated (GBI) is
multinational manufacturing company based in the US and Germany that sells bikes and accessories to
specialized dealers throughout the world. On your first day at work, you are tasked with analyzing U.S.
census data to understand the demographics for your home and university metro areas. The
demographic data will be used to help the sales team devise a targeted sales and marketing plan.
a. The assignment requires you to identify/compare the latest census.gov demographics data
for your university and home (e.g., current residence) metro areas to answer the questions
listed below. Use St. Louis, MO as your university metro area. If your current residence is
also St. Louis, MO, then pick another city (e.g., birth) as your home metro area for
comparison purposes. If you were not born in a metro area, choose the closest metro area to
your home address where you can find comparable data. Note: metro areas are delineated in
census.gov as Metropolitan Statistical Areas. If you are unable to find current demographic
data for your university and home Metropolitan Statistical Areas, it can be approximated
using county data of the principal city.
b. Access the U.S. Census data at this url: https://www.census.gov
c. Familiarize yourself with the Census website. Start by clicking “QuickFacts” on the home
page to explore data for some common questions. If you cannot find answers to the
questions from the “QuickFacts” data, click “Explore Data -> Explore Data Main” on the
home page and click “GO TO DATA.CENSUS.GOV” to use the advance search function.
d. For some questions, you will need to import a census.gov data table into Excel and do some
formatting and simple calculations.
e. Include screen captures of the Census/Excel data visualizations and tables to support your
answers. For each question include a short sentence that clearly answers the question, and
for question 9 write a short paragraph based on your insight from a review of the data in the
Question 1: What is the average travel (commute) time to work for individuals in your university
and home state metro areas? (It’s OK to use county data of the principal cities to approximate
the metro areas for this question.)
Question 2: What is the median and mean household income for individuals within your
university and home state metro areas during the past year?
Question 3: Create an Excel chart that compares the median and mean incomes for your
university and home state metro areas.
Question 4: Create maps that show the population density of your university and home state
metro areas. (It’s OK to use county data of the principal cities to approximate the metro areas for
Question 5: Has the population in your university and home state metro areas grown or shrunk
over the past 10 years? By how much (% change and raw numbers)?
Question 6: What percent of the population within your university and home state metro areas
is between the ages of 18 and 64? What is an estimated population for this age group?
Question 7: How many commuters (>16 years old) in your university and home state metro areas
currently ride bicycles or walk to work? How many drive alone?
Question 8: Create a dashboard by combining three or more maps/charts/tables that you
accessed on the census website and paste a screen shot here. Comment on how the dashboard
could be used by GBI to help develop a targeted sales and marketing plan.
Question 9: Comment and provide rationale on which metro area (your university or home) you
would recommend for a GBI targeted sales and marketing plan.