Excel Chapter 2 – Formulas and Functions:

Math Basics for Spreadsheet Use

Table of Contents

Lesson Plan...... 2

Solution for Ch 2 Homework (Hands-On Exercises in Chapter).... 4

Capstone Exercise for Ch 2 (End of Chapter Capstone Exercise)... 8

Solution for Capstone Exercise...... 9

Homework Handout for next class period (Ch 3)...... 12

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

Materials Needed

Description of Document / Name of Document / Location of Files
Solution to Homework Hands-on Exercises for
Excel Chapter 2 / chap2_ho1_payroll_solution.xlsx
chap2_ho2_payroll_solution.xlsx
chap2_ho3_gradebook_solution.xlsx
chap2_ho4_van_solution.xlsx / Exploring Excel 2007
Chapter 2 Folder on
Instructor Resource Disk
or

Capstone Exercise for
Excel Chapter 2 / ExplOffice2007_ExcelCh2CE.docx
Solution for
Capstone Exercise for Excel Chapter 2 / ExcelCh2CE_solution.xlsx
ExcelCh2CE_solution_formulas.xlsx
Homework Handout for Excel Chapter 3 / ExplOffice2007_ExcelCh3HW.docx
Answer Key for MyITLab Excel Chap 2 / AnswerKey_MyIT_Excel_Chap_2

Class Run-Down

  1. Have students turn in Homework assignments.
  2. Talk about chapter using discussion questions listed below.
  3. Demonstrate Excel 2007.
  4. Direct students to the MyITLab site to complete the following:
  5. Excel Chapter 2Pre-Test
  6. Study Plan
  7. Excel Chapter 2Post-Test
  8. Have students complete Capstone Exercise for Excel Chapter 2.
  9. Give students Homework Handout for next class period.

Learning Objectives

At the end of this lesson students should be able to:

  • Create and copy formulas.
  • Use relative and absolute cell addresses.
  • Use AutoSum.
  • Insert basic statistical functions.
  • Use date functions.
  • Use the IF function.
  • Use the VLOOKUP function.
  • Use the PMT function.
  • Use the FV function.

Discussion Questions

  • Three icons show on the formula bar when you start typing in a cell: a check mark, an X, and an fX. Can you explain what these are?
  • What is the difference between an absolute reference and a relative reference? When should you use an absolute reference?
  • Pointing can be used to enter cell references into formulas. What are the advantages of using this method rather than typing the cell location directly into the formula?
  • What date functions does Excel have available? Click on the fX button and look at the Date & Time category to help with your answer.

Additional Web Resources

From Microsoft Office Online for Excel 2007:

  • Overview of formulas
  • List of worksheet functions (by category)
  • VLOOKUP

Demonstrate how to:

  • Copy formulas.
  • Display formulas.
  • Use absolute versus relative cell referencing.
  • Use AutoSum, Date, IF, PMT, VLOOKUP, and FV functions.

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

chap2_ho1_payroll_solution.xlsx

SmithtownHospital
Radiology Department Payroll
Name / Hourly Wage / Regular Hours / Overtime Hours / Gross Pay / Withholding Tax / Soc Sec Tax / Net Pay
Dwyer / 8 / 40 / 8 / 416 / 116.48 / 31.824 / 267.696
Smith / 7.2 / 40 / 8 / 374.4 / 104.832 / 28.6416 / 240.9264
Brown / 8 / 40 / 10 / 440 / 123.2 / 33.66 / 283.14
Look / 9 / 35 / 0 / 315 / 88.2 / 24.0975 / 202.7025
Thomas / 9.2 / 40 / 0 / 368 / 103.04 / 28.152 / 236.808
Gonzolaz / 10 / 40 / 4 / 460 / 128.8 / 35.19 / 296.01
Synder / 14 / 40 / 8 / 728 / 203.84 / 55.692 / 468.468
Franklin / 6.35 / 20 / 0 / 127 / 35.56 / 9.7155 / 81.7245
Kennedy / 7.2 / 38 / 0 / 273.6 / 76.608 / 20.9304 / 176.0616
Willson / 10 / 40 / 5 / 475 / 133 / 36.3375 / 305.6625
Johnson / 6.35 / 15 / 0 / 95.25 / 26.67 / 7.286625 / 61.29338
Schmitz / 7.2 / 10 / 0 / 72 / 20.16 / 5.508 / 46.332
Totals
Assumptions / Summary
Overtime rate / 1.5 / Average Gross Pay
Withholding tax / 0.28 / Highest Gross Pay
Social Security/Medicare tax / 0.0765 / Lowest Gross Pay
Prepared by:
Notes:
1. Withholding tax is calculated on the taxable pay
2. Social Security tax is calculated on the employee's gross pay

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

