利用Excel自動實現(xiàn)投資項目敏感性分析

郁玉環(huán)

【摘 要】 投資項目敏感性分析的Excel實現(xiàn)需解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。文章創(chuàng)新設計了不確定性因素基本系數區(qū)域，從而可以運用Excel模擬運算表解決上述三個問題。該設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析過程得以自動實現(xiàn)。

【關鍵詞】 投資項目； 敏感性分析； Excel； 模擬運算表

投資項目敏感性分析是投資項目決策中常用的一種重要的分析方法，它是通過保持其他假設變量不變，調整某個假設變量的取值，計算改變后的評價指標內部收益率（IRR）或凈現(xiàn)值（NPV）的影響，不斷重復測算；然后將所有變動結果同基本分析結合起來，根據評價指標的變動程度判斷項目的風險大小，并決定項目是否可行。

一、敏感性分析的步驟

（一）確定敏感性分析指標

一般選擇項目IRR與NPV指標作為分析對象。

（二）選擇不確定性因素（假設變量）

影響項目經濟效益的因素很多，敏感性分析通常選擇對投資項目資金流量起主要作用的因素，包括項目投資額、營業(yè)收入與經營成本。

（三）確定不確定性因素的變化范圍

一般選擇±20%、±15%、±10%，以5%為間隔，變化范圍越大，需要測算的次數越多。

（四）進行敏感性分析并找出敏感性因素

分別對投資額、營業(yè)收入、經營成本按變化范圍進行測算，得到不同變化范圍下的IRR與NPV。如本例不確定性因素的變化范圍選擇±20%，則需要測算8（變化率）×3（不確定因素）×2（分析指標）共48次，過程較為繁瑣。

（五）編制敏感性分析表，繪制敏感性分析圖

二、案例資料

甲項目固定資產投資120萬元，其中第1年年初和第2年年初投資分別為70萬元和50萬元，第1年年末項目竣工并投入試生產；項目生產期10年，固定資產直線法折舊，預計凈殘值率10%；項目投產時需墊支流動資金50萬元，第1年年末支付30萬元，第2年年末支付20萬元，項目結束時收回；項目投產后第1年營業(yè)收入60萬元、第2年80萬元，第3—8年100萬元/年，第9年每年減少為85萬元，第10年減少為60萬元，項目投產后第1年經營成本38萬元，第2年45萬元，第3—8年65萬元/年，第9年減少到48萬元，第10年減少到35萬元；該企業(yè)適用所得稅率為25%，基準折現(xiàn)率10%。根據以上資料，甲項目投資現(xiàn)金流量表（簡表）與基本評價結果如表1所示。

為進行敏感性分析，表1中回收固定資產余值（N6）、回收流動資金（N7）、調整所得稅等項目都應設計為公式自動計算，因為當投資、收入與經營成本發(fā)生變化時，其結果也會發(fā)生相應變化。其中公式：

三、運用Excel模擬運算表進行投資項目敏感性分析設計思路

如何利用Excel進行投資項目敏感性分析，現(xiàn)有的做法歸納有兩種：一是枚舉法。先設計一個NPV與IRR的基本計算表，其中NPV與IRR運用Excel公式計算；然后對每個不確定性因素（假設變量）按變化率分別測算，得到各變量不同變化率的評價結果。這種方法僅解決了NPV與IRR的自動計算問題，但未簡化繁瑣的測算過程。二是利用Excel“窗體”中的“微調項”工具按鈕設置變化程度，通過移動滾動條得到每一變量不同變化程度下的評價指標。這種做法必須假設投資是一次性投入，收入、經營成本在不同的時期保持不變，而現(xiàn)實中大多數項目是分期投資的，收入與經營成本在投產前期受投產率影響逐漸提高，臨近項目結束時因產能下降而逐漸下降。本文案例按此情況設計。另外，現(xiàn)有的兩種做法測算的結果不能保存，每做一次測算都需要單獨記錄評價結果。因此在設計中應解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。

