nacm excel presentation jon paul president, value added finance resources 847 372-1963...
TRANSCRIPT
NACM Excel Presentation
Jon Paul
President, Value Added Finance Resources
847 372-1963
www.valueaddedfinance.com
www.excel-erate.biz
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
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
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
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
Agenda
• Case History
• Uses of Excel in Credit Management
• Excel Tips
• Resources For Further Study
• Other Questions
Objectives
• See New Ways You Can Use Excel
• Pick Up Pointers on Excel
• Show Ways You Can Learn More
• Answer Questions During and Afterwards
Uses of Excel in Credit Management
• Rolodex
• Financial Analysis
• Database
• Reporting
Rolodex
• Account Lists
• Call Scheduling
• Call Tracking
Financial Analysis
• Bad Debt History
• Aging
• Credit Analysis
• Collection History
Bad Debt History
• Determine Bad Debt Rate
• Key- Matching Proper Periods
• Pull Back Bad Debts to Initial Revenue
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%
Aging Flaws
• Looks Back
• Matches Different Time Periods
• Skewed When Sales Grow or Fall
• Not as Predictive
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%
Alternative- Collection History
• Set Up 30 Day Buckets
• Track Collections During Each Bucket
• Analyze % Collected At Each Stage
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
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
Key to Success- Collection History
• Get Started
• Set Up 30 Day Buckets
• Consistent Cutoffs
• Break Down Into Groups
• Set Targets
• Measure Against Targets
Collection History ReportCollected 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%
Collection History Chart
0.0%
10.0%
20.0%
30.0%
40.0%
50.0%
60.0%
Jan Sales Feb Sales Mar Sales
Current 0-30 31-60 61-90 Over 90
% Available Collected
0.0%
5.0%
10.0%
15.0%
20.0%
25.0%
Current 0-30 31-60 61-90 Over 90
Jan Sales Feb Sales Mar Sales
Database Steps
• Determine Data Availability• Where to Keep Data• Clean Up Data• Perform Calculations• Group Data• Analyze Data• Summarize Results• Automate Process
Data Availability
• What Data Do You Want
• What Data is Available
• How Large is Dataset
• How Many Different Sources
• What Frequency of Data
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
Clean Up Data
• Converting Text to Numbers
• Splitting Data
• Correcting Data
• Filling In Missing Data
Adding Calculated Fields
• Dates- number of days
• Sums- total collected
• Ratios- % of bad debts
Adding Group Fields- Examples
• Date- week month year
• Territory
• Customer Group
• Product Line
• Credit Score
Analyze Data
• Pivot Tables
• Pivot Charts
• Lookups- multiple tables
Summarize Data
• Spreadsheet
• Pivot Table
• Charts
• Pivot Charts
• Dashboards
Excel Skills
• Broken Down Into Areas
• Time Won’t Allow Us to Cover All
• See Appendix for more complete list
• Resource- www.excel-erate.biz• Online Lessons
www.excel-erate.biz/excel/excel_index_alpha.htm
• Use to Test / Enhance Your Skills
• Same For Staff or Potential Hires
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
Backup Copy Dialog Box
Navigation
Go To Special
• Click F5 Key
• Click Special at Bottom of Dialog Box
• Select Where You Want to Go
• Wide Range of Choices
Go To Special Dialog Box
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
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
Manipulation Example
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
Paintbrush Example
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
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
Multiple Charts- Same Sheet
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
Secondary Axis Screen
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
Chart Gap Fix Options
Charts- Special TypesScatter
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
Labels in Formulas- Setup
Labels in Formula- Example
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
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(b3:b20),-3)
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(B2:B6*C2:C6)/B7}
Weighted Average
If Statements- Conditional Formulas
Fundamental IF Statement
• Parts- Condition, Value if true, Value if false
• = if (D5=0,0,b5/c5)
• Can use text- = if (D5>.05,”Review”,””)
• Can use multiple conditions- And, Or– =if(And(D5>.05,C5>1000000),.06,.03)
• Can nest- to test for condition within condition
Error Trapping
ISERROR, ISNA
• Other ways to catch broader range of errors
• ISNA very useful for lookup errors when new values added that don’t match
• Example- =if(ISERROR(b5/c5),0,b5/c5)
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)
Concatenate Example
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
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
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
Named Range Box
Named RangesFormula Example
Lookups- Benefits
• Automatically Pull in Information
• Avoid Manual Re-Entry
• Quality Control
• Faster Formula Creation
• Make Global Changes
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)
Lookup ExampleAdding Product Name
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
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
Data Filter Example
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
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
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
Pivot Table Result
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
Pivot Chart Example
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)}
Data Table- 2 Variable
Macros- VBA- Benefits
• Automate Repetitive Tasks
• Data Cleanup
• Perform More Complex Calculations
• Streamline Navigation, Printing
• Faster Calculations, Setup
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
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
Questions- Contact Us
Jon Paul, PresidentValue Added Finance Resources
www.excel-erate.biz