Developing a Revenue Recognition Model in Power Pivot
The Situation
A $300 million optical care company with both retail and medical service revenue had high variability in monthly revenue and was not able to obtain key financial insights from its existing revenue software. The Company’s revenue had over 200 payors across 9 retail hubs with differing contract prices, making it very complex. The revenue close process was manual and took three days to complete. Due to the complexity of the revenue and the manual nature of the process, the Company used a high-level approach that provided little insight into regional, payor, or service line trends. The Company engaged Accordion to help streamline the revenue recognition process while simultaneously providing deeper financial insights.
Services
Visibility
Financial Statement Preparation & Analysis
Account Reconciliations
The Execution
- Developed a revenue recognition model in Microsoft’s Power Pivot that connected to the Company’s revenue data server.
- Coordinated with the Company’s accounting, FP&A, revenue operations, and information technology teams throughout the engagement.
- Analyzed billing and cash data over a two-year period to understand data components, business trends, and the relevant information that would be contemplated in the model.
- Determined the required outputs for both the financial statement close and monthly reporting processes and tagged each for inclusion in the model.
- Developed a custom automated dashboard that showed regional, payor, and product revenue metrics in total and by geographic hub.
- Coordinated and oversaw the development of a new dedicated data reporting server to access the Company’s data in real-time using an SQL-based interface.
- Validated the data to ensure reporting accuracy and completeness.
- Conducted weekly meetings to provide overview of progress, key milestones achieved, and risk areas requiring decision making or attention.
- Trained client team members on how to use the model and how to make updates for changes in the business before ultimately transitioning the model to the client team.
The Results
In close coordination with the Company, Accordion provided a revenue recognition model that utilized Power Pivot to calculate revenue across 9 retail service hubs, 5 product lines, and 5 payor categories in under 30 minutes. As a result, the Company was able to reduce its financial statement close process timeline and gained increased visibility into revenue price trends and gross margins across service lines. The Company was also able to utilize data in the model to further understand certain payor trends and identify a significant under-paying insurer.