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

EXCEL表在利潤(rùn)管理中的應(yīng)用目錄Contents0102目標(biāo)利潤(rùn)分析模型03

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

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

本量利基本模型

（一）本量利分析

本量利分析，即成本—數(shù)量—利潤(rùn)分析，是通過(guò)數(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ì)算過(guò)程與結(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)能力。一般來(lái)說(shuō)，盈虧平衡點(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ù)的本-量-利圖形，通過(guò)滾動(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ì)算過(guò)程與結(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ù)的本-量-利圖形，通過(guò)滾動(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)品，該公司原來(lái)的目標(biāo)利潤(rùn)為100000元，在此目標(biāo)利潤(rùn)下，銷售單價(jià)為12.5元，銷售數(shù)量為60000，固定成本200000元．單位變動(dòng)成本7.5元?，F(xiàn)在假定公司將目標(biāo)利潤(rùn)為150000元，問(wèn)：從單個(gè)因素來(lái)看，影響目標(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)品，該公司原來(lái)的目標(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%）。通過(guò)滾動(dòng)條按鈕動(dòng)態(tài)反映出各因素變化對(duì)企業(yè)利潤(rùn)的變化情況及“盈利”、“虧損”、“保本點(diǎn)”的決策信息

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

敏感分析模型

企業(yè)一定時(shí)期的利潤(rùn)受到銷售量、單價(jià)、單位變動(dòng)成本和固定成本4個(gè)因素的影響。為了分析各種因素的變化對(duì)利潤(rùn)指標(biāo)的影響可以通過(guò)建立利潤(rùn)敏感性分析模型來(lái)對(duì)利潤(rùn)變化的情況進(jìn)行動(dòng)態(tài)分析。

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

敏感系數(shù)=利潤(rùn)變動(dòng)率/某因素的變動(dòng)率

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

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

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

方案管理器

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

例題：某企業(yè)有三種商品分別為商品1、商品2、商品3，資料如下：?jiǎn)挝蛔儎?dòng)成本人力成本120運(yùn)輸成本70商品1商品2商品3商品成本100012001500商品產(chǎn)量100121144銷售價(jià)300025002800

請(qǐng)使用方案管理器，管理上述方案，方案命名“方案一”、“方案二”、“方案三”。人力成本：120；運(yùn)輸成本70：

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

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

實(shí)訓(xùn)2、某