中財(cái)訊201108上海課件-Excel高效財(cái)務(wù)管理應(yīng)用韓良智.ppt

1、Excel 高效財(cái)務(wù)管理應(yīng)用,主講教師 韓良智 教授 北京科技大學(xué)經(jīng)濟(jì)管理學(xué)院,目錄,1 運(yùn)用Excel高效處理數(shù)據(jù) 2 營運(yùn)資金管理 3 成本費(fèi)用管理 4 銷售管理 5 長期投資與融資決策 6 財(cái)務(wù)預(yù)測與財(cái)務(wù)報(bào)表分析,1 運(yùn)用Excel高效處理數(shù)據(jù),1.1 文本型數(shù)據(jù)及其處理方法 1.2 數(shù)值型數(shù)據(jù)及其處理方法 1.3 日期型數(shù)據(jù)及其處理方法 1.4 時(shí)間型數(shù)據(jù)及其處理方法 1.5 高效整理數(shù)據(jù)表,1.1 文本型數(shù)據(jù)及其處理方法,1.1.1 文本型數(shù)據(jù)的輸入及文本運(yùn)算符 1.1.2 常用文本函數(shù)的應(yīng)用 1.1.3 將文本型數(shù)字轉(zhuǎn)化為純數(shù)字,1.1.1 文本型數(shù)據(jù)的輸入及文本運(yùn)算符,文本型數(shù)字

2、的輸入 設(shè)置單元格格式后輸入 使用單引號(hào) 使用雙引號(hào) 文本運(yùn)算符 &符號(hào),1.1.2 常用文本函數(shù)的應(yīng)用,LEFT函數(shù)：從左邊取指定長度的字符。 RIGHT函數(shù)：從右邊取指定長度的字符。 MID函數(shù)：從指定位置取指定長度的字符。 TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為指定格式的文本。 ISTEXT函數(shù)：檢驗(yàn)數(shù)據(jù)是否為文本。,1.1.3 將文本型數(shù)字轉(zhuǎn)化為純數(shù)字,利用智能標(biāo)記 選擇性粘貼 利用VALUE函數(shù) 利用公式 刪除數(shù)字中的特殊字符 【例1-1】 文本型數(shù)據(jù)的處理,1.2 數(shù)值型數(shù)據(jù)及其處理方法,1.2.1 數(shù)值型數(shù)據(jù)的輸入 1.2.2 與數(shù)值型數(shù)據(jù)有關(guān)的函數(shù) 1.2.3 將小寫金額轉(zhuǎn)換為大寫 1.2

3、.4 縮位顯示數(shù)據(jù),1.2.1 數(shù)值型數(shù)據(jù)的輸入,輸入負(fù)數(shù) 輸入分?jǐn)?shù) 常規(guī)數(shù)字格式與科學(xué)計(jì)數(shù)法 有效數(shù)字位數(shù)的限制,1.2.2 與數(shù)值型數(shù)據(jù)有關(guān)的函數(shù),MAX函數(shù)：求最大值。 MIN函數(shù)：求最小值。 AVERAGE函數(shù)：求平均數(shù)。 ROUND函數(shù)：四舍五入。 INT函數(shù)：無條件舍去小數(shù)，保留整數(shù)部分。 【例1-2】處理計(jì)算誤差,1.2.3 將小寫金額轉(zhuǎn)換為大寫,使用特殊格式顯示大寫數(shù)字 使用NUMBERSTRING函數(shù) 【例1-3】小寫金額轉(zhuǎn)換成大寫,1.2.4 縮位顯示數(shù)據(jù),使用自定義數(shù)字格式縮位顯示數(shù)據(jù)代碼： 縮小1百位 0.00 縮小1千位顯示數(shù)字： 0.00， 縮小1萬位顯示數(shù)字： 0

