Building models to conduct seamless internal management reporting is a common use case for Prophix customers. At UPMC Insurance Services, Jason Schoming, Manager of Finance, has decided to also use Prophix for external reporting purposes. He created an industry benchmark report that standardizes company measures. We spoke with Jason to find out more about the organization’s uses of his benchmark cube.
On a more personal note, this is a special interview for me as working with Jason/UPMC was one of my first projects, 3 years ago!
What business need had you identified that the Administrative Benchmarking Cube would help to address?
The benchmarking survey is an external, annual survey that requires us to re-distribute our financials in a way that is consistent with the survey guidelines. Since our Account/Department structure did not line up exactly with the benchmark reporting lines, we had to move costs around to fit within the required structure. Up until 2018, this process was done in Excel. I’m sure many Prophix customers can imagine how confusing and time consuming that was. The first year I took over the survey preparation (2017), it took me weeks to reconcile my data back to the general ledger. I needed a better way to complete the process that would improve accuracy and speed.
I recall seeing those massive Excel reports during our on-site visit. I don’t know how you managed! So why did you choose Prophix for this?
We utilize Prophix for most of our allocation and reporting needs, so it seemed like a natural fit. I was already familiar with how to build allocation processes, and figured all we needed was a new cube with our existing Financial Cube structure, but with an additional dimension to meet the needs.
How did you build this model?
We built a new cube that replicated the Financial Cube structure and added a dimension called “Reporting Line.” We then mapped all of our Departments (aka Cost Centers) to a Reporting Line. Some Cost Centers lined up perfectly. There was a Member Services Reporting Line, and we have a Member Services department. Easy! Other Cost Centers were not so simple, so we classified those into a single Allocation placeholder member, in the Reporting Line dimension. Then, we reached out to the operational leaders in each area to get statistics (typically % of effort or % of FTEs) that we used as drivers to allocate the costs to the different reporting lines. 5 imports, 1 procedural calculation, and 53 Infoflexes later, we had our clean, organized data ready to export into the survey submission document.
Can you tell us about some of the feedback you have received (internal at UPMC or external)?
The internal feedback was extremely positive. Executive leadership had much more confidence in the numbers that we were reporting in the survey, and therefore became much more likely to utilize the results. My immediate team recognized the benefits too, as I was no longer chained to my desk for 3-4 weeks every spring. (I have since moved on from the role, and the person taking over had a much easier time transitioning the work due to this model.) Externally, it received praise as well. I’ve given multiple presentations of the model at the annual benchmarking survey conference. One company was so impressed that they requested follow up information on Prophix to see if Prophix could help them achieve something similar. I was able to refer them to Prophix and they are now a customer!
What are you working on now (with Prophix) at UPMC?
In my new role, we’ve built our full-year projection model in Prophix. This allows us to update information monthly and easily comment on variances from previous projections. We have also used Prophix to create a model that stratifies our revenue and expenses down to a customer level, so that we can present income statements on a client basis. Currently we are working with Rob Shillinger and others to export our data from the Customer Profitability Model into Power BI, so we can reach a wider internal audience with our dashboards. The hope is that soon we’ll have a dedicated link to Power BI that will make updating these reports instantaneous. In the near future, we also hope to utilize DPM Scenarios for different revenue streams and vendor relationships to facilitate even better forecasting.
Thanks for sharing, Jason! If I’m ever back in Pittsburgh, we’ll have to visit Bakersfield Tacos!
Have a question or comment for Jason about benchmark reporting? Stop by and say hello!