estimasi aliran kas

ESTIMASI ALIRAN KAS

PENDAHULUANEstimasi aliran kas (cash flow estimation) merupakan tahap yang paling penting dan sekaligus paling sulit dalam proses capital budgeting.

Dua hal penting dalam estimasi aliran kas:

1. Keputusan dalam capital budgeting harus didasarkan pada aliran kas bukan laba bersih.

2. Hanya incremental cash flow yang relevan dalam estimasi aliran kas.

TIGA KELOMPOK UTAMA ALIRAN KASInitial investment outlay: kelompok aliran kas yang terjadi pada awal proyek (sebelum proyek beroperasi). Misalnya: pembelian tanah, gedung, mesin, peraatan dan modal kerja.

Operating cash flow: kelompok aliran kas yang terjadi selama proyek beroperasi (umur proyek).

Terminal year cash flow: kelompok aliran kas yang terjadi pada saat proyek dianggap berakhir (terminate). Misalkan: nilai sisa bersih dan pengembalian modal kerja

TIGA KELOMPOK UTAMA ALIRAN KAS0123456Initial inv. outlayOperating cash flowTerminal year cash flow Tanah, gedung Mesin, perlengkapan Modal kerja bersih Penjualan Harga pokok penjualan Laba kotorB. AdministrasiB. DepresiasiLaba sebelum pajakPajakLaba bersihOperating CF = Laba bersih + Depresiasi

NIlai sisa bersihPengembalian modal kerja bersihOperating CF = Laba bersih + Depresiasi

INPUT DATA

Sheet1

12model2/25/03 23:065/8/00

Chapter 12. Spreadsheet Models for Capital Budgeting

This worksheet contains the model used to analyze BQC's new project decision as described in the text.

In addition, models for analyzing replacement decisions and bond refunding decisions are

provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"

and "Bond Refunding" at the bottom of the screen.

Model for Evaluating A New Capital Budgeting Project:

The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs

used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.

Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the

building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of

the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,

and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs

result in changes in the key outputs.

Note that all dollars are shown in thousands; this is done for convenience.

Identifying the relevant cash flows

For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,

operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from

the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS

Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the

determination of net salvage values to be incorporated into our cash flow estimation.

Table 12-1. Analysis of a New (Expansion) Project

Part 1. Input Data (in thousands of dollars)Data used in Scenario Analysis

Key Output: NPV =51,661,753Sale PriceUnitsVCGrowth

Building cost (= Depreciable basis)120,000,000

Equipment cost (= Depreciable basis)80,000,000Market value of building in 200575,000,000Good$3.9026,000$1.4730%

Net Operating WC60,000,000Market value of equip. in 200520,000,000Base$3.0020,000$2.100%

First year sales (in units)2,000,000Tax rate40%Bad$2.1014,000$2.73-30%

Growth rate in units sold0.0%WACC12%

Sales price per unit300Inflation: growth in sales price0.0%Change the inputs to the above to get the NPVs

Variable cost per unit210Inflation: growth in VC per unit0.0%for the scenarios below and the cash flows for the

Fixed costs80,000,000Inflation: growth in fixed costs0.0%decision tree. Change to the indicated variables,

then use copy>Paste Special>Values to

YearsCumulativerecord the results below, and then make the next

1234Depr'nset of changes. When finished, return to base

Building Depr'n Rate1.3%2.6%2.6%2.6%case variable levels.

Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000

Ending Book Val: Cost - Cum. Depr'n118,440,000115,320,000112,200,000109,080,000

Equipment Depr'n Rate20.0%32.0%19.0%12.0%

Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000


to determine the depreciation expense for the year. See Appendix 12A for a review of MACRS depreciation rates.

Part 3 of Table 12-1. Net Salvage Values in 2005

BuildingEquipmentTotal

Estimated Market Value in 200575,000,00020,000,000

109,080,00013,600,000

-34,080,0006,400,000

Taxes paid or tax credit-13,632,0002,560,000

88,632,00017,440,000106,072,000

building, accumulated depreciation equals $1,092, so book value equals $12,000 - $1,092 = $10,908. For the equipment,

accumulated depreciation equals $6,640, so book value equals $8,000 - $6,640 = $1,360.

