商业金融和投资评估Business Finance and Investment Appraisal

Excel使用和投资价值评估Excel use and Investment Appraisal

(1)  Bond valuation: 债券估值
一个公司拥有100份债券 ，每个债券面值1000英镑，并支付5％的利息。债券有效期限是10年，但该公司将在4年后出售这些债券，当时的期望利率为6％。
债券价值和利率之间的公式是（根据凯恩斯的理论）：
A company holds 100 identical bonds – each bond is sold at par value of £1000, and pays 5%.  The bond period is 10 years, but the company will dispose of these after 4 years and expects interest rates at that time to be 6%.
The relationship between bond values and interest rates can be stated (based on Keynes’ work):
 
该公司的折现率是9％。建立此债券投资组合的现金流量预测，并评估其净现值。
The firm operates with a discount rate of 9%.  Build up the ‘cash flow forecast’ for this bond portfolio and evaluate its Net Present Value.

(2)  Chesherton fibres:
Load the spreadsheet ‘Chesherton Fibres model answer_Nov07.xls’.  There are several parts to note
• Rows 3 to 29 – this is a ‘data entry’ area. 
o It provides key values for Investment costs (land, vehicles, etc.), variables costs (materials, etc.), fixed costs (admin, etc.).
o Altering the values in these ‘change cells’ can be automatically fed through to all the following calculations, so that different scenarios can be easily tested.
• Rows 34 onwards provide the relationships that give specific values, e.g.:

Row:    Relationship  Function  http://ukthesis.org/jjxzy/
Tb1 INVESTMENT COSTS Column B Column B
35  Cell B4 (land investment cost data entry) x B12 (spread of land investment cost) =b4*b12
36  B5 (buildings cost) x B13, C13, D13, etc.. (spread of cost) =b5*b13, can be entered as $b$5*b$13 and then pasted along row (into columns C and D – the $ sign fixes a row number or column letter)
37  B6 (Machinery) x B,C,D14 (spread) =b6*b14, can be entered as above
38  B7 (Vehicles) x D15 (spread) =b7*d15, but note replacement every 5 years also…
Tb2 PRODUCTION & SALES Column E on Column E on
45 Production tns DATA ENTRY DATA ENTRY
46 Stock D46 (previous stock) + (E45 – E48 i.e. production – sales or unsold production) =d46+(e45-e48)
47 Incremental stock E45 – E48 =(e45-e48)
48 Sales tonnes DATA ENTRY DATA ENTRY
49 Sales value E48 (sales tns) x B18 (Price) =e48*b18
Tb3 COSTS  #p#分页标题#e#
56 Raw materials B20 x row 45 =$b$20*e45
57 Utilities B21 x row 45 =$b$21*e45
58 Direct Labour  B22 x row 45 =$b$22*e45
59 Other Direct cst B23 x row 45 =$b$23*e45
   
61 Utilities FC B28 =$b$28
62 Maintenance FC B6 x row 27 (extend 5%s…) =$b$6*e27, etc..
63 Admin FC B29 =$b$29
   
Tb4  Working capital details.. 
Tb5  Summary table – incremental cash flow 

执行以下操作，看看如何影响行净现值
1、减少机械采购
2、（反转之前所做的更改，然后）维修费用翻倍。
3、（反转之前所做的更改，然后）单价增长为每吨45,000英镑。
4、（反转之前所做的更改，然后）土地出售价值翻倍（表1中的16年）

鉴于上述变化的反应：
•解释结果是如何微小变化的以及变化时间

Do the following and see how this impacts on the NPVs in rows 96-99
• Reduce the spread of machinery purchases so that 50% occurs in each of years 1 and 2
• (Reverse the previous change, and then) Double the maintenance costs.
• (Reverse the previous change, and then) Increase the unit price to £45,000 per tonne.
• (Reverse the previous change, and then) Double the disposal value for land (Year 16 in Table 1)

Given the responses to the above changes:
• Comment on how sensitive the results are to minor changes, and their timing