Model Facts

This spreadsheet supports STUDENT analysis of the case, "Diamond

Chemicals (A)" (UVA-F-1351 v. 1.4).

Please note:

1) This is a working model. Assumptions / Inputs presented can be

changed to vary the results.

2) As long as default spreadsheet calculation is "automatic" the impact

of changing assumptions will be computed in real time. If calculation is
set as "manual" you should press the F9 function key to
recalculate results. To set numerical calculation settings to automatic
look under tools, options, calculations menu.

Copyright (C) 2001 by the Trustees of the University of Virginia Darden School Foundation.

Page 1
 Model Facts

chool Foundation.

Page 2
 Exhibit 2 VA-F-1351
DIAMOND CHEMICALS PLC (A)
Revised and Final DCF Analysis: Merseyside Capital Expenditure Program
(Reflects charge for FULL EROSION of Rotterdam business volume)

Assumptions
Annual Output (metric tons) 250,000 Discount rate 10.0% <== Note that discount rate should be set to 10 % for
Output Gain/Original Output 7.0% Depreciable Life (years) Plant 15 exact comparability to case exhibit.
Price/ton (pounds sterling) 541 Depreciable Life (yrs.) Tank Cars 10
Inflation (prices and costs) 3.0% Overhead/Investment 3.5%
Gross Margin (ex. Deprec.) 12.50% Salvage Value 0
Old Gross Margin 11.5% WIP Inventory/Cost of Goods 3.0%
Tax Rate 30.0% Months Downtime, Construction 1.5
Investment Outlay (mill.) - Plant 9.00 After-tax Scrap Proceeds 0
Investment in Tank Cars 2.00 Old Gross Margin at Rotterdam 11.5%
Energy Savings/Sales Yr. 1-5 1.25%
Yr. 6-10 0.8%
Yr. 11-15 0.0%

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Year Now 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
1. Estimate of Incremental Gross Profit
New Output 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500 267,500
Lost Output--Construction (33,438)
New Sales (Millions) 126.63 149.06 153.53 158.14 162.88 167.77 172.80 177.98 183.32 188.82 194.49 200.32 206.33 212.52 218.90
New Gross Margin 13.8% 13.8% 13.8% 13.8% 13.8% 13.3% 13.3% 13.3% 13.3% 13.3% 12.5% 12.5% 12.5% 12.5% 12.5%
New Gross Profit 17.41 20.50 21.11 21.74 22.40 22.23 22.90 23.58 24.29 25.02 24.31 25.04 25.79 26.57 27.36

Old Output 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000
Old Sales 135.25 139.31 143.49 147.79 152.23 156.79 161.50 166.34 171.33 176.47 181.76 187.22 192.83 198.62 204.58
Old Gross Profit 15.55 16.02 16.50 17.00 17.51 18.03 18.57 19.13 19.70 20.29 20.90 21.53 22.18 22.84 23.53

Cannibalization:
Lost Rotterdam Output (MT) 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500 17,500
Lost Revenue 9.47 9.75 10.04 10.35 10.66 10.98 11.30 11.64 11.99 12.35 12.72 13.11 13.50 13.90 14.32
Lost Rotterdam Gross Profit 1.09 1.12 1.16 1.19 1.23 1.26 1.30 1.34 1.38 1.42 1.46 1.51 1.55 1.60 1.65

Incremental Gross Profit 0.77 3.35 3.45 3.56 3.66 2.94 3.02 3.11 3.21 3.30 1.94 2.00 2.06 2.13 2.19

2. Estimate of Incremental Depreciation

+ New Depreciation, Plant 1.20 1.04 0.90 0.78 0.68 0.59 0.51 0.44 0.38 0.33 0.43 0.43 0.43 0.43 0.43
+ New Depreciation,Tank Cars 0.40 0.32 0.26 0.20 0.16 0.13 0.13 0.13 0.13 0.13
- Old Depreciation, Tank Cars 0.42 0.34 0.27 0.22 0.17 0.14 0.14 0.14 0.14 0.14
Total Change in Depreciation 1.20 1.04 0.90 1.18 1.00 0.42 0.37 0.33 0.30 0.29 0.42 0.42 0.42 0.29 0.29

3. Pretax Incremental Profit -0.43 2.31 2.55 2.38 2.67 2.52 2.65 2.78 2.91 3.02 1.52 1.58 1.64 1.83 1.90
4. Tax Expense -0.13 0.69 0.77 0.71 0.80 0.76 0.80 0.83 0.87 0.91 0.46 0.47 0.49 0.55 0.57

5. After-tax Profit -0.30 1.62 1.79 1.66 1.87 1.76 1.86 1.95 2.04 2.11 1.07 1.11 1.15 1.28 1.33
6. Cash Flow Adjustments
CAPEX -9.00 -2.00 2.12
Depreciation 1.20 1.04 0.90 1.18 1.00 0.42 0.37 0.33 0.30 0.29 0.42 0.42 0.42 0.29 0.29
Working Capital Merseyside 0.31 -0.16 -0.16 -0.17 -0.17 -0.20 -0.21 -0.22 -0.22 -0.23 -0.28 -0.29 -0.30 -0.31 -0.31
Change WC Merseyside 0.31 -0.47 0.00 0.00 -0.01 -0.03 -0.01 -0.01 -0.01 -0.01 -0.05 -0.01 -0.01 -0.01 -0.01
Change WC Rotterdam 0.25 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01
Recovery WC -0.07
After-tax Scrap Proceeds 0.00
7. Free Cash Flow -9.00 1.46 2.19 0.69 2.85 4.99 2.16 2.23 2.28 2.34 2.40 1.45 1.53 1.57 1.58 1.56

NPV= 7.29
IRR= 22.5%