How Power BI Helps FP&A Teams Improve Management Reporting

Introduction

In the past, FP&A was synonymous with "spreadsheet gymnastics": a job that required manual data extraction, complex VLOOKUPs and constant fear that a broken cell reference would ruin the Board of Directors presentations.

The emergence of BI tools - particularly Microsoft Power BI - has changed this model. Finance is evolving to become not only the record keeping function of the back office, but also the strategic partner for business operations. The automation of data flows, and the interactive and real-time information provided by Power BI, allow FP&A Teams to transform their focus from merely reporting what happened, to predicting what will happen.

Breaking the "Excel Ceiling"

Ceiling Excel continues to be the dominant tool of finance but has a number of limitations in the new digital age in which we work:

• Data volumes will often exceed millions of rows causing slow performance and/or errors in performance and/or cause slow performance or failure in system access from time to time.
• The nature or function of Excel is static; once a user emails their report, they are often emailing the report as well as their old information about their company rather than sending out updated or timely information.
• The infamous budget_final_v3_actual.xlsx limitation creates not only a single point of failure for financial reporting, but also a lack of true single source of truth in regard to financial reporting.

Power BI is an evolution of the Excel product and is not intended to replace Excel. Rather than replacing Excel, Power BI utilizes the same underlying engines that advanced Excel users know and therefore are familiar with, both of which (Power Query (M) and Power Pivot (DAX)) are available in a cloud-based organization for Power BI users.

Key Capabilities for FP&A

To comprehend how Power BI Provides Assistance it’s critical to explore the actual pillars of the FP&A process.

A. Data Integration (The "Single Source of Truth")

