To the current financial statements as the basis, and then in the project to make some necessary adjustments, such as the increase in variable costs, contribute to the gross profit, fixed costs, EBITDA, quick assets, long-term assets and so on. Note: If you use the method of automatic filling of financial data, the adjusted items must be listed in the accounting software or Excel template provided under the statement, so as not to automatically fill in the financial data on the line.
How to use Excel for financial analysis
Two, for the main column, add the necessary columns.
Including the number of columns in the current period, the number of last period or the planned number of columns, industry averages columns, comparison columns, comparison columns can be divided into absolute and relative numbers, that is, the difference and percentage. Of course, for clarity, the columns should not be set too much, you can place different comparisons in the same workbook in different worksheets.
How to use Excel for financial analysis
Three, for the main column, to determine the analysis of indicators.
One should pay attention to the content of the indicators can be determined according to the specific needs of enterprises. Such as the economic efficiency of the comprehensive indicator system, DuPont financial analysis system, bankruptcy test ratio system, in addition to consider increasing the guaranteed interest multiples, cash ratios, fixed ratios, fixed long-term suitability rate and other indicators.
Two should pay attention to the location of the indicators, must be under the given statement format. In determining the analytical indicators, the following principles should be followed:
(1) To determine the indicators according to the various analytical purposes.
(2) There is also a question of analyzing indicators in line with international standards. For example, the 14 ratios of the American Dunn's company, the 11 ratios published in the annual issue of the American "Statement Research", and then the assessment indicators of the famous international assessment organizations can be included.
(3) Not only to consider the current needs, but also to consider the future needs. The rapid development of science and technology, the emergence of knowledge-based economy, there must be greater innovation in financial indicators. (4) In order to avoid future losses, indicators that may be needed should be included as much as possible, on the other hand, the indicators should not be too many and too indiscriminate, so as not to overpower the main purpose.
How to use Excel for financial analysis
Four, enter the formula.
To quickly and timely calculation of financial indicators, it is necessary to enter each formula into a cell in advance. Here need to pay attention to the problem: First, the location of the formula must be calculated with the indicators in the same line; Second, the formula is written, if the reference to other cells to use the cell's information to the address to indicate that, as for the use of absolute cells or relative cells depending on the need. Generally speaking, the relative cell can be used.
How to use Excel for financial analysis
Fifth, fill in the financial data.
There are two ways to fill in the report data: one is manually entered, the second is automatically generated. Which is automatically generated and divided into two cases, one is the use of computerized accounting statements, the basic practice is to first call out the computerized accounting statements file. Then save as. Excel file, then save, close, and then finally use Excel to call up the saved file can be. H is the use of databases, the basic approach is to first call up the database file in Excel, and then enter the analysis, and finally save. Specific process: Excel - open the file - file name (dbf suffix) - enter the indicators and formulas - - Save - file name - the file is saved. -Save - file name - save.
It should be noted that in the Excel program, generally equipped with industry, commerce, tourism, transportation, finance and several other industries financial statement templates, specifically including balance sheets, income statements, profit and loss statements, profit distribution statements, statements of changes in financial position. If manually entered, you should first open a statement template, and then enter the raw data to generate the report, and then copied to the analysis file. This method is characterized by the fact that there is no need to calculate the total columns for current assets, net fixed assets, total assets, total liabilities, etc., which are automatically generated by the system. However, since the statement of changes in financial position has been replaced by the statement of cash flows, the statement of cash flows needs to be prepared by yourself or make your own template. In the case of construction, real estate, joint-stock, foreign-invested enterprises, etc., since the system does not provide a variety of statement templates, enterprises should also be self-made.
Sixth, generate analysis results.
When the completion of the above steps, the results of the analysis of the indicators are automatically generated. If for the sake of intuition, can also be structural analysis, trend analysis of the results reflected in the chart.
7
Seven, take protective measures.
In order to prevent the formula has been entered into the cell was mistakenly deleted, mistakenly changed, or tampered with, in order to prevent the leakage and modification of the report data, and at the same time, in order to continue to use the worksheet for the next time, you can analyze the format of the table, that is, the cells of the project and the cells with the formula to set up password protection. If you do not want to show the formula, the formula can also be hidden. The approach is: first of all, some of the cells (the main column of items and items outside the guest column and does not come with the formula of the cell) excluded from the scope of protection. That is: Selected - Format - Cell - Protection - the above cells selected for not "lock"; then, and then to protect the cells with the formula is set to lock, hide, the same method; finally, and then the protection of the worksheet, that is: Tools - protection - -protect the worksheet - options - select the dialog box "content" and other items - -Enter the password - OK - -Protect the worksheet. -Enter the password - OK - and then enter - password - OK! This is how the protection is realized. This protects the sheet. The next time you open it, you must enter the password. Of course, the entire analysis file can also be set to open the password and modify the right to password protection.