4、!.0, 縮小10萬位顯示數(shù)字： 0!.00, 縮小100萬位顯示數(shù)字： 0.00, 縮小1000萬位顯示數(shù)字： 0!.0, 縮小1億位顯示數(shù)字： 0!.00, 縮小10億位顯示數(shù)字： 0.00,1.3 日期型數(shù)據(jù)及其處理方法,1.3.1 日期型數(shù)據(jù)的輸入及顯示格式 1.3.2 日期型數(shù)據(jù)的計(jì)算 1.3.3 與日期型數(shù)據(jù)有關(guān)的函數(shù) 1.3.4 查找與修改非法日期,1.3.1 日期型數(shù)據(jù)的輸入及顯示格式,日期型數(shù)據(jù)的輸入 日期型數(shù)據(jù)的顯示格式 常規(guī)顯示格式 自定義顯示格式,1.3.2 日期型數(shù)據(jù)的計(jì)算,日期型數(shù)據(jù)計(jì)算的應(yīng)用情況 計(jì)算應(yīng)收賬款賬齡 計(jì)算應(yīng)付賬款賬齡 計(jì)算工齡 設(shè)置常規(guī)數(shù)據(jù)格式,1.

5、3.3 與日期型數(shù)據(jù)有關(guān)的函數(shù),TODAY函數(shù)：返回系統(tǒng)當(dāng)前日期的序列號(hào)。 DATE函數(shù)：將三個(gè)數(shù)字組成一個(gè)日期序列號(hào)。 YEAR函數(shù)：獲取指定日期序列號(hào)的年份數(shù)字。 MONTH函數(shù)：獲取指定日期序列號(hào)的月份數(shù)字。 DAY函數(shù)：獲取指定日期序列號(hào)的日數(shù)字。 WEEKDAY：獲取某日期為星期幾的數(shù)字。,1.3.4 查找與修改非法日期,查找非法日期 利用“單元格格式”對話框判斷 使用ISNUMBER函數(shù)判斷 設(shè)置數(shù)據(jù)有效性并圈釋無效數(shù)據(jù) 修改非法日期 查找與替換 使用函數(shù) 使用分列工具 【例1-4】處理非法日期數(shù)據(jù),1.4 時(shí)間型數(shù)據(jù)及其處理方法,1.4.1 時(shí)間型數(shù)據(jù)的輸入及顯示格式 1.4.2

6、 時(shí)間型數(shù)據(jù)的計(jì)算 1.4.3 與時(shí)間型數(shù)據(jù)有關(guān)的函數(shù),1.4.1 時(shí)間型數(shù)據(jù)的輸入及顯示格式,時(shí)間型數(shù)據(jù)的輸入 時(shí)間型數(shù)據(jù)的顯示格式 常規(guī)顯示格式 自定義顯示格式,1.4.2 時(shí)間型數(shù)據(jù)的計(jì)算,【例1-5】計(jì)算工作時(shí)數(shù) 注意跨越午夜的情況,1.4.3 與時(shí)間型數(shù)據(jù)有關(guān)的函數(shù),NOW函數(shù)：返回當(dāng)前日期和時(shí)間所對應(yīng)的序列號(hào)。 HOUR函數(shù)：用于獲取時(shí)間值的小時(shí)數(shù)。 MINUTE函數(shù)：用于獲取時(shí)間值的分鐘數(shù)。 SECOND函數(shù)：用于獲取時(shí)間值的秒數(shù)。,1.5 高效整理數(shù)據(jù)表,1.5.1 快速刪除數(shù)據(jù)表中的所有空行 1.5.2 快速填充數(shù)據(jù)表中的所有空單元格 1.5.3 利用分列工具高效整理數(shù)據(jù) 1