Excel模擬運算表是一種只需一步操作就能計算出所有變化的模擬分析工具。它可以顯示公式中某些值的變化對計算結果的影響，為同時求解某一運算中所有可能的變化值組合提供了捷徑。因此運用Excel模擬運算表能很好地解決敏感分析中的分別測算問題和測算結果保存問題。但如何解決分期投資、收入、經營成本在不同時期不等的問題，則需要單獨設計。模擬運算表中引用行或列是對應原計算公式中的一個單元格。顯然，表1中投資、收入與經營成本的行引用在NPV與IRR的計算公式中都是多個單元格。因此運用Excel模擬運算表的難點是解決行單元格的引用問題。

為此，本文創(chuàng)新地設置一個不確定性因素基本系數區(qū)域，將不確定因素的基本系數乘到基本現(xiàn)金流量表運算公式中，模擬運算表只需將引用行或列單元格設為基本系數就可以解決1個單元格的問題了。

四、運用Excel模擬運算表實現(xiàn)投資項目敏感性分析

第1步：將“項目投資現(xiàn)金流量表”復制到sheet2，將sheet2命名為“敏感性分析表”，表1“項目投資現(xiàn)金流量表”更名為“表2項目投資現(xiàn)金流量表（含基本系數）”。

第2步：設置不確定性因素基本系數區(qū)域（圖1）。

第3步：用基本系數乘表2中的每個營業(yè)收入、建設投資、流動資金、經營成本，將基本系數與表2聯(lián)結在一起。公式為：

經過以上處理，項目基本評價結果與表1結果完全一致。

第4步：利用模擬運算表得到各不確定性因素±20%變化區(qū)間的NPV與IRR（圖2）。對固定資產投資的測算，設置從0.8、0.85到1.2共9個變化區(qū)間。

在E24與E25單元格分別輸入IRR與NPV的計算公式，E24=$C$18，E25=$C$19，選中E23：N25區(qū)域點菜單欄“工具”—“模擬運算表”，在引用行的單元格輸入$C$23（固定資產投資的基本系數），就得到了9個變化區(qū)間的IRR與NPV的評價結果（見圖2）。

對墊支流動資金測算，將E23：N25區(qū)域復制到E27：N29區(qū)域，將模擬運算表中引用行的單元格改為C24即完成，同理營業(yè)收入與經營成本見圖3。

第5步：編制敏感性分析表（圖4），繪制敏感性分析圖（圖5）。

將第3步模擬運算表中的計算結果直接復制到圖4中的表3、表4中，就完成了敏感性分析表的編制。公式：

設C41=F24，C42=F28，C43=F32，C44=F36，向右填充，完成表3；設C48=F25，C49=F29，C50=F33，C51=F37，向右填充，完成表4。

根據圖4中的表3、表4繪制敏感性分析圖，如圖5、圖6。

從圖5、圖6結果看，甲項目營業(yè)收入敏感程度最高，當營業(yè)收入從降低20%到增長20%，IRR則從2.52%上升到19.47%，NPV從-54.12上升到79.72，依次是經營成本、建設投資、流動資金。比較各變化區(qū)間發(fā)現(xiàn)，甲項目當收入下降5%、經營成本增長10%、固定資產投資增加15%時，IRR與NPV均不可行，因此投資者在進行投資決策時應重點考察營業(yè)收入、經營成本與固定資產投資實際可能的變化率，考慮其風險慎重決策。

五、結論

敏感性分析可以反映不確定性因素與項目經濟效益的依存關系，有助于投資決策者了解項目在條件發(fā)生變化時的各種可能結果，從而可以預先考慮措施與對策，避免決策上的失誤。本文所做創(chuàng)新設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析的過程得以自動實現(xiàn)。

【參考文獻】

[1] China Certified Public Accountant Association； financial cost management； China Financial and Economic Press； 2009：146-147.

[2] Zhong Aijun； Excel application of investment project sensitivity analysis； Accounting communication. comprehensive version； 2007 Volume 11：67-68.endprint

