| Home > Investment and Business Valuation User Guide > Financial Data Input Help Topic | Share |
| 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 |
After setting the assumptions for the valuation, clicking the 'Next' button in the Assumptions sheet will take you either to the 'Before' sheet, if you have chosen the 'existing investment' option, or directly to the 'After' sheet for new investment valuations. The 'Before' and 'After sheets are identical requiring the same input, and the 'Results' sheet simply calculates the difference between the two. The following information, therefore applies to both sheets. Like the 'Assumptions' sheet, all input cells are identified with a light blue color.
The Taxable Revenue section allows for the input of revenue items that are deemed assessable for taxation purposes. While two lines of input are immediately available, more can be added by clicking the Plus icon to the left. In a similar fashion, lines can be removed by clicking the Minus icon whereby any input data in the bottom input line with be deleted. This functionality also applies to the Non-Taxable Revenue and Operating Expenses sections below.
Input data for taxable revenue can be linked from supplementary spreadsheets and may typically include sales revenue calculated from forecast sales volume and price data. The Regression Forecasting model serves as an excellent tool for producing such forecast data and can be downloaded directly using the link provided to the right.
It should be noted that, unlike the following sections, input for taxable revenue is only available in years 1 to 5, and not in year 0. This is on the basis that costs may be incurred up-front (in year 0), but resulting revenues will not received until the first year of the forecast period.
The Non-Taxable Revenue section should be used to input revenues that are not assessable for tax purposes, and expenses that are non-deductible for tax purposes (entered as negatives for positive expenses). A further input for this section is any change in Working Capital that may be applicable to the analysis. Working capital is the difference between Current Assets and Current Liabilities and often arises from differences in the cash flow timing of payments to trade creditors for raw materials and inventories (liabilities) and customer settling of accounts receivable (assets). This information is typically obtained by using historical average days for settlement of creditor accounts payable and accounts receivable applied to forecast raw material purchase and forecast sales respectively. The correct calculation for the change in working capital in year t is:
- ([current assets(t)-current liabilities(t)] – [current assets(t-1)-current liabilities(t-1)])
The Operating Expenses section should be used for tax deductible expenses required for the ongoing operation of the business and resulting revenue generation. Typically, operating expenses include costs such as raw materials, transport, personnel, marketing, office supplies, legal and administration costs. Specifically, operating expenses entered here should not be investments for future revenue generation or cost savings as outlined in the Investment Expenses section below. While including such 'investment' expenses here will not have any effect on cash flow, it will affect the calculation of Economic Value Added by excluding them from the capital employed.
The Investment Expenses section is used for inputting tax deductible costs that essentially represent investments in future revenue generation or cost savings. Typically, such costs include restructuring and redundancy costs and enhancements to existing assets. In order to determine whether an expense is an 'investment' expense one should ask, 'Is this cost required for the current ongoing operation of the business?' (It is an operation expense), or 'Will the benefits of making this cost not be realized until subsequent years?' (It is an investment expense).
These costs are treated differently to operating expenses under the Economic Value Added calculation due to their quasi capital type nature. Instead of being expensed when incurred, in the EVA calculation they are capitalized and amortized over the period in which the benefits from the investment are expected to be realized. Up to three different types of Investment Expense can be entered here with different amortization periods ranging between 1 and 10 years.
The Existing Assets section allows for the input of any existing assets that need to be taken into account for the valuation. This is useful for valuing current versus proposed asset structures or lease versus ownership scenarios. Up to five existing assets can be entered here with input parameters provided across the columns. The first category of parameters deals with asset life and disposal:
It should be noted that any forecast sale of assets can be input here by manipulating the Life to equal the current Age plus remaining time to sale, and the Disposal value as a percentage of the expected sale price to original purchase price.
The next category of parameters deals with accounting depreciation conventions:
The next category of parameters deals with tax depreciation conventions:
The final parameter is for any unamortized portion of goodwill for the asset. Goodwill arises when an asset is purchased for more than its book value. The difference is amortized over a specified period.
The Capital Expenditure section allows for input of expected purchases of new assets. It should be noted that any capital commitments (from asset purchases or capitalized investment expenses) remaining after year 5 are taken into account to properly capture the impact of the capital investment activities undertaken during the explicit forecast period. This is accomplished by calculating the present value of such remaining commitments as at the end of year 5. Up to five new asset purchases can be entered here with input parameters provided across the columns.
The first category of parameters deals with the asset purchase, life and disposal:
It should be noted that, like the 'Existing Asset' section, any forecast sale of new assets can be input here by manipulating the Life to equal the time to sale, and the Disposal value as a percentage of the expected sale price to original purchase price.
The next two categories of parameters deal with accounting and tax depreciation conventions:
The final parameter specifies the expected mix of funding for the purchase of the new asset. This is input as a percentage of Equity to total (debt plus equity) funding for the purchase. The default value here is the equity to total capital ratio as specified in the 'Assumptions' sheet. This is used for calculating the ongoing capital and cost of capital.
The Financial Analysis section summarizes the financial impact of the input data, in three formats:
The Cash Flow Analysis and Economic Value Added include an additional calculation for the PV of capital commitments after 5 years. This is to ensure that any remaining impacts of capital structure changes (made during the forecast period) are taken into account in the present value calculation. Detailed calculations for components of these analyzes can be viewed by checking the View boxes below to unhide the calculation categories.
| Back | Next |
){kind=link}