Financial analysis template: Using Excel to edit a financial analysis form

Many en­tre­pren­eurs have their tax con­sult­ants prepare their financial analysis, or analysis, for them. However, those who have a small company usually try to do as much as possible them­selves in order to save expenses. A financial analysis template as an Excel file can help you create a financial analysis yourself every month with little effort. We offer you a financial analysis template for Excel and explain how you can fill it out and adapt it for your company.

Financial analysis is a tool for keeping track of the economic situation of a company through­out the year. For this reason, the report is usually created on a monthly basis and contains in­form­a­tion that also appears in the profit and loss account. The turnover is compared with the costs and thus a pre­lim­in­ary result is reached in several in­ter­me­di­ate steps. In addition, there are often com­par­at­ive values, for example from previous months or the previous year, which can be used to describe a de­vel­op­ment. In addition, many managing directors also add quotas to the financial analysis. This allows the per­cent­age com­pos­i­tion of profit or loss to be displayed.

The financial analysis is not a report required by law, unlike the balance sheets you need to be drawing up. Therefore, there are no specific re­quire­ments for a financial analysis. However, since investors and credit in­sti­tu­tions also often want the eval­u­ation, you should pay attention to the clarity and com­pre­hens­ib­il­ity of the financial analysis.

There is no fixed format for a financial analysis. In principle, you can set up the eval­u­ation as you see fit. However, in order for the report to be as mean­ing­ful as possible, it should contain certain key figures. The structure should also make sense. When creating the financial analysis, it is a good idea to use the structure of a profit and loss account as a guideline. First, list the positive items, the sales revenues, clean them up in the next step to eliminate the inventory changes and the material usage, and then add the other revenues. This de­term­ines the gross profit.

All costs are listed below this, and then totalled. If you subtract this amount from the gross operating profit, you receive the final operating result: the result before deduction of interest and taxes. These two items are now taken into account in the next step and you receive the pre­lim­in­ary result – this month’s profit.

This alone gives you a good overview of the economic situation of your company. However, in order for you to be able to gain further in­form­a­tion, it is worth making ad­di­tion­al entries: it is in­ter­est­ing to see how the values develop. Have costs and sales increased or decreased compared to the previous month? To do this, add values from previous months or years to the financial analysis. In addition, ratios provide in­form­a­tion on how profits or costs are made up, e.g. what pro­por­tion of the total per­form­ance you had to spend on personnel costs. If you also visualise the previous year’s figures and quotas using diagrams, you can obtain the most important in­form­a­tion at a glance.

Our Excel template contains several functions that simplify the work on the financial analysis. Totals, sub­trac­tions, divisions, and diagrams are filled in auto­mat­ic­ally. However, this also means that you must ensure that you do not ac­ci­dent­ally delete or change the functions when adjusting the template. Therefore, you should know which auto­mat­isms we have included in the Excel file.

• Totals: In­ter­me­di­ate results are cal­cu­lated using the totals formula. Values in a selected range above the result cell are added.
• Sub­trac­tions: In the financial analysis, specific subtotals are sub­trac­ted from other results. This is done in the Excel template by simply sub­tract­ing two cells.
• Divisions: A simple division is used to calculate the quotients. The cor­res­pond­ing cell is divided by another specific source (for example, the content of the total power). In addition, the cell must be formatted as a per­cent­age.
• Diagrams: The given diagrams also refer to the in­form­a­tion in the cells. If these are changed, the diagrams also adapt.

You can easily enter the costs and revenues yourself. But what if you have to enter ad­di­tion­al items or certain in­form­a­tion is not relevant for you?

If you want to add ad­di­tion­al items, you must insert complete rows. This is done by right-clicking on the row numbering and then on “insert cell.” An empty cell will then be inserted above the row you selected. Remember that this also adjusts the cell spe­cific­a­tions in the formulas. Since the functions refer to specific cells, they must change ac­cord­ingly. Therefore, check whether all results are still displayed correctly. If the financial analysis template contains items that you do not need, remove them by deleting the entire row. This also changes both absolute and relative ref­er­ences.

It is not easy to remove results that are important for further cal­cu­la­tions. If, for example, you want to adjust your financial analysis so that the subtotals “operating result” and “profit before tax” are not included, you must also adjust the formula behind the pre­lim­in­ary result. This cell extracts your data from the two fields mentioned. If you delete these two, the #REF error occurs.

You don’t ne­ces­sar­ily have to remove rows – or even columns – com­pletely. If you want to keep your financial analysis slim, you can simply hide the cor­res­pond­ing areas. To do this, select the column or row (again right-click on the row numbering at the edge) and select “hide.” The area is no longer visible, but the cor­res­pond­ing values and functions still exist. Excel leaves a small column in the border area, where the hidden area is located. If you click the right mouse button again at this point, you can show the area again.

Let’s say you want to add more months to your financial analysis. So that you do not have to re-enter all functions and format­ting, you can transfer this in­form­a­tion. To do this, select all the columns that affect the month on the far right, left click the red square in the lower right corner, and fill in the next columns. Excel has now trans­ferred everything to the new area and adapted the cell ref­er­ences as well.

Al­tern­at­ively, you can select, copy, and paste the filled area into the empty cells. The result is the same. Now, of course, the wrong values are still in the cells. As usual, you exchange them and the Excel table takes over the cal­cu­la­tion again.

Two graphs in par­tic­u­lar are of interest in the context of a financial analysis: a line diagram showing the de­vel­op­ment over time and a pie chart comparing revenues and costs. You can customise both diagrams according to your needs. In addition to purely cosmetic changes, you should be par­tic­u­larly in­ter­ested in which data is displayed. You can determine this using the diagram filter. If you click on the chart in Excel, three symbols appear: The funnel-shaped icon rep­res­ents the diagram filter. By ac­tiv­at­ing and de­ac­tiv­at­ing the check boxes you can add or remove further data.

If, for example, you want to display each cost item in­di­vidu­ally instead of the total value, activate these boxes and uncheck the box for total costs. In this way, you can display your eval­u­ation in the way that makes most sense for your company.

Please note the legal dis­claim­er relating to this article.