Financial statements translated automatically into many languages, using the Microsoft Excel Vlookup and Match functions

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 of Microsoft Excel is often underestimated. All over the world they know Excel, but they do not know the potential it offers. I want to give away for free, this work that I did some years ago.

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:

  1. State of financial situation
  2. Profit and Loss
  3. Statement of changes in Equity
  4. Statement of changes in financial position
  5. 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
As soon as you download the file, Open Excel. Enable protected view editing. Enable macros. (So ​​that the start button works) Click on start. In user, you place: 1111 and in password you put: 12345.

About working the file

The job is not locked, has no hidden cells or ranges. It is so that you have fun looking at the potential that Excel has.

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:

Alejandro Quiceno García
MVP Microsoft Excel


About Us

Excel Audi es el nombre web comercial del profesor Alejandro Quiceno García: Autor de libros de Excel y Conferencista Internacional con énfasis en Contadores, Auditores, Administradores, Gerentes, Ingenieros Civiles y Agrónomos.

Visite el canal Alejoquiceno en YouTube:

El sitio aún está en construcción. Si desea comunicarse con el profesor, puede escribirle al email o via celular en Colombia al 312-767 93 91

Recent Comments


2276,1,auditoria al software contable,1,auditoria en la biblia,1,Brexit,1,buscar parecidos,1,cash flow direct method,1,changes in financial position,1,conciliaciones,2,Conciliar parecidos,1,cruces de informacion,1,demonstrações financeiras,1,ebooks,1,eeff trans,3,escandalos de firmas,3,états financiers,1,exogena,3,Exógena,1,Financial Statements,1,formato 2276,1,formato 2276 Exogena,1,formato 2276 para la Dian,1,Funciones,6,Funciones anidadas,1,Funciones Matriciales,4,Fuzzy Lookup,1,Generalidades de Excel,4,german garmendia,1,hola soy german,1,Humor Contable,19,Ifac,1,Ignacio Cueto Plaza,1,importante,45,increible,1,libro fiscal,1,libro fiscal en excel,1,Libros,17,Libros gratis de Excel para contadores,2,macros,17,Niif,5,noticias,53,opinion,1,peliculas,3,Plantillas Excel,4,religion y economia,1,robotica y desempleo,1,sexo auditoria,1,solver,1,sumar si conjunto,1,,1,Tablas dinamicas,2,videos,33,youtubers,1,
Macros para Contadores: Financial statements translated automatically into many languages, using the Microsoft Excel Vlookup and Match functions
Financial statements translated automatically into many languages, using the Microsoft Excel Vlookup and Match functions
Macros para Contadores
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy