中財訊201108上海課件-Excel高效財務管理應用韓良智.ppt

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

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

3、.4 縮位顯示數(shù)據(jù),1.2.1 數(shù)值型數(shù)據(jù)的輸入,輸入負數(shù) 輸入分數(shù) 常規(guī)數(shù)字格式與科學計數(shù)法 有效數(shù)字位數(shù)的限制,1.2.2 與數(shù)值型數(shù)據(jù)有關的函數(shù),MAX函數(shù)：求最大值。 MIN函數(shù)：求最小值。 AVERAGE函數(shù)：求平均數(shù)。 ROUND函數(shù)：四舍五入。 INT函數(shù)：無條件舍去小數(shù)，保留整數(shù)部分。 【例1-2】處理計算誤差,1.2.3 將小寫金額轉換為大寫,使用特殊格式顯示大寫數(shù)字 使用NUMBERSTRING函數(shù) 【例1-3】小寫金額轉換成大寫,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ù)的計算 1.3.3 與日期型數(shù)據(jù)有關的函數(shù) 1.3.4 查找與修改非法日期,1.3.1 日期型數(shù)據(jù)的輸入及顯示格式,日期型數(shù)據(jù)的輸入 日期型數(shù)據(jù)的顯示格式 常規(guī)顯示格式 自定義顯示格式,1.3.2 日期型數(shù)據(jù)的計算,日期型數(shù)據(jù)計算的應用情況 計算應收賬款賬齡 計算應付賬款賬齡 計算工齡 設置常規(guī)數(shù)據(jù)格式,1.

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

6、 時間型數(shù)據(jù)的計算 1.4.3 與時間型數(shù)據(jù)有關的函數(shù),1.4.1 時間型數(shù)據(jù)的輸入及顯示格式,時間型數(shù)據(jù)的輸入 時間型數(shù)據(jù)的顯示格式 常規(guī)顯示格式 自定義顯示格式,1.4.2 時間型數(shù)據(jù)的計算,【例1-5】計算工作時數(shù) 注意跨越午夜的情況,1.4.3 與時間型數(shù)據(jù)有關的函數(shù),NOW函數(shù)：返回當前日期和時間所對應的序列號。 HOUR函數(shù)：用于獲取時間值的小時數(shù)。 MINUTE函數(shù)：用于獲取時間值的分鐘數(shù)。 SECOND函數(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ù)表綜合練習,1.5.1 快速刪除數(shù)據(jù)表中的所有空行,排序法 篩選法 定位法 【例1-6】快速刪除所有空行,1.5.2 快速填充數(shù)據(jù)表中的所有空單元格,快速填充表格中的所有空單元格 將空單元格填充為上一個單元格數(shù)據(jù) 【例1-7】快速填充數(shù)據(jù),1.5.3 利用分列工具高效整理數(shù)據(jù),【例1-8】整理銀行對賬單,整理前,整理后,1.5.4 整理數(shù)據(jù)表綜合練習,快速整理從ERP中導入的數(shù)據(jù)表 【例1-9】整理數(shù)據(jù)綜合練習,2 營運資金管理,2.1 現(xiàn)金管理 2.2 應收賬款管理 2.3 存貨管理 2.4 應付賬款管理,2.1 現(xiàn)金管理,2.1.1 普通日記賬與現(xiàn)金日記賬 2.1.2 現(xiàn)

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

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

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

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

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

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

14、.3 成本費用分析圖表,3.3.1 費用預算執(zhí)行情況分析圖 3.3.2 動態(tài)費用分析圖表,3.3.1 費用預算執(zhí)行情況分析圖,【例3-11】 組合圖表 數(shù)據(jù)系列格式設置,3.3.2 動態(tài)費用分析圖表,【例3-12】可選定項目的費用折線圖 INDIRECT函數(shù) ADDRESS函數(shù) CELL函數(shù) ROW函數(shù) COLUMN函數(shù),4 銷售管理,4.1 銷售數(shù)據(jù)的透視分析 4.2 數(shù)據(jù)透視表的高級應用 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、運用數(shù)據(jù)透視表多角度分析財務數(shù)據(jù) 4.1.4 制作與使用數(shù)據(jù)透視圖 【例4-1】銷售數(shù)據(jù)透視分析,4.1.1 制作數(shù)據(jù)透視表的基本方法,【數(shù)據(jù)】/【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】 合理設置數(shù)據(jù)透視表的布局 相關概念 頁字段 行字段 列字段 數(shù)據(jù)字段,4.1.2 數(shù)據(jù)透視表的編輯與更新方法,修改字段名稱 設置字段的數(shù)字格式 取消數(shù)據(jù)透視表行總計或列總計 取消字段的分類匯總 合并數(shù)據(jù)標志 設置錯誤值或空值的顯示方式 更新數(shù)據(jù)透視表的數(shù)據(jù),4.1.3 運用數(shù)據(jù)透視表多角度分析銷售數(shù)據(jù) 修改數(shù)據(jù)透視表布局 顯示明細數(shù)據(jù) 4.1.4 制作與使用數(shù)據(jù)透視圖 在數(shù)據(jù)清單基礎上制作數(shù)據(jù)透視圖 在數(shù)據(jù)透視表基礎上制作

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

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

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

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

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