You can use the cash flow forecast and analysis starter workbook, as shown in Figures 13-1 through 13-5, to construct cash flow forecasts and analysis summaries for assets or investments for which you want to measure profitability and liquidity. To complete it for an asset or investment, develop and then enter information on the initial cash outlay needed to acquire the asset or investment, information on the cash inflows and outflows resulting from holding the asset or investment, and information on any residual cash flows from disposing of the asset or investment.
Given a set of data that includes your initial cash investment, sales and cost of sales, operating expenses, interest expenses, marginal income tax rates, depreciation and other noncash expenses, and debt principal payments and other cash nonexpenses, this starter workbook calculates the operating profit (or loss) and cash flows stemming from holding an investment. (Noncash expenses are those expenses, such as depreciation, that do not require any cash outflow. Other noncash expenses include the depletion expense of using up an intangible asset. Cash nonexpenses are those cash payments, such as debt principal payments, that represent a cash outflow but that are not considered an expense when calculating profit.) Given a set of data that includes gross residuals, transaction/disposal costs, outstanding debt, nontaxable portions of the residual, and marginal capital gains tax rates, this starter workbook calculates the capital gain (or loss) and cash flows stemming from disposing of an asset or investment. (The gross residual is the amount you can sell the asset or investment for. The marginal capital gains tax rate is the percentage that, when multiplied by the capital gains, correctly calculates the capital gains tax.) Given all of this data and your reinvestment and discount rates, the workbook calculates pretax and after-tax internal rates of return, pretax and after-tax adjusted rates of return, pretax and after-tax net present values, and the asset or investment payback period. You need some or all of this information to evaluate the economics of alternative investments and assets and to calculate overall profits (or losses), overall capital gains (or losses), and overall cash flows.
To enter your own data in the cash flow forecast and analysis starter workbook, follow these steps:
- Open the cash flow forecast and analysis starter workbook, CASHFLOW.XLS, from the companion CD.
The starter workbook initially contains the default inputs shown in Figure 13-1.
Figure 13-1. The inputs area of the cash flow forecast and analysis starter workbook. - Enter the Initial Cash Investment value.
The initial cash investment is the amount required to acquire the investment. Enter a cash outflow as a positive amount and a cash inflow as a negative amount. If you use debt to fund a portion of the purchase, the initial cash investment is probably the gross sales price of the asset less the amount of the debt. - Enter the Pretax and After-Tax Reinvestment Rate values.
The Pretax and After-Tax Reinvestment Rate figures apply to the adjusted rate of return calculations. The rates represent the forecasted returns at which interim cash flows will be reinvested over the holding period. Generally, pretax rates approximate the yields delivered by intermediate-term taxable bonds, and the after-tax rates approximate the yields delivered by intermediate-term tax-exempt bonds. You don’t want to commingle returns with different tax treatment. Both the pretax return and the interest income from a taxable bond are taxable. Both the after-tax return and the interest income from a tax-exempt bond are nontaxable. You pick bonds with intermediate maturities because the maturity of the asset or investment is typically intermediate.
Note: You use the pretax reinvestment rate to calculate pretax adjusted rates of return, so if you do not want to calculate pretax adjusted rates of return, you do not need to enter this value. And you use the after-tax reinvestment rate to calculate after-tax adjusted rates of return, so if you do not want to calculate after-tax adjusted rates of return, you do not need to enter this value. - Enter the Pretax and After-Tax Discount Rate values.
The Pretax and After-Tax Discount Rate figures apply to the net present value calculations. Generally, the pretax discount rate approximates the pretax internal rate of return delivered on assets and investments with a similar level of risk, and the after-tax discount rate approximates the after-tax internal rate of return delivered by similarly risky assets and investments. However, wide diversity continues to exist in both the theory and practice of developing and using appropriate discount rates for net present value analysis.
Note: You use the pretax discount rate value to calculate pretax net present values, so if you do not want to calculate pretax net present values, you do not need to enter this value. And you use the after-tax discount rate value to calculate after-tax net present values, so if you do not want to calculate after-tax net present values, you do not need to enter this value. - Enter the Gross Sales value forecasted for each period of the forecasting horizon.
The Gross Sales values represent the forecasted sales generated by the asset or investment over each of the periods of the forecasting horizon. You use these forecasts to estimate the income tax expense and the cash flows. Accordingly, implicit in the construction of the starter workbook is the assumption that you use cash-basis accounting for income tax purposes and for development of the sales forecasts. Enter cash inflows as positive amounts and cash outflows as negative amounts. (If you are analyzing the cash flows from financial assets, this amount might be the investment revenue forecasted for each period. If you are analyzing the cash flows from assets that deliver productivity or efficiency gains, this amount might be the cost savings forecasted for each period.) - Enter the Cost of Sales values forecasted for each period of the forecasting horizon.
The Cost of Sales values represent the forecasted costs that are tied to sales generated over the forecasting horizon. These values might include cost of goods sold, selling costs, and perhaps other variable sales costs, such as commissions owed salespeople. You use these forecasts to estimate the taxable income and the cash flows. Enter cash outflows as positive amounts and cash inflows as negative amounts. (If you are analyzing the cash flows from a financial asset or an asset that delivers productivity gains, this amount might be 0 for each period.) - Enter the Cost Center costs for Cost Centers 1, 2, and 3.
The operating expenses for Cost Centers 1, 2, and 3 represent the cash basis operating expenses for the forecasting horizon. These values might be three expense classifications related to holding the asset or investment, or they might be the total expenses for three groups of expenses. You use these forecasts to estimate the taxable income and the cash flows. In the Cost Center 1 Costs row, enter those costs that fall into the first classification or category. In the Cost Center 2 Costs row, enter those costs that fall into the second classification or category. In the Cost Center 3 Costs row, enter those costs that fall into the third classification or category. Be sure to enter cash outflows as positive amounts and cash inflows as negative amounts. - Enter the Interest Expense value, the cost of carrying any debt used to fund a portion of the asset or investment purchase.
The Interest Expense values represent the period interest expense of carrying any debt related to the asset purchase. The interest expense equals 0 when you use no debt in the asset or investment purchase. You use these forecasts to estimate the taxable income and the cash flows. - Enter the Marginal Income Tax Rate values.
The Marginal Income Tax Rate value is the percentage that, when multiplied by the operating profit (or loss) for the period, calculates the income tax expense (or savings). If you are interested only in calculating pretax profit measures, enter 0 as this amount. - Enter the Depreciation expenses included in the expense categories 1, 2, or 3 of the forecasting horizon.
The Depreciation expenses represent the amounts of depreciation included in operating expenses for Cost Centers 1, 2, and 3 for the forecasting horizon. When no depreciation is included in the operating expenses for Cost Centers 1, 2, and 3, this amount equals 0. - Enter the Other Noncash Expenses values for the forecasting horizon.
The Other Noncash Expenses values represent the amounts of noncash expenses other than depreciation, included in operating expenses for Cost Centers 1, 2, and 3 for the forecasting horizon. Examples of such noncash expenses include the depletion of natural resource assets and the amortization of intangible assets. When no other noncash expenses exist in the operating expenses for Cost Centers 1, 2, and 3, these amounts equal 0. - Enter the Debt Principal Payments values.
The Debt Principal Payments values represent the cash paid out to reduce any debt used to fund any portions of the asset or investment purchase. If you use no debt in the asset or investment purchase or you use debt for which the payments you made include only interest, these amounts equal 0. - Enter the Other Cash Nonexpenses values.
The Other Cash Nonexpenses values represent the amounts of cash nonexpenses, other than debt principal payments, that affect cash flows but not profits. Examples of such cash nonexpenses include the expenses that are not deductible for calculation of taxable profits, such as life insurance on key employees, and expenditures that are not expenses, such as deposits paid to vendors and suppliers. - Enter the Gross Residual value forecasted for each period of the forecasting horizon.
The Gross Residual values represent the figures at which you can dispose of the asset or investment on the forecasting horizon. You use these amounts to calculate the capital gains (or losses) and the liquidation cash flows. If the asset or investment cannot be liquidated except at the end of the holding period, you need to enter only the final residual forecast. - Enter the Transaction/Disposal Costs values.
The Transaction/Disposal Costs values represent any incidental expenses or costs of disposing of the asset or investment. Examples include removal costs and brokerage fees. You use these amounts to calculate the capital gains (or losses) and the liquidation cash flows. You need only enter Transaction/Disposal Costs figures for those periods for which you forecast a gross residual. - Enter the Outstanding Debt on Asset(s) values.
The Outstanding Debt on Asset(s) values represent the debt that you will pay off as a result of disposing of the asset. You need to enter outstanding debt figures only for those periods for which you forecast a gross residual. - Enter the Nontaxable Portion of Residual values.
The Nontaxable Portion of Residual values represent those amounts of the cash received upon disposal that are not subject to capital gains taxes. You use these amounts to calculate the capital gains (or losses) and the liquidation cash flows. Typically, the non-taxable portion of the residual is the net book value of the asset or investment. If no depreciation has been charged, this means the nontaxable portion equals the original cost. You need to enter the Nontaxable Portion of Residual figures only for those periods for which you forecast a gross residual. - Enter the Marginal Tax Rate on Residual value.
The Marginal Tax Rate on Residual, or capital gains rate, represents the percentage that, when multiplied by the net gain or loss stemming from the disposal of the asset, calculates the capital gains tax expense (or savings). You need to enter Marginal Tax Rate on Residual figures only for those periods for which you forecast a gross residual.
After you enter the required inputs, the starter workbook makes the calculations necessary to construct pro forma cash flow forecasts and calculate a standard set of investment measures.
Leave a Reply