7、.5.4 整理數(shù)據(jù)表綜合練習(xí),1.5.1 快速刪除數(shù)據(jù)表中的所有空行,排序法 篩選法 定位法 【例1-6】快速刪除所有空行,1.5.2 快速填充數(shù)據(jù)表中的所有空單元格,快速填充表格中的所有空單元格 將空單元格填充為上一個(gè)單元格數(shù)據(jù) 【例1-7】快速填充數(shù)據(jù),1.5.3 利用分列工具高效整理數(shù)據(jù),【例1-8】整理銀行對賬單,整理前,整理后,1.5.4 整理數(shù)據(jù)表綜合練習(xí),快速整理從ERP中導(dǎo)入的數(shù)據(jù)表 【例1-9】整理數(shù)據(jù)綜合練習(xí),2 營運(yùn)資金管理,2.1 現(xiàn)金管理 2.2 應(yīng)收賬款管理 2.3 存貨管理 2.4 應(yīng)付賬款管理,2.1 現(xiàn)金管理,2.1.1 普通日記賬與現(xiàn)金日記賬 2.1.2 現(xiàn)

8、金預(yù)算表及其合并計(jì)算,2.1.1 普通日記賬與現(xiàn)金日記賬,【例2-1】普通日記賬與現(xiàn)金日記賬 整理標(biāo)準(zhǔn)數(shù)據(jù)清單 篩選現(xiàn)金日記賬 計(jì)算現(xiàn)金余額 設(shè)置條件格式,2.1.2 現(xiàn)金預(yù)算表及其合并計(jì)算,【例2-2】 數(shù)據(jù)/合并計(jì)算 被合并計(jì)算的數(shù)據(jù)區(qū)域結(jié)構(gòu)應(yīng)相同,2.2 應(yīng)收賬款管理,2.2.1 建立應(yīng)收賬款臺(tái)賬 2.2.2 應(yīng)收賬款的排序分析 2.2.3 應(yīng)收賬款的篩選分析 2.2.4 應(yīng)收賬款分類匯總分析 2.2.5 制作應(yīng)收賬款賬齡分析表,2.2.1建立應(yīng)收賬款臺(tái)賬,【例2-3】 利用記錄單輸入和編輯數(shù)據(jù) 數(shù)據(jù)/記錄單 拆分工作表 窗口/拆分,2.2.2 應(yīng)收賬款的排序分析,【例2-4】 數(shù)據(jù)/排

9、序 一個(gè)關(guān)鍵字的情況 多個(gè)關(guān)鍵字的情況,2.2.3 應(yīng)收賬款的篩選分析,【例2-5】 數(shù)據(jù)/篩選 自動(dòng)篩選 高級(jí)篩選 一個(gè)條件的情況 多個(gè)條件的情況,2.2.4 應(yīng)收賬款分類匯總分析,【例2-6】 建立分類匯總 數(shù)據(jù)/分類匯總 查看分類匯總結(jié)果 復(fù)制分類匯總結(jié)果 選擇可見單元格 刪除分類匯總,2.2.5 制作應(yīng)收賬款賬齡分析表,【例2-7】 SUMIF函數(shù)根據(jù)指定條件對若干單元格求和。 SUMPRODUCT函數(shù)將給定的幾個(gè)數(shù)組的對應(yīng)的元素相乘，并返回乘積之和。,2.3 存貨管理,2.3.1 建立存貨收發(fā)存明細(xì)表 2.3.2 存貨臺(tái)帳及存貨查詢 2.3.3 存貨的ABC分類模型 2.3.4 經(jīng)濟(jì)

10、訂貨批量模型,2.3.1 建立存貨收發(fā)存明細(xì)表,【例2-8】 為整列定義名稱 刪除網(wǎng)格線 運(yùn)用條件格式設(shè)置邊框和字體顏色,2.3.2 存貨查詢,【例2-9】 VLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 COLUMN函數(shù)返回給定引用的列標(biāo)。,2.3.3 存貨的ABC分類模型,存貨的分類 A類存貨關(guān)鍵的少數(shù) C類存貨不重要的多數(shù) B類存貨居中的類別 常見的分類標(biāo)準(zhǔn)：,2.3.3 存貨的ABC分類模型,【例2-10】 COUNTA函數(shù)返回參數(shù)列表中非空值的單元格個(gè)數(shù)。 COUNTIF函數(shù)計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。 繪制兩軸線柱形圖

