Preparing for an IPO by Establishing Innovative, Automated Forecasting & BI Infrastructure
The Situation
A leading automobile services company needed short- and long-range forecasting capabilities. The management team and sponsor engaged Accordion to build a monthly three-statement projection model based on the roll-up of sales forecasts for 1,500+ stores. This bottom-up forecast needed to be paired with a top-down macro model that would validate the Company’s long-range plan. Lastly, in order to analyze performance trends and quickly understand the root cause of variance to forecast, our team created a new BI tool.
Services
Budgeting & Forecasting Process Improvement
Stakeholder Reporting & KPI Development
IPO Readiness
Big Data Analytics
The Execution
- Worked closely with the Company’s FP&A team to consider macro and micro business drivers, as well as inorganic expansion plans, to identify key assumptions for the Company’s long-range financial model.
- Coded a model script to build store-level projections based on the historical trends of the store and its region.
- Paired the model script with a PowerBI interface to create a scalable, repeatable projections model that was back-tested across multiple historical time periods.
- Linked the automated store-level forecast into Excel at a summary level, which served as the basis for a traditional three-statement projections model in Excel.
- Built additional PowerBI visuals that combined the forecast model with the actual results residing in the Company’s finance cube in order to understand variance to forecast.
- Developed a top-down macro projections model utilizing industry research and macro views – serving as independent validation of the bottom-up forecast and providing an additional tool for management to communicate the growth opportunities and macro perspectives of the Company.
The Results
Accordion delivered a projections model that covered near-term monthly and quarterly periods, as well as longer term projections – which were all based on a granular, monthly by-store build. The new BI performance enabled the finance team to quickly understand variance to forecast and immediately analyze performance by automatically manipulating data. These tools provided visibility into financial performance and key metrics, such as same store sales growth (SSSG). The financial model automatically updated an SSSG forecast, providing near-term and long-term estimates of SSSG, which was a critical metric as the Company prepared for their IPO. As a result of the engagement, the Company now has a data-driven forecasting platform to support the next phase of the its growth. The tools our team implemented offered a variety of views to communicate the Company’s expansion plans and the ability to understand the underlying drivers of financial performance.