chap2_ho2_payroll_solution.xlsx

SmithtownHospital
Radiology Department Payroll
Name / Hourly Wage / Regular Hours / Overtime Hours / Gross Pay / Withholding Tax / Soc Sec Tax / Net Pay
Dwyer / $8.00 / 40 / 8 / $416.00 / $116.48 / $31.82 / $267.70
Smith / $7.20 / 40 / 8 / $374.40 / $104.83 / $28.64 / $240.93
Brown / $8.00 / 40 / 10 / $440.00 / $123.20 / $33.66 / $283.14
Look / $9.00 / 35 / 0 / $315.00 / $88.20 / $24.10 / $202.70
Thomas / $9.20 / 40 / 0 / $368.00 / $103.04 / $28.15 / $236.81
Gonzolaz / $10.00 / 40 / 4 / $460.00 / $128.80 / $35.19 / $296.01
Synder / $14.00 / 40 / 8 / $728.00 / $203.84 / $55.69 / $468.47
Franklin / $6.35 / 20 / 0 / $127.00 / $35.56 / $9.72 / $81.72
Kennedy / $7.20 / 38 / 0 / $273.60 / $76.61 / $20.93 / $176.06
Willson / $10.00 / 40 / 5 / $475.00 / $133.00 / $36.34 / $305.66
Johnson / $6.35 / 15 / 0 / $95.25 / $26.67 / $7.29 / $61.29
Schmitz / $7.20 / 10 / 0 / $72.00 / $20.16 / $5.51 / $46.33
Totals / $4,144.25 / $1,160.39 / $317.04 / $2,666.82
Assumptions / Summary
Overtime rate / 1.5 / Average Gross Pay / $345.35
Withholding tax / 28.00% / Highest Gross Pay / $728.00
Social Security/Medicare tax / 7.65% / Lowest Gross Pay / $72.00
Prepared by: / Student Name / Today's Date
Notes:
1. Withholding tax is calculated on the taxable pay
2. Social Security tax is calculated on the employee's gross pay

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

chap2_ho3_gradebook_solution.xlsx

Today's Date / West Transylvania Athletics Grade Book - Final Semester Averages
Name / Test 1 / Test 2 / Test 3 / Test 4 / Test Average / Homework / Semester Average / Grade
Albert, Eddy / 80 / 71 / 70 / 84 / 76.3 / Poor / 76.3 / C
Thomas, Frank / 96 / 98 / 97 / 90 / 95.3 / OK / 98.3 / A
Jones, Theodore / 78 / 81 / 70 / 78 / 76.8 / OK / 79.8 / C
Fagan, Samuel / 65 / 65 / 65 / 60 / 63.8 / OK / 66.8 / D
Dicks, Starr / 92 / 95 / 79 / 80 / 86.5 / OK / 89.5 / B
Frampton, Judy / 90 / 90 / 90 / 70 / 85.0 / OK / 88.0 / B
Marie, Tina / 60 / 50 / 40 / 79 / 57.3 / OK / 60.3 / D
Sandy, Grace / 75 / 70 / 65 / 95 / 76.3 / OK / 79.3 / C
Dwyer, Helen / 90 / 90 / 80 / 90 / 87.5 / Poor / 87.5 / B
Severson, Jane / 82 / 78 / 62 / 77 / 74.8 / OK / 77.8 / C
Opolak, Alice / 92 / 88 / 65 / 78 / 80.8 / OK / 83.8 / B
Little, Franklin / 94 / 92 / 86 / 84 / 89.0 / OK / 92.0 / A
Wu, Lin / 92 / 78 / 65 / 82 / 79.3 / Poor / 79.3 / C
Henry, Thomas / 60 / 50 / 65 / 80 / 63.8 / Poor / 63.8 / D
Your Name / HW Bonus / 3 / Grading Criteria
0 / F
60 / D
70 / C
80 / B
90 / A

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

