第七章Excel在營運資金

管理中的應用

計算機財務管理第一頁，共六十九頁。1第一節(jié)最佳現(xiàn)金持有量決策模型一、成本分析模型

成本分析模型是通過分析持有現(xiàn)金的成本，將持有現(xiàn)金的總成本最低的現(xiàn)金持有量作為最佳現(xiàn)金持有量。持有現(xiàn)金的成本主要有機會成本、管理成本和短缺成本。通過計算比較各個現(xiàn)金持有量方案的總成本，選取總成本最低的方案為最優(yōu)方案。例7－1：某企業(yè)有四種現(xiàn)金持有方案，有關成本資料如表7—1所示。第二頁，共六十九頁。2確定現(xiàn)金最佳持有量。

創(chuàng)建過程：

創(chuàng)建步驟見模型批注，如圖7-1所示。第三頁，共六十九頁。3圖7-1第四頁，共六十九頁。4二、存貨模式確定最佳現(xiàn)金持有量的存貨模式也稱鮑莫模式，是1952年由美國經(jīng)濟學家威廉·鮑莫(Willian?J?Baumol)首先提出的。根據(jù)這種模式，企業(yè)的現(xiàn)金持有量非常類似于存貨，因此存貨的經(jīng)濟訂貨批量模型可以用來確定最佳現(xiàn)金持有量。計算公式為:

式中:b為現(xiàn)金與有價證券的轉(zhuǎn)換成本，T為一定時期的現(xiàn)金需求總量，N為最佳現(xiàn)金持有量，i為有價證券的利息率第五頁，共六十九頁。5例7-2，某企業(yè)的現(xiàn)金流量穩(wěn)定，預計全年現(xiàn)金需求總量200000元，現(xiàn)金與有價證券的轉(zhuǎn)換成本為每次400元，有價證券的利率為10%。采用存貨模式確定該企業(yè)最佳現(xiàn)金持有量。

計算過程:如圖7-2所示。圖7-2第六頁，共六十九頁。6第二節(jié)應收帳款信用政策決策模型應收賬款政策，是指企業(yè)向客戶提供商業(yè)信用時，為了對應收賬款進行規(guī)劃和控制所確定的基本原則和規(guī)范。信用政策是企業(yè)財務政策的一個重要組成部分。它由信用標準、信用條件和收賬政策三要素組成。一、信用標準決策模型信用標準是指企業(yè)同意向客戶提供商業(yè)信用時，客戶必須具備的最低財務能力。第七頁，共六十九頁。7企業(yè)的信用標準比較嚴格，只對信譽較好的客戶提供商業(yè)信用，則可以減少壞賬損失，降低應收賬款的機會成本和管理成本，但是也會減少企業(yè)的銷售量；反之，如果企業(yè)的信用標準比較寬松，雖然會增加銷售量，但是也會相應增加壞賬損失和應收賬款的機會成本與管理成本。因此，企業(yè)必須在擴大銷售與增加成本之間權衡利弊，制定一個比較合理的信用標準。第八頁，共六十九頁。8信用標準確定相關計算公式：第九頁，共六十九頁。9例7-3，某公司2001年的經(jīng)營情況和信用標準如表7—3所示。該公司為了擴大銷售，增加利潤總額，采取了放寬信用標準的策略，提出了A、B兩個方案，其信用標準及對企業(yè)的影響如表7-4。第十頁，共六十九頁。10第十一頁，共六十九頁。11表7-4分析過程:第十二頁，共六十九頁。12第一步，建立如圖7-3-1、2所示的分析模型結(jié)構(gòu)。圖7-3圖7-3-1第十三頁，共六十九頁。13第二步，在單元格C30中輸入公式”=B19*$C$8/100”；圖7-3-2第十四頁，共六十九頁。14第三步，在單元格C31中輸入公式”=(B19/360)*B21*$C$13/100”；第四步，在單元格C32中輸入公式”=B19*B23”；第五步，在單元格C33中輸入公式”=B25”；第六步，在單元格C34中輸入公式”=C30-C31-C32-C33”，得到A方案的計算機結(jié)果。第七步，選取單元格區(qū)域C30:C34，將其復制到單元格區(qū)域D30:D34中，得到方案B的有關計算結(jié)果。第八步，在單元格C35中輸入公式”=IF(AND(C34>0,D34>0),IF(C34>D34,"采用方案A","采用方案B"),IF(D34>0,"采用方案B","仍采用目前的信用標準"))”第十五頁，共六十九頁。15二、信用條件決策模型信用條件是指企業(yè)接受客戶信用訂單時所提出的付款要求，主要包括信用期限、折扣期限和現(xiàn)金折扣。采取現(xiàn)金折扣方式在有利于刺激銷售、降低相關成本或損失的同時，也需付出一定的成本代價，即現(xiàn)金折扣造成的損失。如果加速收款增加的收益能夠補償現(xiàn)金折扣成本，企業(yè)就可以采取現(xiàn)金折扣或進一步改變當前的折扣方針；反之，現(xiàn)金折扣優(yōu)惠便是不合理的。主要計算公式如下：

