How to use Microsoft Power BI for more effective financial reporting

In the modern business environment, data has become the driving force behind strategic decision-making. And yet, the majority of businesses tend to approach data in silos - sales, marketing, finance - each with their respective metrics and reports. 

The challenge is to cross-analyze data from different departments to get a more complete picture of a business. 

Consider the scenario where you’re responsible for creating management reports. In Excel, you would need to spend a significant amount of time manually extracting data from your accounting software, CRM and other information systems, crossing-out tables, creating charts and calculations, and formatting the report for presentation purposes. 

This process can take hours or even days, and any changes to the data (such as new invoices or accounting transactions, new closed won deals) would require you to start over again.

The solution is to integrate these isolated data sources and model into one combined dataset to create a unified, comprehensive view of your business operations. By doing this, you eliminate the barriers that keep your valuable data separated, enabling more sophisticated and cross-functional analysis, saving you hundreds of hours. 

In this space, one tool stands out as an excellent solution due to its modelling capabilities: Microsoft's Power BI. We’re about to explore what makes this tool so valuable, and how you can use it in your own business. 

[For an in-depth understanding of how this tool meets the demands of today's finance professionals, feel free to check out this dedicated whitepaper - 'Power BI for Finance Professionals: Reporting Automation']

Cross-analysis in Power BI

Power BI is more than just a data visualization and automation tool; it's a complete platform that lets you combine different kinds of data to tell a story with analytics. The true strength of Power BI isn't just about solving your finance team's reliance on manual Excel sheets for report building. It has the ability to combine and cross-check data from different parts of your business.

Consider a SaaS, B2B, or Ecommerce business, overwhelmed with a vast range of complex data. This includes customer acquisition costs, churn rates, website traffic and conversion rates from the marketing and sales side; revenue, costs, margins and profitability from the finance side. By cross-analysing this data, Power BI provides a comprehensive picture of the business performance. 

It provides insights into how marketing spend impacts customer acquisition, or how churn rate affects revenue - invaluable knowledge that influences strategic decisions.

Example 1: gross margin

In this example, the gross margin formula can be customized to fit the specific needs of your company, providing more accurate insights into your financial performance. The formula takes into account the sales and cost of sales (CoS) figures to determine the gross margin for your business. 

With a tailored approach, you can sort all your expense details into main cost groups, and then choose which groups are part of your cost of sales. Ultimately you always have up-to-date information on your gross margin right at your fingertips.

As you can see from the illustration above, the modelling exercise involves writing a formula in your Power BI desktop (free to download) and dragging and dropping the result where best fits into your reporting layout.

You can further add new measures adjusting the calculations for the operating margins % and net margins %. Explore the outcome of these additions in our live dynamic Power BI dashboard here.

Now let's examine in detail how one of the most critical metrics in any business or industry that acquires customers or clients can be monitored by leveraging Power BI's modelling flexibility.

Example 2: Customer acquisition cost (CAC)

Customer acquisition cost (CAC) serves as a vital metric, measuring how much money a company spends to acquire new customers. It’s computed by dividing the total expenses associated with sales and marketing (S&M) from a preceding period (usually a month or quarter) by the number of new customers obtained in the current period.

By extracting financial data on S&M expenses and correlating it with the company's CRM pipeline (new customers converted), Power BI can accurately calculate the Customer Acquisition Cost. As you can see it is critical that both systems source data are fed into or connected to your Power BI model.

A straightforward method to put this into action involves using two distinct data sources in our Power BI model. One set of data will come from our accounting system (be it Xero, Microsoft Dynamics, Oracle, etc.), and the other will be drawn from our CRM platform (such as Hubspot, Salesforce, etc.) using the Get Data built-in feature:

Step 1: Once you have the tables connected, start by totalling only the costs associated with Sales and Marketing from your accounting dataset. The following is the measure translated into DAX language that fits Power BI:

S&M_Measure =

CALCULATE(

SUM(Accounting[Value_Column]),

OR(Accounting[Dimension_Category] = "Sales_EXP",

Accounting[Dimension_Category] = "MKT_EXP")

)

Step 2: separate a new measure counting the number of new agreements that your sales team has marked as 'closed won' in the CRM.

NewAcquired_Measure =

CALCULATE(

COUNT(CRM[DealID_Column]),

CRM[Status_Deal] = "Deal_Won_New"

)

Step 3: formulate the CAC measure by dividing the 'S&M_Measure' by 'NewAcquired_Measure'.

CAC =

DIVIDE([S&M_Measure], [NewAcquired_Measure], BLANK())

Now, these newly defined measures can be leveraged in your reports, which lets you assess Customer acquisition cost in relation to both your sales and marketing expenses and the volume of new customers you've gained.

A strategic step from here would be to communicate this new metric internally to your sales team, and compare prior to drafting new annual contract values for customers. The comparison between New Annual Contract Value (ACV) and Customer Acquisition Cost (CAC) is a powerful way to evaluate the efficiency of customer acquisition strategies. This metric is useful in identifying whether the revenue generated from new customers in their first year surpasses the cost it took to acquire them.

To implement this and more, you or your team could enrol in training courses to learn Power BI modelling skills, or you could save considerable time by leveraging the expertise of a Power BI consultant.

More financial metrics in Power BI

Further metrics for businesses focused on customer acquisition which can be easily modelled in your Power BI Dashboard are:

• ARR or MRR total amount of revenue that a company generates from its recurring subscriptions over the course of the period

• Monthly recurring revenue (MRR): Number of active subscribers X average monthly subscription price

• Annual recurring revenue (ARR) = MRR * 12

• Total customers retained: number of active customers at the end of the period - number of churned customers

• Total customers churned: number of active customers at the beginning of the period - number of active customers at the end of the period

• Annual contract value (ACV) = total annual revenue / number of customers

• Net dollar retention: total revenue generated from existing customers) / (total revenue Generated from new and existing customers). This is crucial for businesses because it measures the growth or shrinkage of existing customer revenues, regardless of new acquisitions. This is key to understanding customer value over time, helping businesses to identify successful products or services and prioritize customer retention strategies.

• Gross dollar retention: (total revenue generated from existing customers) / (total revenue generated from all customers). This gauges the revenue retained from existing customers, excluding any upsells or cross-sells, and provides a clear picture of customer churn.

• CAC payback period: (total cost of acquiring new customers) / (average monthly revenue generated from new customers). This tells you how long it takes to recoup the investment when acquiring a new customer.

• Customer lifetime value (LTV): (average customer lifetime) X average monthly revenue generated from each customer

About Farhan

Farhan Qamar is a Certified Chartered Accountant (ACCA) who serves as a Business Finance Partner at Valuefinex, a promising and disruptive outsourced CFO services provider in the UK. With over a decade of experience, he has proven expertise in leading finance departments in both high-growth startups and multinational companies. He has directed finance functions at high-growth, Series A tech startups, and contributed towards financial operations control at Morningstar, a global leader in investment research. 

His most recent achievements include the authorship of a whitepaper on Power BI for finance professionals, leading implementation of systems, and helping companies transition to a subscription-based SaaS from traditional one-off contracts.