4ºESOen – TICs – Excel spreadsheet activities (IV)

You are going on a “Back to School” shopping trip with your parents in Boise, Idaho. You will also be purchasing supplies for your brother and sister.  Your mother has asked you to prepare a spreadsheet of the expenses to help in budgeting for the trip.

Type the following information into the spreadsheet file. You will need to widen columns A and B and bold row 1.

excel1

Formatting Changes:

  • Click on the B at the top of column B. Drag down through cell B8.  Center the contents of this column.
  • Click in Cell C1 and drag through D8. Right-align these cells.
  • Although there is currently no text in cells B9 –D11, right align these cells as well.
  • Formatting Currency. Click the letter C at the top of column C and keep the mouse clicked while dragging to column D.  This process will select the complete contents of both columns.
  • Click the currency button from the Formatting toolbar.

excel2

This will format the numbers in columns C and D so that they will be displayed with a dollar sign and two decimals.

excel3

Let’s figure out how much money your parents will spend on backpacks.

Click Cell D2. Type =B2*C2. (The number of backpacks purchased times the price of each backpack). Mentally estimate how much money you think the backpacks will cost.  Hit <Enter>.

Compare your estimate to the total shown in column D.  Are they close? If you formatted the currency correctly, column D should show the total with a dollar sign.

Your spreadsheet should now look like this:

excel4

How much money will your parents spend on shoes?

Click Cell D3. Type in the formula =B3*C3. Estimate the answer in your head and then compare after pressing <Enter>. Your turn to enter the formulas. Click on Cell D4.  Type the formula that will compute the amount that will be spent on paper.  Remember the formula MUST begin with an = sign.  Press <Enter> after typing the formula.  Does the total in cell D4 make sense?

Do you really have to type the formula in each time for the totals in Column D? Remember the Fill Down feature?  Click cell D4.  Position the mouse in the lower-right corner of the cell, and then drag to cell D8 to fill down.  Did the totals appear in each cell?  If the same number appeared in each cell, then you copied the contents, rather than the formula.  Undo and try again until you have different answers in each cell of Column D.

Creating a Border. Highlight cells A8 through D8.  Click the Bottom Border button to create a line below this group of cells.

excel5

Computing the subtotal.

Click cell C9.  Type the word Subtotal.

Click cell D9.  You need to put the formula that will give you the total of these cells.  You have several choices, based on what you learned before.  Select the easiest and quickest method for you.

Your total should equal $189.30.  If it doesn’t, go back and check your formula.

Imagine you will now have to pay a 6% sales tax!

Click cell C10.  Type the words Sales Tax. That means you pay an additional 6% of your subtotal in tax.  The formula for this sales tax will be =6%*Subtotal.  The subtotal is in cell D9, so the final formula would be =6%*D9.

If the number displayed in D10 is NOT $11.36, check to see where you made your error.

Computing the Grand Total.

Click cell C11.  Type Grand Total. What formula should be typed into cell D11 to compute the total of the purchases (your subtotal or cell D9) and the sales tax (cell D10)?  Enter this formula into cell C11.  Press <Enter>.

Your finished spreadsheet should look like the one below.  If the numbers are NOT the same, check to see where you made an error.  Remember to use the quick shortcut to show formulas for an easy way to find mistakes.

excel6

 

You need to return to Boise to purchase some additional items and return others.  Make the following changes to the spreadsheet contents:

The backpacks were not big enough.  You must return all 3 of them and buy bigger ones.  But the bigger ones cost $14.99.  Change the price of the backpacks to $14.99.  Note what happens to the total cost in Column D.

You and your siblings need 2 binders each instead of 1.  Change the total number of items in Column B under binders to 6.  Note what happens to the total cost in Column D.

Because you used formulas in Column D instead of actual numbers, when you change the numbers in the other columns, Column D changes automatically. This is the beauty of a spreadsheet!  Once set up correctly, you can change the contents and quickly see how that change will affect the whole process!

Check what happened to the subtotal, sales tax, and grand total. Each of these should have also changed when you made the changes in step 3.  The sales tax should now read $12.97.  If it does not, go back to step 3 and check your work.

 

 

 

4ºESOen – TICs – Excel spreadsheet activities (V)
Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s