taken versus "true" depreciation, and it is treated as an operating expense for 2005. Equipment: $2,000 market value -

$1,360 book value = $640 profit. Here the depreciation charge exceeds the "true" depreciation, and the difference is called

"depreciation recapture". It is taxed as ordinary income in 2005.

net salvage value = $7,500 - (-$1,363) = $8,863.

Part 4 of Table 12-1. Projected Net CashYears

01234

20012002200320042005

Investment Outlays at Time Zero

Building-120,000,000

Equipment-80,000,000

Increase in Net Operating WC-60,000,000

Operating Cash Flows over the Project's Life

Units sold2,000,0002,000,0002,000,0002,000,000

Sales price300300300300

Sales revenue600,000,000600,000,000600,000,000600,000,000

Variable costs420,000,000420,000,000420,000,000420,000,000

Fixed operating costs80,000,00080,000,00080,000,00080,000,000

Depreciation (building)1,560,0003,120,0003,120,0003,120,000

Depreciation (equipment)16,000,00025,600,00015,200,0009,600,000

Oper. income before taxes (EBIT)82,440,00071,280,00081,680,00087,280,000

Taxes on operating income (40%)32,976,00028,512,00032,672,00034,912,000

Net Operating Profit After Taxes (NOPAT)49,464,00042,768,00049,008,00052,368,000

Add back depreciation17,560,00028,720,00018,320,00012,720,000

Operating cash flow67,024,00071,488,00067,328,00065,088,000

Terminal Year Cash Flows

60,000,000

Net salvage value106,072,000

Total termination cash flows166,072,000

Net Cash Flow (Time line of cash flows)-260,000,00067,024,00071,488,00067,328,000231,160,000

sold off and receivables are collected.

Part 5 of Table 12-1. Key Output and Appraisal of the Proposed Project

Net Present Value (at 12%)51,661,753

IRR19.33%

MIRR17.19%Years

01234

Cumulative cash flow for payback(260,000,000)(192,976,000)(121,488,000)(54,160,000)177,000,000

Cum. CF > 0, hence Payback Year:0.000.000.000.003.23

Payback found with Excel function =3.23See note below for an explanation of the Excel calculation.

Check: Payback = 3 + 5,416/23,116 =3.23Manual calculation for the base case.

The Excel payback calculation is based on the logical IF function. Returns FALSE if the cumulative CF is negative or

the actual payback if the cumulative CF is positive. Then, we use the MIN (minimum) function to find first year when

payback is positive. The min function procedure is necessary for projects with longer lives, because then values, not the

word FALSE, would appear in a number of cells. The min function picks the smallest number, which is the payback.

Based on the firm's 12% weighted average cost of capital, this project has a NPV of $5,166. Since the NPV is positive,

we tentatively conclude that the project should be accepted. The IRR and MIRR confirm this decision because both

exceed the cost of capital. Note, though, that no risk analysis has been conducted. It is possible that the firm's

managers, after appraising the project's risk, might conclude that its projected return is insufficient to compensate

for its risk, and reject it.

Part 6. Evaluating Risk: Sensitivity Analysis

Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected outcome.

For example, if there were a high probability that the $5,166 expected NPV as calculated above will actually turn

out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected

outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project turned

out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than expected,

then the output will likewise be worse than expected. In Part 6 we use Excel to examine the project's sensitivity to

changes in the input variables.

I. Sensitivity of NPV and to Variations in Unit Sales.

Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.

% DeviationWACC% Deviation1st YEAR UNIT SALES

fromNPVfromUnitsNPVGoal Seek finds the value of some input variable that

Base CaseWACC51,661,753Base CaseSold$51,661,7531st Year Sales wouldcaused an output variable to hit a specified level. In

-30%8.4%$8,294-30%14,000-$4,675the project break eventhis case, we want to know the 1st year unit sales that

-15%10.2%$6,674-15%17,000$246in the sense that NPVwill cause NPV = 0, holding other things constant.

0%12.0%$5,166Base Case0%20,000$5,166= $0?

15%13.8%$3,76115%23,000$10,087Click Tools, then Goal Seek, to get the following dialog

