《Exce在財務中的應用》 課件 項目7、8：數(shù)據(jù)分析工具的應用、資金需求量的預測

《Excel在財務中應用》（第四版）項目7：數(shù)據(jù)分析工具的應用Excel提供了很多非常實用的數(shù)據(jù)分析工具，例如模擬運算表、規(guī)劃求解工具、方案管理器等，利用這些分析工具，可解決財務管理中的許多問題。本章內容介紹單變量求解、規(guī)劃求解工具、方案管理器的基本應用。實訓1：單變量求解的應用實訓目的

理解單變量求解的基本原理與作用，能夠利用單變量求解方式根據(jù)目標值的計算公式進行逆運算求出其它影響因素的值。能夠在財務決策中應用單變量求解方式，提高計算效率，為分析決策服務。實訓資料中原公司職工張三的年終獎金是全年銷售額的0.2%，前三個季度的銷售額分別為120000元、180000元、150000元，張三想知道第四季度的銷售額為多少時，才能保證年終獎金為1500元。請采用單變量求解方式完成第4季度銷售額的計算。(1)提示：300000。需要先確定目標單元格與可變單元格，在目標單元格中事先定義好計算公式。操作向導“單變量求解”是一組命令的組成部分，這些命令有時也稱作假設分析工具。如果已知單個公式的預期結果，而用于確定此公式結果的輸入值未知，則可使用“單變量求解”功能。簡單的說，就是知道一個公式的結果，求一個公式中包含的某個單元格的值。從結果反推并改變一個變量。例如算式z=3x+4y+1，我們要求當z=20、y=2時x的值，就可以使用單變量求解功能。我們可以把單變量求解想象成公式求解的逆過程。1.定義計算公式啟動Excel，按下列格式建立一張工作表。在B9單元格中輸入“=SUM(B4:B7)*B2”，用以計算年終獎金見圖7-1。2.確定目標值，選定可變單元格執(zhí)行【數(shù)據(jù)】/【假設分析】/【單變量求解】命令，打開“單變量求解”對話框。見圖7-2。3.確定計算結果單變量求解對話框單元格定義目標單元格中必須已有設置好的計算公式的單元格；可變單元格為我們需要計算求解的單元格該單元格中無公式。(1)提示：300000=300000同步訓練７-１已知江北公司某投資項目各期的現(xiàn)金流如下所示，第1期期初流出100萬元，期末流入50萬元，第2期期末流入60萬元，第3期期末流入70萬元。請問，若要求將凈現(xiàn)值的目標值定為100萬元，則第4期期末的現(xiàn)金凈流量應達到多少萬元？請用單變量求解方式求解。按自然計算結果保留2位小數(shù)，（1）用單變量求解方式計算第4期現(xiàn)金流（萬元）。（2）用財務函數(shù)完成凈現(xiàn)值的計算（萬元）。(1)提示：76.6699999999078=76.67,(2)提示：=NPV(B10,50:76.67)+-100=100.00實訓2：規(guī)劃求解的應用實訓目的理解規(guī)劃求解的基本原理與作用，能夠利用規(guī)劃求解方式根據(jù)目標值的計算公式自動求出影響目標值的其他各因素的最優(yōu)值。能夠在財務決策中應用規(guī)劃求解方式，提高計算效率，為分析決策服務。實訓資料已知西部公司生產甲和乙兩種產品，甲產品的單位變動生產成本為70元，乙產品的單位變動生產成本為120元，甲產品的銷售單價為150元，乙產品的銷售單價為200元.利用規(guī)劃求解甲產品和乙產品的數(shù)量為多少時，公司的毛利額最大？相關資源限制條件及產品消耗定額，見圖7-5。操作向導單變量求解僅可解決存在一個變量的問題而規(guī)劃求解可以求解更多變量。1.建立規(guī)劃求解模型（1）確定可變單元格。（2）確定目標單元格與可變單元格數(shù)值之間的關系。（3）確定規(guī)劃求解的約束條件。約束條件為8X1+12X2<=400；5X1+4X2<=500；12X1+15X2<=1000；X1、X2均要大于零且為整數(shù)；2.利用規(guī)劃求解命令求解在加載規(guī)劃求解命令，就可以利用規(guī)劃求解命令進行求解。執(zhí)行【數(shù)據(jù)】/【規(guī)劃求解】命令，打開“規(guī)劃求解”對話框，見圖7-8。【操作提示7-2】設置約束條件為“整數(shù)”有時候需要對可變單元格設置的約束條件為“整數(shù)”，如產品生產的件數(shù)。（4）規(guī)劃求解。實訓3：方案分析的應用實訓目的掌握方案分析的應用原理，能根據(jù)不同財務決策需要，利用Excel建立不同的方案，進行決策分析參考。掌握方案的建立、修改、刪除等操作、能根據(jù)已建立的不同方案顯示方案摘要報告；實訓資料江南公司目前年產D產品2500件，銷售單價120元，單位變動成本60元，全年專屬固定成本50000元。為了進一步提高產品的盈利能力，公司管理層在集中討論各種可以實現(xiàn)的途徑。影響因素方案一方案二方案三產品售價-10%-8%0%單位變動生產成本0%-2%-2%預計產銷量30%25%20%固定成本001%該公司擬了三種運作方案，請你計算每一種方案下的目標利潤，并提供三種方案的結果比較報告。12定義方案分析器中各單元格名稱選中A4：B7單元格區(qū)域，執(zhí)行【公式】/【定義的名稱】命令，打開“根據(jù)所選內容創(chuàng)建”，在最左列的復選框中打鉤，見圖7-14。3.建立方案執(zhí)行【數(shù)據(jù)】/【數(shù)據(jù)有效工具】/【假設分析】命令，打開【方案管理器】對話框，單擊“添加”按鈕，見圖7-15。4.輸入可變單元格的值在“方案變量值”對話框內，輸入不同方案可變單元格的值，然后單擊“確定”按鈕，見圖7-17所示。5.顯示方案方案制定好以后，任何時候都可以執(zhí)行方案，查看不同的執(zhí)行結果，具體方法如下。執(zhí)行【數(shù)據(jù)】/【數(shù)據(jù)工具】/【假設分析】命令，打開“方案管理器”對話框，見圖7-18。6.建立方案摘要報告執(zhí)行【數(shù)據(jù)】/【數(shù)據(jù)工具】/【假設分析】命令，打開“方案管理器”對話框，單擊“摘要”按鈕。項目8：資金需要量的預測本章主要介紹銷售百分比法高低點法回歸直線法資金預測模型公司籌集資金首先要對資金需要量進行預測。公司對固定資產資金需要量的預測一般是通過投資決策、編制資本預算完成的。在公司正常經營的情況下，資金的預測主要是對流動資金需要量進行測算。實訓1：用銷售百分比法預測資金需要量實訓目的掌握IF函數(shù)及數(shù)據(jù)有效性在實務中的應用技巧，能在Excel中制作下拉式菜單，能夠制作一個通用的銷售百分比法資金需要量預測模型。掌握在Excel中的公式輸入，能正確運用“+”、“-”、“*”、“/”等運算符進行相關數(shù)據(jù)的計算。實訓資料請根據(jù)江南公司資產負債表、利潤表等相關資料見表8-1、8-2.采用銷售百分比法預測該公司20×9年資金需要量。其它資料如下：銷售增長率通過計算營業(yè)收入三年平均增長率確定；營業(yè)凈利率通過計算近三年的平均營業(yè)凈利率確定；固定資產投資金額按投資計劃表金額495萬元確定；股利支付率假定為30%。操作向導1.銷售百分比法的基本原理銷售百分比法是在分析資產負債表有關項目與銷售額關系的基礎上，根據(jù)市場調查和銷售預測取得的資料，確定資產、負債和所有者權益的有關項目占銷售的百分比，并依此推算出流動資金需要量的一種方法。采用銷售百分比法預測資金需要量的步驟如下：（1）根據(jù)歷史數(shù)據(jù)，預計銷售收入增長率；（2）計算資產負債表中敏感項目與銷售收入的百分比。敏感項目和非敏感項目敏感項目與銷售額的百分比=（基期敏感項目數(shù)額÷基期銷售額）×100%（3）計算需要增加的資金；需增加的資金=增加的資金―增加的自發(fā)性負債（4）計算內部留存收益增加額；＝預計銷售額×計劃銷售凈利率×（1―股利支付率）（5）計算外部融資需求。＝（資產銷售百分比×新增銷售額）－（負債銷售百分比×新增銷售額）－留存收益增加額2.Excel中銷售百分比法資金需要量計算模型的設計（1）搜集整理企業(yè)近年來的銷售、盈利情況資料。（2）資產負債表敏感性分析選擇表設計。選擇C4：C54單元格，執(zhí)行【數(shù)據(jù)】/【數(shù)據(jù)有效性】，打開【數(shù)據(jù)有效性】窗口，進行如下設置見圖8-4。（3）計算敏感項目銷售百分比。“敏感項目與銷售額的百分比=（基期敏感項目數(shù)額÷基期銷售額）×100%在D5單元格中輸入“=IF(C5="是",B5/利潤表!$B$3,"")”（4）計算敏感性資產、敏感性負債銷售百分比之和。在D28單元格中輸入“=SUM(D4:D27)”在D54單元格中輸入“=SUM(D31:D53)”公司每增加銷售收入100元，需要增加53.28元的資產，同時，增加34.83元的商業(yè)信用（屬自發(fā)性負債籌資）。