【摘 要】 投資項目敏感性分析的Excel實現(xiàn)需解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。文章創(chuàng)新設計了不確定性因素基本系數區(qū)域，從而可以運用Excel模擬運算表解決上述三個問題。該設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析過程得以自動實現(xiàn)。

【關鍵詞】 投資項目； 敏感性分析； Excel； 模擬運算表

投資項目敏感性分析是投資項目決策中常用的一種重要的分析方法，它是通過保持其他假設變量不變，調整某個假設變量的取值，計算改變后的評價指標內部收益率（IRR）或凈現(xiàn)值（NPV）的影響，不斷重復測算；然后將所有變動結果同基本分析結合起來，根據評價指標的變動程度判斷項目的風險大小，并決定項目是否可行。

一、敏感性分析的步驟

（一）確定敏感性分析指標

一般選擇項目IRR與NPV指標作為分析對象。

（二）選擇不確定性因素（假設變量）

影響項目經濟效益的因素很多，敏感性分析通常選擇對投資項目資金流量起主要作用的因素，包括項目投資額、營業(yè)收入與經營成本。

（三）確定不確定性因素的變化范圍

一般選擇±20%、±15%、±10%，以5%為間隔，變化范圍越大，需要測算的次數越多。

（四）進行敏感性分析并找出敏感性因素

分別對投資額、營業(yè)收入、經營成本按變化范圍進行測算，得到不同變化范圍下的IRR與NPV。如本例不確定性因素的變化范圍選擇±20%，則需要測算8（變化率）×3（不確定因素）×2（分析指標）共48次，過程較為繁瑣。

（五）編制敏感性分析表，繪制敏感性分析圖

二、案例資料

甲項目固定資產投資120萬元，其中第1年年初和第2年年初投資分別為70萬元和50萬元，第1年年末項目竣工并投入試生產；項目生產期10年，固定資產直線法折舊，預計凈殘值率10%；項目投產時需墊支流動資金50萬元，第1年年末支付30萬元，第2年年末支付20萬元，項目結束時收回；項目投產后第1年營業(yè)收入60萬元、第2年80萬元，第3—8年100萬元/年，第9年每年減少為85萬元，第10年減少為60萬元，項目投產后第1年經營成本38萬元，第2年45萬元，第3—8年65萬元/年，第9年減少到48萬元，第10年減少到35萬元；該企業(yè)適用所得稅率為25%，基準折現(xiàn)率10%。根據以上資料，甲項目投資現(xiàn)金流量表（簡表）與基本評價結果如表1所示。

為進行敏感性分析，表1中回收固定資產余值（N6）、回收流動資金（N7）、調整所得稅等項目都應設計為公式自動計算，因為當投資、收入與經營成本發(fā)生變化時，其結果也會發(fā)生相應變化。其中公式：

三、運用Excel模擬運算表進行投資項目敏感性分析設計思路

如何利用Excel進行投資項目敏感性分析，現(xiàn)有的做法歸納有兩種：一是枚舉法。先設計一個NPV與IRR的基本計算表，其中NPV與IRR運用Excel公式計算；然后對每個不確定性因素（假設變量）按變化率分別測算，得到各變量不同變化率的評價結果。這種方法僅解決了NPV與IRR的自動計算問題，但未簡化繁瑣的測算過程。二是利用Excel“窗體”中的“微調項”工具按鈕設置變化程度，通過移動滾動條得到每一變量不同變化程度下的評價指標。這種做法必須假設投資是一次性投入，收入、經營成本在不同的時期保持不變，而現(xiàn)實中大多數項目是分期投資的，收入與經營成本在投產前期受投產率影響逐漸提高，臨近項目結束時因產能下降而逐漸下降。本文案例按此情況設計。另外，現(xiàn)有的兩種做法測算的結果不能保存，每做一次測算都需要單獨記錄評價結果。因此在設計中應解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。

