小資料：Excel在財務(wù)預(yù)測中的應(yīng)用

申請免費試用、咨詢電話：400-8352-114

來源：泛普軟件 計算機技術(shù)已經(jīng)成為推動世界經(jīng)濟發(fā)展的因素之一，計算機的廣泛應(yīng)用，對財務(wù)會計理論與實務(wù)帶來了深刻影響。筆者就 Microsoft Excel在財務(wù)預(yù)測中的一些問題提出解決方案和實踐體會和大家共享。本文是以Excel 2000版為例，Excel其他版本會略有不同。   Excel功能介紹   Microsoft公司推出的Excel電子化表格處理軟件，是Office套件的核心程序之一。其集表格計算、圖表顯示、超強內(nèi)置函數(shù)、數(shù)據(jù)分析等功能于一體，被廣泛應(yīng)用在商務(wù)辦公、財務(wù)統(tǒng)計、工程計算等各個領(lǐng)域。擁有容量大、內(nèi)置功能函數(shù)豐富、多種類型制圖和數(shù)據(jù)庫強大等多種特點。   Excel在財務(wù)預(yù)測中的應(yīng)用 方案的提出：以一元線性回歸分析為例 下表是某加工企業(yè)當(dāng)年的機器加工工時及對應(yīng)的間接費用，根據(jù)生產(chǎn)預(yù)算，要求財務(wù)人員測算次年加工工時分別安排為65、69、75、78、85、91等時相應(yīng)發(fā)生的間接費用。   構(gòu)建數(shù)學(xué)模型 第一步：創(chuàng)建工作表。 啟動Excel，建立一張新工作表，命名并保存。將原始數(shù)據(jù)按順序輸入工作表中，如下表所示： 第二步：作散點圖并添加趨勢線。 首先，將機器工時當(dāng)作自變量X，將間接費用當(dāng)作因變量Y，通過圖表向?qū)Чぞ邅硗瓿蓤D表制作：選取“插入圖表”以啟動圖表工具向?qū)?，在“圖表類型”中選取XY散點圖；在“數(shù)據(jù)源區(qū)域”中選取上表中的A2：M3部份；在“圖表選項”中的圖表標(biāo)題欄寫上“散點圖分析技術(shù)”，在分類X軸上填上“機器工時”，在數(shù)據(jù)Y軸上填“間接費用”；點選“作為其中的對象插入”可選項；按確定后便嵌入一個圖表到工作表中。在嵌入完基礎(chǔ)圖表后如果對圖表不太滿意還可以雙擊圖表，以激活圖表進行相關(guān)編輯修改。 創(chuàng)建完圖表后，就可以開始添加趨勢線了，操作步驟是：用鼠標(biāo)選中圖表，從菜單條中選取“圖表&添加趨勢線”，再從彈出的選項框中選取一種最接近于現(xiàn)有圖表的線型（Excel提供了6種常用的趨勢預(yù)測曲線），創(chuàng)建完后可以直觀地看出，數(shù)據(jù)之間的變化趨勢呈線性關(guān)系。   第三步，建立數(shù)學(xué)模型。 用制作圖表的方法建立數(shù)據(jù)變量之間的趨勢關(guān)系后，便可以近似地建立機器工時與間接費用之間的線性模型Y=A+BX了。其中：Y表示因變量間接費用，X表示自變量機器工時，A和B是線性模型中待求解的常數(shù)。   相關(guān)性分析 數(shù)學(xué)模型的建立，并不表示其客觀表達了機器工時和間接費用之間的函數(shù)關(guān)系，還需要通過一定參數(shù)對模型進行判斷。這就需要用到CORREL相關(guān)分析函數(shù)，該函數(shù)是返回兩組數(shù)據(jù)之間的相關(guān)系數(shù)，語法CORREL(array1,array2)，其中：array1表示為第一組數(shù)值的單元格區(qū)域，array2表示為第二組數(shù)值的單元格區(qū)域。函數(shù)運算結(jié)果實質(zhì)上就是統(tǒng)計學(xué)中相關(guān)系數(shù)的計算公式：ρx?y＝ 。用文字表述就是：X、Y變量的相關(guān)系數(shù)等于其協(xié)方差除以變量X和變量Y的標(biāo)準(zhǔn)差。 在B4單元格中輸入公式“＝CORREL（B1：M1，B2：M2）”，按回車后即可解得變量X與變量Y的相關(guān)系數(shù)為0.90，該數(shù)值以定量的方式表示出機器工時X與間接費用Y呈現(xiàn)的正相關(guān)關(guān)系及相關(guān)程度（1表示直線關(guān)系），通過相關(guān)系數(shù)檢驗后，便可確定構(gòu)建的數(shù)學(xué)模型Y＝A＋BX成立。   回歸分析 數(shù)學(xué)模型Y＝A＋BX已經(jīng)建立好，但A與B的值如何來確定？這時，需要借助于Excel統(tǒng)計分析函數(shù)來解決。Excel中用于線性回歸分析的函數(shù)一共有3個，在此只用其中2個函數(shù)即：Intercept、Slope。 求解系數(shù)A的函數(shù)Intercept。本函數(shù)是利用已知的X值與Y值計算回歸直線與Y軸的截距，即模型中的A值。該函數(shù)語法是Intercept?known＿y’s?known＿x’s?，其中：known＿y’s表示為因變量的數(shù)據(jù)值集合，known＿x’s表示為自變量的數(shù)據(jù)值集合。在了解該函數(shù)語法結(jié)構(gòu)后，便可以準(zhǔn)確運用此函數(shù)了。在B5單元格中輸入公式“＝Intercept（b1：m1，b2：m2）”，按回車后Excel便計算出了Intercept函數(shù)求解出值是100.86，即系數(shù)A值為100.86。 求解系數(shù)B的函數(shù)Slope。本函數(shù)是返回known＿y’s和known＿x’s中的數(shù)據(jù)點擬合的線性回歸直線的斜率，即模型中的B值。該函數(shù)語法是 Slope?known＿y’s?known＿x’s?，其中：known＿y’s與known＿x’s表示的意義與Intercept相同。參照上述函數(shù)使用原理，在B6單元格中輸入公式“＝Slope（b1：m1，b2：m2）”，按回車后Slope函數(shù)求解值為13.30，即系數(shù)B值為13.30。 通過上述計算，預(yù)測模型表達式Y(jié)＝100.86＋13.3?X便完全建立了。   應(yīng)用模型解決方案 在完全模型表達式后，便可以將機器加工工時計劃數(shù)值65、69、75、78、85、91分別輸入單元格：C10中，在D5中輸入公式：“＝＄B＄5＋?＄B＄6”（其中的＄表示絕對引用關(guān)系），然后將D5單元格中的公司直接向下復(fù)制到D6至D10中，即可求解出對應(yīng)于機器工時的間接費用的數(shù)值：965，1018，1098，1138，1231，1311。   來源：CBI