EXCEL与财务分析课件

EXCEL財務分析

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

TC=

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

各部門人力資源優化結果

產品1(x1)產品2(x2)

生產量3979

利潤19700

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