• 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.