To perform the financial analysis of a company, we created an Excel template, composed of four sheets, interlaced with each other. In these sheets, we find a Profit and Loss Statement (PyG), a Balance Sheet (BCE), a Cash Flow Statement and a Summary Table, where we also calculate the Ratios that financial analysts need.
The user only has to fill in data on the “PyG” and “BCE” sheets. The cells to be filled in are indicated with colors, detailed in a legend. On the other sheets, the formulas provide the relevant results. The details of each sheet are explained below.
The Profit and Loss Statement sheet “PyG”, marks in light blue the cells that the user can fill in, and in white those that are blocked, because they contain formulas. It shows a Profit and Loss Statement that is broken down into various concepts. The columns indicate the data, by years entered, as historical (up to seven years). Also the years taken as Forecasts (up to four years). The sheet is assembled in such a way that with a macro the data is updated, to add new years, causing the movement of the data, between columns. The macro affects all the sheets, that is, the changes also affect the Balance Sheet, the Cash Flow Sheet and the Summary Table. There are also check formulas, to verify the data entered. We show an image:
The balance sheet “BCE” divides the values of historical and forecast years into several items. The items are grouped into Non-Current Assets, Current Assets, Equity, Non-Current Liabilities and Current Liabilities. The user must fill in the blue cells with values. There are also check formulas, to verify the data entered. We show an image:
In the “Cash Flow” sheet, the user only has to look at the results that the formulas present. On this sheet there are formulas for checking and verifying the results. We show an image:
On the last page, the “Summary Box” shows the results of the formulas entered. Groups of data corresponding to the Balance, the “PyG”, the Net Cash Flow Calculation and finally a Ratio table are presented. We show an image:
Application made with Excel. To use this application you need to install the MS Excel program. The application is made in Spanish language. Note: The data inserted in the images are fictitious.
