5 spreadsheets essential for financial advisory

0
8693
financial consulting spreadsheets
Excel Spreadsheets

One of the biggest demands in the business consulting universe is the financial side. Brazil has hundreds of companies with insolvency or administrative problems. Find out in this article 5 spreadsheets for financial consulting.

In this article we will talk about:

The financial area of ​​a business is one of the most essential parts of any business. Quite simply, if you can't control your spending or understand your break-even point, you're likely to have a hard time maintaining a healthy cash level.

5 Essential Worksheets for Financial Advising 1

In general, a financial consultancy will consist of analyzing the business cash flow, understanding the pricing, the main financial indicators and future projections to know the viability of a new business or the company's own market value. To get this result, there are 5 financial spreadsheets that can help you organize this path:

Step 1. Cash Flow Worksheet

This is the first step of any financial organization. There is no financial control without knowledge of cash flow, so it is important to know the revenues and expenses of the business. With this data, it is possible if the company is giving profit or loss, its profitability and the balance accumulated month by month. In our spreadsheet cash flow you will be able to view cash flow statements as shown below:

cash flow with bank analysis - annual cash flow

In addition, it is still possible to make specific cost analyzes, accounts payable and receivable and even the DRE (statement of income for the year). According to the groups of chart of accounts established you can have excellent vsualizations like the division of expenses shown in the chart:

5 Essential Worksheets for Financial Advising 2

What to do with this information?

  • Understand whether the company is making a profit or a loss
    • if you profit, analyze the main groups of income and see how to profit more from them
    • if you do damage, analyze the main expenditure groups and see how to reduce unnecessary costs
  • Analyze the trend of growth or decline of the business
  • View the cash requirement (working capital) for future months from accounts payable and receivable
    • In case of need of cash, evaluate if there is sufficient cash balance so the company does not go red
  • For businesses with high sales volume (commerce, retail), make daily analyzes
  • Specifically evaluate months with a higher-than-standard spend to discover cost-cutting opportunities

5 Essential Worksheets for Financial Advising 1

Step 2. Point of Equilibrium Calculation Worksheet

For those who already have a minimally decent control of their cash flow, I believe the second essential point is to find out the contribution margin and the break-even point of the business products and services. In this way, in addition to the general financial understanding, it will also be possible to analyze sales and production possibilities.

To summarize, the contribution margin is the difference between the sales price and the direct expenses, while the break-even point is the indicator that shows the amount of products or services that need to be sold for the company to have no profit and no loss. Since we usually work with companies that want to make a profit, we will always have to have bigger sales than the break even point. See an example of calculation made in our balance point calculation worksheet:

Break-even point and sales projection

Usually this calculation is very simple in cases of companies with a single product or service (Point of Balance = Fixed Costs / Contribution Margin). If your company or the one you are analyzing has more than one product or service, it will be necessary to apportion the costs between the products. Particularly I like to make this apportionment by the method of sales projections, where I take exactly the percentage projection of each product or service and divide the fixed costs following these values.

What to do with this information?

  • Ensure that products have a positive contribution margin
  • Make sure that the productive capacity of the company is greater than the breakeven point
  • Make sure there is demand greater than the breakeven point
  • Reduce direct or fixed costs according to the need to make the product / service more competitive

Step 3. Price Training Worksheet

If you already know the quantity of products or services that the company needs to sell, a third necessary step is to do a more in-depth analysis on the formation of the sale price, since it will directly influence the break-even point. In general, this is a simple view of our pricing worksheet for services:

Consulting project pricing - price formation with mark up

As with any other pricing you need to be aware of some items in this process:

  • Direct costs
  • Sale price
  • Contribution margin
  • Mark up desired
  • Fixed costs
  • Price of competitors
  • Business Positioning
  • Value perceived by customers

5 Essential Worksheets for Financial Advising 1

What to do with this information?

  • Ensure that the sales price is higher than the direct cost of the product
  • Have a competitive edge over competitors
  • If you do not have a competitive differential, compare price with those of the competition

Step 4. Economic Feasibility Study Sheet

The first three steps are the most essential for a well-done financial control, but there may be times when your client wants to go a little further than just the financial analysis of his business. Usually this occurs when there is a desire for:

  • Open a new business
  • Purchase a new machine or equipment
  • Open a point of sale in a new location
  • Realization of a project

In all these cases, as a financial adviser, your role is to find out the viability of these desires and the best way to arrive at this answer is with a economic feasibility study sheet like that of LUZ:

Analysis of scenarios in the study of economic feasibility - comparative graph of scenarios

With it, just make investment projections, expenses and revenues that you will have the key indicators of a business's viability (net present value, internal rate of return and payback).

What to do with this information?

  • Ensure a VPL above zero
  • Ensure an IRR above the discount rate
  • Evaluate if payback is within your expectations
  • Always work with optimistic and pessimistic scenarios

Step 5. Valuation Worksheet

Lastly, another need that may arise for financial advisors is that of calculating the person's business valuation. Usually this method follows a pattern similar to that of EVE, but using some other indicators and financial data. In our valuation worksheet you just have to fill in the key financial data of your business to get the results:

financial area - valuation

Although this calculation is very common for companies that are looking for the sale, acquisition or investment, it serves for other cases as in an analysis of how much your business is worth compared to the initial investment made in it.

What to do with this information?

  • Show for potential investors
  • Show for potential buyers
  • Understand if your business is worth more than your initial investment
  • Know the value of your business at market price

How do you do your financial consulting?

I would like to know your opinion here in the comments. Have you hired a financial advisor? How was the process? If you are a financial consultant, tell us what are the steps you use in your consulting.

5 Essential Worksheets for Financial Advising 1

For both cases I strongly recommend our business finance spreadsheet package.

Excel Spreadsheets

LEAVE AN ANSWER

Please, write your comment
Please enter your name