Setting the Assumptions

Help for setting the assumptions in the investment and business valuation template for Excel

User Guide Navigation: Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing Investment and Business Valuation Help Topics: Setting the Assumptions Financial Data Input Analyzing and Utilizing the Results

The 'Assumptions' sheet lists the key assumptions necessary for the model to evaluate the particular investment proposal. Default values have been input here with help icons to detail the purpose and functionality of each input. Input cells on this sheet, and the rest of the workbook, are identified with a light blue color.

 

The Investment Details section allows the identification of a New or Existing investment. Choosing the New option hides the existing investment name input and will require only new investment input data to be entered in the subsequent sheets.  Alternatively, the Existing option can be used requiring both new and existing investment names and two sets of input data to be entered in subsequent sheets. This option is useful under several different analytical scenarios:

• To evaluate the difference between the current business operating model and a modified business model incorporating a new strategy, process, supply chain etc.
• To evaluate the difference between two new investment proposals. A common example would be to evaluate whether to lease or buy a new asset.
• To evaluate the impact of a change in assumptions to an investment proposal already evaluated and undertaken. Here, the new assumptions can be evaluated against the old ones.
• The first year of valuation and denomination of input values, also entered in this section, are used for display purposes in subsequent sheets.

 

The Taxation and Amortization section is used for corresponding calculations in the model. The company or business tax rate should be the legal rate, as opposed to the calculated rate actually paid by the company. Checking the box to carry forward tax credits on operational losses simply nets negative tax expenses on losses against taxable profit. The goodwill amortization period represents the period over which the company writes off goodwill arising from assets purchased for more than their book value.

 

The Capital and Cost of Capital section is used to calculate initial discount rates and capital employed for EVA analysis. The cost and levels of debt and equity are used to calculate the weighted average cost of capital for the analysis. A calculation tool is provided to assist in the calculation of the cost of equity under the Capital Asset Pricing Model. Required inputs for this include:

• The Risk-Free rate. The equivalent 1-year government bond rate can be used here.
• The Market Risk Premium. This represents the return that investors expect over and above the risk-free rate to compensate for the increased risk of holding equity. Essentially the risk-free rate plus the market risk premium represents the expected return from the equity market. This information can be obtained from historical market return data, or from estimations made by third party data and analysis providers.
• The Equity Beta. This represents the ratio of company or industry specific risk compared to the overall market risk. Essentially, an equity beta under 1 indicates less risk than the market and vice versa. It is calculated by the company or industry price volatility correlation to the market and can be obtained from historical equity price data or third party data providers.

 

Cash flows can be discounted with the cost of capital either at the end of each year, or midyear where the beginning and end cash flow levels are averaged. Midyear discounting has the benefit of averaging out large cash flow fluctuations at the beginning or end of any year.

 

The capital charge for EVA can be calculated with capital employed taken at either the beginning of the year or the average of the beginning and end year levels. Calculating the capital charge with capital as at the beginning of the year is more appropriate for EVA remuneration systems as this way management cannot simply reduce the capital charge by reducing the amount of capital employed throughout the year.

 

The final option in this section is whether to include existing asset capital in the EVA capital charge. This box should be checked when analyzing the difference between two different asset structures. However, when this option is chosen, it should be noted that it creates a discrepancy between cash flow where there is no impact of existing assets (no purchases or sale of assets) and EVA where the asset incurs an ongoing capital charge.

 

The Terminal Value section allows customization to the values and calculation method of the investments terminal value after the 5-year explicit forecasting period. If it is decided to include a terminal value in the overall evaluation, the calculation can be made using one of two alternative methods:

• Infinite. This is the traditional perpetuity calculation utilizing the cost of capital and required growth rate to infinity. The growth rate can be entered in the cell provided or left blank for which the cash flow growth rate from year 4 to 5 will be utilized.
• Finite. This unique alternative can be used when it is unreasonable to expect cash flows to grow at a prescribed rate to infinity. This is especially useful for new economy or highly competitive environments where product life cycles are short. This method requires a growth rate after year 5, like the Infinite method, and also the year that the investment reaches maturity (0 cash flow growth rate) and the year that the investment terminates (0 net cash flow). The calculation uses parabolic functions to smooth cash flow from year 6 to maturity and down to the terminal date.

 

Both methods allow the input of ongoing annual capital expenditure, which is deducted from the cash flow for each year of the terminal value calculation.

 

The Comparable Investment Score section allows for the unique setting of scoring parameters so that competing, subsequent, or previous investment proposals can be quickly compared and prioritized. Optimal value and weighting parameters should be set at the corporate level based on the organizations specific environment, strategy and priorities. Parameters should then be held constant so that investment and business case proposals can be evaluated quickly and efficiently on an even playing field.

The parameters included in the score are:

• Return on Invested Capital, as a percentage over and above the WACC.
• The Net Present Value of cash flows, as an amount in input denominations.
• The Cost/Benefit ratio, as a ratio of 5-year net cash flows generated to cash invested up front.
• The Payback period, as the number of years needed cumulative cash flow to breakeven.
• The Free cash flow after 2 years, as an amount in input denominations.

 

Company wide optimal values can be entered into the input cells next to each parameter. Overall score weightings can be altered to suit the current operating environment using the slide bars. Alternatively, one of two preset weighting systems can be selected from the dropdown box.

 

The Capital Constrained weighting option is useful for companies with scarce resources wishing to maximize the percentage return on Invested Capital. Conversely, the No Capital Constraint option is useful 'cash rich' companies without capital constraints wishing to maximize the nominal return amounts or NPV of cash flows.

Screenshot: Setting the Assumptions

Back

Next