Power BI for Dynamics NAV Consolidated Financials

Tony Zhang - Wednesday, November 08, 2017
Power BI for Dynamics NAV Consolidated Financials

Following the blog post that talks about Power BI in general. This blog introduces the Power BI implementation offer for Dynamics NAV. The deliverables are the financial data model, dashboards and Excel template for all the companies in one NAV database.

Deliverables

The Financial Data Model covers General Ledger, Accounts Payable and Accounts Receivable.

The comprehensive financial optimizer dashboard provides quick access to business metrics that are key to monitoring profitability and liquidity. The “what-if” analysis parameters allows the modelling of the probable outcomes by changing the sales and cost of goods sold.

  • Performance KPI: Net Sales, Gross Margin and percentage, Operating Profit and percentage
  • Activity KPI: Accounts Receivable Balance, Accounts Payable Balance, Cash Collection Cycle
  • Profitability KPI: Net Income and percentage, Return on Assets, Return on Equity
  • Liquidity KPI: Working Capital, Current Ratio, Quick Ratio
  • Leverage KPI: Debt Ratio, Debt to Equity Ratio, Long-Term Debt to Equity Ratio
  • Gross Margin Over Time
  • Operating Profit Over Time
  • AR Gauge: AR Balance, AR Invoice and AR Invoice LTM
  • AP Gauge: AP Balance, AP Invoice and AP Invoice LTM
  • Cash Collection Cycle Over Time

The income statement dashboard provides the Budget vs. Actual report and the consolidated income statement, and supplements it with trend analysis.

  • Budget vs. Actual, YTD Budget vs. YTD Actual, Variance and percentage, for selected budget
  • Consolidated Income Statement by Company/Dimension
  • Net Sales, Net Income – Actual vs. Budget vs. Forecast Over Time

The Revenue & Profitability dashboard enables organizations to drive change in the management of revenue and profitability, analysis of growth drivers and increase speed to insight.

  • Net Sales KPI: Actual vs. Budget over Fiscal Periods
  • Gross Margin Gauge: Net Sales, Gross Margin – Actual vs. Budget
  • Net Sales Variance to Budget by revenue lines defined in the income statement
  • Net Sales Variance to Budget analysis by dimensions

The Operating Expenses dashboard provides insight to expense cost drivers and results, increases the productivity through use of mobile, improves management of planned vs. actual operating expenses.

  • Operating Expenses KPI: Actual vs. Budget over Fiscal Periods
  • Operating Expenses Gauge: Gross Margin, Operating Expenses – Actual vs. Budget
  • Operating Expenses Variance to Budget by expense lines defined in the income statement
  • Operating Expenses Variance to Budget analysis by dimensions
The Accounts Receivable dashboard provides key metrics combined with other information to improve the order to cash cycle time. No more manually consolidating multiple aged trial balances.
  • Customer Balance: URL link right back to the NAV customer card (requires NAV use Office 365 login), Sales, Cost, Profit and percentage and Balance
  • AR Balances and Average Days to Pay Over Time
  • Collection Effectiveness Analysis Over Time, DSO, BDSO, ADD, CEI and Average Days to Pay
  • AR Invoices, Count of Customers & Count of New Customers Over Time
  • Top 10 Customers by AR Balance, by aging buckets

The accounts payable dashboard provides key metrics enabling managers to monitor the effectiveness of accounts payable workflows, and the insights on how a vendor or group is performing across all your entities.

  • Vendor Balance: URL link right back to the NAV vendor card (requires NAV use Office 365 login), Purchase, Invoice, Balance and percentage
  • AP Balances and Average Days to Pay Over Time
  • Payable Analysis Over Time, Invoices LTM, Balance, DPO and Average Days to Pay
  • AP Invoices, Count of Vendors & Count of New Vendors Over Time
  • Top 10 Vendors by Balance, by aging buckets

The Excel Template includes: (Click here to review)

  • Trial Balance
  • Income Statement
  • Balance Sheet
  • KPIs
  • Aged Accounts Receivable
  • Aged Accounts Payable