chap2_ho4_van_solution.xlsx

Today's Date / School for Exceptional Children
PMT Function - The Purchase of a School Van
Purchase Price / $26,000
Manufacturer's Rebate / $1,000
Down Payment / $3,000
Amount to Finance / $22,000
Interest Rate / 9%
Term (years) / 3
Monthly Payment / $699.59
Your Name Goes Here

1

Exploring Office 2007 Excel Chapter 2 Capstone Exercise

Student Name: ______Section: ______
Due Date: ______00 Points PossiblePoints Earned:______

Objectives

▪Use various functions to complete calculations for interest rate, down payment, monthly payment, and average selling price.

▪Copy and/or move cells within a worksheet; differentiate between relative, absolute, and mixed references.

▪Format a worksheet to include boldface, italics, shading, and borders; change the font and/or alignment of a selected entry.

▪Print a worksheet to show either the displayed values or the cell contents; use the Page Setup command to modify the appearance of the printed worksheet.

Preparation & Helpful Information

Read Chapter 2 – “Formulas and Functions: Math Basics for Spreadsheet Use” located in the Microsoft Office 2007 Professional book.

Check each step before continuing; it is easy to skip one when you hurry.

If you are not familiar with Excel, allow yourself extra time for the exercises.

Read this assignment sheet first before you complete any assignments. Many times there will be additional instructions not included in your book.

Instructions

Designate one disk for your Excel assignments, as these files take up a lot of space.

TASK 1: Read Excel Chapter 2, pages 000-000.

TASK 2: Complete Capstone Exercise: First National Bank New Loans on page 00.

TASK 3: Print the Spreadsheet.

TASK 4: Print the Formulas.

When you complete your assignment, do the following:

Turn in your assignment to your instructor.

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

ExcelCh2CE_solution.xlsx

Arnold / $328,788.00 / 15 / 5.75% / $49,318.20 / $279,469.80 / $2,320.75
Barber / $500,000.00 / 15 / 5.75% / $75,000.00 / $425,000.00 / $3,529.24
Bollis / $112,485.00 / 30 / 6.25% / $28,121.25 / $84,363.75 / $519.44
George / $350,000.00 / 30 / 6.25% / $87,500.00 / $262,500.00 / $1,616.26
Hood / $761,978.00 / 20 / 6.00% / $152,395.60 / $609,582.40 / $4,367.24
Morgan / $192,940.00 / 15 / 5.75% / $28,941.00 / $163,999.00 / $1,361.86
Paul / $606,563.00 / 20 / 6.00% / $121,312.60 / $485,250.40 / $3,476.48
Pinder / $319,765.00 / 30 / 6.25% / $79,941.25 / $239,823.75 / $1,476.64
Average Selling Price / $381,985.89 /
Loan Term / Interest Rate / % Required for Down Payment
15 / 5.75% / 15.00%
20 / 6.00% / 20.00%
30 / 6.25% / 25.00%
Loan Statistics
Number of Loans / 9
Highest Amount Financed / $ 609,582
Lowest Amount Financed / $ 84,364
Total Amount Financed / $ 2,749,005
Todays Date / Current Date
Prepared by: / Student Name

1

