Massachusetts Institute of Technology Department of Urban Studies and Planning 11.204: Planning, Communication & Digital Media Fall 2004 Lab 3: Using Excel to Understand Population Change Assigned: Rec. 4 Due: Rec. 5 at 5PM I. Scenario This lab exercise is designed to help you learn more about MS Excel, while enhancing your understanding of Central Square in the City of Cambridge. So, you should not only keep an eye toward the functions packaged in Excel, but also think about the data you are manipulating as you move through the following exercise. We expect you to turn in something "professional." We don't want you to prepare extra analyses, but each chart should have all the necessary elements appearing properly. II. DirectionsA. MS Excel XP interface In this section, you will learn how to launch MS Excel and identify the Excel application window and its components. If you are familiar with MS Excel interface, you can skip this section; otherwise, enter here please. B. Data input & formatting For this exercise, you will use population data from the US Census Bureau for the City of Cambridge, Massachusetts. Data for 1970, 1980, 1990, and 2000 are provided to you; however, since the data were collected at four different points in time, they are stored in four different file formats. They are: text file (.txt), dBASE4 file (.dbf), MS Excel 95 (.xls), and MS Excel XP(.xls). 1. Open a new Excel workbook. [Help] 2. Load the four files into the new Excel workbook as four separate worksheets. Name the worksheets "Popu1970," "Popu1980," "Popu1990," and "Popu2000." [Help] Be aware that all rows in the four worksheets are in the same order. Do not change the order otherwise the data will not integrate properly. 3. In order to view and work with data from each of the four decades simultaneously, you need to combine the four worksheets into one (name it "Population"). [Help] 4. Save your file in your own CRL locker. Name your workbook "username_lab3.xls." For example, it would look like "H:\private\11.204 \lab3\johndoe_lab3.xls" if your username was "johndoe". [Help] Be sure to save it as an XLS file. (If your default save as type is ".txt" a window will pop up saying that a ".txt" file can not save a multi-sheet into one file.) C. Basic statistical analysesWith the population data at hand, now you can answer several basic questions. 1. In 1970, what was the total population of Cambridge, MA? In 1980? In 1990? In 2000? [Help] 2. In 1970, what was the average population for a census tract in Cambridge, MA? In 1980? In 1990? In 2000? [Help] 3. In 1990, which census tract in Cambridge had the greatest population? What was the population of this census tract? [Help] 4. From 1970 to 2000, which census tract in Cambridge has the sharpest population change? (Calculate change as a percentage using 1970 as the base year. In your answer only consider the positive values.)[Help]D. Making chartsIn order to visualize the change in population over these four decades, you will generate two charts. 1. Chart One: Make a chart that shows the population for each census tract in Cambridge (in 2000) sorted by population size. [Help] We require that 1) the X-axis shows the census tract number and the Y-axis indicates the population size. 2) you must add a title, the author's name, today's date and name the data resource "US Census 2000." 3) We leave it to you to select an appropriate chart type. Make sure that all information described above appear on the final version of your chart. Excel often hides some important elements without warning. If this occurs, change the font size and other elements. 2. Chart Two: Make a chart that shows how the population of the City of Cambridge (as a whole) has changed from 1970 to 2000. [Help] We require that 1) the X-axis shows the year (1970, 1980, 1990, and 2000) and the Y-axis shows the population size (for the City of Cambridge - not individual census tracts). 2) you must add a title, the author's name, today's date and name the data resources. 3) We leave it to you to select an appropriate chart type. E. Comparison with the State of Massachusetts From Chart Two you made in Part D, you can examine the trend of the population change for the City of Cambridge. We want you to make the same type of chart for the Commonwealth of Massachusetts and compare the trend for Cambridge with that of Massachusetts. In the population data file we’ve given you for the Commonwealth of Massachusetts (for years 1970, 1980, 1990 and 2000) the geographic unit is county rather than census tract. Since we are only considering the total population, you will need to sum the county population for each decade before you create the chart. 1. Chart Three: Make a chart that shows how the population for the Commonwealth of Massachusetts (as a whole) has changed from 1970 to 2000. We require that 1) the X-axis shows the year (1970, 1980, 1990, and 2000) and the Y-axis shows the population size (for the Commonwealth of Massachusetts - not individual counties). 2) you must add a title, the author's name, today's date and name the data resources. 3) We leave it to you to select an appropriate chart type. 2. Carefully examine the two charts (Chart Two and Chart Three), and describe in just a few sentences (approx. 100 words) the population trends in Cambridge and Massachusetts. Be sure to point out the obvious differences or similarities. III. Things to ponder z If you had to create a presentation using the data from this lab, what argument would you formulate? z How would you communicate your position? z Would you display all of the data to your audience or would you summarize the data by constructing a chart? z If you use a chart to communicate how population changes over time, which type is most effective? z Would your audience understand your argument more easily if presented with a pie or bar chart? z If you use a bar chart, should each bar represent a decade? z Should each bar be represented using a different color? z If so, what color palette(s) are most appropriate? Would you consider forecasting the population for 2010? z If so, what methods (mathematical and visual) would you employ? IV. Materials to submit This lab is due at 5:00pm on October 13th. Print your answers to the questions in the sections C, D, and E, and submit them to the "11.204 Box" . The colors (or shades of gray) that you select should provide ample contrast and your document should be