《Excel在財務管理中的應用》課件 05EXCEL表在營運資金管理中的應用

EXCEL表在營運資金管理中的應用目錄Contents應收賬款管理01最佳現(xiàn)金持有量02

經(jīng)濟訂貨量0301應收賬款管理一、應收賬款信用標準決策模型

信用標準是指企業(yè)建立的允許客戶享受企業(yè)所提供信用的最低標準。信用標準通?？捎每蛻舻念A計壞賬損失率來表示。嚴格的信用標準--不利于企業(yè)擴大銷售、增加利潤。寬松的信用標準--有助于企業(yè)擴大銷售、增加利潤,但是增加應收賬款的相關成本。（一）應收賬款信用標準決策的基本原理應收賬款的相關成本主要包括機會成本、壞賬成本、管理成本和現(xiàn)金折扣成本，管理成本屬于固定成本，一般情況下不考慮。對利潤的影響=銷售額×銷售利潤率壞賬損失=銷售額×壞賬損失率凈收益=增加的利潤-機會成本-壞賬損失（二）與應收賬款成本有關的計算公式：機會成本=銷售額/360×平均收款期×變動成本率×機會成本率（三）應收賬款信用標準決策模型的建立【例1】某公司目前采用的信用標準以及備選的信用標準方案的有關資料如圖的【已知條件】區(qū)域所示。要求建立一個對該公司選擇信用標準方案做出決策的模型。原來的信用標準的有關數(shù)據(jù)采用備選信用標準方案的有關數(shù)據(jù)預計壞賬損失率標準8%備選方案甲方案乙方案信用期限（天）30預計壞賬損失率標準5%10%年賒銷收入（萬元）3500年賒銷額增加（萬元）-150300銷售利潤率20%增加銷售額的平均收款期（天）4045平均實際發(fā)生的壞賬損失率9%增加銷售額的平均壞賬損失率6%11%平均收款期（天）40增加銷售額引起的管理費增加（萬元）-23應收賬款的機會成本8%變動成本率55%一年計算天數(shù)360計算與決策結果（萬元）信用標準變化的影響甲方案乙方案對銷售利潤的影響-3060對機會成本的影響-0.731.65對壞賬成本的影響-933對管理費用的影響-23對凈收益的綜合影響-18.2722.35決策結論：乙方案二、應收賬款信用條件決策模型

信用條件是指企業(yè)同意客戶支付賒銷款項的具體條件,包括信用期限、折扣期限和現(xiàn)金折扣比率。例如,(2/30,N/60)這一信用條件的含義是:30天內(nèi)付款的客戶可以享受2%的現(xiàn)金折扣,全部款項應在60天內(nèi)付清,其中60為信用期限,30為折扣期限,2為現(xiàn)金折扣的百分率。（一）應收賬款信用條件決策的基本原理對利潤的影響=新銷售額×銷售利潤率壞賬損失=新銷售額×壞賬損失率凈收益=增加的利潤-機會成本-壞賬損失-現(xiàn)金折扣成本（二）與應收賬款成本有關的計算公式：現(xiàn)金折扣=（原銷售額+新銷售額）×現(xiàn)金折扣銷售百分比×現(xiàn)金折扣率機會成本=[原銷售額/360×（新收賬期-原收賬期）+新銷售額/360×新收賬期]×變動成本率×機會成本率（三）應收賬款信用條件決策模型的建立【例2】某公司2個備選的信用條件方案的有關資料如圖的【已知條件】區(qū)域所示；要求建立一個對該公司選擇信用條件方案做出決策的模型。基本項目數(shù)據(jù)項目數(shù)據(jù)銷售額(元)150000變動成本率60%利潤(元)30000銷售利潤率25%信用標準(預期壞帳損失率限制)10%平均壞帳損失率6%信用條件30天付清平均收款期(天)45應收帳款的機會成本率15%信用條件變化方案新項目數(shù)據(jù)項目方案A方案B信用條件45天內(nèi)付清,無現(xiàn)金折扣"2/10,n/30"因信用條件變化增加或減少的銷售額(元)2000030000增加銷售額的平均壞帳損失率11%10%需付現(xiàn)金折扣的銷售額占總銷售額的百分比0%50%現(xiàn)金折扣率0%2%平均收款期(天)6020計算與決策結果項目方案A方案B信用條件變化對利潤的影響(元)50007500信用條件變化對應收帳款機會成本的影響(元)862.5-787.5信用條件變化對現(xiàn)金折扣成本的影響(元)01800信用條件變化對壞帳損失的影響(元)22003000信用條件變化帶來的增量利潤(元)1937.53487.5三、應收賬款收賬政策決策模型企業(yè)收回應收的賬款往往要花費一定的費用；收賬越努力,花費的收賬費用越多,收回賬款的可能性越大；但是收賬費用花費到一定的程度,繼續(xù)增加收賬費用可能會得不償失。（一）應收賬款收賬政策決策的基本原理需要對花費多少收賬費用去收回賬款做出決策。機會成本=銷售額/360×平均收款期×變動成本率×機會成本率壞賬損失=銷售額×壞賬損失率總成本=機會成本+壞賬損失+管理成本（二）與應收賬款收賬政策有關的計算公式：

