Financial Management: What is and how to do with a spreadsheet?

complete financial management
Excel Spreadsheets

What is financial management?

A gestão financeira is one of the main activities linked to management and exists in any company. I do not know a business that works well without an organization in its financial area. I say this because all internal relations (wages, benefits, bonuses, etc.) and external (sales of products or services, payment of suppliers, etc.) are based on the movement of money.

Financial Management: What is and how to do with a spreadsheet? 1

That is, if you do not control how, when, how much and where this money is going, you will most likely be short-sighted and at serious risk of having working capital problems, lack of cash, excessive installments, understanding of future entries and so on.

Business Spreadsheets

Financial management will be responsible for analyzing and making decisions based on information about the finances of the company as a whole and its main functions involve:

  • Organization Chart of Accounts
  • Control of financial launches
  • Statements of cash flow (cash flow)
  • Statements of accrual basis (statement of income for the year)
  • Future payments and receipts (accounts payable and receivable)
  • Financial organization (cost centers, control of banks, forms of payments, customers and suppliers)
  • Financial planning (budget control)
  • Decision making on investments and financing

It is not necessary for all companies to control all these functions. The truth is that the level of financial management will depend on the characteristics and size of the business in question. Anyway, if you are not aligned with some or all of these points, you may need to rethink how your business finances are happening.

Greater difficulties in financial management

Usually when I talk to clients of LUZ there are two major difficulties with regard to the financial management of their companies. One is a clear way of analyzing your indicators. Very few customers arrive at the end of the month with reports like the charts in our complete financial management worksheet. Here's how a graphical analysis can help you get insights:

Complete Financial Management - Planned vs. Performed Charts

A second chronic problem is the lack of detail and understanding of what actually happens. Usually these managers can even know the end result of the company, but they fail to understand simple things like:

  • View of the cash regime against accrual basis (cash flow vs. DRE)
  • View of payment methods (how much debit, credit, money, checks, etc.)
  • Banks view (in cases where the company works with more than one bank account)
  • Cost center view (to understand which areas are most profitable)
  • Vision of customers and suppliers (who are the best customers and cheapest suppliers)

Business Spreadsheets
All this misunderstanding is often the difference between good decision making and burning money with actions that should never have gone forward if a basic knowledge of the business existed.

Step by step to complete financial management in cash flow

That's why understanding how to organize your finances in an organized way is essential for any company and now I'll show you how to use the complete financial management worksheet to arrive at this result using the example of a consultancy:

1 - Organization Chart of Accounts

The first step of any financial is the categorization and classification of your items of income and expenses. This is the only way to identify which groups of revenues and expenses are most important to the company. Let's start with the revenue classification. If we think about this type of sector, we can have consulting services and in some cases it is also common to find revenues from selling products like books.

complete financial management - chart of accounts revenue

In the case of expenses, we will have the direct expenses (which are directly linked to the rendering of services or manufacturing of products). Here we list expenses that only happen when the provision of the consultancy occurs or when there is the production of a new consignment of books:

complete financial management - direct expenses account plan

and fixed (or indirect) expenses, which are usually divided by the categories of human resources, operating expenses and marketing:

complete financial management - chart of fixed expenses

For purposes of using the complete financial management worksheet, it is still possible to organize the chart of accounts with information about the banks used, payment methods, cost centers, customers and suppliers of the consultancy in question:

complete financial management - bank account plan

This is an example for a consulting firm, but in your case it could be a series of other items. The most important thing is that you have the notion of simplification. The fewer items you register, the easier it will be to do the reviews and find the best opportunities and financial bottlenecks.

As soon as you organize the chart of accounts, you will not have to deal with it again. This is the basis for filling in the rest of the worksheet. Throughout the year an unplanned revenue or expense may arise. If this is the case, in the next financial review, it is worth adding this item to the chart of accounts.

2 - Input and Output Control

This is one of the most complicated parts to do well, because it takes a great commitment to fulfill every day the income and expenses that your company has. Below I listed a group of revenues and expenses for our consulting services for the month of January (and this is exactly what you should do when organizing your financial management):

complete financial management - January launches

Note that for each item there are a number of fields to fill. For our first example, the sale of the book "Be a Consultant", we have to mark on which day the sale happened, what type of revenue is (in this case, revenue with products), which item in the chart of accounts (5.000), the payment status (paid or unpaid), if it belongs to a cost center, which customer purchased (1 client), to which bank the transaction occurred (Itaú), the form of payment (in this case, money) and if there was any discount due to this form of payment.

Complete Financial Management - Example Launch

Having done this for each of the inputs, the complete financial management worksheet will generate specific reports that can be analyzed further ahead. To continue filling in, when you have finished one month, you fill in the data for the following month. In our case, February:

complete financial management - February releases

Note that this month has more launches than the month of January and that in the column of customers and suppliers, some lines were not filled, this happened because they are internal expenses like wages, meter and marketing that we decided not to count for any supplier, since it is not relevant.

After this insertion, in each month you can see a general summary, which shows the balance of the previous month, total revenues, expenses, the result of the month and the accumulated:

complete financial management - summary of launches

This is a natural flow to any financial area, no matter if you are using our worksheet or not, the important thing here is the notion of tracking expenses and receipts.

Financial Management: What is and how to do with a spreadsheet? 1

3 - Cash Flow Analysis

Now, if you have completed the first two steps perfectly, it is time to do more in-depth analysis. For this, it is important to have access to the cash flow statement. This is a table that shows only the revenues and expenses that actually happened and have already entered or exited your bank account.

