財(cái)務(wù)軟件論文-excel本量利分析模型

財(cái)務(wù)軟件論文——excel本量利分析模型財(cái)務(wù)軟件論文——excel本量利分析模型/財(cái)務(wù)軟件論文——excel本量利分析模型本量利分析模型設(shè)計(jì)、分析與應(yīng)用本量利分析法是財(cái)務(wù)管理中的一項(xiàng)重要內(nèi)容,主要用于研究產(chǎn)品的成本、業(yè)務(wù)量和利潤(rùn)三者之間變動(dòng)關(guān)系,通過(guò)對(duì)以上各因素的調(diào)整,尋找一條增加利潤(rùn)或?qū)崿F(xiàn)目標(biāo)利潤(rùn)的途徑。本量利的基本方程式非常簡(jiǎn)單,表達(dá)為:利潤(rùn)=銷(xiāo)售收入-總成本=銷(xiāo)售收入-(變動(dòng)成本+固定成本)=(單價(jià)-單位變動(dòng)成本)×銷(xiāo)售量-固定成本在這個(gè)方程式當(dāng)中有5個(gè)相互聯(lián)系的變量(因素):利潤(rùn)、單價(jià)、單位變動(dòng)成本、銷(xiāo)售量、固定成本,只要給定其中任意4個(gè)變量值,便可以求出最后一個(gè)變量的值。運(yùn)用Excel表格設(shè)計(jì)本量利分析模型,基本原理就是依據(jù)由本量利基本方程式建立起來(lái)的5個(gè)變量(或者因素)之間的相互關(guān)系,在Excel表格中定義出函數(shù)后,則既可以對(duì)函數(shù)中的一個(gè)變量(因素)進(jìn)行變動(dòng)分析,也可以對(duì)多個(gè)變量(因素)同時(shí)進(jìn)行變動(dòng)分析,成為本量利分析過(guò)程中的一件利器。所謂excel圖表本量利分析模型其特點(diǎn)是根據(jù)變量之間數(shù)量關(guān)系在excel工作表中建立本量利分析的基本模型。同時(shí)利用excel工作表的圖表顯示功能生成能夠以圖表方式動(dòng)態(tài)地顯示本量利變量關(guān)系的分析模型"。一、本量利分析模型的創(chuàng)建第一步,將相關(guān)資料輸入Excel工作表。在Excel工作表中輸入創(chuàng)建本量利分析基本模型的相關(guān)資料。如圖1圖1第二步,計(jì)算銷(xiāo)售總額、成本總額、利潤(rùn)總額和保本點(diǎn)。（這里根據(jù)表1進(jìn)行計(jì)算）用鼠標(biāo)單擊單元格C7,光標(biāo)變?yōu)椤皘”時(shí)輸入“=C3*C6”,按回車(chē)鍵得到銷(xiāo)售總額。用鼠標(biāo)單擊單元格C8,光標(biāo)變?yōu)椤皘”時(shí)輸入“=C3*C5+C4”,按回車(chē)鍵得到成本總額。用鼠標(biāo)單擊單元格C9,光標(biāo)變?yōu)椤皘”時(shí)輸入“=C7-C8”,按回車(chē)鍵得到銷(xiāo)售總額。用鼠標(biāo)單擊單元格C11,光標(biāo)變?yōu)椤皘”時(shí)輸入“=C4/(C6-C5)”,按回車(chē)鍵得到保本點(diǎn)。然后在C13單元格輸入IF語(yǔ)句“=IF(C9>0,‘利潤(rùn)總額’&ROUND(C9,0),‘虧損額’&-ROUND(C9,0))”如圖2圖2至此,用來(lái)進(jìn)行本量利分析的基本模型已經(jīng)建立完成,利用這個(gè)模型我們就可以根據(jù)案例所給的資料計(jì)算出銷(xiāo)售總額、成本總額、利潤(rùn)總額和保本點(diǎn)等指標(biāo)。同時(shí)利用上述本量利分析模型我們還可以進(jìn)行因素變動(dòng)的假設(shè)分析,即當(dāng)影響利潤(rùn)額的各個(gè)因素單獨(dú)發(fā)生變化或同時(shí)發(fā)生變化時(shí),觀察其對(duì)結(jié)果(利潤(rùn)額)的影響程度。之后可根據(jù)各已知條件完善該表格。第三步：繪制動(dòng)態(tài)圖表本量利分析圖表。點(diǎn)擊菜單欄的“插入”,選擇“圖表”,彈出“圖表向?qū)?步驟之1”選擇框,選擇“XY散點(diǎn)圖”中的子圖“平滑線(xiàn)散點(diǎn)圖”,然后點(diǎn)擊對(duì)話(huà)框的“下一步”按鈕,彈出“圖表向?qū)?步驟之2”選擇框,點(diǎn)擊“系列”標(biāo)簽添加系列。點(diǎn)擊“添加”按鈕,進(jìn)入添加系列對(duì)話(huà)框。點(diǎn)擊“名稱(chēng)(N):”框右側(cè)的紅色箭頭,折疊對(duì)話(huà)框后,用鼠標(biāo)選擇F2單元格,再次單擊折疊對(duì)話(huà)框右側(cè)的紅色箭頭,回到添加系列對(duì)話(huà)框。點(diǎn)擊“X值(X):”框右側(cè)的紅色箭頭,折疊對(duì)話(huà)框,用鼠標(biāo)選擇E4:E13(銷(xiāo)售量)單元格區(qū)域,再次單擊折疊對(duì)話(huà)框右側(cè)的紅色箭頭,回到添加系列對(duì)話(huà)框。接著點(diǎn)擊“Y值(Y):”框右側(cè)的紅色箭頭,折疊對(duì)話(huà)框,用鼠標(biāo)選擇F4:F13單元格區(qū)域,再次單擊折疊對(duì)話(huà)框右側(cè)的紅色箭頭,回到添加系列對(duì)話(huà)框。這樣成本總額線(xiàn)就繪制出來(lái)了。然后再點(diǎn)擊“添加”按鈕繪制銷(xiāo)售總額線(xiàn),用同樣方法我們可以分別繪制出利潤(rùn)總額線(xiàn)、保本點(diǎn)指示線(xiàn)和利潤(rùn)指示線(xiàn)。點(diǎn)擊“下一步”按鈕,彈出“圖表向?qū)?步驟之3”選擇框。在“圖表標(biāo)題(T):”對(duì)話(huà)框輸入“本量利分析圖”,在“數(shù)值(X)軸(A)”對(duì)話(huà)框輸入“業(yè)務(wù)量”,在“數(shù)值(Y)軸(V)”對(duì)話(huà)框輸入“成本利潤(rùn)”后,點(diǎn)擊“下一步”按鈕,彈出“圖表向?qū)?步驟之4”選擇框。選擇“作為新工作表插入(S):”然后點(diǎn)擊“完成”按鈕就得到繪制完成的動(dòng)態(tài)圖表本量利分析圖。如圖（chart1）第四步：為各因素設(shè)置“滾動(dòng)條”按鈕。如圖3圖3在“滾動(dòng)條”按扭上右擊，單擊“設(shè)置控件格式”，即彈出如圖3所示的對(duì)話(huà)框。同時(shí)選擇對(duì)話(huà)框中的“控制”標(biāo)簽頁(yè)，然后在各個(gè)選項(xiàng)框中設(shè)置控制值，并將其單元格鏈接到指定單元，其余值可參照?qǐng)D4設(shè)置。如圖4圖4二、模型的擴(kuò)展擴(kuò)展一：我們可以在該模型的分析結(jié)果區(qū)域增加自己感興趣的一些指標(biāo)，比如：原來(lái)的保本銷(xiāo)售量，目前保本銷(xiāo)售量、邊際貢獻(xiàn)、邊際貢獻(xiàn)率、變動(dòng)成本率等，通過(guò)諸如此類(lèi)的指標(biāo)，更進(jìn)一步的達(dá)到分析的目的。擴(kuò)展二：上面的分析，是以影響利潤(rùn)的各個(gè)因素為己知數(shù)，利潤(rùn)是待求數(shù)。而在企業(yè)中可能會(huì)碰到另一種情況，即利潤(rùn)是已知數(shù)，其他因素是待求數(shù)。我們同樣也可以利用Excel強(qiáng)大的功能來(lái)求解，這里用到的是Excel的“單變量求解”工具。我們以通過(guò)提高單價(jià)來(lái)說(shuō)明。首先，打開(kāi)“工具”菜單，選擇“單變量求解”命令，出現(xiàn)“單變量求解”對(duì)話(huà)框，如圖所示。其次，在“目標(biāo)單元格”中輸入預(yù)計(jì)利潤(rùn)單元地址，同時(shí)在“目標(biāo)值”中輸入該單位的目標(biāo)利潤(rùn)，在“可變單元格”中輸入銷(xiāo)售單價(jià)單元地址。最后，可以單擊“確定”，同時(shí)出現(xiàn)如圖所示對(duì)話(huà)框。同理，可以將單位變動(dòng)成本、銷(xiāo)售數(shù)量、固定成本總額單元地址分別輸入“單變量求解”對(duì)話(huà)框的“可變單元格”，也可以求得目標(biāo)利潤(rùn)的各個(gè)因素的相關(guān)變化。三、本量利模型分析假設(shè)產(chǎn)銷(xiāo)量一致，則有：利潤(rùn)=（單價(jià)-單位變動(dòng)成本）*銷(xiāo)量-固定成本）利用這個(gè)關(guān)系式可以進(jìn)行盈虧臨界點(diǎn)分析、單因素變動(dòng)對(duì)目標(biāo)利潤(rùn)影響分析、多因素變動(dòng)對(duì)目標(biāo)利潤(rùn)影響分析以與各個(gè)因素變化對(duì)利潤(rùn)變化影響的敏感程度分析。1、盈虧臨界點(diǎn)分析。盈虧臨界點(diǎn)亦稱(chēng)保本點(diǎn)或損益平衡點(diǎn)，是指企業(yè)處于不盈利也不虧損狀態(tài)時(shí)的業(yè)務(wù)水平，即指利潤(rùn)為零時(shí)的銷(xiāo)售量或銷(xiāo)售額。盈虧臨界點(diǎn)分析是本量利分析的基礎(chǔ)，企業(yè)在規(guī)劃目標(biāo)利潤(rùn)、控制利潤(rùn)完成情況，估計(jì)經(jīng)營(yíng)風(fēng)險(xiǎn)時(shí)都要利用它。盈虧臨界點(diǎn)的基本計(jì)算模型:銷(xiāo)售單價(jià)×盈虧臨界點(diǎn)銷(xiāo)售量-單位變動(dòng)成本×盈虧臨界點(diǎn)銷(xiāo)售量-固定成本總額=0公式演變后為:盈虧臨界點(diǎn)銷(xiāo)售量=固定成本總額/(銷(xiāo)售單價(jià)-單位變動(dòng)成本)盈虧臨界點(diǎn)銷(xiāo)售額=盈虧臨界點(diǎn)業(yè)務(wù)量×銷(xiāo)售單價(jià)2、因素變動(dòng)分析本量利分析不但可以用于預(yù)測(cè)盈虧臨界點(diǎn),更重要的是還能用來(lái)預(yù)測(cè)利潤(rùn),規(guī)劃最優(yōu)的利潤(rùn)目標(biāo),這就是影響利潤(rùn)的因素變動(dòng)分析。因素變動(dòng)分析的主要方法就是將變化了的因素代入“利潤(rùn)=(銷(xiāo)售單價(jià)-單位變動(dòng)成本)×銷(xiāo)售量-固定成本總額”這個(gè)關(guān)系式,用來(lái)測(cè)定其對(duì)利潤(rùn)的影響。因數(shù)變動(dòng)分析分為單因數(shù)變動(dòng)分析和多因數(shù)變動(dòng)分析,單因素變動(dòng)對(duì)利潤(rùn)結(jié)果的影響,即為實(shí)現(xiàn)目標(biāo)利潤(rùn)所采取的單一措施;多個(gè)因素組合變動(dòng)對(duì)利潤(rùn)結(jié)果的影響,即為實(shí)現(xiàn)目標(biāo)利潤(rùn)所采取的綜合措施。3.、敏感分析本量利關(guān)系的敏感分析，一是研究與分析有關(guān)因素發(fā)生多大變化會(huì)使盈利轉(zhuǎn)為虧損，或虧損轉(zhuǎn)為盈利，二是研究與分析各個(gè)因素變動(dòng)對(duì)利潤(rùn)變化的影響程度。進(jìn)行本量利關(guān)系的敏感分析，有助于增強(qiáng)我們?cè)诮?jīng)營(yíng)管理工作中進(jìn)行有效預(yù)防和控制意識(shí)，針對(duì)情況變化，與時(shí)采取對(duì)策，調(diào)整企業(yè)計(jì)劃，使企業(yè)生產(chǎn)經(jīng)營(yíng)活動(dòng)被控制在最有利的狀態(tài)之下，以保證目標(biāo)利潤(rùn)的實(shí)現(xiàn)。單價(jià)、單位變動(dòng)成本、銷(xiāo)量和固定成本的變化達(dá)到一定程度，會(huì)使利潤(rùn)消失，進(jìn)入盈虧臨界狀態(tài)，導(dǎo)致企業(yè)的經(jīng)營(yíng)狀況發(fā)生質(zhì)變。在本量利分析中，首先必須明確單價(jià)最小值、單位變動(dòng)成本最大值、固定成本最大值以與銷(xiāo)售量最小值。在本量利關(guān)系中，各因數(shù)變化都會(huì)引起利潤(rùn)的變化，但其影響程度各不相同。有的因數(shù)發(fā)生微小變化，就會(huì)使利潤(rùn)發(fā)生很大的變動(dòng)，利潤(rùn)對(duì)這些因數(shù)的變化十分敏感，稱(chēng)這類(lèi)因素為敏感因數(shù)；與此相反，有些參數(shù)發(fā)生變化后，利潤(rùn)的變化不大，反映較為遲鈍，被稱(chēng)之為不敏感因素。反映敏感程度定額指標(biāo)是敏感系數(shù)，其計(jì)算公式為：敏感系數(shù)=目標(biāo)值變動(dòng)百分比/參數(shù)值變動(dòng)百分比其中:敏感系數(shù)為正值的,表明它與利潤(rùn)為同向增減;敏感系數(shù)為負(fù)值的,表明它與利潤(rùn)為反向增減。四、本量利分析模型的應(yīng)用在現(xiàn)實(shí)經(jīng)濟(jì)工作中!盈虧臨界點(diǎn)分析可依賴(lài)人工辦法一次計(jì)算得出!但由于在復(fù)雜的經(jīng)濟(jì)環(huán)境中對(duì)利潤(rùn)的影響往往是多因素的，各因素又是相互關(guān)聯(lián)的。要實(shí)現(xiàn)目標(biāo)利潤(rùn)大多需要采取綜合措施!這就要進(jìn)行綜合調(diào)整和反復(fù)計(jì)算，依賴(lài)人工就非常困難，這就嚴(yán)重影響了本量利分析廣泛和深入的應(yīng)用。在計(jì)算機(jī)環(huán)境中手工的這一困惑得到了徹底的解決。通過(guò)excel的公式設(shè)計(jì)并利用其數(shù)據(jù)鏈接能力，建立具有動(dòng)態(tài)計(jì)算能力的本量利分析模型，只需用鼠標(biāo)單擊微調(diào)按扭來(lái)調(diào)整各個(gè)因素的值，就可以非常方便地進(jìn)行單因素和多因素的分析。參考文獻(xiàn):[1]首都經(jīng)濟(jì)貿(mào)易大學(xué)會(huì)計(jì)學(xué)院，北京馬