第3章excel在財務(wù)管理與分析中應(yīng)用

第三章Excel在財務(wù)管理

與分析中的應(yīng)用第一節(jié)經(jīng)濟(jì)定貨量模型

經(jīng)濟(jì)訂貨量（EconomicOrderQuantityEOQ）是采用數(shù)學(xué)的方法計算出庫存的每次訂貨量，這一訂貨量能夠使在一定時期內(nèi)，某一品種的庫存總成本達(dá)到最低。一、經(jīng)濟(jì)訂貨量的前提條件

在一定時期內(nèi)某種商品的需求量保持不變

每次訂貨成本都保持不變

單件商品的存儲成本（年存儲成本）固定不變

初始庫存量為零

庫存能夠得到及時補(bǔ)充（即不考慮安全庫存）P141二、經(jīng)濟(jì)訂貨量模型的基本概念

平均庫存量根據(jù)以上的前提條件，平均庫存量可用以下公式表示：

平均庫存量=Q/2Q

：商品一次訂貨量

儲存成本

儲存成本=P*K*Q/2P

：貨物單價（采購單價）

K

：貨物的年存儲費(fèi)率

單位年儲存成本是指一件商品在倉庫中保存一年的儲存成本。

單位年存儲成本（h）=

P*K

因此，儲存成本等于平均庫存量與單位年儲存成本的乘積，即：

儲存成本=P*K*Q/2=h*Q/2

訂貨成本訂貨成本等于單位訂貨成本乘以訂貨次數(shù)。由于在一定時期內(nèi)（一般為一年），商品的總需求量（年需求量）是一定的，所以：

訂貨成本=A*D/QA：一次訂貨成本

D

：商品全年的需求量

采購成本由于全年商品的需求量是固定的，因此，無論每次訂貨量為多少，全年所支付的采購成本是固定的，與訂貨量無關(guān)。

采購成本=P*DP：商品單價

總成本（C）總成本應(yīng)是采購成本、訂貨成本和儲存成本之和，即：C=D*P+A*D/Q+P*K*Q/2

鑒于采購成本為固定的，與訂貨量無關(guān)，因此在討論經(jīng)濟(jì)訂貨量時，可以將企業(yè)的訂儲成本看成是儲存成本與訂貨成本之和，即：

C=A*D/Q+P*K*Q/2C=A*D/Q+P*K*Q/2

由以上的訂儲成本的計算公式可看出：

訂貨量（Q）越大，儲存成本就越高，而訂貨成本就越低；反之，訂貨量越小，儲存成本就越低，但訂貨成本卻越高。

企業(yè)需要在增加訂貨量所節(jié)約的成本與增加庫存量所提高的成本之間進(jìn)行權(quán)衡，以求得兩者之間最佳的組合。

經(jīng)濟(jì)訂貨量經(jīng)濟(jì)訂貨量就是指使總成本（訂儲成本）最低的一次訂貨批量。

Q*=SQRT(2*D*A/h)或

Q*=SQRT(2*D*A/(P*K))

在經(jīng)濟(jì)訂貨量下：

年訂貨成本

=年儲存成本

即：A*D/Q*=P*K*Q*/2

年總成本的最小值為：

Cmin=SQRT(2*D*A*h)或Cmin=SQRT(2*D*A*P*K)D：年需求量

A：一次訂貨成本P：產(chǎn)品單價 K：年儲存費(fèi)率h：單位年儲存成本 三、經(jīng)濟(jì)訂貨量模型舉例1、基本經(jīng)濟(jì)訂貨量模型【例】

某企業(yè)全年需要某種材料5000千克，一次訂貨費(fèi)用為20元，材料單價為25元/千克，材料的存儲費(fèi)率為10%。求該材料的經(jīng)濟(jì)訂貨量、全年訂貨次數(shù)和最低訂儲費(fèi)用?！纠?4】P142【例】

