The client, a Private Equity firm asked the TresVista team to analyze the generator model, a budget model, and an investment growth model of a pet company for anomalies. They also requested to run an LBO transaction and calculate returns analysis separately for 2 rounds of fundraising (from here on 1st closers and 2nd closers). A quick turnaround was requested for this task as the client wanted to reach out to investors and close the 1st round of fundraising by November. The team also changed location financials and opening store dates for 2nd closers which essentially meant revamping the model.
To analyze the models provided for anomalies, run an LBO transaction, and calculate returns analysis for 2 rounds of fundraising.
The TresVista Team followed the following process:
• Performed a quick analysis of the models (there were a lot of errors) and as per the client’s suggestion the team created one consolidated LBO model
◦ Started with building individual tabs for 9 locations and a rollup tab for 31 generic locations (each location/store opening at a different period in time)
◦ Post that, prepared consolidated 3-statements and ran an LBO for the transaction
◦ This was followed by building Proforma Annual Financials at close for the funded entity
◦ Once the proforma tab was built, the new forecasts numbers were taken into account and created returns analysis along with sensitivity analysis
• The final deliverable included Proforma Financials, Returns Analysis, Forward Economics, Yearly-Location wise Returns and Location Financials
The major hurdles faced by the TresVista team were:
• Consolidating Generic Location financials and complexity of formulas
• Multiple revisions – Tailoring the model as per different set of investors
• Tight deadline with managing multiple deliverables from the CEO
The team overcame these hurdles by following a cohort analysis and ladder approach for the generic location financials, Further, the team also set up bi-weekly calls with the client to share the progress update and note down the revised inputs.
The TresVista team built a comparative analysis for multiples cases running in the model which helped in comparing returns at different variables and created a Dollar-on-Dollar return analysis at different levels of Rev and EBITDA. The team also updated and organized the IM for the investor presentation.
