Assignment 7 – Dream Room Spreadsheet

Student Objectives:
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.

Student Directions:
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:

pic-spreadsheet-room

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. [lcunha@fallriverschools.org]

12. File > Download as PDF with the following settings:

  • Current Sheet
  • Actual Size
  • Landscape

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

Leave a Reply

Your email address will not be published. Required fields are marked *