Balance Sheet Analysis

High Level Design

Overview

Financial statement analysis (or financial analysis) is the process of reviewing and analyzing a company's financial statements to make better managerial decisions to increase financial performance of company, to succeed in business environment.

In this BI solution we propose a method or process involving specific techniques for evaluating risks, performance, financial health, and prospects of company. It is aimed to allow employees authorized to make managerial decisions (a) to identify the current financial position of the company as of date as well as financial results for specific time frame; (b) allow to measure liquidity, profitability, leverage, recourses turnover, company-wide efficiency, and cash flow; (c) to define trends by comparing ratios across multiple periods and statement types etc.

Data sources are commonly used and can be official financial statement of the company (Balance Sheet, Profit & Loss Account, Cash flow Statements prepared based on the IFRS) as well as General ledger containing detailed list of all journal entries.

It is used by a variety of stakeholders, such as credit and equity investors, the government, the public, and decision-makers within the organization. These stakeholders have different interests and apply a variety of different techniques to meet their needs. For example, equity investors are interested in the long-term earnings power of the organization and perhaps the sustainability and growth of dividend payments. Creditors want to ensure the interest and principal is paid on the organization’s debt securities (e.g., bonds) when due.

Goals

Provide comprehensive analysis of company`s corporate finances aimed to make relevant disclosure enough to make managerial decisions related to financial, operational company`s business activity.

Objectives

The set of the objects below enables to reach out the goal above:

  • To visualize balance sheet structure based on main parts as of reporting dates to see dynamics;
  • Calculation of Debt`s utilization as of specific date (Total/Net/ST/LT Debt) as of reporting dates in dynamics;
  • Estimate creditworthiness of company based on calculation debt load;
  • Estimate Leverage and Gearing levels with the ability to have a look on trends;
  • Assess the company`s possibility to repair all obligations in dynamic in dates;
  • Analyze turnover of the company’s resources (AR, AP, Inventories, etc.) and how it influences on profitability efficiency available in time dynamics;

KPI Architecture

Objectives KPIs Measures Data Source
1. Balance Sheet Structure as of reporting dates Balances of Current/Long-term

Assets/Liabilities per each quartal

Fact.General_Ledger
2.

 

Total Debt Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt +

Short Term Debt + Current Portion of Long Term Debt (CPLTD)

All from FACT.Balance_transpose
Net Debt Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt – Cash & Marketable Securities All from FACT.Balance_transpose
Short Term Debt Short Term Debt + Current Portion of Long Term Debt (CPLTD) All from FACT.Balance_transpose
Long Term Debt Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt All from FACT.Balance_transpose
3. TOTAL DEBT/EBITDA Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt +

Short Term Debt + Current Portion of Long Term Debt (CPLTD) /

Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses)

TOTAL DEBT – from FACT.Balance_transpose

EBITDA from FACT.P&L_transpose

NET DEBT/EBITDA (Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt – Cash & Marketable Securities)

/ (Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses))

Net Debt – from Balance

EBITDA from FACT.P&L_transpose

LT DEBT/EBITDA (Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt ) / (Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses))

LT DEBT – from Balance

EBITDA from FACT.P&L_transpose

4. Leverage (Total Liabilities) / (Equity – Goodwill – Intangibles) All from FACT.Balance_transpose
Gross Gearing (Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt +

Short Term Debt + Current Portion of Long Term Debt (CPLTD))

/(Equity – Goodwill – Intangibles)

All from FACT.Balance_transpose
Net Gearing (Senior Bank Debt + Finance Lease + Subordinated Debt + Accrued interests (ST+LT) + Other Debt – Cash & Marketable Securities)

/(Equity – Goodwill – Intangibles)

All from FACT.Balance_transpose
5. Current Liquidity (Current Assets)/(Current Liabilities) All from FACT.Balance_transpose
Quick Liquidity (Cash & Marketable Securities+ Accounts Receivable) / (Current Liabilities) All from FACT.Balance_transpose
Working Investments/Sales (Current Assets – Cash & Marketable Securities) / (Current Liabilities – Short Term Debt – CPLTD) All from FACT.Balance_transpose
6. Days Payable (Accounts Payables)/(Cost of Sales)*365 Accounts Payables – from FACT.Balance_transpose

Cost of Sales from FACT.Balance_transpose

Days Receivable (Account Receivables)/(Sales)*365 Accounts Receivables – from FACT.Balance_transpose

Sales from FACT.Balance_transpose

Days Inventory (Inventories)/(Cost of Sales)*365 Inventories – from FACT.Balance_transpose

Cost of Sales from FACT.Balance_transpose

Sales/ Net Fixed Assets (Sales)/ (Net Fixed Assets+ Net Finance Leases) Sales – from FACT.P&L_transpose

Other from FACT.Balance_transpose

Entities Relationship Diagram

Balance Sheet Analysis

Data Requirements

# Source Table Name Table Details (Type, #Rows, Key field/s)
1 ERP or accounting systems

in Company

General _Ledger The list of all accounting journal entries during analyzed period
2 ERP or accounting systems

in Company

Balance Sheet Official finance reporting of the company prepared by accountant divisions
3 ERP or accounting systems

in Company

Profit and Loss Statements Official finance reporting of the company prepared by accountant divisions

Plugins & Scripts Used

Implementation
Kit

The following resources will enable you to design your dashboard and data model with sample data and then apply it to your own data. Note that you will need to have a previously installed version of Sisense (you can use the free trial version if you’re not a customer).

Sample data and dashboard examples (direct download)

Documentation