FP&A teams create Reports data by consolidating all their actuals from differing Systems such erp’s (e.g. SAP, Oracle, (www.netsuite.com) CRMs (e.g. Salesforce), HRIS (www.workday.com), Flat Files;

• Power BI contains an Automated way to connect and receive data from the systems above.
• Using Power Query; Finance departments can implement data cleaning and mashing-up processes to automate how their Reports are created moving forward. Once the logic has been established in power query it is like creating a template and can be used for years to come.

B. Dynamic Variance Analysis

The basis of any FP&A department will be the Budget to Actual Report and Variance reporting process. In a traditional process, to identify what caused a variance in a Budget to Actual (Example: Monthly Travel Expense), required multiple Reports to be opened and searched through.

• In Power BI any Variance (Example: Travel Expense) can be clicked on within a Power BI dashboard to provide Managers info about where the variance occurred (Source Transaction, Department, Date).
• FP&A Teams have the ability to create "What If" models directly from within their Budget to Actual Reports. For Example; if Raw

Material Costs were to increase by 5% what impact would that have on our EBITDA?

C. Advanced Visualization for Management

Presenting data in reports for management is about creating an emotionally persuasive narrative through data; therefore, rather than giving executives only a series or wall of data, you should present the information that helps convey how the company did over time. For an example, if your company is showing lower revenue compared to previous years, then a tree diagram (decomposition tree) could show you how many different regions, categories, and salespeople contributed to the overall drop in sales, which would be useful for determining the cause of the drop in revenue.

The use of waterfall charts allows managers to see exactly where their company's profit level went from the previous year to the present year, along with the contributions or "drivers" that increased or decreased profits.

Enhancing Management Reporting

One of the most significant problems in management reporting is the "Reporting Lag", which is the duration of time it takes to complete the financial close and prepare a management report deck (usually at least two weeks). Power BI drastically reduces this timeframe.

Daily Access to Management Information

Using Power BI, management can view their daily Dashboard to proactively manage their business, rather than reactively manage their business after the MBR (Monthly Business Review). For example, if a specific product is underperforming in week one, management has the ability to change marketing expenditure for that product during week two instead of waiting until the monthly MBR to find out about the problem.

Mobility and Collaborative Work Environments

Today's executives are constantly on the go. With Power BI's mobile application, a CFO can now view their organization's liquidity ratios or sales performance from their mobile phones. Additionally, Power BI integrates with Microsoft Teams so that people can have discussions about the financial reports in the same location where they actually work, which promotes a culture of having data-driven conversations.

The Path to Predictive Analytics

The Field of Financial Planning and Analysis, or FP&A, is evolving into the Expanded Financial Planning and Analysis (xP&A) model, which combines both operational and financial data. In accomplishing this, Power BI is facilitating that change with the following capabilities.

1. AI Insights: Through the use of features such as Key Influencers' in Power BI, finance departments can quickly find out what operational variables, such as weather delays in shipping or currency fluctuations, are contributing most significantly to a company's profitability.
2. Forecasting: Power BI includes forecasting models that leverage historical information, in addition to seasonality, to help create a forward-looking indication about business performance.
3. Integration with Power Automate: Using Power BI can allow a finance department to set Data Alerts; for instance, when the Debt to-Equity ratio reaches a specific level, the Treasurer receives an automated notification via email or Slack.

Self-Service Analytics: Democratizing Data

In the past, if a department leader had an operational question regarding how much they have spent or where it is going, that leader had to submit a request to finance that could take days to be fulfilled.

• Empowering Non-Finance Users: With Power BI, FP&A can create templates that enable department leaders to filter, sort, and summarize their own data.
• Reducing the "Ad-Hoc" Burden: As a result, the FP&A staff may shift their role away from being just 'Report Builders' to being 'Data Enablers'. This will give FP&A employees more time to dedicate to strategic high-level analysis of their company's financial data.

Granular Cost Allocation and Profitability Analysis

Gross Margin is a simple measure of profitability, but determining the ultimate net profitability at customer or SKU level is complex.

• Complex Allocations: Power BI can accommodate complex cost allocation methods, such as allocating corporate overhead based upon employee count or square footage, which would not work well with Excel due to the large amount of data.
• Customer Tiering: By integrating sales and cost-to-serve data (shipping, support requests, discounts), FP&A can produce "Whale Curve" visualizations depicting which 20% of customers contribute to 80% of total profit.

Scenario Planning and "What-If" Modeling

For an economy that is volatile, any static annual budget will become obsolete the moment it gets approved.

• Parameter Driven Modeling: FP&A can design dashboards utilizing "sliders". For example, the CFO can slide a bar to illustrate how a potential increase of 2 % points in interest rates will impact the GSM's Debt Service Coverage Ratio (DSCR).
• Monte Carlo simulations: Advanced users can utilize the integration of R or Python scripts into Power BI to create thousands of Monte Carlo simulations that will generate a probability distribution of achieving annual targets by the end of the year.

Enhanced Data Governance and Security

Electronic transmission of sensitive financial information through emailing Excel spreadsheets is a significant security concern.

• Row-Level Security (RLS) is the foundation of developing one report for management purposes and allowing managers to view only relevant, unique to them, row information (e.g., North American managers see only North America data, CEOs see all the data).
• With respect to Audit Trails, data is tracked through its transformation from the source to visualization. In Microsoft Excel, it may be difficult to determine who changed the formula. However, with Power BI, which allows the use of specialized version control, tracking data transformation is improved.

Bridging the Gap between Financial and Operational Data (xP&A)

The finance department is transitioning from the traditional scheduled financial forecast to xP&A. This is an activity that encourages the breaking down of department silos within the organization.

• Supply Chain Integration includes Power BI. For example, while pulling in an inventory turnover ratio, the cash flow statement will be displayed next to it. The finance department can use this information to recognize how an inventory “bottleneck” in the warehouse may impact a company’s ability to pay its liabilities in three months (using the “Quick Ratio”).
• HR and Payroll data can also provide useful information to the finance department. By integrating hr systems (HRIS) data with finance and payroll data, the finance department has access to real-time tracking of "Revenue per Full-Time Equivalent" (FTE) and "Variance in Attainment" for the sales teams. This provides a more accurate and comprehensive overview of the overall health of the business.

Natural Language Querying (Q&A)

One of the most "executive-friendly" features in Power BI would be its Q&A tool.

• AI-Generated Responses: Executives can input something like "What was total revenue for the Southwest region last quarter versus last year," and Power BI will automatically create a chart showing this data for their convenience.
• Accessibility: Because of this, senior leaders who may not possess the technical know-how needed to navigate complicated dashboards can use this feature at a moment’s notice to get nugget information during meetings.

11. Automated Narratives (Smart Narratives)

One of the typically required components of all management reports includes an automated report with a written explanation showing the "Why?" behind the numbers.

• AI-generated summaries: The Smarter Narrative feature in Power BI uses machine learning to analyse a chart and generate a paragraph that identifies the trend(s) and outliers within that data set.
• Consistency: By providing a summary generated by Power BI that is based on data, it eliminates the potential for bias or cherry picking of results that is associated with human-generated summaries.

Conclusion

Power BI provides FP&A professionals with an opportunity to transition from being data collectors to becoming value-added contributors to their organizations. Automated data collection processes coupled with improved quality visual displays of data for managers help maintain the finance department's status as the "nerve center" of the organization. During times of economic upheaval, having clear visibility into your data and the ability to rapidly respond to it will give you a distinct competitive edge.