Modelling for Debt Restructuring

Course Content:

Day One

Case Study Outline
 Analysis framework
 Reasons for financial distress
 Current data
 Case study outline
 Model objectives
 Systematic Design Method
 Basics of spreadsheet design
 Modelling ‘rules’
 Common spreadsheet errors

Exercise: starting a model template

Historic Statements
 Modelling initial accounts
 Income and balance sheet
 Generating cash flow

Exercise: adding and checking accounting statements




Historic Analysis
 Identifying existing debt
 Calculating forward debt repayments and current balance
 Calculating debt and other ratios
 Bankruptcy ratios and scores

Exercise: adding loan sheets and calculating ratios

Forecast Accounts
 Forecast accounts
 Time lines and flags
 Sales growth
 Cost base
 CAPEX requirements
 Working capital – stock, receivables, payables
 Integrity checks
 Circular references

Exercise: completing initial forecast statements

Day Two

New Loans
 Excel functions
 Calculating new loans
 Rate, profile and timing
 Amortisation profiles
 Incorporating scenarios

Exercise: calculating new loans and repayment profiles

Completing Forecast Accounts
 Incorporating new loans
 Balancing the accounts
 Checking on requirements for further capital
 Recalculating forward ratios
 Computing forward default scores
 Integrity checks on results

Exercise: adding loans to forecast statements and checking cash position

Model Stress Testing
 Constructing forward scenarios
 What-if and downside analysis
 Optimising the model results
 Assessing the importance of individual factors

Exercise: layering stress testing on the initial model

Model Completion
 Version control
 Documentation
 Chart output
 Dynamic dashboards
 Management summaries

Exercise: model completion and final checks

.