Excel模擬運算表是一種只需一步操作就能計算出所有變化的模擬分析工具。它可以顯示公式中某些值的變化對計算結果的影響，為同時求解某一運算中所有可能的變化值組合提供了捷徑。因此運用Excel模擬運算表能很好地解決敏感分析中的分別測算問題和測算結果保存問題。但如何解決分期投資、收入、經營成本在不同時期不等的問題，則需要單獨設計。模擬運算表中引用行或列是對應原計算公式中的一個單元格。顯然，表1中投資、收入與經營成本的行引用在NPV與IRR的計算公式中都是多個單元格。因此運用Excel模擬運算表的難點是解決行單元格的引用問題。

為此，本文創(chuàng)新地設置一個不確定性因素基本系數區(qū)域，將不確定因素的基本系數乘到基本現(xiàn)金流量表運算公式中，模擬運算表只需將引用行或列單元格設為基本系數就可以解決1個單元格的問題了。

四、運用Excel模擬運算表實現(xiàn)投資項目敏感性分析

第1步：將“項目投資現(xiàn)金流量表”復制到sheet2，將sheet2命名為“敏感性分析表”，表1“項目投資現(xiàn)金流量表”更名為“表2項目投資現(xiàn)金流量表（含基本系數）”。

第2步：設置不確定性因素基本系數區(qū)域（圖1）。

第3步：用基本系數乘表2中的每個營業(yè)收入、建設投資、流動資金、經營成本，將基本系數與表2聯(lián)結在一起。公式為：

經過以上處理，項目基本評價結果與表1結果完全一致。

第4步：利用模擬運算表得到各不確定性因素±20%變化區(qū)間的NPV與IRR（圖2）。對固定資產投資的測算，設置從0.8、0.85到1.2共9個變化區(qū)間。

在E24與E25單元格分別輸入IRR與NPV的計算公式，E24=$C$18，E25=$C$19，選中E23：N25區(qū)域點菜單欄“工具”—“模擬運算表”，在引用行的單元格輸入$C$23（固定資產投資的基本系數），就得到了9個變化區(qū)間的IRR與NPV的評價結果（見圖2）。

對墊支流動資金測算，將E23：N25區(qū)域復制到E27：N29區(qū)域，將模擬運算表中引用行的單元格改為C24即完成，同理營業(yè)收入與經營成本見圖3。

第5步：編制敏感性分析表（圖4），繪制敏感性分析圖（圖5）。

將第3步模擬運算表中的計算結果直接復制到圖4中的表3、表4中，就完成了敏感性分析表的編制。公式：

設C41=F24，C42=F28，C43=F32，C44=F36，向右填充，完成表3；設C48=F25，C49=F29，C50=F33，C51=F37，向右填充，完成表4。

根據圖4中的表3、表4繪制敏感性分析圖，如圖5、圖6。

從圖5、圖6結果看，甲項目營業(yè)收入敏感程度最高，當營業(yè)收入從降低20%到增長20%，IRR則從2.52%上升到19.47%，NPV從-54.12上升到79.72，依次是經營成本、建設投資、流動資金。比較各變化區(qū)間發(fā)現(xiàn)，甲項目當收入下降5%、經營成本增長10%、固定資產投資增加15%時，IRR與NPV均不可行，因此投資者在進行投資決策時應重點考察營業(yè)收入、經營成本與固定資產投資實際可能的變化率，考慮其風險慎重決策。

五、結論

敏感性分析可以反映不確定性因素與項目經濟效益的依存關系，有助于投資決策者了解項目在條件發(fā)生變化時的各種可能結果，從而可以預先考慮措施與對策，避免決策上的失誤。本文所做創(chuàng)新設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析的過程得以自動實現(xiàn)。

【參考文獻】

[1] China Certified Public Accountant Association； financial cost management； China Financial and Economic Press； 2009：146-147.

[2] Zhong Aijun； Excel application of investment project sensitivity analysis； Accounting communication. comprehensive version； 2007 Volume 11：67-68.endprint

