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.