某企業(yè)采購某零件，全年需求量為15000件，每次訂貨成本為500元，單件零件的年儲存成本為30元，當(dāng)訂貨量為900件時，要求：（1）計算年訂貨成本、年儲存成本和年訂儲成本；（2）計算經(jīng)濟(jì)訂貨量及年訂儲成本極小值；（3）在本工作表中生成一個運(yùn)算表，計算當(dāng)訂貨量從200～1500變化時該零件的年訂貨成本、年儲存成本、年訂儲成本；（4）根據(jù)此運(yùn)算表制作無數(shù)據(jù)點平滑線散點圖；【經(jīng)濟(jì)訂貨量1A】（5）繪制反映該零件的年訂貨成本、年儲存成本、年訂儲成本隨年需求量（從10000按增量1000到20000）、訂貨量（從200按增量50到1500）變化的圖形（注：用微調(diào)框和文本框進(jìn)行控制和顯示當(dāng)前的值）；（6）在圖形上添加當(dāng)前訂貨量和經(jīng)濟(jì)訂貨量的垂直參考線及顯示參考點的數(shù)據(jù)值。【經(jīng)濟(jì)訂貨量1B】【例】某商店A商品的年銷量為8000，一次訂貨的訂貨成本為30元，單位年儲存成本為3.8元。（1）試確定該商品的經(jīng)濟(jì)訂貨量與年總成本的最小值。（2）建立一張運(yùn)算表，計算年需求量從5000按步長1000變化到10000，年訂貨量從200按步長50變化到500的年訂儲成本（訂貨成本與儲存成本之和）。（3）根據(jù)運(yùn)算表繪制出該商品的年訂貨成本、年儲存成本與年總成本隨訂貨量變化的曲線圖形（無數(shù)據(jù)點平滑線散點圖，曲線用灰色線表示）?！窘?jīng)濟(jì)訂貨量2A】（4）制作一個受年需求量控件控制的反映當(dāng)前年需求量的紅色曲線，使其在上述5條灰色曲線間移動（用微調(diào)框和文本框控制和顯示當(dāng)前處需求量的值）（5）紅色曲線的最低點沿綠色軌跡曲線（不同需求量下的經(jīng)濟(jì)訂貨量曲線）移動，而經(jīng)過該最低點的藍(lán)色垂直參考線也應(yīng)會作相應(yīng)的移動?！窘?jīng)濟(jì)定貨量2B】2、有數(shù)量折扣情況下的經(jīng)濟(jì)訂貨量模型前述的經(jīng)濟(jì)訂貨量的計算并不考慮商品購買時的單價。但面對采購商品的折扣優(yōu)惠可以按以下兩種思路去考慮：

折扣優(yōu)惠政策規(guī)定的訂貨量小于或等于經(jīng)濟(jì)訂貨量，則按經(jīng)濟(jì)訂貨量確定每次訂貨量。這樣既可以享受折扣優(yōu)惠政策又實現(xiàn)最佳的訂貨量。P144

折扣優(yōu)惠政策規(guī)定的訂貨量大于經(jīng)濟(jì)訂貨量，則必須進(jìn)行總成本分析。此時的年總成本不僅包含年訂貨成本和年儲存成本，還應(yīng)該考慮年采購成本，即商品的單價。

如果按折扣優(yōu)惠政策進(jìn)行訂貨的年總成本低于按經(jīng)濟(jì)訂貨量進(jìn)行訂貨的年總成本，則可以接受折扣優(yōu)惠政策；

如果按折扣優(yōu)惠政策進(jìn)行訂貨的年總成本高于按經(jīng)濟(jì)訂貨量進(jìn)行訂貨的年總成本，則放棄折扣優(yōu)惠政策，堅持經(jīng)濟(jì)訂貨量策略。進(jìn)行訂貨批量決策的步驟：①計算不同折扣價格下的經(jīng)濟(jì)訂貨量（按傳統(tǒng)的經(jīng)濟(jì)訂貨量計算方法計算），并判斷這些經(jīng)濟(jì)訂貨量是否有效（是否符合折扣優(yōu)惠政策）；②計算有效的經(jīng)濟(jì)訂貨量的年總成本和不同折扣起點下訂貨量的年總成本；③將計算出的年總成本進(jìn)行比較。

供貨商的折扣優(yōu)惠政策有兩種：

