Spreadsheets

1
Spreadsheets

• Calculation and Visualization

2
Outline

• The Spreadsheet
• nature and purpose
• terms
• Spreadsheet Graphics From Digits to Drawings

3
The nature of spreadsheets

• presents organized data
• manually constructed has been around for
  centuries
• aids in managerial decisions
• can be time consuming and tedious
• mistakes may mean redo all calculations

4
Spreadsheets

• for numerical work - combine formulas and numbers
• table format
• answer what if questions
• example What if the commission rate for the
  sales staff were raised 1/4, how much would that
  increase each salesperson's commission?

5
Electronic Spreadsheets

• computerized version of the manual
• spreadsheet is set up-- just enter in the data
  and then the calculations you need
• does all the calculations -- error free
• automatic recalculation - change one value or
  calculation and all dependent values on the
  spreadsheet are automatically recalculated

6
The Spreadsheet

• The spreadsheet is a malleable matrix that
  consists of
• Worksheet (a spreadsheet document)
• Columns (alphabetical horizontal divisions)
• Rows (numbered vertical divisions)

7
The Spreadsheet

• Cells (the intersection of a row and column)
• Addresses (column letter and row number, e.g.,
  C12)

8
Spreadsheet Structure

• spreadsheets are composed of rows of columns
• columns are designated by letters (A-AZ) Excel
  provides 256 columns (A-IZ)
• rows are designated by numbers starting with the
  number 1. Max rows in Excel is 65,536

9
Cells

• cell - the intersection of a row and a column
• Excel (65,536 X 256 ) 16,777,216 cells
• cell coordinates - is a combination of
  letter(column) and number(row). Example A1
  (sometimes this is also called cell address)

10
Active Cell

• Active Cell - highlighted cell (also called
  current cell)
• insert information
• change information

11
The Worksheet

• The worksheet is a grid formed by columns and
  rows and can contain
• Values (or numbers such as 4, -76, 120.00).
• Labels (words that explain what the numbers mean
  such as Food).

12
Different Types of Data

• Cells contain data
• labels - descriptions (text) no numerical
  calculation
• values - numbers and special chars ( , . ) -
  can be used in calculations
• formulas - numerical operation performed on
  values that can contain cell coordinates and
  values

13
Entering Data

• position the cursor over the cell, type and hit
  enter
• use mouse, arrow keys to navigate around
• PgUp/PgDn- move up or down a spreadsheet a screen
  at a time
• Cntrl/PgUp Cntrl/PgDn - move across a screen at
  at time

14
Entering Labels

• first character is a letter
• information contains nonnumeric characters
• if a number is used as a label (i.e... Street
  address) then precede it with a single quote mark
• quote mark will not be displayed
• labels are aligned left by default

15
Entering Values

• first character is a number or numeric symbol
• numeric data are aligned to the right

16
The Worksheet

• Formulas (a step-by-step procedure for
  calculating a number, e.g. Sum(B2B4).

17
Entering Formulas

• is an instruction to the program to calculate a
  number
• formulas contain cell addresses and one or more
  arithmetic operations (,-,,/ , , ())
• first character is an
• Excel evaluates formulas as you enter them and
  displays the result in the cell

18
Building Formulas

• use mathematical operators and cell addresses
• A1 B2
• can use other operators -, , /,
• What if you need a long string of adjacent cell
  addresses -- use a range

19
Range

• rectangular group of cells that is treated as a
  unit for a given operation
• specifies a lower and upper limit
• to define a range, it is upper-left to
  lower-right cells of the block
• format for a range is
• (upper-left cell lower-right cell)
• examples(B1B7), (F4H4), (E7F14)

20
The order in which Microsoft Excel performs
operations in formulas

• Operator Description
• Negation (as in 1)
• Percent
• Exponentiation
• and / Multiplication and division
• and Addition and subtraction
• Connects two strings of text (concatenation)
• Comparison

21
Building Formulas

• functions - like a preprogrammed formula - (
  Essentials Book)
• use built-in functions like SUM, MAX, MIN, AVG
• SUM(A1B6)
• follow format of function
• example Min(range)

22
Worksheets

• each Excel file is workbook which may contain one
  or several worksheets
• Excel can have up to 255 worksheets

23
Spreadsheet Features

• Automatic replication of values, labels, and
  formulas (relative versus absolute references)
• Automatic recalculation

24
Commands

• Help the user set up and use the spreadsheet
  (Menus and speed buttons)
• Global Commands - the entire spreadsheet
• Range Commands - on selected block of cells
• Cell Commands - on an individual cell

25
Formatting

• making the spreadsheet more readable and
  appealing
• widen columns
• number symbols (,,...)
• justification, fonts
• borders, some even different colors

26
Spreadsheet Features

• Predefined functions (e.g., SUM, AVG, SQRT).
• Macros (custom design your own feature)
• Templates (ready-to-use worksheets).

27
Spreadsheet Features

• Linking (reflect changes in related worksheets).
• Database capabilities.

28
What If? Questions

• Spreadsheets allow you to change numbers and
  instantly see the effects of those changes.
• What if I enter this value?
• Equation solvers
• Some spreadsheets generate data needed to fit a
  given equation and target value.

29
Spreadsheets
Spreadsheets
What would happen if we could cut shipping costs
by just 5 percent? A few keys are pressed, and
the other manager says, Looks good. If we can
get the shipper to agree to this, we can double
our sales volume -- and everyone wins.
Module
C
What-if scenario
30
What If? Questions

• Validators - the equivalent of spelling and
  grammar checkers for spreadsheets.

31
Spreadsheet Graphics From Digits to Drawings

• Charts allow you to turn numbers into visual
  data
• Pie charts (show relative proportions to the
  whole)
• Line charts (show trends or relationships over
  time)

32
Spreadsheet Graphics From Digits to Drawings

• Bar charts (use if data falls into a few
  categories)
• Scatter charts (use to discover, rather than to
  display, a relationship between two variables)

33
Types of Charts

• line chart
• bar chart/stacked bar chart
• pie chart
• x-y scatter chart
• radar chart

34
Bar Chart
35
3D Bar Chart
36
Pie Chart
37
Spreadsheets
Spreadsheets

• Advanced spreadsheet features
• macros
• Spreadsheet risks
• burying important assumptions inside cells
• creating formulas that contain mistakes
• poor design
• cells containing formulas look like any other
  cell (formulas can be erased by mistake)
• Strategies for avoiding errors