Exploring Excel

1
Exploring Excel
Chapter 8 The Expert User Workgroups, Auditing,
and Templates By Robert T. Grauer Maryann Barber
2
Objectives (1 of 2)

• Develop spreadsheet model for financial forecast
• Use styles to automate formatting
• Differentiate between precedent and dependent
  cells
• Track editing changes
• Use cell comments

3
Objectives (2 of 2)

• Explain how workgroup functions enable
  collaborative work
• Resolve conflicts between users
• Describe the use of data validation
• Use conditional formatting
• Explain how template facilitates the creation of
  a new spreadsheet
• Create a template

4
Overview

• Learn to use worksheets for financial and budget
  planning
• Use a financial forecast
• Spreadsheet as a decision making tool
• Use the Auditing toolbar to ensure accuracy

5
A Financial Forecast

• Enter row and column heading
• Enter initial conditions and assumptions
• Develop the formulas for the first year
• Develop the formulas for second year and assumed
  rates of change
• Copy the formulas and format

6
Building a Financial Forecast

• Decide which cell references should be relative
  and which should be absolute
• Isolate assumptions and conditions separately
  from forecast
• Conditional formatting
• Setting styles

7
A Financial Forecast
8
Checking the Forecast

• Potential for user error exists in spreadsheets
• Check spreadsheets for math and logic errors not
  just formatting mistakes
• Use functions rather than formulas
• Auditing toolbar helps you trace the
  relationships between cells

9
Hands-On Exercise 1

• Enter the Formulas for Year One
• Enter the Formulas for Year Two
• Copy the Formulas to the Remaining Years
• Create a Style
• Rotate and Indent Text
• Conditional Formatting
• Complete the Formatting

10
Workgroups and sharing files

• Workgroups make it easier to share Excel files
  among a group of users
• Use Reviewing toolbar to Track changes made by
  others
• Allows for changes to be monitored
• Comments can help other users
• Can be shared on the Web on an Intranet

11
Auditing the Worksheet

• Shows graphical relationships built into formulas
• Identifies Precedents and Dependents
• Precedents are the cells referenced by the
  formula
• Dependents identify the formulas that reference a
  cell

12
The Workbook
Trace dependents
Trace precedents
13
Data Validation

• Data Validation command restricts values to be
  accepted in a cell
• Data validation is useful in shared workbooks
• Garbage In Garbage Out (GIGO) implies that
  spreadsheet is only as data it is based on

14
Data Validation command
15
Hands-On Exercise 2

• Display the Auditing and Reviewing Toolbars
• Highlight Changes
• Trace Dependents
• Trace Precedents
• Accept of Reject Changes (Resolve Conflicts)
• Insert a Comment
• Data Validation

16
Templates

• Most templates are based on protected workbooks
• Certain cells are changed but others are
  protected
• Unlock cells that are subject to change
• Templates are stored in the template folder file

17
Hands-On Exercise 3

• Clear the Assumption Area
• Protect the Worksheet
• Test the Template
• Save the Template
• Open the Template

18
Summary (1 of 2)

• Spreadsheet used as a decision making tool
• What if analysis can e employed
• Use a style to set formatting
• Apply conditional formatting
• Use the Auditing Toolbar

19
Summary (2 of 2)

• Build a shared workbook to be edited by multiple
  users
• Use the Data Validation command to restrict poor
  data entry
• Build a template to create other workbooks
• Protect the worksheet

20
Practice with Excel

• Email Your Homework
• The VALUE Error
• Erroneous Grade Book
• The Power of Compound Interest
• Add Macros to a Template
• The Scenario Manager
• The Expense Report

21
Case Studies

• The Entrepreneur
• Publishing to the Web
• Spreadsheet Solutions
• The License Agreement
• Password Protection