NACM Excel Presentation

1
NACM Excel Presentation

• Jon Paul
• President, Value Added Finance Resources
• 847 372-1963
• jon_at_valueaddedfinance.com
• www.valueaddedfinance.com
• www.excel-erate.biz

2
Case History

• Telecom Client
• Aggressive Launch
• Explosive Sales Growth- 50 Million Rate
• Realized Bad Debt Problem
• Running 15 of Sales
• Turned Out- Really Running 55 of Sales

3
Case History- End Result

• Within One Year Down to 5
• Company Became Profitable
• Stock Climbed from Low of 1.50 to over 60
• Sold to Larger Competitor
• Employees Did Very Well In 401k Accounts

4
Case History- Action Items

• Cut Off Non Paying Accounts
• Set Up Prepaid Program For Poorest Credits
• Added Credit Scoring
• Tightened Credit Policies
• Stronger Collection Efforts

5
Case History- Role of Excel

• Identify Higher Amount of Bad Debts
• Capture and Monitor Collection History
• Identify Soft Spots in Collections
• Predict Future Collections
• Analyze Credit Scoring
• Communicate and Celebrate Success

6
Agenda

• Case History
• Uses of Excel in Credit Management
• Excel Tips
• Resources For Further Study
• Other Questions

7
Objectives

• See New Ways You Can Use Excel
• Pick Up Pointers on Excel
• Show Ways You Can Learn More
• Answer Questions During and Afterwards

8
Uses of Excel in Credit Management

• Rolodex
• Financial Analysis
• Database
• Reporting

9
Rolodex

• Account Lists
• Call Scheduling
• Call Tracking

10
Financial Analysis

• Bad Debt History
• Aging
• Credit Analysis
• Collection History

11
Bad Debt History

• Determine Bad Debt Rate
• Key- Matching Proper Periods
• Pull Back Bad Debts to Initial Revenue

12
Bad Debt History Example
Jan Feb Mar Apr May Jun Total
Before Matching
Sales 500 1,200 2,000 3,000 4,200 5,500 16,400
Bad Debts 20 60 245 378 615 825 2,143
Bad Debt 4.0 5.0 12.3 12.6 14.6 15.0 13.1
After Matching
Sales 500 1,200 2,000 3,000 4,200 5,500 16,400
Bad Debts 230 615 1,095 203 2,143
Bad Debt 46.0 51.3 54.8 6.8 0.0 0.0 13.1
Bad Debts Proj 230 615 1,095 1,650 2,310 3,025 8,925
Bad Debts Proj 46.0 51.3 54.8 55.0 55.0 55.0 54.4
13
Aging Flaws

• Looks Back
• Matches Different Time Periods
• Skewed When Sales Grow or Fall
• Not as Predictive

14
AR Aging- Growth Can Skew
AR Aging Current 0-30 31-60 61-90 Over 90 Total
Jan 395 190 121 73 69 847
Feb 960 316 161 109 138 1,684
Mar 1,640 797 269 145 241 3,092

AR Aging Current 0-30 31-60 61-90 Over 90 Total
Jan 46.6 22.4 14.3 8.6 8.1 100.0
Feb 57.0 18.8 9.6 6.5 8.2 100.0
Mar 53.0 25.8 8.7 4.7 7.8 100.0
15
Alternative- Collection History

• Set Up 30 Day Buckets
• Track Collections During Each Bucket
• Analyze Collected At Each Stage

16
Benefits- Collection History Approach

• Know What You Collect In Each Bucket
• Predict Future Bad Debts
• Identify Soft Spots In Efforts
• Red Flag Downtrends Early
• Set Targets For Improvement

17
Finding Soft Spots- Football Analogy

• 0-30 Days- First Down
• 31-60 Days- Second Down
• 61-90 Days- Third Down
• Over 90- Goal Line Stand
• What is Relatively Soft in Collections
• Almost Every Company Has a Soft Spot

18
Key to Success- Collection History

• Get Started
• Set Up 30 Day Buckets
• Consistent Cutoffs
• Break Down Into Groups
• Set Targets
• Measure Against Targets

19
Collection History Report
Collected Current 0-30 31-60 61-90 Over 90 Collected Bad Debts
Jan Sales 105 79 47 27 12 270 230
Feb Sales 240 163 104 55 22 585 615
Mar Sales 360 256 166 91 32 905 1,095

Collect Current 0-30 31-60 61-90 Over 90 Collected Bad Debts
Jan Sales 21.0 15.8 9.5 5.4 2.4 54.1 46.0
Feb Sales 20.0 13.6 8.6 4.6 1.9 48.7 51.3
Mar Sales 18.0 12.8 8.3 4.6 1.6 45.2 54.8

Avail Current 0-30 31-60 61-90 Over 90 Collected Bad Debts
Jan Sales 21.0 20.0 15.0 10.0 5.0 54.1 46.0
Feb Sales 20.0 17.0 13.0 8.0 3.5 48.7 51.3
Mar Sales 18.0 15.6 12.0 7.5 2.8 45.2 54.8
20
Collection History Chart
21
Available Collected
22
Database Steps

