Automatically calculated financial statement start...

pierpa · ‎02-13-2020

Hello, I need help to structure a financial report automatically calculated starting from all the transactions. I will briefly introduce you the situation and then the problem.

I need to create an in come statement which is structured like this:

Revenue
- Cost of Goods Sold
= Gross Profit
- Expenses
- Marketing
- General & Administrative
- Depreciation & Amortization
- Interest
= Earning Before Taxes
- Taxes
= Net Earnings

Every element (node) in this statement is made by the sum of certain accounts.

For example Expenses is made of:

Marketing, Advertising & Promotion
General & Administrative
…

Every voice, as Marketing, Advertising… is an aggregation of accounts as:

Social media expenses
Banner expenses
TV advertising expenses

Moreover every account is an aggregation of transactions:

Social media expenses 500$ 31/11/2019
Social media expenses 500$ 12/12/2019
…

So, I need to arrive to Net Earnings, which is the subtraction and the sum of different elements (node), every element (Expenses), is the aggregation of a lot of accounts (Social media expenses), wich is an aggregation of transactions (social media expenses 500$ 12/12/2019).

From my company systems I can download every transactions whic contains the parent account, and the parent element (that we call node)

I created hierarchy that tells me all the node, and the parent of every node (for simplicity I’ve represented just one level of node, but in reality they are more)

Now the question is, how can I replicate the structure of the financial statement, starting from the download of all the transactions, knowing that every transaction is contained in an account, that is contained in a specific node?

Account hierarchy example:

Account	Description	Parent Node
P123456789	Income from properties used for own activities	N0001
P112345678	Income from land and agricultural property	N0001
P111234567	Income from other real rights	N0002
P111123456	Income from tangible assets and inventories	N0002

Node hierarchy example:

Node	Description	Parent
N0000	Group result of the period
N0001	Consolidated result of the period	N0000
N0002	Profit after taxes	N0001
N0003	Profit before taxes	N0002
N0004	Profit before taxes (gross of unallocated holding expenses)	N0003
N0005	Operating result	N0004

We are talking about 300 nodes and 2.000 accounts, 50.000 transactions.

Thank you in advance

v-kelly-msft · ‎02-13-2020

Hi @pierpa ，

If the structure already exists in a table and the size of the table is smaller than 1G,you can directly connect it with desktop by import,otherwise import all the related tables to desktop,then create the model you need in desktop.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

pierpa · ‎02-14-2020

Hi @v-kelly-msft thank you for your reply, the problem is not importing the data, but create the structure of the financial statement by automatically recreate the hierarchy of nodes, elements and account.

So, I need to arrive to Net Earnings, which is the subtraction and the sum of different elements (node), every element (Expenses), is the aggregation of a lot of accounts (Social media expenses), wich is an aggregation of transactions (social media expenses 500$ 12/12/2019

To do it by just importing a file i would need to create a table where for every node there is a parent, the parent of the parent, etc.

Node	Parent 1	Parent 2	Parent 3	Parent 4...
N0015	N0010	N0005	N0002	N0000

Then, by knowing the account associated to every node it is possible to put all the transactions in the correct node, sum every transaction in a node and by sorting them, recreate the income statment.

But building a table with the hierarchy of the nodes by hands is almost impossible. As stated before there are more than 300 nodes, so I'm looking for a better way to do it. As for now I only have a list of all the nodes and the first parent of each node.