非連續(xù)價格形式的折扣優(yōu)惠即當(dāng)達(dá)到一定訂貨量限度后商品的單價整體降低的一種折扣優(yōu)惠形式

連續(xù)價格形式的折扣優(yōu)惠即當(dāng)訂貨量達(dá)到或超過折扣限量時，在特定的訂貨量范圍區(qū)間內(nèi)的訂貨量可以按折扣優(yōu)惠計價【例】

某企業(yè)每年需要配件20000件，每次訂貨費(fèi)用為1000元，存儲費(fèi)率是配件單價的15%。供貨商規(guī)定，凡一次性購買3000件以下的價格為8元/件，3000件或以上但6000件以下的價格為7元/件，6000件或以上但8000件以下的價格為6元/件，8000件或以上的價格為5元/件。問企業(yè)應(yīng)如何訂貨成本最低？【例46】P144【解題步驟】：①計算不同折扣價格下的經(jīng)濟(jì)訂貨量②判斷這些經(jīng)濟(jì)訂貨量是否有效③計算有效的經(jīng)濟(jì)訂貨量的年總成本和不同折扣起點下訂貨量的年總成本④將計算出的年總成本進(jìn)行比較

⑤數(shù)據(jù)分析——作出最佳選擇【例】

某企業(yè)采購某零件，全年需求量為15000件，每次訂貨成本為500元，單件零件的年儲存成本為30元。如果每次訂貨量大于等于1500件，則每件單價在原價4.5元/件的基礎(chǔ)上可以享受5%的優(yōu)惠折扣?！窘?jīng)濟(jì)訂貨量3】（1）建立數(shù)據(jù)模型，依據(jù)模型選擇訂貨策略；（2）建立訂貨量按增量100從300變化到4000的運(yùn)算表，計算不同訂貨量下的年總成本（訂貨成本、儲存成本、采購成本）；（3）按上述運(yùn)算表建立圖表（無數(shù)據(jù)點平滑線散點圖，生成的曲線在折扣閾限值處應(yīng)有一個陡降的效果）；（4）添加經(jīng)濟(jì)訂貨量垂直參考線和最低年總成本水平參考線；【例】

在上例數(shù)據(jù)的基礎(chǔ)上：（1）添加兩個微調(diào)框，其中一個控制折扣閾值，按增量100從1000變化到2000；另一個控制折扣率，按增量1%從5%變化到15%；【經(jīng)濟(jì)訂貨量3B】（2）創(chuàng)建價格優(yōu)惠條件下的年總成本變化圖表；（3）添加經(jīng)濟(jì)訂貨量垂直參考線和最低年總成本水平參考線；【例】某煉鋼廠每年需消耗20000噸礦石，每噸礦石價格250元，該廠每次訂購時，需花費(fèi)手續(xù)費(fèi)等合計6000元，儲存1噸礦石的年成本為50元。（1）在工作表中構(gòu)造一個模型來確定當(dāng)?shù)V石訂購量等于3000噸時的年采購成本、年訂貨成本、年儲存成本和年總成本。并計算出經(jīng)濟(jì)訂貨量及全年總成本；（2）建立一個運(yùn)算表，計算當(dāng)訂貨量從1400按步長100變化到3400時全年總成本隨訂貨量變化的值；【經(jīng)濟(jì)訂貨量4】（3）基于此運(yùn)算表制作一個訂貨量從1400按步長100變化到3400時表示全年總成本隨訂貨量變化的曲線圖形（無數(shù)據(jù)點平滑線散點圖）。（4）建立一運(yùn)算表，計算當(dāng)年單位儲存成本從50按步長1變化到60時的經(jīng)濟(jì)訂貨量和全年總成本；（5）基于此運(yùn)算表，在圖形上添加當(dāng)年單位儲存成本從50按步長1變化到60時的經(jīng)濟(jì)訂貨量和全年總成本構(gòu)成的空心紅色點子和紅色曲線；（6）在圖形上添加一條反映當(dāng)前經(jīng)濟(jì)訂貨量且隨控件變化的紅色水平參考線以及實心的紅色點子和該參考點的數(shù)據(jù)值。（7）用一個微調(diào)框和文本框控制和顯示當(dāng)前年儲存成本；紅色水平線、點子及藍(lán)色曲線隨其變化而變動?！窘?jīng)濟(jì)訂貨量4B】第二節(jié)投資決策模型一、投資決策概述1、投資決策的基本概述投資是企業(yè)（投資者）為適應(yīng)今后生產(chǎn)經(jīng)營過程中的長遠(yuǎn)需要而投入于固定資產(chǎn)增加、擴(kuò)建、更新、資源的開發(fā)與利用等方面的資金。因此投資支出也稱為資本支出。投資的方式很多，將投資的品種、投資的預(yù)期收益、投資的時間等投資因素進(jìn)行系統(tǒng)化和表格化的整理，可以形成一系列的備選方案。投資決策是指對一個投資項目的各種方案進(jìn)行對比分析，以選擇投資效益最佳的方案。P157

