Impact of Education and Earnings Power

Using the census data of the mean income of workers by education attainment, students will use the data to create a chart and compare information. Students will use the data for answering particular questions.

Math SOL Objective:

Math 7:16: The student will create and solve problems involving the measures of central tendency (mean, median, mode) and the range of a set of data.

Math 7:17: The student, given a problem situation, will collect, analyze, display, and interpret data, using a variety of graphical methods

Math 7:19: The student will display data using frequency distribution, line plots, scattergrams, line and bar graphs.

Computer/Technology SOL Objective:

C/T 6-8.6: The student will use technology to locate, evaluate and collect information from a variety of sources (internet, spreadsheet).

Time needed: two - three 40 minute class periods

Materials: computer for each partner grouping (minimum)

  1. Students will be assigned partners and a particular ethnic group to investigate (White, Black, Non-Hispanic, Hispanic)
  2. Open an Excel document (called workbook)
  3. Setting the page parameters
  4. From the Menu Bar – click File – Page Setup – Margins tab
  5. Enter 1.5 margin for the top and 0.75 margin for the left and right margin
  6. Place a check in the Horizontal box.
  7. Click OK
  8. Beginning with cell A1 and moving across the row, enter the following headings in each cell.
  9. A1 Year
  10. B1 Total Pop. Wkg age 18+
  11. C1 Less HS diploma
  12. D1 HS diploma
  13. E1 A.A.
  14. F1 B.S.
  15. G1 M.S.+

Formatting Headers

  1. Select the Row 1 Header (click the Row header 1 to highlight row)
  2. From the Menu Bar – select Format – Cell – Alignment tab.
  3. In the Text Alignment – Horizontal – select “Center” from the drop down arrow
  4. In the Text Alignment – Vertical – select “Center from the drop down arrow.
  5. In the Text Control – place a check in front of Wrap text.
  6. In the Orientation – select a 90 degree slant.
  7. Click the Font tab – under Font Style select Bold
  8. Click OK
  9. Adjust the row height, if necessary - Place cursor on the Row header line – click and drag down.

Formatting the data columns

  1. In column A, enter the years starting with 2003 through 1996. ***IMPORTANT: before each year place an apostrophe (i.e. ‘2003) This is important in creating the chart.
  2. Highlight Column Header B- From the MenuBar select Format – Cell – Number tab
  3. Under Category, select Number
  4. Highlight the Column Headers C – G
  5. From the Menu Bar select Format – Cells – Number tab
  6. Under Category, select Currency.
  7. In the Decimal Places enter “0” (or use the drop down arrow)
  8. Click OK
  9. Save to the network folder

Adding gridlines to particular cells

  1. Highlight the cells A1 to G1down toG9
  2. From the Formatting Bar click. the drop down arrow for the Borders and select All borders
  3. Click anywhere on the spreadsheet to remove highlighting.

Insert a Title for the spreadsheet using Headers

  1. From the Menu Bar – click View – Header and Footer
  2. Click the Header/Footer tab – Custom Header
  3. Place the cursor in the Center Section and enter the following text:
  4. Impact of Education to Earnings Power, click Enter key
  5. Enter your particular ethnic group (i.e. Hispanic, working males and females), click Enter key
  6. Enter the names of both partners (i.e. Karen Schenck and Marcia Salansky)
  7. Click OK, click OK
  8. To see the effect click File – Print Preview then Close from the upper tool bar.

Classroom Assignment

For this project, you will be working in partners. Both partners should have a laptop. Partner A will find and recite the required data from the webpage while the Partner B will enter the data into the spreadsheet.

Each group will be assigned an Ethnic group to research. The data recorded must be that of both sexes of a particular ethnic group.

  1. After setting up your table, Partner A will access the following website:
  1. From the PDF Menu Bar – increase the zoom to 100% (using the drop down arrow select 100%)
  2. Recite the mean earners for each level of education and for each year.
  1. Partner B will record the mean earnings into the spreadsheet as recited by Partner A.

Creating the chart

  1. Highlight the data from A2down toA9
  2. Holding down the Ctrl button highlight cells C2 – G2down toG9
  3. From the Menu Bar click Insert – Chart (the Chart wizard opens)
  4. In the Chart Type select Line and in the chart sub-type select the first box in the second row.
  5. To preview the chart appearance click the Press and Hold to View Sample button.
  6. Click Next
  7. Click the Series tab
  8. In the Series window highlight Series 1
  9. In the Name window enter less HS diploma
  10. Highlight Series 2 and in the Name window enter HS diploma
  11. Highlight Series 3 and in the Name window enter A.A.
  12. Series 4 – B.S.
  13. Series 5 – M.S.+, thenClick Next, select the Title tab
  14. In the Chart Title enter Impact of Education on Earnings Power
  15. In the X axis enter Years, in the Y axis enter Earnings
  16. Select the Gridline tab, place a check next to Majorgridlines for X axis
  17. Select the Legend tab, click the Bottom radio button, click Next
  18. Select As object In radio button, click Finish
  19. Resize the chart from the corner handlebars (click and drag in or out)
  20. Reposition the chart by placing cursor in the center of the chart and moving it with a click and drag.
  21. From the Menu Bar select File – Print Preview
  22. Check to make sure that the data and chart fit on one page. If not, resize the chart as necessary.

Editing the finished chart

  1. Double click on the year 2003, Select the Font tab, set the font size to 8, click OK
  2. Double click on $20,000, set the font size to 8, click OK
  3. Double click on HS diploma, set the font size to 8, click OK
  4. Double click on any line on the chart
  5. In the Color drop down arrow, select a different color
  6. In the Weight drop down arrow, select a different thickness
  7. In the Style drop down arrow, select a different graphic
  8. Repeat these steps for any of the lines in need of change.
  9. Experiment with by double clicking any of the individual elements.

Save to your network folder. Save OFTEN!!!!!

Analyzing the Data Answer the following questions using the chart you just created. Compare the charts for other ethnic groups.

  1. What is your first impression of the graph you made? Were there any surprises for you or did the graph come out as you expected?
  2. What information does this graph tell you?
  3. Which group has had the greatest increase in income?
  4. Why do you think this has happened?
  5. Which group has had the least increase in income?
  6. Why do you think this has happened?

Education Impact on Earnings PowerMarcia Salansky, ITRT

Mean Earnings Across Ethnic DifferencesKaren Schenck and Wayne Brizzi, 7 Math