EXCEL與財務(wù)分析課件

EXCEL財務(wù)分析

基礎(chǔ)知識：如何使用控件例1：某人準(zhǔn)備購買一套住房，現(xiàn)有若干面積、樓層和位置的商品房可供選擇。購車時允許分期付款，但必須支付不少於一定比例（如3成）的首付，其餘部分可在15年的時間內(nèi)分月付清，年利率為9.5%，但有可能在8-11%範(fàn)圍內(nèi)變化。試構(gòu)建一個EXCEL模型來計算採用各種購房方案時的月付款額以便可以綜合考慮各種因素從而根據(jù)自己的承受能力決定一種最合適的購房方案?；A(chǔ)知識：如何使用控件一個簡單的例子建立方案列表建立手工輸入模型使用控件EXCEL中的財務(wù)函數(shù)基礎(chǔ)知識：如何使用控件一個簡單的例子建立方案列表建立手工輸入模型使用控件EXCEL中的財務(wù)函數(shù)(例子)9.1EXCEL與財務(wù)預(yù)測一、財務(wù)預(yù)測的目的預(yù)測企業(yè)未來的融資需求預(yù)測不準(zhǔn)的損失二、財務(wù)預(yù)測的步驟銷售預(yù)測資產(chǎn)總量預(yù)測收入、費用與盈餘預(yù)測融資預(yù)測9.1EXCEL與財務(wù)預(yù)測三、財務(wù)預(yù)測的方法*銷售百分比法：總額預(yù)測，增加額預(yù)測回歸分析法現(xiàn)金預(yù)算法四、EXCEL財務(wù)預(yù)測公司下年預(yù)計銷售收入3000萬元，上年銷售額2500萬元，淨(jìng)利150萬元，股利支付率為30%，根據(jù)公司本年資產(chǎn)負(fù)債表進(jìn)行財務(wù)預(yù)測，確定融資需求EXCEL財務(wù)預(yù)測1、建立融資需求表2、確定銷售百分比3、計算預(yù)計銷售額下的預(yù)計資產(chǎn)負(fù)債額4、預(yù)計留存收益增加額和預(yù)計留存收益額預(yù)計留存收益=上年留存收益+預(yù)計新增收益新增留存收益=預(yù)計銷售額*計畫銷售淨(jìng)利（本年）*（1-股利支付率）融資需求=預(yù)計總資產(chǎn)-預(yù)計總負(fù)債-預(yù)計權(quán)益5、計算外部融資需求EXCEL財務(wù)預(yù)測EXCEL財務(wù)預(yù)測類似的問題銷售預(yù)測日?，F(xiàn)金需求量預(yù)測成本費用預(yù)測利潤預(yù)測……9.2EXCEL與債券估價一、財務(wù)估價與債券估價估價是財務(wù)管理的核心用適當(dāng)?shù)恼郜F(xiàn)率計算的資產(chǎn)預(yù)期未來現(xiàn)金流量的現(xiàn)值債券估價：債券到期日所支付的款項的現(xiàn)值，取決於當(dāng)前利率和現(xiàn)金流量的風(fēng)險水準(zhǔn)二、債券估價的基本模型債券價值:PV利息:I到期本金:M貼現(xiàn)率:i到期前年數(shù)：n9.2EXCEL與債券估價三、債券估價的方法計算債券利息現(xiàn)值和調(diào)整相關(guān)數(shù)據(jù)動態(tài)計算四、EXCEL財務(wù)預(yù)測公司擬於2008年2月1日發(fā)行面額為1000元的債券，票面利率8%，每年2月1日計算並支付一次利息，並於5年後的1月31日到期。同等風(fēng)險投資的必要報酬率為10%，試計算債券的價值。9.2EXCEL與債券估價1、建立債券估價表2、計算債券利息現(xiàn)值3、調(diào)整相關(guān)數(shù)據(jù)4、債券價值動態(tài)計算債券估價EXCEL與估價類似的問題購房的分期付款問題存款的複利問題年金的現(xiàn)值問題貸款利率中途調(diào)整問題國庫券的投資計算……9.3EXCEL與存貨管理一、存貨管理的目標(biāo)保證生產(chǎn)和銷售和正常供應(yīng)總成本最低：取得成本與儲存成本最低二、存貨成本取得成本=訂貨成本+購置成本訂貨成本=固定成本F1(次數(shù)無關(guān))+變動成本K（次數(shù)相關(guān)）購置成本=年需要量D*單價U若每次訂貨量為Q，則取得成本為：F1+D/Q*K+D*U9.3EXCEL與存貨管理二、存貨成本儲存成本：為保存存貨而發(fā)生的費用，也可分為固定成本和變動成本。固定成本與數(shù)量無關(guān)，用F2表示變動成本與數(shù)量有關(guān)，單位成本用Kc表示，則存貨成本為：F2+Kc*Q/2其中Q/2表示本年的平均存貨9.3EXCEL與存貨管理三、經(jīng)濟(jì)訂貨批量存貨總成本：

