MBA預(yù)備課程Excel應(yīng)用上海財(cái)經(jīng)大學(xué)電子商務(wù)系崔麗麗Excel應(yīng)用3盈虧平衡分析管理決策與管理決策模型模型是對(duì)于問題的本質(zhì)屬性的簡化表示管理問題經(jīng)常采用不同類型的模型幫助分析和決策問題數(shù)學(xué)模型也是一種經(jīng)常采用的模型利用計(jì)算機(jī)技術(shù)實(shí)現(xiàn)的數(shù)學(xué)模型就是計(jì)算機(jī)模型數(shù)學(xué)模型（定量模型）是描述問題中各個(gè)變量之間的依賴關(guān)系的數(shù)學(xué)關(guān)系式數(shù)學(xué)模型中的變量包括決策變量、目標(biāo)變量與中間變量以及各種外生參數(shù)變量問題:確定壟斷商品的生產(chǎn)數(shù)量目標(biāo)變量決策變量外生參數(shù)數(shù)量數(shù)單量價(jià)銷售收益中間變量利潤總成本單位變動(dòng)成本固定成本決策者決策過程的各個(gè)階段分析決策問題，找到?jīng)Q策的變量建立決策問題的數(shù)學(xué)模型根據(jù)數(shù)學(xué)模型建立電子表格決策模型利用決策模型進(jìn)行“What-If”分析結(jié)合決策者本人對(duì)外生參數(shù)可能取值的個(gè)人判斷確定付諸實(shí)施的最后方案盈虧平衡分析概述盈虧平衡分析的一個(gè)基本應(yīng)用是本－量－利分析通過分析成本、銷量和利潤三者之間的關(guān)系，幫助決策在生產(chǎn)與銷售一種產(chǎn)品時(shí)，因?yàn)榇嬖诠潭ǔ杀荆麧櫯c產(chǎn)品銷量一般會(huì)呈現(xiàn)這樣的關(guān)系–產(chǎn)品銷量較小時(shí)，利潤為負(fù)值（虧損）–銷量超過某個(gè)臨界數(shù)量后，利潤變?yōu)檎担ㄓ┄C使銷售達(dá)到不盈不虧的狀態(tài)的銷量，就是盈虧平衡點(diǎn)，即盈虧平衡銷量(Breakeven

Point).盈虧平衡分析的應(yīng)用盈虧平衡分析支持決策–新產(chǎn)品開發(fā)時(shí)，計(jì)算達(dá)到盈利所需要的銷量–現(xiàn)有水平上擴(kuò)張時(shí)，計(jì)算銷售收益的變化–設(shè)備改造時(shí)，分析設(shè)備購置效應(yīng)盈虧平衡分析的理解確定盈虧平衡點(diǎn)的兩種理解方法–在銷量增長的過程中，銷售收益逐步克服總成本，從而使虧損逐步轉(zhuǎn)為盈利–在銷量增長的過程中，邊際貢獻(xiàn)逐步克服固定成本，從而使虧損逐步轉(zhuǎn)為盈利盈虧平衡分析的數(shù)學(xué)表達(dá)銷售收益克服總成本達(dá)到盈虧平衡p

-

vFQ

=R

-

C

=

0pQ0

-

(F

+

vQ0

)

=

0(p

-

v

)Q0

-

F

=

0銷售收益（R）=pQp:單價(jià)總成本（C）=F

+vQv:單位變動(dòng)成本利潤（p）=R-CF：固定成本盈虧平衡時(shí):Q：銷量0盈虧平衡分析的數(shù)學(xué)表達(dá)邊際貢獻(xiàn)克服固定成本

單位邊際貢獻(xiàn)(m)=p

-v 邊際貢獻(xiàn)(M

)=mQ利潤(p)=mQ

-F.............

=

M

-

F盈虧平衡時(shí)：M

-

F

=

0mQ0

-

F

=

0mQ

=

F0盈虧平衡分析的數(shù)學(xué)表達(dá)邊際貢獻(xiàn)率(k)=單位邊際貢獻(xiàn)(p

-v)=1-v銷售單價(jià)(

p)

pk

=邊際貢獻(xiàn)(R

-V

)=1-V銷售收益(R)

R利潤(p

)=kR

-F邊際貢獻(xiàn)克服固定成本邊際貢獻(xiàn)率(k)=單位邊際貢獻(xiàn)(p

-

v)

=1-v銷售單價(jià)(

p)

pk

=邊際貢獻(xiàn)(R

-V

)

=1-V銷售收益(R)

R利潤(p

)=kR

-F盈虧平衡銷量FQ

0

=

m盈虧平衡銷量的兩種概念-使邊際貢獻(xiàn)與固定成本達(dá)到平衡的銷量利潤(p)=M