（5）計算預計年度需要增加的資金（6）計算預計年度內部留存收益數(shù)額（7）計算外部融資需求數(shù)據(jù)有效性的作用你可償試一下，修改資產負債表中敏感項目，最終的計算結果是否發(fā)生變化？在這里，數(shù)據(jù)有效性的設置能發(fā)揮出較好的作用。實訓2：用回歸直線法預測資金需要量實訓目的能夠調用Excel中的分析工具庫中工具用于財務實務決策。能夠正確使用分析工具庫中的回歸分析法，能正確設置Y值與X值，能根據(jù)計算結果編寫分析項目的回歸公式。假設中南公司20×4~20×8年度的資金占用與銷售收入之間的關系見表8-3。假設20×9的預計銷售收入為700萬元。請用回歸分析法預測中南公司20×9年的資金需要量。實訓資料年度20×420×520×620×720×8業(yè)務量x(萬元)500520480540690資金占用y(萬元)100110120125130操作向導1.回歸直線法的基本原理y=66.3503＋0.0928x20×9年的資金需要量=66.3503＋0.0928×700=131.31（萬元）2.利用EXCEL分析工具庫來進行公式的設置（1）加載宏,選擇分析工具庫。（2）選擇回歸分析方法。（3）設置因變量和自變量計算區(qū)域。(4)編輯資金預測的線性回歸公式。寫出資金預測的線性公式：y=66.3503+0.0928x。實訓3：用高低點法預測資金需求量實訓目的掌握MAX、MIX函數(shù)的使用，能夠利用該函數(shù)在一組數(shù)據(jù)中找到最大值與最小值；理解HLOOKUP函數(shù)的使用，能夠利用該函數(shù)在指定的數(shù)據(jù)表中按照指定的查找條件在數(shù)據(jù)表的首行查找，并能精確返回指定行數(shù)的值。掌握CONCATENATE函數(shù)的使用，能夠利用該函數(shù)將多個單元格中的數(shù)字或文本合成一個簡要的字符串。實訓資料假設中南公司20×4~20×8年度的資金占用與銷售收入之間的關系見表8-6。假設20×9的預計銷售收入為700萬元。請用高低點法預測中南公司20×9年的資金需要量。操作向導1.高低點法的含義高低點法（High-lowMethod）是利用代數(shù)式y(tǒng)=a＋bx，選用一定歷史資料中的最高業(yè)務量與最低業(yè)務量的所對應的資金需求之差，與兩者業(yè)務量之差進行對比，求出單位業(yè)務量的變動資金需求，然后將其代入高點或低點的資金需求公式，求出固定資金需求的方法。然后再利用y=a＋bx預測資金需要量。MAX與MIN函數(shù)MAX(number1,number2,...)2.編制資金需求預測的線性公式。MAX函數(shù)用于返回一組值中的最大值。MIN(number1,number2,...)MIN