Intermediate Excel for CPAs

November 10, 2000

Your Instructor:

 JULIA E. BENSON Assistant Professor 770-551-3140 [email protected]

Excel Versions

 Excel 97  Excel 2000

Prerequisite Skills

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

Creating and Retrieving

 Financial Workbooks  Financial Worksheets

Cell Addresses

 Absolute  Relative  Mixed

Data Entry

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

The Power of Recalculation

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

Selection and Navigation

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

Spreadsheet Management

 Save • Manual • AutoSave  Print

Practice Review

Making Spreadsheets More Effective

Project Planning

 What is your purpose?

 What results do you desire?

 Determine • • data available calculations needed  Design • • spreadsheet layout data entry

Customizing the Spreadsheet

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

Mathematical/Financial Operators

 +   /  *  %  ^  &  =  <>  >  <  >=  <=

Order Of Operations

 ( )  %  ^  * /  +  &  = <> > < >= <=

AutoFill



Fill

Handle  Drag-and-drop data/formula entry  Custom Lists

Ranges of Cells

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

Other Useful Tools

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

Predefined Functions and the

S

Key

 Now( )   Today( ) Sum( ) and S  Average( )  Count( )  CountA( )  Max( )  Min( )  Pmt( )

Practice Exercises 1 and 2A

Changing Attributes For Effective Presentations

Font Changes

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

Aligning Cell Contents

 Centering Across Columns  Center and Merge Cells

Inserting and deleting

 Rows  Columns

Adding Cell Enhancements

 Lines  Borders  Backgrounds  Patterns

Autoformat

 Replicate cell formatting  Uses Format Painter tool

Printing Enhancement

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

Scaling Printouts

 Printing on one page

Practice Exercise 2B

Creating Charts & Graphs

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

Graphic Enhancement

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

Printing Graphs

 Allows display of financial information for better understanding/comprehension.

 Graphs Only  Graphs With Spreadsheet

Saving Graphs

 Saved automatically when worksheet saved

Practice Exercises 2C and 3

DATABASE CAPABILITIES OF EXCEL

Lists

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

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

Sorting Lists

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

Simple Autofiltering

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

Practice Exercise 4

EXTENDING DATABASE CAPABILITIES

Criteria Ranges

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

Advanced Filtering

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

Database Functions

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

Common Database Functions:

• • • DSUM DAVERAGE DMAX • • • DMIN DCOUNT DCOUNTA

Practice Exercise 5

THREE-DIMENSIONAL WORKBOOKS

Multisheet Workbooks

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

Three-Dimensional Cell References

 Add worksheet reference to cell reference  Possible formats: • WorksheetName!CR

• • WorksheetName!CR:CR Worksheet1:Worksheet2!CR

 Worksheet references are always absolute

SIMPLE MACROS

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

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

Editing Macros

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

WHAT-IF FINANCIAL ANALYSIS

Performing What-If Financial Analysis

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

Scenarios

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

Scenario Manager

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

Practice Exercise 6

The End

Thank you for attending!