Trusted By:
0 Part Course  | 
Book places now

Cash Sweep Modelling and Error Checking Techniques

Master the modelling of integrated financial statements

Cash Sweep Modelling and Error Checking Techniques Course

A one-day course

  • This course is hands-on with practical examples and illustrations in excel exercises
  • The trainer has qualified as a Chartered Accountant and 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 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 covenant thresholds which can make cash flow allocations conditional on financial covenants
  • Learn the most commonly used 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 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
    • 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 formulas with ease: CTRL [
  • Using Excel to show the formulas underlying 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

Case studies – Participants will be given a series of models containing errors within them. Using the checking techniques, they will identify and resolve the errors contained within these models and thereby reinforce their learning of error checking techniques

After graduating from Cambridge University in 1993, the trainer worked as an auditor with Arthur Andersen, gaining a comprehensive knowledge of corporate accounting and financial statements. He qualified as a chartered accountant (ACA) and then worked in investment banking in the City of London, initially with HSBC and subsequently at Merrill Lynch. Throughout his time in the City he was in a front-office role, advising companies on valuation, financing, acquisitions, foreign exchange and derivatives. This breadth of practical experience together with his passion for sharing and communicating knowledge, naturally then led him towards a career in financial training.

He has worked in financial training for the last ten years and delivered training courses on accounting, financial modelling, corporate finance, valuation and capital markets at investment banks, private equity and corporate finance firms throughout the world.

This one-day workshop 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.

Have this course presented In-House

  • On a date, time and in a location of your choice
  • Topics expanded or deleted to your bespoke requirements
CLICK HERE TO REQUEST A FEE QUOTE

Have this course pre-recorded

  • Full course recording edited exclusively for your company
  • Files converted to enable housing on your LMS
CLICK HERE TO REQUEST A FEE QUOTE

We use cookies

In order to show you courses tailored to your profession we use cookies.

To enjoy all the features of this website please accept.