ISP 120 Midterm Practice

WARNING: It would be unwise to use this practice as the only studying you do for the midterm. There will additional material on the midterm that is not represented here. Make sure you look over all of the activities and homework.

Percentages

1. Items at a store were marked down 20%.  The store then advertises that they will take an additional 20% off at the register when you make your purchase.  By how many percent are prices reduced under this sale?  The answer is not 40%.

The price first dropped from 100% to 80%.  Then it drops by 20% of 80% or 0.2*0.8=0.16, i.e., 16%.  So in the end it is 80%-16%=64% of the original price, a 36% drop.

2.  Chlorofluorocarbons (CFC's) are chemicals that harm the ozone layer that protects the earth from ultraviolet radiation from the sun; they also cause global warming.  CFC's were used in refrigerators (freon is a CFC) and in the making of other chemicals.  In 1950, world CFC production was 42 thousand tons.  In 1988 their production peaked at 1260 thousand tons.  The most recent figures available (1996) put production at 141 thousand tons.

a. By how many percent did production increase from 1950 to 1988?

Production increased by 2900%.  The calculation is (1260-42)/42=29.

b. By what factor was the 1996 production larger than the production in 1950?

Production in 1996 was larger than the production in 1950 by a factor of 3.4.  The calculation is 141/42.

c. By how many percent was 1996 production less than the production in 1988?

Production in 1996 was 89% less than in 1988. The calculation is (1260-141)/1260.

Sorting Data using Excel; Estimation  

3.  Open the file mileage.xls which contains the city and highway mileage for 1996 model cars and four-wheel drive vehicles.  Using Excel's sort function, determine: 

a. The mid-size car model that gets the best city mileage Dodge Stratus

b. The mid-size car model that gets the worst highway mileage Jaguar XJ12

c. The four wheel drive car model that gets the best city mileage GeoTracker

4.  Estimate how much you'd spend on regular unleaded gas making the 1500 mile road trip from Chicago to the Grand Canyon in a 1996 Toyota Landcruiser.  What would you spend on gas if you drove a Jeep Cherokee instead? 

The Toyota Landcruiser get 13 miles per gallon in the city and 15 miles per gallon on the highway. A trip to the Grand Canyon would be most be mostly highway driving, so for the purposes of estimating, we can assume will get 15 mpg.   That means we would use 1500/15=100 gallons of gasoline.  At $1.50 per gallon, it would come to about $150.  A Jeep Cherokee gets 20 mpg, so we would use 1500/20=75 gallons.  That would come to $112.50, or about $110.

Percentages, Percent Change, Rates & Ratios   

5.  Open the file AccidentalDeaths.xls which lists the number of different types of accidental deaths reported in 1980 and in 1994 for the total population of the United States (U.S. Census Bureau data). 

a.  What type of accident accounted for the highest number of accidents in both 1980 and 1994?

Motor vehicle accidents accounted for the highest number of accidents in both 1980 and 1994.

b.  In column D, calculate the percent change for each accident category between 1980 and 1994. Paste the resulting table in your Word document.

A correct formula for cell D7 is =(C7-B7)/B7.  Format the cell by clicking on the percent format button.

c.  What type of accidental deaths decreased the most during this 14 year time span?  What category had the largest increase?  What is the percentage change in the number of deaths due to motor vehicle accidents during this time?  How does this compare with other types of accidental deaths (meaning, is it higher or lower than most others, or about average)?

Water transport accidents decreased the most (-49.4%) while accidental poisonings increased the most (214.1%). The number of deaths due to motor vehicle accidents decreased by 20% and is about average.

d.  What was the total number of accidental deaths reported in 1980?  in 1994?  What percentage of the total accidental deaths are due to motor vehicle accidents in 1980?  In 1994?  (Hint:  use the sum function to total the 1980 accidents and the 1990 accidents in the last cell of each column.  Then divide motor vehicle accidents by those totals in each year)

The total number of accidental deaths reported in 1980 was 90,677.  In 1994, there were 78,607.  The percentage of accidental deaths due to motor vehicle accidents in 1980 was 58.6%.  In 1994, it was 54.1%.

e.  Given that the total population of the US in 1980 was 227,726,000, what was the rate of total accidental deaths per 100,000 people in 1980?  What would that accidental rate per 100,000 people be in 1994, when the total US population was 260,660,000? 

The rate in 1980 was 90,677/227726000 * 100000= 39.8 per 100,000.   In 1994, it was 78,697/260660000*100000 = 30.2 per 100,000.  Overall the rate of accidental deaths in the US decreased significantly.

Consumer Price Index; Graphing  

For these problems, you'll need to use the file CPI.xls.  You might also want to consult the CPI tutorial.

6.  In 1965, the new Ford Mustang convertible had a sticker price of $5,000.   What would that sticker price be in constant 2000 dollars?  How does the price compare the comparable cars sports cars today?

In constant 2000 dollars it would be $5000*172.3/31.5=$27349.21. Comparable cars today are less expensive.

7.  Open the file CarSales.xls, which lists the total revenue in millions of dollars for General Motors and Ford for 1987 to 1996. 

a.    Which company seems to have done better financially during this time period?  Briefly state your position in a paragraph and justify it using the data in the table. 

Both companies increased by about 12 billion, but GM started from a smaller base, so it had a larger percentage increase over the time period.

b.  Make an XY scatterplot of Ford and GM revenue from 1987 to 1996.  In a well written paragraph, describe the graph. 

GM's revenue started at $13.1 billion in 1987, the lowest point in the time period.  It rose steadily to a local maximum of 25 billion in 1992. It then dropped back down to 20.3 billion in 1993, but then rose to absolute maximum of 25.5 billion.  Ford's graph was similar.  Ford started the period with revenues of 17.3 billion. Revenue dropped to 11.4 billion in 1988, the low for the period.   Revenues then rose for two years, achieving a local maximum of 25.7 billion in 1990. Revenue then fell slightly for the next three years, achieving a local minimum in 1993 of 21.7 billion. Finally, Ford's revenues increased from 1994 to 1996 achieving the absolute maximum for the graph in 1996, 29.9 billion.

Comparing the two graphs, we see that Ford tended to have higher revenues than GM in this period. Only in 1988 were Ford revenues significantly less than GM's. GM seemed to "catch up" with Ford in 1992, but Ford then pulled ahead again from 1994 to 1996.

c.  Convert the series of Ford's revenue to constant 2000 dollars, using the CPI table (Hint: from the CPI table, copy the CPI from years 1987 to 1996 and paste this into the column next to the Ford data.  Then use a formula with cell references to convert the Ford data in the first year; drag this formula down the column.  Remember to use 2000 CPI in your conversion ratio, not 1996 CPI).  

d.  Make an XY graph of the resulting adjusted Ford revenue dollars.   Briefly describe the graph.  Does it differ substantially from the graph in 7a?  What can you say about Ford's revenue in this time period? 

The graph looks somewhat different.  Ford revenue dipped in 1988, rose to its maximum in 1990 (33.9 2000$), and then decreased to a local minimum in 1993. It recovered in 1994-1996, but it did not reach the maximum attained in 1990. In the first graph, Ford's revenue seems to increase quite a bit overall (from about 17 to 30), but the second graph shows that taking into account inflation, it also rose (from about 26 to 33) but not as dramatically.