Clever Use Of Excel To Do Financial Analysis How To Deal With?

For accountants, most of them use manual calculation of financial indicators when carrying out financial analysis.

In addition, enterprises calculate financial indicators according to their superiors' requirements, and enterprises rarely design their own, so 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.

Based on the current financial statements, some necessary adjustments should be made on the project, such as increasing the variable cost, contributing gross profit, fixed cost, pre tax profit, quick assets and long term assets.

In addition, there may be some financial analysis indicators in the accounting software of accounting units.

But if these indicators can not be increased or modified, it is often difficult to fully meet the different needs of various enterprises.

It includes the number of columns in the current period, the number of items in the previous period or the number of columns, the average column and comparison column, and the comparison column can be divided into absolute and relative numbers, that is, the difference and percentage.

Of course, for the sake of clarity, there should not be too many columns, so different content can be placed in different worksheets of the same workbook.

1, pay attention to the content of indicators, which can be determined according to the specific needs of enterprises.

For example, the comprehensive index system of economic efficiency, the DuPont financial analysis system and the bankruptcy test ratio system, we can also consider increasing the interest guarantee ratio, cash ratio, fixed ratio, fixed long-term suitability rate and so on.

2, pay attention to the location of the index, which must be under the given report format.

The following principles should be followed when determining the indicators.

(1) determination based on various analytical purposes.

。

(2) there is also a problem of international standards in the analysis index.

Such as the 14 ratios of Deng's company, the 11 ratios published in the annual report research form of the United States can be included in the evaluation index of international famous institutions.

(3) not only to consider current needs, but also to consider future needs.

With the rapid development of science and technology and the emergence of knowledge economy, financial indicators must be greatly innovating.

(4) in order to avoid future losses, it is necessary to include as many indicators as possible, and on the other hand, too many indicators should not be too large to avoid making noises.

To quickly and timely calculate financial indicators, we must input each formula into a cell in advance.

Here we need to pay attention to the following problems: first, the position of the formula must be in the same line as the index to be calculated; two, the formula is written. If the information of other cells is quoted, the address of the cell must be used.

In general, you can use relative cells.

Fill in the statement

There are two ways: one is manual input, the other two is automatic generation.

Automatic generation is divided into two situations. The first is the use of computerized accounting statements. The basic method is to first calculate computerized accounting statements.

Then save it as.

Excel file, then save and close, and finally use Excel to pfer the saved files.

H is a database. Its basic method is to first drop the database files in Excel, then input the analysis contents, and save them at last.

The specific process is: Excel-- opens the file -- the name of the file.

DBF suffix) -- input index and formula - save - file name - save.

It should be noted that in the Excel program, there are generally several financial reporting forms in industries, business, tourism, pportation, finance and other industries, including the balance sheet, profit and loss statement, profit distribution table, and financial statement of changes.

If you manually input, you should open a report template first, then input the original data, generate the report, and then copy it to the analysis file.

This method is characterized by no need to calculate the total amount of liquid assets, net assets, total assets, total liabilities and so on, which is 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 must be made or made by itself.

If it is a construction, real estate, joint-stock, foreign investment and other enterprises, because the system does not provide a variety of reporting templates, enterprises should also make their own.

When the above steps are completed, the result of each analysis index is automatically generated.

In order to be intuitive, the results of structural analysis and trend analysis can also be reflected in the chart.

In order to prevent the formula in the input cell from being deleted, altered or artificially altered, in order to prevent the leakage and modification of the report data, and in order for the worksheet to continue to be used next time, the format of the analysis table can be set up, namely, the protection of each cell and formula cell.

If the formula is not shown, the formula can be hidden.

The method is: first, exclude some cells (except the main column items and the items outside the column bar) without the formula.

That is to say, select - Format - cell protection - choose the above cell as not "locked";

Then, with the proposed protection with

The cell is set to lock and hide, and the method is the same; finally, the worksheet is protected, namely, tool protection -- protect worksheet -- option -- select the contents of the dialog box -- input the password -- confirm -- re-enter -- password -- confirm.

This means protection is achieved.

When you open it next time, you must enter your password.

Of course, you can also set up password and modify password protection for the entire analysis file.

Finally, it is necessary to note that if the original column and index are not applied, the financial statements can also be adjusted to increase the main column or increase the number of columns, and add additional analysis items.

However, after setting up protection, we must cancel the protection of the worksheet and rename the file after the modification.