Model Facts
EC(A).XLS
This spreadsheet supports STUDENT analysis of the case, "Empirical
Chemicals (A)" (UVA-F-1020 v. 2.3).
Please note:
1) All worksheets have been protected to prevent any inadvertent errors.
To unprotect the worksheet press Control "u" and to protect worksheet
press Control "p" (use lower case).
2) This is a working model. Assumptions / Inputs presented can be
changed to vary the results.
3) 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.
Revised: 9-Jan-98
Copyright (C) 1998 by the Trustees of the University of Virginia Darden School Foundation.
Page 1
Exhibit 2 VA-F-1020
EMPIRICAL CHEMICALS (A)
Jim Hawkin's DCF Analysis of Merseyside Project
(Financial values in millions of British Pounds)
Assumptions
Annual Output (metric tons) 135,000 Discount rate 13.0%
Output Gain/Original Output 7.0% Depreciable Life (years) 15
Price/ton (pounds sterling) 611 Overhead/Investment 3.5%
Inflation Rate (prices and costs) 0.0% Salvage Value 0
Gross Margin (ex. Deprec.) 12.90% WIP Inventory/Cost of Goods 3.0%
Old Gross Margin 11.5% Months Downtime, Construction 2
Tax Rate 35.0% After-tax Scrap Proceeds 0
Investment Outlay (mill.) 7.00 Preliminary Engineering Costs 0.5
Energy Savings/Sales Yr. 1-5 1.0%
Yr. 6-10 0.5%
Yr. 11-15 0.0%
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Year Now 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006
1. Estimate of Incremental Gross Profit
New Output (tons) 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450 144,450
Lost Output--Construction (22,500)
New Sales (Millions) 74.51 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26 88.26
New Gross Margin 13.9% 13.9% 13.9% 13.9% 13.9% 13.4% 13.4% 13.4% 13.4% 13.4% 12.9% 12.9% 12.9% 12.9% 12.9%
New Gross Profit 10.36 12.27 12.27 12.27 12.27 11.83 11.83 11.83 11.83 11.83 11.39 11.39 11.39 11.39 11.39
Old Output 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000 135,000
Old Sales 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49 82.49
Old Gross Profit 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49 9.49
Incremental Gross Profit 0.87 2.78 2.78 2.78 2.78 2.34 2.34 2.34 2.34 2.34 1.90 1.90 1.90 1.90 1.90
2. Estimate of Incremental Depreciation
New Depreciation 0.93 0.81 0.70 0.61 0.53 0.46 0.40 0.34 0.32 0.32 0.32 0.32 0.32 0.32 0.32
3. Overhead 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25
4. Prelim. Engineering Costs 0.50
5. Pretax Incremental Profit -0.81 1.73 1.84 1.93 2.01 1.64 1.70 1.75 1.78 1.78 1.33 1.33 1.33 1.33 1.33
6. Tax Expense -0.28 0.60 0.64 0.68 0.70 0.57 0.60 0.61 0.62 0.62 0.47 0.47 0.47 0.47 0.47
7. After-tax Profit -0.52 1.12 1.19 1.25 1.31 1.07 1.11 1.14 1.15 1.15 0.87 0.87 0.87 0.87 0.87
8. Cash Flow Adjustments
Less Capital Expendi -7.00
Add back Depreciation 0.93 0.81 0.70 0.61 0.53 0.46 0.40 0.34 0.32 0.32 0.32 0.32 0.32 0.32 0.32
Less Added WIP inventory 0.27 -0.36 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
After-tax Scrap Proceeds 0.00
8. Free Cash Flow -7.00 0.67 1.58 1.89 1.86 1.83 1.52 1.50 1.48 1.47 1.47 1.19 1.19 1.19 1.19 1.19
NPV = 2.36
IRR = 19.2%
