EXCEL在財(cái)務(wù)管理中的應(yīng)用.ppt

EXCEL 在財(cái)務(wù)管理中的應(yīng)用,為什么EXCEL對財(cái)務(wù)人員如此重要,財(cái)務(wù)人員每天面對大量數(shù)據(jù)和信息，大部分的數(shù)據(jù)都將通過EXCEL處理 決策和分析需要用數(shù)據(jù)（事實(shí)）說話 EXCEL是幫助財(cái)務(wù)人員進(jìn)行數(shù)據(jù)處理的強(qiáng)大通用工具 用好EXCEL意味著： 大大減少工作量和勞動(dòng)強(qiáng)度 節(jié)省時(shí)間去處理更為重要的任務(wù) 及時(shí)快速的決策支持 在下屬和上司面前樹立自己的技術(shù)權(quán)威,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,提升Excel操作水平的快捷方法,掌握財(cái)務(wù)工作中常用的快捷鍵 掌握財(cái)務(wù)工作中常用的菜單項(xiàng) 熟悉財(cái)務(wù)工作中常用的函數(shù)和公式 雙手操作，鍵盤鼠標(biāo)同上陣,模塊一：常用函數(shù)的運(yùn)用 模塊二：透視表的運(yùn)用 模塊三：圖表的制作 模塊四：EXCEL與PPT的結(jié)合運(yùn)用,課程安排,模塊一：實(shí)用函數(shù),財(cái)務(wù)部常用函數(shù)介紹 實(shí)用案例：VLOOKUP的巧用 實(shí)用案例：工資個(gè)人所得稅計(jì)算的技巧 實(shí)用案例：補(bǔ)充不完整的數(shù)據(jù)表 實(shí)用案例：三招搞定excel單多條件求和,Year( ):取出單元格中日期字符的年份 Month( )：取出單元格中日期字符的月份 Now( ):取出當(dāng)天日期，也可以用函數(shù)TODAY( )代替 想想看，制作應(yīng)收賬款余額表時(shí),日期函數(shù)有什么用？ Now( ) Date( )?,常用函數(shù)之日期篇,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,Sum( )：數(shù)值求和 Sum函數(shù)的快捷鍵是”ALT”+”+” Sumproduct( ):利用數(shù)組進(jìn)行數(shù)值求和 Sumif( )：有條件的數(shù)值求和 Count( ):數(shù)目求和 Countif( ):有條件的數(shù)目求和,常用函數(shù)之統(tǒng)計(jì)篇,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,Vlookup( )：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值（可返回一列，也可利用數(shù)組返回多列，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。用于條件定位，是財(cái)務(wù)工作中使用頻率極高的一個(gè)函數(shù)。如果使用數(shù)組的話可以一次找出多列結(jié)果.,常用函數(shù)搜索篇,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,巧用VLOOKUP查找，替換數(shù)據(jù),編制的基礎(chǔ)是從財(cái)務(wù)軟件中取得的應(yīng)收帳款科目的明細(xì)記錄清單。要得到所需要的輸出格式，我們還需要增加以下四個(gè)字段的信息：帳齡，客戶名稱，銷售經(jīng)理和銷售渠道,巧用VLOOKUP查找，替換數(shù)據(jù),VLOOKUP的原理就是根據(jù)兩張表（源表，目標(biāo)表）共有的索引列來把源表的指定列引用到目標(biāo)表格上。接下來我們使用VLOOKUP函數(shù)將供應(yīng)商名稱從訂單信息表中查找到并填寫到當(dāng)前表格上去。,你需要根據(jù)哪個(gè)索引列查找？,確定進(jìn)行要在源表上進(jìn)行查找的數(shù)據(jù)區(qū)矩形區(qū)域,指定引用列在在矩形區(qū)域中所處的列數(shù)，以索引列為第1列,默認(rèn)為0或FALSE，表示絕對匹配,巧用VLOOKUP查找，替換數(shù)據(jù),索引列的唯一性：索引列的重復(fù)將導(dǎo)致系統(tǒng)只引用首先找到的（通常從上往下的第一個(gè)）值，從而丟失數(shù)據(jù) 絕對引用：引用源表的索引區(qū)域時(shí)一定記得加$，否則可能無法查到數(shù)據(jù) 索引列：字符前后無空格，其數(shù)據(jù)類型兩張表必須保持一致，否則查找不到，可以運(yùn)用TRIM（）函數(shù)解決,VLOOKUP使用時(shí)容易犯的三大錯(cuò)誤,巧用VLOOKUP查找，替換數(shù)據(jù),連續(xù)輸入三次VLOOKUP函數(shù)可以完成以上三列，有沒有一次搞定的好方法呢？ 如果需要引用的不是三列，而是十三列，三十列，那么即便一次次的輸入VLOOKUP函數(shù)也會(huì)讓人抓狂，財(cái)務(wù)人員需要更強(qiáng)大的VLOOKUP函數(shù)使用方法,VLOOKUP的進(jìn)階使用：一次搞定N列！,相對引用和絕對引用,相對引用、絕對引用是指在公式中使用單元格或單元格區(qū)域的地址時(shí)，當(dāng)將公式向旁邊復(fù)制時(shí)，地址是如何變化的。 具體情況舉例說明： 1、相對引用，復(fù)制公式時(shí)地址跟著發(fā)生變化，如C1單元格有公式：=A1+B1 當(dāng)將公式復(fù)制到C2單元格時(shí)變?yōu)椋?A2+B2 當(dāng)將公式復(fù)制到D1單元格時(shí)變?yōu)椋?B1+C1 2、絕對引用，復(fù)制公式時(shí)地址不會(huì)跟著發(fā)生變化，如C1單元格有公式：=$A$1+$B$1 當(dāng)將公式復(fù)制到C2單元格時(shí)仍為：=$A$1+$B$1 當(dāng)將公式復(fù)制到D1單元格時(shí)仍為：=$A$1+$B$1,在Excel表中創(chuàng)建公式時(shí)，若使用單元格引用，就意味著這個(gè)公式同工作薄中的其他單元格連接起來了，當(dāng)引用單元格中的值發(fā)生變化時(shí)，公式的值也相應(yīng)地發(fā)生變化。,If( )：可以使用函數(shù) IF 對數(shù)值和公式進(jìn)行條件檢測 And( )：所有參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要一個(gè)參數(shù)的邏輯值為假，即返回 FALSE。 Or( )：在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE，否則為FALSE 判斷型函數(shù)一般不會(huì)單獨(dú)使用，而是與其它類型的函數(shù)構(gòu)成復(fù)合函數(shù)出現(xiàn)。 看上去很簡單的函數(shù)，用得好就能化腐朽為神奇，節(jié)約你大量寶貴的工作時(shí)間。,常用函數(shù)之判斷篇,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,Find( )：查找其他文本字符串 (within_text) 內(nèi)的文本字符串 (find_text)并返回 find_text 的起始位置編號。 Left( ):從一個(gè)文本字符串的左邊第一個(gè)字符開始，截取指定數(shù)目的字符。 Right( ):從一個(gè)文本字符串的右邊第一個(gè)字符開始，截取指定數(shù)目的字符 Mid( ):從一個(gè)文本字符串的指定位置開始，截取指定數(shù)目的字符。 Len( ):統(tǒng)計(jì)文本字符串中字符數(shù)目。 Trim( ):把單元格字符串中前后的空格去除，常用于精確比較前的準(zhǔn)備工作,常用函數(shù)之字符篇,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,實(shí)用復(fù)合函數(shù)例2補(bǔ)充不完整的數(shù)據(jù)表,公式法,自動(dòng)宏,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,其他部門交來的表格留下了大量的空格，相同欄目只是第一行會(huì)寫名字，如果一個(gè)個(gè)手工給填上去，幾千行數(shù)據(jù)，得花多少時(shí)間啦？Excel能幫我想想辦法嗎？,實(shí)用三招搞定EXCEL單/多條件求和匯總,EXCEL中用SUM求和大家都了解。如圖所示。但如果我們需要知道區(qū)域是華北的A產(chǎn)品銷售額是多少？能不能有辦法用公式/函數(shù)實(shí)現(xiàn)呢？,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,第一招：用SUMIF（）單條件求和,指定條件所在的區(qū)域,指定條件，可以是數(shù)值或表達(dá)式,要進(jìn)行匯總的數(shù)據(jù)區(qū)域,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,第二招：用EXCEL自帶的條件求和向?qū)?shí)現(xiàn)多條件求和,第二招：用EXCEL自帶的條件求和向?qū)?shí)現(xiàn)多條件求和,條件求和向?qū)枰诩虞d宏中加載方可運(yùn)用,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,第二招：用EXCEL自帶的條件求和向?qū)?shí)現(xiàn)多條件求和,第三招：用SUM+IF實(shí)現(xiàn)多條件求和,求A產(chǎn)品30000和B產(chǎn)品都大于25000的分公司收入合計(jì),*代表和，改成+就代表或,運(yùn)用數(shù)組和用*/+連接乃是實(shí)現(xiàn)多條件求和的關(guān)鍵,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,=SUM(IF($C$2:$C$1430000,IF($D$2:$D$1425000,$F$2:$F$14,0),0),致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,QUESTION：,統(tǒng)計(jì)出區(qū)域在華北并且A產(chǎn)品銷售額超過20000元或B產(chǎn)品銷售額超過15000元的分公司之銷售收入總和。,Tips: /+，（），數(shù)組的靈活組合與運(yùn)用是實(shí)現(xiàn)多條件求和（求個(gè)數(shù)）的關(guān)鍵。,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,記住這三點(diǎn)，N多條件求和也不怕！,+號表示條件OR/*號表示條件AND,（）里面的表達(dá)式將得到優(yōu)先運(yùn)算然后再參與（）外的運(yùn)算,換成Count將可以統(tǒng)計(jì)符合條件的公司數(shù)量,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,模塊一：常用函數(shù)的運(yùn)用 模塊二：透視表的運(yùn)用 模塊三：圖表的制作 模塊四：EXCEL與PPT的結(jié)合運(yùn)用,課程安排,模塊二：透視表的應(yīng)用,透視表的基本應(yīng)用 透視表在財(cái)務(wù)報(bào)表中的綜合運(yùn)用,對每個(gè)月預(yù)算的執(zhí)行情況，財(cái)務(wù)部在月結(jié)完后都要對外提供管理報(bào)表。由于每位部門副總看問題的角度、對報(bào)表格式的要求不一致，同樣的數(shù)據(jù)內(nèi)容（比如應(yīng)收帳款的分析），財(cái)務(wù)部總是要做出好幾張報(bào)表，費(fèi)時(shí)費(fèi)力。有沒有更好的辦法來一勞永逸的解決問題呢？ 銷售：按供應(yīng)商排序、匯總 財(cái)務(wù)：按帳齡排序、匯總 人力資源：按銷售經(jīng)理排序、匯總 ,2.1 透視表應(yīng)用案例,1.打開課堂用沙盤文件目錄下的應(yīng)收帳款余額表文件，選到應(yīng)收帳款余額明細(xì)表，執(zhí)行以上操作。,2.1 透視表應(yīng)用案例,Step 1-點(diǎn)擊插入按鈕,Step 2-點(diǎn)擊數(shù)據(jù)透視表,2.1 透視表應(yīng)用案例,Step 3-數(shù)據(jù)選擇區(qū)域確認(rèn)，確保你需要的數(shù)據(jù)內(nèi)容都被選上！,Step 4-點(diǎn)擊確認(rèn)，一般情況下我們都將透視表放在新工作表內(nèi)，因此第2部分的選項(xiàng)不需要改動(dòng),2.1 透視表應(yīng)用案例,透視表進(jìn)階按鈕區(qū),透視表字段選擇區(qū),透視表字段置放區(qū)，,透視表結(jié)果顯示區(qū)，將字段從選擇區(qū)拖拽至置放區(qū)，則結(jié)果顯示區(qū)發(fā)生變化。顯示結(jié)果。,數(shù)據(jù)透視表選項(xiàng)-顯示-經(jīng)典數(shù)據(jù)透視表布局,2.1 透視表應(yīng)用案例,將應(yīng)收帳款余額拖至此處,將帳齡拖至此處,將客戶名額拖至此處,Step4.按財(cái)務(wù)的表格要求制作透視表。（試試看將渠道拖至頁字段會(huì)出現(xiàn)什么情況？）,2.1 透視表應(yīng)用案例,將應(yīng)收帳款余額拖至此處,將帳齡拖至此處,將客戶名額拖至此處,Step4.按財(cái)務(wù)的表格要求制作透視表。（試試看將渠道拖至頁字段（報(bào)表篩選）會(huì)出現(xiàn)什么情況？）,2.1 透視表應(yīng)用案例,Step5.表格的雛形初顯，還需要進(jìn)一步加工 1.金額顯示為千元級，小數(shù)點(diǎn)后兩位。 2.按總計(jì)金額大小降序排列。 3.帳齡顯示應(yīng)該從左至右升序排列，方符合財(cái)務(wù)工作的習(xí)慣。,2.1 透視表應(yīng)用案例,課堂練習(xí)：通過改變數(shù)據(jù)表的結(jié)構(gòu)完成銷售和人力資源要求的表格。,2.1 透視表應(yīng)用案例,數(shù)據(jù)更新和追加數(shù)據(jù)按鈕,萬一找不到字段列表了，就點(diǎn)它,通過增加公式字段來適用不同的報(bào)表需要，而不必改變原始數(shù)據(jù)表,小貼士-記牢快捷鍵，不變應(yīng)萬變,多記住一些快捷鍵操作，比單純記住菜單項(xiàng)上的選擇界面要更有效。 Excel2007的界面比之前的版本有了巨大的變動(dòng)，很多菜單項(xiàng)都讓人感覺找不到了，但快捷鍵沒有變。 “以不變應(yīng)萬變”是應(yīng)付Excel版本不斷更新的一個(gè)好辦法。,常用的透視表操作,致力于財(cái)務(wù)管理實(shí)踐，打造卓越財(cái)務(wù)經(jīng)理人！,多重區(qū)域合并的透視表運(yùn)用,同時(shí)對多張相同的表格進(jìn)行透視表分析 應(yīng)注意的事項(xiàng),模塊一：常用函數(shù)的運(yùn)用 模塊二：透視表的運(yùn)用 模塊三：圖表的制作 模塊四：EXCEL與PPT的結(jié)合運(yùn)用,課程安排,第一部分 Excel圖表基本篇,餅圖的運(yùn)用,餅圖一般多用于某種靜態(tài)數(shù)據(jù)分布的表現(xiàn)，反映的只是企業(yè)財(cái)務(wù)狀況的一個(gè)切面，較之其他圖形用得較少，但貴在簡要、直觀。,直方圖的運(yùn)用,直方圖可以通過數(shù)據(jù)系列格式選項(xiàng)分類間距來改變直方的粗細(xì)及間距。 改變刻度,直方圖的運(yùn)用-哪張圖中的12月份費(fèi)用增加比例更大？,柱狀復(fù)合圖,特別適用于表示預(yù)算與實(shí)際情況的對比,雙軸圖,如何畫曲線圖表,散點(diǎn)圖法 趨勢線法 顯示公式,第二部分 Excel圖表提高篇,散點(diǎn)圖和趨勢線的財(cái)務(wù)預(yù)測功能,給你所圖所示的數(shù)據(jù)，你能預(yù)測不同產(chǎn)量水平下的潤滑油費(fèi)用支出預(yù)算嗎？,散點(diǎn)圖和趨勢線的財(cái)務(wù)預(yù)測功能,動(dòng)態(tài)圖表的演示,讓圖表動(dòng)起來！點(diǎn)哪個(gè)按鈕就顯示哪個(gè)地區(qū)的形勢。,模塊一：常用函數(shù)的運(yùn)用 模塊二：透視表的運(yùn)用 模塊三：圖表的制作 模塊四：EXCEL的綜合運(yùn)用,課程安排,EXCEL與PPT的結(jié)合運(yùn)用,照相機(jī)的使用 PPT內(nèi)嵌EXCEL表格的處理 色彩，格式的調(diào)配使用,我們在用Excel處理文件時(shí)，如果需要將當(dāng)前的內(nèi)容作為圖片保存起來的話，通常會(huì)選擇用截圖的方法來實(shí)現(xiàn)?？墒悄阒绬?，Excel還提供了一個(gè)“照相機(jī)”功能，只要用它把要保存的內(nèi)容“照”下來，再粘貼到另一個(gè)頁面上就行了,照相機(jī)的使用,照相機(jī)的使用,照相機(jī)的使用,Excel自帶的照相機(jī)和普通截圖功能的區(qū)別 用Excel自帶的照相機(jī)進(jìn)行截圖后，里面的數(shù)據(jù)可以同步進(jìn)行更新。也就是說，在Excel截取成圖片的部分，如果原Excel數(shù)據(jù)進(jìn)行更改的話，圖片也會(huì)同步進(jìn)行更改。,照相機(jī)的使用,EXCEL內(nèi)嵌表格的處理,最常用的七個(gè)菜單項(xiàng),1. 選擇性粘貼(Pastespecial) 2.分列/文字剖析(TextToColumns) 3.排序及自定義排序(Sort & User-Defined Sort) 4.篩選和高級篩選(Filter & Advanced Filter) 5.尋找和替代(Find&Replace) 6.數(shù)據(jù)有效性(Validity) 7.打印預(yù)覽(Print Preview),費(fèi)用報(bào)銷單的制作,費(fèi)用報(bào)銷單的制作-數(shù)據(jù)有效性,Excel在預(yù)算管理中的應(yīng)用模型,第一章 經(jīng)典自動(dòng)化預(yù)算模板簡介,1.1 傳統(tǒng)預(yù)算編制方法的特點(diǎn) 1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示 1.3 自動(dòng)化預(yù)算模板編制的優(yōu)點(diǎn),1.1 傳統(tǒng)預(yù)算編制方法的特點(diǎn),輸入：數(shù)據(jù)收集模板內(nèi)容簡單，格式不統(tǒng)一，申報(bào)部門可自行修改，隨意性大。（后期的數(shù)據(jù)更新、維護(hù)工作量巨大。） 中間整理：較少運(yùn)用公式、函數(shù)、鏈結(jié)，基本仍停留在將Excel當(dāng)在電子草稿紙使用的階段。表與表之間很少或無數(shù)據(jù)聯(lián)動(dòng)關(guān)系。 輸出：三大表之間很少或無數(shù)據(jù)聯(lián)動(dòng)關(guān)系，與數(shù)據(jù)收集模板之間也無公式或鏈結(jié)聯(lián)系。,1.1 傳統(tǒng)預(yù)算編制方法的缺點(diǎn),慢！難以滿足企業(yè)決策層的快速反應(yīng)的決策需要，不能現(xiàn)場對景境的變化給出模擬結(jié)果。 累！由于預(yù)算表格缺乏自動(dòng)更新的能力，幾個(gè)數(shù)據(jù)的更新，可能意味著三大表都要推倒重來一次。 難！預(yù)算參數(shù)牽一發(fā)而動(dòng)全身，每次都靠人來判斷其財(cái)務(wù)影響，很難做到客觀、全面,第一章 經(jīng)典自動(dòng)化預(yù)算模板簡介,1.1 傳統(tǒng)預(yù)算編制方法的特點(diǎn) 1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示 1.3 自動(dòng)化預(yù)算模板編制的優(yōu)點(diǎn),1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示,情景模擬：財(cái)務(wù)部負(fù)責(zé)人帶著最新版本的公司預(yù)算到高級管理層會(huì)議上接受咨詢，經(jīng)討論，有許多決策參數(shù)可能需要改動(dòng)（包括匯率、利率、原材料漲價(jià)幅度，人員加薪水平,產(chǎn)品的銷量和價(jià)格，總經(jīng)理希望能看到按此景境（Scenario）修正后的模擬結(jié)果,方可繼續(xù)討論下去。作為財(cái)務(wù)部負(fù)責(zé)人，你能否現(xiàn)場即時(shí)提供并分析其影響？還是表示要回去研究，下次開會(huì)再接著討論？,1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示,匯率調(diào)整前的利潤數(shù)字,1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示,將參數(shù)假設(shè)表上的明年匯率預(yù)計(jì)改為6.0,預(yù)計(jì)凈利潤從322萬變成了310萬，下降了12萬元！完成這新的預(yù)測結(jié)果只用了幾秒鐘！ （小提示：看看資產(chǎn)負(fù)債表，現(xiàn)金流量表的數(shù)字有沒有跟著匯率同步更新？合不合理）,第一章 經(jīng)典自動(dòng)化預(yù)算模板簡介,1.1 傳統(tǒng)預(yù)算編制方法的特點(diǎn) 1.2 自動(dòng)化預(yù)算模板現(xiàn)場演示 1.3 自動(dòng)化預(yù)算模板編制的優(yōu)點(diǎn),1.3 自動(dòng)化預(yù)算編制方法的優(yōu)點(diǎn),智能化！ 預(yù)算數(shù)據(jù)的改變只需數(shù)秒至數(shù)分鐘的時(shí)間即可反映到以三大表為首的預(yù)算結(jié)果上，大大提高了決策效率，體現(xiàn)出財(cái)務(wù)部的決策支持能力！ 標(biāo)準(zhǔn)化！數(shù)據(jù)收集模板全部標(biāo)準(zhǔn)化格式，新舊版本的數(shù)據(jù)更新瞬間即可完成！省時(shí)省力！ 集中化！各類決策參數(shù)對財(cái)務(wù)方方面面的影響在模板設(shè)計(jì)之初就已全面考慮并設(shè)計(jì)好。因此后期使用時(shí)不用預(yù)測的全面性和準(zhǔn)確性！,1.2 自動(dòng)化預(yù)算模板基本結(jié)構(gòu),第二章 Excel預(yù)算編制技巧綜述,500強(qiáng)公司財(cái)務(wù)部EXCEL預(yù)算編制時(shí)的五大利器 公式和鏈接 標(biāo)準(zhǔn)化數(shù)據(jù)收集模板 透視表 復(fù)合函數(shù) 宏（VBA）,第三章 基礎(chǔ)預(yù)算數(shù)據(jù)的收集與整理,3.1 制作標(biāo)準(zhǔn)化數(shù)據(jù)收集模板的意義 3.2 標(biāo)準(zhǔn)化數(shù)據(jù)收集模板的制作技巧 3.3 基礎(chǔ)預(yù)算數(shù)據(jù)的整理 3.4 基礎(chǔ)預(yù)算數(shù)據(jù)的更新,3.1 標(biāo)準(zhǔn)化數(shù)據(jù)收集模板的意義,保證數(shù)據(jù)輸入的質(zhì)量和全面性 便于后期的維護(hù)和版本更新 利于數(shù)據(jù)的處理和后期財(cái)務(wù)分析 防止填寫部門篡改數(shù)據(jù)和信息泄漏 介面友好、填寫方便,3.2 標(biāo)準(zhǔn)化數(shù)據(jù)收集模板的制作技巧,單元格的隱藏和保護(hù) 數(shù)據(jù)有效性 Vlookup函數(shù),常見的預(yù)算收集模板種類,按部門收集 （大部分傳統(tǒng)企業(yè)采用此形式） 按項(xiàng)目、產(chǎn)品收集（以項(xiàng)目型、產(chǎn)品事業(yè)部為主導(dǎo)的公司） 綜合型 （多見組織架構(gòu)為矩陣型的大型公司）,綜合應(yīng)用型,創(chuàng)建一張匯總表，其后為各部門分表 各部門分表用鏈結(jié)引用各部門上交的格用預(yù)算模板 總表用SUM+數(shù)組的方法合并分表數(shù)據(jù) 另建一張分部門匯總表。,各部門交來的表格分門別類放好，便于追蹤。,常見的預(yù)算收集模板種類,按部門收集 （大部分傳統(tǒng)企業(yè)采用此形式） 按項(xiàng)目、產(chǎn)品收集（以項(xiàng)目型、產(chǎn)品事業(yè)部為主導(dǎo)的公司） 綜合型 （多見組織架構(gòu)為矩陣型的大型公司）,費(fèi)用匯總表,費(fèi)用匯總表完成后，預(yù)算工作下一步的重點(diǎn)一目了然。對那些業(yè)務(wù)部門也無法明確分辨出來的共享成本（或稱之為共性費(fèi)用），需要中間過程計(jì)算（分配）表的設(shè)計(jì)和應(yīng)用了。,費(fèi)用匯總表-鏈結(jié)管理,部門又改了預(yù)算版本，數(shù)字又變了，怎么辦？需要將之前的工作再做一遍嗎？,編輯鏈接是個(gè)好東西！來多少版修改都不怕，幾秒鐘的事！,選中有需要更新版本的預(yù)算文件，點(diǎn)擊“更改源”按鈕,選中有部門重交上來的更新版本，點(diǎn)擊確定即可立即完成全文件的更新。（記得之后所有相關(guān)的透視表也不要忘記同步更新）,費(fèi)用匯總表-鏈結(jié)管理,傳統(tǒng)的預(yù)算前期工作流程,傳統(tǒng)模式：收集完所有部門預(yù)算模板后再開始匯總、分析。收集完之前的時(shí)間很多在等待中浪費(fèi)掉了。因?yàn)闊o法形成整體印象。 自動(dòng)鏈結(jié)模式：收集、更新、匯總、分析同步進(jìn)行，時(shí)間利用效率高。,第三章 預(yù)算表格間的鏈結(jié)與聯(lián)動(dòng),3.1 基礎(chǔ)數(shù)據(jù)表與三大表直接聯(lián)動(dòng) 3.2 基礎(chǔ)數(shù)據(jù)表與三大表間接聯(lián)動(dòng) 3.3 三大表之間的聯(lián)動(dòng),基礎(chǔ)數(shù)據(jù)表,自上而下的參數(shù)假設(shè)表 自下而上的部門預(yù)算收集模板,中間過程計(jì)算表,大部分收集上來的預(yù)算材料并不能直接填入三大表，而是需要一個(gè)整理的過程，如匯總、分類、分配、攤銷等，把這個(gè)過程模板化和標(biāo)準(zhǔn)化，就形成了中間過程計(jì)算表，它是基礎(chǔ)數(shù)據(jù)表與三大表之間的橋梁。 典型的中間過程計(jì)算表： 各類費(fèi)用往產(chǎn)品、項(xiàng)目、事業(yè)部進(jìn)行分?jǐn)偟谋砀瘛?根據(jù)職級標(biāo)準(zhǔn)和人力結(jié)構(gòu)計(jì)算工資成本用的表格。 間接法下的營運(yùn)現(xiàn)金流計(jì)算表格 利息計(jì)算表格,基本模式,成本預(yù)算資料的收集,料：生產(chǎn)部門提供材料清單（BOM COST），無需再次分配 工：人力資源部負(fù)責(zé)收集和提供，間接人工也需要用中間過程計(jì)算表分配 費(fèi)：分直接費(fèi)用和間接費(fèi)用，間接費(fèi)用用模板收集，用中間過程計(jì)算表分配,4.1 基礎(chǔ)數(shù)據(jù)表與三大表直接聯(lián)動(dòng),直接用鏈接進(jìn)行引用：無須攤銷或分配的數(shù)據(jù) =銷售收入!G6*直接材料成本!$F$37+銷售收入!G7*直接材料成本!$F$37+銷售收入!G9*直接材料成本!$F$41+銷售收入!G10*直接材料成本!$F$40 “Alt+”顯示公式，能對單元格的公式設(shè)置是否正常進(jìn)行快速的檢查。,數(shù)量X單價(jià),4.1 基礎(chǔ)數(shù)據(jù)表與三大表直接聯(lián)動(dòng),資產(chǎn)負(fù)債表中存在著大量的直接與參數(shù)假設(shè)表的引用聯(lián)動(dòng)公式,4.1 基礎(chǔ)數(shù)據(jù)表與三大表直接聯(lián)動(dòng),現(xiàn)金流量表中存在著大量的直接與參數(shù)假設(shè)表的引用聯(lián)動(dòng)公式,費(fèi)用表與分?jǐn)偙壤钠ヅ?廠房折舊,間接物料,水、電、燃料,輔助生產(chǎn)人員工資,運(yùn)費(fèi),生產(chǎn)廢品損失,廠房面積比,機(jī)器工時(shí)比,員工數(shù)量比,原材料/產(chǎn)品價(jià)值比,產(chǎn)品數(shù)量比,收發(fā)貨次數(shù)比,A產(chǎn)品,B產(chǎn)品,4.2 基礎(chǔ)數(shù)據(jù)表與三大表間接聯(lián)動(dòng),小劉是一家數(shù)碼產(chǎn)品銷售公司的財(cái)務(wù)經(jīng)理，為了提前掌握和分析公司的銷售情況和現(xiàn)金流，他要求各銷售代表按月更新客戶的產(chǎn)品訂單需求。 為刺激銷售，該公司的產(chǎn)品實(shí)行量價(jià)臺階的機(jī)制，并且根據(jù)實(shí)際銷售情況，該臺階每月還會(huì)有更新。 該工作目前是純手工計(jì)算，工作量非常大，最近管理層要求按星期來更新。,中間過程表案例,中間過程表案例,量價(jià)臺階表，每月一更新,白色區(qū)域是銷售代表們輸入的預(yù)計(jì)訂單信息,彩色區(qū)域是財(cái)務(wù)部為了完成最后的分析報(bào)表要判斷、填寫的內(nèi)容。,中間過程表案例,有沒有辦法在收集銷售訂單信息后，在10分鐘后做出這套銷售分析報(bào)表呢？ IF+VLOOKUP=智能化判斷取數(shù) 透視表=自動(dòng)更新輸入報(bào)表,中間過程計(jì)算表的意義,幫助完成從輸入到輸出的中間過程計(jì)算，以免最終的預(yù)算表格過于臃腫，難于閱讀。 典型中間過程表： 營運(yùn)資金計(jì)算表：損益表與現(xiàn)金流量表之間的橋梁 利息計(jì)算表 各類制造費(fèi)用、管理費(fèi)用的分?jǐn)偙?投資預(yù)算表：同時(shí)影響三大表，本身也是輸出結(jié)果。,中間計(jì)算表的構(gòu)建方法,第一部分：完成原始數(shù)據(jù)的引用,最好是通過公式或鏈接動(dòng)態(tài)的引用數(shù)據(jù)收集模板上的數(shù)據(jù)。 第二部分：完成數(shù)據(jù)的歸納和轉(zhuǎn)換，通過公式和函數(shù)的設(shè)定完成，避免手動(dòng)的調(diào)整。 第三部分：對第二部分的數(shù)據(jù)按業(yè)務(wù)驅(qū)動(dòng)因素進(jìn)行分配、二次分配等操作。其結(jié)果以適合引用至三大表的方式排列。,中間計(jì)算表總結(jié),加深對公司業(yè)務(wù)的理解。行業(yè)不同，從收集上來的數(shù)據(jù)資料轉(zhuǎn)化為報(bào)表資料的過程也不同。 將平時(shí)用于轉(zhuǎn)化的報(bào)表格式固定下來，并利用各類復(fù)合型函數(shù)和透視表實(shí)際自動(dòng)化的轉(zhuǎn)換，以節(jié)約時(shí)間、提高效率。,4.3 三大表之間的聯(lián)動(dòng),聯(lián)動(dòng)的前提是對財(cái)務(wù)三大表之間 的內(nèi)在勾稽聯(lián)系要有深刻的理解，并能用公式量化的加以表達(dá)。 直接聯(lián)動(dòng) 間接聯(lián)動(dòng),利潤表與各表的勾稽關(guān)系,銷量：收入表 銷售收入：收入表 直接材料成本：直接材料表 采購運(yùn)費(fèi)：運(yùn)費(fèi)表 可變/固定制造費(fèi)用：制造費(fèi)用匯總表 質(zhì)保費(fèi)：報(bào)價(jià)表 折舊：折舊表 技術(shù)服務(wù)費(fèi)：技術(shù)服務(wù)費(fèi)表 專利權(quán)使用費(fèi)：專利權(quán)使用費(fèi)表 售后服務(wù)費(fèi)：售后服務(wù)費(fèi)表 管理費(fèi)用：管理費(fèi)用表 利息：利息表，現(xiàn)金流量表通過它來影響利潤表,利潤表作為最終的結(jié)果輸出表，完全由鏈接和公式組成，不應(yīng)有任何輸入數(shù)字,資產(chǎn)負(fù)債表與各表的勾稽關(guān)系,以本年度12月31日的預(yù)計(jì)資產(chǎn)負(fù)債表為基礎(chǔ)來編制 流動(dòng)資產(chǎn)：現(xiàn)金流量表和營運(yùn)資本表 固定資產(chǎn)：固定資產(chǎn)投資計(jì)劃表 無形資產(chǎn)：無形資產(chǎn)計(jì)劃表 流動(dòng)負(fù)債：現(xiàn)金流量表和營運(yùn)資本表 長期負(fù)債：現(xiàn)金流量表 股本：股東股益變動(dòng)表 留存收益：利潤表,資產(chǎn)負(fù)債表除了第一欄的上年度底數(shù)字為基礎(chǔ)外，其他各欄數(shù)字均為鏈結(jié)和公式,營運(yùn)資金的預(yù)測,通過對應(yīng)收帳款周轉(zhuǎn)天數(shù)（DSO）、應(yīng)付帳款周轉(zhuǎn)天數(shù)（DPO）、存貨周轉(zhuǎn)天數(shù)（DTO）的預(yù)測，模擬出不同假設(shè)條件下的企業(yè)營運(yùn)資金狀況。 企業(yè)存在2.35億的資金缺口，由于無法從客戶和供應(yīng)商處獲得更優(yōu)惠的付款條件，預(yù)計(jì)只能通過銀行貸款解決，這將大大增加企業(yè)的利息費(fèi)用。 盡管主要原材料的周轉(zhuǎn)天數(shù)有望控制在25天的設(shè)定目標(biāo)，但由于備品備件庫需要大量的補(bǔ)充，這一計(jì)劃將使09年的存貨周轉(zhuǎn)天數(shù)降至約35天。,營運(yùn)資金表,營運(yùn)資金表,公司每1元錢營業(yè)收入竟然需要1.2元營運(yùn)資金，應(yīng)收、應(yīng)付、存貨哪個(gè)的帳期影響大？你能快速的完成敏感性分析嗎？,經(jīng)典外企500強(qiáng)預(yù)算模板結(jié)構(gòu),參數(shù)假設(shè)表,宏觀經(jīng)濟(jì)參數(shù),匯率 利率、貼息率 CPI/PPI（漲價(jià)預(yù)備費(fèi)）：做3-5年規(guī)劃時(shí)用到 出口退稅率 其他：對本企業(yè)預(yù)算有重要影響的宏觀調(diào)控參數(shù)及決策層必須作出決策的要素進(jìn)行量化得出,企業(yè)財(cái)務(wù)假設(shè),目標(biāo)利潤率：適用于成本加成企業(yè)或目標(biāo)成本倒擠的企業(yè)。 新建固定資產(chǎn)折舊開始時(shí)間表 質(zhì)保費(fèi)率 稅率：增值稅、營業(yè)稅等。 經(jīng)營效率指標(biāo)：應(yīng)收帳款天數(shù)、應(yīng)付帳款天數(shù)、存貨周轉(zhuǎn)天數(shù) 專利使用費(fèi)率/特許權(quán)使用費(fèi)用 財(cái)務(wù)杠桿指標(biāo)：股權(quán)投資與債權(quán)投資的比例 商業(yè)往來票據(jù)的貼現(xiàn)VS背書比例,技術(shù)假設(shè),常見于項(xiàng)目型或投資型公司 需要哪些資源，不同的項(xiàng)目/作業(yè)/投資方案決定了需要不同的資源，而不同的資源對應(yīng)的不定的價(jià)格 技術(shù)假設(shè)表可以在財(cái)務(wù)的指導(dǎo)下由業(yè)務(wù)部門自行設(shè)計(jì)并提供。力求即符合業(yè)務(wù)部門的使用習(xí)慣，又能滿足財(cái)務(wù)的需要。,成本動(dòng)因下的費(fèi)用分?jǐn)偙壤?面積比：各部分占用的廠房面積/總廠房面積 工時(shí)比：各類工序/產(chǎn)品占用的工時(shí)/總工時(shí) 原材料價(jià)值比：各類產(chǎn)品的原材料價(jià)值/總原材料價(jià)值 產(chǎn)品數(shù)量比：各類產(chǎn)品數(shù)量/總產(chǎn)品數(shù)量 員工數(shù)量比 收發(fā)貨次數(shù)比 制造費(fèi)用/管理費(fèi)用分配比例,標(biāo)準(zhǔn)化-所有報(bào)表、模板統(tǒng)一格式，不允許隨便變更。 集中化-同類數(shù)據(jù)/參數(shù)集中在一張表上，便于引用。 公式化-多用公式和函數(shù)，少用常數(shù) 系統(tǒng)化-從輸入模板到輸入報(bào)表，要形成有機(jī)聯(lián)系的系統(tǒng)，而不是一般散沙，彼此獨(dú)立的表格。,預(yù)算編制中的四化原則,用EXCEL建立財(cái)務(wù)分析指標(biāo)體系,直接在三大表聯(lián)動(dòng)的基礎(chǔ)上生成財(cái)務(wù)分析指標(biāo)體系，而方便又快捷，能對管理層的決策改動(dòng)作出最快的響應(yīng)。,盈虧平衡點(diǎn)計(jì)算,價(jià)格的盈虧平衡點(diǎn) 產(chǎn)能/銷量的盈虧平衡點(diǎn) 投資的盈虧平衡點(diǎn) 成本的盈虧平衡點(diǎn),數(shù)據(jù)表在預(yù)算分析中的運(yùn)用,預(yù)算分析中最常見的一類問題就是對決策參數(shù)不確定性的分析。企業(yè)很難斷定明年的人民幣匯率、銀行貸款利率、產(chǎn)品價(jià)格一定就在某個(gè)固定的數(shù)值上，而是只能大致的估計(jì)可能的變動(dòng)范圍，在這種情況下，公司管理層必然要求財(cái)務(wù)部對這些影響企業(yè)利潤的重要參數(shù)的變動(dòng)作敏感性分析。 敏感性分析可能是只針對單變量的，也可能是針對多變量的。如匯率與利率都變化的情況下對公司 的利潤有何影響。,用單/多變量數(shù)據(jù)表完成敏感性分析,例：公司領(lǐng)導(dǎo)需要知道當(dāng)銷量在現(xiàn)有預(yù)算水平上下滑動(dòng)0%-10%時(shí)對利潤水平的影響？ 1.可以用手工測算的方法一個(gè)個(gè)抄到表格上，但下一次要求是測算正負(fù)15%的區(qū)間呢？結(jié)果是面臨巨大的工作量。 2.用單變量數(shù)據(jù)表一定完成，并能自動(dòng)更新。,用單/多變量數(shù)據(jù)表完成敏感性分析,例：領(lǐng)導(dǎo)需要知道當(dāng)銷量在現(xiàn)有預(yù)算水平上下變動(dòng)10%時(shí)對利潤水平的影響？ 1.可以用手工測算的方法一個(gè)個(gè)抄到表格上，需要存20個(gè)表格的計(jì)算結(jié)果。 2.用單變量數(shù)據(jù)表一定完成，并能自動(dòng)更新。,用單/多變量數(shù)據(jù)表完成敏感性分析,假設(shè)明年對稅后凈利潤的最大不確定性來自兩大方面：銀行加息和人民幣升值，試預(yù)測這兩個(gè)條件不同組合區(qū)間對稅后凈利潤的影響。 兩個(gè)決策變量的組合，其組合可