LEARN NC

K–12 teaching and learning · from the UNC School of Education

Learn more

Related pages

  • Introduction to spreadsheets: In this two-week unit the students will use a teacher made spreadsheet to learn how calculations work. Then student teams will work together to create their own "store" order form on a spreadsheet. The final part of the lesson is a simulated game show where students "shop" at each others stores, trying to spend an exact amount of money without going over, getting closer to their required amount than any other team.
  • Interdisciplinary Integrated Unit on DNA/Genetics Part B: Math: The second lesson of an interdisciplinary integrated unit on DNA and genetics, focusing on math. The other lessons in the unit focus on science and language arts.
  • Collecting and organizing data: In this lesson students will learn to collect data and organize the data into a chart using computer technology.

Related topics

Help

Please read our disclaimer for lesson plans.

Legal

The text of this page is copyright ©2008. See terms of use. Images and other media may be licensed separately; see captions for more information and read the fine print.

Learning outcomes

Modify and use spreadsheets to solve problems.

Teacher planning

Time required for lesson

30 minutes

Technology resources

Pre-activities

Students should know how to format a spreadsheet.

Activities

Use Microsoft Excel to complete the exercise below. Carefully follow all directions and proofread your work.

Teachers note: Point Scale: -5 per error. These instructions may be printed from the Carson Instructions attachment.

Scenario:

You are to prepare a financial analysis for 1998 and 1999 Accounts Department of Carson Interiors. You already have the data for 1998; Tiawana, an 8th grade student from a local middle school, will provide you with the 1999 figures that you need.

  1. Start Excel.
  2. Open the file called Carson.xls.
  3. Enter your first name in cell A2, your last name in cell B2, grade level in cell C2, and homeroom teacher’s name in cell D2.
  4. Save the workbook as “Carson” along with your name on it in the student folder on your hard drive.
  5. Bold the name Carson Interiors.
  6. Change column widths as follows:
    • Column A: 18 (Highlight any cell in column A, click on format, column, and width. Type 18, and click OK.)
    • Columns B, C, D, E, G, and H: 11 (Highlight any cell in columns B–H, click on format, column, and width. Type 11, and click OK.)
    • Column F: 5 (Highlight any cell in column F, click on format, column, and width. Type 5, and click OK.)
  7. Format the 1st three rows of numeric data including columns G and H (Operating Revenue-Net Income) for commas with no decimal places. (Highlight the cells named above, click on format, cells, number, use 1000 separator, decimal equals 0, and OK.)
  8. Format the Earnings Per Share values (B13:H13) for two decimal places. (Highlight the cells then click on format, cells, number, decimal equals 2.)
  9. Use the fill handle to enter the next two quarterly labels. Remember to select both quarter labels first. (Highlight cells B8:E8, and click on edit, fill, series, autofill, and OK.)
  10. Skip one column, and enter the following new column headings: TOTALS, AVERAGES on row 8.
  11. Enter the formulas shown in the illustration for totals and averages. Enter other missing data if needed.
  12. Center and bold all column headings.
  13. Use the Merge and Center button on the toolbar to center the title lines over columns A-H. (Highlight cells A4:H4, click on the merge, and center tool on the tool bar. Highlight cells A5:H5, click on the merge and center tool on the tool bar. Highlight cells A6:H6, click on the merge, and center tool on the tool bar.)
  14. Copy your 1998 report, and paste it four rows below. (Highlight cells A6:H13; click on the copy tool on the tool bar, select cell A17, and click on the paste tool on the tool bar.)
  15. Edit the copy that you pasted below to show the changes displayed in Tiawana’ 1999 report.
  16. Create a header with the words Financial Information appearing on the left side and the date appearing on the right. (Click on view, header and footer, and custom header to key in the information. Click on OK.)
  17. Create a footer with the page number in the center.
  18. Preview the report.
  19. Change the Page Setup to print Landscape. (Click on file, page setup, page tab, and landscape.)
  20. Set the print area to print the worksheet data. (Click on file, page setup, page tab, fit to box, type 1 in the first box, and delete the number from the second box. Click OK.)
  21. Print the workbook; save the changes.

  22. Close the workbook and exit Excel.

March June TOTALS AVERAGES

  • Operating Revenues 1926722 1325238 1257613 980148 =SUM(B10:E10) =AVERAGE(B10:E10)
  • Operating Income 263903 215326 257591 205328 =SUM(B11:E11) =AVERAGE(B11:E11)
  • Net Income 147850 96478 154156 131419 =SUM(B12:E12) =AVERAGE(B12:E12)
  • Earnings Per Share 0.8 0.52 0.83 0.7 =SUM(B13:E13) =AVERAGE(B13:E13)

QUARTERLY PERIODS ENDED 1999

March June September December TOTALS AVERAGES

  • Operating Revenues 3449877 4598112 3664767 2445067 =SUM(Operating Revenues)
  • Operating Income 454900 678345 855098 171564 =SUM(Operating Income)
  • Net Income 645100 888121 202498 349111 =SUM(Net Income)
  • Earnings Per Share 0.79 0.88 0.55 0.76 =SUM(Earnings Per Share)
  • Tiawana’s 1999 figures

Assessment

Have students review the files of their peers to compare the printed information to the set of instructions to make sure the assignment is complete. Students should be provided a copy of the attached answer sheet. They will be required to deduct 5 points for each item that does not match the answer sheet then a classmate will double check the paper to see if all errors were detected. Sometimes students recognize what they did wrong during the process of evaluating the work of a peer.

Supplemental information

Attachments:

Comments

This lesson was developed to help students understand how to use formulas to solve math problems.

North Carolina Curriculum Alignment

Computer Technology Skills (2005)

Grade 6

  • Goal 2: The learner will demonstrate knowledge and skills in the use of computer and other technologies.
    • Objective 2.03: Use spreadsheet terms/concepts and functions to calculate, represent, and explain content area findings. Strand - Spreadsheet
  • Goal 3: The learner will use a variety of technologies to access, analyze, interpret, synthesize, apply, and communicate information.
    • Objective 3.05: Enter/edit data and use spreadsheet features and functions to project outcomes and test simple "what if..." statements in content assignments. Strand - Spreadsheet

Grade 7

  • Goal 1: The learner will understand important issues of a technology-based society and will exhibit ethical behavior in the use of computer and other technologies.
    • Objective 1.09: Demonstrate knowledge that spreadsheets are used to process information in a variety of settings (e.g., schools, government, business, industry, mathematics, science). Strand - Spreadsheet
    • Objective 1.10: Use spreadsheet and graphing terms/concepts to present and explain content area assignments. Strand - Spreadsheet

Grade 8

  • Goal 1: The learner will understand important issues of a technology-based society and will exhibit ethical behavior in the use of computer and other technologies.
    • Objective 1.09: Recognize, discuss, and investigate how spreadsheets are used in a variety of settings (e.g., schools, government, business, industry, transportation, communications). Strand - Spreadsheet
  • Goal 2: The learner will demonstrate knowledge and skills in the use of computer and other technologies.
    • Objective 2.03: Select and use spreadsheet formulas and functions to solve problems in content areas. Strand - Spreadsheet
    • Objective 2.04: Use spreadsheet features/functions to calculate and present findings for content area assignments. Strand - Spreadsheet
  • Goal 3: Select and use a variety of technology tools to collect, analyze, and present information. Strand - Societal/Ethical Issues
    • Objective 3.04: Create/modify spreadsheets to analyze and interpret information, test simple "what if..." statements, solve problems, and make decisions in content areas. Strand - Spreadsheet