具體估算各個投資方案形成的各項現(xiàn)金流入和各期現(xiàn)金流出的數(shù)量，是正確計算投資方案經(jīng)濟(jì)評價的基礎(chǔ)，也是正確評價投資效益的一個必要條件。

現(xiàn)金流入是整個投資及其回收過程中所發(fā)生的實際現(xiàn)金收入。它包括項目投產(chǎn)后每年的產(chǎn)品銷售（營業(yè)）收入、固定資產(chǎn)報廢時的殘值收入，以及項目結(jié)束時回收的流動資金。

現(xiàn)金流出是整個投資及其回收過程中所發(fā)生的現(xiàn)金支出。它包括固定資產(chǎn)投資支出、流動資金投資支出、產(chǎn)品制造成本、銷售費(fèi)用以及管理費(fèi)用中以現(xiàn)金支付的部分和銷售稅金。

進(jìn)行投資方案比較與優(yōu)選是投資評價的關(guān)鍵。投資評價指標(biāo)的計算，歸根到底是為方案的對比與優(yōu)選服務(wù)的。備選投資方案的評價主要依據(jù)是凈現(xiàn)值、凈現(xiàn)值率和內(nèi)部收益率指標(biāo)。這3個指標(biāo)從不同側(cè)面反映投資項目的經(jīng)濟(jì)效益，也各有其局限性。2、貨幣的時間價值在不同時間點發(fā)生的同一數(shù)額的貨幣的價值是不同的。所謂貨幣的時間價值就是指同面額的貨幣在不同的時間點的價值不同。（1）資金的未來值將某一數(shù)額的資金在將來某年的價值稱為該資金的未來值。（2）資金的現(xiàn)值資金的現(xiàn)值是指未來某一特定金額的現(xiàn)在值，即與將來某年某一數(shù)額的資金等價的當(dāng)前價值。

計算未來值是從已知現(xiàn)在的金額（現(xiàn)值）、利率及期限測算將來的值；計算現(xiàn)值是從已知未來的金額（未來值）、利率及期限來測算相當(dāng)于現(xiàn)在的金額。把未來值折算為現(xiàn)值的過程稱為折現(xiàn)。

現(xiàn)值與折現(xiàn)率（貼現(xiàn)率）有密切的關(guān)系：折現(xiàn)率越高，折算的現(xiàn)值就越小。

未來值是本利之和，現(xiàn)值就是未來的收入扣除利息因素。（3）現(xiàn)金流量串的現(xiàn)值將每年發(fā)生相等金額的現(xiàn)金流量串（如每月應(yīng)付的房貸款）通常稱為年金。年金的現(xiàn)值即為各年發(fā)生的現(xiàn)金流量與“貼現(xiàn)因子”（數(shù)值上可以理解為貼現(xiàn)率）乘積之和。在現(xiàn)金流量的計算過程中，應(yīng)該明確現(xiàn)金流量發(fā)生在這個時間期的期初還是期末（期初和期末正好差一個時期）。（4）投資項目的凈現(xiàn)值投資項目的現(xiàn)金流量串的現(xiàn)值與初始投資額之差稱為投資項目的凈現(xiàn)值。一個投資項目的凈現(xiàn)值大于0，且它的回報超過了參考項目的回報，則這個投資項目是可取的；反之，如果投資項目的凈現(xiàn)值小于0，即它的未來收入不足以抵消當(dāng)前投資的金額，則這個投資項目是不可取的。這是對任何一個投資項目進(jìn)行評估時的準(zhǔn)則?，F(xiàn)值的計算可以通過NPV()函數(shù)來計算。（5）內(nèi)部報酬率在一個投資項目未來各年的現(xiàn)金流量串不變的條件下，其凈現(xiàn)值是隨著所使用的貼現(xiàn)率大小而變化的。所謂內(nèi)部報酬率（內(nèi)部收益率）就是資金流入現(xiàn)值總額與資金流出現(xiàn)值總額相等，即凈現(xiàn)值等于零時的貼現(xiàn)率。內(nèi)部報酬率可以通過IRR()函數(shù)來計算。3、投資決策模型