11、,2.3.3 經(jīng)濟(jì)訂貨批量模型,存貨的相關(guān)總成本（總訂儲(chǔ)費(fèi)用） 經(jīng)濟(jì)訂貨批量 最佳的訂貨次數(shù) 最低的總訂儲(chǔ)費(fèi)用 式中：Q為一次訂貨批量；D為一定時(shí)期存貨的需求量；A為一次訂貨費(fèi)；P為存貨單價(jià)；K為存貨的存儲(chǔ)費(fèi)率；PK即為單位存儲(chǔ)費(fèi)用。,2.3.4 經(jīng)濟(jì)訂貨批量模型,【例2-11】計(jì)算經(jīng)濟(jì)訂貨批量 SQRT函數(shù)開平方 建立和使用自定義函數(shù) 工具/宏/Visual Basic編輯器 插入/模塊 插入/過程,2.4 應(yīng)付賬款管理,應(yīng)付賬款到期日的計(jì)算 EDATE函數(shù)計(jì)算出所指定月數(shù)之前或之后的日期 EOMONTH函數(shù)計(jì)算指定日期到月底的天數(shù) 應(yīng)付賬款到期日提前提醒 設(shè)置條件格式 【例2-12】應(yīng)付賬

12、款管理,3 成本費(fèi)用管理,3.1成本費(fèi)用的日常管理 3.2 制作成本費(fèi)用匯總表 3.3 成本費(fèi)用分析圖表,3.1 成本費(fèi)用的日常管理,3.1.1 制作限額領(lǐng)料單 3.1.2 快速編制原材料費(fèi)用預(yù)算 3.1.3 工資數(shù)據(jù)管理 3.1.4 快速制作工資條 3.1.5 期間費(fèi)用管理,3.1.1 制作限額領(lǐng)料單,【例3-1】制作限額領(lǐng)料單 數(shù)據(jù)有效性的設(shè)置 隱藏行和列 保護(hù)單元格和保護(hù)工作表 為工作簿設(shè)置密碼,3.1.2 快速編制原材料費(fèi)用預(yù)算,【例3-2】計(jì)算原材料成本 【例3-3】編制材料需求量預(yù)算表 MMULT函數(shù)返回兩數(shù)組的矩陣乘積,3.1.3 工資費(fèi)用管理,【例3-4】計(jì)算基本工資和工齡工資

13、 根據(jù)職稱確定基本工資 根據(jù)工齡計(jì)算工齡工資 【例3-5】計(jì)算銷售人員年度工資和獎(jiǎng)金 HLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。,3.1.3 工資費(fèi)用管理,【例3-6】計(jì)算個(gè)人所得稅 使用IF函數(shù)計(jì)算 建立自定義函數(shù)計(jì)算 【例3-7】工資數(shù)據(jù)管理 對單元格區(qū)域數(shù)據(jù)進(jìn)行相同的修改 標(biāo)示最大值和最小值,3.1.4 快速制作工資條,【例3-8】快速制作工資條,3.2 期間費(fèi)用管理,3.2.1 費(fèi)用項(xiàng)目分類過賬 【例3-9】 3.2.1 營業(yè)費(fèi)用自動(dòng)匯總表 【例3-10】 INDIRECT函數(shù)返回由文本字符串指定的引用。 ROW函數(shù)返回行號(hào),3

14、.3 成本費(fèi)用分析圖表,3.3.1 費(fèi)用預(yù)算執(zhí)行情況分析圖 3.3.2 動(dòng)態(tài)費(fèi)用分析圖表,3.3.1 費(fèi)用預(yù)算執(zhí)行情況分析圖,【例3-11】 組合圖表 數(shù)據(jù)系列格式設(shè)置,3.3.2 動(dòng)態(tài)費(fèi)用分析圖表,【例3-12】可選定項(xiàng)目的費(fèi)用折線圖 INDIRECT函數(shù) ADDRESS函數(shù) CELL函數(shù) ROW函數(shù) COLUMN函數(shù),4 銷售管理,4.1 銷售數(shù)據(jù)的透視分析 4.2 數(shù)據(jù)透視表的高級(jí)應(yīng)用 4.3 高效匯總多重區(qū)域的銷售數(shù)據(jù) 4.4 使用函數(shù)匯總銷售額 4.5 繪制銷售圖表,4.1 銷售數(shù)據(jù)的透視分析,4.1.1 制作數(shù)據(jù)透視表的基本方法 4.1.2 數(shù)據(jù)透視表的編輯與更新方法 4.1.3

