《Excel在財務(wù)管理中的應(yīng)用》課件全套 （劉洪民） 第5-8章 EXCEL表在營運資金管理中的應(yīng)用- EXCEL表在財務(wù)分析中的應(yīng)用 - 副本

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

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

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

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

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

變動成本率×機會成本率現(xiàn)金折扣=（原銷售額+新銷售額）×現(xiàn)金折扣銷售百分比×現(xiàn)金折扣率壞賬損失=銷售額×壞賬損失率課上練習(xí)1實訓(xùn)資料1：某公司兩個備選的信用條件方案的有關(guān)資料如圖的【已知條件】區(qū)域所示。要求建立一個對該公司選擇信用條件方案做出決策的模型。現(xiàn)有A和B兩個方案，有關(guān)數(shù)據(jù)如表所示。實訓(xùn)要求：通過計算分析回答，應(yīng)選擇哪個方案？已知條件基本數(shù)據(jù)備選方案方案A方案B銷售單價（元/件）120信用期限（天）3045單位變動成本（元/件）72預(yù)計銷售量（件）6000070000應(yīng)收賬款的機會成本12%預(yù)計壞賬損失率2%3%一年計算天數(shù)360預(yù)計收賬費用30005000計算與決策結(jié)果（元）備選方案方案A方案B銷售利潤28800003360000機會成本43,200.0075,600.00壞賬損失144000252000收賬費用30005000凈收益2,689,800.003,027,400.00決策結(jié)論：方案B課上練習(xí)2某公司3個備選的信用條件方案的有關(guān)資料如圖的【已知條件】區(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應(yīng)收賬款平均收款天數(shù)406892壞賬損失率2%3%5%年收賬費用（萬元）80100150銷售利潤率

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

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

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

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

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

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

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

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

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

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

鮑曼模型（存貨模式）

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

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

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

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

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

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

C方案

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

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

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

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

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

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

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

Q／2——存貨平均持有量；F為訂貨成本；K為單位儲存成本；Q為經(jīng)濟批量；T為存貨總需求量；經(jīng)濟批量的相關(guān)成本=進貨成本+儲存成本，=√2*T*訂貨成本*單位儲存成本=√2*T*訂貨成本/單位儲存成本【例7】某企業(yè)每年耗用甲材料1600噸，該材料的單價為1000元，單位儲存成本為800元，一次訂貨成本為3600元，則經(jīng)濟訂貨量是多少？Q==120噸已知條件某企業(yè)年消耗甲材料（噸）1600材料單價（元）1000單位儲存成本800一次訂貨成本3600計算與決策結(jié)果（元）計算方法利用公式計算規(guī)劃求解結(jié)果最佳經(jīng)濟訂貨量120120經(jīng)濟批量相關(guān)成本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×單價。

如果供應(yīng)商實行數(shù)量折扣，那么，除了訂貨成本和儲存成本之外，采購成本也成了決策中的相關(guān)成本。三種成本的年成本合計最低的方案，才是最優(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為單位儲存成本?！纠?】某企業(yè)有四種貨物要采購，供應(yīng)商規(guī)定了數(shù)量折扣，其數(shù)量折扣條件為：甲材料>400kg，乙材料>450kg，丙材料>500kg，丁材料>500kg；相關(guān)各項目數(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)金成本=轉(zhuǎn)換成本+持有機會成本：

經(jīng)濟批量的相關(guān)成本=進貨成本+儲存成本，課堂小結(jié)現(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.集中到貨課堂小結(jié)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單價?！?*T*訂貨成本/單位儲存成本TC=(T/Q)*F+(Q/2)*K=（T/Q）*訂貨成本+（Q/2）*單位儲存成本課上練習(xí)5教師活動：通過教師端分發(fā)優(yōu)秀的作業(yè)，在學(xué)生操作中進行指導(dǎo)實訓(xùn)資料4：某公司每個月需要支付現(xiàn)金約800000元，公司在有價證券上投資的平均收益率為6.5％，每筆證券的交易費用為85元，公司的現(xiàn)金支付固定且連續(xù)。實訓(xùn)要求：試通過設(shè)計模型解答下列問題。(1)該公司最佳現(xiàn)金持有量是多少?(2)轉(zhuǎn)換和持有現(xiàn)金的總成本為多少?(3)每隔多少天進行一次現(xiàn)金轉(zhuǎn)換?學(xué)生活動：依據(jù)教師下發(fā)的上節(jié)課作業(yè)成果，構(gòu)建最佳現(xiàn)金持有模型已知條件月現(xiàn)金需求量（元）800000年現(xiàn)金需求量9600000有價證券轉(zhuǎn)換成本（元/次）85有價證券年利率6.5%最佳現(xiàn)金余額確定方法存貨模式（鮑曼模型）計算與決策結(jié)果（萬元）計算方法利用公式計算規(guī)劃求解結(jié)果最佳現(xiàn)金余額158,454.07158,454.07最低持有現(xiàn)金相關(guān)總成本10,299.5110299.51有價證券交易次數(shù)6161有價證券交易間隔天66實訓(xùn)資料6：某公司每年消耗甲材料14400千克，該材料的采購單價10元，每千克儲存成本2元，每次進貨成本400元。實訓(xùn)要求：試通過設(shè)計模型解答下列問題。(1)該公司經(jīng)濟訂貨量為多少?(2)總成本是多少？(3)最佳進貨次數(shù)？(4)經(jīng)濟批量資金占用額？課上練習(xí)6某企業(yè)年消耗甲材料（千克）14400材料單價（元）10單位儲存成本2一次訂貨成本400計算與決策結(jié)果（元）計算方法利用公式計算規(guī)劃求解結(jié)果最佳經(jīng)濟訂貨量2,400.002,400.00經(jīng)濟批量相關(guān)成本4,800.004,800.00最佳訂貨次數(shù)66.00最佳訂貨周期6060經(jīng)濟訂貨量占用資金1200012000

本節(jié)課主要講解有關(guān)資金營運的相關(guān)知識，介紹應(yīng)收賬款賒銷策略的內(nèi)容以及分析方法，學(xué)會用成本分析法和鮑曼模型做出最佳現(xiàn)金持有量的決策，可以運用不同的經(jīng)濟訂貨批量模型進行經(jīng)濟訂貨量的決策，此外熟練掌握EXCEL下規(guī)劃求解功能，學(xué)會利用EXCEL的數(shù)據(jù)計算和圖表功能設(shè)計應(yīng)收帳款賒銷策略模型、最佳現(xiàn)金持有量決策模型和經(jīng)濟訂貨批量決策模型。課堂小結(jié)感謝聆聽EXCEL表在利潤管理中的應(yīng)用目錄Contents0102目標利潤分析模型03

利潤敏感分析模型04

方案管理器應(yīng)用本量利基本模型01

本量利基本模型

（一）本量利分析

本量利分析，即成本—數(shù)量—利潤分析，是通過數(shù)學(xué)分析和圖示分析等形式對銷售數(shù)量、銷售單價、變動成本、固定成本等因素與利潤指標的內(nèi)在聯(lián)系進行研究，以協(xié)助管理者進行項目規(guī)劃和期間計劃的預(yù)測分析方法。一、本量利基本理論

利潤是企業(yè)在一定時期內(nèi)生產(chǎn)經(jīng)營活動的最終財務(wù)成果，是企業(yè)生產(chǎn)經(jīng)營活動的效率和效益的最終體現(xiàn)。它不僅是反映企業(yè)經(jīng)營狀況的一個基本指標，也是考核、衡量企業(yè)經(jīng)營成果與經(jīng)濟效益最重要的標準。企業(yè)生產(chǎn)經(jīng)營活動的主要目標就是要不斷提高盈利水平，增強獲利能力。（二）本量利分析法的基本假設(shè)（1）所有成本分為變動成本和固定成本。（2）單價和銷售結(jié)構(gòu)在相關(guān)范圍內(nèi)保持不變。（3）生產(chǎn)能力和生產(chǎn)效率在相關(guān)范圍內(nèi)保持不變。（4）產(chǎn)銷量基本平衡，存貨水平保持不變。（三）本量利分析基本公式為：利潤=銷售收入－總成本=銷售收入－（變動成本+固定成本）

=銷售量×（銷售單價－單位變動成本）－固定成本

上述基本關(guān)系中含有五個相互聯(lián)系的變量，只要其中四個變量的值被確定，就可以利用該關(guān)系式推算出另一個變量的數(shù)值，這就是本量利分析的原理。

二、盈虧臨界分析

盈虧臨界點，也稱損益平衡點、保本點，它是指企業(yè)當期銷售收入與當期成本剛好相等、不虧不盈即達到盈虧平衡的狀態(tài)。利潤=銷售收入-變動成本-固定成本（利潤=0）

=銷售量×（銷售單價－單位變動成本）-固定成本

R=Q×（p-v）-F(R利潤，Q銷量，p單價，v單位變動成本，F(xiàn)固定成本)（一）單一品種：盈虧臨界點分析計算公式

保本銷售量=固定成本/單位邊際貢獻=固定成本／（單價－單位變動成本）保本銷售額=保本銷售量×銷售單價=固定成本／邊際貢獻率邊際貢獻=收入總額-變動成本總額單位邊際貢獻=單價-單位變動成本邊際貢獻率=邊際貢獻/收入總額=單位邊際貢獻/單價例題1.某公司生產(chǎn)銷售A產(chǎn)品，資料如下表所示，要求利用EXCEL表計算A產(chǎn)品的保本點銷售量。已知條件項目預(yù)測值單價（元/件）100單位變動成本（元/件）60固定成本350000計算與結(jié)果項

目金

額單位邊際貢獻40.00保本點銷售量8,750.00課上練習(xí)1：某公司生產(chǎn)銷售A產(chǎn)品，預(yù)側(cè)單價90元、變動成本60元，固定成本300000元，要求利用EXCEL表計算A產(chǎn)品的保本點銷售量。計算與結(jié)果項

目金

額單位邊際貢獻30.00保本點銷售量10,000.00（二）多品種：盈虧臨界分析計算公式計算原理：根據(jù)各產(chǎn)品的銷售額和邊際貢獻計算綜合邊際貢獻率；然后在計算綜合保本點銷售額及各產(chǎn)品的保本點銷售額。綜合邊際貢獻率=各產(chǎn)品邊際貢獻總額／銷售收入總額綜合保本點銷售額=固定成本/綜合邊際貢獻率某產(chǎn)品的銷售額=各產(chǎn)品的收入比重×綜合保本點銷售額

例題2.某公司生產(chǎn)和銷售甲、乙、丙三種產(chǎn)品，預(yù)計銷售量、單價和成本等數(shù)據(jù)如表所示，要求建立一個計算綜合保本點銷售額以及保本點各產(chǎn)品銷售額和銷量的模型。已知條件產(chǎn)品名稱甲產(chǎn)品乙產(chǎn)品丙產(chǎn)品預(yù)計銷售量（件）500060007000單位產(chǎn)品售價（元/件）202530單位變動成本（元/件）121621固定成本（元）600000計算過程與結(jié)果產(chǎn)品名稱甲產(chǎn)品乙產(chǎn)品丙產(chǎn)品合計銷售收入（元）100000150000210000460000各產(chǎn)品銷售收入的比重21.74%32.61%45.65%

變動成本6000096000147000

邊際貢獻400005400063000157000綜合邊際貢獻率34.13%綜合保本點銷售額1757961.783保本點各產(chǎn)品銷售額382,165.61573,248.41802,547.77

保本點各產(chǎn)品銷售量19,108.2822,929.9426,751.59

課上練習(xí)2：某公司生產(chǎn)和銷售甲、乙、丙三種產(chǎn)品，預(yù)計銷售量、單價和成本等數(shù)據(jù)如表所示，要求建立一個計算綜合保本點銷售額以及保本點各產(chǎn)品銷售額和銷量的模型。已知條件產(chǎn)品名稱甲產(chǎn)品乙產(chǎn)品丙產(chǎn)品預(yù)計銷售量（件）150016001700單位產(chǎn)品售價（元/件）120125130單位變動成本（元/件）889095固定成本（元）120000（三）盈虧臨界分析模型的設(shè)計

盈虧平衡分析的原理就是利用本量利之間的關(guān)系，計算項目的盈虧平衡點的銷售量，從而分析項目對目標是市場需求變化的適應(yīng)能力。一般來說，盈虧平衡點是指企業(yè)既不虧又不盈或營業(yè)利潤為零時的銷售量。（四）滾動條（窗體控件）功能1.設(shè)置開發(fā)工具

文件-選項-自定義功能區(qū)-開發(fā)工具2.設(shè)置滾動條參數(shù)開發(fā)工具-插入-選擇滾動條按鈕-右鍵-設(shè)置控件格式-控制-設(shè)置參數(shù)-鏈接單元格

例題3.A公司生產(chǎn)一種新產(chǎn)品。公司最大銷售量為1500件，固定成本為37800元，單位可變成本為60元，當前的銷量為900件，單位售價為90元，公司管理層需要建立一個決策模型用于盈虧平衡分析，模型應(yīng)包含以下功能：（1）計算銷售收入、總成本及利潤。（2）計算盈虧平衡銷量及盈虧平衡銷售收入。（3）提供反映公司的銷售收入、總成本、利潤等數(shù)據(jù)的本-量-利圖形，通過滾動條動態(tài)反映出銷量從900件按增量10件變化到1500件時，利潤的變化情況及“盈利”、“虧損”、“保本點”的決策信息。已知條件預(yù)測值的變動情況項目基礎(chǔ)數(shù)據(jù)銷售量變化參數(shù)變動選擇控件銷售量（件）900900產(chǎn)品單價（元/件）90

單位變動成本（元/件）60

固定成本（元）37800

計算過程與結(jié)果銷售收入113400保本點銷售量（件）1260總成本113400保本點銷售額（元）113400利潤0保本點

課上練習(xí)3：某公司生產(chǎn)和銷售一種產(chǎn)品，企業(yè)的銷量為1600件，單價120元，單位變動成本84元，固定成本60000元，預(yù)計企業(yè)的銷量數(shù)變化區(qū)間-10%--10%，公司管理層需要建立一個決策模型用于盈虧平衡分析，模型應(yīng)包含以下功能：（1）計算銷售收入、總成本及利潤。（2）計算盈虧平衡銷量及盈虧平衡銷售收入。（3）提供反映公司的銷售收入、總成本、利潤等數(shù)據(jù)的本-量-利圖形，通過滾動條動態(tài)反映出銷量各因素變化對企業(yè)利潤的變化情況及“盈利”、“虧損”、“保本點”的決策信息。課堂小結(jié)基本公式為：利潤=銷售量×（單價－單位變動成本）－固定成本邊際貢獻=收入－變動成本單位邊際貢獻=單價－單位變動成本邊際貢獻率=邊際貢獻／收入=單位邊際貢獻／單價=（單價－單位變動成本）/單價邊際貢獻率+變動成本率=1（單一品種）保本點銷售量=固定成本/單位邊際貢獻=固定成本/（單價-單位變動成本）（多品種）綜合保本點銷售額=固定成本/綜合邊際貢獻率各品種保本點銷售額=綜合保本點銷售額×銷售比率02

目標利潤分析模型

實現(xiàn)保本僅僅是企業(yè)經(jīng)營的基礎(chǔ)，企業(yè)經(jīng)營的最終目的在于為社會提供優(yōu)質(zhì)產(chǎn)品和服務(wù)的同時獲取最大限度的利潤。如果企業(yè)在經(jīng)營活動開始之前就根據(jù)有關(guān)收支狀況確定了目標利潤，那么，就可以計算確定為實現(xiàn)目標利潤而必須達到的銷售數(shù)量和銷售額，即測算保本點，公式為：

目標利潤=銷售量×（銷售單價－單位變動成本）－固定成本

實現(xiàn)目標利潤的銷售量=（目標利潤＋固定成本）/（銷售單價－單位變動成本）=

（目標利潤＋固定成本）/單位邊際貢獻

實現(xiàn)目標利潤的銷售額=（目標利潤＋固定成本）/邊際貢獻率三、目標利潤分析模型

【例4】

某A公司生產(chǎn)和銷售一種新產(chǎn)品，該公司原來的目標利潤為100000元，在此目標利潤下，銷售單價為12.5元，銷售數(shù)量為60000，固定成本200000元．單位變動成本7.5元?，F(xiàn)在假定公司將目標利潤為150000元，問：從單個因素來看，影響目標利潤的四個基本要素該作怎樣的調(diào)整?利用滾動條按鈕建立一個動態(tài)模型。計算與結(jié)果目標利潤參數(shù)計算可變單元格目標函數(shù)目標利潤的銷量（件）70000150000目標利潤的單價（元/件）13.33150000目標利潤的單位變動成本（元/件）6.67150000目標利潤的固定成本150000150000

【例5】

假設(shè)A公司生產(chǎn)和銷售一種產(chǎn)品，該公司原來的目標利潤為100000元，在此目標利潤下，銷售單價為12.5元,銷售數(shù)量為60000件，固定成本為200000元，單位變動成本為7.5元，由以上數(shù)據(jù)設(shè)置基本數(shù)據(jù)表格。利用滾動條（窗體控件）按鈕，改變單元格的數(shù)據(jù)直觀分析銷售量、單價、單位變動成本、固定成本等各因數(shù)的變化對企業(yè)利潤的影響，每個參數(shù)的變化區(qū)間為（-10%--10%）。通過滾動條按鈕動態(tài)反映出各因素變化對企業(yè)利潤的變化情況及“盈利”、“虧損”、“保本點”的決策信息

課上練習(xí)4：某公司生產(chǎn)和銷售一種產(chǎn)品，企業(yè)的銷量為80000件，單價18元，單位變動成本12元，固定成本300000元，計算企業(yè)的利潤。假如企業(yè)的目標利潤為250000元，利用單變量求解，目標利潤的銷售量、單價、單位變動成本及固定成本。03

敏感分析模型

企業(yè)一定時期的利潤受到銷售量、單價、單位變動成本和固定成本4個因素的影響。為了分析各種因素的變化對利潤指標的影響可以通過建立利潤敏感性分析模型來對利潤變化的情況進行動態(tài)分析。

利潤=銷售量×（銷售單價－單位變動成本）－固定成本

敏感系數(shù)=利潤變動率/某因素的變動率

利潤敏感性分析法是研究和制約利潤的有關(guān)因素發(fā)生某種變化時，利潤變化程度的一種分析方法。影響利潤的因素很多，如售價、單位變動成本、銷量、固定成本等。敏感性分析就是衡量因素變化對結(jié)果的影響程度的分析。

利潤敏感性分析就是計算當銷售量(單價、單位變動成本、固定成本)變動1%時，利潤變動的百分比(即敏感系數(shù))，從而分析各因素利潤的影響程度。四、敏感分析模型

例7某公司只生產(chǎn)和銷售一種產(chǎn)品,其銷售量35000件、單價9元、單位成本5元，固定成本38000元。要求建立一個帶有可以選擇各因素變動率的數(shù)值調(diào)節(jié)按鈕的本量利分析模型，1.計算產(chǎn)品的保本點銷量及銷售額。2.假如銷量、價格、單位成本及固定成本的變化區(qū)間為0-20%，計算銷量、價格、單位成本及固定成本各因素的敏感系數(shù)。建立模型的具體步驟如下所述。04

方案管理器

Excel中的方案管理器能夠幫助用戶創(chuàng)建和管理方案。使用方案，用戶能夠方便地進行假設(shè)，為多個變量存儲輸入值的不同組合，同時為這些組合命名。下面以使用方案管理器對銷售利潤進行預(yù)測為例介紹Excel方案管理器的使用方法。五、方案管理器

例題：某企業(yè)有三種商品分別為商品1、商品2、商品3，資料如下：單位變動成本人力成本120運輸成本70商品1商品2商品3商品成本100012001500商品產(chǎn)量100121144銷售價300025002800

請使用方案管理器，管理上述方案，方案命名“方案一”、“方案二”、“方案三”。人力成本：120；運輸成本70：

方案創(chuàng)建后可以對方案名、可變單元格和方案變量值進行修改，在“方案管理器”對話框的“方案”列表中選擇某個方案后單擊“編輯”按鈕打開“編輯方案”對話框，使用與創(chuàng)建方案相同的步驟進行操作即可。另外，單擊“方案管理器”對話框中的“刪除”按鈕將能夠刪除當前選擇的方案。

實訓(xùn)1、一個職工的年終獎金是全年銷售額的0.1%，前三個季度的銷售額752000，該職工想知道第四季度的銷售額為多少時，才能保證年終獎金為1000元。利用單變量求解計算第四季度的銷售額最低達到多少，才能保證終獎金實現(xiàn)。

實訓(xùn)2、某公司只生產(chǎn)和銷售一種產(chǎn)品,其銷售量10000件、單價65元、成本30元固定成本50000元，要求利用滾動條建立一個可調(diào)節(jié)的各因素變化對利潤影響和利用數(shù)字調(diào)節(jié)按鈕設(shè)計各因素敏感性分析模型。（銷量變化區(qū)間-20%--20%，單價及單位變動成本變化區(qū)間-10%-10%，固定成本變化區(qū)間-5%--5%）

本節(jié)課主要利用EXCEL表計算保本點銷售量、銷售額，設(shè)計保本點分析模型，目標利潤分析模型，使用滾動條（窗體控件）按鈕、數(shù)值調(diào)節(jié)按鈕設(shè)計因素變動分析模型及利潤敏感分析模型。介紹單變量求解及方案管理器的使用方法。課堂小結(jié)感謝聆聽EXCEL表在財務(wù)預(yù)測中的應(yīng)用目錄Contents基本理論0102高低點法03

回歸分析法04

函數(shù)預(yù)測分析法05銷售百分比法01基本理論一、財務(wù)預(yù)算的基本內(nèi)容

財務(wù)預(yù)測作為財務(wù)管理的一個重要環(huán)節(jié)，其內(nèi)容包括資金需要量預(yù)測、成本費用預(yù)測、收入利潤預(yù)測等，其中，資金需求量預(yù)測是財務(wù)預(yù)測的重要內(nèi)容。資金需求量預(yù)測：1.企業(yè)所需要的資金，一部分來自企業(yè)內(nèi)部，另一部分通過外部融資取得。2.企業(yè)需要預(yù)先知道自身的財務(wù)需求，確定資金的需要量，提前安排融資計劃，以免影響資金周轉(zhuǎn)。

資金需要量的預(yù)測的方法有：銷售百分比法、高低點法和回歸分析法等。二、資金需要量預(yù)測的步驟（1）銷售預(yù)測

銷售預(yù)測是企業(yè)財務(wù)預(yù)測的起點；企業(yè)資金需要量預(yù)測以銷售預(yù)測為基礎(chǔ)。（2）估計需要的資產(chǎn)

根據(jù)銷售量，預(yù)測所需資產(chǎn)總量和負債總量。（3）估計收入、費用和留存收益

收入和費用與銷售額之間也存在一定的函數(shù)關(guān)系；凈利潤和股利支付率，共同決定了留存收益所能提供的資金數(shù)額。（4）估計需要追加的資金需要量，確定外部融資數(shù)額

預(yù)計資產(chǎn)總量減去負債總量和內(nèi)部提供的留存收益就是企業(yè)所需的外部融資數(shù)額。02

銷售百分比法三、資金需要量預(yù)測模型設(shè)計

（一）銷售百分比法

該方法有兩個基本假定；其一，假定某項目與銷售額存在比例關(guān)系，能夠計算且保持不變；其二，假定未來的銷售額能夠預(yù)測。

銷售百分比法一般通過編制預(yù)測資產(chǎn)負債表來確定企業(yè)的資金需用量。企業(yè)通過編制預(yù)測資產(chǎn)負債表，可以預(yù)測資產(chǎn)、負債及留存收益等有關(guān)項目的數(shù)額，從而確定企業(yè)需要從外部籌資的數(shù)額。預(yù)測的步驟為：（1）確定資產(chǎn)、負債中與銷售額敏感性項目；（2）計算每個項目占銷售收入的比重；（3）確定計劃期內(nèi)部留存收益的增加額；（4）確定企業(yè)計劃期需要從外部籌措的資金需要量。

計算公式為：M＝式中：M為外部融資需求量，A為隨銷售變化的資產(chǎn)；B為隨銷售變化的負債；S1為基期銷售額；S2為預(yù)測期銷售額；

為銷售變動額；P為銷售凈利潤率；E為保留盈余比率；

為變動資產(chǎn)占基期銷售額的百分比；

為變動負債占基期銷售額的百分比。1.敏感資產(chǎn)項目：如：現(xiàn)金、存貨、應(yīng)收賬款等；2.敏感負債項目：如：應(yīng)付賬款、預(yù)收賬款、預(yù)提費用、應(yīng)付職工薪酬等；3.非敏感項目：對外投資、固定資產(chǎn)、短期借款、非流動負債、實收資本等；4.固定資產(chǎn)項目既可能是非敏感性資產(chǎn)，也可能是敏感性資產(chǎn)【例1】

恒泰公司2018年末資產(chǎn)負債簡表如表。公司當年的銷售額為1100萬元，銷售凈利潤率為10％，企業(yè)生產(chǎn)能力尚有剩余。預(yù)計2019年銷售額增長20％，獲利能力保持前一年水平，2019年預(yù)計股利分配率為60％。試預(yù)測2019年籌資需求。資產(chǎn)金額負債及所有者權(quán)益金額現(xiàn)金200應(yīng)付賬款150應(yīng)收賬款60應(yīng)付票據(jù)20存貨265短期借款9固定資產(chǎn)凈值475應(yīng)付債券0

實收資本600

資本公積140

留存收益81資產(chǎn)合計1000負債及所有者權(quán)益合計1000銷售百分比計算表資產(chǎn)金額負債及所有者權(quán)益金額現(xiàn)金18.18%應(yīng)付賬款13.64%應(yīng)收賬款5.45%應(yīng)付票據(jù)1.82%存貨24.09%短期借款N固定資產(chǎn)凈值N應(yīng)付債券N實收資本N資本公積N留存收益N資產(chǎn)合計47.73%負債及所有者權(quán)益合計15.45%課上練習(xí)1：清源公司2020年銷售收入800萬元，銷售凈利率15%，利潤分配率60%，資產(chǎn)負債表如表所示：該公司2021年預(yù)計銷售收入增長率20%，銷售凈利率與利潤分配政策與上年保持一致。預(yù)測該公司2021年外部籌資額。課堂小結(jié)1銷售百分比法1.計算敏感項目占銷售額的比重（資產(chǎn)和負債）；2.凈增加的資金需求量=資產(chǎn)增加的資金需求量-負債增加的資金需求量；3.留存收益增加額=凈利潤×（1-股利分配率%）4.預(yù)測凈利潤=目前銷售額S1×（1+增長率%）×銷售利潤率%5.外部資金需求量=凈增加的資金需求量-留存收益增加額03

高低點法（二）高低點法

高低點法是根據(jù)資金習(xí)性預(yù)測未來資金需要量的一種方法。從中選出銷量最高和最低的兩點坐標建立線性預(yù)測方程，據(jù)此推算不變資金總額和單位變動資金從而預(yù)測資金需要量的一種方法。不變資金是指在一定的產(chǎn)銷量范圍內(nèi),不受產(chǎn)銷量變動的影響而保持固定不變的那部分資金。變動資金是指隨產(chǎn)銷量的變動而成比例變動的那部分資金。具體步驟：1.找出產(chǎn)銷量最高點和最低點，x1,x2y1,y2線性方程y=ax+b2.計算單位變動資金（斜率）

單位變動資金a=高低點資金占用量之差/高低點產(chǎn)量之差=(y1-y2)/(x1-x2)3.計算不變資金總額（截距）

不變資金總額b=高點占用資金-單位變動資金*高點產(chǎn)銷量=y1-ax14.預(yù)測某一時期的資金需求量；根據(jù)預(yù)測銷售量x3,計算資金需求量y3年底產(chǎn)銷量（萬件）資金占用量（萬元）20148972015121352016151682017201622018252052019預(yù)計產(chǎn)銷量（萬件）28

【例2】某公司2014年值2018年的產(chǎn)銷量和資金占用量及2019年預(yù)測的產(chǎn)銷量如表所示。要求建立一個高低點法預(yù)測該公司2019年資金需要量的模型。課上練習(xí)2：某企業(yè)產(chǎn)量和資金需要量的歷史資料如表所示。假設(shè)該企業(yè)2020年產(chǎn)量12萬件，要求用高低點法預(yù)測2020年的資金需求量。已知條件年度產(chǎn)銷量（萬件）資金占用量（萬元）20157.5800201687802017982020188.579020199.59002020預(yù)計產(chǎn)銷量（萬件）1204

回歸分析法（三）回歸分析法

回歸分析法是利用數(shù)理統(tǒng)計中回歸分析來確定兩種或兩種以上的變量間相互依賴的定量關(guān)系的一種統(tǒng)計分析方法?！纠?】

豐源公司2013年--2018年銷售量及對應(yīng)的資金需要量如表所示。預(yù)計2019年的銷售量12萬件，請預(yù)測2019年的資金需要量。年度銷售量X（萬件）資金需要量Y（萬元）2013910201489.5201578201691020171010.520181112項目銷售量X（萬件）資金需要量Y（萬元）2019年1212.70回歸方程：Y=0.9X+1.9

課上練習(xí)3：清源公司2020年下半年銷售量和資金需要量的資料如表所示，該公司預(yù)計2021年1月份銷售量150萬件，要求用回歸分析法預(yù)測2020年1月份資金需求量。清源公司2020年下半年銷售量與資金占用量月份789101112銷售量（萬件）1001059095110120資金占用量（萬元）140152138140148158項目銷售量X（萬件）資金需要量Y（萬元）2021年1月150178.64回歸方程：Y=0.69X+75.14課堂小結(jié)2一.銷售百分比法資金需求量=（資產(chǎn)%-負債%）*（銷售凈增加額）外部資金需求量=資金需求量-凈利潤*（1-股利分配率）二.高低點法y=ax+ba=(y1-y2)/(x1-x2)b=y1-ax1y3=ax3+b三.回歸分析法利用Excel表的回歸分析功能

求出y=ax+b方程，根據(jù)方程求解。05

函數(shù)預(yù)測分析法

（一）LINEST函數(shù)：對已知的數(shù)據(jù)進行最佳直線擬合；其用途是使用最小二乘法對已知數(shù)據(jù)進行最佳直線擬合，并返回描述此直線的數(shù)組，表達式y(tǒng)=mx+b。LINEST函數(shù)語法為：LINEST(known_y's，known_x's，const，stats)。Known_y's是表達式y(tǒng)=mx+b中已知的y值集合；Known_x's是關(guān)系表達式y(tǒng)=mx+b中已知的可選x值集合；Const為一邏輯值，指明是否強制使常數(shù)b為0，如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為0，并同時調(diào)整m值使得y=mx；Stats為一邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE，函數(shù)LINEST返回附加回歸統(tǒng)計值。如果stats為FALSE或省略，函數(shù)LINEST只返回系數(shù)m和常數(shù)項b。四、函數(shù)預(yù)測分析法

利用EXCEL提供的LINEST()、TREND()、LOGEST()、GROWTH()函數(shù)用于預(yù)測分析。

【例4】某企業(yè)2014年-2018年銷售收入與資金需要量的關(guān)系如表所示，2019年預(yù)測銷售收入3000000元，資金需要量是多少元？年份20142015201620172018資金需要量Y12200001440000158000017200001800000銷售收入X18000002025000220000024400002610000（1）E3單元格插入函數(shù)LINEST()Known_y's：B4:F4

Known_x's：B5:F5Const：默認Stats：默認（2）確定，得出m=0.71（3）利用組合鍵求b:選中E8：F8，按F2，顯示函數(shù)參數(shù)，【ctrl+shift+enter】

b=-18558.87（4）表達式：y=0.71x-18558.87（5）預(yù)測當收入為3000000時，資金需要量2108608.86元

（二）TREND函數(shù)構(gòu)造線性回歸直線方程函數(shù)名為：TREND(known_y's,known_x's,new_x's,const)函數(shù)功能返回一條線性回歸擬合線的值；即找到適合已知數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)值new_x's在直線上對應(yīng)的y值，表達式y(tǒng)=mx+b。函數(shù)語法：TREND(known_y's,known_x's,new_x's,const)known_y's：表示為已知y=mx+b中的y值集合。known_x's：表示為已知y=mx+b中可選的x值集合。new_x's：表示新的x值，需要通過TREND函數(shù)返回的對應(yīng)的y值。Const：表示邏輯值，指明是否將常數(shù)b強制為0。【例5】某公司2014年-2018年的收入和資金占用量如表所示。2019年預(yù)測銷售收入305萬元，要求使用TREND函數(shù)，預(yù)測其資金需求量是多少？已知條件（萬元）年度銷售收入資金需要量2019年預(yù)計銷售收入30520148562

201511685

2016175109

2017237128

2018291143

C11單元格插入函數(shù)TREND()known_y's：C4:C8

known_x's：B4:B8new_x's：C10Const：默認計算結(jié)果2019年資金需要量（萬元）152.43

（三）LOGEST函數(shù)：回歸擬合曲線返回該曲線的值LOGEST函數(shù)：在回歸分析中，計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。表達式：y=b*m^xLOGEST函數(shù)語法為：LOGEST(known_y's，known_x's，const，stats)。Known_y's表示為一組符合y=b*m^x函數(shù)關(guān)系式的y值的集合；Known_x's表示為一組符合y=b*m^x函數(shù)關(guān)系式的可選x值集合；Const為一邏輯值，指明是否強制使常數(shù)b為0，如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為0，并同時調(diào)整m值使得y=m^x；Stats為一邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE，函數(shù)函數(shù)返回附加回歸統(tǒng)計值。如果stats為FALSE或省略，函數(shù)只返回系數(shù)m和常數(shù)項b?！纠?】某企業(yè)2014年-2018年銷售收入與資金需要量的關(guān)系如表所示，2019年銷售收入預(yù)測3000000元，請使用LOGEST()預(yù)測資金需要量是多少元？年份20142015201620172018資金需要量Y24500002650000285000032300003360000銷售收入X18000002025000220000024400002610000（1）輸入函數(shù)LOGEST()Known_y's：B4:F4Known_x's：B5:F5Const：默認Stats：默認（2）確定，得出m=1.0000004（3）利用組合鍵求b:選中E8：F8，按F2，顯示函數(shù)參數(shù)，【ctrl+shift+enter】b=1167400.15（4）表達式：y=1167400.15*1.0000004^x（5）E10=POWER(D11,C8)*F8（6）當收入為3000000時，預(yù)測資金需要量3980706.17元（四）GROWTH根據(jù)現(xiàn)有的數(shù)據(jù)預(yù)測指數(shù)增長值函數(shù)名為：GROWTH(known_y's,known_x's,new_x's,const)函數(shù)用于給定數(shù)據(jù)預(yù)測指數(shù)增長值。根據(jù)現(xiàn)有的x值和y值，GROWTH函數(shù)返回一組新的x值和y值，可以使用GROWTH函數(shù)來擬合滿足現(xiàn)有的x值和y值的指數(shù)曲線。函數(shù)語法：GROWTH(known_y's,known_x's,new_x's,const)known_y's：表示滿足指數(shù)回歸擬合曲線的一組已知的y值。known_x's：表示滿足指數(shù)回歸擬合曲線的一組已知的x值。new_x's：表示一組新的x值，可以通過GROWTH函數(shù)返回的對應(yīng)的y值。Const：表示邏輯值，指明是否將系數(shù)b強制為1。如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為1。【例8】某公司2019年1-9月份的資金需求量如表所示，預(yù)測10、11、12月份的資金需求量。月份資金需要量預(yù)測10、11、12月份資金需求量1128951013874.007622132871113882.105323133661213890.20775416899

513600

613697

715123

812956

913135

（1）在D4單元格輸入函數(shù)GROWTH()known_y's：B4:B12

known_x's：A4:A12new_x's：C4:C6Const：省略返回10月份的資金預(yù)測量13874（2）選中D4:D6,按F2，顯示函數(shù)參數(shù)。（3）按【ctrl+shift+enter】，返回11月份資金預(yù)測值13882，12月份的資金預(yù)測值13890?！纠?】

達盟公司2020年銷售資料如表所示，已知該公司生產(chǎn)銷售趨于平穩(wěn)，預(yù)測2020年12月份的銷售額。要求分別利用LNEST()函數(shù)、TREND()函數(shù)、LOGEST()函數(shù)和GROWTH()函數(shù)預(yù)測。已知條件（萬元）年份2020年實際銷售月份1234567891011

銷售額350320320340370400450500550650750

銷售額可能變動趨勢預(yù)測期自變量

線性趨勢y=m*x+b2020年12月12

指數(shù)趨勢y=b*m^x

課上練習(xí)4：某公司2010-2020年銷售資料如表所示，已知該公司生產(chǎn)銷售趨于平穩(wěn)，預(yù)測2021的銷售量為800，預(yù)測2021年資金需求量。要求分別利用LNEST()函數(shù)、TREND()函數(shù)、LOGEST()函數(shù)和GROWTH()函數(shù)預(yù)測。已知條件（萬元）年份20101112131415161718192020銷售量x249210220225255285320380420500600資金量y350320320340370400450500550650750變動趨勢預(yù)測期銷售量

線性趨勢y=m*x+b2021800

指數(shù)趨勢y=b*m^x

本節(jié)課主要對財務(wù)預(yù)測分析進行學(xué)習(xí)，學(xué)完本節(jié)課后要掌握財務(wù)預(yù)測分析的方法和財務(wù)預(yù)測模型的設(shè)計；熟練掌握銷售百分比法、高低點法和回歸分析法進行財務(wù)預(yù)測的方法，掌握EXCEL中提供的預(yù)測函數(shù)（LINEST()、TREND()、LOGEST()、GROWTH()函數(shù)）的基本功能及使用方法。課堂小結(jié)4感謝聆聽EXCEL表在財務(wù)分析中的應(yīng)用目錄Contents財務(wù)分析基本理論0102財務(wù)比較分析模型03

財務(wù)圖表分析模型04財務(wù)比率指標及應(yīng)用05杜邦分析體系01財務(wù)比率基本理論二、財務(wù)分析的意義（一）財務(wù)分析是評價財務(wù)狀況、衡量經(jīng)營業(yè)績的重要依據(jù)。（二）財務(wù)分析可以識別會計報表的粉飾及動機。（三）財務(wù)分析是合理實施投資決策的重要步驟。（四）財務(wù)分析是挖掘潛力、改進工作、實現(xiàn)理財目標的重要手段。一、財務(wù)分析的目的（一）評價過去的經(jīng)營業(yè)績（二）衡量當前財務(wù)狀況（三）預(yù)計未來的發(fā)展前景三、財務(wù)分析的程序（一）財務(wù)分析信息搜集整理階段；（二）戰(zhàn)略分析與會計分析階段；（三）財務(wù)分析實施階段；（四）財務(wù)分析綜合評價階段。四、財務(wù)分析的主要方法（一）財務(wù)比率分析法：計算財務(wù)比率（二）財務(wù)比較分析法：企業(yè)財務(wù)比率與行業(yè)財務(wù)比率比較（三）財務(wù)圖解分析法1.趨勢圖解分析法：縱向比較2.結(jié)構(gòu)圖解分析法：報表結(jié)構(gòu)（四）財務(wù)綜合分析法--杜邦分析體系五、財務(wù)分析的數(shù)據(jù)來源（一）會計核算數(shù)據(jù)（二）外部數(shù)據(jù)02

財務(wù)比率指標及應(yīng)用

財務(wù)比率分析是以某一基準進行比較的分析方法。單純的財務(wù)比率是沒有意義的。財務(wù)比率是通過財務(wù)報表中的財務(wù)數(shù)據(jù)的關(guān)聯(lián)性計算得到的，顯示了財務(wù)報表科目之間的關(guān)系。

財務(wù)比率分析是指將財務(wù)報表中的有關(guān)項目進行對比，得出一系列的財務(wù)比率，以此來揭示企業(yè)財務(wù)狀況的一種方法。

財務(wù)比率分析有外部比較和內(nèi)部比較兩種。

外部比較是企業(yè)之間的比較，它以同行業(yè)或同類型企業(yè)的平均值為基礎(chǔ)進行比較。

內(nèi)部比較是將企業(yè)近幾年的財務(wù)比率進行比較分析，考察本企業(yè)的財務(wù)狀況和變化趨勢。

常用的財務(wù)比率可分為變現(xiàn)能力比率、資產(chǎn)管理比率、負債比率、盈利能力比率等幾大類。四、財務(wù)比率指標及應(yīng)用

（一）變現(xiàn)能力比率

變現(xiàn)能力比率又稱短期償債能力比率，是衡量企業(yè)產(chǎn)生現(xiàn)金能力大小的比率，它取決于可以在近期轉(zhuǎn)變?yōu)楝F(xiàn)金的流動資產(chǎn)的多少。反映變現(xiàn)能力的財務(wù)比率主要有流動比率和速動比率。1.流動比率

流動比率=流動資產(chǎn)／流動負債

（數(shù)據(jù)來源：資產(chǎn)負債表）

流動比率是衡量企業(yè)短期償債能力的一個重要財務(wù)指標。

這個比率越高，說明企業(yè)償還流動負債的能力越強，流動負債得到償還的保障越大。流動比率在2左右比較合適。2.速動比率

速動比率=速動資產(chǎn)／流動負債=（流動資產(chǎn)－存貨－預(yù)付賬款－一年內(nèi)到期的非流動資產(chǎn)－其他流動資產(chǎn)）／流動負債

速動資產(chǎn)是流動資產(chǎn)減去變現(xiàn)能力較差且不穩(wěn)定的存貨、預(yù)付賬款、一年內(nèi)到期的非流動資產(chǎn)和其他流動資產(chǎn)等后的余額。

一般情況下，速動比率越高，說明企業(yè)償還流動負債的能力越強。但速動比率過高，則表明企業(yè)會因現(xiàn)金及應(yīng)收賬款占用過多而增加企業(yè)的機會成本。通常認為正常的速動比率為1，低于1的速動比率被認為是短期償債能力偏低。

（二）資產(chǎn)管理比率

資產(chǎn)管理比率，又稱運營效率比率，是用來衡量公司在資產(chǎn)管理方面效率高低的財務(wù)比率。

資產(chǎn)管理比率包括：存貨周轉(zhuǎn)率、應(yīng)收賬款周轉(zhuǎn)率、流動資產(chǎn)周轉(zhuǎn)率、固定資產(chǎn)周轉(zhuǎn)率和總資產(chǎn)周轉(zhuǎn)率。

通過對這些指標的高低及其成因的考察，決策者能夠?qū)Y產(chǎn)是否在有效運轉(zhuǎn)、資產(chǎn)結(jié)構(gòu)是否合理、所有的資產(chǎn)是否能有效利用以及資產(chǎn)總量是否合理等問題做出較為客觀的認識。1.存貨周轉(zhuǎn)率

存貨周轉(zhuǎn)率是衡量和評價企業(yè)購入存貨、投入生產(chǎn)、銷售收回等各環(huán)節(jié)管理狀況的綜合性指標。它是銷售成本被平均存貨所除而得到的比率，又稱存貨的周轉(zhuǎn)次數(shù)。用時間表示的存貨周轉(zhuǎn)率就是存貨周轉(zhuǎn)天數(shù)。其計算公式為：

存貨周轉(zhuǎn)率（周轉(zhuǎn)次數(shù)）=營業(yè)成本／平均存貨余額

存貨周轉(zhuǎn)天數(shù)=360／存貨周轉(zhuǎn)率

平均存貨=（期初存貨余額+期末存貨余額）／2

一般來講，存貨周轉(zhuǎn)率越高越好。存貨周轉(zhuǎn)率越高，表明存貨變現(xiàn)的速度越快，周轉(zhuǎn)額越大，資金占用水平越低。2.應(yīng)收賬款周轉(zhuǎn)率應(yīng)收賬款周轉(zhuǎn)率=營業(yè)收入／平均應(yīng)收賬款余額應(yīng)收賬款周轉(zhuǎn)天數(shù)=360／應(yīng)收賬款周轉(zhuǎn)率其中，應(yīng)收賬款包括會計核算中“應(yīng)收賬款”和“應(yīng)收票據(jù)”等全部賒銷賬款。平均應(yīng)收款=（期初應(yīng)收款余額+期末應(yīng)收款余額）／2

一般而言，應(yīng)收賬款周轉(zhuǎn)率越高，應(yīng)收賬款周轉(zhuǎn)天數(shù)越短，說明應(yīng)收賬款的收回越快，可以減少壞賬損失。

應(yīng)收賬款周轉(zhuǎn)天數(shù)同時還考察了企業(yè)的信用管理能力。如果與行業(yè)平均值偏離過大，應(yīng)考慮公司的信用政策是否合理，或是否還有其他原因。3.流動資產(chǎn)周轉(zhuǎn)率

流動資產(chǎn)周轉(zhuǎn)率是銷售收入與流動資產(chǎn)平均余額之比，它反映的是全部流動資產(chǎn)的利用效率。其計算公式為：

流動資產(chǎn)周轉(zhuǎn)率=營業(yè)收入／平均流動資產(chǎn)

平均流動資產(chǎn)=（期初流動資產(chǎn)余額+期末流動資產(chǎn)余額）／24.固定資產(chǎn)周轉(zhuǎn)率

固定資產(chǎn)周轉(zhuǎn)率是企業(yè)銷售收入與平均固定資產(chǎn)凈值之比。該比率越高，說明固定資產(chǎn)的利用率越高，管理水平越好。其計算公式為：

固定資產(chǎn)周轉(zhuǎn)率=營業(yè)收入／平均固定資產(chǎn)凈值

平均固定資產(chǎn)凈值=（期初固定資產(chǎn)凈值+期末固定資產(chǎn)凈值）／25.總資產(chǎn)周轉(zhuǎn)率

總資產(chǎn)周轉(zhuǎn)率是企業(yè)銷售收入與平均資產(chǎn)總額之比，可以用來分析企業(yè)全部資產(chǎn)的使用效率?？傎Y產(chǎn)周轉(zhuǎn)率（周轉(zhuǎn)次數(shù)）=營業(yè)收入/平均資產(chǎn)總額平均資產(chǎn)總額=（期初資產(chǎn)總額+期末資產(chǎn)總額）／2

如果公司的總資產(chǎn)利用率較低，說明企業(yè)的資產(chǎn)利用不充分。

若公司有閑置資產(chǎn)，則應(yīng)設(shè)法變賣，若公司在建工程未完工，則占用的資產(chǎn)暫時不能帶來效益，這一點在分析時應(yīng)注意。

（三）負債比率

負債比率是說明債務(wù)和資產(chǎn)、凈資產(chǎn)間關(guān)系的比率。它反映企業(yè)償付到期長期債務(wù)的能力。通過對負債比率的分析，可以看出企業(yè)的資本結(jié)構(gòu)是否健全合理，從而評價企業(yè)的長期償債能力。負債比率主要有：資產(chǎn)負債率、股東權(quán)益比率、產(chǎn)權(quán)比率和利息保障倍數(shù)等。1.資產(chǎn)負債率

資產(chǎn)負債率是企業(yè)負債總額與資產(chǎn)總額之比，它反映企業(yè)的資產(chǎn)總額中有多少是通過舉債而得到的。其計算公式為：

資產(chǎn)負債率=負債總額／資產(chǎn)總額×100%

資產(chǎn)負債率反映企業(yè)償還債務(wù)的綜合能力，該比率越高，企業(yè)償還債務(wù)的能力越差，企業(yè)財務(wù)風險也越大。反之，償還債務(wù)的能力越強，企業(yè)財務(wù)風險也越小。注意：在對該指標進行分析時，不能簡單的從指標數(shù)值的高低進行考察。不同的人對資產(chǎn)負債比率取值的要求不同。2.股東權(quán)益比率

股東權(quán)益比率是股東權(quán)益總額與資產(chǎn)總額之比。該比率反映企業(yè)資產(chǎn)中有多少屬于所有者。其計算公式為：

股東權(quán)益比率=股東權(quán)益總額／資產(chǎn)總額×100%3.產(chǎn)權(quán)比率

產(chǎn)權(quán)比率又稱負債權(quán)益比率，是負債總額與股東權(quán)益總額之比。

產(chǎn)權(quán)比率=負債總額／股東權(quán)益總額×100%

該比率反映了債權(quán)人所提供的資金與股東所提供資金的對比關(guān)系，從而揭示企業(yè)的財務(wù)風險以及股東權(quán)益對債務(wù)的保障程度。

該比率越低，說明企業(yè)長期財務(wù)狀況越好，債權(quán)人貸款的安全越有保障，企業(yè)風險越小。4.利息保障倍數(shù)

利息保障倍數(shù)是稅前利潤加利息支出之和（即息稅前利潤）與利息支出的比值，其計算公式為：

利息保障倍數(shù)=（稅前利潤+利息支出）／利息支出=息稅前利潤／利息支出

反映了企業(yè)用經(jīng)營所得支付債務(wù)利息的能力。該比率越高，說明企業(yè)用經(jīng)