【例3】某公司目前的收賬政策和備選的收賬政策的有關資料如圖的【已知條件】區(qū)域所示。要求建立一個對該公司選擇收賬政策方案做出決策的模型。建立模型的具體步驟如下所述。收賬政策方案目前方案備選方案年收賬費用（萬元）1020平均收款天數(shù)6030壞賬損失率3%2%年賒銷額（萬元）420480變動成本率60%60%資金成本率15%15%一年計算天數(shù)360360計算與決策結果（萬元）收賬政策方案目前方案備選方案應收賬款機會成本6.303.60壞賬損失12.609.60年收賬費用10.0020.00總成本28.9033.20決策結論目前方案應收賬款管理模型應收賬款信用標準決策模型應收賬款信用條件決策模型應收賬款收賬政策決策模型課堂小結對利潤的影響機會成本壞賬成本管理費用對利潤的影響機會成本（與原銷售額合并計算）現(xiàn)金折扣成本（與原銷售額合并計算）壞賬成本收賬費用機會成本壞賬成本收賬費用計算公式對利潤的影響=銷售額×銷售利潤率機會成本=銷售額/360×平均收款期×變動成本率×機會成本率機會成本=[原銷售額/360×（新收賬期-原收賬期）+新銷量/360×新收賬期]×

變動成本率×機會成本率現(xiàn)金折扣=（原銷售額+新銷售額）×現(xiàn)金折扣銷售百分比×現(xiàn)金折扣率壞賬損失=銷售額×壞賬損失率課上練習1實訓資料1：某公司兩個備選的信用條件方案的有關資料如圖的【已知條件】區(qū)域所示。要求建立一個對該公司選擇信用條件方案做出決策的模型?，F(xiàn)有A和B兩個方案，有關數(shù)據(jù)如表所示。實訓要求：通過計算分析回答，應選擇哪個方案？已知條件基本數(shù)據(jù)備選方案方案A方案B銷售單價（元/件）120信用期限（天）3045單位變動成本（元/件）72預計銷售量（件）6000070000應收賬款的機會成本12%預計壞賬損失率2%3%一年計算天數(shù)360預計收賬費用30005000計算與決策結果（元）備選方案方案A方案B銷售利潤28800003360000機會成本43,200.0075,600.00壞賬損失144000252000收賬費用30005000凈收益2,689,800.003,027,400.00決策結論：方案B課上練習2某公司3個備選的信用條件方案的有關資料如圖的【已知條件】區(qū)域所示；方案A信用條件(3/10,N/30)，方案B信用條件(2/30,N/60)，方案C信用條件(1/60,N/90)。要求建立一個對該公司選擇信用條件方案做出決策的模型。建立模型的具體步驟如下所述。已知條件備選方案方案A方案B方案C信用期限（天）306090第1擋折扣期限（天）103030第1擋現(xiàn)金折扣率3%2%2%取得第1擋現(xiàn)金折扣銷售比例80%70%45%第2擋折扣期限（天）0060第2擋現(xiàn)金折扣率001%取得第2擋現(xiàn)金折扣銷售比例0035%年賒銷額（萬元）100012001500應收賬款平均收款天數(shù)406892壞賬損失率2%3%5%年收賬費用（萬元）80100150銷售利潤率

變動成本率65%機會成本率20%一年計算天數(shù)360計算與決策結果（萬元）備選方案方案A方案B方案C銷售利潤200240300現(xiàn)金折扣成本24.0016.8018.75應收賬款機會成本14.4429.4749.83壞賬損失203675收賬費用80.00100.00150.00凈收益61.5657.736.42最優(yōu)方案方案A課上練習3