30%15.6%$2,45030%26,000$15,007box, which we completed:

NPV against Sales and

see about where NPV

% DeviationVARIABLE COSTS% DeviationGROWTH RATE, UNITS= 0. Alternatively, you

fromVariableNPVfromGrowthNPVcould use Tools > Goal

Base CaseCost$51,661,753Base CaseRate %$51,661,753Seek as described in

-30%$1.47$28,129-30%-30%-$5,847the columns to the right.

-15%$1.79$16,647-15%-15%-$907The answer is 16,850

0%$2.10$5,166Base Case0%0%$5,166units.

15%$2.42-$6,31515%15%$12,512

30%$2.73-$17,79630%30%$21,269

We want to get NPV = 0, so we specify the "Set cell" as

being G148 (Excel adds the dollar signs), and the "To

% DeviationSALES PRICE% DeviationFIXED COSTSvalue" to be 0. We specify the cell to change as F148,

fromSalesNPVfromFixedNPVand when we click OK, G148 changes to $0 and F148

Base CasePrice$51,661,753Base CaseCosts$51,661,753goes to 16,850.

-30%$2.10-$27,637-30%$5,600$9,540

-15%$2.55-$11,236-15%$6,800$7,353The Goal Seek feature is easy to use, and it often

0%$3.00$5,166Base Case0%$8,000$5,166comes in handy.

15%$3.45$21,56815%$9,200$2,979

30%$3.90$37,97030%$10,400$792

We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:

Figure 12-1. Evaluating Risk: Sensitivity Analysis (Dollars in Thousands)

DeviationNPV at Different Deviations from Base

fromSalesVariableGrowthYear 1Fixed

Base CasePriceCost/UnitRateUnits SoldCostWACC

-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294

-15%($11,236)$16,647($907)$246$7,353$6,674

0%$5,166$5,166$5,166$5,166$5,166$5,166

15%$21,568($6,315)$12,512$10,087$2,979$3,761

30%$37,970($17,796)$21,269$15,007$792$2,450

Range65,60745,92527,11619,6828,7485,844

We see from the tables and graph that NPV is most sensitive to changes in the sales price and variable

costs, somewhat sensitive to changes in first-year sales and the sales growth rate, and not very sensitive to

changes in WACC and fixed costs. Thus, the real issue is our confidence in the forecasts of the sales

price and variable costs, as well as the first-year sales and the growth rate in units sold.

NPV can change dramatically if the key input variables change, but we do not know how much the

variables are likely to change. For example, if we were buying components under a fixed price contract,

then variable costs might be locked in and not likely to rise more than say 5%, and we might have a firm

contract to sell the projected number of units at the indicated price per unit. In that case, the "bad

conditions" would not materialize, and a positive NPV would be pretty well guaranteed. We go on to look at

the probabilities of different conditions in Part 7.

Part 7. Evaluating Risk: Scenario Analysis

Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence

to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilities of

changes in the key variables. Part 7 provides a scenario analysis of the computer project.

We saw from the sensitivity analysis that the key variables are sales price, variable costs, unit sales, and the unit

growth rate. Therefore, in our sensitivity analysis we hold the other variables at their base case levels and then

examine the situation when the key variables change. We assume that the company regards the worst case as one

where each of the three variables is 30% worse than the base level, and the best case has each variable 30% better

than base. We also assume that there is a 25% chance of the best and worst cases, and a 50% chance of base case

levels.

Table 12-2. Scenario Analysis (Dollars in Thousands)Squared

Deviation

SalesUnitVariableGrowthTimes

ScenarioProbabilityPriceSalesCostsRateNPVProbability

Best Case25%$3.9026,000$1.4730%$144,0243307034782Manually changed the Part 1 inputs, then put the

Base Case50%$3.0020,000$2.100%$5,166284276572NPV as calculated with the modified variables here.

Worst Case25%$2.1014,000$2.73-30%($38,315)1133161977Took deviations from the expected values, squared

4724473330them, and multiplied by the probabilities to get the

Expected NPV = sum, prob times NPV$29,010numbers shown.

Standard Deviation = Sq Root of column I sum$68,735Summed the squared deviations and took sq root