【摘 要】 投資項目敏感性分析的Excel實現(xiàn)需解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。文章創(chuàng)新設計了不確定性因素基本系數區(qū)域，從而可以運用Excel模擬運算表解決上述三個問題。該設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析過程得以自動實現(xiàn)。

【關鍵詞】 投資項目； 敏感性分析； Excel； 模擬運算表

投資項目敏感性分析是投資項目決策中常用的一種重要的分析方法，它是通過保持其他假設變量不變，調整某個假設變量的取值，計算改變后的評價指標內部收益率（IRR）或凈現(xiàn)值（NPV）的影響，不斷重復測算；然后將所有變動結果同基本分析結合起來，根據評價指標的變動程度判斷項目的風險大小，并決定項目是否可行。

一、敏感性分析的步驟

（一）確定敏感性分析指標

一般選擇項目IRR與NPV指標作為分析對象。

（二）選擇不確定性因素（假設變量）

影響項目經濟效益的因素很多，敏感性分析通常選擇對投資項目資金流量起主要作用的因素，包括項目投資額、營業(yè)收入與經營成本。

（三）確定不確定性因素的變化范圍

一般選擇±20%、±15%、±10%，以5%為間隔，變化范圍越大，需要測算的次數越多。

（四）進行敏感性分析并找出敏感性因素

分別對投資額、營業(yè)收入、經營成本按變化范圍進行測算，得到不同變化范圍下的IRR與NPV。如本例不確定性因素的變化范圍選擇±20%，則需要測算8（變化率）×3（不確定因素）×2（分析指標）共48次，過程較為繁瑣。

（五）編制敏感性分析表，繪制敏感性分析圖

二、案例資料

甲項目固定資產投資120萬元，其中第1年年初和第2年年初投資分別為70萬元和50萬元，第1年年末項目竣工并投入試生產；項目生產期10年，固定資產直線法折舊，預計凈殘值率10%；項目投產時需墊支流動資金50萬元，第1年年末支付30萬元，第2年年末支付20萬元，項目結束時收回；項目投產后第1年營業(yè)收入60萬元、第2年80萬元，第3—8年100萬元/年，第9年每年減少為85萬元，第10年減少為60萬元，項目投產后第1年經營成本38萬元，第2年45萬元，第3—8年65萬元/年，第9年減少到48萬元，第10年減少到35萬元；該企業(yè)適用所得稅率為25%，基準折現(xiàn)率10%。根據以上資料，甲項目投資現(xiàn)金流量表（簡表）與基本評價結果如表1所示。

為進行敏感性分析，表1中回收固定資產余值（N6）、回收流動資金（N7）、調整所得稅等項目都應設計為公式自動計算，因為當投資、收入與經營成本發(fā)生變化時，其結果也會發(fā)生相應變化。其中公式：

三、運用Excel模擬運算表進行投資項目敏感性分析設計思路

如何利用Excel進行投資項目敏感性分析，現(xiàn)有的做法歸納有兩種：一是枚舉法。先設計一個NPV與IRR的基本計算表，其中NPV與IRR運用Excel公式計算；然后對每個不確定性因素（假設變量）按變化率分別測算，得到各變量不同變化率的評價結果。這種方法僅解決了NPV與IRR的自動計算問題，但未簡化繁瑣的測算過程。二是利用Excel“窗體”中的“微調項”工具按鈕設置變化程度，通過移動滾動條得到每一變量不同變化程度下的評價指標。這種做法必須假設投資是一次性投入，收入、經營成本在不同的時期保持不變，而現(xiàn)實中大多數項目是分期投資的，收入與經營成本在投產前期受投產率影響逐漸提高，臨近項目結束時因產能下降而逐漸下降。本文案例按此情況設計。另外，現(xiàn)有的兩種做法測算的結果不能保存，每做一次測算都需要單獨記錄評價結果。因此在設計中應解決分別測算問題、測算結果保存問題以及分期投資、收入、經營成本在不同時期不等的問題。

