Cash Sweep Modelling and Error Checking Techniques

• This Cash Sweep Modelling course is hands-on with practical examples and illustrations in excel exercises
• The trainer has qualified as a Chartered Accountant and is well qualified to understand both the accounting and financial aspects of the workshop
• He has spent time working on building cash sweep models and teaching error checking techniques to finance professionals

• Learn what is a cash sweep and how to build cash sweeps within a 3-statement financial forecast, and allocate cash flow across different debt and equity instruments, with mandatory and accelerated repayments.
• Learn how to build in cash sweep covenant thresholds which can make cash flow allocations conditional on financial covenants
• Learn the most commonly used techniques in cash sweep mechanism such as: sense check, formula checking and stress testing techniques
• Learn how to identify and fix unintended circular references within a model

Modelling a Cash Sweep

• Building a detailed cash sweep debt schedule for a leveraged company with multiple debt instruments
  • Cash Flow available for debt service vs. cash flow available for debt repayment
  • The waterfall of debt repayments from mandatory to discretionary and senior to subordinated
  • Modelling an assumption for minimum ‘operating’ cash
  • Modelling the revolver to absorb any shortfall after mandatory repayments
  • Modelling the mandated debt repayments
  • Modelling the accelerated/ discretionary debt repayments
  • Modelling mezzanine debt as per cash sweep clause
  • Modelling shareholder loan and dividend cash flows
  • Incorporating covenant thresholds to allocate cash between equity vs. discretionary debt repayments

Case study – Participants will build a detailed debt schedule for a leveraged finance model with multiple debt and equity instruments.

Troubleshooting and error checking

• Understanding the three pillars of error checking
  • Sanity checking: sense checking the ratios and checking for linear trends
  • Matrix integrity checking for column consistent and rogue formulae or constants
  • Stress testing assumptions to test robustness and predictability of results
• Using Excel tools to help with integrity checking: F5 GoTo Special
• Finding unidentified hard numbers quickly and easily
• Using Excel to trace through cash sweep formulas with ease: CTRL [
• Using Excel to display the formulas that underpin the output: CTRL ‘
• Using Excel’s auditing tool to trace formulas
• Dealing with circular references
  • Finding unintentional circularities: disable iterative calculations
  • Protecting the model from ‘blow-ups’ using ‘circularity’ switches: IFERROR vs. Toggle switch
  • Using Excel’s circular reference toolbar to find and resolve circular references

This one-day Cash Sweep Modelling course will guide participants through best practice techniques for modelling a cash sweep and error-checking techniques.

We set up a hierarchy of a series of debt items and we learn how to model the sequential debt paydown using a ‘cash sweep’ approach. We learn to incorporate both mandated and accelerated debt repayments as well as allocate cash to equity investors subject to covenant thresholds. Best practice modelling techniques are emphasized throughout.

The afternoon session focuses on building models without making errors, and on useful tips and tricks for finding and fixing errors quickly. We start by laying out 10 integrity modelling rules, which teach participants how to minimize the risk of creating errors in models. We then proceed to examine techniques to correct a non-balancing balance sheet. The last part of the session is dedicated to dealing with circular formulas and related problems. We use a series of financial models to allow participants to practice applying the error-checking techniques for themselves.

This one-day Cash Sweep Modelling course will guide participants through best practice techniques in modelling a cash sweep and error checking techniques.

We set up a hierarchy of a series of debt items and we learn how to model the sequential debt paydown using a ‘cash sweep’ approach. We learn to incorporate both mandated and accelerated debt repayments as well as allocating cash to equity investors subject to covenant thresholds. Best practice modelling techniques are emphasized throughout.

The afternoon session focuses on building models without making errors, and on useful tips and tricks for finding and fixing errors quickly. We start by laying out 10 integrity modelling rules, which teach participants how to minimize the risk of creating errors in models. We then proceed to examine techniques to correct a non-balancing balance sheet. The last part of the session is dedicated to dealing with circular formulae and related problems. We use a series of financial models to allow participants to practice applying the error-checking techniques for themselves.