Students will be able to:
- Gather information on the Internet (mock shopping spree – price, item)
- Enter this information into Google Spreadsheet
- Enter formulas to calculate totals, balance, least most and average price
- Link your spreadsheet to a blog post.
You just won $5,000 to decorate your dream room. This could be a game room, bedroom, home office, TV room, etc. You are looking for furnishing and decor for your room.
Shop at IKEA http://www.ikea.com/us/en/ or Target http://www.target.com
But wait! There’s a catch! The total cost of all the items you select must not exceed (go over) $5000.00, tax included. Also, the total cost of all the items (tax included) must fall between $4995.00 and $5000.00 or you lose everything. “Not a problem!” you exclaim. “I have a secret weapon called Google spreadsheet! I can make it do the math for me – right down to the very last penny! Show me the money!”
1. Log on to your Google Docs account and set up your file as follows:
2. Format B3-B18 (blue cells) as currency with 2 decimal places and show the dollar sign.
3. Format D3-D27 (blue cells) as currency with 2 decimal places and show the dollar sign.
4. Go to View > Freeze Rows > Freeze 2 Rows (this will freeze the top two rows)
5. Save your file as YourName_ShoppingSpree.
6. Go to IKEA or Target and shop for items to decorate your room:
7. Enter items and quantity.
8. Create the following formulas:
- In D3 – formula to multiply cost of the item by the quantity, then use the fill handle to fill to D18
- In D19 – formula to add prices of all the items (D3 to D26)
- In D20 – formula to calculate tax, sub-total by 5%
- In D 21 – formula to add the subtotal and tax
- In D22 – formula to subtract Grand Total from $5,000
- In D25 formula to calculate the most expensive item (use cost of item column)
- In D26 formula to calculate the least expensive item (use cost of item column)
- In D27 formula to calculate the average priced item (use cost of item column)
9. Insert 2-3 pictures and format font (size & color) so that your spreadsheet is easy to read.
10. Rename your sheet to MyRoomExpenses
11. Share this file with me so I can correct your work. [email@example.com]
12. File > Download as PDF with the following settings:
- Current Sheet
- Actual Size
13. Add this file to your blog.
Create a new post titled: Assignment 7 – Dream Room Spreadsheet and file it under the assignments category. Write a short intro paragraph about this assignment. What did you do? How did you like this lesson? etc. At the end of the paragraph create a link to your spreadsheet. “Check out my dream room expenses.”