【正文】 按部門統(tǒng)計(jì)，這就要用到數(shù)據(jù)透視表了。 數(shù)據(jù)透視圖報(bào)表以圖形形式表示數(shù)據(jù)透視表中的數(shù)據(jù)。 第二篇 Excel在財(cái)務(wù)管理中的應(yīng)用 第 6章 財(cái)務(wù)預(yù)測(cè) 本章學(xué)習(xí)目標(biāo) 本章主要講解利用 Excel進(jìn)行財(cái)務(wù)預(yù)測(cè)和建立財(cái)務(wù)預(yù)測(cè)模型的相關(guān)知識(shí)。 預(yù)測(cè)分析步驟 預(yù)測(cè)分析一般可按以下步驟進(jìn)行： （ 1） 確定預(yù)測(cè)目標(biāo)。 （ 3）選擇預(yù)測(cè)方法。 （ 5）檢查驗(yàn)證。 （ 7）報(bào)告預(yù)測(cè)結(jié)論。 1．定量預(yù)測(cè)法 定量預(yù)測(cè)法是在掌握與預(yù)測(cè)對(duì)象有關(guān)的各種要素的定量資料的基礎(chǔ)上，運(yùn)用現(xiàn)代數(shù)學(xué)方法進(jìn)行數(shù)據(jù)處理，據(jù)以建立能夠反映有關(guān)變量之間規(guī)律性聯(lián)系的各類預(yù)測(cè)模型的方法體系，可分為趨勢(shì)外推分析法和因果分析法。這種方法是將時(shí)間作為制約預(yù)測(cè)對(duì)象變化的自變量 ，把未來作為歷史的自然延續(xù)，屬于按事物的自身發(fā)展趨勢(shì)進(jìn)行預(yù)測(cè)的動(dòng)態(tài)預(yù)測(cè)方法。因此，該方法又被稱為時(shí)間序列分析法。這種方法是根據(jù)變量之間存在的因果關(guān)系函數(shù)，按預(yù)測(cè)因素的未來變動(dòng)趨勢(shì)來推測(cè)預(yù)測(cè)對(duì)象（即因變量）未來的相關(guān)預(yù)測(cè)方法。 2．定性預(yù)測(cè)法 定性預(yù)測(cè)法是由有關(guān)方面的專業(yè)人員根據(jù)個(gè)人經(jīng)驗(yàn)和知識(shí)，結(jié)合預(yù)測(cè)對(duì)象的特點(diǎn)進(jìn)行綜合分析，對(duì)事物的未來狀況和發(fā)展趨勢(shì)做出推測(cè)的預(yù)測(cè)方法。 實(shí)際工作中常常將二者結(jié)合應(yīng)用，相互取長補(bǔ)短，以提高預(yù)測(cè)的準(zhǔn)確性和預(yù)測(cè)結(jié)論的可信度。 （ 2）利潤預(yù)測(cè)。 （ 4）資金預(yù)測(cè)。用于銷售量預(yù)測(cè)的常用方法有判斷分析法、趨勢(shì)外推分析法、因果分析法和產(chǎn)品壽命周期推斷法等。在這里，我們主要討論趨勢(shì)外推分析法。 平均法是指根據(jù)所掌握的特定預(yù)測(cè)對(duì)象若干時(shí)期銷售量的歷史資料，按照一定方法進(jìn)行處理并計(jì)算其平 均值，以確定預(yù)測(cè)銷售量的方法。其計(jì)算公式為： n Qy tn ???? 時(shí)間序列期數(shù) 業(yè)務(wù)量之和已知時(shí)間序列各期銷售預(yù)測(cè)銷售量 1 這種方法的優(yōu)點(diǎn)是計(jì)算過程很簡單，缺點(diǎn)是沒有考慮遠(yuǎn)近期銷售業(yè)務(wù)量的變動(dòng)對(duì)預(yù)測(cè)銷售狀況的影響程度的不同，從而使不同時(shí)期資料的差異簡單平均化，所以，該方法只適用于對(duì)各期銷售業(yè)務(wù)量比較穩(wěn)定，且沒有季節(jié)性變化的食品和日常用品等的銷售預(yù)測(cè)。 （ 1）一次移動(dòng)平均法。其預(yù)測(cè)模型的計(jì)算公式為： tttt yyyy ????? ??? n y? 1nt11 ? 1??ty 為第 t+1 期的預(yù)測(cè)值； Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 50 ty為第 t期的移動(dòng)平均數(shù)； 1nt1 y ?? ??? ?tt yy為將被平均的 n個(gè)觀察值； n為移動(dòng)平均的項(xiàng)數(shù)。但是，在實(shí)際工作中，當(dāng)企業(yè)或商店要逐月預(yù)測(cè)下個(gè)月的成百上千種產(chǎn)品的進(jìn)貨或銷售情況時(shí)，這種方法還是很適用的。遞推算法的計(jì)算公式為： nyyynyynynyyynyy nttnttnk ktnk ntntktnk ktt ??? ??? ????? ?? ?????????? ??? ?? 111101 上述公式說明，第 t+1 期的預(yù)測(cè)值 1??ty 是在第 t 期預(yù)測(cè)值 y? t的基礎(chǔ)上加上一個(gè)修正值nyy ntt ??得出的。對(duì)于同樣的變化量 ntt yy ?? ， n 越大，則nyy ntt ??越小，從而修正作用越小，也就是對(duì)數(shù)據(jù)的平滑能力增強(qiáng)。因此，對(duì) n 的選擇不同，其預(yù)測(cè)的結(jié)果也就不同。 （ 2）二次移動(dòng)平均法 二次移動(dòng)平均法是對(duì)時(shí)間序列計(jì)算一次移動(dòng)平均數(shù)后，再對(duì)一次移動(dòng)平均數(shù)序列進(jìn)行一次移動(dòng)平均運(yùn)算。 ??? ???? ? 二次移動(dòng)平均預(yù)測(cè)模型為： Tbay ttTt ???? Tty?? 為第 t+T 期的預(yù)測(cè)值； T為第 t 期后的期數(shù)； at、 bt為參數(shù)，即平滑系數(shù)。求解平滑系數(shù)估 計(jì)值的公式為： ? ?????? ??? ?? 39。122ttttttyynbyya 二次移動(dòng)平均法解決了一次移動(dòng)平均法只能預(yù)測(cè)下一期的局限性，它可以進(jìn)行近、遠(yuǎn)期的預(yù)測(cè)。因?yàn)楫?dāng)預(yù)測(cè)期限拉長時(shí)， at、 bt值卻一直沒有變化，那么，在此基礎(chǔ)上得出的預(yù)測(cè)值的代表性就會(huì)下降。 （ 3）趨勢(shì)平均法 趨勢(shì)平均法是指在移動(dòng)平均法計(jì)算 n 期時(shí)間序列移動(dòng)平均值的基礎(chǔ)上，進(jìn)一步計(jì)算趨勢(shì)值的移動(dòng)平均值，進(jìn)而利用特定基期銷售量移動(dòng)平均值和趨勢(shì)值移動(dòng)平均值來預(yù)測(cè)未來銷售 量的一種方法。 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 52 趨勢(shì)平均法的優(yōu)點(diǎn)在于它既考慮了銷售量的移動(dòng)平均數(shù)，又考慮了趨勢(shì)值的移動(dòng)平均值。 （ 2）根據(jù)有關(guān)資料確定銷售量與自變量之間的數(shù)量關(guān)系，建立因 果分析模型。 利潤預(yù)測(cè) 目標(biāo)利潤預(yù)測(cè) 利潤預(yù)測(cè)是企業(yè)經(jīng)營預(yù)測(cè)的一個(gè)重要方面，它是在銷售預(yù)測(cè)的基礎(chǔ)上，通過對(duì)產(chǎn)品的銷售量、價(jià)格水平、成本狀況進(jìn)行分析和測(cè)算，預(yù)測(cè)出企業(yè)未來一定時(shí)期的利潤水平。 （ 2）計(jì)算目標(biāo)利潤基數(shù)。其計(jì)算公式為： 目標(biāo)利潤基數(shù) =有關(guān)利潤標(biāo)準(zhǔn) *相關(guān)指標(biāo) （ 3）確定目標(biāo)利潤修正值 。最終下達(dá)的目標(biāo)利潤應(yīng)該為目標(biāo)利潤基數(shù)與修正值的代數(shù)和，即： 最終下達(dá)的目標(biāo)利潤 =目標(biāo)利潤基數(shù) +目標(biāo)利潤修正值 利潤敏感性分析 1．利潤敏感性分析的主要任務(wù) 利潤敏感性分析的主要任務(wù)是計(jì)算有關(guān)因素的利潤靈敏指標(biāo)，揭示利潤與有關(guān)Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 53 因素之間的相對(duì)數(shù)量關(guān)系，并利用靈敏指標(biāo)進(jìn)行利潤預(yù)測(cè)。 （ 2）單獨(dú)變動(dòng)的假定。 （ 4）同一變動(dòng)幅度的假定。某因素的利潤靈敏度指標(biāo)為該因素按上述假定單獨(dú)變動(dòng) 1%后使利潤增長的百分比指標(biāo)，其計(jì)算公式為： 第 I 個(gè)因素利潤靈敏度指標(biāo)（ Si） =該因素的中間變量基數(shù) /利潤基數(shù)*1%=Mi/P*1%。 概率分析法在利潤預(yù)測(cè)中的應(yīng)用 1．銷售業(yè)務(wù)量為不確定因素時(shí)的情況 2．多因素為不確定因素時(shí)的情況 成本預(yù)測(cè)與資金需要量預(yù)測(cè) 成本預(yù)測(cè) 成本預(yù)測(cè)是根據(jù)企業(yè)未來的發(fā)展目標(biāo)和現(xiàn)實(shí)條件，參考其他資料，利用專門方法對(duì)企業(yè)未來成本水平及其發(fā)展趨勢(shì)所進(jìn)行的推測(cè)與估算。 （ 1）歷史成本分析法。 （ 3）目標(biāo)成本預(yù)測(cè)法。 資金需要量預(yù)測(cè) 銷售百分比法是一種以未來銷售收入變動(dòng)的百分比為主要參數(shù)，考慮隨銷售量變動(dòng)的資產(chǎn)負(fù)債項(xiàng)目及其他因素對(duì)資金的影響，從而預(yù)測(cè)未來需要追加的資金量的一種定量方法。 該方法的計(jì)算步驟如下： （ 1）確定未來銷售收入增長率指標(biāo) K，公式為： K=（預(yù)計(jì)銷售收入 基期銷售收入） /基期銷售收入 （ 2）分析基期資產(chǎn)負(fù)債表的有關(guān)項(xiàng)目，計(jì)算 A與 L。周轉(zhuǎn)中的貨幣資金，正常的應(yīng)收賬款、存貨等項(xiàng)目，一般會(huì)隨銷售額的變動(dòng)而變動(dòng)，應(yīng)列入 A；對(duì)固定資產(chǎn)則視基期生產(chǎn)能力是否還有潛力可利用而定，如果還有潛力，不需要追加資金投入則不予考慮，否則應(yīng)將其列入Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 55 A。 ② L的確定。 ③ 按折舊計(jì)劃和更新改造計(jì)劃確定可作為內(nèi)部周轉(zhuǎn)資金來源的折舊攤銷額與同期將用于更新改造的資金數(shù)額，進(jìn)而計(jì)算 D。 ⑤ 確定新增零星資金開支數(shù)額。 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 56 第 7章 財(cái)務(wù)預(yù)算和財(cái)務(wù)計(jì)劃 本章學(xué)習(xí)目標(biāo) 本章主要講解利用 Excel 編制財(cái)務(wù)預(yù)算和財(cái)務(wù)計(jì)劃的相關(guān)知識(shí)。 銷售預(yù)算 銷售預(yù)算的主要內(nèi)容有銷售量、單價(jià)和銷售收入。在銷售預(yù)算中，通常還包括預(yù)期的現(xiàn)金收入，預(yù)期的現(xiàn)金收入包括上期銷售將于本期收到的現(xiàn)金和本期銷售可于本期收到的現(xiàn)金。產(chǎn)品的生產(chǎn)量與銷售量之間的關(guān)系可表述為： 預(yù)計(jì)生產(chǎn)量 =（預(yù)計(jì) 銷售量 +預(yù)計(jì)期末存貨） 預(yù)計(jì)期初存貨 因?yàn)槠髽I(yè)的生產(chǎn)和銷售不能做到“同步同量”，需要設(shè)置一定量的存貨，以避免存貨太少而影響下一季度銷售活動(dòng)的正常進(jìn)行，或者存貨太多而占用企業(yè)過多的資金，所以，產(chǎn)成品的期初、期末存貨要作為生產(chǎn)預(yù)算的一個(gè)必要組成部分。 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 57 直接材料預(yù)算 根據(jù)生產(chǎn)預(yù)算可編制直接材料預(yù)算，以確定預(yù)算期材料采購量和采購額。由于賒銷的存在，企業(yè)每期購買材料預(yù)期的現(xiàn)金支出應(yīng)該包括前期采購材料將于本期支付的現(xiàn)金和本期采購材料應(yīng)由本期支付的現(xiàn)金，計(jì)算公式為： 本期需要現(xiàn)金支付的采購款 =本期材料采購額 +前期采購于本期的支付額 本期采購于后期的支付額 編制該預(yù)算 通常需要包括以下內(nèi)容： （ 1）預(yù)計(jì)生產(chǎn)量 （ 2）預(yù)計(jì)生產(chǎn)需要量 （ 3）預(yù)計(jì)期末材料存量 （ 4）預(yù)計(jì)期初材料存量 （ 5）預(yù)計(jì)材料采購量 （ 6）材料采購額 （ 7）第一季度付款情況 （ 8）第二季度付款情況 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 58 （ 9）第三季度付款情況 （ 10）第四季度付款情況 （ 11）付款合計(jì)數(shù) 直接人工預(yù)算 直接人工預(yù)算是根據(jù)生產(chǎn)預(yù)算中所確定的預(yù)算期生產(chǎn)量來計(jì)算的，其主要內(nèi)容包括預(yù)計(jì)產(chǎn)量、單位產(chǎn)品工時(shí)、人工總工時(shí)、每小時(shí)人工成本和人工總成本，其公式為： 預(yù)算期直接人工成本 =預(yù)算期生產(chǎn)量 *Σ（單位產(chǎn)品所需工時(shí) *單位工時(shí)直 接人工成本） 編制該預(yù)算通常需要包括以下內(nèi)容： （ 1）預(yù)計(jì)生產(chǎn)量 （ 2）人工總工時(shí) （ 3）人工總成本 制造費(fèi)用預(yù)算 制造費(fèi)用按其習(xí)性，可分為變動(dòng)制造費(fèi)用和固定制造費(fèi)用。 編制該預(yù)算通常需要包括以下內(nèi)容： （ 1）預(yù)計(jì)總工時(shí) （ 2）各季度及全年的變動(dòng)制造費(fèi)用（小計(jì)） （ 3）制造費(fèi)用合計(jì)數(shù) （ 4）以現(xiàn)金支 付的費(fèi)用 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 59 產(chǎn)品成本預(yù)算 產(chǎn)品成本預(yù)算是生產(chǎn)預(yù)算、直接材料預(yù)算、直接人工預(yù)算和制造費(fèi)用預(yù)算的匯總，其主要內(nèi)容包括產(chǎn)品的單位成本和總成本，單位產(chǎn)品成本的有關(guān)數(shù)據(jù)來自前述三個(gè)預(yù)算。 編制該預(yù)算通常需要包括以下內(nèi)容： （ 1）固定制造費(fèi)用分配率 固定制造費(fèi)用分配率從制造費(fèi)用預(yù)算中計(jì)算得到，其公式為： 固定制造費(fèi)用分配率 =全年固定制造費(fèi)用 /全年預(yù)計(jì)總工時(shí) （ 2）單位 成本 （ 3）生產(chǎn)成本 （ 4）存貨成本 （ 5）銷售成本 銷售與管理費(fèi)用預(yù)算 銷售費(fèi)用預(yù)算是指為了實(shí)現(xiàn)銷售預(yù)算所需支付的費(fèi)用預(yù)算，它以銷售預(yù)算為基礎(chǔ)，分析銷售收入、銷售利潤和銷售費(fèi)用的關(guān)系，力求實(shí)現(xiàn)最有效的使用銷售費(fèi)用。其目的在于資金不足時(shí)籌措資金，資Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 60 金充足時(shí)及時(shí)處理現(xiàn)金余額， 并提供現(xiàn)金收支的控制限額，可以發(fā)揮現(xiàn)金管理的作用。 1．現(xiàn)金收入 2．現(xiàn)金支出 3．現(xiàn)金多余或不足 4．現(xiàn)金的籌集與運(yùn)用 編制該預(yù)算通常需要包括以下內(nèi)容： （ 1）銷售現(xiàn)金收入 （ 2）可供使用資金 （ 3）購買材料使用的現(xiàn)金 （ 4）直接人工使用的現(xiàn)金 （ 5）制造費(fèi)用 （ 6）銷售與管理費(fèi)用 （ 8）現(xiàn)金多余或不足 （ 9）支付利息 （ 10）借款支出合計(jì) （ 11）期末現(xiàn)金余額 預(yù)計(jì)損益表 預(yù)計(jì)損益表是在匯總銷售、成本、銷售及管理費(fèi)用等預(yù)算的基礎(chǔ)上編制的。 編制該預(yù)算通常需要包括以下內(nèi)容： （ 1）銷售收入 （ 2）銷貨成本 Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 61 （ 3）毛利 （ 4）銷售及管理費(fèi)用 （ 5）利息 （ 6）利潤總額 （ 7）所得稅 （ 8）凈利潤 凈利潤 =利潤總額 所得稅 預(yù)計(jì)資產(chǎn)負(fù)債表 編制預(yù)計(jì)資產(chǎn)負(fù)債表，是為了判斷預(yù)算所反映的財(cái)務(wù)狀況的穩(wěn)定性和流動(dòng)性。 編制該報(bào)表通常需要包括以下內(nèi)容： （ 1）現(xiàn)金 （ 2）應(yīng)收賬款 （ 3）直接材料 （ 4）產(chǎn)成品 （ 5）房屋設(shè)備年末數(shù) （ 6）累計(jì)折舊 （ 7）資產(chǎn)總額 （ 8）應(yīng)付賬款 （ 9）未分配利潤年末數(shù) （ 10）負(fù)債及股東權(quán)益總額 負(fù)債及股東權(quán)益總額 =負(fù)債合計(jì) +股東權(quán)益合計(jì) Excel 財(cái)務(wù)管理中的應(yīng)用 —— 實(shí)戰(zhàn)篇 62 財(cái)務(wù)計(jì)劃模型 預(yù)計(jì)財(cái)務(wù)報(bào)表的方法 1．銷售百分比法 銷售百分比法是將銷售收入按百分比增長，假設(shè)各財(cái)務(wù)變量與銷售收入之間的關(guān)系為比率關(guān)系。 其計(jì)算公式為： 銷售收入 =基期銷售收入 *（ 1+銷售增長率） 報(bào)表其他要素 =銷售收入 *相應(yīng)百分比比例 2．線性回歸法 線性回歸法是以過去一系列的財(cái)務(wù)狀況為依據(jù)，觀察銷售收入與各財(cái)務(wù)變量之間的關(guān)系，運(yùn)用回歸分析方法測(cè)定其直線回歸方程： 報(bào)表要素 =A*銷售收入 +B 計(jì)算出該方程中的參數(shù) A、 B 和相關(guān)系數(shù) R2，則得到報(bào)表要素的各線性回歸方程，如果相關(guān)系數(shù) R2接近于 1，說明這些財(cái)務(wù)變量與銷售收入之間存在線性關(guān)系，可以用上述方程進(jìn)行預(yù)測(cè)。在這種情況下，可以采用多元回歸法進(jìn)行預(yù)測(cè)。 2．建立預(yù)計(jì)財(cái)務(wù)報(bào)表 按銷售百分比法建立預(yù)計(jì)財(cái)務(wù)報(bào)表，即進(jìn)行財(cái)務(wù)預(yù)測(cè)的依據(jù)是基年財(cái)務(wù)報(bào)表數(shù)據(jù)按比例推