Intermediate Excel for CPAs

1
Intermediate Excel for CPAs

• November 10, 2000

2
Your Instructor

• JULIA E. BENSON Assistant Professor770-551-3140
  jbenson_at_gpc.peachnet.edu

3
Excel Versions

• Excel 97
• Excel 2000

4
Prerequisite Skills

• Creating and opening workbooks
• Cell references
• Basic data entry and selection
• Numeric formatting
• Navigation
• Saving and printing worksheets

5
Creating and Retrieving

• Financial Workbooks
• Financial Worksheets

6
Cell Addresses

• Absolute
• Relative
• Mixed

7
Data Entry

• Text
• Values
• Financial formulas
• point/create formulas
• Financial functions

8
The Power of Recalculation

• Allows for what-if financial analysis
• Changes in values change formula/function results

9
Selection and Navigation

• Point and click selection
• Keyboard navigation
• Copying data vs. moving data

10
Spreadsheet Management

• Save
• Manual
• AutoSave
• Print

11
Practice Review
12
Making Spreadsheets More Effective
13
Project Planning

• What is your purpose?
• What results do you desire?
• Determine
• data available
• calculations needed
• Design
• spreadsheet layout
• data entry

14
Customizing the Spreadsheet

• Row Height/Column Width
• Use of
• Menu
• Click and Drag Formatting
• AutoFit
• Renaming Workbook Sheets

15
Mathematical/Financial Operators

• -
• /

• ltgt
• gt
• lt
• gt
• lt

16
Order Of Operations

• ( )
• /
• -
• ltgt gt lt gt lt

17
AutoFill

• Fill Handle
• Drag-and-drop data/formula entry
• Custom Lists

18
Ranges of Cells

• Keyboard Selection
• Mouse Selection
• Contiguous vs. Random Selection
• Clearing
• Naming Ranges of Cells

19
Other Useful Tools

• Freezing Frames
• Keep header row/column fixed in place
• Spell Checking
• Toolbar
• Menu
• Comments
• Annotating cells
• Viewing comments

20
Predefined Functions and the S Key

• Now( )
• Today( )
• Sum( ) and S
• Average( )
• Count( )

• CountA( )
• Max( )
• Min( )
• Pmt( )

21
Practice Exercises 1 and 2A
22
Changing Attributes For Effective Presentations
23
Font Changes

• Typeface
• Size
• Bold and Italic
• Number Formats
• Strikethrough
• Subscript and Superscript

24
Aligning Cell Contents

• Centering Across Columns
• Center and Merge Cells

25
Inserting and deleting

• Rows
• Columns

26
Adding Cell Enhancements

• Lines
• Borders
• Backgrounds
• Patterns

27
Autoformat

• Replicate cell formatting
• Uses Format Painter tool

28
Printing Enhancement

• Print Preview
• Orientations
• Portrait
• Landscape
• Headers Footers

29
Scaling Printouts

• Printing on one page

30
Practice Exercise 2B
31
Creating Charts Graphs
32
The Chart Wizard

• Four-step process
• Creates chart/graph based on cells you select
• Over 30 basic types of chart available
• Most types have several variations
• Chart Toolbar

33
Graphic Enhancement

• The Chart Toolbar
• Moving/resizing
• Changing chart type
• Formatting chart components
• Showing/hiding items
• Text boxes

34
Printing Graphs

• Allows display of financial information for
  better understanding/comprehension.
• Graphs Only
• Graphs With Spreadsheet

35
Saving Graphs

• Saved automatically when worksheet saved

36
Practice Exercises 2C and 3
37
DATABASE CAPABILITIES OF EXCEL
38
Lists

• Header row
• Column labels
• Formatting of headers and columns
• Data

39
Data Forms for Input

• Easy way to add/edit/delete records in a list
• Header row specifies field names used in form
• Navigate fields with tab
• Navigate between records with Enter

40
Sorting Lists

• Sort records in list
• Based on one or more key fields
• Primary
• Secondary
• Tertiary
• Ascending or Descending

41
Simple Autofiltering

• Select records meeting specific criteria
• Data values
• Combinations of data values
• Top 10
• All (unfilters)
• Records filtered out are hidden, NOT deleted

42
Practice Exercise 4
43
EXTENDING DATABASE CAPABILITIES
44
Criteria Ranges

• Criteria range
• defines filtering criteria
• area in worksheet independent of list
• at least two rows by one column

45
Advanced Filtering

• Uses criteria ranges
• May return records in place (hiding filtered
  records)
• Or may copy filtered records to another location

46
Database Functions

• Parallel statistical functions
• Operate only on records satisfying established
  criteria

47
Common Database Functions

• DSUM
• DAVERAGE
• DMAX

• DMIN
• DCOUNT
• DCOUNTA

48
Practice Exercise 5
49
THREE-DIMENSIONAL WORKBOOKS
50
Multisheet Workbooks

• Moving worksheets
• Copying worksheets
• Displaying multiple worksheets
• Selecting and grouping multiple worksheets

51
Three-Dimensional Cell References

• Add worksheet reference to cell reference
• Possible formats
• WorksheetName!CR
• WorksheetName!CRCR
• Worksheet1Worksheet2!CR
• Worksheet references are always absolute

52
SIMPLE MACROS
53
Recording New Macros

• Determine macro actions ahead of time
• Write it out and test it before recording!
• Create macro and give it a name
• May save in
• Current workbook
• New workbook
• Macro workbook

54
Playback of Macros

• Select active cell
• Determines starting location for relative
  references
• Play back the macro
• Results are same as if you had typed/clicked

55
Editing Macros

• Requires editing actual Visual Basic code
• May or may not require actual knowledge of Visual
  Basic

56
WHAT-IF FINANCIAL ANALYSIS
57
Performing What-If Financial Analysis

• Set up original data and formulas
• Determine variables to analyze
• View results of modifying data on results

58
Scenarios

• Create worksheet versions with different values
  for selected cells
• Shows changing results from different values
• Useful for complex analysis and summarizing
  results

59
Scenario Manager

• Automates scenario creation
• Specifies cells to change and values for cells
• Invokes summary display on separate worksheet

60
Practice Exercise 6
61
The End

• Thank you for attending!