Modelling for Debt Restructuring

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