15、運(yùn)用數(shù)據(jù)透視表多角度分析財(cái)務(wù)數(shù)據(jù) 4.1.4 制作與使用數(shù)據(jù)透視圖 【例4-1】銷售數(shù)據(jù)透視分析,4.1.1 制作數(shù)據(jù)透視表的基本方法,【數(shù)據(jù)】/【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】 合理設(shè)置數(shù)據(jù)透視表的布局 相關(guān)概念 頁字段 行字段 列字段 數(shù)據(jù)字段,4.1.2 數(shù)據(jù)透視表的編輯與更新方法,修改字段名稱 設(shè)置字段的數(shù)字格式 取消數(shù)據(jù)透視表行總計(jì)或列總計(jì) 取消字段的分類匯總 合并數(shù)據(jù)標(biāo)志 設(shè)置錯(cuò)誤值或空值的顯示方式 更新數(shù)據(jù)透視表的數(shù)據(jù),4.1.3 運(yùn)用數(shù)據(jù)透視表多角度分析銷售數(shù)據(jù) 修改數(shù)據(jù)透視表布局 顯示明細(xì)數(shù)據(jù) 4.1.4 制作與使用數(shù)據(jù)透視圖 在數(shù)據(jù)清單基礎(chǔ)上制作數(shù)據(jù)透視圖 在數(shù)據(jù)透視表基礎(chǔ)上制作

16、數(shù)據(jù)透視圖,4.2 數(shù)據(jù)透視表的高級(jí)應(yīng)用,4.2.1 通過組合字段創(chuàng)建多維數(shù)據(jù)匯總表 4.2.2 通過設(shè)置字段顯示方式創(chuàng)建各種比較分析表 4.2.3 通過自定義設(shè)置靈活編制銷售數(shù)據(jù)匯總表,4.2.1 通過組合字段創(chuàng)建多維銷售數(shù)據(jù)匯總表,按不同字段分組查看數(shù)據(jù) 按日期分組 按地區(qū)分組 按商品類別分組,4.2.2 通過設(shè)置字段顯示方式創(chuàng)建各種比較分析表,差異或差異百分比 占同行或同列百分比 占總合百分比 累計(jì)值顯示 縱向差異比較,4.2.3 通過自定義設(shè)置靈活編制數(shù)據(jù)匯總表,添加自定義計(jì)算字段 添加自定義計(jì)算項(xiàng),4.3 高效匯總多重區(qū)域銷售數(shù)據(jù),【例4-2】高效匯總多重區(qū)域的銷售數(shù)據(jù) 無論擬匯總的

17、數(shù)據(jù)區(qū)域結(jié)構(gòu)是否相同均適用,4.4 使用函數(shù)匯總銷售額,【例4-3】按客戶匯總銷售額 SUMIF函數(shù)條件求和,4.4 繪制銷售圖表,4.4.1 制作組合銷售圖表 【例4-4】 4.4.2 制作銷售預(yù)算完成進(jìn)度圖表 【例4-5】 4.4.3 繪制動(dòng)態(tài)銷售圖表 【例4-6】 OFFSET函數(shù)以指定的引用為參照系，通過給定偏移量返回新的引用 使用單選按鈕控件,5 長期投資與融資決策,5.1 常用財(cái)務(wù)函數(shù)的應(yīng)用 5.2 財(cái)務(wù)規(guī)劃工具的運(yùn)用 5.3 投資風(fēng)險(xiǎn)分析,5.1 常用財(cái)務(wù)函數(shù)的應(yīng)用,5.1.1 計(jì)算終值的函數(shù) 5.1.2 計(jì)算現(xiàn)值的函數(shù) 5.1.3 計(jì)算內(nèi)部收益率的函數(shù) 5.1.4 計(jì)算投資期的

