This template enables business owners and buyers or sellers of businesses to compile an informal valuation of a business based on the cash flow projections that are automatically compiled from the template assumptions. The net annual cash flows are discounted at the weighted average cost of capital (WACC) in order to calculate a net present value (NPV), internal rate of return (IRR) and an estimated business valuation. The flexible design of the template also enables users to perform sensitivities on the projected cash flow and financing of a business in order to evaluate how much the business is worth. All valuation calculations are based on both a three year and a five year period.
Note: A formal valuation of a business usually involves a comprehensive analysis of the cash flow that is generated by the business and the inherent risk factors that may affect a business valuation. We therefore believe that it is impossible to compile a formal, independent business valuation by using any stand alone business valuation tool and the results that are produced by this template should therefore not be interpreted as a formal business valuation. This template is however a unique business valuation solution that adds immeasurable value in determining and analyzing the estimated value of a business on a discounted cash flow basis.
The following sheets are included in this template:
Assumptions - this sheet contains all the user assumptions that are required in order to compile the business valuation calculations in this template. Most of the assumptions are used in compiling the five year forecast of annual cash flows, but some important financing assumptions are also included at the bottom of the sheet.
CashFlow - the 5 year annual cash flow forecast on this sheet is automatically compiled from the assumptions that are entered on the Assumptions sheet. The only user input that is required on this sheet is the addition of expense items if the 23 default expenses are not sufficient.
Valuation - the annual cash flow projections and the financing assumptions that are entered at the bottom of the Assumptions sheet are used in the calculation of the weighted average cost of capital (WACC), net present value (NPV), internal rate of return (IRR) and estimated business valuation on this sheet. We have also included a calculation of the annual cash flow after debt repayment in order to provide an indication of the estimated net disposable income that the business can be expected to generate.
The turnover amounts that are included in the annual cash flow forecast are calculated from the year 1 amount that is entered in cell C6 on the Assumptions sheet and increased for subsequent years by applying the appropriate annual increase percentages that are specified in row 7 on the Assumptions sheet to the appropriate previous year's turnover amount.
Gross Profit %
The estimated gross profit percentages that are entered in row 9 on the Assumptions sheet are used to calculate the appropriate gross profit amounts in each year on the cash flow forecast. The cost of sales amounts are then calculated by deducting the gross profit amounts from the appropriate turnover amounts.
Note: Gross profit percentages of 100% can be entered in row 9 in order to compile a cash flow forecast for service based businesses. The turnover amounts will then be the same as the gross profit amounts and you can hide the cost of sales and gross profit rows on the CashFlow sheet if you don't want to include these calculations on the annual cash flow projections.
The template includes 23 default expense items that can be customized based on requirements. The default expense item descriptions can be edited in order to change the appropriate expense items and you can sort the expense item descriptions on the Assumptions sheet in alphabetical order after customizing the default list. The expense item descriptions and amounts on the cash flow forecast are automatically updated.
You can also add additional expense items to the default list by simply inserting a row anywhere above the "Add new expense items above this row" text in row 34. The expense items that are added above this row are automatically updated on the CashFlow sheet but you also need to insert the appropriate number of additional rows on this sheet in order to include all the expenses that are listed on the Assumptions sheet on the cash flow forecast.
The year 1 expense amounts in column C on the Assumptions sheet are automatically included in the appropriate rows on the CashFlow sheet. The year 2 to year 5 expense amounts are calculated by applying the expense increase percentages that are entered in row 35 to the appropriate expense amounts of the previous year.
Individual expense items are not covered in these instructions because the nature of expenses differs based on the nature of the business that the cash flow forecast is compiled for. We would however like to emphasize that users need to ensure that all expenses are included in the cash flow forecast because the omission of material expenses could result in an inaccurate business valuation being calculated.
Some expense items do however require special mention for the purpose of this template. If you are calculating a business valuation for a start-up business, some costs may be incurred in the first year but not in subsequent years. These start-up costs should be included in the list of expense items on the Assumptions sheet but the formulas that are used in order to calculate the expenses for subsequent years on the CashFlow sheet should be deleted and replaced by nil values.
The Salaries & Wages expense for owner managed businesses should also be carefully considered. For business valuation purposes, it is important that the amounts that are included in expenses are market related given the role that the owner intends to fulfil in the business. The salary that the owner wishes to earn from the business should not form part of this expense line item because it will distort the business valuation that is calculated.
Instead, the annual cash flow after debt repayment (estimated annual net disposable income) should be added to the owner's market related salary in order to determine the projected gross earnings (before income tax) that the owner would be able to earn from the business. The annual cash flows after debt repayment are calculated in row 26 to 28 on the Valuation sheet.
Note: Non-cash expense items like depreciation on property, plant & equipment should not be included in the cash flow projections quite simply because these items are accounting entries and not related to the cash that is generated by a business.
The working capital of a business consists of inventory, debtors and creditors balances. The movements in these balances form part of the operating cash flow of a business and are therefore included in the calculation of our annual cash flow projections. In order to calculate the movements in working capital, users therefore need to enter the estimated start-up balances (for existing businesses) and annual closing balances for each of the working capital account groups (inventory, debtors and creditors). Note that the estimated closing creditor balances need to be entered as negative values.
When this template is used to calculate an estimated business valuation for an existing business or a business that is acquired as a going concern, the start-up working capital balances should be easy to determine (based on the business accounts or acquisition agreement). If the template is however used to calculate a valuation for a business with no previous trading history, the start-up balances should be nil and the closing working capital balances for each projection year should be estimated.
We recommend applying an estimated "days" factor to the appropriate income statement items in order to calculate an accurate estimate of the appropriate working capital closing balances. For example, the closing inventory balance for each year can be calculated by dividing the cost of sales amount of the appropriate year by 365 and multiplying the result by the estimated days of inventory that is kept on hand.
Similarly, debtors closing balances can be calculated by dividing the turnover amount of the appropriate year by 365 and multiplying the result by the number of days' sales that is expected to be outstanding at the end of each year. Debtor trading terms and the ratio of cash and credit sales should also be considered in determining this estimate.
The outstanding creditors balances can also be calculated based on a number of days assumption, but users should keep in mind that this balance is influenced by inventory purchases as well as expense items and the ratio of cash and credit payments. The trading terms that are negotiated with suppliers can therefore be used in calculating the estimate but the expense line items that are paid in the same period as the expenses were incurred should also be taken into account.
All the forecasted acquisitions of property, plant & equipment (fixed assets) that do not form part of the initial business acquisition should be entered in row 41 on the Assumptions sheet. It is also important to note that you should only include fixed assets with a useful life of more than one year in capital expenditure. Assets with a useful life of less than a year should be included in the Expenses section.
All capital expenditure amounts should be entered as positive values - the amounts are automatically converted to negative values (cash outflows) on the CashFlow sheet. Note that it is the amounts that are forecasted to be spent in each year that should be included in the assumptions and not the closing balance of all fixed assets.
As we've mentioned before, depreciation should not be included as an expense item for the purpose of this template because it is not a cash expense. We do however recommend that the deterioration of the condition of fixed assets is considered when estimating the capital expenditure amounts, especially in years 4 and 5.
Acquisition Price & Financing
A business acquisition price should be entered in cell B43 on the Assumptions sheet. We recommend that an amount is entered regardless of whether a valuation is being calculated based on a business acquisition or not. If there isn't a specific business acquisition price, enter an estimated valuation for the business in this input cell. This value is required in order to calculate the WACC and therefore in order to calculate a business valuation in accordance with the principles of discounted cash flow.
Note: If you enter a nil amount as the business acquisition price, the WACC will be nil and the annual cash flows that are forecasted will be discounted by 0% in determining the value of the business. This means that the NPV and the estimated business valuation will equal the sum of the appropriate annual cash flows and will not be discounted to take the required return on investment into account. The IRR can also not be calculated if an initial capital outflow amount (business acquisition price) is not specified and will be reflected as 0%.
If the business acquisition price includes an amount for working capital, this amount should still be included in the amount that is entered in cell B43 even though the working capital balances are also entered in the working capital section of the assumptions. The full business acquisition price needs to be evaluated in the template calculations and the working capital start-up balances are only entered in the working capital section of the assumptions in order to calculate the working capital movements in year 1.
If the valuation is calculated for a business that is being acquired as a going concern and the new business will be registered for sales tax purposes, the sales tax amount that is included in the business acquisition price should be deducted from the full acquisition price. This amount will be claimed back from the appropriate tax authorities and the business acquisition price should therefore be exclusive of sales tax under these circumstances.
The next four input cells relate to the financing of the business and play an important role in the calculation of the WACC. The loan amount (amount of debt financing) should be entered in cell B44 on the Assumptions sheet. This amount is also used in order to calculate the amount of equity that will be required in order to finance the business acquisition. The equity contribution is simply the difference between the business acquisition price and the amount that is financed by debt (loan amount).
Note: For businesses that are being acquired, the loan amount should be the amount of debt financing that will be used to finance the business acquisition. For start-up businesses, the loan amount should equal the amount of financing that will be available in order to finance the initial business activities. For existing businesses, the loan amount can either be the outstanding amount on existing loan facilities or can be calculated by using the business's existing debt / equity ratio.
Note: The loan amount must be less than or equal to the business acquisition price and the business acquisition price must therefore obviously be greater than or equal to the loan amount. If you therefore want to edit the values in these input cells by entering a business acquisition price that is less than the current loan amount input cell value, you have to edit the loan amount input cell first otherwise the value that you enter will not be accepted.
The cost of debt that is entered in cell B45 should be the annual interest rate associated with the debt facility to which the loan amount in cell B44 relates. This value should therefore be entered as a percentage. The repayment period that is entered in cell B46 should be the remaining loan repayment period. This input cell is only used in the calculation of the net cash flow after debt repayment on the Valuation sheet and a minimum value of 1 should be entered in this cell in order to calculate the appropriate annual debt repayment amounts.
The required return on equity should be entered in cell B47. The value that is entered in this input cell is a subjective value that depends on the annual return that the shareholders who contribute the equity funds to the business require from their investment. The required return should be entered as a percentage before taking the effect of income tax into account. In other words, this should be the minimum return that the shareholders expect from the funds that they are investing in the business before income tax is deducted from the amounts that are returned to them.
Note: The income tax applicable to the returns of shareholders could be in the form of income tax on dividends if profits are returned to shareholders through the declaration of dividends or income tax on earnings in the case of an owner managed business if profits are distributed to the owners as remuneration (salaries & wages).
Note: A benchmark for the required return on equity is between 20% and 30%. The percentage that is specified in this input cell influences the WACC and therefore has a direct impact on the business valuation that is calculated. As we mentioned before, this is a subjective input variable and we therefore recommend that users test the effect that different return on equity percentages have on the business valuation that is calculated.
Annual Cash Flow
The annual cash flow projections on the CashFlow sheet are automatically compiled from the input values that are entered on the Assumptions sheet. The calculation of the line items that are included on the cash flow projections is covered under the Assumptions section of these instructions. The only user input that is required on the CashFlow sheet is the addition of expense items (if additional expenses have been added to the Assumptions sheet).
If additional expense items have been added on the Assumptions sheet, you may notice that not all the expense items are included in the cash flow projections. The additional expenses have to be added to the cash flow projections by inserting the appropriate number of additional rows anywhere between the existing expense rows and copying the formulas in column A to G from one of the existing rows. Note that the descriptions of the expense items below the empty rows will change after inserting the new rows but all the appropriate descriptions are included after copying the formulas.
The order in which expense items are displayed on the CashFlow sheet is exactly the same as the order in which expense items are included on the Assumptions sheet. If you therefore delete some of the expenses from the list on the Assumptions sheet, the row below the last expense item on the CashFlow sheet will contain the "Add new expense items above this row" text. You therefore need to delete this row and all the other rows below it in the Expenses section so that only valid expense items are included on the cash flow projections.
Note: We recommend that you review the completeness of expense items on the cash flow projections by ensuring that the last expense item on the Assumptions sheet is included on the CashFlow sheet.
Note: The cash outflow relating to the repayment of loans is not included on the cash flow forecast because it is included in the calculation of the weighted average cost of capital (WACC) which is used as the discount rate in calculating the NPV and estimated business valuation.
Business Valuation Calculations
The Valuation sheet contains all the business valuation calculations that are included in this template. All the calculations on this sheet are automatically calculated from the input values that are entered on the Assumptions sheet and the cash flow projections on the CashFlow sheet. No user input is required on the Valuation sheet.
All the business valuation calculations that are included in this template are calculated over both a 3 year and 5 year period. In practice, some business brokers or advisors may indicate that a three year calculation period is the norm but we believe that it is a lot more prudent to evaluate an investment over both a 3 year and 5 year period. If the business owner intends to sell the business after 3 years, the 3 year calculation should however carry more weight. If however the business owner has a longer investment period in mind, the calculations over the 5 year period should be considered the most important.
For example: In some scenarios, the business valuation calculations may indicate that a business does not provide an adequate return over a 3 year period but when the business is evaluated over a 5 year period, the investment significantly exceeds the investment return requirements. If a valuation calculation is only performed over a 3 year period, it may therefore result in the incorrect investment decision. If however the business owner has no intention of owning the business for longer than 3 years, the 5 year calculation is in effect meaningless.
Note: When you calculate a business valuation over a period as long as 5 years, you should be prudent in the assumptions that are used in compiling annual cash flow projections. This principle is especially important in relation to the capital expenditure that is included in the cash flow projections - it is important to recognize that the condition of fixed assets deteriorates over time and that some assets may have to be replaced after a certain period has elapsed. Also, if your cash flow projections include a significant increase in turnover, you should ensure that you provide for the acquisition of additional capital assets if the capacity of existing assets is insufficient in order to achieve the projected levels of turnover.
The Valuation sheet includes the following business valuation calculations:
Weighted Average Cost of Capital (WACC)
The WACC is the weighted average cost of the capital that is used to finance the business and is expressed as an annual percentage. For the purpose of this template, the WACC calculation consists of capital in the form of equity and debt.
The debt amount that is included in the WACC calculation is the same as the loan amount that is entered in cell B44 on the Assumptions sheet. The equity amount that is included in the WACC calculation is calculated by deducting the debt amount from the business acquisition price that is entered in cell B43 on the Assumptions sheet. In order to calculate the WACC of the business, we need to calculate the percentage of the business acquisition funding that can be attributed to each source of financing, apply these percentages to the cost that is associated with each financing component and sum the calculated result.
The percentage of the acquisition price that is financed by debt and equity is calculated in cells C7 and C8 and the cost of each component is included in cells D7 and D8. Note that the cost of debt and the cost of equity (or required return on equity) are specified in cells B45 and B47 on the Assumptions sheet. The WACC of the business is calculated by multiplying the percentages in cells C7 and C8 by the costs in cells D7 and D8 and adding up the resulting percentages in cells E7 and E8 in order to display the WACC in cell E9.
The WACC is the minimum annual return on investment that is required in order to cover the cost of the capital that is used to finance a business. For the purpose of a discounted cash flow calculation, the WACC is used as a discount rate in a Net Present Value (NPV) calculation in order to determine whether the estimated annual return on investment from the business provides an adequate return for the providers of debt financing (banks or other financial institutions) and contributors of equity (owners or shareholders).
Note: The WACC calculation is subjective in nature because even though the cost of debt of a business is usually determined by the costs (interest) charged by financial institutions, the required return on equity is determined by the shareholders of the business. The shareholders of one business may be satisfied with a return on equity of 20% per year, but the shareholders of another business may only be satisfied with 25% per year.
Note: The ratio in which debt and equity is contributed to a business could have a material impact on the business valuation that is calculated. The cost of debt is usually lower than the required return on equity. This is because of the higher level of risk that is associated with equity investments. If a business acquisition is financed mostly out of debt, the cost of capital (or WACC) and therefore also the required investment return would be lower. The WACC is used as the discount rate in our business valuation calculation and a lower discount rate will inevitably result in a higher business valuation. We therefore recommend that you calculate the estimated business valuation based on a number of different debt / equity combinations in order to measure the impact that the debt / equity ratio has on the calculation of the estimated business valuation.
Note: As we've mentioned before, the WACC is used as the discount rate in the business valuation calculation and the subjective nature of the required return on equity component and the subjective assumptions that are used in compiling the annual cash flow projections therefore result in a subjective calculation result. This does not mean that the business valuation that is calculated will not be accurate, it only means that the calculation result will only be as accurate as the assumptions that are entered and different required rate of return values will result in different valuation results. We therefore recommend that users test the business valuations that are calculated by entering different levels of return on equity (thereby measuring the sensitivity of this calculation variable).
Net Present Value (NPV)
The NPV calculation indicates whether a business provides an adequate return on investment and is calculated by discounting the annual cash flow projections by the WACC. If the NPV calculation results in a positive value, it means that the business provides a return in excess of the required investment return (the WACC). Conversely, if the NPV valuation results in a negative value, it means that the projected investment return is less than the required investment return and for the purpose of this template that the business acquisition price may be overstated.
The value that is returned by the NPV calculation is also important because it indicates the value by which the investment return exceeds or falls short of the required investment return. The NPV calculation can therefore be used effectively in conjunction with the IRR calculation because the one calculation indicates the value of an excess or shortfall in investment return (NPV) while the other calculation indicates the annual projected investment return in percentage terms (IRR).
The NPV calculation consists of three components, namely the WACC, the annual cash flow projections and the business acquisition price (or initial capital outlay). If changes are made to any of these components, the NPV calculation result will change. The WACC is calculated in the cell range from cells A7 to E9 on the Valuation sheet, the annual cash flow projections are compiled on the CashFlow sheet and the business acquisition price (the initial capital outlay) is entered in cell B43 on the Assumptions sheet.
Internal Rate of Return (IRR)
The IRR indicates the annual return on investment and is calculated based on the business acquisition price that is entered in cell B43 on the Assumptions sheet and the annual cash flows that are compiled on the CashFlow sheet. The calculation result is expressed as a percentage and it is important to note that this percentage represents an annual investment return (as opposed to an investment return for the entire 3 or 5 year period).
If the IRR exceeds the WACC that is calculated in cell E9, it means that the projected investment return exceeds the required investment return. Conversely, if the IRR is less than the WACC, the projected investment return is less than the required investment return. An IRR which exceeds the WACC will also result in a positive NPV value and an IRR which is less than the WACC will result in a negative NPV value.
Note: If you don't enter a business acquisition price in cell B43 on the Assumptions sheet, the IRR calculation may result in an error or in an inaccurate result. An estimated business valuation should therefore be entered in this input cell if the business acquisition price has not been determined.
Note: An IRR calculation can only be performed if the initial capital outflow (the business acquisition price) and the cash flow projections include both positive and negative values. This requirement usually does not represent a limitation in the calculation methodology because the initial capital outlay is usually a negative cash flow and the annual cash flow projections are usually positive. If both the initial capital outlay and the annual cash flow projections are negative, an IRR cannot be calculated simply because there is no return on investment. A 0% value will therefore be the calculation result.
Estimated Business Valuation
The estimated business valuation is also calculated by using the NPV function but the business acquisition price (initial capital outlay) is not included in the calculation. The calculation is therefore based only on the WACC and the annual cash flow projections. The calculation result therefore indicates what the estimated value of the projected annual cash flows are if the cash flows are discounted by the required investment return.
The estimated business valuation can therefore also be interpreted as the amount of capital that needs to be contributed in order to produce the projected annual cash flows at the required return on investment (the WACC). If the business acquisition price exceeds the estimated business valuation, it means that the required investment return cannot be achieved and vice versa.
Note: The estimated business valuation is also calculated by discounting the projected annual cash flows by the WACC (as discussed under the Net Present Value section of the instructions). As we've mentioned before, the WACC calculation is subjective because it is based on the investment return that the shareholders or business owners require. If the return on equity that is required is overstated, it may therefore result in a lower estimated business valuation being calculated and the business acquisition price may therefore seem overstated. We therefore recommend that users perform sensitivities on the required investment return input variables in order to test the impact of different levels of return on the estimated business valuation calculation.
Another way of looking at the estimated business valuation calculation is that it can be calculated by adding or subtracting the NPV calculation result from the business acquisition price (the initial capital outlay). An important point to note about this calculation methodology is that the assumption is made that the valuation can be calculated based on a constant WACC. In effect, this assumption means that the debt / equity ratio that is included in the financing of the business acquisition (business acquisition price) will remain constant even if the estimated business valuation differs from the business acquisition price. We have therefore included the equity and debt financing amounts on which the estimated business valuation is based in rows 21 and 22.
Note: If the calculated debt and equity amounts in rows 21 and 22 are not attainable, you should amend the business acquisition price and the loan amount that is specified on the Assumptions sheet in order to calculate a new WACC and then measure the impact that this adjustment has on the estimated business valuation that is calculated. In most scenarios, the impact on the estimated business valuation should not be material.
For example: If the maximum loan amount that can be obtained through debt financing is 800,000 and the estimated business valuation is based on a debt amount of 870,000, you should change the business acquisition price in cell B43 on the Assumptions sheet to the estimated business valuation that was previously calculated and retain the 800,000 loan amount in cell B44 on the Assumptions sheet. By amending the business acquisition price and keeping the loan amount constant, a new WACC will be calculated and the estimated business valuation will therefore also change. The same technique can be used to perform a number of sensitivities in order to measure the effect that different WACC calculations have on the calculation of the estimated business valuation.
Note: As you can see, this template can be used to perform a number of sensitivities on the calculation of a business valuation in accordance with the discounted cash flow methodology and therefore adds immeasurable value to the process of making a decision on whether a business acquisition price is reasonable or simply calculating an estimated valuation for an existing business. The template is therefore an extremely useful tool for the parties involved in buying a business, the parties involved in selling a business and business owners that simply want to determine what their businesses are worth!
Net Cash Flow After Debt Repayment
The net cash flow after debt repayment calculation has been included at the bottom of the Valuation sheet in order to provide users with an indication of the annual net disposable cash flow that is available after meeting debt repayment obligations. The net cash flow after debt repayment is calculated by deducting the annual debt repayments from the annual net cash flow projections that are calculated on the CashFlow sheet.
The annual debt repayment amounts are calculated from the loan amount that is entered in cell B44 on the Assumptions sheet, the interest rate (cost of debt) that is entered in cell B45 on the Assumptions sheet and the repayment period that is entered in cell B46 on the Assumptions sheet.
Note: This calculation is especially useful for owner managed businesses where the owner's market related salary has been included in the Salaries line on the annual cash flow forecast and the owner needs to determine the total earnings that can be derived from the business based on the net disposable cash flow that will be available. The market related salary can therefore be added to the net cash flow after debt repayment in order to calculate an estimated total annual earnings amount before tax.