excel與財務分析

1、第十章 EXCEL財務分析,會計電算化課程組2008.08,第十章 EXCEL與財務分析,,10.1 EXCEL與財務預測10.2 EXCEL與債券估價10.3 EXCEL與存貨管理10.4 EXCEL與短期籌資決策10.5 EXCEL與最佳資本結構10.6 EXCEL與量本利分析10.7 EXCEL與設備投資決策10.8 EXCEL與人力資源管理,基礎知識：如何使用控件,例1：某人準備購買一套住房，

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

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

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

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

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

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

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

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

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

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

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

13、,,類似的問題,債券購買信用條件與企業(yè)利潤信用條件與銷售收入,,9.5 EXCEL與最佳資本結構,一、最佳資本結構企業(yè)各種長期資金籌資來源的構成和比例關系。最佳資本結構應當是可使公司的總價值最大，資本成本最低的結構。二、最佳資本結構模型公司市場總價值等于其股票的總價值與債券的總價值之和，V=B+S假設債券價值等于其面值，則股票的市場價格為：S=（EBIT-I）*(1-T)/Ks,,9.5 EXCEL與最佳資本結構,二、

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

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

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

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

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

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

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

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

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

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

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