For example, if you made an installment purchase of $ 5.000 in 5 times and only paid the first installment, the cash flow should only compute $ 1.000 (the other $ 4.000 has not yet gone out of the account). Here's a preview of this statement:

complete financial management - cash flow statement

The most important thing is to understand what kind of insight an FC can provide you. In our case specifically, we have the month of March as the only month with loss of the whole year.

Important Measures Regarding Cash Flow Analysis

If we wanted, we could go back to the postings section and analyze the data that may have made our consulting company lose money in March.

In a quick analysis I can see that the month of March had an increase of expenses in relation to the month of February. In addition, as I said earlier, cash flow only matters the costs that were actually paid. Therefore, we can enter the March releases tab and filter the expenses that were paid:

complete financial management - cash flow statement analysis

After making the filters, I can see that there was a higher payment of salaries, the result of thirteenth others in arrears. Since this is a one-off expense, it does not cause concern for future months, but if it were another type of expense, an expense reduction analysis could be done.

Business Spreadsheets

4 - Analysis of the DRE (Statement of Income for the Year)

The second most important statement is the DRE, which shows a view of its revenue and expenditure by the competency regime (check the differences between the cash regime and the competence here). Once again, with the demo table ready, it is possible to have insights.

complete financial management - statement of profit and loss for the year - DRE

In our case, the month of September is the only one that has a negative result.

Important steps in relation to the DRE analysis

Now it is worth looking at the detail of the accounts, to know if there is any launch that is influencing this negative result. Once again, in the complete financial management worksheet you can analyze this data. See below:

complete financial management - analysis of the statement of income for the year - DRE

Clearly all values ​​follow proportional changes from month to month, except for the amounts spent on online marketing in September (cell marked), which were R $ 40.000 compared to the other months that reached a maximum of R $ 8.000.

From this analysis, our consulting firm should review as quickly as possible why online marketing spending was high and change the budget for the next month.

5 - Must have control of accounts payable and receivable

A third form of analysis is the cash requirement generated by accounts payable and receivable. Basically understanding this item is simple, if you have more bills to pay than to receive in a given month, that means you need extra money that month.

See the statement of future receipts and payments. Here we have 2 months that will require extra cash inflow or a previous capital accumulation.

complete financial management - statement of accounts payable and receivable

Important Measures in Accounts Payable and Receivable

If it is known to the company that in certain months there may be losses, it is important to take steps to try to reduce future expenses, to pay salaries for that month or even to try to make a special sales effort to compensate for the imminent loss.

Financial Management: What is and how to do with a spreadsheet? 1

6 - Other important reviews

In addition to the reviews and demos that I have shown so far, we still have a number of other possibilities that can help and a lot in the best possible financial behavior of the company. Some of these statements are:

  • Cash Flow by Banks

complete financial management - banking analysis

Knowing that Itaú's cash flow is negative throughout the year and that of Banco Santander is positive throughout its fiscal year, it is worthwhile to make transfers from one bank to another to maintain the financial health of both banks. even to modify the bank to which the revenues are intended in some cases.

  • Cash Flow by Cost Centers

Although we have included generic cost centers in our example, imagine that the cost center is related to a specific product or service. In that case you could have the exact information whether that service is positive or not for the company as a whole.

In our example, imagine which 1 cost center refers to the financial advisory service. In this case, we would know that in general it was positive to maintain this service in our portfolio.

complete financial management - cost center analysis

It is still possible (and desirable) to do analyzes by:

  • Ways you can pay
  • Customers and Suppliers

7 - Financial Projection

So far we were talking about financial control and results that actually occurred or that had at least been recorded under the accrual basis. At that point we arrived at a second stage of financial management, which involves planning future revenues and expenses.

If you have the accounts of your result accomplished, you need to project to the future months how much you can get to receive and spend. This process of how to make the financial projection involves some data, perhaps the most important are:

  • Historical result
  • Seasonality
  • Percent growth
  • Potential investments

With them, it is possible to arrive at a monthly follow-up of your planned and realized results:

complete financial management - financial projection

Obviously, with this result in hand it is possible to perfect your forecasting exercise, adjust action plans and your financial planning and budgeting of the company as a whole and the areas specifically.

8 - Graphics Analysis

Finally, to close, nothing better than having all or most of that information separated and organized into charts. This graphical analysis allows a quick understanding of the financial reality of your company, the best and worst months, as well as how its structure is organized.

Business Spreadsheets

Below is a chart of our consultant's general cash flow:

complete financial management - general graphical analysis

We quickly realized that our best month of revenue (and profit) was December, while looking at our profit / loss line, we can see that the month of March is the only one below 0, indicating a loss.

We could have a similar analysis for the payables and receivables chart, which always shows a bad indicator when the red line is above the green line (February and September months):

complete financial management - graphical analysis accounts payable and receivable

And to close our chart analysis, we could also see a breakdown of the expense and revenue categories to know the most relevant ones. In our example below, I separated the expenses division for the month of January, which shows 66% of total expenses with services and human resources expenses and 16% with operating expenses.

complete financial management - graphic analysis expenses

Is your financial management complete?

My idea with this article was to show you all the possibilities to have a well-structured, organized financial that is able to provide you insights and insights for assertive decision making. For that I used our complete financial management sheet.

Financial Management: What is and how to do with a spreadsheet? 1

This is not to say that you need to control all of these items in your company, you simply understand what is really important in your business and do this monitoring, control and financial planning.

Excel Spreadsheets


Please, write your comment
Please enter your name