實訓資料2：某公司是一家商業(yè)企業(yè)，由于目前的信用政策過于嚴厲，不利于擴大銷售，且收帳費用較高，故需要進行信用政策調(diào)整。調(diào)整前的銷售額為240000元，變動成本率為80%，應收帳款的機會成本率為15%，利潤以及銷售利潤率為12000元和24%，壞帳損失率為10%?，F(xiàn)有甲和乙兩個放寬信用政策方案，數(shù)據(jù)如表所示。原來的信用標準的有關數(shù)據(jù)采用備選信用標準方案的有關數(shù)據(jù)預計壞賬損失率標準10%備選方案甲方案乙方案銷售額（元）240000信用條件n/902/60，n/180變動成本率80%年賒銷額增加（元）3080026700銷售利潤率24%現(xiàn)金折扣占銷售額的比例2%3%應收賬款的機會成本15%增加銷售額的平均收款期（天）9060平均收賬期（天）50增加銷售額的平均壞賬損失率2.5%3%一年計算期天數(shù)360現(xiàn)金折扣率02%計算與決策結果（元）信用標準變化的影響甲方案乙方案對銷售利潤的影響7,392.006,408.00現(xiàn)金折扣成本0.00160.02對機會成本的影響4,124.001,334.00對壞賬成本的影響770.00801.00對凈收益的綜合影響2,498.004,112.98決策結論：乙方案02

最佳現(xiàn)金持有量五、最佳現(xiàn)金持有量模型設計

現(xiàn)金是企業(yè)流動性最強的資產(chǎn),持有一定量的現(xiàn)金可以降低企業(yè)的償債風險。但現(xiàn)金的營利性很低,持有過多的現(xiàn)金也會使企業(yè)遭受損失。因此,企業(yè)應在對風險和收益權衡考慮的基礎上,采用適當?shù)姆椒ù_定最佳現(xiàn)金持有量。

確定最佳現(xiàn)金余額的方法有多種,下面介紹其中比較常用的兩種方法:

成本分析模式和存貨模式。

成本分析模式是通過分析持有現(xiàn)金的成本，尋找持有成本最低的現(xiàn)金持有量。企業(yè)現(xiàn)金成本=機會成本+管理成本+短缺成本

（一）成本分析模式現(xiàn)金成本機會成本現(xiàn)金持有量過多，機會成本大幅度上升（正比例關系）管理成本一種固定成本，與現(xiàn)金持有量之間無明顯的比例關系短缺成本隨現(xiàn)金持有量的增加而下降，隨現(xiàn)金持有量的減少而上升（反比例關系）【例5】某企業(yè)現(xiàn)有A、B、C、D四種現(xiàn)金持有方案，有關成本資料如表所示。

現(xiàn)金持有方案成本資料項目ABCD平均現(xiàn)金持有量（元）100000200000300000400000機會成本率10%10%10%10%短缺成本（元）4800025000100006000管理成本（元）40000400004000040000

根據(jù)表資料，利用EXCEL工具，運用成本分析法編制該企業(yè)的最佳現(xiàn)金持有量測算表。操作步驟如下。計算與決策結果（元）備選方案A方案B方案C方案D方案現(xiàn)金余額（元）100000200000300000400000短缺成本（元）4800025000100006000管理成本（元）40000400004000040000機會成本（元）10000200003000040000總成本（元）98000850008000086000最優(yōu)方案C方案最佳現(xiàn)金余額（元）300000（二）

鮑曼模型（存貨模式）

鮑曼模型又稱存貨模型，它由美國經(jīng)濟學家威廉·鮑曼提出的。他認為公司現(xiàn)金持有量在許多方面與存貨相似，存貨經(jīng)濟訂貨批量模型可用于確定目標現(xiàn)金持有量,并以此為出發(fā)點，創(chuàng)立了鮑曼模型?，F(xiàn)金成本=轉換成本+機會成本：TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*機會成本式中Q／2——現(xiàn)金平均持有量；

T／Q——為一個周期內(nèi)的現(xiàn)金轉換次數(shù)；F為有價證券固定成本；K為利息率(機會成本)；Q為最佳現(xiàn)金持有量；T為現(xiàn)金總需求量；最佳現(xiàn)金持有量就是貨幣資產(chǎn)余額總成本為最低點的資產(chǎn)余額。現(xiàn)金總成本：T為現(xiàn)金總需求量；F為有價證券固定成本；Q為最佳現(xiàn)金持有量；K為利息率(機會成本)；=√2*T*固定成本/機會成本=√2*T*固定成本*機會成本四、EXCEL表規(guī)劃求解1.“規(guī)劃求解”是一組命令。2.“規(guī)劃求解”改變單元格中的值

