Get best of the week

How do we generate consolidated financial statements?


25 companies, 9 countries, 6 functional currencies ... The reporting system and procedures could easily be made complex and expensive. But we found a simple solution from a technical point of view that our users really like, and especially specialists in the preparation of consolidated financial statements in accordance with international standards (hereinafter referred to as IFRS) and management reporting.


Very often, "financiers" and "techies" do not understand each other. As a result, unreasonably complex, expensive and inconvenient solutions appear. In this article, we will try to explain the basic things, the lack of understanding of which often prevents the creation of effective solutions, using the example of the reporting process in our company. We will also tell about our successful experience in creating a reporting system - what we wanted to do, what happened, what we did not like from the result, and we will change it.


We warn you right away - there will be neither about Blockchain, nor about Big Data, nor about Artificial Intelligence and other fashionable technologies. We did not use anything of this for one simple reason - we did not need them for this task.


What you need to get "output"


Since our group of companies does not publish consolidated financial statements in the public domain, we cannot give examples of our reports. They are fairly standard and similar to the many financial statements that can be found on the Internet.


For example, here are the main Google reports:


INCOME STATEMENT (part of the report ):


Fiscal year is January-December. All values ​​USD Millions.20182017201620152014
Sales / Revenue136,958111,02489,73373.5965.83
Sales growth23.36%23.73%21.94%11.79%-
Cost of Goods Sold (COGS) incl. D&A59,54945.58335,13828,16425,691
COGS excluding D&A50,51438,66828,99423,10120,712
Depreciation & Amortization Expense9,0356,9156,1445,0634,979
Depreciation8,1646,1035,2674,1323,523
Amortization of intangibles8718128779311,456
COGS Growth30.64%29.73%24.76%9.63%-
Gross income77,40965,44154,59545,42640,139
Gross Income Growth18.29%19.87%20.18%13.17%-

BALANCE SHEET  ( ):


Fiscal year is January-December. All values USD Millions.20182017201620152014
Cash & Short Term Investments109,14101,87186,33373,06664,395
Cash Only16,70110,71512,91816,54918,347
Short-Term Investments92,43991,15673,41556,51746,048
Cash & Short Term Investments Growth7.14%18.00%18.16%13.47%-
Cash & ST Investments / Total Assets46.88%51.63%51.54%49.55%49.85%
Total Accounts Receivable21,19318,70514,23213,90910,849
Accounts Receivables, Net20,83818,33614,13711,5569,383
Accounts Receivables, Gross21,56719,0114,60411,8529,608
Bad Debt/Doubtful Accounts-729-674-467-296-225 
Other Receivables355369952,3531,466
Accounts Receivable Growth13.30%31.43%2.32%28.21%-
Accounts Receivable Turnover6.465.946.315.2906.07
Inventories1,107749268491-
Finished Goods1,107----
Other Current Assets4,2362,9834,5752,6483,412
Miscellaneous Current Assets4,2362,9834,5752,6483,412
Total Current Assets135,676124,308105,40890,11478,656

, — / , .


, .


? . , , 100 . , , , . (, , , ) , . 


— , , , ..


, . , .


. .


,


  • () . , , , , . .
  • — — , , .
  • , , .
  • — ( / ).

?


() :  , 100 , — 146 
 , . , , ( , , , ), .


, . , , (, ..) — , « » («cooking the books» / «window dressing»).


: , . / — .


, , , .



ERP Microsoft Dynamics NAV ( — NAV), . ERP NAV.


NAV , . .


( ), NAV Excel, Pivot Table. — .


, — Power Pivot, Excel.


, , . .


M — LucaNet Microsoft Analysis Services ( Microsoft SQL Server).


Analysis Services (NAV Microsoft SQL Server), . — LucaNet . , Analysis Services — , ( , LucaNet), .  


Analysis Services — Tabular Multidimensional. Multidimensional, . 


, OLAP , Excel. Pivot Table, .


, Reporting Services — Microsoft SQL Server.




NAV (W1) .


( , , ..) Master Data Management, .


( ) , ( ).


— (). — , . — , , .. , , . 


NAV , , . , .


, — , , . , , .


— — . , .


(, ) , NAV , , , , .


( ).


, « » — GENERAL IFRS.


, , «IFRS».


, , — «IFRS».


, — . «» .


( — ERP , — ) . EUR ( ) — . EUR — — EUR.


« » . , NAV. 


«» .



. — EUR. , EUR, EUR.


NAV ( ). , — USD, USD, — EUR, EUR.


NAV . , , , — EUR , .


( ).



( , ..) EUR.



:



(, ..), , ( , ..) NAV.


NAV ( ) .


NAV , , DWH (Data WareHouse — ). , .


OLAP .


Excel .


Tableau ( ), . Excel .


«» , . — .


, :


  • . , - .
  • , — , , . «» — . 

, NAV . NAV . , . . ( ).


. ( NAV) . 10 — 15 .


, :


MS NAV

MS NAV tables in the database


Key to financial accounting is the G_L Entry table.


, : [CRONUS International Ltd_$G_L Entry], «CRONUS International Ltd» — . — . . NAV . «G_L Entry».


