Tricky Excel Modeling Issues

This session deals with advanced modeling topics in valuation and deal structuring. Participants complete and add to existing models using a variety of small case studies that highlight key financial modeling issues.

Share buy backs

• Making assumptions about how much cash the company has to buy back shares
• Forecasting the company value in the future (either equity or aggregate values)
• Calculating the number of shares the company can buy back
• Reworking earnings per share

Tax losses

• Modeling tax loss carry forwards and carry backs using the cash taxes method
• Modeling tax loss carry forwards and carry backs using the deferred tax asset method
• Making the model flexible

PIK interest

• Understanding the impact of PIK interest on the three financial statements
• Integrating PIK interest into the model

Debt repayment accelerators (cash sweeps)

• Understand how senior debt loan documentation affects how to model the repayments
• Simple cash sweeps using the MIN, MAX and IF functions
• Documented repayments, plus cash sweep mechanisms
• Pro-rata cash sweep mechanisms

Mid-year convention and complex discounting models

• Two different ways of putting mid-year convention into DCF models
• Using the XNPV and EDATE functions
• Using date math to discount to a deal date
• Using the DATE, YEAR, MONTH and DAY functions to automate the date modeling
• Macros for models

M&A models and related complexities

• Modeling stub years and forecasting the completion balance sheet
• M&A deals where the target is in another currency, has a different year end and the deal date is mid year
• Asset step ups and the related deferred tax liability
• Debt issuance fees and the related amortization

Other modeling issues

• Conditional formatting to highlight assumptions being used
• Data validation for switches and scenarios