《Excel在財(cái)務(wù)管理中的應(yīng)用》課件 05EXCEL表在營(yíng)運(yùn)資金管理中的應(yīng)用、06EXCEL表在利潤(rùn)管理中的應(yīng)用

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

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

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

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

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

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

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

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

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

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

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

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

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

（一）成本分析模式現(xiàn)金成本機(jī)會(huì)成本現(xiàn)金持有量過多，機(jī)會(huì)成本大幅度上升（正比例關(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)金持有方案成本資料項(xiàng)目ABCD平均現(xiàn)金持有量（元）100000200000300000400000機(jī)會(huì)成本率10%10%10%10%短缺成本（元）4800025000100006000管理成本（元）40000400004000040000

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

鮑曼模型（存貨模式）

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

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

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

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

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

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

C方案

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

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

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

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

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

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

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

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

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

如果供應(yīng)商實(shí)行數(shù)量折扣，那么，除了訂貨成本和儲(chǔ)存成本之外，采購(gòu)成本也成了決策中的相關(guān)成本。三種成本的年成本合計(jì)最低的方案，才是最優(yōu)方案。計(jì)算公式：總成本TC=訂貨成本+儲(chǔ)存成本+采購(gòu)成本=T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)式中，T為總需求量；Q為經(jīng)濟(jì)訂貨量；F為訂貨成本；P為每日送貨量；D為每日消耗量；Q/P為每批存貨全部送達(dá)所需日數(shù)；Q/P*D為送貨期內(nèi)全部耗用量；U為采購(gòu)單價(jià)；Di為數(shù)量折扣；F為訂貨成本；K為單位儲(chǔ)存成本?！纠?】某企業(yè)有四種貨物要采購(gòu)，供應(yīng)商規(guī)定了數(shù)量折扣，其數(shù)量折扣條件為：甲材料>400kg，乙材料>450kg，丙材料>500kg，丁材料>500kg；相關(guān)各項(xiàng)目數(shù)據(jù)見圖所示。存貨名稱甲材料乙材料丙材料丁材料材料年需要量T18000200003000025000一次訂貨成本K25252525單位儲(chǔ)存成本C2343每日送貨量P100200300250每日耗用量d20304025數(shù)量折扣di2%2%2%2%單價(jià)U10203025規(guī)劃求解存貨名稱甲材料乙材料丙材料丁材料最優(yōu)訂貨批量Q*750626658680采購(gòu)成本176,400392,000882,000612,500儲(chǔ)存成本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)濟(jì)訂貨量占用資金3675613796708335鮑曼模型（存貨模型）現(xiàn)金成本=轉(zhuǎn)換成本+持有機(jī)會(huì)成本：

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

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

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

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

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

利潤(rùn)敏感分析模型04

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

本量利基本模型

（一）本量利分析

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

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

=銷售量×（銷售單價(jià)－單位變動(dòng)成本）－固定成本

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

二、盈虧臨界分析

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

=銷售量×（銷售單價(jià)－單位變動(dòng)成本）-固定成本

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

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

目金

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

目金

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

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

變動(dòng)成本6000096000147000

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

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

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

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

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

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

單位變動(dòng)成本（元/件）60

固定成本（元）37800

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

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

目標(biāo)利潤(rùn)分析模型

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

目標(biāo)利潤(rùn)=銷售量×（銷售單價(jià)－單位變動(dòng)成本）－固定成本

實(shí)現(xiàn)目標(biāo)利潤(rùn)的銷售量=（目標(biāo)利潤(rùn)＋固定成本）/（銷售單價(jià)－單位變動(dòng)成本）=

（目標(biāo)利潤(rùn)＋固定成本）/單位邊際貢獻(xiàn)

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

【例4】

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

【例5】

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

課上練習(xí)