For this assignment, you will analyze a second project and compare it to the "Brewery Expansion" project. This second potential project is described below.

Suppose the same brewery is evaluating an additional option. The brewery is considering purchasing oak barrels to produce high-end, barrel-aged, beers. The relevant input information and project details are described below. Adjustments for sensitivity and scenario analysis are listed in parentheses following the base case information as (best case, worst case).

The brewery plans to purchase 50 barrels at an expected cost of $200 per barrel. However, there is a chance they may receive the barrels for free from a winery, and there is a chance they may have to pay $300/barrel if there is a shortage. There is no salvage value for the barrels.

Cost of oak barrel: $200/barrel (-100%, +50%)

Unlike the tank, which can produce a monthly lot of beer, beer now needs to be aged for likely a year. However, some beers may mature in as little as 6 months or as long as 2 years. There are 300 beers produced per batch per barrel. (Also assume, as is the case in the original brewery expansion, 55%, 75%, 95%, and 100% of production capacity is realized as actual sales in years 1-4; see F9:I9.)

Production time for oak barrel: 1 year (-50%, +100%)

The barrel-aged beers are premium products that are expected to sell for $10 per bottle (the same size as beers from a tank). If the beer turns out worse than expected, the brewery will have to slash the price to $5 per bottle. However, if the beer turns out better than expected, the brewery may be able to charge $20 per bottle.

Market price: $10 (+100%, -50%)

Variable costs, fixed costs, and WACC have little uncertainty and only fluctuate by 10%.

Annual variable cost: $90,000, VC/unit (-10%, +10%)

Annual fixed cost: $30,000 (-10%, +10%)

WACC: 10% (-10%, +10%)

Repeat all analyses (project analysis, sensitivity analysis, scenario analysis) performed in the original Brewery Expansion project for the new barrel-aging project. In 1-2 paragraphs, summarize your findings and compare the projects.

Suppose there is a 2% chance the barrel-aging program catches the attention of a large brewery, leading to a $10,000,000 investment from the large brewery at the end of the project.

In a new sheet, recalculate and report the NPV of the barrel-aging expansion project (at base case values, no sensitivity analysis). Compare this value with the NPV of the original expansion project.

Transcribed Image Text:

A D E 1 Cash Flow Estimation and Analysis for Tank Purchase $30,000 100-barrel tank purchased using long-term loan. A tank can produce a batch of beer each month, and there are 300 beers produced per barrel. The beer can be sold for $5. Annual variable costs 2 are $800,000. 3456 7 8 9 Unit sales B Investment Outlays at Time = 0 CAPEX = Building and Equipment ANOWC = Additional net operating working capital needed Operating Cash Flows Over the Project's Life (Time=1-4) 10 Sales price 11 Variable cost per unit 12 Sales revenues Units x Price 13 Variable costs Units * Cost/unit 14 Fixed operating costs except depr'n 15 Depreciation: Accelerated from table below 16 Total operating costs 17 EBIT (or Operating income) 26 18 Taxes on operating income 40% 19 EBIT (1-T) = After-tax project operating income 20 Add back depreciation 21 EBIT (1-T) + Depreciation 22 Terminal Cash Flows at Time=4 23 Salvage value (taxed as ordinary income) Tax on salvage value = 0.4 x (SV-BV of equipment 24 at t = 4) 25 After-tax salvage value C Depreciation ANOWC Recovery of net operating working capital Project free cash flows = EBIT(1-T) + DEP-CAPEX 27 - ANOWC 28 29 30 31 32 33 34 35 Project Evaluation @WACC = 36 Accelerated 37 38 39 40 Cost: Alternative depreciation Cost: NPV IRR MIRR Payback $30,000 $30,000 Accelerated Rate Depreciation Straight line Rate $43,423.10 16.526% 14.173% 3.43 Depreciation 10% 0 -$30,000 0 -$30,000 F 1 198,000 $5.00 $4.04 $990,000 800,000 600,000 9,900 $1,409,900 -$419,900 -167,960 -$251,940 9,900 -$242,040 -$242,040 1 33% $9,900 1 25% $7,500 G 2 800,000 600,000 13,500 270,000 342,000 $5.00 $5.00 $2.96 $2.34 $1,350,000 $1,710,000 -$24,600 2 45% $13,500 H $1,413,500 $1,404,500 -$63,500 $305,500 -25,400 122,200 -$38,100 $183,300 13,500 4,500 -$24,600 $187,800 2 25% $7,500 3 800,000 600,000 4,500 Straight line $42,943.79 16.410% 14.089% 3.44 $187,800 3 15% $4,500 3 25% $7,500 I 4 360,000 $5.00 $2.22 $1,800,000 800,000 600,000 2,100 $1,402,100 $397,900 159,160 $238,740 2,100 $240,840 20,000 8,000 12,000 0 $252,840 4 7% $2,100 4 25% $7,500 A D E 1 Cash Flow Estimation and Analysis for Tank Purchase $30,000 100-barrel tank purchased using long-term loan. A tank can produce a batch of beer each month, and there are 300 beers produced per barrel. The beer can be sold for $5. Annual variable costs 2 are $800,000. 3456 7 8 9 Unit sales B Investment Outlays at Time = 0 CAPEX = Building and Equipment ANOWC = Additional net operating working capital needed Operating Cash Flows Over the Project's Life (Time=1-4) 10 Sales price 11 Variable cost per unit 12 Sales revenues Units x Price 13 Variable costs Units * Cost/unit 14 Fixed operating costs except depr'n 15 Depreciation: Accelerated from table below 16 Total operating costs 17 EBIT (or Operating income) 26 18 Taxes on operating income 40% 19 EBIT (1-T) = After-tax project operating income 20 Add back depreciation 21 EBIT (1-T) + Depreciation 22 Terminal Cash Flows at Time=4 23 Salvage value (taxed as ordinary income) Tax on salvage value = 0.4 x (SV-BV of equipment 24 at t = 4) 25 After-tax salvage value C Depreciation ANOWC Recovery of net operating working capital Project free cash flows = EBIT(1-T) + DEP-CAPEX 27 - ANOWC 28 29 30 31 32 33 34 35 Project Evaluation @WACC = 36 Accelerated 37 38 39 40 Cost: Alternative depreciation Cost: NPV IRR MIRR Payback $30,000 $30,000 Accelerated Rate Depreciation Straight line Rate $43,423.10 16.526% 14.173% 3.43 Depreciation 10% 0 -$30,000 0 -$30,000 F 1 198,000 $5.00 $4.04 $990,000 800,000 600,000 9,900 $1,409,900 -$419,900 -167,960 -$251,940 9,900 -$242,040 -$242,040 1 33% $9,900 1 25% $7,500 G 2 800,000 600,000 13,500 270,000 342,000 $5.00 $5.00 $2.96 $2.34 $1,350,000 $1,710,000 -$24,600 2 45% $13,500 H $1,413,500 $1,404,500 -$63,500 $305,500 -25,400 122,200 -$38,100 $183,300 13,500 4,500 -$24,600 $187,800 2 25% $7,500 3 800,000 600,000 4,500 Straight line $42,943.79 16.410% 14.089% 3.44 $187,800 3 15% $4,500 3 25% $7,500 I 4 360,000 $5.00 $2.22 $1,800,000 800,000 600,000 2,100 $1,402,100 $397,900 159,160 $238,740 2,100 $240,840 20,000 8,000 12,000 0 $252,840 4 7% $2,100 4 25% $7,500

Answer rating: 100% (QA)

NoteIf large brewery invest 10000000 TOTAL ... View the full answer