Pivot-Tables for the statement of changes in financial position

This post is the result of observation, trial and error. If you are an accountant, and you have suffered for the statement of changes in fi...

This post is the result of observation, trial and error.
If you are an accountant, and you have suffered for the statement of changes in financial position, I'm sure you'll really appreciate this learning.

The Pivot tables are an incredible Reporter. And if you know them you can parameterize anything. Ok. ¿How to parameterize or configure a database to obtain the statement of changes in financial position?

Follow these steps:
One: If you know the financial statement, you get to know the differences between the movements of the final balance less the opening balance.

Two: You must have a trial balance of the annual period: January 1 of year to work until December 31st of the year to work.

Three: Needless to say, that in the profit and loss accounts, the opening balance is zero and that the balance must be square.

Four: In a spreadsheet, get the difference unless Opening balance Ending balance, the variation.

Fifth: In a new column, to all accounts in the income statement will name "1. Profit or Loss for the year."

Sixth: The asset accounts related provisions, Depreciation, Amortization, the will name "2. Charges (credits) not affecting working capital:"

Seventh: The accounts of Capital and Reserves, the will name "3. Increased capital"
Eighth: The accounts of the profit or loss for the year and profit or loss of previous ejercios (heritage), the will name "Distribution Utilities"

Ninth: The rest of the assets and liabilities, name them according to their current and noncurrent portion. (Current assets, noncurrent assets, current liabilities, noncurrent liabilities.) If the account "Accounts Receivable", "Customer" or accounts receivable and have current portion of long-term portion, you must separate each one and classify current assets and active in non-current. The same for investment and other items of current assets. Same for current liabilities, most of all with regard to loans from financial institutions or other lenders.

Ok, this is the first part, which is like the "top level". Now comes the following grouping:

In a second column:

The 'profit or loss for the period ", the" Charges (credits) not affecting working capital "and" Capital increase "will call" 1. Provided Resources ".

The assets and noncurrent liabilities, and distribution of profits, will name "2. Financial resources applied to"

The assets and current liabilities will name it "3. Changes in Assets and Current Liabilities"
In a third column:
to "1. Provided Resources" and "2. financial resources applied to" the will name "1. Increase or decrease in working capital."

Finally, the "3. Changes in Assets and Current Liabilities," call it "2. Analysis of changes in working capital"

If you believe that any accounting item is incorrectly configured, you can correct the parameterization in the base and refresh the PivotTable.

Well, having configured the balance, we can now make a pivot table that we assemble in seconds the statement of changes in financial position

VERY IMPORTANT: Please note the signs. Credit accounts are negative in nature, if you had utility, you will see credit balance, negative, in red. So, to not confuse readers of the report, you must arrange the signs. But, we need the signs according to the nature of the accounts to see if the match report. Similarly you can build cash flow by the indirect method.

You will understand much better if you download the example here.

I appreciate share the entry on social networks: Twitter, Facebook and Google Plus. Help me to show my blog please.


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 alejoquiceno@gmail.com 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,44,increible,1,libro fiscal,1,libro fiscal en excel,1,Libros,17,Libros gratis de Excel para contadores,2,macros,16,Niif,5,noticias,52,opinion,1,peliculas,3,Plantillas Excel,4,religion y economia,1,robotica y desempleo,1,sexo auditoria,1,solver,1,sumar si conjunto,1,sumar.si.conjunto,1,Tablas dinamicas,2,videos,33,youtubers,1,
Macros para Contadores: Pivot-Tables for the statement of changes in financial position
Pivot-Tables for the statement of changes in financial position
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