Holiday Gift Tax Spreadsheet

In the classroom portion of this lesson, a fictitious anonymous benefactor gave you $5000 to spend on gifts for your family and friends. You could purchase no more than 3 items for yourself and at least 10 items for other people. Your goal was to spend as much of the money as possible. In the computer lab, you will now create a spreadsheet that will calculate sales taxes and totals. An IF statement will be used to calculate sales tax based on the 8% tax charged in Lewisville, or 8.25% tax charged in Dallas. You will also create a formula which calculates the amount of spending money that you have spent after each purchase. You will then create a formula that checks whether your answers are correct.

1. Start Microsoft Excel.

Points to Remember about Spreadsheets

Formulas - math problems written in a way that the spreadsheet understands.

• Formulas begin with =
• Use cell names (E3) instead of values ($47.52) in formulas.
• DO create formulas like this =D2+F2
• DON'T create formulas like this =132.36+8.93

Functions - special programming words that make the spreadsheet perform a desired math operation.

• When using functions like SUM, the range has a starting cell name and an ending cell name.
  Ex. =SUM(B2:B36) where B2 is the starting cell and B36 is the ending cell.
• An IF function is used to tell the computer how to make decisions, so you don't have to. To calculate the sales tax on a gift purchased in Lewisville or Dallas, the computer would make the following decision and calculations. Was the gift purchased in Lewisville? If true multiply the price by 8%, if false multiply the price by 8.25%. Here is what the spreadsheet formula would look like:
  =IF(E2="L",D2*.08,D2*.0825)
  where E2="L" is the comparison/decision, D2*.08 is the calculation for true, and D2*.0825 is the calculation for false.

Making Columns Wider

• If #### is displayed in a column, then the column is too narrow to display the data. So...
• To adjust the widths of columns,
  1. Click on the column label for the column to adjust.
  2. Move your mouse pointer to the vertical line between the selected column and the column to the right. when your mouse pointer is over the vertical line, the mouse pointer will change to a two-headed arrow.
  3. Double-click to make the column as wide as the widest text in the column OR...
     Click and drag left or right to adjust the width of your column to the desired width.
  4. Release the mouse button when the column is the correct width.

Copy and Pasting

• To copy and paste, highlight the cell(s) to copy, click on the copy button, highlight the cell(s) to paste to and click on the paste button.

Independent Practice

1. On the menu bar, click on File|Save As
2. In YOUR folder on the NETWORK drive, save the file as Teachername-GIFTTAX-lastnamef

Adding Your Name Header

1. On the Menu Bar, click on View|Header and Footers...
2. In the Page Setup dialog, click on Custom Header... button.
3. Click in the white space under Center Section.
4. Type your full name, your teacher's name, and your class period. See example below.
   Joe Schmoe
   Mrs. Gipson
   Period 6
5. Click on OK.
6. Click on OK.

Setting Up Your Spreadsheet Printing Properties

1. Click on File|Page Setup...

On the Page tab

1. On the Page tab, choose Landscape orientation.
2. Choose Fit To: 1 page(s) wide by 2 tall scaling.
3. Click on the Margins tab.

On the Margins tab

1. Set the top margin to 1.5 inch.
2. Change the bottom margin to 1 inch.
3. Change the left margin to .5 inch.
4. Change the right margin to .5 inch.
5. Choose Center on Page Horizontally.
6. Click on the Header/Footer tab.

On the Header/Footer tab

1. Do you recognize what you typed in earlier?
2. Click on the Sheet tab.

On the Sheet tab

1. Under Print Titles click in the box to the right of Rows to repeat at top:
2. Type 1:1
3. Under Print, choose Gridlines.
4. Click on OK.

Type your Column Headings

1. Type the column headings in the following cells:
   A1 - Gift Given To:
   B1 - Gift
   C1 - Store Name
   D1 - Gift Price
   E1 - City
   F1 - Sales Tax
   G1 - Amount Paid
   H1 - Total Spent
2. Click on the row 1 label on the far left side. The entire row should be selected.
3. Make the column headings bold.
4. Adjust the column widths so that all text is displayed in the header row. If you don't remember how, click here.

Changing the Number Format to Display $ in columns D, F, G, and H

1. Click on column label D.
2. On the Formatting Toolbar, click on the $ Currency Style tool.
3. Repeat for columns F, G and H.

Typing the data.

1. Type in your data for Gift Given To:, Gift, Store Name, Gift Price, City (L for Lewisville, D for Dallas) for each of the people in your list.
2. TYPE ONLY THE ABOVE ITEMS from your paper spreadsheet.

