excel與財(cái)務(wù)分析

1、第十章 EXCEL財(cái)務(wù)分析,會(huì)計(jì)電算化課程組2008.08,第十章 EXCEL與財(cái)務(wù)分析,,10.1 EXCEL與財(cái)務(wù)預(yù)測(cè)10.2 EXCEL與債券估價(jià)10.3 EXCEL與存貨管理10.4 EXCEL與短期籌資決策10.5 EXCEL與最佳資本結(jié)構(gòu)10.6 EXCEL與量本利分析10.7 EXCEL與設(shè)備投資決策10.8 EXCEL與人力資源管理,基礎(chǔ)知識(shí)：如何使用控件,例1：某人準(zhǔn)備購(gòu)買(mǎi)一套住房，

2、現(xiàn)有若干面積、樓層和位置的商品房可供選擇。購(gòu)車(chē)時(shí)允許分期付款，但必須支付不少于一定比例（如3成）的首付，其余部分可在15年的時(shí)間內(nèi)分月付清，年利率為9.5%，但有可能在8-11%范圍內(nèi)變化。試構(gòu)建一個(gè)EXCEL模型來(lái)計(jì)算采用各種購(gòu)房方案時(shí)的月付款額以便可以綜合考慮各種因素從而根據(jù)自己的承受能力決定一種最合適的購(gòu)房方案。,,基礎(chǔ)知識(shí)：如何使用控件,一個(gè)簡(jiǎn)單的例子建立方案列表建立手工輸入模型使用控件EXCEL中的財(cái)務(wù)函數(shù),,基礎(chǔ)知

3、識(shí)：如何使用控件,一個(gè)簡(jiǎn)單的例子建立方案列表建立手工輸入模型使用控件EXCEL中的財(cái)務(wù)函數(shù)(例子),,9.1 EXCEL與財(cái)務(wù)預(yù)測(cè),一、財(cái)務(wù)預(yù)測(cè)的目的預(yù)測(cè)企業(yè)未來(lái)的融資需求預(yù)測(cè)不準(zhǔn)的損失二、財(cái)務(wù)預(yù)測(cè)的步驟銷(xiāo)售預(yù)測(cè)資產(chǎn)總量預(yù)測(cè)收入、費(fèi)用與盈余預(yù)測(cè)融資預(yù)測(cè),,9.1 EXCEL與財(cái)務(wù)預(yù)測(cè),三、財(cái)務(wù)預(yù)測(cè)的方法*銷(xiāo)售百分比法：總額預(yù)測(cè)，增加額預(yù)測(cè)回歸分析法現(xiàn)金預(yù)算法四、EXCEL財(cái)務(wù)預(yù)測(cè)公司下年預(yù)計(jì)銷(xiāo)售收入

4、3000萬(wàn)元，上年銷(xiāo)售額2500萬(wàn)元，凈利150萬(wàn)元，股利支付率為30%，根據(jù)公司本年資產(chǎn)負(fù)債表進(jìn)行財(cái)務(wù)預(yù)測(cè)，確定融資需求,,EXCEL財(cái)務(wù)預(yù)測(cè),1、建立融資需求表2、確定銷(xiāo)售百分比3、計(jì)算預(yù)計(jì)銷(xiāo)售額下的預(yù)計(jì)資產(chǎn)負(fù)債額4、預(yù)計(jì)留存收益增加額和預(yù)計(jì)留存收益額預(yù)計(jì)留存收益=上年留存收益+預(yù)計(jì)新增收益新增留存收益=預(yù)計(jì)銷(xiāo)售額*計(jì)劃銷(xiāo)售凈利（本年）*（1-股利支付率）融資需求=預(yù)計(jì)總資產(chǎn)-預(yù)計(jì)總負(fù)債-預(yù)計(jì)權(quán)益5、計(jì)算外部融資需求,

