Financial statements translated automatically into many languages, using the Microsoft Excel Vlookup and Match functions ¡The Excel tem...
Financial statements translated automatically into many languages, using the Microsoft Excel Vlookup and Match functions
¡The Excel template also automatically assembles general purpose financial statements!
The power and versatility that Excel can never be matched by any accounting software in the world.
For those who know the functions of Excel, this template is not complex, and in fact, it is not. Added value comes from the fact that it alone, in addition to translating, also automatically assembles the 5 general purpose financial statements:
- State of financial situation
- Profit and Loss
- Statement of changes in Equity
- Statement of changes in financial position
- Indirect method cash flow
So to achieve this, you do not need to know the function index or match. You must have officiated as an accountant, and have experience building these financial statements. In addition to knowing the potential of the array formulas and adapting them to the needs, in this case, automatically assembling the financial statements.
What you must do to download the file
My files are in a folder in MediaFire. Please click here
About working the file
The basis of the chart of accounts is the Colombian chart of accounts that was formerly used in Decree Law # 2650 of 1993. This encoding uses numbers instead of alpha-numeric tags or codes.
You can use this example and adapt it to yours.
What you should do is translate the top-level account codes: Class, Group, and Sub-group into the different languages.
The database sheet should contain a trial balance, preferably of an annual lapse.The lapse should always begin on January 1. The starting balance should be consistent with the final balance of the previous year (check or audit that topic). Of course, the trial balance should be square.
In the initial and final balances, the assets, cost and expense accounts are positive, as mandated by their nature. The liabilities, equity and income accounts are negative. For this reason, the total sum of this initial Balance column must be ZERO. All debits and credits are typed POSITIVE. And the sum of both columns, must be EQUAL. The sum of the final balance must be, ZERO.
The different sheets have the account codes of the example company. It is natural that the codes of your company are not there.
The first thing to verify is that the code of the account exists in the "dictionary" sheet.
Then, if in each section of each report, if there is no account code for your company or case you are implementing, look for the location and insert a blank row. Then copy the functions of the top row.
As you can see in the file, I use the nested Vlookup functions with the Match functions. Sumif, some array formulas, Like the one I use in the statement of changes in financial position:
To learn more of these array formulas Clic here
About translating accounts
This translation was done with the help of Google Traslator. It is possible that in some accounts is not perfect, so I present excuses for that inconvenience.
I think as a user I accomplished a lot doing this job.
I thank you to subscribe to my blog, and also subscribe to my channel on YouTube:
Https://www.youtube.com/channel/UCBw-_HeCw_dSvoQP4EqiTQQ
Colombia
MVP Microsoft Excel
COMMENTS