-Fm

=

p

-v0

=

mQ0

-

Fp

-

vF-使銷售收益與總成本達(dá)到平衡的銷量利潤(p

)=R

-C0

=

pQ0

-(F

+

vQ0

)0

=

(

p

-

v)Q0

-

FQ

=0盈虧平衡銷量盈虧平衡銷量的計(jì)算方法利用數(shù)學(xué)公式來計(jì)算利用靈敏度分析生成的銷量-利潤對(duì)照表,查表再加內(nèi)插計(jì)算的方法利用規(guī)劃求解(Solver)/單變量求解(GoalSeeking)功能盈虧平衡分析實(shí)操【例1】某公司制造一種運(yùn)動(dòng)鞋，平均每雙銷售價(jià)格為90元，每雙可變成本由下面四種費(fèi)用組成：直接人工費(fèi)用為2.25元，直接材料費(fèi)用為28.25元，可變制造費(fèi)用為3.10元，可變管理費(fèi)用為2.40元；固定成本由下面四種費(fèi)用組成：管理層薪酬為23000元，保險(xiǎn)費(fèi)用為1300元，折舊費(fèi)用為5000元，廣告費(fèi)用為8500元。假設(shè)此公司最高生產(chǎn)能力為1500，且預(yù)測(cè)銷量為

900，試在Excel中建立盈虧平衡分析模型，分析該產(chǎn)品可能產(chǎn)生的利潤和該產(chǎn)品的盈虧平衡點(diǎn)，并分析單價(jià)的變化對(duì)盈虧平衡點(diǎn)會(huì)有什么影響盈虧平衡分析實(shí)操63000.010800.00140000120000100000800006000040000200000-20000

0-40000250500750100012501500銷售收益 總成本 利潤盈虧平衡分析圖形銷量=900時(shí),盈利 售價(jià)=90元，盈虧平衡銷量=700盈虧平衡分析實(shí)操(續(xù))【例2】采用例1提供的數(shù)據(jù)。假設(shè)此公司最高生產(chǎn)能力為1500，且預(yù)測(cè)銷售量為900，試在Excel中建立盈虧平衡模型，按如下要求操作：計(jì)算銷售收益、總成本、利潤；計(jì)算盈虧平衡銷量；繪制反映盈利與虧損的堆積面積圖圖表；在圖表中添加一個(gè)微調(diào)項(xiàng)控件，使得管理層薪酬從14000到50000按步長500變化，且顯示“盈利

”、“保本”或“虧損”等結(jié)論文字。盈虧平衡分析實(shí)操(續(xù))邊際貢獻(xiàn)、固定成本、利潤曲線-20000-400000

200

400

600

800100012001400管理層薪酬=37000 盈虧平衡銷量=959140000邊際貢獻(xiàn)120000

固定成本利潤(π)100000800006000040000200000銷量=900時(shí),虧損，利潤=-3200安全邊際和安全邊際分析公司真正需要的不是不盈不虧而是盈利，因此借助于盈虧平衡分析確定的盈虧平衡銷量（或銷售收益）只是必須超過的下限公司需要確定達(dá)到目標(biāo)利潤的銷量（或銷售收益）計(jì)劃銷量（目標(biāo)銷量）超過盈虧平衡銷量越多，一方面，如果能達(dá)到則公司可以獲得越大的利潤，另一方面，在出現(xiàn)不利因素時(shí)發(fā)生虧損的可能性也越小，也就是越安全計(jì)劃銷量超過盈虧平衡銷量的部分稱為安全邊際安全邊際除以計(jì)劃銷量為安全邊際率(Q

-

Q

) (R

-

R

)=

0

=

0Q

RQ安全邊際率:s

=S安全邊際:S

=Q