5、,EXCEL財(cái)務(wù)預(yù)測(cè),,EXCEL財(cái)務(wù)預(yù)測(cè),類(lèi)似的問(wèn)題銷(xiāo)售預(yù)測(cè)日常現(xiàn)金需求量預(yù)測(cè)成本費(fèi)用預(yù)測(cè)利潤(rùn)預(yù)測(cè)……,,9.2 EXCEL與債券估價(jià),一、財(cái)務(wù)估價(jià)與債券估價(jià)估價(jià)是財(cái)務(wù)管理的核心用適當(dāng)?shù)恼郜F(xiàn)率計(jì)算的資產(chǎn)預(yù)期未來(lái)現(xiàn)金流量的現(xiàn)值債券估價(jià)：債券到期日所支付的款項(xiàng)的現(xiàn)值，取決于當(dāng)前利率和現(xiàn)金流量的風(fēng)險(xiǎn)水平二、債券估價(jià)的基本模型債券價(jià)值:PV利息:I到期本金:M貼現(xiàn)率:i到期前年數(shù)：n,,9.2 EXCEL與債券

6、估價(jià),三、債券估價(jià)的方法計(jì)算債券利息現(xiàn)值和調(diào)整相關(guān)數(shù)據(jù)動(dòng)態(tài)計(jì)算四、EXCEL財(cái)務(wù)預(yù)測(cè)公司擬于2008年2月1日發(fā)行面額為1000元的債券，票面利率8%，每年2月1日計(jì)算并支付一次利息，并于5年后的1月31日到期。同等風(fēng)險(xiǎn)投資的必要報(bào)酬率為10%，試計(jì)算債券的價(jià)值。,,9.2 EXCEL與債券估價(jià),1、建立債券估價(jià)表2、計(jì)算債券利息現(xiàn)值3、調(diào)整相關(guān)數(shù)據(jù)4、債券價(jià)值動(dòng)態(tài)計(jì)算,,債券估價(jià),,EXCEL與估價(jià),類(lèi)似的問(wèn)題購(gòu)房

7、的分期付款問(wèn)題存款的復(fù)利問(wèn)題年金的現(xiàn)值問(wèn)題貸款利率中途調(diào)整問(wèn)題國(guó)庫(kù)券的投資計(jì)算……,,9.3 EXCEL與存貨管理,一、存貨管理的目標(biāo)保證生產(chǎn)和銷(xiāo)售和正常供應(yīng)總成本最低：取得成本與儲(chǔ)存成本最低二、存貨成本取得成本=訂貨成本+購(gòu)置成本訂貨成本=固定成本F1(次數(shù)無(wú)關(guān))+變動(dòng)成本K（次數(shù)相關(guān)）購(gòu)置成本=年需要量D*單價(jià)U若每次訂貨量為Q，則取得成本為：F1+D/Q*K+D*U,,9.3 EXCEL與存貨管理,二、存

8、貨成本儲(chǔ)存成本：為保存存貨而發(fā)生的費(fèi)用，也可分為固定成本和變動(dòng)成本。固定成本與數(shù)量無(wú)關(guān)，用F2表示變動(dòng)成本與數(shù)量有關(guān)，單位成本用Kc表示，則存貨成本為：F2+Kc*Q/2其中Q/2表示本年的平均存貨,,9.3 EXCEL與存貨管理,三、經(jīng)濟(jì)訂貨批量存貨總成本： TC= F1+D/Q*K+D*U+F2+Kc*Q/2當(dāng)F1、K、D、F2、Kc為常數(shù)時(shí)，TC的大小取決于Q，求導(dǎo)可得經(jīng)濟(jì)訂貨批量：Q=sqrt(2*K*D/Kc)

9、Sqrt()為開(kāi)平方函數(shù),,9.3 EXCEL與存貨管理,三、經(jīng)濟(jì)訂貨批量經(jīng)濟(jì)訂貨批量：Q=sqrt(2*K*D/Kc)年最佳訂貨次數(shù)N=D/Q=sqrt(D*Kc/2*K)最佳訂貨周期：T=1/N=1/sqrt(D*Kc/2*K)批量相關(guān)總成本TC（Q）=SQRT（2*K*D*Kc）經(jīng)濟(jì)訂貨量占用資金I=Q/2*U=sqrt(D*K/2*Kc)*U,,EXCEL經(jīng)濟(jì)訂貨批量模型,1、建立經(jīng)濟(jì)訂貨批量模型表2、

