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

申請(qǐng)免費(fèi)試用、咨詢(xún)電話(huà)：400-8352-114

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