凈現(xiàn)值法模型在對兩個以上具有相同有效期的投資項目進(jìn)行選擇的準(zhǔn)則是：如果各個備選投資項目的凈現(xiàn)值中的極大值大于0，那么實現(xiàn)凈現(xiàn)值極大值的高效項目就是應(yīng)該被選擇的最優(yōu)項目，否則這些備選項目便無一可取。

內(nèi)部報酬率法模型在所涉及的各個備選項目初始投資額相同的情況下，也可以利用內(nèi)部報酬率作為決策的準(zhǔn)則：如果各個備選項目的內(nèi)部報酬率的極大值大于參考項目的回報率，則實現(xiàn)內(nèi)部報酬率極大值的投資項目就是應(yīng)該被選擇的最優(yōu)項目，否則這些項目就無一可取。

各個備選投資項目的初始投資額不同時，不能使用此準(zhǔn)則?！纠?/p>

某企業(yè)有A、B、C三個投資項目可供選擇：這3個項目的當(dāng)前（第0年）投資金額與今后3年（第1年~第3年）的預(yù)期回報分別如下表所示。（1）試建立一個決策模型，當(dāng)公司使用的貼現(xiàn)率為1%時，模型能給出這3個項目的最優(yōu)投資方案?！緝衄F(xiàn)值1A】初始投資額（萬元）預(yù)期回報（萬元）第1年第2年第3年項目A18001300900500項目B1800120010001000項目C200600400200（2）試建立一個模型，當(dāng)公司使用的貼現(xiàn)率在1%~15%范圍內(nèi)變化時，模型能給出這3個項目中的最優(yōu)最投資項目，并這3個項目的凈現(xiàn)值柱形圖。

注意觀察貼現(xiàn)率的變化對投資項目選擇的影響。在本例中，當(dāng)貼現(xiàn)率較小時，最優(yōu)投資項目是項目B；當(dāng)貼現(xiàn)率較大時（大于11%），最優(yōu)投資項目是C?！纠?/p>

某企業(yè)有甲、乙兩個投資項目，相關(guān)數(shù)據(jù)如下。試計算其凈現(xiàn)值和內(nèi)部報酬率，并選擇最優(yōu)方案?！緝?nèi)部報酬率1】▲ROUNDUP函數(shù)語法：ROUNDUP(number,num_digits)功能：對number遠(yuǎn)離零值，向上舍入數(shù)字。說明：

取舍位數(shù)由參數(shù)num_digits決定。

ROUNDUP函數(shù)和ROUND函數(shù)的功能基本相同，兩者區(qū)別的是：ROUNDUP函數(shù)在進(jìn)行取舍時總是向上取舍，即進(jìn)行遠(yuǎn)離0的取舍。

當(dāng)取舍位數(shù)為0時，取舍到整數(shù)；取舍位數(shù)為負(fù)值時，即表示小數(shù)點的左側(cè)應(yīng)有多少個零?！纠縍OUND(18.435,2) ROUNDUP(18.435,2) 18.44 18.44ROUND(18.435,1) ROUNDUP(18.435,1) 18.4 18.5ROUND(-18.435,1) ROUNDUP(-18.435,1) -18.4 -18.5ROUND(18.435,0) ROUNDUP(18.435,0) 18 19ROUND(18.435,-1) ROUNDUP(18.435,-1) 20 20ROUND(18.435,-2) ROUNDUP(18.435,-2) 0 100▲ROUNDDOWN函數(shù)語法：ROUNDDOWN(number,num_digits)功能：對number進(jìn)行靠近零值，向下舍入數(shù)字。說明：