18、函數(shù) 5.1.5 計(jì)算等值年金的相關(guān)函數(shù),5.1.1 計(jì)算終值的函數(shù),FV函數(shù)計(jì)算復(fù)利終值 【例5-1】計(jì)算終值 公式審核 追蹤引用單元格 追蹤從屬單元格,5.1.2 計(jì)算現(xiàn)值的函數(shù),PV函數(shù)計(jì)算復(fù)利現(xiàn)值。 NPV函數(shù)計(jì)算一系列現(xiàn)金流的現(xiàn)值。 XNPV計(jì)算非固定間隔期發(fā)生的一系列現(xiàn)金流的現(xiàn)值。 【例5-2】計(jì)算現(xiàn)值與凈現(xiàn)值 【例5-3】制作現(xiàn)值系數(shù)表 運(yùn)用數(shù)組公式,5.1.3 計(jì)算內(nèi)部收益率的函數(shù),RATE函數(shù)返回未來款項(xiàng)的各期利率。 IRR函數(shù)返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。 XIRR函數(shù)返回一組現(xiàn)金流的內(nèi)部收益率，這些現(xiàn)金流不一定定期發(fā)生。 【例5-4】計(jì)算投資收益率,5.1.4

19、計(jì)算投資期的函數(shù),NPER函數(shù)基于固定利率及等額分期付款方式，返回某項(xiàng)投資（或貸款）的總期數(shù)。 【例5-5】計(jì)算投資回收期,5.1.5 計(jì)算年金的相關(guān)函數(shù),PMT函數(shù)基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。 IPMT函數(shù)基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內(nèi)的利息償還額。 PPMT函數(shù)基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內(nèi)的本金償還額。 【例5-6】分期等額攤還貸款,5.2 財(cái)務(wù)規(guī)劃工具的運(yùn)用,5.2.1 模擬運(yùn)算表工具的運(yùn)用 5.2.2 單變量求解工具的運(yùn)用 5.2.3 規(guī)劃求解工具的運(yùn)用,5.2.1 模擬運(yùn)算表工具的運(yùn)用,

20、【數(shù)據(jù)】/【模擬運(yùn)算表】 單變量模擬運(yùn)算表 雙變量模擬運(yùn)算表 【例5-7】每期還款額的模擬運(yùn)算,5.2.2 單變量求解工具的運(yùn)用,在目標(biāo)單元格中輸入公式 公式中引用可變單元格 【工具】/【單變量求解】 【例5-8】實(shí)現(xiàn)目標(biāo)利潤的措施分析,5.2.3 規(guī)劃求解工具的運(yùn)用,在目標(biāo)單元格中輸入公式 在相關(guān)單元格中建立約束條件 公式中引用可變單元格 【工具】/【規(guī)劃求解】 【例5-9】投資組合規(guī)劃,5.3 投資項(xiàng)目的綜合評(píng)價(jià),【例5-10】 計(jì)算凈現(xiàn)金流量 繪制現(xiàn)金流量圖 計(jì)算評(píng)價(jià)指標(biāo) 做出決策結(jié)論,6 財(cái)務(wù)預(yù)測與財(cái)務(wù)報(bào)表分析,6.1 財(cái)務(wù)預(yù)測 6.2 財(cái)務(wù)報(bào)表分析,6.1 財(cái)務(wù)預(yù)測,6.1.1 利用相關(guān)函數(shù)預(yù)測 6.1.2 利用數(shù)據(jù)分析工具預(yù)測 6.1.3 利用繪圖工具預(yù)測,6.1.1 利用相關(guān)函數(shù)預(yù)測,常用的線性預(yù)測函數(shù) INTERCEPT函數(shù)求回歸直線的截距 SLOPE求回歸直線的斜率。 TREND函數(shù)返回一條線性回歸擬合線的值。