Henrico County Public Schools

Technology Integration Lesson Plan

Advanced Computer Information Systems 6613

Lesson Title:

Creating Templates and Working with Multiple Worksheets and Workbooks

Teacher: Hunter Thomas / School: Godwin High School
Grade Level: 9-12
List specific tasks and related SOLs which will be taught during this lesson:
Task/Competency Number: / BUS6613.008 / SOL# / C/T 12.2
Task/Competency Number: / BUS6613.011 / SOL# / C/T 12.2
Task/Competency Number: / BUS6613.012 / SOL# / C/T 12.3
Task/Competency Number: / SOL#
Lesson Overview:
Students will create a workbook with multiple worksheets using a template. They will also analyze the data and create a complex graph to show their answer.
Objectives and Goals:
  1. To create a template

  1. To copy data among worksheets in a workbook
  2. To drill an entry through worksheets

  1. To create formulas that use 3-D references to cells in different sheets in a workbook
  2. To create and reformat complex graphs

Approximate Time Required to Complete the Lesson:
60 minutes
Tools Needed:

Microsoft Excel

Tasks/Activities:
The clubs of Godwin High School have decided to put in a pool in the commons area of the school next year. The school has decided that this would build the students’ self-esteem. The school has approved this if the clubs pay for it. The pool will cost $8,300.00 to be installed. The clubs have also decided to each come up with a name for the pool. The club who raises the most money will get to name the pool.
Club A – This club decided to sell candy bars. It costs 60 cents for the club to buy each candy
bar. They sell the candy bars for $1.00 each. The first week, they sold 990 candy bars, the second week, they sold 1065 candy bars, the third week, they sold 1050 candy bars, and the fourth week, they sold 1000 candy bars. They want to name the pool – “The Eagle Bubbling Pit.”
Club B – This club decided to sell doughnuts. It costs $2.00 for the club to buy each dozen
doughnuts. They sell the doughnuts for $3.50 each. The first week, they sold 265 doughnuts, the second week, they sold 295 doughnuts, the third week, they sold 285 doughnuts, and the fourth week, they sold 255 doughnuts. They want to name the pool-“The Eagles Wet Nest.”
Club C – This club decided to sell cookies. It costs 50 cents for the club to buy each oversized
cookie. They sell the cookies for $1.00 each. The first week, they sold 268 cookies, the second week, they sold 275 cookies, the third week, they sold 275 cookies, and the fourth week, they sold 285 cookies. They want to name the pool – “Once Upon an Eagles Pond.”
Club D – This club decided to sell wrapping paper. It costs $1.50 for the club to buy each roll of
wrapping paper. They sell the wrapping paper for $4.00 each. The first week, they sold 168 rolls of wrapping paper, the second week, they sold 173 rolls of wrapping paper, the third week, they sold 170 rolls of wrapping paper, and the fourth week, they sold 165 rolls of wrapping paper. They want to name the pool – “The Pump Pond.”
Club E – This club decided to sell T-shirts. It costs $6.00 for the club to buy each T-shirt.
They sell the T-shirts for $10.00 each. The first week, they sold 125 T-shirts, the second week, they sold 120 T-shirts, the third week, they sold 100 T-shirts, and the fourth week, they sold 75 T-shirts. They want to name the pool – “West End Beach.”
You have been told that the formula for profit is: sales – cost
Process:
  1. Create a template to use for this problem.
  2. Create a six-sheet workbook.
  3. Name all of the sheets. Name the first sheet Pool Totals Sheet.
  4. Fill in all information into the worksheets.
  5. Create formulas that use 3-D references to cells in different sheets on the Pool Totals Sheet.
  6. Create 2 charts as new sheets:
  7. One should show who raised the most money.
  8. The other should show which week was most profitable for the club that raised the most money.
Assessment:
Printed papers will be graded on the scale below along with the answer sheet below:
Template is worth 5 points
Each sheet of the workbook is worth 10 points (the Pool Totals Sheet is worth 15)
Each Questions below is worth 5 points each:
  1. Did they raise enough money to put a pool in the commons area? ______
  2. How much money did they raise? ______
  3. Who made the most profit in week 2? ______
  4. What is (or was) the pool’s name going to be? ______
  5. Who made the least amount of money over the 4 weeks? ______
  6. How much did all clubs combined make during week 3? ______
Total Amount of Points: 100
Additional Information:
Students can get graphics from the Microsoft Office Design Gallery if they decide they need graphics in their workbooks.