• Determine Data Availability
• Where to Keep Data
• Clean Up Data
• Perform Calculations
• Group Data
• Analyze Data
• Summarize Results
• Automate Process

23
Data Availability

• What Data Do You Want
• What Data is Available
• How Large is Dataset
• How Many Different Sources
• What Frequency of Data

24
Where to Keep Data

• Number of Records
• Excel Now- 65,536 row limit
• Excel 2007- 1,000,000 rows
• Alternatives
• Excel
• Database- Tie With Excel
• Summary Information- External Tool- OLAP

25
Clean Up Data

• Converting Text to Numbers
• Splitting Data
• Correcting Data
• Filling In Missing Data

26
Adding Calculated Fields

• Dates- number of days
• Sums- total collected
• Ratios- of bad debts

27
Adding Group Fields- Examples

• Date- week month year
• Territory
• Customer Group
• Product Line
• Credit Score

28
Analyze Data

• Pivot Tables
• Pivot Charts
• Lookups- multiple tables

29
Summarize Data

• Spreadsheet
• Pivot Table
• Charts
• Pivot Charts
• Dashboards

30
Excel Skills

• Broken Down Into Areas
• Time Wont Allow Us to Cover All
• See Appendix for more complete list
• Resource- www.excel-erate.biz
• Online Lessonswww.excel-erate.biz/excel/excel_ind
  ex_alpha.htm
• Use to Test / Enhance Your Skills
• Same For Staff or Potential Hires

31
General

• Saving Backup Copy of File
• File Menu
• Save As
• Click on Tools
• Select General Options
• Check Always Save Backup
• Can Use Same Process to Password Protect

32
Backup Copy Dialog Box
33
Navigation

• Go To Special
• Click F5 Key
• Click Special at Bottom of Dialog Box
• Select Where You Want to Go
• Wide Range of Choices

34
Go To Special Dialog Box
35
Cut and Paste

• Two Not Well Known Shortcuts
• Ctrl D copy down
• Copies row above down to next row
• Ctrl R copy right
• Copies column at left to next column

36
Manipulation

• Paste Special Values
• Suppose You Had to Increase Forecast 5
• Put in 1.05 in blank cell and copy
• Select cells with forecast to be increased
• Select paste special values
• Select values and multiply

37
Manipulation Example
38
Formatting

• Paintbrush
• Handy Formatting Button in System Toolbar
• Use to Copy Format to Another Section
• Highlight Format You Like
• Click or Double-Click Paintbrush
• Move Paintbrush Over Sections to Reformat

39
Paintbrush Example
40
Printing

• Multiple Page Setup
• Use to Set Up Print Format of Multiple Sheets
• Or Set Up Print Format of New Sheet
• Select Sheet With Print Format You Like
• Select Remainder of Sheets (Shift or Ctrl Key)
• Select File, then Page Setup
• Click OK

41
Charts- Views

• Same Sheet- Multiple Charts
• Create new blank chart sheet- click on a blank
  cell and click F11 to create new chart
• Create each chart locate in this new blank
  chart sheet
• Go to the chart sheet - click Size with Window
• Adjust the size and scaling of the individual
  charts as needed

42
Multiple Charts- Same Sheet
43
Charts- Formatting

• Secondary Axis
• Add Data For Secondary Axis
• Multiply If Needed to Show- Such as
• Chart- Click on Data- Format Data Series
• Click on Axis- Change to Secondary
• Change Chart Type to Line
• Reformat Line
• Shrink Number Back to Original Size if Needed

44
Secondary Axis Screen
45
Charts- Formulas

• Chart Gaps- When Gap In Data
• Tools menu
• Select Options
• Click Chart Tab
• Make Selection
• Not plotted- leave gap
• Zero
• Interpolate- fill in gap

46
Chart Gap Fix Options
47
Charts- Special Types

• Scatter

48
Formulas- General Techniques

• Labels in Formulas
• Create Formulas Based on Row or Column Headings
• Setup- Go to Tools, Options, Calculation tab
• Select Accept Labels in Formulas
• Can Create Faster Formulas
• Intuitive for User to Follow

49
Labels in Formulas- Setup
50
Labels in Formula- Example
51
Sums

