The cash flow forecast and analysis starter workbook has eight parts: the inputs area, the Profit and Loss Statement, the Gain and Loss Statement, the Operating Cash Flow Statement, the Liquidation Cash Flow Statement, the Cash Flow Analysis, the Pretax Cash Flow Scenarios, and the After-Tax Cash Flow Scenarios.
Cash Flow Forecasting Inputs
Only one set of formulas exists in the inputs area of the cash flow forecast and analysis starter workbook: the one in the second row that identifies the period for which the results are calculated. The rest of the rows contain input cells where you can enter your own data. Unless you turn off cell protection, these are the only cells in which you can enter data.
The period identifier simply numbers the periods forecasted. The start of the first period is stored as the integer 0. (Using 0 as the starting balance is the traditional way to identify those cash flows that are not discounted because they occur at the beginning of the forecasting horizon.) Periods that follow are stored as the previous period plus 1.
Note: The period identifiers are formatted with a custom format that places the word period in front of the period identifier. If you want to change this, simply use another number format.
Profit and Loss Statement
The Profit and Loss Statement schedule has 12 rows that contain calculated data (see Figure 13-2).
Gross Sales
The Gross Sales figures show the sales estimates. You enter this amount in the inputs area of the starter workbook. The Profit and Loss Statement simply references the input value you supply.
Less: Cost of Sales
The Cost of Sales figures show the cost of sales estimates. You enter this amount in the inputs area of the starter workbook. Again, the Profit and Loss Statement simply references the input value you supply.
Gross Margin
The Gross Margin figures show the amounts left over from the sales proceeds after paying for the cost of sales. The Gross Margin figures represent the amount of cash that goes toward paying your other expenses and your profits.
The Gross Margin value for each period is the Gross Sales figure for the period less the Cost of Sales figure. But because the Cost of Sales figures are pulled into the Profit and Loss Statement as negative amounts, the Gross Margin formula simply adds the positive Gross Sales figure to the negative Cost of Sales figure. For example, the formula for the first period is:
=C26+C27
The formula for the second period is:
=D26+D27
and so on.
Operating Expenses – Cost Centers 1, 2, and 3
The figures in these three rows show the amounts of the operating expenses for the three categories entered in the inputs area of the starter workbook.
Total Operating Expenses
The Total Operating Expenses figures show the sums of the operating expenses entered in the inputs area of the starter workbook for the three expense categories. For example, the formula for the first period is:
=SUM(C31:C33)
The formula for the second period is:
=SUM(D31:D33)
and so on.
Operating Income
The Operating Income figures show the amounts of sales dollars left after paying the cost of sales and the operating expenses. The Operating Income figures represent the amounts that go toward paying financing expenses, income taxes, and profits.
The Operating Income value for each period is the Gross Margin figure for the period minus the Total Operating Expenses figure. For example, the formula for the first period is:
=C28-C34
The formula for the second period is:
=D28-D34
and so on.
Interest Expense
The Interest Expense figures show the amounts required to carry any debt used to fund portions of your asset or investment purchase. If you used no debt to fund the purchase, these amounts are 0.
The Interest Expense value for each period is the value you enter in the inputs area of the starter workbook.
Net Income (Loss) Before Taxes
The Net Income (Loss) Before Taxes figures represent the amounts of operating income left after paying any interest expense. These amounts represent your taxable operating profits.
The Net Income (Loss) Before Taxes figure for each period is the Operating Income figure minus the Interest Expense figure. For example, the formula for the first period is:
=C35-C37
The formula for the second period is:
=D35-D37
and so on.
Income Tax Expenses (Savings)
The Income Tax Expenses (Savings) figures show the forecasted income tax expenses (or savings) using the pretax operating profits calculated and the marginal income tax rates entered in the inputs area of the starter workbook.
The Income Tax Expenses (Savings) figure for each period is the Net Income (Loss) Before Taxes figure multiplied by the Marginal Income Tax Rate figure. For example, the formula for the first period is:
=C14*C38
The formula for the second period is:
=D14*D38
and so on.
Net Income (Loss) After Taxes
The Net Income (Loss) After Taxes figures show the after-tax profits of holding the asset or investment.
The Net Income (Loss) After Taxes value for each period is the Net Income (Loss) Before Taxes figure minus the Income Tax Expenses (Savings) figure. For example, the formula for the first period is:
=C38-40
The formula for the second period is:
=D38-D40
and so on.
Gain and Loss Statement
The Gain and Loss Statement schedule has seven rows of calculated data (see Figure 13-2).
Gross Residual
The Gross Residual figures show the total amounts for which the asset or investment can be liquidated for each period of the forecasting horizon. You enter these figures in the inputs area of the starter workbook.
Less: Transaction/Disposal Costs
The Transaction/Disposal Costs figures show the costs associated with liquidating the asset or investment for each period of the forecasting horizon. You enter these figures in the inputs area of the starter workbook.
Net Residual
The Net Residual figures are the amounts left over from liquidating an asset or investment after paying any transaction or disposal costs, using the Gross Residual figures and the Transaction/Disposal Costs figures.
The Net Residual figure for each period is the Gross Residual figure minus the Transaction/Disposal Costs figure. But because the Transaction/Disposal Costs figure is pulled into the Gain and Loss Statement schedule as a negative amount, the Net Residual formula simply adds the positive Gross Residual figure to the negative Transaction/Disposal Costs figure. For example, the formula for the first period is:
=C45+C46
The formula for the second period is:
=D45+D46
and so on.
Nontaxable Portion of Residual
The Nontaxable Portion of Residual figures show the amounts of the residuals that are not included in capital gains or losses calculations. You enter these figures in the inputs area of the starter workbook.
Pretax Gain (Loss) on Disposal
The Pretax Gain (Loss) on Disposal figures are the capital gains or losses that must be included in capital gains tax calculations.
The Pretax Gain (Loss) on Disposal figure for each period is the Net Residual figure minus the Nontaxable Portion of Residual figure. For example, the formula for the first period is:
=C47-C49
The formula for the second period is:
=D47-D49
and so on.
Income Tax Expenses (Savings)
The Income Tax Expenses (Savings) figures represent the tax effect of the liquidation of the asset or investment, calculated by using the Pretax Gain (Loss) on Disposal figures and the Marginal Income Tax Rate figures entered in the inputs area of the starter workbook.
The Income Tax Expenses (Savings) value for each period is calculated by multiplying the Marginal Tax Rate on Residual figure by the Pretax Gain (Loss) on Disposal figure. For example, the formula for the first period is:
=C50*C23
The formula for the second period is:
=D50*D23
and so on.
After-Tax Gain (Loss) on Disposal
The After-Tax Gain (Loss) on Disposal figures show the after-tax profit (or loss) from liquidating the asset or investment.
The After-Tax Gain (Loss) on Disposal value for each period is the Pretax Gain (Loss) on Disposal figure minus the Income Tax Expenses (Savings) figure stemming from the disposal. For example, the formula for the first period is:
=C50-C52
The formula for the second period is:
=D50-D52
and so on.
Operating Cash Flow Statement
The Operating Cash Flow Statement schedule has eight rows with calculated data (see Figure 13-3).
Net Income Before Taxes
The Net Income Before Taxes figure shows the pretax profits calculated on the Profit and Loss Statement.
Addbacks of Noncash Expenses – Depreciation
The Depreciation figures show the depreciation expenses included in the three operating expense classifications or categories. You enter these amounts in the inputs area of the starter workbook.
Addbacks of Noncash Expenses – Other
The Other Noncash Expenses figures show the other noncash expenses included in the three operating expense categories. You enter these amounts in the inputs area of the starter workbook.
Deducts of Cash Nonexpenses – Debt Principal Payments
The Debt Principal payments figures show the debt principal payments made to reduce the debt used to fund a portion of the asset or investment purchase. You enter these amounts in the inputs area of the starter workbook.
Deducts of Cash Nonexpenses – Other
The Other Cash Nonexpense figures show the other cash payments you made that were not expenses and, therefore, were not included in the three operating expense categories. You enter these amounts in the inputs area of the starter workbook.
Pretax Operating Cash Flow
The Pretax Operating Cash Flow figures are the pretax cash expended or received as a result of holding the asset or investment. The first cash flow figure shows the initial cash outlay needed to acquire the asset or investment. The second and subsequent cash flow figures show the pretax operating cash flow figures. The Cash Flow Analysis Statement schedule uses these pretax cash flows to calculate the pretax profitability and liquidity measures.
The Pretax Operating Cash Flow value for Period 0, the initial cash investment required to acquire the investment, is the value you enter in the inputs area of the starter workbook. Notice that this amount is pulled into the Operating Cash Flow Statement schedule as a negative amount because it is an outflow. The Pretax Operating Cash Flow values for subsequent periods are calculated by adding noncash expenses to the net income (Loss) Be- fore Taxes figure and subtracting the Other Cash Nonexpenses figure from the Net Income (Loss) Before Taxes figure. For example, the formula for the first period is:
=C58+C60+C61-C63-C64
The formula for the second period is:
=D58+D60+D61-D63-D64
and so on.
Income Tax Expenses (Savings)
The Income Tax Expenses (Savings) figures show the income tax expenses (or savings) calculated in the Profit and Loss Statement schedule.
After-Tax Operating Cash Flow
The After-Tax Operating Cash Flow figures are calculated by using the Pretax Operating Cash Flow figures and the Income Tax Expenses (Savings) figures. The Cash Flow Analysis schedule uses these after-tax cash flows to calculate the after-tax profitability and liquidity measures.
The After-Tax Operating Cash Flow value for Period 0, the initial cash investment, is pulled from the cell containing the pretax operating cash flow (B65). The figures for subsequent periods are calculated as the Pretax Operating Cash Flow figure minus the Income Tax Expenses (Savings) figure. For example, the first-period formula is:
=C65-C67
The formula for the second period is:
=D65-D67
and so on.
Liquidation Cash Flow Statement
The Liquidation Cash Flow Statement schedule has six rows with calculated data (see Figure 13-3).
Gross Residual
The Gross Residual figures show the amounts for which the asset or investment can be sold. You enter these amounts in the inputs area of the starter workbook.
Less: Transaction/Sales Costs
The Transaction/Sales Costs figures show the expenses of liquidating the asset or investment. You enter these amounts in the inputs area of the starter workbook.
Less: Outstanding Debt
The Outstanding Debt figures show the principal balances of any debt used to fund portions of the asset or investment purchase. You enter these amounts in the inputs area of the starter workbook.
Pretax Liquidation Cash Flow
The Pretax Liquidation Cash Flow figure is calculated as the Gross Residual figure minus the Transaction/Sales Costs figure and minus the Outstanding Debt figure. Because the Transaction/Sales Costs figure and the Outstanding Debt figure are pulled into the Liquidation Cash Flow Statement schedule as negative amounts, the Pretax Liquidation Cash Flow formula simply adds the Gross Residual figure to the negative Transaction/Sales Costs figure and the negative Outstanding Debt figure. For example, the formula for the first period is:
=C72+C73+C74
The formula for the second period is:
=D72+D73+D74
and so on.
Income Tax Expenses (Savings)
Income Tax Expenses (Savings) figures show any capital gains taxes associated with liquidating the asset or investment.
The figure for each period is the value calculated in the Gain and Loss Statement schedule.
After-Tax Liquidation Cash Flow
The After-Tax Liquidation Cash Flow figures are the after-tax cash received as a result of liquidating an asset or investment at the end of the period.
The After-Tax Liquidation Cash Flow value for each period is the Pretax Liquidation Cash Flow figure minus the Income Tax expenses (Savings) figure. For example, the formula for the first period is:
=C75-C77
The formula for the second period is:
=D75-D77
and so on.
Cash Flow Analysis
The Cash Flow Analysis schedule calculates the profitability and liquidity measures for each of the alternative holding periods (see Figure 13-4). The schedule has 10 rows with calculated data. The values for Pretax IRR, After-Tax IRR, Pretax Adjusted IRR, After-Tax Adjusted IRR, Pretax Net Present Value, and After-Tax Net Present Value are similar in that the value shown in the Period 1 column assumes that the asset or investment is pur- chased at the beginning of the first period (Period 0), is held for one period, and then is sold at the end of the first period. Similarly, the values shown in the subsequent period columns assume that the asset or investment is purchased at the beginning of the first period and then is sold at the end of the indicated period. These values often fluctuate, depending on the holding period. By developing and examining the values delivered by the asset or investment under alternative holding periods, you can choose holding periods that enhance profits. For example, a 10% pretax internal rate of return of an asset or investment held for three years means you get back not only all your initial investment but a dime a year for every dollar invested. An 8% pretax internal rate of return for the same asset or investment held for four years means you get back all your initial investment but only eight cents for every dollar invested a year.
Pretax IRR
The Pretax IRR figures are the pretax internal rates of return, which are calculated by using the pretax operating and liquidation cash flows that are generated by the asset or investment, assuming the asset or investment is held through the end of the period.
The specific Pretax IRR (internal rate of return) figure for each period is calculated by using the figures in the Pretax Cash Flow Scenarios schedule. The formula for the first period is:
=IRR($B$99:$C$99)
The formula for the second period is:
=IRR($B$100:$C$100)
and so on. The values in the IRR function represent the pretax cash flows forecasted if the asset or investment is held through the period.
After-Tax IRR
The After-Tax IRR figures are the after-tax internal rates of return, which are calculated by using the after-tax operating and liquidation cash flows that are generated by the asset or investment, assuming the asset or investment is held through the end of the period.
The specific After-Tax IRR (internal rate of return) figure for each period is calculated by using the figures in the After-Tax Cash Flow Scenarios schedule. The formula for the first period is:
=IRR($B$112:$C$112)
The formula for the second period is:
=IRR($B$113:$C$113)
and so on. The values in the IRR function represent the after-tax cash flows forecasted if the asset or investment is held through the period.
Note that the equation that calculates the internal rates of return for an asset or investment held 10 periods is, by definition, a tenth root polynomial equation with up to 10 correct solutions. Accordingly, several internal rates of return can be correct for any investment you analyze. Be particularly careful in applying the internal rate of return measure to those assets or investments for which cash flows fluctuate between positive and negative amounts. Generally, an asset or investment has as many correct IRRs as sign changes in the cash flow. If there is only one sign change—for example, if the initial investment is negative and all the cash flows that follow are positive—you have only one IRR. However, if the first cash flow and fourth cash flows are negative, there are three sign changes and up to three correct IRRs. (The first sign change is the initial negative cash flow changing to positive, the second is the third-period changing to the fourth-period negative, and the third is the fourth-period negative changing to the fifth-period positive cash flow.) For this reason, you might want to use the adjusted rate of return or the net present value profit measure instead of the internal rate of return measure.
Pretax Adjusted IRR
The Pretax Adjusted IRR figures are the pretax internal rates of return, which are calculated by using the pretax operating and liquidation cash flows that are generated by the asset or investment, assuming that the asset or investment is held through the end of the period and assuming that any interim cash flows are reinvested at the pretax reinvestment rate specified in the inputs area of the starter workbook. (Notice that in Period 1, because there would be no interim cash flows—both the operating and liquidation cash flows occur at the end of the first period—the pretax adjusted IRR equals the pretax IRR.) This schedule assumes that when you buy an asset or make an investment at the beginning of the first period and sell at the end of the second period, you reinvest the operating cash flow generated by the investment in the first period at the pretax reinvestment rate specified in the inputs area of the starter workbook until the end of the second period.
The figure for each period’s Pretax Adjusted IRR (internal rate of return adjusted for reinvestment of the interim cash flows) is calculated by using the figures in the Pretax Cash Flow Scenarios schedule and the Pretax Reinvestment Rate figure specified in the inputs area of the starter workbook. The formula for the first period is:
=MIRR($B$99:$C$99,,$B$4)
The formula for the second period is:
=MIRR($B$100:$C$100,,$B$4)
and so on. The values used in the MIRR function represent the pretax cash flows forecasted if the asset or investment is held through the period. The contents of the cell referenced by $B$4 is the pretax reinvestment rate.
After-Tax Adjusted IRR
The After-Tax Adjusted IRR figures are the after-tax internal rates of return, which are calculated by using the after-tax operating and liquidation cash flows that are generated by the asset or investment, assuming the asset or investment is held through the end of the period and assuming that any interim cash flows are reinvested at the after-tax reinvestment rate specified in the inputs area of the starter workbook. (Notice that in Period 1, because there would be no interim cash flows—both the operating and liquidation cash flows occur at the end of the first period—the after-tax adjusted IRR equals the after-tax IRR.) This schedule assumes that when you buy an asset or make an investment at the beginning of the first period and sell at the end of the second period, you reinvest the operating cash flow generated by the investment in the first period at the after-tax reinvestment rate specified in the inputs area of the starter workbook until the end of the second period.
The figure for each period’s After-Tax Adjusted IRR (internal rate of return adjusted for reinvestment of the interim cash flows) is calculated by using the figures in the After-Tax Cash Flow Scenarios schedule and the After-Tax Reinvestment Rate figure specified in the inputs area of the starter workbook. The formula for the first period is:
=MIRR($B$112:$C$112,,$B$5)
The formula for the second period is:
=MIRR($B$113:$C$113,,$B$5)
and so on. The values in the MIRR function represent the after-tax cash flows forecasted if the asset or investment is held through the period. The contents of the cell referenced by $B$5 is the after-tax reinvestment rate.
Pretax Net Present Value
The Pretax Net Present Value figures are calculated by using the pretax operating and liquidation cash flows generated by the asset or investment, assuming the asset or investment is held through the end of the period, and by using the pretax discount rate specified in the inputs area of the starter workbook. Pretax net present values are significant in that they express in current cash the amount by which the investment falls short of or exceeds the time value of money specified by the pretax discount rate. For example, a $1,000 net present value of an asset or investment held for three years means that holding the investment for three years returns $1,000 more (in the current dollar value) than the pretax discount rate specifies. A negative $500 net present value for the same asset or investment held four years means that holding the asset or investment for four years returns $500 less (in current dollar value) than the pretax discount rate specifies. The pretax net present values often fluctuate, depending on the holding period. By developing and examining the pretax net present values delivered by the asset or investment under alternative holding periods, you can choose holding periods to enhance pretax profits.
For instance, in the example introduced in the preceding sentences, you actually lose $1,500 by holding the investment an additional (fourth) year. (The $1,500 of loss is the difference between making $1,000, which is what happens if you hold the investment for three years, and losing $500, which is what happens if you hold the investment for four years.)
The Pretax Net Present Value figure for each period is calculated by using the figures in the Pretax Cash Flow Scenarios schedule and the Pretax Discount Rate figure specified in the inputs area of the starter workbook. The formula for the first period is:
=NPV($B$6,$C$99:$C$99)+$B$99
The formula for the second period is:
=NPV($B$6,$C$100:$C$100)+$B$100
and so on. The values in the NPV function represent the pretax cash flows forecasted if the asset or investment is held through the period. The contents of the cell referenced by $B$6 is the pretax discount rate. The amount added to the NPV function is the initial investment.
After-Tax Net Present Value
The After-Tax Net Present Value figures are calculated by using the after-tax operating and liquidation cash flows generated by the asset or investment, assuming the asset or investments held through the end of the period, and by using the after-tax discount rate specified in the inputs area of the starter workbook. After-tax net present values are significant in that they express in current cash the amount by which the investment falls short of or exceeds the time value of money specified by the after-tax discount rate. For example, a $1,000 net present value of an asset or investment held for three years means that holding the investment for three years returns $1,000 more (in current dollar value) than the after-tax discount rate specifies. A negative $500 net present value for the same asset or investment held four years means that holding the asset or investment for four years returns $500 less (in current dollar value) than the after-tax discount rate specifies. The after-tax net present value often fluctuates, depending on the holding period. By developing and examining the after-tax net present values delivered by the asset or investment under alternative holding periods, you can choose holding periods to enhance after-tax profits. For example, in the example introduced in the preceding sentences, you lose $1,500 by holding the investment an additional (fourth) year.
The After-Tax Net Present Value figure for each period is calculated by using the figures in the After-Tax Cash Flow Scenarios schedule and the After-Tax Discount Rate figure specified in the inputs area of the starter workbook. The formula for the first period is:
=NPV($B$7,$C$112:$C$112)+$B$112
The formula for the second period is:
=NPV($B$7,$C$113:$C$113)+$B$113
and so on. The values in the NPV function represent the after-tax cash flows forecasted if the asset or investment is held through the period. The contents of the cell referenced by $B$7 is the after-tax reinvestment rate. The amount added to the NPV function is the initial investment.
Pretax Cumulative Cash Flows
The Pretax Cumulative Cash Flows figures represent the cumulative cash flows that result from holding the asset or investment, which are calculated by using the pretax cash flows from the Pretax Cash Flows Scenarios schedule, assuming the investment is held for 10 periods. The period during which the cumulative cash flow figure turns from a negative amount to a positive amount indicates the period in which the investment pays back the original cash invested—a common measure of liquidity.
The Pretax Cumulative Cash Flows figure for each period is calculated by using the 10- period holding scenario in the Pretax Cash Flow Scenarios schedule. The formula in the Period 0 column for the initial investment is:
=SUM($B$108:B108)
The formula for the first period is:
=SUM($B$108:C108)
The formula for the second period is:
=SUM($B$108:D108)
and so on. The results represent the cumulative pretax cash flows through the period.
Pretax Payback Period
The Pretax Payback Period is a text flag (the word Payback) that identifies the period during which the initial investment and any negative operating cash flows are finally paid back. The text flag appears in the column for the period in which the cumulative cash flow changes from negative to positive.
The Pretax Payback Period formulas determine whether the cumulative pretax cash flow has turned from a negative amount, indicating that the initial investment has not been fully paid back, to a positive amount, indicating that the initial investment has been paid back. The formula for the first period is:
=IF((AND(B91<0,C91>0=))=TRUE(),?Payback?,??)
The formula for the second period is:
=IF((AND(C91<0,D91>0=))=TRUE(),?Payback?,??)
and so on. For the period during which the initial investment is finally paid back, the text flag Payback appears in the column.
After-Tax Cumulative Cash Flows
The After-Tax Cumulative Cash Flows figures represent the cumulative cash flows that result from holding the asset or investment calculated by using the after-tax cash flows as calculated in the After-Tax Cash Flow Scenarios schedule, assuming the investment is held for 10 periods. The period during which the cumulative cash flow figure turns from a negative amount to a positive amount indicates the period in which the investment pays back the original cash invested—a common measure of liquidity.
The After-Tax Cumulative Cash Flows figure for each period is calculated by using the 10- year holding period scenario in the After-Tax Cash Flow Scenarios schedule. The formula for the initial investment is:
=SUM($B$121:B121)
The formula for the first period is:
=SUM($B$121:C121)
The formula for the second period is:
=SUM($B$121:D121)
and so on. The results represent the cumulative after-tax cash flows through the period.
After-Tax Payback Period
The After-Tax Payback Period is a text flag (the word Payback) that identifies the period during which the initial investment and any negative operating cash flows are finally paid back. The text flag appears in the column for the period in which the cumulative cash flow moves from a negative amount to a positive amount.
The After-Tax Payback Period formulas determine whether the cumulative after-tax cash flow has turned from a negative amount, indicating that the initial investment has not been fully paid back, to a positive amount, indicating that the initial investment has been paid back. The formula for the first period is:
=IF((AND(B93<0,C93>=0))=TRUE(),?Payback?,??)
The formula for the second period is:
=IF((AND(C93<0,D93>=0))=TRUE(),?Payback?,??)
and so on. For the period during which the initial investment is finally paid back, the text flag Payback appears in the column.
Pretax Cash Flow Scenarios
The Pretax Cash Flow Scenarios schedule has 10 rows with calculated data (see Figure 13-5). These are the forecasted cash flows for the alternative holding periods and are used to calculate the profitability and liquidity measures in the Cash Flow Analysis schedule. You will probably use this schedule only indirectly because it provides the raw data used to calculate the profitability measures. However, to read the schedule, you simply look down column A, which describes the various lengths of time you can hold the asset or investment, until you come to the number of periods held that you want to examine—that row then shows the cash flows occurring each period for the number-of-periods-held scenario. For example, suppose you want to view the pretax cash flows if the investment is held for 5 periods. You first look down the first column of the Pretax Cash Flow Scenarios column. When you come to 5, you’re at the row that shows the cash flows that assume you hold the asset or investment for 5 periods. The negative amount in column B shows the period 0 cash flow ($15,000 in Figure 13-5). The positive amounts in columns C through F in Figure 13-5 show the operating cash flow $4,000. The positive cash flow in column G shows the combined operating and liquidation cash flows as $17,750. Notice that in columns H and beyond, representing periods 6 and beyond, the amounts appear as 0 because the asset or investment has been disposed of and, therefore, no longer results in cash flows.
Period 0
The values in the Period 0 cash flows column show the initial cash outlay to acquire the asset or investment and are the same for each of the alternative holding periods.
Period 1 Through 10
The period cash flows show the forecasted pretax cash flow stemming from holding and perhaps disposing of an asset or investment for each of 10 periods. For example, the Period 1 pretax cash flow for holding period 1 equals the sum of both the pretax operating cash flow and the pretax liquidation cash flow for the first period; cash flows beyond the first period equal 0, signifying that asset or investment has been liquidated. Similarly, the Period 2 pretax cash flow for holding period 2 equals the sum of the pretax operating cash flow and the pretax liquidation cash flow for the second period; cash flows for Period 3 and beyond for holding Period 2 equal 0. The Period 1 pretax cash flow for holding period 2 equals the pretax operating cash flow for the first period. This schedule provides the alternative pretax cash flows used in the pretax profitability and liquidity measures shown in the Cash Flow Analysis schedule.
The same basic formula calculates the period cash flows for any of the periods in each of the alternative holding period scenarios. The basic formula uses a nested IF statement with the following structure:
IF the period is before the period the asset or investment is liquidated,
THEN assume that the period cash flow equals the pretax operating cash flow for the period,
ELSE IF the period is the same as the period the asset or investment is liquidated,
THEN assume the period cash flow equals the sum of the pretax operating cash flow and the pretax liquidation cash flow,
ELSE assume the period cash flow is 0 because the period is after the period the asset or investment was liquidated.
For example, the formula to calculate the first-period cash flow when you hold the asset or investment for one period is:
=IF(C$98<$A99,C$65,IF(C$98=$A99,C$65+C$75,0))
The formula to calculate the second-period cash flow when you hold the asset or investment for one period is:
=IF(D$98<$A99,D$65,IF(D$98=$A99,D$65+D$75,0))
The formula to calculate the first-period cash flow when you hold the asset or investment for two periods is:
=IF(C$98<$A100,C$65,IF(C$98=$A100,C$65+C$75,0))
The formula to calculate the second-period cash flow when you hold the asset or investment for two periods is:
=IF(D$98<$A100,D$65,IF(D$98=$A100,D$65+D$75,0))
and so on.
After-Tax Cash Flow Scenarios
The After-Tax Cash Flow Scenarios schedule has 10 rows with calculated data (see Figure 13-5). These are the forecasted cash flows for the alternative holding periods and are used to calculate the profitability and liquidity measures in the Cash Flow Analysis schedule.
Period 0
The values in the Period 0 cash flow column show the initial cash outlay to acquire the asset or investment and are the same for each of the alternative holding periods.
Period 1 Through 10
The period cash flows show the forecasted after-tax cash flow stemming from holding and perhaps disposing of an asset or investment for each of 10 periods. For example, the Period 1 after-tax cash flow for holding period 1 equals the sum of both the after-tax operating cash flow and the after-tax liquidation cash flow for the first period; cash flows beyond the first period equal 0, signifying that the asset or investment has been liquidated. Similarly, the Period 2 after-tax cash flow for holding period 2 equals the sum of the after-tax liquidation cash flow for the second period; cash flows for Period 3 and beyond for holding period 2 equal 0. The Period 1 after-tax cash flow for holding period 2 equals the after-tax operating cash flow for the first period. This schedule provides the alternative after-tax cash flows used in the after-tax profitability and liquidity measures shown in the Cash Flow Analysis schedule.
The same basic formula calculates the period cash flows for any of the periods in each of the alternative holding period scenarios. The basic formula uses a compound IF function statement with the following structure:
IF the period is before the period the asset or investment is liquidated,
THEN assume that the period cash flow equals the after-tax operating cash flow for the period,
ELSE IF the period is the same as the period the asset or investment is liquidated,
THEN assume the period cash flow equals the sum of the after-tax operating cash flow and the after-tax liquidation cash flow,
ELSE assume the period cash flow is 0 because the period is after the period the asset or investment was liquidated.
For example, the formula to calculate the first-period cash flow when you hold the asset or investment for one period is:
=IF(C$111<$A112,C$68,IF(C$111=$A112,C$68+C$78,0))
The formula to calculate the second-period cash flow when you hold the asset or investment for one period is:
=IF(D$111<$A112,D$68,IF(D$111=$A112,D$68+D$78,0))
The formula to calculate the first-period cash flow when you hold the asset or investment for one period is:
=IF(C$111<$A113,C$68,IF(C$111=$A113,C$68+C$78,0))
The formula to calculate the second-period cash flow when you hold the asset or investment for two periods is:
=IF(D$111<$A113,D$68,IF(D$111=$A113,D$68+D$78,0))
and so on.
Leave a Reply