Insurer Flagstone RE implemented business performance management (BPM) software in the fall of 2007 to gain better control over an exclusively Excel forecasting process. Although the new application looks and feels much like a spreadsheet, its back-end database has dramatically increased the level of detail in Flagstone RE’s forecasts and management reports, while reducing the manual work required in performance management processes.

Shawn Mercer
Director, Planning
Flagstone RE
Industry: Insurance
Annual Revenue: $782 million

SYSTEM SETUP
General Ledger: Microsoft Dynamics
Planning & Forecasting: Quantrix
Financial Reporting: FRx & Quantrix

BPM Magazine: How does budgeting work in your organization?

Shawn Mercer: It’s continually evolving and getting more challenging as our company grows. On a quarterly basis, we update our revenue forecast based on current assumptions provided by various departments within our organization, including premium projections and pricing changes by line of business and segment from our underwriters. In terms of G&A budgeting, department and company heads are responsible for populating G&A budget templates in Quantrix. Our 2009 financial plan was our first budgeting cycle that used these templates to capture department expense budgets by company. We will use a similar process for reforecasting on a quarterly basis going forward. Since we’ve also been active on the M&A front, we will work with those companies to include their budgets in our financial projections.

Planning works with the executive committee to ensure that our corporate forecast is comprehensive and in line with their expectations. Forecasts, including our annual financial plan, are vetted by the CFO and then circulated to our executive committee for review before going to our board.

BPM: Do people send their projections to you in spreadsheets, or do they enter them directly into Quantrix?

Mercer: Expense budget projections are entered directly by the department head into Quantrix-based templates, thus eliminating the need to pull data from Excel spreadsheets. We have used similar templates for other inputs, including premium projections. Any inputs provided in another format, like Excel, are imported by Quantrix’s datalink functionality. 

BPM: Are there benefits to that, beyond the obvious time savings for you?

Mercer: We have saved time, but I also think we have improved accuracy and increased accountability. It has saved us a fair amount of time, as we did not have to consolidate spreadsheets with different layouts and assumptions. We provided the user a template that was locked down, so they cannot change things like formulae, global assumptions, or formatting. We no longer had to worry about other people's potential spreadsheet errors. Also, I expect the department heads to become more accountable. By directly participating in the data collection process, they will “own” their G&A budget template. I think the G&A budget process will be much better, from their perspective and from ours as well.

BPM: So currently, once you get information into Quantrix, how does it get into a budget or plan?

Mercer: We incorporate projected budgets by quarter and company into the pro forma income statement. Each company is within a legal entity category in one matrix, as are calendar year, quarter, and the income statement items. Moreover, each category is a dimension of the matrix, so this simple example has four dimensions.

Compared to a typical two-dimensional spreadsheet, Quantrix allows multiple categories or dimensions (line of business, segment, income statement item, company, quarter, and year) in one matrix. The categories are then linked between matrices, so if you add a company, the change gets incorporated to every matrix. In a spreadsheet environment, you would have had to add the new company to every spreadsheet and update every formula attached to it. This is automatic in Quantrix.

It’s also helpful that the formulae on a matrix are intuitive, so you’re not writing a formula A1+B1=C1, like you would in Excel, but it’s more like Revenues-Expenses=Profit. You write the formula against the categories, rather than the cells.

We forecast our business on a segment/line-of-business level. From there, we roll it up into multiple legal entities that we then consolidate. So, basically, the data comes in, and we layer assumptions on to that data to generate the financial projections — balance sheet, income statement, and cash flow — for each company and our consolidated company.

BPM: So it’s harder to make mistakes.

Mercer: Yes. You can still make mistakes, but the chances are greatly reduced. For example, in our current model, we have 14.8 million cells. Of those, 4.5 million have calculations in them. In Excel, that would be 4.5 million formulae, but in Quantrix we only have 2,040. From an audit or due diligence perspective, it is very apparent that we have reduced our chances of making an error by over 2,200 times.

BPM: So when you need for a cell to make a calculation, you can just reference a formula you’ve already written?

Mercer: It is automatic if the formula already exists. When you highlight a cell it will highlight the formula attached to it, if applicable.

BPM: What kinds of financial analytics do you do?

Mercer: We calculate numerous financial ratios (such as book value per share, return on equity, and earnings per share) and compare them against what the analysts are projecting for us. We pull in analyst estimates and line them up against our projections and estimates. We prepare this on a quarterly basis and fiscal-year basis so we can compare what the analysts are projecting for us and what we’re projecting.

BPM: Do you have a process for holding managers accountable for the projections and assumptions they provide?

Mercer: We compile new projections each quarter and provide them to the board, so the board sees our prior quarter’s forecast and our current reforecast. Then we explain what the differences are and where and why we’re making changes. We update our forecast as applicable based on the variance between actuals and the forecast. Plus, department owners have to explain variances on a quarterly basis. We are currently moving toward dispersing the variance analyses on a monthly basis.

BPM: Is it pretty obvious, generally, where the variance stems from, or do you have a process for looking into that?

Mercer: Some items might be pretty obvious, but for other items, a lot goes into generating the forecast. We are able to drill down to the granular level — line of business level — in Quantrix to see where the variance arose. We pull in actual results via datalink to line up against our financial projections. For the G&A items, department owners will provide the explanation, but we will provide quarterly variance reports to facilitate the process. Again, we can drill down right to the account level to examine variances.

BPM: How has your analysis of financial data changed since you implemented software built specifically for performance management BPM?

Mercer: I think it’s much tighter now. There’s a lot less manual intervention. Even just bringing in historicals, the only way we could have gotten that in Excel would have been manual input for the most part. Incorporating various end users’ requirements for various companies within the organization would have been risky to do in Excel. Excel was fine when we were just looking at a simple, consolidated company, but having this tool has allowed us to become more sophisticated and accommodate various reporting requirements in a controlled environment. Actually, it has probably increased requests for financial projections. It definitely allows us to drill down to a more granular level.

The fact that Quantrix is multidimensional, and not a two-dimensional, spreadsheet enables us to be more sophisticated in how we model assumptions on various lines of business. When you consider we had multiple lines of business and multiple segments, you end up with many variables for each assumption. In Excel, it was very difficult to model certain things on a line-of-business level without employing Visual Basic. Instead, sometimes we had to take the line-of-business total and layer assumptions on it. Now we’re able to assign a new assumption to a specific line of business within a specific segment, which is very simple compared to writing new Visual Basic code. 

One other change is that there’s so much data in one Quantrix matrix because of the multiple dimensions. Each matrix is an über Pivot Table, in that you can filter and produce information for users very quickly. I find that in Excel you would have to manipulate and consolidate different sheets to get exactly what someone’s looking for. Having a more sophisticated BPM solution allows for superior analysis and dissemination of data and results. It’s been invaluable for board reporting and for turning around executive requests in a prompt, timely fashion.