BONUS SCHEMES & BUDGET TRACKERS AND POWER BI
Use Power BI to automate the calculation and distribution of reports based on complex formulas. Traditionally professional services firms have relied on excel to perform this function. Power BI allows you to combine the flexibility of excel with a more robust security and distribution model. It can also save a lot of time.
Let’s take the example of bonus structures and budget tracking.
I’ve worked with a number of firms who have these structures in place. They can get very complex. Different fee earners can have different models with many moving parts.
Let’s work through an example where:
Milla, a junior lawyer, earns 15% of the fees she bills over 5% over her budget on the proviso that the firm makes budget. There is a cap on what she can earn as a bonus of $5,000.
Meg, another junior lawyer, earns 5% of the fees she bills over 5% over her budget on the proviso that the firm makes budget. There is a cap on what she can earn as a bonus of $5,000.
Kelly, an associate who is leading a small team, gets a flat bonus of $5,000 if her whole team makes budget and she herself makes 5% over her own budget.
James, a rain-making partner, gets 10% of the fees collected on the clients he introduces to the firm, invoiced within the year of introduction. Provided the firm makes budget. There is a cap on what he can earn as a bonus of $20,000.
It’s generally not possible make these variable-heavy calculations within a practice management system (and report on it), so an external tool needs to be used.
Normally a spreadsheet which provides these functions:
The calculations and formulas themselves
The application of the formula to financial data which is imported into the report from a practice management system
The checking of the resultant data to see how the bonus figures will impact on cash flow
The distribution of the information to the fee earners impacted.
Ideally at regular intervals so that the fee earners are motivated by the bonus – which is really the entire point of the exercise. In reality, this doesn’t occur because the method of distribution is too complex and time consuming.
Why excel isn’t the best solution
It’s a lot to ask excel to do. While it handles the first function admirably, the others can become clunky.
I’m an Excel fangirl from way back. Pivot tables and vlookups are my happy place. However, even I can see that Excel has its limitations.
Excel is a great tool for individuals. When you use it to share data you run into issues with versioning and control. Not to mention the protection of formulas and simply understanding how the author of the spreadsheet made the calculations. It does not lend itself to a secure distribution model. Getting the data out to fee earners is painful and time consuming.
Another way?
With Power BI, you can import the bonus structure model from excel, where it can be updated with relevant data easily.
Power BI can access the financial information directly from your practice management system and apply it to the structure.
You can use Power BI to deliver the results securely and easily to the people who need to see the data.
Let’s have a look at how this works in practice
An excel spreadsheet can list the calculation logic:
This is imported into Power BI and married with imported data from the practice management system.
The management team might see a dashboard that collates the bonuses, as well as an indication of how the firm is doing collectively to support the pay out of those bonuses. To learn more about the power of dashboards, have a read of The Power of Intelligent Dashboards.
Each fee earner could see a dashboard that indicates how they are tracking against their bonus goals:
Milla can see that she has made her bonus, and in fact reached her cap. But that the firm needs to invoice more collectively in order for her to qualify for that bonus.
Meg can see that she is less than half way to budget.
Other use cases
Some firms use an incentive structure to help teams work together to achieve a collective goal. You could apply a similar approach to the one above to teams.
Incentive structures could extend to non financial contributions, like time devoted to business development or precedent development.
You might have collective firm goals that you could share in a similar way. For instance, how many matters you would like to see opened or how much time you spend on probono work.
It becomes easy to share this on a daily or weekly basis . You might choose to present this information on you intranet, or even internal screens within your firm as a visual prompt.
You can analyse your bonus data over time to understand the incentives that work best to boost profitability.