10、計(jì)算最佳訂貨批量3、計(jì)算其它參數(shù)4、模型的進(jìn)一步擴(kuò)展,,EXCEL經(jīng)濟(jì)訂貨批量模型,類(lèi)似的問(wèn)題固定成本、可變成本總成本最小問(wèn)題量本利固定需求情況下2種商品的最優(yōu)訂貨策略固定訂貨周期庫(kù)存管理策略固定訂貨數(shù)量庫(kù)存管理策略,,經(jīng)濟(jì)訂貨批量,,9.4 EXCEL與短期籌資,一、短期籌資的特點(diǎn)資金可使用時(shí)間較短，一般不超過(guò)一年籌資速度快，容易取得，成本低，風(fēng)險(xiǎn)高二、短期籌資的主要形式商業(yè)信用：應(yīng)付賬款、應(yīng)付票據(jù)、預(yù)收賬款

11、應(yīng)付賬款做為企業(yè)最常用的短期籌資工具，如何建立模型進(jìn)行管理其余的方法類(lèi)似,,9.4 EXCEL與短期籌資,三、應(yīng)付賬款的決策模型免費(fèi)信用：規(guī)定折扣期內(nèi)享受折扣有代價(jià)信用：放棄折扣獲得的信用展期信用：推遲付款獲得的信用決策依據(jù)：權(quán)衡放棄現(xiàn)金折扣的成本現(xiàn)金折扣成本=折扣百分比/(1-折扣百分比）*一年的天數(shù)/（信用期-折扣期）,,9.4 EXCEL與短期籌資,四、EXCEL模型的構(gòu)建公司按2/10,N/30的條件購(gòu)入貨物5

12、00萬(wàn)元，如果企業(yè)在10天內(nèi)付賬，則享受10天的免費(fèi)信用期，并獲得2%的折扣，若企業(yè)放棄折扣，在10天后（30天以?xún)?nèi)）付款，企業(yè)便要承擔(dān)因放棄折扣而造成的隱含利息成本，試運(yùn)用EXCEL進(jìn)行付賬決策分析。,,9.4 EXCEL與短期籌資,五、EXCEL模型的步驟1、創(chuàng)建應(yīng)付賬款成本決策分析已知數(shù)據(jù)表2、根據(jù)應(yīng)付賬款成本決策模型計(jì)算放棄折扣成本現(xiàn)金折扣成本=折扣百分比/(1-折扣百分比*一年的天數(shù)/（信用期-折扣期）,,短期籌資決策

13、,,類(lèi)似的問(wèn)題,債券購(gòu)買(mǎi)信用條件與企業(yè)利潤(rùn)信用條件與銷(xiāo)售收入,,9.5 EXCEL與最佳資本結(jié)構(gòu),一、最佳資本結(jié)構(gòu)企業(yè)各種長(zhǎng)期資金籌資來(lái)源的構(gòu)成和比例關(guān)系。最佳資本結(jié)構(gòu)應(yīng)當(dāng)是可使公司的總價(jià)值最大，資本成本最低的結(jié)構(gòu)。二、最佳資本結(jié)構(gòu)模型公司市場(chǎng)總價(jià)值等于其股票的總價(jià)值與債券的總價(jià)值之和，V=B+S假設(shè)債券價(jià)值等于其面值，則股票的市場(chǎng)價(jià)格為：S=（EBIT-I）*(1-T)/Ks,,9.5 EXCEL與最佳資本結(jié)構(gòu),二、

14、最佳資本結(jié)構(gòu)模型S=（EBIT-I）*(1-T)/KsKs=Rs=Rf+b(Rm-Rf)EBIT：息稅前利潤(rùn)I：年利息額T：所得稅率Ks:權(quán)益資本成本Rf:無(wú)風(fēng)險(xiǎn)報(bào)酬b:股票貝塔系數(shù)Rm:必要報(bào)酬率,公司資本成本用加權(quán)平均資本Kw表示：Kw=Kb(B/S)(1-T)+Ks(S/V),,9.5 EXCEL與最佳資本結(jié)構(gòu),三、EXCEL模型的構(gòu)建公司年息稅前利潤(rùn)500萬(wàn)元，資金全部由普通股資本構(gòu)成，股票賬面價(jià)值200

15、萬(wàn)元，所得稅率33%。該公司認(rèn)為目前的資本結(jié)構(gòu)不太合理，擬發(fā)行債券回購(gòu)部分股票，經(jīng)調(diào)查，目前的債券等量資本成本和權(quán)益成本情況如下表：,,9.5 EXCEL與最佳資本結(jié)構(gòu),三、EXCEL模型的構(gòu)建1、建立已知條件表2、計(jì)算股票資本成本Ks3、建立公司價(jià)值與資本成本表4、計(jì)算發(fā)行不同債券B下的市場(chǎng)價(jià)值S與公司價(jià)值V，依據(jù)Kb與Ks，計(jì)算Kw5、從Kw值中選擇最低值，對(duì)應(yīng)的B為最佳,,短期籌資決策,,類(lèi)似的問(wèn)題,股票發(fā)行價(jià)格的計(jì)算

16、財(cái)務(wù)杠桿資本成本經(jīng)營(yíng)杠桿每股盈余……,,9.6 EXCEL與量本利分析,一、量本利分析概述成本有不同的形態(tài)：固定成本、變動(dòng)成本、總成本。加入收入和利潤(rùn)，就可以構(gòu)建量本利數(shù)學(xué)模型總成本=變動(dòng)成本+固定成本=單位變動(dòng)成本*銷(xiāo)量+固定成本銷(xiāo)售收入=單價(jià)*銷(xiāo)量利潤(rùn)=銷(xiāo)售收入-總成本=（單價(jià)-單位變動(dòng)成本）*銷(xiāo)量-固定成本保本量=固定成本/(單價(jià)-單位變動(dòng)成本)保利量=（固定成本+利潤(rùn)） /(單價(jià)-單位變動(dòng)成本)單位變

17、動(dòng)成本=單價(jià)-（固定成本+利潤(rùn)）/銷(xiāo)量固定成本=（單價(jià)-單位變動(dòng)成本）*銷(xiāo)量-利潤(rùn),,9.6 EXCEL與量本利分析,二、EXCEL模型的構(gòu)建公司月固定成本10000元，單價(jià)50元，單位變動(dòng)成本30元，試用EXCEL構(gòu)建量本利模型。1、建立已知數(shù)據(jù)表格2、計(jì)算保本量、保利量3、單變量求解保本量、保利量下的單價(jià)、變動(dòng)成本與固定成本4、制作圖形,,EXCEL與量本利分析,,類(lèi)似的問(wèn)題,產(chǎn)量確定情況下的定價(jià)變動(dòng)成本與利潤(rùn)固定成

18、本與利潤(rùn)銷(xiāo)售單價(jià)確定情況下的保利量計(jì)算成本性態(tài)分析……,,9.7 EXCEL與設(shè)備投資決策,一、設(shè)備投資決策概述設(shè)備購(gòu)置項(xiàng)目評(píng)價(jià)多個(gè)購(gòu)置項(xiàng)目的比較設(shè)備的租買(mǎi)比較,,9.7 EXCEL與設(shè)備投資決策,二、EXCEL模型的構(gòu)建公司擬增加一臺(tái)機(jī)器，用以生產(chǎn)一種 新的產(chǎn)品，有三種設(shè)備可供選擇，每種 設(shè)備原始資料如表9-3，用EXCEL建模分析在只考慮凈現(xiàn)值時(shí)如何決策。1、建立已知數(shù)據(jù)表格2、計(jì)算三種設(shè)備的市場(chǎng)收益3、計(jì)算

19、三種設(shè)備投資的凈現(xiàn)值4、動(dòng)態(tài)決策4、制作圖形,,9.7 EXCEL與設(shè)備投資決策,,類(lèi)似的問(wèn)題,項(xiàng)目投資評(píng)價(jià)設(shè)備租、購(gòu)比較不同設(shè)備購(gòu)買(mǎi)比較……,,9.8 EXCEL與人力資源管理和優(yōu)化,一、人力資源管理概述為完成即定生產(chǎn)計(jì)劃，將勞動(dòng)力合理分配到生產(chǎn)的各個(gè)環(huán)節(jié)上。使用EXCEL的線性規(guī)劃功能，解決約束條件下的最大化問(wèn)題。二、一個(gè)實(shí)例公司生產(chǎn)2種產(chǎn)品：產(chǎn)品1和產(chǎn)品2，每件產(chǎn)品分別可獲利潤(rùn)100元和200元，生產(chǎn)2種產(chǎn)品

20、均需要3道工序，分別由部門(mén)1、2、3完成，其中每道工序所需人數(shù)和每個(gè)部門(mén)可提供的人數(shù)如下表。問(wèn)該廠應(yīng)生產(chǎn)多少產(chǎn)品1和產(chǎn)品2才能在滿足各部門(mén)人力資源的約束條件下獲得最大利潤(rùn)？,,9.8 EXCEL與人力資源管理和優(yōu)化,1、人力資源模型構(gòu)建設(shè)定決策變量：2種產(chǎn)品的產(chǎn)量，x1,x2構(gòu)建目標(biāo)利潤(rùn)函數(shù)：獲得最大利潤(rùn)：MAX Y=100x1+200x2構(gòu)建約束條件：各部門(mén)人力資源約束0.6x1+1x2=0,int,,9.8 EXCEL

21、與人力資源管理和優(yōu)化,2、EXCEL建模產(chǎn)品與工序人力資源對(duì)照表三部門(mén)人力資源約束表人力資源優(yōu)化配置表3、規(guī)劃求解規(guī)劃求解參數(shù)設(shè)置4、模型求解結(jié)果分析獲利最大的X1與X2提高效率的瓶頸工種轉(zhuǎn)換教育與培訓(xùn),,9.8 EXCEL與人力資源管理和優(yōu)化,5、人力資源優(yōu)化模型添加決策變量：Xij—從i部門(mén)轉(zhuǎn)到j(luò)部門(mén)的職工數(shù),建立部門(mén)間人員調(diào)配表構(gòu)建優(yōu)化目標(biāo)函數(shù)：考慮培訓(xùn)費(fèi)的利潤(rùn)Max y=100x1+200x2-50*

22、(x12+x13+x21+x23+x31+x32)構(gòu)建優(yōu)化約束條件：考慮人工轉(zhuǎn)移0.6x1+1x2=0,x13,x21,x23,x31,x32>=0,int,,EXCEL中的線性規(guī)劃求解,,EXCEL中的線性規(guī)劃求解,,EXCEL中的線性規(guī)劃求解,,類(lèi)似的問(wèn)題,約束條件下的極大極小值……,,第三次作業(yè)要求,1、選擇一個(gè)會(huì)計(jì)相關(guān)的項(xiàng)目，在EXCEL中建立模型；如：量本利分析、投資項(xiàng)目評(píng)價(jià)、杜邦分析、購(gòu)房的分期付款問(wèn)題存款的

23、復(fù)利問(wèn)題、年金的現(xiàn)值問(wèn)題、貸款利率中途調(diào)整問(wèn)題、國(guó)庫(kù)券的投資計(jì)算、債券購(gòu)買(mǎi)、。。。。。。要求能根據(jù)已知數(shù)據(jù)，按該方法的計(jì)算要求自動(dòng)計(jì)算本關(guān)的指標(biāo)2、界面美觀，自動(dòng)化程度高；3、使用下拉列表框、微調(diào)框等控件4、如果需要請(qǐng)使用可調(diào)圖形5、制作完成后發(fā)到我的郵箱：westone@shzu.edu.cn,文件命名和郵箱主題按發(fā)送報(bào)表時(shí)的方式。6、21周周三為最后期限。,,第三次作業(yè)要求,例：用excel的規(guī)劃求解計(jì)算的求最佳方案

24、的題目 一個(gè)廠家，在一段時(shí)間內(nèi)生產(chǎn)兩種產(chǎn)品，分別是A，B產(chǎn)品。受原材料的限制，產(chǎn)品A的產(chǎn)量不超過(guò)4000元，產(chǎn)品B的產(chǎn)量不超過(guò)4000元。受機(jī)器和人的限制，兩種產(chǎn)品之和不超過(guò)7000元。產(chǎn)品A的單價(jià)利潤(rùn)為166元，每多生產(chǎn)100件，單價(jià)利潤(rùn)增加2元，產(chǎn)品B的單價(jià)利潤(rùn)為190元，每多生產(chǎn)100件，單價(jià)利潤(rùn)增加3元。現(xiàn)在進(jìn)行規(guī)劃求解，求出產(chǎn)品A和產(chǎn)品B的產(chǎn)量的最佳方案，使利潤(rùn)最大.,,The End… Thanks…Keep in c