Spreadsheets Calculation and Visualization Outline The Spreadsheet nature

Spreadsheets Calculation and Visualization

Outline • The Spreadsheet: – nature and purpose – terms • Spreadsheet Graphics: From Digits to Drawings 2

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

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?

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

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

The Spreadsheet – Cells (the intersection of a row and column) – Addresses (column letter and row number, e. g. , C 12) 7

Spreadsheet Structure • spreadsheets are composed of rows of columns • columns are designated by letters (AAZ…) Excel provides 256 columns (AIZ) • rows are designated by numbers starting with the number 1. Max rows in Excel is 65, 536

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 A 1 (sometimes this is also called cell address)

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

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). A B 1 Expenses Amount 2 Rent $400 3 Food $250 4 Utilities $120 5 Total $760 11

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

Entering Data • position the cursor over the cell, type and hit enter • use mouse, arrow keys to navigate around • Pg. Up/Pg. Dn- move up or down a spreadsheet a screen at a time • Cntrl/Pg. Up Cntrl/Pg. Dn - move across a screen at at time

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

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

The Worksheet – Formulas (a stepby-step procedure for calculating a number, e. g. =Sum(B 2: B 4). 16

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

Building Formulas • use mathematical operators and cell addresses • =A 1 + B 2 • can use other operators -, *, /, ^ • What if you need a long string of adjacent cell addresses -- use a range

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(B 1: B 7), (F 4: H 4), (E 7: F 14)

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 20

Building Formulas • functions - like a preprogrammed formula - ( Essentials Book) • use built-in functions like SUM, MAX, MIN, AVG • =SUM(A 1: B 6) • follow format of function • example =Min(range)

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

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

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

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

Spreadsheet Features • Predefined functions (e. g. , SUM, AVG, SQRT). • Macros (custom design your own feature) • Templates (ready-touse worksheets). 26

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

“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. 28

Spreadsheets Module C “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. ” What-if scenario 29

“What If? ” Questions • Validators - the equivalent of spelling and grammar checkers for spreadsheets. 30

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) 31

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) 32

Types of Charts • • • line chart bar chart/stacked bar chart pie chart x-y scatter chart radar chart 33

Bar Chart 34

3 D Bar Chart

Pie Chart 36

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 37