第十六頁，共六十九頁。16第十七頁，共六十九頁。17信用條件變化帶來的增量利潤為第一項與后三項之差第十八頁，共六十九頁。18例7－4，某企業(yè)擬改變信用條件，現(xiàn)有兩個可供選擇的信用條件方案，有關資料表7－5所示。企業(yè)應選擇哪個方案。表7-5第十九頁，共六十九頁。19

分析過程:第一步，建立如圖7-4所示的分析模型結(jié)構(gòu)。續(xù)表第二十頁，共六十九頁。20圖7-4第二十一頁，共六十九頁。21第二步，在單元格C26中輸入公式”=C18*$C$8”；第三步，在單元格C27中輸入公式”=((C22-$C$12)/360*$C$5+C22/360*C18)*$C$6*$C$13”第四步，在單元格C28中輸入公式”=(C5+C18)*C20*C21”；第五步，在單元格C29中輸入公式”=C18*C19”；第六步，在單元格C30中輸入公式”=C26-C27-C28-C29”，得到A方案的計算機結(jié)果。第二十二頁，共六十九頁。22第七步，選取單元格區(qū)域C26:C30，將其復制到單元格區(qū)域D26:D30中，得到方案B的有關計算結(jié)果。第八步，在單元格C31中輸入公式”=IF(AND(C30>0,D30>0),IF(C30>D30,"應采用方案A","應采用方案B"),IF(C30>0,"應采用方案A",IF(D30>0,"應選擇方案B","仍采用目前的信用條件")))”第二十三頁，共六十九頁。23

三、應收帳款信用政策的綜合決策模型信用政策中的每一項內(nèi)容的變化都會影響到企業(yè)的利益，因此，需要這些因素綜合起來來濾，以制定合適的信用政策。主要計算公式：1、利潤政策變化對利潤的影響第二十四頁，共六十九頁。242、信用政策變化對應收帳款機會成本的影響第二十五頁，共六十九頁。253、信用政策變化對現(xiàn)金折扣成本的影響4、信用政策變化對壞帳損失的影響第二十六頁，共六十九頁。26

5、信用政策變化對收帳管理成本的影響

例7－5，某企業(yè)現(xiàn)有的信用政策以及要改變信用政策的兩個可供選擇的方案所表7－6所示。決策。第二十七頁，共六十九頁。27表7－6