Coefficient of Variation = Std Dev / Expected NPV2.37Std Dev divided by Expected NPV

a. Probability Graph

Probability

50%

25%

(38,315)029,010144,024

5,166NPV ($)

Most LikelyMean of distribution

b. Continuous Approximation

Probability Density

(38,315)029,010144,024

NPV ($)

5,166

The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a

25% probability that the project would result in a loss of $38.3 million. There is also a 25% probability that it could

produce an NPV of $144 million. The standard deviation is high, at $68.7 million, and the coefficient of variation is a

high 2.37.

Note that the expected NPV in the scenario analysis is much higher than the base case value. This occurs because

under good conditions we have high numbers multiplied by other high numbers, giving a very high result.

This analysis suggest that the project is relatively risky, hence that the base case NPV should be recalculated using a

higher WACC. At a WACC of 15% (versus 12% for an average risk project), the base case NPV is:$2,877

That number is not very high in relation to the project's cost.

Changing the WACC would also change the scenario analysis. Here are new figures:

ProbabilityNPVDev. Sqd^2

Worst Case25%($38,102)992811800

Base Case50%$2,877242855067

Best Case25%$132,0112867361381

Expected NPV:$24,916

Standard Deviation:$64,055

Coefficient of Variation:2.57

At this point, the project looks risky but acceptable. There is a good chance that it will produce an NPV of $2,877, but

there is also a chance that the NPV could be dramatically higher or lower.

If the bad conditions occur, this will hurt but not bankrupt the firm--this is just one project for a large company.

We indicate at the start that this project's returns would be highly correlated with the firm's other projects'

returns and also with the general stock market. Thus, its stand-alone risk (which is what we have been analyzing)

also reflects its within-firm and market risk. If this were not true, then we would need to make further risk

adjustments.

Finally, recall that we stated at the start that if the firm undertakes the project, it will be committed to operate it for the

full 4-year life. That is important, because if it were not so committed, then if the bad conditions occurred during the

first year of operations, the firm could simply close down operations. This would cut its losses, and the worse case

scenario would not be nearly as bad as we indicated. Then, the expected NPV would be higher, and the standard

deviation and coefficient of variation would be lower. We extend the model to deal with abandonment in the next section,

which is designed to be examined after reading Chapter 13.

EXTENDING THE MODEL TO ALLOW FOR ABANDONMENT

This section is an "extension" that deals with a "real option," the option to abandon the project. This topic is covered in

Chapter 13, so it will be more clear after you have read that chapter.

abandoned, then the company can sell the related assets at the end of the year after abandonment and realize a net cash

flow equal to the asset's book value at the end of the abandonment year. Use a decision tree format to set up the scenarios

with and without the abandonment option. Thus, if it has bad results in Year 1, it can abandon the project and receive the

end-of-Year 1 book value as a cash flow at the end of Year 2.

Situation 1. Decision Tree Where Firm Cannot Abandon

End of Period:NPV thisProb.Data for

01234ProbabilityScenariox NPVStd Deviation

25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697

25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995

Expected NPV = sum, prob times NPV$29,010

Standard Deviation = Sq Root of column K sum$68,735

Coefficient of Variation = Std Dev / Expected NPV2.37

Situation 2. Decision Tree Where Firm Has the Option to Abandon



25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553

25%0%-$7,356-$6,660$10,5010%-$38,315$00

-$9,390

25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260

Expected NPV = sum, prob times NPV$36,390,006

Standard Deviation = Sq Root of column K sum$62,940,247

Coefficient of Variation (CV) = Std Dev / Expected NPV1.73

Where the abandonment option exists, the firm would definitely abandon the project at the end of Year 1 if the worse case

scenario developed. Therefore, we show a zero probability of continuing to operate in that case, and a 25% probability

that case will develop and the project will be abandoned.

Under abandonment, the worse case scenario still results in a negative NPV, but it is less negative than if abandonment

were not possible. This raises the expected NPV and lowers the standard deviation, and both of these changes work to

lower the CV. Thus, the project is seen to be more profitable and to have a lower risk if it can be abandoned.

Note that we discounted cash flows at 12% in the decision tree analysis. However, if 12% were appropriate given the