Setting up your Sales Tax formula.
When you calculated this by hand, you did the following comparison and calculation automatically. You looked at the gift, if the gift was purchased in Lewisville, you multiplied by a different tax rate than for gifts purchased in Dallas. Now we have to tell the computer how to do the same thing.

1. Click on cell F2.

On the Formula Bar/Formula dialog

1. Click Insert/Function and a dialog box will pop up.
2. Select IF from the list and click OK.
3. In the box to the right of Logical_test, type E2="L"
4. In the box to the right of Value_if_true, type D2*.08
5. In the box to the right of Value_if_false, type D2*.0825
6. Click on OK.

Setting up your Amount Paid and Total Spent formula.

1. Click on cell G2.
2. Type a simple formula to calculate the Amount Paid for the gift (price + sales tax). If you don't remember how, click here.
3. Click on cell H2.
4. Type the Total Spent formula, =G2

Copying and Pasting the Sales Tax, Amount Paid and Total Spent formulas to the remaining cells.
Why would you want to retype this stuff? Copying and Pasting is much better, yes?

1. Copy the F2 and G2 formulas and paste to F3 and G3. If you don't remember how, click here.
2. Click on cell H3.
3. Type a slightly different Total Spent formula =H2+G3
   Why did we need a different formula? Think about it.
4. Copy the F3, G3, H3 formulas and paste to the remaining F, G, and H cells.

Creating a Total Gift Price formula for Gift Price.

1. Click on the row number of your last gift row of data and REMEMBER that number.
2. From now on, if you see #, you will substitute your row number for that variable.
3. On the row below the last row of gift data in your spreadsheet, click on the cell in column D.

On the Formula Bar/Formula dialog

1. Click Insert/Function and a dialog box will pop up.
2. Select Sum from the list and click OK.
3. In the box to the right of Number_1, type D2:D# where # is the row number of your last gift data row.
4. Click on OK.

Creating a Total Sales Tax formula for Sales Tax.

1. On the row below the last row of gift data in your spreadsheet, click on the cell in column F.

On the Formula Bar/Formula dialog

1. Click Insert/Function and a dialog box will pop up.
2. Select Sum from the list and click OK.
3. In the box to the right of Number_1, type F2:F# where # is the row number of your last gift data row.
4. Click on OK.

Creating a Total Amount Paid formula for Amount Paid.

1. On the row below the last row of gift data in your spreadsheet, click on the cell in column G.
2. Set up a formula to calculate the Total Amount Paid using what you have learned above.

Creating a Check My Work Formula

1. On the row below your last row of gift data in your spreadsheet, click on the cell in column I.

On the Formula Bar/Formula dialog

1. Click Insert/Function and a dialog box will pop up.
2. Select IF from the list and click OK.
3. In the box to the right of Logical_test, click on the Total Amount Paid cell (bottom of column G), type =, and then click on the last Total Spent cell in your list (bottom of column H.) Yours should be similar to the following example: G43=H42
4. In the box to the right of Value_if_true, type Congratulations! SPELLING IS IMPORTANT
5. In the box to the right of Value_if_false, type Check Your Work! SPELLING IS IMPORTANT
6. Click on OK.

Setting up Conditional Formatting

1. Click on your Check My Work formula cell.
2. Click on Format|Conditional Formatting...
3. Under Condition 1, click on the middle box down arrow and choose not equal to...
4. In the box on the right, type Congratulations! SPELLING IS IMPORTANT
5. Click on the Format button.
6. Beneath the word Color:, choose red.
7. Click on OK
8. Click on Add>>
9. Under Condition 2, click on the middle box down arrow and choose equal to...
10. In the box on the right, type Congratulations! SPELLING IS IMPORTANT
11. Click on the Format button.
12. Beneath the word Color:, choose green.
13. Click on OK
14. Click on OK

Setting your Print Area

1. Highlight ALL of your data.
2. Click on File|Print Area...|Set Print Area
3. Check spelling and your numbers, do a reality check to make sure your calculations are correct.
4. Observe your Check My Work formula cell. Is the value displayed in green? If not, there are things to fix.
5. You may now change the fonts and styles to suit you. The only requirement is that the data must be readable and approved by your math teacher who will be grading this assignment.

Call a teacher to check your work before you print... Congratulations on completing this project.

For more information about this lesson, please contact Jeff Skelton, Computer Literacy Teacher, Lewisville ISD