計算結果。3.單元格數(shù)值有約束條件

。查看打開EXCEL表-數(shù)據(jù)-規(guī)劃求解（設置規(guī)劃求解選項卡：文件--選項--加載項--管理--轉到-規(guī)劃求解加載項-確定）【例6】某企業(yè)現(xiàn)金收支狀況比較穩(wěn)定，預計全年（按360天）需要現(xiàn)金400萬元，現(xiàn)金與有價證券的轉換成本為每次400元，有價證券的年利率為8%，則企業(yè)的最佳現(xiàn)金持有量為多少？已知條件全年現(xiàn)金需求量（元）4000000有價證券轉換成本（元/次）400有價證券年利率8%最佳現(xiàn)金余額確定方法存貨模式（鮑曼模型）SQRT()求平方根函數(shù)必須為正數(shù)計算與決策結果（元）計算方法利用公式計算規(guī)劃求解結果最佳現(xiàn)金余額200000200000最低持有現(xiàn)金相關總成本1600016000有價證券交易次數(shù)2020有價證券交易間隔天1818最佳現(xiàn)金持有量模型設計1、成本分析模式

現(xiàn)金成本=機會成本+管理成本+短缺成本2、鮑曼模型（存貨模型）現(xiàn)金成本=轉換成本+機會成本：課堂小結TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*機會成本式中Q／2——現(xiàn)金平均持有量；

T／Q——為一個周期內(nèi)的現(xiàn)金轉換次數(shù)；F為有價證券固定成本；K為利息率(機會成本)；Q為最佳現(xiàn)金持有量；T為現(xiàn)金總需求量；=√2*T*固定成本/機會成本=√2*T*固定成本*機會成本實訓資料4：甲、乙兩家公司持有現(xiàn)金余額的有關資料如圖的【已知條件】區(qū)域所示。要求建立一個確定兩家公司最佳現(xiàn)金余額的模型（甲公司使用成本分析法，乙公司使用存貨模式）。甲公司備選方案A方案B方案C方案D方案E方案現(xiàn)金余額（元）20000400006000080000100000短缺成本（元）120008000400020000機會成本12%最佳現(xiàn)金余額確定方法成本分析模式已知條件乙公司全年現(xiàn)金需求量（元）450000有價證券轉換成本（元/次）900有價證券年利率10%最佳現(xiàn)金余額確定方法存貨模式（鮑曼模型）甲公司備選方案A方案B方案C方案D方案E方案現(xiàn)金余額（元）20000400006000080000100000短缺成本（元）120008000400020000機會成本240048007200960012000總成本1440012800112001160012000最優(yōu)方案

C方案

最佳現(xiàn)金余額（元）

60000乙公司計算方法規(guī)劃求解結果利用公式計算最佳現(xiàn)金余額9000090000最低持有現(xiàn)金相關總成本90009000有價證券交易次數(shù)5有價證券交易間隔天7203

經(jīng)濟訂貨量六、訂貨批量決策模型設計

一定量的存貨是企業(yè)正常生產(chǎn)所必須的。存貨在流動資產(chǎn)中占據(jù)很大的比重，一般占30%~40%。存貨資金占用越低，存貨資金的周轉就越快。

經(jīng)濟訂貨量，是指訂購費用和保管費用的合計數(shù)為最低的訂購批量

經(jīng)濟訂貨量的基本模型是一種理想的市場狀況，其基本假設為：（1）企業(yè)能及時補充存貨，即需要訂貨時即可立即取得存貨；（2）能夠集中到貨，而不是陸續(xù)到貨；（3）不允許缺貨，即無缺貨成本；（4）存貨單價不因訂貨批量的變化而變化。（一）經(jīng)濟訂貨批量的集中到貨模型TC=(T/Q)*F+(Q/2)*K=（T/Q）*訂貨成本+（Q/2）*單位儲存成本式中

T／Q——為一個周期內(nèi)的存貨轉換次數(shù)；