-Q0反映產(chǎn)品盈利的安全幅度反映產(chǎn)品盈利的安全程度安全邊際和安全邊際分析(續(xù))目標(biāo)利潤與目標(biāo)銷量盈虧平衡幫助企業(yè)實(shí)現(xiàn)目標(biāo)控制企業(yè)設(shè)定將實(shí)現(xiàn)的目標(biāo)利潤，盈虧平衡分析確定實(shí)現(xiàn)目標(biāo)利潤的銷量課件下載郵箱：密碼：shufemba安全邊際和安全邊際分析(續(xù))在銷售收益增加時(shí)利潤同時(shí)增大，而且利潤的相對(duì)增長率（變化百分比）大于銷售收益的相對(duì)增長率（變化百分比），二者的比值稱為經(jīng)營杠桿系數(shù)利潤相對(duì)變化率大于銷售收益相對(duì)變化率的根本原因在于固定成本的存在經(jīng)營杠桿效應(yīng)有時(shí)對(duì)企業(yè)有利，有時(shí)對(duì)企業(yè)不利安全邊際率的倒數(shù)為經(jīng)營杠桿系數(shù)p安全邊際和安全邊際分析(續(xù))【例3】采用例1的假設(shè)和數(shù)據(jù)，完成下列操作：若目標(biāo)利潤為20000時(shí)，計(jì)算目標(biāo)銷量、盈虧平衡點(diǎn)、安全邊際、安全邊際率；繪制銷量-利潤圖；在圖中添加一個(gè)微調(diào)器控件，當(dāng)目標(biāo)利潤按步長2000從10000變化到40000時(shí)，折角參考線及結(jié)論文字隨之變化。課件下載郵箱：密碼：shufemba安全邊際和安全邊際分析(續(xù))24000-30000-400003000020000100000-100000-2000030060011441200銷量1500利潤

40000實(shí)現(xiàn)目標(biāo)利潤所需目標(biāo)銷量目標(biāo)利潤=24000,目標(biāo)銷量=1144900安全邊際＝444課件下載郵箱：密碼：shufemba成本決策成本決策，按照既定目標(biāo)，在成本預(yù)算的基礎(chǔ)上，挖掘企業(yè)降低成本的潛力，擬定出各種降低成本的可行方案，并對(duì)方案進(jìn)行評(píng)估，選擇最佳方案，使目標(biāo)成本最優(yōu)化的過程成本是提高企業(yè)經(jīng)濟(jì)效益的關(guān)鍵成本決策用于部分零件自制還是外購的決策【例4】某公司制造產(chǎn)品時(shí)需要某種零件，此零件如果自制，單位變動(dòng)成本為4.8元，固定成本共計(jì)30000元，如果向外采購，單件買價(jià)為7元，但可以避免固定成本20000元。若需求量為10000件，建立一個(gè)模型，試分析此公司應(yīng)該選擇“自制”還是“外購”；在圖表上添加一個(gè)微調(diào)器控件，使得需求量按步長500，從1000到34000進(jìn)行變化同時(shí)在圖表上添加一個(gè)微調(diào)器，使得單件買價(jià)按步長0.1，從5.5到7.0進(jìn)行變化。—用文本框反映決策結(jié)論“自制”還是“外購”。成本決策(續(xù))自制與外購兩種方案的決策0500002500002000001500001000000500010000

15000

20000

250003000035000需求量成本自制總成本 外購總成本需求量=10000購買單價(jià)=7決策結(jié)論:最佳方案是自制成本決策(續(xù))成本決策（續(xù)）兩個(gè)決策方案（自制還是外購）的決策是盈虧平衡分析的一種拓展：參數(shù)的變化導(dǎo)致兩個(gè)決策方案的優(yōu)劣的變化，發(fā)生優(yōu)劣交替時(shí)的參數(shù)值就是盈虧平衡點(diǎn)。210C

1

=

F1

+

v

1QC

2

=

F2

+

v

2QC

1

=

C

2v

-

vF

-

F=

2

1Q方案1方案2自制與外購兩種方案的決策0500002500002000001500001000000500010000

15000

20000

250003000035000需求量成本自制總成本 外購總成本需求量=10000購買單價(jià)=7決策結(jié)論:最佳方案是自制成本決策(續(xù))【例5】某公司制造產(chǎn)品時(shí)需要某種零件，此零件如果自制，單位變動(dòng)成本為4.8元，固定成本共計(jì)30000元；如果向外采購，單件買價(jià)為7元，但可以避免固定成本20000元，需求量為25000件。要求：若采購批量（需求量）達(dá)到18000件時(shí)單件買價(jià)可以降低到4.1元，建立一個(gè)公司應(yīng)該選擇“自制”還是“外購”的決策模型；繪制一個(gè)隨需求量變化的反映自制和外購兩種方案的圖表；在圖表上添加一個(gè)微調(diào)器控件，使得需求量按步長1000，從2000到34000進(jìn)行變化；在圖表上添加一個(gè)微調(diào)器控件，使得折扣閾現(xiàn)值按步長200，從15000變化到28000進(jìn)行變化；在圖表上添加一個(gè)微調(diào)器控件，使得達(dá)到折扣閾限的單件買價(jià)按步長0.1，從4.0到7.0進(jìn)行變化；用文本框反映決策結(jié)論“自制”還是“外購”。成本決策(續(xù))價(jià)格優(yōu)惠時(shí)自制與外購兩種方案的決策成本

需求量=25000

采購折扣閾限值=18000達(dá)到折扣閾限的單件買價(jià)=4.1200000決