risk as measured by the CV in the no-abandonment case, then a lower WACC would be appropriate if the abandonment

option exists. Thus, it would make sense to go through another iteration and find the project's with-abandonment NPV

using a somewhat lower WACC. No precise procedure is available for determining the risk-adjusted discount rate, so

the adjustment would have to be made on a judgmental basis.

Note also that this type of analysis can be use to estimate the value of the option to abandon--its value is the difference

between the NPVs with and without abandonment. In this case, with our figures, it is

Expected NPV with Abandonment$36,390,006

Expected NPV without Abandonment$29,010

Approximate Value of Option$36,360,996

Thus, if abandonment were not possible due to an agreement with a labor union, BQC could afford to offer up to $4.619

million to get out of that constraint.

Finally, note that other types of real options, including timing options, expansion options, production flexibility options,

and the like can be analyzed using the decision tree format. Decision trees provide a powerful tool for investment

decision analysis.

Sheet1

-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866

-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544

5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066

21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519

37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813

Sales price

Variable cost

Growth rate

Units sold

Fixed cost

WACC

NPV

Sheet2

Sheet3

Tabel 1. Klasifikasi Berdasarkan Jenis dan Umur Proyek

Class Life

Type of property

3 years

Certain special manufacturing tools

5 years

Automobiles, light-duty trucks, computers, and certain special manufacturing equipment

7 years

Most industrial equipment, office furniture, and fixtures

10 years

Certain longer-lived types of equipment

27,5 years

Residential rental real property such as apartment buildings

39 years

All nonresidential real property, including commercial and industrial buildings

Tabel 2. Beban Depresiasi Berdasarkan Kelas Aset

Ownership Year

Class of Investment

3 year

5 year

7 year

10 year

1

33%

20%

14%

10%

2

45

32

25

18

3

15

19

17

14

4

7

12

13

12

5

11

9

9

6

6

9

7

7

9

7

8

4

7

9

7

10

6

11

3

100%

100%

100%

100%

SKEDUL DEPRESIASI

Sheet1

12model2/25/03 23:125/8/00




































Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000



Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000






109,080,00013,600,000

-34,080,0006,400,000


88,632,00017,440,000106,072,000








01234

20012002200320042005






Units sold2,000,0002,000,0002,000,0002,000,000


Sales revenue600,000,000600,000,000600,000,000600,000,000

Variable costs420,000,000420,000,000420,000,000420,000,000










60,000,000







IRR19.33%

MIRR17.19%Years

01234





























0%12.0%$5,166Base Case0%20,000$5,166= $0?




see about where NPV





-15%$1.79$16,647-15%-15%-$907The answer is 16,850

0%$2.10$5,166Base Case0%0%$5,166units.

15%$2.42-$6,31515%15%$12,512

30%$2.73-$17,79630%30%$21,269






-30%$2.10-$27,637-30%$5,600$9,540



15%$3.45$21,56815%$9,200$2,979

30%$3.90$37,97030%$10,400$792






-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294

-15%($11,236)$16,647($907)$246$7,353$6,674

0%$5,166$5,166$5,166$5,166$5,166$5,166

15%$21,568($6,315)$12,512$10,087$2,979$3,761

30%$37,970($17,796)$21,269$15,007$792$2,450

Range65,60745,92527,11619,6828,7485,844




















levels.


Deviation











Probability

50%

25%

(38,315)029,010144,024

5,166NPV ($)



Probability Density

(38,315)029,010144,024

NPV ($)

5,166




high 2.37.








Worst Case25%($38,102)992811800

Base Case50%$2,877242855067

Best Case25%$132,0112867361381










adjustments.

















25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697

25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995







25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553

25%0%-$7,356-$6,660$10,5010%-$38,315$00

-$9,390

25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
























decision analysis.

Sheet1

-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866

-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544

5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066

21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519

37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813

Sales price

Variable cost

Growth rate

Units sold

Fixed cost

WACC

NPV

Sheet2

Sheet3

NET SALVAGE VALUENSV = MV - {(MV - BV) * T}= 20 jt - {(20jt-13jt) *0.4}= 20 jt - 2.560.000= 17.440.000