分析過程：第一步：建立如圖7-5所示的分析模型結(jié)構(gòu)。第二十八頁，共六十九頁。28圖7-5第二十九頁，共六十九頁。29第二步，在單元格D17中輸入公式”=(D5-$C$5)*D6”；第三步，在單元格D18中輸入公式”=((D9-$C$9)/360*$C$5+D9/360*(D5-$C$5))*D13*D12”第四步，在單元格D19中輸入公式”=D5*D8-$C$5*$C$8”；第五步，在單元格D20中輸入公式”=D5*D11*D10-$C$5*$C$10*$C$11”；第六步，在單元格D21中輸入公式”=D5*D7-$C$5*$C$7”;第三十頁，共六十九頁。30第七步，在單元格D22中輸入公式”=D17-D18-D19-D20-D21”，計算A方案的增量利潤。第八步，選取單元格區(qū)域D17:D22，將其復制到單元格區(qū)域E17:E22中，得到方案B的有關計算結(jié)果。第九步，在單元格C31中輸入公式”=IF(AND(D22>0,E22>0),IF(D22>E22,“采用方案A”,“采用方案B”),IF(D22>0,“采用方案A”,IF(E22>0,“采用方案B”,“采用目前信用政策”)))，產(chǎn)生結(jié)論。設計過程第三十一頁，共六十九頁。31第三節(jié)存貨的經(jīng)濟訂貨批量決策模型

存貨決策涉及多方面的內(nèi)容，包括決定進貨項目、選擇進貨單位、決定進貨時間和決定進貨批量等，其中最常見的存貨決策是確定經(jīng)濟訂貨批量。企業(yè)購買和儲存存貨的有關成本包括：

⑴訂貨成本指取得訂單的成本，如辦公費、差旅費、郵資、電報電話費等支出。⑵購置成本指存貨本身的價值和運雜費。⑶儲存成本是指為保持存貨而發(fā)生的成本。⑷缺貨成本是指由于存貨供應中斷而造成的損失。第三十二頁，共六十九頁。32一、基本的經(jīng)濟訂貨批量模型㈠經(jīng)濟訂貨量基本模型第三十三頁，共六十九頁。33

例7-6，某企業(yè)全年需要材料3600千克，一次訂貨費用25元，材料單價20元/千克，單位材料的儲存費用為2元，求該材料的經(jīng)濟訂貨批量、全年的訂貨次數(shù)和與儲存存貨相關的總成本。設計過程：第一步，建立如圖7-6所示分析模型結(jié)構(gòu)。第二步，在單元格C8中輸入公式”=SQRT(2*C3*C2/C5)計算經(jīng)濟批量；第三步，在單元格C9中輸入公式”=C2/C8”計算一年采購的次數(shù)；第三十四頁，共六十九頁。34圖7-6第三十五頁，共六十九頁。35

第四步，在單元格C10中輸入公式”=SQRT(2*C2*C3*C5)”計算與經(jīng)濟批量相關的總成本。設計過程㈡存貨陸續(xù)供應和耗用情況下的經(jīng)濟訂貨批量模型第三十六頁，共六十九頁。36

例7-7,如例7－6中的企業(yè)所需要的材料不是瞬時到貨,而是陸續(xù)供貨,進貨期內(nèi)每日供貨量30千克,每日耗用量為10千克。計算經(jīng)濟訂貨批量、全年的訂貨次數(shù)和與儲存存貨相關的總成本。

設計過程：第一步，建立如圖7-7所示分析模型結(jié)構(gòu)。第三十七頁，共六十九頁。37圖7-7第三十八頁，共六十九頁。38第二步，在單元格E8中輸入公式”

=SQRT(2*C3*C2/C5)，計算經(jīng)濟批量；第三步，在單元格E9中輸入公式”=C2/E8”，計算一年采購的次數(shù)；第四步，在單元格E10中輸入公式”=SQRT(2*C2*C3*C5*(1-E3/E2))”,計算與經(jīng)濟批量相關的總成本。

設計過程第三十九頁，共六十九頁。39

㈢有數(shù)量折扣的經(jīng)濟訂貨批量決策模型

