Spreadsheet Shopping Spree

Imagine This…

A distant relative in California, whom you have never met, has won the lottery. He was awarded $1,000,000 and has decided to be very generous with the money! He is giving every relative that he knows $1,000 to spend, and the timing could not be more perfect for the holiday gift-buying season! He attached a note to your check to remind you to be very generous when spending the money.

He warns that you are not spend more than $100 on yourself, and you must not spend more than $200 on each person you buy gifts for. You MUST spend EXACTLY $1,000 (no more, no less) or all of the money will have to be repaid.

You should probably know that this relative has two kids of his own, a four year-old son and a 7 year-old daughter. You might want to send them a little something too, to show your gratitude to the relative that gave you this money. Use the chart on the back to plan out your shopping trip. You do not need to use all of the lines, but don’t forget your siblings and your parents!

Happy Shopping!

Objectives

· Students will be able to create a spreadsheet with researched information.

· Students will be able to use formulas to solve math problems within their spreadsheet

METS

  • CI.2- Create an original project using a variety of media. (Spreadsheet)

  • CT.1- Use databases to make predictions, develop strategies, and evaluate decisions to assist wit problem solving

  • DC.1- Provide accurate citations when referencing information.

  • RI.1- Use a variety of digital resources to locate information.

  • TC.6- Use accurate technology terminology.

Before you begin working on your Shopping Spree, let's review some of the basic terms that we have learned about Microsoft Excel:

What is a Spreadsheet???

A spreadsheet is a grid that organizes data into columns and rows. Spreadsheets make it easy to display information, and people can insert formulas to work with the data. For example, there is a particular icon that has a formula to sum up numbers that are given. This icon is called auto sum. Information can also be sorted and filtered.

Basic Spreadsheet Vocabulary

Now Onto Our Shopping Spree...

To Set up Your Spreadsheet

1. In Open up your Google Drive and create a new Google Sheet, Title your sheet, Shopping Spree.

2. In cell A1 type: Shopping Spree (Be sure to Merge the cells).

5. In cell A2 type: Money to Spend: $1000.00 (Merge the cells again).

6. Type the information in the following cells:

A3:Gift Recipient

B3: Gift Purchased

C3:Store Purchased From

D3: Cost of Item

E3: Quantity

E3: Total

F27: Subtotal

F28: Tax

F29: Grand Total

F30: Money Left Over

  • Be sure to BOLD all of the headings above.

7. Input the items you would like to buy in column A. Record the cost of each item in column B and how many of each item you’d like to buy in column C.

Example: See the first three items Josh selected in the example below.

Josh's Shopping List

8. Let's Go Shopping! Begin shopping and spend your $1,000.00 inheritence and use the spreadsheet to keep the total cost of all the toys just under $1000.00, tax included. And don’t forget that the total cost of all the items (tax included) must fall between $995.00 and $1000.00 or you lose all the money.

Creating Formulas

To calculate the Total of each row, Type the formula

=(B4*C4). = Cost of Item * Quantity in cell E4, F4, G4, H4....each cell underneath the Total heading. **(The number will change for each row)

Subtotal: Type the formula =SUM (F4:F26) in cell F27

Tax: Type the formula =(F27*0.06) in cell F28

Grand Total: Type the formula =SUM(F27:F28) in cell F29

Money Left Over: Type the formula =(1000-F29) in cell F30

Formatting Your Spreadsheet

Highlight the entire column D. Click on Format-> Number then select Currency.

Click on the letter F in Column F to highlight the entire column. Click on Format-> Cells then click on Currency. Click OK

You will now add color to make your spreadsheet look more eye appealing. Highlight the cells you wish to color and Click on the arrow next to the Fill Color button on the Formatting Toolbar and select a color that you wish to add to the selected cells. Do this throughout your spreadsheet.

Be sure to Bold, center, and change the font size and color of ALL of your headings.