Creating Data Tables:

"Getting Rich by the Dozen"

This lesson will enable students to create and manipulate a simple table of data. Students will work a story problem about a donut sale fundraiser and calculate the difference between cost and sale price (e.g., the profit). This exercise will also encourage students to ask "what if" questions. It is a good preparatory exercise for the longer, more complex activities found in one of the other Teaching for Understanding strands-Integrated Projects.

Classroom Discussion and Activities

Computer Lab Activities

Classroom Discussion and Activities

• Present the following story problem to give students a context for the exercise:

 

The school soccer team sells donuts every Wednesday morning before school to raise money for new uniforms. Their coach would like to figure out how long it will take to reach their goal of $250.

 

• Give students the following information and ask them to perform the basic computations either by hand or with calculators:

 

The soccer team purchases donuts for $3.60 a dozen.

 

How much does each donut cost the team? ___________

 

They sell the donuts for 50 cents each.

 

How much profit do they make on one donut? ___________

 

How much profit do they make per dozen? _____________

 

How much do they make if they sell 9 dozen? __________

 

• Build a table to compute profit by dozen.

 

Build a table using gridport.doc as a basis for the lab exercise with the spreadsheet. Create labels for each column (Dozen, Cost, Sale Price, Profit). See donuts.xls as an example.

 

Work with students on the formulas that will be used in this table. There are two important types of formulas:

• Basic operations-multiplication must include the equal sign, cell references, and the multiplication sign (e.g., =B2*B3).

• Using constants-to multiply by the same number, you can use a constant. In donut.xls, we multiply the sale price of $6.00 per dozen down the column. The formula for the cell below the $6.00 is =A3*$C$2. The dollar signs in the formula tell the computer to make the number in cell C2 a constant. If you use a constant, you can change the number (e.g., change it from $6.00 to $6.50), and all of the numbers in the column will change accordingly. This will be important when students ask "what if" questions.

An example of different formulas can be seen in the table below.

 

• Predict Long-range Profits

 

Build a table to predict long-range profits. You can do this by asking the following kinds of questions: "How many weeks could the team sell donuts during one school year?" "How much would they make in a year if they sell 9 dozen donuts every week?" "How could you add a column to the table to calculate profit for one year for each of the different amounts?"

 

Computer Lab Activities

• Have students enter data and formulas for the following two spreadsheets:

 

Profit by Dozen

 

Long-range Profits

 

• Make changes to spreadsheet

 

Make a variety of changes to the spreadsheet discussing the power of the computer and formulas to change many calculations at once. Here are some possible changes:

 

• The cost of donuts just went up to $4.00 a dozen.

• The cost of donuts just went up to $4.00 a dozen and you decide to charge 55 cents.

• The donuts cost $3.60 and you decide to sell each one for 45 cents.

• You can only sell donuts for 30 weeks.

Added Exercises

• Make line charts of the anticipated growth.

• Make a table of actual profits and compare with the predicted table.

• Make line charts comparing the actual versus predicted income.