Excel模擬運算表是一種只需一步操作就能計算出所有變化的模擬分析工具。它可以顯示公式中某些值的變化對計算結果的影響，為同時求解某一運算中所有可能的變化值組合提供了捷徑。因此運用Excel模擬運算表能很好地解決敏感分析中的分別測算問題和測算結果保存問題。但如何解決分期投資、收入、經營成本在不同時期不等的問題，則需要單獨設計。模擬運算表中引用行或列是對應原計算公式中的一個單元格。顯然，表1中投資、收入與經營成本的行引用在NPV與IRR的計算公式中都是多個單元格。因此運用Excel模擬運算表的難點是解決行單元格的引用問題。

為此，本文創(chuàng)新地設置一個不確定性因素基本系數區(qū)域，將不確定因素的基本系數乘到基本現(xiàn)金流量表運算公式中，模擬運算表只需將引用行或列單元格設為基本系數就可以解決1個單元格的問題了。

四、運用Excel模擬運算表實現(xiàn)投資項目敏感性分析

第1步：將“項目投資現(xiàn)金流量表”復制到sheet2，將sheet2命名為“敏感性分析表”，表1“項目投資現(xiàn)金流量表”更名為“表2項目投資現(xiàn)金流量表（含基本系數）”。

第2步：設置不確定性因素基本系數區(qū)域（圖1）。

第3步：用基本系數乘表2中的每個營業(yè)收入、建設投資、流動資金、經營成本，將基本系數與表2聯(lián)結在一起。公式為：

經過以上處理，項目基本評價結果與表1結果完全一致。

第4步：利用模擬運算表得到各不確定性因素±20%變化區(qū)間的NPV與IRR（圖2）。對固定資產投資的測算，設置從0.8、0.85到1.2共9個變化區(qū)間。

在E24與E25單元格分別輸入IRR與NPV的計算公式，E24=$C$18，E25=$C$19，選中E23：N25區(qū)域點菜單欄“工具”—“模擬運算表”，在引用行的單元格輸入$C$23（固定資產投資的基本系數），就得到了9個變化區(qū)間的IRR與NPV的評價結果（見圖2）。

對墊支流動資金測算，將E23：N25區(qū)域復制到E27：N29區(qū)域，將模擬運算表中引用行的單元格改為C24即完成，同理營業(yè)收入與經營成本見圖3。

第5步：編制敏感性分析表（圖4），繪制敏感性分析圖（圖5）。

將第3步模擬運算表中的計算結果直接復制到圖4中的表3、表4中，就完成了敏感性分析表的編制。公式：

設C41=F24，C42=F28，C43=F32，C44=F36，向右填充，完成表3；設C48=F25，C49=F29，C50=F33，C51=F37，向右填充，完成表4。

根據圖4中的表3、表4繪制敏感性分析圖，如圖5、圖6。

從圖5、圖6結果看，甲項目營業(yè)收入敏感程度最高，當營業(yè)收入從降低20%到增長20%，IRR則從2.52%上升到19.47%，NPV從-54.12上升到79.72，依次是經營成本、建設投資、流動資金。比較各變化區(qū)間發(fā)現(xiàn)，甲項目當收入下降5%、經營成本增長10%、固定資產投資增加15%時，IRR與NPV均不可行，因此投資者在進行投資決策時應重點考察營業(yè)收入、經營成本與固定資產投資實際可能的變化率，考慮其風險慎重決策。

五、結論

敏感性分析可以反映不確定性因素與項目經濟效益的依存關系，有助于投資決策者了解項目在條件發(fā)生變化時的各種可能結果，從而可以預先考慮措施與對策，避免決策上的失誤。本文所做創(chuàng)新設計一次性解決了內部收益率、凈現(xiàn)值多次測算、記錄等繁瑣問題，使投資項目敏感性分析的過程得以自動實現(xiàn)。

【參考文獻】

[1] China Certified Public Accountant Association； financial cost management； China Financial and Economic Press； 2009：146-147.

[2] Zhong Aijun； Excel application of investment project sensitivity analysis； Accounting communication. comprehensive version； 2007 Volume 11：67-68.endprint