Skip To Main Content

G Sheet - Shopping Spree

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

 

 

Active cell An active is the cell you are currently working on
Auto sum A formula that will add up a column of numbers
Cell Reference The column number and the row letter of a cell
Cell Each individual box on the spreadsheet
Column The vertical reference on the spreadsheet
Fill  To fill a cell with color using the paint bucket tool
Fill handle The dot at the bottom of each cell while it is active
Filter The procedure to select certain information in a spreadsheet.
Formula A formula must always starts with “=” sign and what the calculations are for each cell
Graph A visual representation of data
Grid Lines The horizontal and vertical lines on the spreadsheet
Row The horizontal reference on the spreadsheet
Selecting To highlight a set of cells
Sheet One page of a worksheet
Sheet tabs Tabs that identify the worksheets in a workbook
Spreadsheets A grid that organizes data
Value A number that can be entered into a cell
Workbook Many worksheets
Worksheet One page of a spreadsheet
 
 
 
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.