Q／2——存貨平均持有量；F為訂貨成本；K為單位儲存成本；Q為經(jīng)濟批量；T為存貨總需求量；經(jīng)濟批量的相關成本=進貨成本+儲存成本，=√2*T*訂貨成本*單位儲存成本=√2*T*訂貨成本/單位儲存成本【例7】某企業(yè)每年耗用甲材料1600噸，該材料的單價為1000元，單位儲存成本為800元，一次訂貨成本為3600元，則經(jīng)濟訂貨量是多少？Q==120噸已知條件某企業(yè)年消耗甲材料（噸）1600材料單價（元）1000單位儲存成本800一次訂貨成本3600計算與決策結果（元）計算方法利用公式計算規(guī)劃求解結果最佳經(jīng)濟訂貨量120120經(jīng)濟批量相關成本9600096000最佳訂貨次數(shù)1313最佳訂貨周期2727經(jīng)濟訂貨量占用資金6000060000如果考慮到存貨不能一次到達，存貨可能陸續(xù)入庫，庫存貨物陸續(xù)增加

（二）經(jīng)濟訂貨批量的陸續(xù)到貨模型計算公式：TC=儲存成本+訂貨成本=T/Q*F+(Q-Q/P*D)/2*KT為總需求量；Q為經(jīng)濟訂貨量；F為訂貨成本；P為每日送貨量；D為每日消耗量；Q/P為每批存貨全部送達所需日數(shù)；Q/P*D為送貨期內(nèi)全部耗用量。通過推倒可以得出：（1）經(jīng)濟訂貨批量Q（2）每年訂貨次數(shù)N=T/Q（3）訂貨周期T=1年/N（4）經(jīng)濟訂貨量占用資金I=Q/2×單價。

如果供應商實行數(shù)量折扣，那么，除了訂貨成本和儲存成本之外，采購成本也成了決策中的相關成本。三種成本的年成本合計最低的方案，才是最優(yōu)方案。計算公式：總成本TC=訂貨成本+儲存成本+采購成本=T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)式中，T為總需求量；Q為經(jīng)濟訂貨量；F為訂貨成本；P為每日送貨量；D為每日消耗量；Q/P為每批存貨全部送達所需日數(shù)；Q/P*D為送貨期內(nèi)全部耗用量；U為采購單價；Di為數(shù)量折扣；F為訂貨成本；K為單位儲存成本。【例8】某企業(yè)有四種貨物要采購，供應商規(guī)定了數(shù)量折扣，其數(shù)量折扣條件為：甲材料>400kg，乙材料>450kg，丙材料>500kg，丁材料>500kg；相關各項目數(shù)據(jù)見圖所示。存貨名稱甲材料乙材料丙材料丁材料材料年需要量T18000200003000025000一次訂貨成本K25252525單位儲存成本C2343每日送貨量P100200300250每日耗用量d20304025數(shù)量折扣di2%2%2%2%單價U10203025規(guī)劃求解存貨名稱甲材料乙材料丙材料丁材料最優(yōu)訂貨批量Q*750626658680采購成本176,400392,000882,000612,500儲存成本6007981,140919訂貨成本

600

7981,140919總成本177,600393,597884,280614,337綜合成本2,069,814最佳訂貨次數(shù)24.031.945.636.7最佳訂貨周期(月)0.50.40.30.3經(jīng)濟訂貨量占用資金3675613796708335鮑曼模型（存貨模型）現(xiàn)金成本=轉換成本+持有機會成本：

經(jīng)濟批量的相關成本=進貨成本+儲存成本，課堂小結現(xiàn)金TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*機會成本批量TC=(T/Q)*F+(Q/2)*K=（T/Q）*訂貨成本+（Q/2）*單位儲存成本F訂貨成本（固定成本）；K單位儲存成本（機會成本）；Q經(jīng)濟批量（現(xiàn)金持有量）；T經(jīng)濟批量總成本（現(xiàn)金總需求量）；最優(yōu)經(jīng)濟訂貨量1.集中到貨課堂小結Q=2.經(jīng)濟訂貨批量的陸續(xù)到貨模型TC=訂貨成本+儲存成本+采購成本=T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)計算公式：

T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)

T/Q*F為訂貨成本；(Q-Q/P*D)/2*K為儲存成本；P為每日送貨量；

Q/P為每批存貨全部送達所需日數(shù)；D為每日消耗量；Q/P*D為送貨期內(nèi)全部耗用量。（1）經(jīng)濟訂貨批量Q（2）每年訂貨次數(shù)N=T/Q（3）訂貨周期T=1年/N（4）經(jīng)濟訂貨量占用資金I=Q/2單價。√2