This template enables business owners and buyers or sellers of businesses to calculate an estimated valuation of a business or company based on the discounted cash flow (DCF) method by using the weighted average cost of capital (WACC) as a discount rate for future cash flow projections over three and five year periods. The estimated business valuations are calculated by using the net present value (NPV) method with the option of including a terminal value. 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 what the business is worth.
Note: A formal valuation of a business usually involves a comprehensive analysis of the cash flow generated by the business and the inherent risk factors which may affect a business valuation. We believe that it is impossible to compile a formal, independent business valuation by using any stand alone business valuation tool and the results produced by this template should therefore not be interpreted as a formal business valuation. This template is a unique business valuation solution which adds immeasurable value in determining and analysing 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 which affect the business valuation calculations. Most of the assumptions are used in compiling the 5-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 entered on the Assumptions sheet. The only user input which is required on this sheet is the addition of expense items if the default expenses are not sufficient.
Valuation - the annual cash flow projections and the financing assumptions set at the bottom of the Assumptions sheet are used in the calculation of the estimated business valuations on this sheet. The template uses the discounted cash flow (DCF) business valuation method with the weighted average cost of capital (WACC) as the discount rate to calculate estimated business valuations based on the net present value (NPV) of future cash flows and a terminal value if applicable.
Assumptions
Business Name
The business name entered in cell B4 on the Assumptions sheet is used as a heading on all sheets.
Turnover
The turnover amounts in the annual cash flow projections are calculated from the year 1 amount entered in cell C9 on the Assumptions sheet and increased for subsequent years by applying the appropriate annual increase percentages specified in row 10 on the Assumptions sheet to the appropriate previous year's turnover amount.
Gross Profit %
The estimated gross profit percentages entered in row 12 on the Assumptions sheet are used to calculate the appropriate gross profit amounts in each year on the cash flow projections. 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 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.
Expenses
The template includes 23 default expense items which 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 37. The expense items which 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 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 which are entered in row 38 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 for which the cash flow forecast is compiled. 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 arrived at.
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 which 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 as well as expenses of a personal nature which have been included in owner managed businesses should also be carefully considered. For business valuation purposes, it is important that all expenses are market related and do not include or otherwise represent profit distribution to owners. If you want to calculate the funds which will be available for distribution to owners or shareholders, you should calculate this separately and not include these values in the cash flow forecast calculations.
Note: Non-cash expense items like depreciation on property, plant & equipment should not be included in the cash flow projections simply because these items are accounting entries and not related to the cash generated by a business.
Working Capital
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 which 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 company valuation without any 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 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 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 negotiated with suppliers can therefore be used in calculating the estimate but the extent of expense line items paid on a cash basis should also be taken into account.
Note: We have included working capital days calculations at the bottom of the CashFlow sheet so that you can determine whether your assumptions of the individual annual working capital balances on the Assumptions sheet are accurate and consistent over all annual cash projection periods.
Capital Expenditure
All the forecasted acquisitions of property, plant & equipment (fixed assets) which do not form part of the initial business acquisition should be entered in row 44 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 which 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
This section at the bottom of the Assumptions sheet contains a number of important assumptions which need to be specified in order to calculate accurate business valuations.
Cost of Capital
Enter the debt finance percentage in cell B53 and the cost of debt in cell B54. The debt finance percentage determines the level of debt which will be used to finance the business acquisition. This together with the cost of debt which is equal to the annual loan interest rate affects the calculation of the weighted average cost of capital (WACC) which is used as the discount rate for determining the present value of future cash flow projections and therefore the value of the business.
The template uses debt and equity as the sources of finance in calculating a business valuation. Equity makes up the remaining part of the funds which are required to purchase the business which is being valued and the debt finance plus the equity finance will always equal 100%. The ratio between debt and equity is important as the cost of debt is usually lower than the cost of equity and the debt equity percentage therefore has an effect on business valuations as it affects the weighted average cost of capital which is used as the discount rate.
If the cost of debt and the cost of equity are the same, the debt capital percentage would not influence the valuation calculations. If a business is therefore financed 100% through debt or the contributors of equity only require a return which is equal to the annual loan interest rate (cost of debt), the debt equity percentage will have no effect on the business valuation. This is however rarely the case as equity contributors usually require a higher return than the loan interest rate due to a higher risk which is associated with equity contributions.
The equity finance percentage in cell B55 is calculated based on the debt finance percentage entered in cell B53. The required return on equity (before taxation) should be entered in cell B56 and is effectively the cost of equity - this means that it is the required annual return on the capital invested to acquire the business which needs to be paid to the equity contributors.
The WACC is used as the discount rate of future cash flows and the general principle is that the higher the WACC, the lower the business valuation. This is because paying higher returns on equity or debt would require more cash which leaves less available to grow the business. Cost of debt is basically the same as the annual interest rate which does not usually differ by much between credit providers but the required return on equity is a lot more subjective as some investors may require a higher return than others on the equity capital that they contribute.
Note: This is especially important when buying a business as business valuation calculations need to be performed specifically based on the required return of equity contributors to determine whether paying these returns would be feasible. For sellers of businesses, using a scenario with 100% debt finance and using the cost of debt as a starting point removes some of the subjectiveness of business valuation calculations and usually results in a higher business valuation being calculated.
Profit Multiple
A profit multiple can be entered in cell B52 and is used to calculate the estimated business valuation in cell H7 on the Valuation sheet. This calculation has been included as it is sometimes used to arrive at an estimated value quickly without having to crunch a lot of numbers. The calculation basically uses an industry profit multiple and multiplies the annual earnings before interest, taxation, depreciation and amortization (EBITDA) by this value to arrive at a business or company valuation.
You therefore need to do some research to determine what a suitable profit multiple for your industry would be and enter this value in cell B52. We do not however recommend using only this valuation technique when calculating business values as it does not include as much detail as the discounted cash flow calculations which are considered to be a lot more accurate.
Terminal Value
The template accommodates the inclusion of a terminal value in the calculation of an estimated business valuation. A terminal value basically provides for the inclusion of cash flows after the 5-year cash flow projection period in the company valuation and the inclusion of a terminal value should therefore only really be considered where there is an established trading history spanning a number of years, where the risk associated with the acquisition of the business is low and where the expected benefits from the cash flows of the business are certain to be derived for a period far exceeding the 5-year annual cash flow forecast period.
The calculation of the terminal value basically adds a specified annual net cash growth rate to the year 5 net annual cash flow and divides this value by the difference between the WACC and the specified annual cash growth rate. The cash flow growth rate which is used in the calculation can be specified in cell B51 on the Assumptions sheet and you can also enter a nil value in this cell to assume a zero growth rate as a more conservative valuation basis.
Note: The above calculation approach basically includes future cash flows in perpetuity in the business valuation calculation which may not always be suitable because buyers of small to medium enterprises which have a reasonably high rate of failure and therefore a higher risk associated with acquiring or investing in them tend to require a much shorter period for making their money back in order for it to be worthwhile investing in such business acquisitions. Terminal values should therefore only be used in cases where there is a lower level of risk due to established profit history and relatively low uncertainty with regards to the consistency of future cash flows.
The terminal value calculation can be activated by simply selecting the Yes option from the list box in cell B50 on the Assumptions sheet. The calculated value is then automatically included in the valuation calculations on the Valuation sheet.
Setting a Business Value
The business valuation calculations in this template are automated but you can also use the template to evaluate a specified business value. If you want to evaluate a specified business value, you can select the Yes option from cell B48 and enter the appropriate value in cell B49 (which will change to a yellow input cell when you select the Yes option).
The net present value (NPV) and internal rate of return (IRR) calculation methodologies are then used to evaluate the business value specified and the calculation results are displayed in the section below the estimated business valuation calculations at the top of the Valuation sheet.
The IRR displays the cumulative annual investment return which is achieved based on the annual cash flows and with the specified business value as an initial capital outlay for acquiring the business. The NPV calculations display whether the cash flows are in excess of what is required at a discount rate equal to the WACC. A positive value basically means that the return on investment is in excess of the WACC while a negative value indicates that the investment return is lower than the WACC.
Note: The IRR and NPV calculations are displayed for both the business valuations calculated over five years and the business valuation calculated over three years. It is therefore important to consider the calculations over both periods.
Annual Cash Flow
The annual cash flow projections on the CashFlow sheet are automatically compiled from the input values entered on the Assumptions sheet. The calculations of the line items included on the cash flow projections are covered under the Assumptions section of these instructions. The only user input which 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, there will be too many expense items included on the CashFlow sheet but all the descriptions of all of the excess items will automatically change to "Delete this row!" which makes it easy to know which excess items to delete.
Note: We have also added conditional formatting in the cell containing the total expenses label so that this cell will be highlighted in red if the number of expenses on the Assumptions sheet does not match the number of expenses on the CashFlow sheet. If you therefore see the red highlighting, you should compare the expense items between the two sheets.
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.
Note: The working capital days calculations at the bottom of the CashFlow sheet have been included to make it easier to determine whether the inventory, debtors and creditors balances on the Assumptions sheet are accurate. You can therefore review these days calculations to determine whether these working capital balances are accurate and consistent between annual cash flow calculation periods.
Business Valuation Calculations
The Valuation sheet contains a business valuation section, an annual cash flow chart and a cash flow calculation highlights section. All the calculations on this sheet are automated based on the assumptions entered on the Assumptions sheet and the cash flow projections on the CashFlow sheet. No user input is required on the Valuation sheet. The print range is limited to the business valuation dashboard section and the dashboard data section below it is not printed.
Discounted Cash Flow, WACC and Profit Multiple
The business valuation section at the top of the sheet contains business valuation calculations based on discounted cash flow over a 3-year and 5-year period, a calculation of the weighted average cost of capital which is used as the discount rate in the discounted cash flow calculations and a business valuation calculation based on a profit multiple.
The discounted cash flow calculations are based on the annual cash flow calculations on the CashFlow sheet and uses the NPV calculation method with the WACC as discount rate to discount the future cash flows to their present value which in effect represents the value of the business. If the terminal value option is activated on the Assumptions sheet, the terminal value is calculated and included at the end of the forecast period and then also discounted by the WACC to its present value.
The section below the 3-year and 5-year discounted cash flow calculations splits the calculated business valuation into the debt and equity portions. This gives the user an indication of the level of debt and equity which is required to be invested should the business be purchased at the calculated valuation.
If the user elects to specify a business value by selecting the Yes option on the Assumptions sheet, the set value will be analysed below the calculated business valuations for 3 years and 5 years and the net present value (NPV) and the internal rate of return (IRR) will be displayed in these sections below the debt finance amounts.
A positive NPV indicates that the investment return exceeds the weighted average cost of capital and a negative NPV indicates that the investment return is below the WACC. The IRR reflects the average annual investment return if the set value is included as the initial capital outlay in the calculation. The IRR is basically the discount rate which would result in a zero (break-even) net present value and the difference between the IRR and WACC basically represents the value difference in the NPV when setting the specified value as the selling price of a business or acquisition price of a business.
Note: The IRR calculation is only displayed if the set value option is activated and there is no terminal value. If the user elects to include a terminal value in the company valuation calculation, the terminal value will be included in the dashboard instead of the IRR. The IRR calculation can however still be seen in the dashboard data section of the sheet.
The weighted average cost of capital (WACC) calculation is also included at the top of the business valuation dashboard and calculated based on the debt finance percentage, cost of debt (annual loan interest rate) and the cost of equity. All of these variables have been covered in more detail in the Assumptions section of these instructions.
Note: The WACC is used as the discount rate in the discounted cash flow calculations and there is an inverse relationship between the WACC and the calculated business value. This means that the higher the WACC, the lower the business valuation and vice versa which should make sense as an increase in the loan interest rate would result in higher loan repayments and therefore a lower value of a business servicing the loan repayments as a result of less cash available for shareholders.
The template also makes provision for the calculation of a business valuation based on a profit multiplier. These types of business valuation calculations are usually based on earnings before interest, taxation, depreciation and amortization (EBITDA) and we have therefore linked the calculation to the year 1 profit before interest and tax (PBIT) on the CashFlow sheet. Our expenses section does not include depreciation or amortization and the PBIT is therefore the same as the EBITDA.
The profit multiple which is used in the calculation can be set on the Assumptions sheet and should be based on the appropriate industry standard for your business and country. Users may need to research this online or stick to our default profit multiple. Please just note that this method of calculating a business valuation is a lot less accurate than calculations which are based on discounted cash flow and the 3-year and 5-year discounted cash flow business valuation calculations should therefore carry more weight than the valuation based on the profit multiplier.
Annual Cash Flow Chart
The 5-year cash flow projections calculated on the CashFlow sheet are included in an annual free cash flow chart on the Valuation sheet. These are the same free cash flow amounts which are used in the net present value function to calculate the company valuations or business valuations based on the discounted cash flow business valuation method.
Cash Flow Calculation Highlights
We have included this section in the business valuation dashboard to provide a one-page summary not only of the business value or company value calculated but also of some of the assumptions which were made in compiling the future cash flow projections on which the discounted cash flow calculations were based.
Dashboard Data Section (Not Printed)
The dashboard data section below the dashboard on the Valuation sheet contains some of the data on which the business valuation calculations are based. The section is for information purposes only and the default page setup excludes the cells from the printed area of the Valuations sheet.
