Intro To Excel - Simple Assignment

80-322 Computer Methods – Faculty of Education, University of Windsor (C. Rivait)

Class Lesson Assignment #2 – Spreadsheets Across the Curriculum Class Date :Wednesday, October 15, 2008 Due Date: Electronically submitted, before the end of the day (midnight), October 29, 2008

Flip your imagination switch to the ON position and pretend that you entered a contest to win a $1000.00 shopping spree at the mall of your choice. Imagine the surprised look on your face when you’re called to the phone and told that you WON!   But wait! The surprised look on your face turns serious when you learn there’s a catch! You are informed that the total cost of all the items you select must not exceed $2500.00, taxes included. Also, the total cost of all the items (tax included) must fall between $2,475.00 and $2,500.00 or you lose everything. "Not a problem!" you exclaim. "I have a secret weapon called a spreadsheet! I can make it do the math for me - right down to the very last penny! Show me the money!" A spreadsheet program can be used to perform calculations, analyze and present data. Spreadsheet programs include tools for organizing, managing, sorting and retrieving data and testing “what if” statements and scenarios. The chart feature can display numerical data as a graph. Spreadsheet Interface Introduction and Overview

Cell Address:  Name given to the cell’s   location in the spreadsheet.   (Colum letter and row  number) 

Familiar “TABS”, buttons,  and application functions 

Data Entry Bar: Shows  data as it is entered  into a cell.

 

Value: Numbers entered  In cells. 

Label: Words at the top of  columns or other text. Column:  The vertical  divisions in a spreadsheet.  (Named with a letter)

Row:  The horizontal  Divisions in a spreadsheet.   (Named with a number) 

Sheet Name: Click on  Name Text to edit  worksheet name; Right  Click over tab to copy  to a new sheet  (duplicate). 

Cell: The small rectangle  In a spreadsheet where a  row and column intersect 

Auto Sum 

 

Requirements and Steps: 1. Spreadsheet columns should be organized as follows, and formatted appropriately for printing (to be demonstrated and setup in class) –

QUANTITY

COST 

PST 

GST 

TOTAL ITEM  COST 

Boots 

1 

$199.99 

$16.00 

$10.00 

$225.99 

CD's 

5 

$17.99 

$1.44 

$0.90 

$20.33 

Gift Cards 

3 

$10.00 

$0.80 

$0.50 

$11.30 

iTouch 

1 

$499.00 

$39.92 

$24.95 

$563.87 

  

  

  

  

  

  

ITEM 

  

  

  

  

  

  

  

  

  

  

  

  

  

  

      TOTAL SPENT 

$821.49

2. Enter first item - label , price, and cost. 3. In the PST cell for your first item, enter the calculation for PST using a cell reference to the “Item Cost” (ex. =D3*0.08) 4. Enter a similar calculation for GST. 5. Calculate the “Total Item Cost” with a formula that uses the SUM function. Try the “Auto Sum” feature and observe what happens. 6. “Copy and paste” cell formulas to other item rows in the appropriate column locations (as demonstrated). 7. Create a “Total Spent” label and create the calculation (Use Auto Sum tool). 8. Manipulate your item cost and quantities (What if?) to achieve the required spending level. 9. Arrange column widths, text sizes and fonts for appropriate presentation. a. Select single cells or groups of cells and format fonts, justification and colour. 10. First, select all spreadsheet cells. With all cells selected, ensure you are on the “Home” tab, use the Border tool to place grid lines between cells. Experiment with thicker border lines. 11. Use the Bucket tool to add colour to header cells. 12. After you have the spreadsheet completed and formatted the way you like, “Right Click” over the worksheet name tab, select Move or Copy, and create a duplicate copy of the worksheet. By clicking on the Name Tabs, edit the text to rename the worksheets – “Original” and “Sorted”. 13. On the “Sorted” worksheet, use the Sort section of the menu to organize/sort the spreadsheet in one (or more) of the following – a. Alphabetically by Item name b. By cost in either Ascending or Descending” order

Other considerations – • • •

Enhanced formatting and presentation techniques o Text, colour, font, justification, borders Pie Chart or Bar Graph – “Proportionate or Relative Cost” Other pertinent calculations/functions/statistical functions o Average, Mean, Median