TC=

F1+D/Q*K+D*U+F2+Kc*Q/2當(dāng)F1、K、D、F2、Kc為常數(shù)時，TC的大小取決於Q，求導(dǎo)可得經(jīng)濟(jì)訂貨批量：Q=sqrt(2*K*D/Kc)Sqrt()為開平方函數(shù)9.3EXCEL與存貨管理三、經(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)*UEXCEL經(jīng)濟(jì)訂貨批量模型1、建立經(jīng)濟(jì)訂貨批量模型表2、計算最佳訂貨批量3、計算其他參數(shù)4、模型的進(jìn)一步擴(kuò)展EXCEL經(jīng)濟(jì)訂貨批量模型類似的問題固定成本、可變成本總成本最小問題量本利固定需求情況下2種商品的最優(yōu)訂貨策略固定訂貨週期庫存管理策略固定訂貨數(shù)量庫存管理策略經(jīng)濟(jì)訂貨批量9.4EXCEL與短期籌資一、短期籌資的特點資金可使用時間較短，一般不超過一年籌資速度快，容易取得，成本低，風(fēng)險高二、短期籌資的主要形式商業(yè)信用：應(yīng)付賬款、應(yīng)付票據(jù)、預(yù)收賬款應(yīng)付賬款做為企業(yè)最常用的短期籌資工具，如何建立模型進(jìn)行管理其餘的方法類似9.4EXCEL與短期籌資三、應(yīng)付賬款的決策模型免費信用：規(guī)定折扣期內(nèi)享受折扣有代價信用：放棄折扣獲得的信用展期信用：推遲付款獲得的信用決策依據(jù)：權(quán)衡放棄現(xiàn)金折扣的成本現(xiàn)金折扣成本=折扣百分比/(1-折扣百分比）*一年的天數(shù)/（信用期-折扣期）9.4EXCEL與短期籌資四、EXCEL模型的構(gòu)建公司按2/10,N/30的條件購入貨物500萬元，如果企業(yè)在10天內(nèi)付賬，則享受10天的免費信用期，並獲得2%的折扣，若企業(yè)放棄折扣，在10天後（30天以內(nèi)）付款，企業(yè)便要承擔(dān)因放棄折扣而造成的隱含利息成本，試運(yùn)用EXCEL進(jìn)行付賬決策分析。9.4EXCEL與短期籌資五、EXCEL模型的步驟1、創(chuàng)建應(yīng)付賬款成本決策分析已知數(shù)據(jù)表2、根據(jù)應(yīng)付賬款成本決策模型計算放棄折扣成本現(xiàn)金折扣成本=折扣百分比/(1-折扣百分比*一年的天數(shù)/（信用期-折扣期）短期籌資決策類似的問題債券購買信用條件與企業(yè)利潤信用條件與銷售收入9.5EXCEL與最佳資本結(jié)構(gòu)一、最佳資本結(jié)構(gòu)企業(yè)各種長期資金籌資來源的構(gòu)成和比例關(guān)係。最佳資本結(jié)構(gòu)應(yīng)當(dāng)是可使公司的總價值最大，資本成本最低的結(jié)構(gòu)。二、最佳資本結(jié)構(gòu)模型公司市場總價值等於其股票的總價值與債券的總價值之和，V=B+S假設(shè)債券價值等於其面值，則股票的市場價格為：S=（EBIT-I）*(1-T)/Ks9.5EXCEL與最佳資本結(jié)構(gòu)二、最佳資本結(jié)構(gòu)模型S=（EBIT-I）*(1-T)/KsKs=Rs=Rf+b(Rm-Rf)EBIT：息稅前利潤I：年利息額T：所得稅率Ks:權(quán)益資本成本Rf:無風(fēng)險報酬b:股票貝塔係數(shù)Rm:必要報酬率公司資本成本用加權(quán)平均資本Kw表示：Kw=Kb(B/S)(1-T)+Ks(S/V)9.5EXCEL與最佳資本結(jié)構(gòu)三、EXCEL模型的構(gòu)建公司年息稅前利潤500萬元，資金全部由普通股資本構(gòu)成，股票帳面價值200萬元，所得稅率33%。該公司認(rèn)為目前的資本結(jié)構(gòu)不太合理，擬發(fā)行債券回購部分股票，經(jīng)調(diào)查，目前的債券等量資本成本和權(quán)益成本情況如下表：9.5EXCEL與最佳資本結(jié)構(gòu)三、EXCEL模型的構(gòu)建1、建立已知條件表2、計算股票資本成本Ks3、建立公司價值與資本成本表4、計算發(fā)行不同債券B下的市場價值S與公司價值V，依據(jù)Kb與Ks，計算Kw5、從Kw值中選擇最低值，對應(yīng)的B為最佳短期籌資決策類似的問題股票發(fā)行價格的計算財務(wù)杠桿資本成本經(jīng)營杠桿每股盈餘……9.6EXCEL與量本利分析一、量本利分析概述成本有不同的形態(tài)：固定成本、變動成本、總成本。加入收入和利潤，就可以構(gòu)建量本利數(shù)學(xué)模型總成本=變動成本+固定成本=單位變動成本*銷量+固定成本銷售收入=單價*銷量利潤=銷售收入-總成本=（單價-單位變動成本）*銷量-固定成本保本量=固定成本/(單價-單位變動成本)保利量=（固定成本+利潤）/(單價-單位變動成本)單位變動成本=單價-（固定成本+利潤）/銷量固定成本=（單價-單位變動成本）*銷量-利潤9.6EXCEL與量本利分析二、EXCEL模型的構(gòu)建公司月固定成本10000元，單價50元，單位變動成本30元，試用EXCEL構(gòu)建量本利模型。1、建立已知數(shù)據(jù)表格2、計算保本量、保利量3、單變數(shù)求解保本量、保利量下的單價、變動成本與固定成本4、製作圖形EXCEL與量本利分析類似的問題產(chǎn)量確定情況下的定價變動成本與利潤固定成本與利潤銷售單價確定情況下的保利量計算成本性態(tài)分析……9.7EXCEL與設(shè)備投資決策一、設(shè)備投資決策概述設(shè)備購置專案評價多個購置專案的比較設(shè)備的租買比較9.7EXCEL與設(shè)備投資決策二、EXCEL模型的構(gòu)建公司擬增加一臺機(jī)器，用以生產(chǎn)一種新的產(chǎn)品，有三種設(shè)備可供選擇，每種設(shè)備原始資料如表9-3，用EXCEL建模分析在只考慮淨(jìng)現(xiàn)值時如何決策。1、建立已知數(shù)據(jù)表格2、計算三種設(shè)備的市場收益3、計算三種設(shè)備投資的淨(jìng)現(xiàn)值4、動態(tài)決策4、製作圖形9.7EXCEL與設(shè)備投資決策類似的問題專案投資評價設(shè)備租、購比較不同設(shè)備購買比較……9.8EXCEL與人力資源管理和優(yōu)化一、人力資源管理概述為完成即定生產(chǎn)計畫，將勞動力合理分配到生產(chǎn)的各個環(huán)節(jié)上。使用EXCEL的線性規(guī)劃功能，解決約束條件下的最大化問題。二、一個實例公司生產(chǎn)2種產(chǎn)品：產(chǎn)品1和產(chǎn)品2，每件產(chǎn)品分別可獲利潤100元和200元，生產(chǎn)2種產(chǎn)品均需要3道工序，分別由部門1、2、3完成，其中每道工序所需人數(shù)和每個部門可提供的人數(shù)如下表。問該廠應(yīng)生產(chǎn)多少產(chǎn)品1和產(chǎn)品2才能在滿足各部門人力資源的約束條件下獲得最大利潤？9.8EXCEL與人力資源管理和優(yōu)化1、人力資源模型構(gòu)建設(shè)定決策變數(shù)：2種產(chǎn)品的產(chǎn)量，x1,x2構(gòu)建目標(biāo)利潤函數(shù)：獲得最大利潤：MAXY=100x1+200x2構(gòu)建約束條件：各部門人力資源約束0.6x1+1x2<=1500.5x1+0.7x2<=750.6x1+0.7x2<=78X1,x2>=0,int9.8EXCEL與人力資源管理和優(yōu)化2、EXCEL建模產(chǎn)品與工序人力資源對照表三部門人力資源約束表人力資源優(yōu)化配置表3、規(guī)劃求解規(guī)劃求解參數(shù)設(shè)置4、模型求解結(jié)果分析獲利最大的X1與X2提高效率的瓶頸工種轉(zhuǎn)換教育與培訓(xùn)9.8EXCEL與人力資源管理和優(yōu)化5、人力資源優(yōu)化模型添加決策變數(shù)：Xij—從i部門轉(zhuǎn)到j(luò)部門的職工數(shù),建立部門間人員調(diào)配表構(gòu)建優(yōu)化目標(biāo)函數(shù)：考慮培訓(xùn)費的利潤Maxy=100x1+200x2-50*(x12+x13+x21+x23+x31+x32)構(gòu)建優(yōu)化約束條件：考慮人工轉(zhuǎn)移0.6x1+1x2<=150+(x31+x21)-(x12+x13)0.5x1+0.8X2<=75+(x12+x32)-(x21+x23)0.9x1+0.7x2<=78+(x23+x13)-(x31+x32)X12+x13<=30X21+x23<=5X31+x32<=15X1,x2>=0,x13,x21,x23,x31,x32>=0,intEXCEL中的線性規(guī)劃求解EXCEL中的線性規(guī)劃求解EXCEL中的線性規(guī)劃求解

各部門人力資源優(yōu)化結(jié)果

產(chǎn)品1(x1)產(chǎn)品2(x2)

生產(chǎn)量3979

利潤19700

類似的問題約束條件下的極大極小值……第三次作業(yè)要求1、選擇一個會計相關(guān)的專案，在EXCEL中建立模型；如：量本利分析、投資專案評價、杜邦分析、購房的分期付款問題存款的複利問題、年金的現(xiàn)值問題、貸款利率中途調(diào)整問題、國庫券的投資計算、債券購買、。。。。。。要求能根據(jù)已知數(shù)據(jù)，按該方法的計算要求自動計算本關(guān)的指標(biāo)2、介面美觀，自動化程度高；3、使用下拉列表框、微調(diào)框等控件4、如果需要請使用可調(diào)圖形5、製作完成後發(fā)到我的郵箱：westone@,檔命名和郵箱主題按