How to analyze financial statements with EXCEL?

First, design the report format. According to the current financial statements, some necessary adjustments should be made to the project, such as increasing variable costs, contributing gross profit, fixed costs, pre-tax profits and interest, quick assets and long-term assets. It should be noted that if the method of automatically filling in financial data is adopted, the adjusted items must be listed under the statements provided by accounting software or Excel template to avoid inconsistency when automatically filling in financial data.

Two, for the main column, add the necessary columns. Including the current number column, the previous number or plan number column, industry average column and comparison column. Comparison columns can be divided into absolute numbers and relative numbers, that is, difference and percentage. Of course, for clarity, don't set too many columns. You can put different comparisons on different worksheets in the same workbook.

Third, for the main column, determine the analysis index. First, we should pay attention to the content of indicators, which can be determined according to the specific needs of enterprises. Such as the comprehensive index system of economic benefits, DuPont financial analysis system and bankruptcy test ratio system, in addition, we can consider increasing the interest guarantee multiple, cash ratio, fixed ratio and fixed long-term suitability rate. Second, pay attention to the position of indicators, which must be in a given report format. When determining the analysis index, we should follow the following principles: (1) We should determine the index according to various analysis purposes. (2) There is also a problem that the analytical indicators are in line with international standards. For example, the ratio 14 of Dunn Company in the United States, the ratio 1 1 published by American annual report research, and the evaluation indicators of internationally renowned evaluation agencies can all be included. (3) We should consider not only the present needs, but also the future needs. With the rapid development of science and technology and the emergence of knowledge economy, financial indicators are bound to have great innovation. (4) In order to avoid future losses, include as many indicators as possible. On the other hand, there should not be too many indicators to avoid usurping the host's role.

Fourth, enter the calculation formula. In order to calculate financial indicators quickly and timely, it is necessary to input each calculation formula into a cell in advance. The problems that need to be paid attention to here are as follows: first, the position of the formula must be on the same line as the index to be calculated; The second is the writing of the formula. If the information of other cells is quoted, it should be expressed by the address of the cell. As for whether to use the opposite community or the opposite community, it depends on the need. Generally speaking, it is enough to use the opposite cell.

5. Fill in the financial data. There are two ways to fill in report data: one is manual entry, and the other is automatic generation. Among them, automatic generation is divided into two situations. One is to use computer accounting statements, and the basic method is to call up computer accounting statement files first. Then save it as. Excel file, then save it, close it, and finally call out the saved file with Excel. H is to use a database. The basic method is to adjust the database file in Excel first, then input the analysis content, and finally save it. The specific process is: Excel- Open File-File Name (. Dbf suffix)-Enter indicators and formulas-Save as-File name-Save.

It should be noted that in Excel programs, there are usually financial statement templates for industries such as industry, commerce, tourism, transportation and finance, including balance sheet, income statement, profit distribution statement and financial status change statement. If you enter manually, you must first open a report template, then enter the original data, generate a report, and then copy it to the analysis file. The characteristic of this method is that it does not need to calculate the total columns of current assets, net fixed assets, total assets and total liabilities, and it is automatically generated by the system. However, because the statement of changes in financial position has been replaced by the cash flow statement, the cash flow statement needs to be compiled or a self-made template. If it is construction, real estate, joint-stock companies, foreign-funded enterprises, etc. Enterprises should also make their own reports, because the system does not provide various report templates.

Sixth, generate analysis results. After the above steps are completed, the results of each analysis index will be automatically generated. If you want to be intuitive, you can also reflect the results of structural analysis and trend analysis in the chart.

Seven, take protective measures. In order to prevent the formula in the input cell from being deleted, modified or tampered with, to prevent the disclosure and modification of report data, and to continue to use the worksheet next time, the format of the analysis table, that is, the cells of each item and the cells with formulas, can be password protected. If you don't want to show the formula, you can also hide it. The method is as follows: first, some cells (cells without formulas except main column items and guest column items) are excluded from the protection scope. That is, select-format-cell-protection-select the above cells as unlocked; Then, set the cells that need to protect the formula as locked or hidden, as shown above; Finally, protect the worksheet, that is, Tools-Protect-Protect Worksheet-Options-Select the contents of the dialog box-Enter the password-OK-Enter the password again-OK. In this way, protection is achieved. You must enter a password the next time you open it. Of course, you can also protect the entire analysis file by opening the password and modifying the password.

Finally, it should be noted that if the original columns and indicators are not enough for application, the financial statements can also be adjusted, adding main columns or guest columns and additional analysis items. However, after setting protection, you must unprotect the worksheet and rename the file after modification.