Excel Chapter 2 – Formulas and Functions: Math Basics for Spreadsheet Use

ExcelCh2CE_solution_formulas.xlsx

First National Bank- New Loans
Customer / Selling Price / Loan Term / Interest Rate / Down Payment / Amount to be Financed / Monthly Payment
Allen / 265354 / 30 / =VLOOKUP(C4,$A$18:$C$20,2) / =VLOOKUP(C4,$A$18:$C$20,3)*B4 / =B4-E4 / =PMT(D4/12,C4*12,-F4)
Arnold / 328788 / 15 / =VLOOKUP(C5,$A$18:$C$20,2) / =VLOOKUP(C5,$A$18:$C$20,3)*B5 / =B5-E5 / =PMT(D5/12,C5*12,-F5)
Barber / 500000 / 15 / =VLOOKUP(C6,$A$18:$C$20,2) / =VLOOKUP(C6,$A$18:$C$20,3)*B6 / =B6-E6 / =PMT(D6/12,C6*12,-F6)
Bollis / 112485 / 30 / =VLOOKUP(C7,$A$18:$C$20,2) / =VLOOKUP(C7,$A$18:$C$20,3)*B7 / =B7-E7 / =PMT(D7/12,C7*12,-F7)
George / 350000 / 30 / =VLOOKUP(C8,$A$18:$C$20,2) / =VLOOKUP(C8,$A$18:$C$20,3)*B8 / =B8-E8 / =PMT(D8/12,C8*12,-F8)
Hood / 761978 / 20 / =VLOOKUP(C9,$A$18:$C$20,2) / =VLOOKUP(C9,$A$18:$C$20,3)*B9 / =B9-E9 / =PMT(D9/12,C9*12,-F9)
Morgan / 192940 / 15 / =VLOOKUP(C10,$A$18:$C$20,2) / =VLOOKUP(C10,$A$18:$C$20,3)*B10 / =B10-E10 / =PMT(D10/12,C10*12,-F10)
Paul / 606563 / 20 / =VLOOKUP(C11,$A$18:$C$20,2) / =VLOOKUP(C11,$A$18:$C$20,3)*B11 / =B11-E11 / =PMT(D11/12,C11*12,-F11)
Pinder / 319765 / 30 / =VLOOKUP(C12,$A$18:$C$20,2) / =VLOOKUP(C12,$A$18:$C$20,3)*B12 / =B12-E12 / =PMT(D12/12,C12*12,-F12)
Average Selling Price / =AVERAGE(B4:B12) /
Loan Term / Interest Rate / % Required for Down Payment
15 / 0.0575 / 0.15
20 / 0.06 / 0.2
30 / 0.0625 / 0.25
Loan Statistics
Number of Loans / =COUNTA(A4:A12)
Highest Amount Financed / =MAX(F4:F12)
Lowest Amount Financed / =MIN(F4:F12)
Total Amount Financed / =SUM(F4:F12)
Todays Date / Current Date
Prepared by: / Student Name

1

Exploring Office 2007Excel Chapter 3HOMEWORK

Student Name: ______Section: ______
Due Date: ______00 Points PossiblePoints Earned:______

Learning Objectives

At the end of this lesson students should be able to:

  • Choose a chart type.
  • Create a chart.
  • Modify a chart.
  • Enhance charts with graphic shapes.
  • Embed charts.
  • Print charts.

Assignment

  • Read Chapter 3 –“Charts: Delivering a Message” pages 000-000.
  • Complete the three Hands-on Exercises in the chapter.

When your assignment is complete, do the following:

Turn in the following printouts to your instructor:

□“chap3_ho1_sales_solution.xlsx” printout.
This should look similar to Figure 0.00 on page 000.

□“chap3_ho2_sales_solution.xlsx” printout.
This should look similar to Figure 0.00 on page 000.

□“chap3_ho3_sales_solution.xlsx” printout.
This should look similar to Figure 0.00 on page 000.

12