ROUNDDOWN函數(shù)和ROUNDUP函數(shù)的功能差不多，只不過ROUNDUP函數(shù)在進(jìn)行取舍時總是向上取舍即遠(yuǎn)離0的取舍；而ROUNDDOWN函數(shù)在進(jìn)行取舍時總是向下取舍即進(jìn)行靠近0的取舍?！纠縍OUNDDOWN(18.435,2) 18.43ROUNDDOWN(18.435,1) 18.4ROUNDDOWN(-18.435,2) -18.43ROUNDDOWN(18.435,0) 18ROUNDDOWN(18.435,-1) 10ROUNDDOWN(18.435,-2) 0模擬運(yùn)算表

Excel攜帶了一個模擬運(yùn)算表工具，該工具用于計算一系列自變量給定值對應(yīng)的函數(shù)值。模擬運(yùn)算表計算函數(shù)值是自動進(jìn)行的，如果某個自變量的給定值被修改了，模擬運(yùn)算表不需要人工干預(yù)就可以自動計算出新的函數(shù)值。模擬運(yùn)算表包括：

一維模擬運(yùn)算表計算一個自變量所有給定值對應(yīng)的函數(shù)值。

二維模擬運(yùn)算表計算兩個自變量所有給定值對應(yīng)的函數(shù)值。

一維模擬運(yùn)算表【例】某企業(yè)采購某零件，全年需求量為15000件，每次訂貨成本為500元，單件零件的年儲存成本為30元，試?yán)肊xcel的模擬運(yùn)算表功能，在工作表中生成一個運(yùn)算表，計算當(dāng)訂貨量從200～1500變化時該零件的年訂貨成本、年儲存成本、年訂儲成本。【一維模擬運(yùn)算】操作時的注意事項：①一維模擬運(yùn)算時，代表函數(shù)關(guān)系的公式必須位于比所有自變量給定值高一行、右邊一列處。②正確判斷自變量的一系列給定值的位置是在同一行還是同一列。③正確選擇函數(shù)關(guān)系中自變量所引用的行和列的所有單元格。

二維模擬運(yùn)算表【例】某商店A商品的年銷量為8000，一次訂貨的訂貨成本為30元，單位年儲存成本為3.8元。試?yán)媚M運(yùn)算表工具計算年需求量從5000按步長1000變化到10000，年訂貨量從200按步長50變化到500的年訂儲成本【二維模擬運(yùn)算】

二維模擬運(yùn)算時，代表函數(shù)關(guān)系的公式位于行、列自變量給定值的交叉處，并區(qū)分引用和和引用列的單元格。▲AND函數(shù)語法：AND(logical1,logical2,……)功能：檢查是否所有參數(shù)值均為TRUE，如果所有參數(shù)值均為TRUE，則返回TRUE。類似的函數(shù)有：OR()、NOT()?！鳱PV函數(shù)語法：NPV(rate,value1,value2,……)功能：基于一系列現(xiàn)金流和固定的各期貼現(xiàn)率，返回一項投資的現(xiàn)值和總和。說明：

rate

各期的貼現(xiàn)率，是一個固定值。

value1,value2,……

連續(xù)各期的現(xiàn)金流量值。P155▲IRR函數(shù)語法：IRR(values,guess)功能：返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。說明：

values

用來計算內(nèi)部收益率的數(shù)組或單元格范圍的引用。values包含至少一個正值和一個負(fù)值。

guess

對函數(shù)IRR()計算的估計值。P157

Excel使用迭代法計算IRR()函數(shù)。從guess開始，函數(shù)不斷修正收益率，直至結(jié)果精度達(dá)到0.00001%。如果經(jīng)過20次迭代，仍未找到結(jié)果，則返回錯誤值#NUM!。

在大多數(shù)情況下，并不需要為IRR()函數(shù)提供guess值，如果省略gu