Excel Financial Analysis 7 Steps

At present, when conducting financial analysis, most financial personnel use manual calculation of financial indicators, and the calculation workload is large. In addition, enterprises calculate financial indicators according to the requirements of their superiors, and few of them design their own financial indicators. Therefore, it is difficult to conduct financial analysis more comprehensively and systematically, and it is difficult to give full play to their enthusiasm and initiative, which is not conducive to the improvement of management level. Some enterprises that have realized computerized accounting may have some financial analysis indicators that can be automatically generated in their accounting software. However, if these indicators cannot be added or modified, it is often difficult to fully meet the different needs of enterprises. In view of the above problems, the author advocates using Excel (electronic form) for analysis, which has the advantages of unlimited analysis methods, unlimited analysis indicators and unlimited analysis contents. The specific operation steps are as follows: Step 1: Design the report format Design the report format on the basis of the current financial statements, and then make some necessary adjustments on the project, such as increasing variable costs, contributing gross profit, fixed costs, profit before tax and interest, quick assets, long-term assets and other items. It should be noted that if the method of automatically filling in the financial data is adopted, the adjusted items must be listed under the report provided by the accounting software or Excel template, so as to avoid the inconsistency when automatically filling in the financial data. Step 2: For the main column, add the necessary columns, including the number column of the current period, the number column of the previous period or the planned number column, the industry average number column, and the comparison column. The comparison column can be divided into absolute number and relative number, that is, difference and percentage. Of course, for the sake of clarity, do not set too many columns. You can put different comparison contents in different worksheets of the same workbook. Step 3: For the main column, determine the analysis indicators. In this step, first pay attention to the content of the indicators, which can be determined according to the specific needs of the enterprise. For example, the comprehensive economic benefit indicator system, DuPont financial analysis system, bankruptcy test ratio system, and other indicators such as interest guarantee multiple, cash ratio, fixed ratio, and fixed long-term fitness rate can be considered. Secondly, attention should be paid to the location of indicators, which must be under the given report format. When determining analysis indicators, the following principles should be followed: indicators should be determined according to various analysis purposes; The analysis indicators should be in line with international standards, such as the 14 ratios of American Duns, the 11 ratios published in the annual report of American Report Research, and the evaluation indicators of internationally renowned evaluation institutions; We should consider not only the current needs, but also the future needs, and innovate in financial indicators in line with the times; In order to avoid future losses, on the one hand, the indicators needed should be included as much as possible, and on the other hand, the indicators should not be too many or too indiscriminate to avoid dominating. Step 4: To quickly and timely calculate financial indicators when entering calculation formulas, each calculation formula must be entered into a cell in advance. Two problems should be noted here: first, the location of the formula must be on the same line as the indicator to be calculated; The second is the formula writing method. If the data of other cells are quoted, the address of the cell should be used. The absolute cell or the relative cell depends on the needs. Generally speaking, the relative cell can be used. Step 5: Fill in financial data There are two methods for filling in report data: manual entry and automatic generation. There are two cases of automatic generation. One is to use electronic accounting statements. The basic method is to first call out the electronic accounting statement file, then save it as an Excel file, then save and close it, and finally call out the saved file with Excel. The second is to use the database. The basic method is: first call out the database file in Excel, then input the analysis content, and finally save it. It should be noted that the Excel program is generally equipped with several industry financial statement templates, including balance sheet, income statement, profit distribution statement, and statement of changes in financial position. If you enter manually, you should first open a report template, enter the original data, generate a report, and then copy it to the analysis file. The feature of this method is that there is no need to calculate the total columns of current assets, net value of fixed assets, total value of 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 cash flow statement, the cash flow statement needs to be prepared or self prepared. In case of construction, real estate, foreign investment and other enterprises, since the system does not provide various report templates, enterprises can only make their own reports. Step 6: Generate Analysis Results When the above steps are completed, the results of each analysis indicator will be automatically generated. If it is intuitive, the results of structural analysis and trend analysis can also be reflected in the chart. Step 7: Take protective measures to prevent the formula entered in the cell from being deleted, modified or tampered with by mistake, to prevent the report data from being leaked and modified, and to continue using the worksheet next time, you can set password protection for the format of the analysis table, that is, each item cell and the cell with the formula, or hide the formula. The specific method is as follows: First, exclude some cells (cells without formulas outside the main column items and object column items) from the protection range, that is, do not select "Lock" for the above cells in "Format - Cell - Protect"; Then, set the cell with formula to be protected as "locked" or "hidden" in the same way as above; Finally, protect the worksheet, that is, select "Content" and other items in the dialog box in "Tools - Protect - Protect Worksheet", and then set a password to protect it. You must enter a password to open the next time you use it. Of course, the entire analysis file can also be set to open the password and modify the password protection. It should be noted that if the originally designed columns and indicators are not enough for application, the financial statements can be adjusted to add main columns or guest columns, and add analysis items. However, after the protection is set, the protection of the worksheet must be revoked before modification. After modification, the file should be renamed.