1 / 81

NACM Excel Presentation

NACM Excel Presentation. Jon Paul President, Value Added Finance Resources 847 372-1963 jon@valueaddedfinance.com www.valueaddedfinance.com www.excel-erate.biz. Case History. Telecom Client Aggressive Launch Explosive Sales Growth- $50 Million Rate Realized Bad Debt Problem

lorenzo
Download Presentation

NACM Excel Presentation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NACM Excel Presentation Jon Paul President, Value Added Finance Resources 847 372-1963 jon@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

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

  14. AR Aging- Growth Can Skew

  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

  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 Won’t Allow Us to Cover All • See Appendix for more complete list • Resource- www.excel-erate.biz • Online Lessonswww.excel-erate.biz/excel/excel_index_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

More Related