stastic Exel

PURCHASING POWER PARITY

1. In many economic theories, purchasing power parity is a technique used to determine the relative value of different currencies. While economists often consider the relative value of complex collections of goods,

another method of estimating purchasing power parity is to compare the price of a single common item in

various countries.

Data provided in the accompanying spreadsheet indicates the Big Mac Index for selected countries in three geographic regions (North and South America, Asia, and Europe). The Big Mac Index compares the price

of a Big Mac hamburger at McDonald’s restaurants in different countries, indicating the percentage above or below the average price of a Big Mac in the United States – if a country’s Big Mac Index is 17, for example, then the price of a Big Mac in that country would be 17% more than the price in the United States.

(If you are interested in more details, view the full article here.)

One common way of visually representing data which is broken into distinct groups is with a bar graph.

a) Create a bar graph which displays the Big Mac Index for the given countries in North and South America.

Note: Many spreadsheet programs (including Microsoft Excel), refer to a bar graph as a “Column Chart.”

b) Create a bar graph which displays the Big Mac Index for the given countries in Asia.

c) Create a bar graph which displays the Big Mac Index for the given countries in Europe.

The accompanying spreadsheet also indicates the average price of a 16GB iPad (with WiFi and Retina

display, reported in September, 2014) in selected countries. At the time the data was compiled, the price of

an equivalent iPad in the United States was $499.

d) For each of the given countries, calculate an “iPad Index” value – that is, calculate the percentage by which each country’s iPad price differs from the United States’ price of $499, displaying the iPad Index value in a column to the right of the existing data.

e) Create a multi-bar graph which displays both the Big Mac Index and the iPad Index for the given countries in North and South America.

f) Create a multi-bar graph which displays both the Big Mac Index and the iPad Index for the given countries in Asia.

g) Create a multi-bar graph which displays both the Big Mac Index and the iPad Index for the given countries in Europe.

h) Identify at least three countries in which a Big Mac is cheaper than in the United States, but in which an iPad is more expensive.

NBA SCORING LEADERS

2. Data provided in the accompanying spreadsheet indicates the Scoring Champion for each season in the history of the National Basketball Association (NBA). The title of Scoring Champion is awarded to the

player with the highest points per game average over the course of a sufficient number of games.

One common way of visually representing the change of some quantity over time is with a line graph. a) Create a line graph displaying the Points per game value for the NBA Scoring Champion for the given

seasons.

b) Based on line graph you just created, what appears to be the typical Points per game value for a Scoring Champion? Has a player ever averaged significantly higher than this typical value? Which player(s)? In

which season(s)?

Note that the total points scored by each player consist of points scored in distinct categories: points from

Free Throws, 2-pointers, and 3-pointers. (The 3-point shot was introduced in the 1979-80 season, so players prior to that season could not score points in that category.)

One common way of visually representing data which changes over time and in which several categories

make up an overall whole is with a stackplot.

c) Create a stackplot displaying how each Scoring Champion’s total points are comprised of Free Throws, 2-pointers, and 3-pointers.

Note: Many spreadsheet programs (including Microsoft Excel), refer to a stackplot as a “Stacked Area” chart.

ALTERNATIVE FUELS 3. The number and variety of alternative fueling stations has grown significantly in recent years in the United

States. Data provided in the accompanying spreadsheet was gathered by the United States Department of

Energy and shows fueling station counts for the top 10 states.

a) Create a column which calculates the total number of fueling stations in each state under consideration.

b) Create a pie chart which displays the data for the total number of fueling stations.

c) Create a bar graph which displays the data for the total number of fueling stations.

d) Discuss which of the two visual representations you just created is a more effective representation of this

data set.

e) Create a row which calculates the total number of stations within each category.

f) Create a pie chart which displays the data for the total number of stations within each category.

g) Create a bar graph which displays the data for the total number of stations within each category.

h) Discuss which of the two visual representations you just created is a more effective representation of this

data set.

In addition to counts of fueling stations which are available within each state, the accompanying

spreadsheet also provides the population of the states under consideration.

i) Create a column which calculates the population of each state divided by the total number of fueling

stations available within that state.

Note that this value indicates the number of people “per fueling station.”

j) Which three states are the “best” at providing alternative fueling stations?

STARBUCKS 4. Data in the accompanying spreadsheet tracks the number of Starbucks stores in the U.S., as well as the total

count worldwide, between the years 1999 and 2012. Additionally, the spreadsheet provides the total annual

revenue for Starbucks from 2001 to 2012.

A stackplot (also called an area graph) displays proportional relationships between quantities over time. a) Create a stackplot which represents the relationship between Starbucks’ Total Number of Stores and

Total U.S. Stores over the period from 1999 to 2012.

b) Based on the stackplot you just created, in which year does it appear that U.S. stores comprised the

smallest percentage of total Starbucks stores worldwide?

c) Create a column which calculates the percentage of total Starbucks stores which are located in the U.S.

in each of the years under consideration.

d) Create a line graph displaying the percentage data you just created.

e) Create a line graph displaying the Total Annual Revenue data.

f) Based on the line graphs you just created, discuss trends you see regarding the percentage of total

Starbucks stores which are located within the U.S. and the company’s Total Annual Revenue.