Excel Homework Problems
1. In 1954 Roger Bannister set a new world’s record, running the mile in less than four minutes. Many experts believed that such a feat was impossible. The record time had, however, been decreasing for many years. The table below includes record times from 1875 through 1775.
Year Runner Time Year Runner Time
1875 Walter Slade, Britain 4:24.5 1943 Andersson 4:02.6
1880 Walter George, Britain 4:23.2 1944 Andersson 4:01.6
1882 George 4:21.4 1945 Haegg 4:01.4
1882 George 4:19.4 1954 Roger Bannister, Britain 3:59.4
1884 George 4:18.4 1954 John Landry, Australia 3:58.0
1894 Fred Bacon, Scotland 4:18.2 1957 Derek Ibbotson, Britain 3:57.2
1895 Bacon 4:17.0 1958 Herb Elliott, Australia 3:54.5
1911 Thomas Connett, U.S. 4:15.6 1962 Peter Snell, New Zealand 3:54.4
1911 John Paul Jones, U.S. 4:15.4 1964 Snell 3:54.1
1913 Jones 4:14.6 1965 Michrel Jazy, France 3:53.6
1915 Norman Tabor, U.S. 4:12.6 1966 Jim Ryan, U.S. 3:51.3
1923 Paavo Nurmi, Finland 4:10.4 1967 Ryan 3:51.1
1931 Jules Ladoumegue, Fr. 4:09.2 1975 Filbert Bayi, Tanzania 3:51.0
1933 Jack Lovelock, N. Z. 4:07.6 1975 John Walker, N. Z. 3:49.4
1934 Glenn Cunningham, U. S. 4:06.8
1937 Sydney Wooderson, Brit. 4:06.4
1942 Gunder Haegg, Sweden 4:06.2
1942 Arne Andersson, Sweden 4:06.2
1942 Haegg 4:04.6
Copy the data above and paste it into an Excel spreadsheet. Remember that you can move data in Excel by selecting it, pointing to the border of the selection, and dragging. Check the formatting to make certain Excel recognizes that the numbers are times.
Create a scatter chart with the year on the X-axis and the times on the Y-axis. Create a scatter plot and add a linear trendline to the data. Does the linear trendline fit the data reasonably well?
Using the linear trendline, estimate the date at which the 3:40 time would be expected to be broken. To do this you may need to change the maximum values for the axes. You will also need to draw a line that lies on top of (i.e. extends) the trendline. Estimate the probably best time today. Search the Internet to find record times since 1975. When was the 3:40 mile broken? What is the most recent record time?
How well does the trendline based on the data through 1975 predict the most recent record time. HINT – If you draw a straight line extending the linear trendline and then add the most recent record you can visually see whether the record departs significantly from the trendline.
2. In time of war it is very important to know whether one is winning or losing. This problem deals with determining how many German submarines were sunk by U.S. ships during a 16 month period of World War II. (The data is from Probability with Statistical Applications, by Mosteller, Rourke, and Thomas, Addison-Wesley, 1970.)
Month Actual number sunk Number reported sunk
(by German Reports) (by U.S. Navy Reports)
1 3 3
2 2 2
3 6 4
4 3 2
5 4 5
6 3 5
7 11 9
8 9 12
9 10 8
10 16 13
11 13 14
12 5 3
13 6 4
14 19 13
15 15 10
16 15 16
The question we wish to answer is: How accurate were the U.S. Navy reports? Begin by creating a scatter plot of the data. Which data should be plotted on the Y-axis and which data should be plotted on the X-axis? HINT: The X-axis is used to represent the independent variable and the Y-axis is used to represent the dependent variable. If you are uncertain which is the independent variable, think of this problem as an experiment designed to determine how accurately the U.S. could estimate the number of submarines sunk. What value would you change in the experiment?
After you have created the scatter plot, add a linear trendline. How well does this trendline appear to fit the data?
Format the trendline to display the value of R2. R is called the correlation coefficient, and is a measure of how well the line fits the data. Discuss the significance of R2?
3. Use the data from problem 7 to create a vertical bar chart showing the submarines actually sunk (German Reports) in each month. Change the chart to a line chart. Which do you think best illustrates the success of the U.S. Navy, and why?
Change your chart back to the vertical bar type and add the U.S. Navy Report to the chart as a second series. You’ll need to display a legend with appropriate titles for this chart to be understandable. How effective do you think this format is for illustrating the success of the U.S. Navy?
4. The population of Manhattan Island from 1790 through 1970 is given in the table below.
Year Population Year Population Year Population
1790 33,131 1860 813,669 1930 1,867,312
1800 60,515 1870 942,292 1940 1,889,924
1810 96,373 1880 1,164,673 1950 1,960,101
1820 123,706 1890 1,441,216 1960 1,698,281
1830 202,589 1900 1,850,093 1970 1,539,233
1840 312,710 1910 2,331,542 1980
1850 515,547 1920 2,284,103 1990
2000
Create a spreadsheet using these data. Create a line chart using only data from 1790 to 1910. Add a trendline to this chart and experiment to find the type of trendline that fits the data best. Based on this data, what would you have estimated the 1940 population of Manhattan Island to be?
Create another chart using all the data. What types of charts would be appropriate for this data. Discuss the merits and shortcomings of each chart that might be used. Add a trendline to this chart. Experiment to find the trendline that fits the data best. Describe this line.
What historic/sociological events are responsible for the changes in the population of Manhattan Island after 1910?
Use the Internet to obtain census figures for Manhattan Island for 1980 through 2000 and add these to the table. Create a new line chart showing population changes from 1790 through 2000.
5. Ralph Feathergill started work as a salesman for the Wonder Widget Works in January of 2004. The table below shows his sales for each month since then.
Mo. 2004 2005 2006
Jan 735,986 1,528,887 1,519,758
Feb 683,073 1,278,420 1,274,287
Mar 843,726 1,394,835 1,384,986
Apr 962,341 1,404,076 1,397,325
May 998,243 1,907,539 1,896,538
Jun 1,264,324 2,504,386 2,599,487
Jul 1,489,543 2,778,648 2,774,093
Aug 1,687,465 2,600,456 2,594,083
Sept 1,634,515 2,125,498 2,146,583
Oct 1,429,749 1,863,587 1,793,863
Nov 1,208,936 1,569,036
Dec 986,357 1,279,865
Copy this data into an Excel spreadsheet. What type of chart would best illustrate the data?
To show the change in Ralph’s sales over time, use a line chart. It will be easier to do this is the data are arranged so that the sales figures are in a single column. This presentation makes it obvious that Wonder Widget Works sales are higher in the summer months and lower in the winter.
A different emphasis is obtained by looking at the average of the last two year’s sales month-by month. What type of chart would be best for this? Create an appropriate chart to illustrate this.
Yet aggregating the data will emphasize yet another perspective. The four months June-September are the high sales months for Wonder Widget Works. Sum the sales for the last 24 months grouping into three categories: June-Sept., Oct-Jan, and Feb-May. Use both a pie chart and a column chart to illustrate this data.
6. Create a spreadsheet for a budget. You should show monthly expenses in various categories including the following:
- Housing (house payment or rent)
- Utilities (you may need more than one category here)
- Food
- Car payment
- Gasoline
- Other car expenses.
- Add other categories as needed.
Income should also be shown including multiple categories if appropriate.
This spreadsheet should be laid out with a different month in each column and the various categories of expenses and income in rows. What sorts of calculations would be appropriate for use in the spreadsheet? What charts would be useful?