:


  • [Entry No_] [int] — ,
  • [G_L Account No_] [nvarchar](20) — — ,
  • [Posting Date] [datetime] — ,
  • [Document Type] [int] — (, , ..),
  • [Document No_] [nvarchar](20) — ,
  • [Amount] [decimal](38, 20) — ,
  • [Transaction No_] [int] — , , ( , ),
  • [Debit Amount] [decimal](38, 20) — ,
  • [Credit Amount] [decimal](38, 20) — ,
  • [Additional-Currency Amount] [decimal](38, 20) — ,
  • [Add_-Currency Debit Amount] [decimal](38, 20) — ,
  • [Add_-Currency Credit Amount] [decimal](38, 20) — ,
  • [Dimension Set ID] [int] — .

«G_L Entry» ( NAV, ):


  • [Amount] = [Debit Amount] — [Credit Amount]
  • [Amount] , [Transaction No_] [Posting Date], ( « »).
  • [Transaction No_] [Posting Date] [Entry No_] ( , ).
  • [Additional-Currency Amount], [Add_-Currency Debit Amount] [Add_-Currency Credit Amount].

«G_L Account». , ( ) .   «G_L Entry» [GL Account No]. «G_L Account» :


  • [No_] [nvarchar](20) — , «G_L Entry» [G_L Account No_],
  • [Name] [nvarchar](50) — ,
  • [Exchange Rate Adjustment] [int] — , ,
  • [Account Subcategory Entry No_] [int] — .

«G_L Account Category» .  


  • [Entry No_] [int] IDENTITY(1,1) — ,
  • [Parent Entry No_] [int] — ,
  • [Presentation Order] [nvarchar](100) — () ,
  • [Indentation] [int] — ,
  • [Description] [nvarchar](80)- ,
  • [Account Category] [int] — (Assets, Liabilities, Equity, Income, Cost of Goods Sold, Expense),
  • [Income_Balance] [int] — , / .

«», «G_L Entry».


«Dimension Set Entry», «» :


  • [Dimension Set ID] [int] — , «G_L Entry»,
  • [Dimension Code] [nvarchar](20) — ,
  • [Dimension Value Code] [nvarchar](20) — .

«Dimension Value», ( ):


  • [Dimension Code] [nvarchar](20) — ,
  • [Code] [nvarchar](20) — , «Dimension Set Entry» [Dimension Value Code]
  • [Name] [nvarchar](50) — .

«Dimension Set Entry» «Dimension Value» — [Dimension Code]   [Dimension Value Code] ([Code]).


. «G_L Entry». 


— , . , , .



, ?


1 ():


, 136,958, «INCOME STATEMENT» ( ) «2018» «Sales/Revenue». , :


  1. «G_L Account Category» , [Description] «Sales/Revenue». 
  2. «G_L Account» , [Account Subcategory Entry No_] [Entry No_] «G_L Account Category», 1.
  3. «G_L Entry» (), [G_L Account No_] [No_] «G_L Account», 2.
  4. 3 , [Posting Date] 2018 , [Posting Date] ‘2018.01.01’ [Posting Date] ‘2018.12.31’.
  5. 4 [Amount], — .

2 ():


16,701, «BALANCE SHEET» ( ) «2018» «Cash Only», : 


  1. «G_L Account Category» , [Description] «Cash Only». 
  2. «G_L Account» , [Account Subcategory Entry No_] [Entry No_] «G_L Account Category», 1.
  3. «G_L Entry» (), [G_L Account No_] [No_] «G_L Account», 2.
  4. 3 , [Posting Date] 2018 , . [Posting Date] ‘2018.12.31’.
  5. 4 [Amount], — .

3 ():


136,958, «INCOME STATEMENT» «2018» «Sales/Revenue», (), :


  1. «G_L Account Category» , [Description] «Sales/Revenue». 
  2. «G_L Account» , [Account Subcategory Entry No_] [Entry No_] «G_L Account Category», 1.
  3. «G_L Entry» (), [G_L Account No_] [No_] «G_L Account», 2.
  4. 3 , [Posting Date] 2018 , [Posting Date] ‘2018.01.01’ [Posting Date] ‘2018.12.31’.
  5. 4 [Dimension Set ID] «Dimension Set Entry»,   [Dimension Code] ‘COUNTRY’ ( — ). «G_L Entry» ( [Dimension Set ID]) «» — NAV. , «» «G_L Entry» .
  6. 5 [Dimension Code] [Dimension Value Code] «Dimension Value» [Name] ( ). «» G_L Entry» ( ) — , ‘NA’ (not allocated / not applicable).
  7. 4   «G_L Entry» , 6, [Amount], — ( ‘NA’)  .

, . Excel OLAP .



. Excel, .


, — , . , , , , .


, .


:


  • , , “” ( ). , , .
  • () , ( , , , ..).
  • “” () , .
  • . — (). Excel .
  • We do not aggregate data when uploading to the reporting system. Each financial transaction (with a unique identifier) ​​is presented in the reporting system as a separate entry.

Sergey Ustinov
Daria Fadeeva, FCCA


PS In preparing the article used a photo from www.pexels.com .

More articles:


All Articles