Power BI for Sage 300 Consolidated Financials

Tony Zhang - Friday, October 27, 2017
Power BI for Sage 300 Consolidated Financials

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

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, cost of goods sold and expenses.

  • 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, Account Segment
  • 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 accounts
  • Net Sales Variance to Budget analysis by account segments

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 accounts
  • Operating Expenses Variance to Budget analysis by account segments

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: Invoice, Invoice YTD, Invoice LTM, Balance and percentage
  • 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: Invoice, Invoice YTD, Invoice LTM, 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.

Excel Reports