例7－8,某企業(yè)每年需要某零件8500件.每次訂貨費用260元,存儲費率為零件單價的10％。供貨商規(guī)定，凡一次購買1000件以下的價格為10元/件，1000件或以上但2000件以下的價格為9元／件，2000件或以上但3000件以下的價格為8元／件，3000件或以上的價格7元。問企業(yè)應如何訂貨。

設計過程：

第一步，建立如圖7-8所示的分析模型結(jié)構(gòu)。第四十頁，共六十九頁。40圖7-8第四十一頁，共六十九頁。41

第二步，在單元格D9、E9、F9、G9、H9、I9中分別輸入以下公式：

單元格D9：“=SQRT(2*$C$4*$C$5/(C9*$C$6))”，計算單價為10元是的經(jīng)濟批量，即采購批量(G)在1<=G<1000時對應的經(jīng)濟批量；單元格E9：“=IF(AND(D9>=B9,D9<B10),”有效“,”無效“)”，判斷計算的經(jīng)濟批量是否落在有效期間內(nèi)，即1<=G<1000；單元格F9：“=IF(E9="有效",C9*$C$4+$C$4*$C$5/D9+$C$6*C9*D9/2,"無效")”第四十二頁，共六十九頁。42單元格G9：“=C9*$C$4+$C$4*$C$5/B9+$C$6*C9*B9/2”，計算按折扣起點批量計算的費用。單元格H9:“=IF(E9=”有效“,MIN(F9:G9),G9”,取最小費用。單元格I9：“=IF(H9=G9,B9,D9)”，判斷最小費用對應的經(jīng)濟訂貨批量或折扣起點批量第二步，將單元格D9:I9復制到D10:I12中，分別得到不同折扣點對應的計算指標。

第三步，將單元格E12中的公式改為：“=IF(D12>=B12,"有效","無效")”第四十三頁，共六十九頁。43

第四步，在單元格C14種輸入公式“=INDEX(I9:I12,MATCH(MIN(H9:H12),H9:H12,0))”，得出無需求量限制的經(jīng)濟訂貨批量。

第五步，在單元格C15種輸入公式“=IF(C4>C14,C14,C4)”，確定最有經(jīng)濟批量。

設計過程第四十四頁，共六十九頁。44第二節(jié)最優(yōu)決策分析工具

——“規(guī)劃求解”工具

一、規(guī)劃求解工具的使用謂規(guī)劃就是指具有多種實用價值的有限資源，當其存在多種利用方案時，如何根據(jù)一定的經(jīng)濟目的，最有效獲地謀劃或選擇最優(yōu)方案。規(guī)劃求解模型就是將這種經(jīng)濟關系，歸納為一定的數(shù)學表達式。然后，求解出問題的最優(yōu)解。規(guī)劃求解主要用來解決①極大值問題(MAX)；②極小值問題(MIN)和定值問題。規(guī)劃求解模型是由決策變量、目標函數(shù)和約束條件三部分組成。根據(jù)數(shù)據(jù)表達式的性質(zhì)可分為線性規(guī)劃和非線性規(guī)劃兩種。第四十五頁，共六十九頁。45決策變量：是需要經(jīng)過模型求解計算來確定的決策因素。一個問題一般有一組決策變量，這些決策變量的一組確切值代表一個具體的規(guī)劃方案。目標函數(shù)：是反映經(jīng)濟目標的數(shù)學表達式。一般只能是具有同一度量單位的第一目標如利潤、產(chǎn)值最大；或成本最低等。約束條件：是實現(xiàn)經(jīng)濟目標的制約因素。如資金、勞力、資源等。包括客觀約束條件和主管約束條件。

第四十六頁，共六十九頁。46二、規(guī)劃求解的設置過程

使用規(guī)劃求解工具只需從［工具］菜單中選擇標出規(guī)劃求解對話框，見圖7-9。只有通過“自定義安裝”方式將Excel完全安裝，［工具］菜單才有［規(guī)劃求解］命令。圖9第四十七頁，共六十九頁。47㈠目標單元格目標單元格也稱目標函數(shù)是模型中的一個你希望最大值、最小值的單元格，目標單元格應含有決策變量(可變單元格)，即引用含有決策變量的單元格或名稱(和目標函數(shù)對應)㈡可變單元格可變單元格也稱決策變量是對目標單元格數(shù)值產(chǎn)生影響的單元格?？勺儐卧褡疃?00個。(和決策變量對應)［可變單元格］框中的輸入項通常是某個單元格區(qū)域的引用位置，或使用逗號隔開的幾個非相鄰單元格的引用第四十八頁，共六十九頁。48㈢約束條件約束條件是必須符合某些限制或目標值的單元格數(shù)值。約束條件可以使用在目標單元格和可變單元格上。1.約束條件中可以指定模型中任意單元格的上下界2.[單元格引用位置]框中所引用到的單元格，通常含有于一個或多個可變單元格直接或間接相關的公式。3.使用“int”運算符時，表示有約束條件約束約束的數(shù)值只能是整數(shù)。只有可變單元格才可以限制為整數(shù)。第四十九頁，共六十九頁。49例7-9：某企業(yè)在某月份生產(chǎn)甲、乙兩種產(chǎn)品，其有關資料如表7-7所示，則企業(yè)應如何安排兩種產(chǎn)品的產(chǎn)銷組合，使企業(yè)獲得最大銷售利潤？表7-7第五十頁，共六十九頁。50

利用規(guī)劃求解工具解決該問題的步驟為：第一步，建立優(yōu)化模型，決策變量：設x和y分別表示甲產(chǎn)品和乙產(chǎn)品的生產(chǎn)量。目標函數(shù)：MAX(銷售利潤)=(140-60)×x+(180-100)×y

約束條件：6x+9y≤3607x+4y≤240

18x+15y≤850y≤30

x≥0,y≥0,且為整數(shù)第五十一頁，共六十九頁。51

第二步，單元格C12和D12為可變單元格，分別存放甲、乙產(chǎn)品的生產(chǎn)量。第三步，單元格C13為目標單元格(銷售利潤)，計算公式為“=SUMPRODUCT(C5:D5-C6:D6,C12:D12)”。第四步，在單元格C14中輸入產(chǎn)品消耗工時合計計算公式“=SUMPRODUCT(C7:D7,C12:D12)”。在單元格C15中輸入產(chǎn)品消耗材料合計計算公式“=SUMPRODUCT(C8:D8,C12:D12)”。在單元格C16中輸入產(chǎn)品消耗能源合計計算公式“=SUMPRODUCT(C9:D9,C12:D12)”。

第五十二頁，共六十九頁。52第五步，單擊[工具]菜單，選擇規(guī)劃求解項，則系統(tǒng)彈出[規(guī)劃求解參數(shù)]對話框，如圖7-10所示。圖7-10第五十三頁，共六十九頁。53

第六步，在[規(guī)劃求解參數(shù)]對話框中，[設置目標單元格]中輸入“$C$13”；[等于]選“最大值”；[可變單元格]中輸入“$C$12:$D$12”；在[約束]中添加以下約束條件：“$C$12:$D$12”、“$C$12:$D$12>=0”、“$C$14<=$F$4”、“$C$15<=$F$5”、“$C$16<=$F$6”、“$D$12<=$D$10”。

添加約束條件的方法是：單擊[添加]澳紐，系統(tǒng)會彈出[添加約束]對話框，如圖7-11所示。圖11第五十四頁，共六十九頁。54第七步，如有需要還可以設置有關的項目，即單擊[選項]按鈕，彈出[規(guī)劃求解選項]對話框，如圖7-12所示。圖7-12第五十五頁，共六十九頁。55有關［選項］設置說明：

⑴［選項］允許你控制求解過程的高級特性并加載或保存工作表上一個特殊問題的選擇；可以為線性問題和非線性問題之間的差異定義參數(shù)。

⑵［最長運算時間］限制求解過程花費的時間，其值必須是正整數(shù)，默認值為100秒；

⑶［精度］控制規(guī)劃求解找到答案的精度，在［精度］框中如入的數(shù)：

①用來決定約束值是否滿足目標或滿足你所指定的上下限；第五十六頁，共六十九頁。56

②必須是一個在0和1（不包含0和1）之間的小數(shù)

③默認值為0.000001

⑷［允許誤差］表示當一個正數(shù)約束條件被用于問題的任何代以元素時，規(guī)劃求解所允許的誤差百分比。當沒有正數(shù)約束條件時，［允許誤差］設置無效；⑸［收斂度］在此輸入收斂度數(shù)值，當最近5此迭代后，目標單元格中數(shù)值的變化小于“收斂度”編輯框中設置的數(shù)值時，“規(guī)劃求解”停止運行。收斂度只適應于非線性規(guī)劃問題，用小數(shù)表示；

⑹［采用線性模型］當模型中的所有關系為線性時，可以選擇該選項第五十七頁，共六十九頁。57

⑺［顯示迭代結(jié)果］選擇此項后，在每次迭代后中斷規(guī)劃求解，顯示迭代結(jié)果

⑻［自動按比例縮放］當輸入（可變單元格）、輸出（目標單元格）和限制在數(shù)量上有較大差別時，選擇此選擇框；

⑼［假定非負］對于在“添加約束”對話框的“約束值”編輯框中沒有設置下限的可變單元格，假定其下限為0；

⑽［估值］該選項自動在每次一維搜索中用來獲得基本數(shù)量的初始估計值的方法。正切函數(shù)：一維線性外推；二次方程：使用二維外推，適用于過度非線性問題第五十八頁，共六十九頁。58

⑾［導數(shù)／偏導數(shù)］對目標函數(shù)和約束條件起作用。前向差分：為默認值中心差分：需要更多的計算工作表，使用它可提高規(guī)劃求解結(jié)果的質(zhì)量

⑿［搜索］決定在每次迭代時使用什么搜索算法來確定搜索方向牛頓法：搜粟需要更多的內(nèi)存，迭代次數(shù)少共軛法：搜所需要的內(nèi)存少，通常需要更多的迭代次數(shù)第五十九頁，共六十九頁。59［估值］、［導數(shù)／偏導數(shù)］、［搜索］等選項中的數(shù)學方法、適用于大部分問題。⒀［全部重新設置］清除當全規(guī)劃求解問題設置，并重新全部設置全部為默認項

⒁開始規(guī)劃求解，直到達到目標值為止第八步，處理規(guī)劃求解所得到的結(jié)果當求解問題的過程結(jié)束時，出現(xiàn)如下對話框：圖7-13。圖7-13第六十頁，共六十九頁。60

敏感性分析報告包括的信息，展示了解決方案對問題所使用公式的變化的敏感程度。運算結(jié)果報告：⑴運算結(jié)果的初值（原值）和終值（求解）；⑵可變單元格的初值和終值；⑶約束條件及相關信息。極限結(jié)果報告：⑴下限極限；⑵上限極限；⑶目標式結(jié)果。當可變單元格位于上下界時，目標單元格的值。第九步，單擊[確定]按鈕，生成結(jié)果見圖7-14。第六十一頁，共六十九頁。61圖7-14第六十二頁，共六十九頁。62

三、應用“規(guī)劃求解”工具分析最優(yōu)訂貨批量例7－10，應用“規(guī)劃求解”工具分析最優(yōu)訂貨批量模型。原始資料見表7-7。甲、乙、丙、丁材料的折扣起點分別為400、450、500、500。表7-7第六十三頁，共六十九頁。63求解過程：第一步，建立如圖7-14所示的分析模型結(jié)構(gòu)。圖7-14第六十四頁，共六十九頁。64第二步，單元個區(qū)域C15、D15、E15、F15分