Close
Let's Talk
Close

Let's Talk.

Looking for our offices? View locations.

Case Study

Developing a Revenue Recognition Model in Power Pivot

Industry
Team Size
1 Managing Director, 2 Vice Presidents
Project Duration
16 weeks

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.