• Sum non-adjacent cells
• Fast way to create these
• Begin formula- Sum(
• Click on first cell
• Hold down shift key
• Click on remainder of cells

52
Rounding

• Round Function
• Use to round to certain number of decimals
• First part- calculation being performed
• Second part- number of decimals
• Can do in reverse- such as -3 for thousands
• Example Round(sum(b3b20),-3)

53
Averages

• Weighted Average
• Fast Way to Calculate
• Start a sum formula
• Multiply two parts
• Finish the sum
• Add the denominator
• Finish- Ctrl Shift Enter key
• SUM(B2B6C2C6)/B7

54
Weighted Average
55
If Statements- Conditional Formulas

• Fundamental IF Statement
• Parts- Condition, Value if true, Value if false
• if (D50,0,b5/c5)
• Can use text- if (D5gt.05,Review,)
• Can use multiple conditions- And, Or
• if(And(D5gt.05,C5gt1000000),.06,.03)
• Can nest- to test for condition within condition

56
Error Trapping

• ISERROR, ISNA
• Other ways to catch broader range of errors
• ISNA very useful for lookup errors when new
  values added that dont match
• Example- if(ISERROR(b5/c5),0,b5/c5)

57
Text

• Concatenate
• Use to combine text into one cell
• Concatenate(text1 or cell1, text2 or cell2,
  etc.) 
• Can use both text and cell references
• CONCATENATE(A2," ",B2," ",C2)

58
Concatenate Example
59
Date

• Use to set up date fields in lists
• Year- year(C2)
• Month- month(C2)
• Day of month- day(C2)
• Weekday- weekday(C2,1)
• Second variable is type
• Can choose if week starts with Sunday or Monday

60
Named Ranges- Benefits

• More intuitive formulas
• Faster formula creation
• Easier to link between sheets and files
• Less likely to break links
• Use to navigate
• Faster consolidation

61
Named Ranges- Skills

• Manual Creation
• Highlight row, column or range
• Add in name in name box
• Pasting in Formulas
• Start formula- such as or
• Click F3 to pull up paste name box- select name
• Manually Adding in Formulas
• Type in name in formula- such as
  sales-cost_sales
• Navigation by Names
• Click F5 Go To dialog box- select name

62
Named Range Box
63
Named RangesFormula Example
64
Lookups- Benefits

• Automatically Pull in Information
• Avoid Manual Re-Entry
• Quality Control
• Faster Formula Creation
• Make Global Changes

65
Lookups

• Basic Lookup formula
• Lookup value- value you are trying to find
• Such as January
• Lookup range- where you are trying to match
• Such as sales data
• Column- what column has the value you want
• Such as the sales for the lookup value
• Type of match- close or exact (false means exact)
• VLOOKUP(F2,sales,2,FALSE)

66
Lookup ExampleAdding Product Name
67
Data Lists- Setting Up

• Continuous Data- No Blank Columns or Rows
• Add Index Field If Desired
• Column Headers- Unique
• Column Headers- One Row
• Fill In Blank Cells as Needed
• Add In Calculated Fields
• Set Up Named Range

68
Data Lists- Viewing

• Basic Auto Filter
• Data menu, filter, then auto filter
• Drop down arrows will be put at top of list
• Can use to select parts of list
• Can select on values in list, top 10 or custom
• Can filter on multiple fields
• Select all to return back to viewing entire list

69
Data Filter Example
70
Pivot Tables- Benefits

• Query data without a database
• Summarize results from large datasets
• Quickly build reports
• Change reports by pivoting fields
• Change views using drop-down lists
• Drill down to details

71
Pivot Table Fundamentals

• Pivot Table Setup
• Data menu, then Pivot Table
• Select type of data, then data range
• Layout screen
• Drag in rows and column fields for report
• Drag in data fields
• Drag in page fields if desired
• Finalize- select location for table

72
Pivot Table Fundamentals II

• Pivoting Fields
• Drag to row, column or page headers
• Narrowing selections
• Using drop-down boxes
• Group selections
• Primarily dates
• Drill Down to Detail
• Double click on data cell in pivot table

73
Pivot Table Result
74
Pivot Charts

• Creating Charts
• Create automatically from pivot table
• Pivoting
• Drag like you do with the pivot table
• Note that chart change will affect table
• Drop Down Lists
• Similar to pivot table

75
Pivot Chart Example
76
Simulations

• Data Tables- 2 Variables
• One calculation based on two variables
• One variable across, one variable down
• Sample calculation in upper left of table
• Sample values above the table
• Close with Ctrl Shift Enter
• TABLE(B5,B4)

77
Data Table- 2 Variable
78
Macros- VBA- Benefits

• Automate Repetitive Tasks
• Data Cleanup
• Perform More Complex Calculations
• Streamline Navigation, Printing
• Faster Calculations, Setup

79
Macros- VBA- Basics

• Syntax- Name Object, then Action
• If someone threw ball to you- Ball, Throw
• Creating- Macro Recorder
• Simplest way to start
• Example- record print settings
• Linking Macros- Menu or Buttons

80
Other Resources

• Excel-erate- www.excel-erate.biz
• John Walkenbach books
• Bill Jelen- Mr Excel- www.MrExcel.com
• Excel User newsletter- www.ExcelUser.com/Biz/
• Inside Microsoft.com- www.elijournals.com

81
Questions- Contact Us

• Jon Paul, President
• Value Added Finance Resources
• 847 372-1963
• jon_at_valueaddedfinance.com
• www.valueaddedfinance.com
• www.excel-erate.biz