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
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
Data Connectors
