商業金融和投資評估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