Sheet1

12model2/25/03 23:145/8/00




































Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000



Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000






109,080,00013,600,000

-34,080,0006,400,000


88,632,00017,440,000106,072,000








01234

20012002200320042005






Units sold2,000,0002,000,0002,000,0002,000,000


Sales revenue600,000,000600,000,000600,000,000600,000,000

Variable costs420,000,000420,000,000420,000,000420,000,000










60,000,000







IRR19.33%

MIRR17.19%Years

01234





























0%12.0%$5,166Base Case0%20,000$5,166= $0?




see about where NPV





-15%$1.79$16,647-15%-15%-$907The answer is 16,850

0%$2.10$5,166Base Case0%0%$5,166units.

15%$2.42-$6,31515%15%$12,512

30%$2.73-$17,79630%30%$21,269






-30%$2.10-$27,637-30%$5,600$9,540



15%$3.45$21,56815%$9,200$2,979

30%$3.90$37,97030%$10,400$792






-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294

-15%($11,236)$16,647($907)$246$7,353$6,674

0%$5,166$5,166$5,166$5,166$5,166$5,166

15%$21,568($6,315)$12,512$10,087$2,979$3,761

30%$37,970($17,796)$21,269$15,007$792$2,450

Range65,60745,92527,11619,6828,7485,844




















levels.


Deviation











Probability

50%

25%

(38,315)029,010144,024

5,166NPV ($)



Probability Density

(38,315)029,010144,024

NPV ($)

5,166




high 2.37.








Worst Case25%($38,102)992811800

Base Case50%$2,877242855067

Best Case25%$132,0112867361381










adjustments.

















25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697

25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995







25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553

25%0%-$7,356-$6,660$10,5010%-$38,315$00

-$9,390

25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
























decision analysis.

Sheet1

-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866

-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544

5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066

21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519

37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813

Sales price

Variable cost

Growth rate

Units sold

Fixed cost

WACC

NPV

Sheet2

Sheet3

INVESTMENT OUTLAY

Sheet1

12model2/25/03 23:175/8/00




































Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000



Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000






109,080,00013,600,000

-34,080,0006,400,000


88,632,00017,440,000106,072,000







Years

01234

20032004200520062007




Increase in NOWC-60,000,000


Units sold2,000,0002,000,0002,000,0002,000,000


Sales revenue600,000,000600,000,000600,000,000600,000,000

Variable costs420,000,000420,000,000420,000,000420,000,000










60,000,000







IRR19.33%

MIRR17.19%Years

01234





























0%12.0%$5,166Base Case0%20,000$5,166= $0?




see about where NPV





-15%$1.79$16,647-15%-15%-$907The answer is 16,850

0%$2.10$5,166Base Case0%0%$5,166units.

15%$2.42-$6,31515%15%$12,512

30%$2.73-$17,79630%30%$21,269






-30%$2.10-$27,637-30%$5,600$9,540



15%$3.45$21,56815%$9,200$2,979

30%$3.90$37,97030%$10,400$792






-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294

-15%($11,236)$16,647($907)$246$7,353$6,674

0%$5,166$5,166$5,166$5,166$5,166$5,166

15%$21,568($6,315)$12,512$10,087$2,979$3,761

30%$37,970($17,796)$21,269$15,007$792$2,450

Range65,60745,92527,11619,6828,7485,844




















levels.


Deviation











Probability

50%

25%

(38,315)029,010144,024

5,166NPV ($)



Probability Density

(38,315)029,010144,024

NPV ($)

5,166




high 2.37.








Worst Case25%($38,102)992811800

Base Case50%$2,877242855067

Best Case25%$132,0112867361381










adjustments.

















25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697

25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995







25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823

50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553

25%0%-$7,356-$6,660$10,5010%-$38,315$00

-$9,390

25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
























decision analysis.

Sheet1

-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866

-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544

5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066

21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519

37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813

Sales price

Variable cost

Growth rate

Units sold

Fixed cost

WACC

NPV

Sheet2

Sheet3

NET OPERATING CASH FLOW

Sheet1

12model2/25/03 23:205/8/00




















determination of net salvage values to be incorporated into our cash

estimasi aliran kas

Documents