Excel高效財(cái)務(wù)管理與分析模板應(yīng)用大全

第1章  建立高效財(cái)務(wù)管理模板的必備技能
1.1  日常數(shù)據(jù)的高效處理技能 2
1.1.1  表格的標(biāo)準(zhǔn)化和規(guī)范化 2
1.1.2  系統(tǒng)導(dǎo)入表格的標(biāo)準(zhǔn)化和規(guī)范化 3
案例1-1  利用“分列”工具修改非法日期 3
案例1-2  利用定位填充公式法快速填充數(shù)據(jù) 5
案例1-3  利用空格分隔符進(jìn)行分列 7
案例1-4  利用文本分隔符進(jìn)行分列 10
案例1-5  使用文本函數(shù)分列 11
1.2  常用函數(shù)的使用技能 12
1.2.1  制作模板常用的函數(shù) 12
1.2.2  邏輯判斷函數(shù) 13
1.2.3  分類匯總函數(shù)：SUMIF函數(shù) 13
案例1-6  按照供應(yīng)商和材料分類匯總（精確匹配和模糊匹配匯總） 13
1.2.4  分類匯總函數(shù)：SUMIFS函數(shù) 14
案例1-7  按客戶按地區(qū)和按客戶按產(chǎn)品匯總(精確匹配和模糊匹配多
條件匯總) 14
1.2.5  分類匯總函數(shù)：SUMPRODUCT函數(shù) 14
案例1-8  從管理費(fèi)用科目余額表中匯總每個(gè)費(fèi)用項(xiàng)目每個(gè)月的
總金額 15
1.2.6  查找引用函數(shù)：VLOOKUP函數(shù) 16
案例1-9  根據(jù)固定資產(chǎn)清單制作固定資產(chǎn)查詢表 17
案例1-10  根據(jù)應(yīng)收賬款清單中的逾期天數(shù)判斷賬齡 18
1.2.7  查找引用函數(shù)：MATCH函數(shù)和INDEX函數(shù) 18
案例1-11  從導(dǎo)入的產(chǎn)品成本數(shù)據(jù)制作產(chǎn)品成本匯總表 19
1.2.8  查找引用函數(shù)：INDIRECT函數(shù)（表內(nèi)滾動(dòng)循環(huán)查找） 20
案例1-12  從應(yīng)收賬款清單制作某個(gè)指定客戶的明細(xì)對(duì)賬單 21
1.2.9  查找引用函數(shù)：INDIRECT函數(shù)（表外滾動(dòng)匯總查找） 24
案例1-13  動(dòng)態(tài)的管理費(fèi)用預(yù)算執(zhí)行情況滾動(dòng)跟蹤分析 24
1.2.10  查找引用函數(shù)：OFFSET函數(shù) 25
案例1-14  動(dòng)態(tài)顯示兩年中各個(gè)月份管理費(fèi)用同比分析 26
1.3  數(shù)據(jù)透視表應(yīng)用技能 27
1.3.1  創(chuàng)建動(dòng)態(tài)數(shù)據(jù)源的數(shù)據(jù)透視表 27
案例1-15  使用OFFSET函數(shù)讓數(shù)據(jù)源變化的時(shí)候?qū)崟r(shí)更新 27
1.3.2  通過設(shè)置字段匯總依據(jù)分析數(shù)據(jù) 29
案例1-16  制作體現(xiàn)各個(gè)部門人數(shù)、最低工資、最高工資、人均工資的
報(bào)表 29
1.3.3  通過設(shè)置字段顯示方式分析數(shù)據(jù) 29
案例1-17  制作各個(gè)地區(qū)銷售額以及各自占比的報(bào)表 29
1.3.4  通過自定義計(jì)算字段分析數(shù)據(jù) 30
案例1-18  在透視表中通過公示對(duì)字段進(jìn)行計(jì)算 30
1.3.5  通過自定義計(jì)算項(xiàng)分析數(shù)據(jù) 31
案例1-19  在某個(gè)字段下做項(xiàng)之間的計(jì)算對(duì)數(shù)據(jù)進(jìn)行分析 31
1.3.6  通過組合字段項(xiàng)目分析數(shù)據(jù) 33
案例1-20  根據(jù)銷售流水賬制作按年、季度、月進(jìn)行匯總的報(bào)告（組合
日期） 33
案例1-21  對(duì)員工的年齡進(jìn)行組合（組合數(shù)字） 34
案例1-22  對(duì)原始數(shù)據(jù)按照費(fèi)用、季度、月進(jìn)行靈活分析（組合
文本） 34
1.3.7  基于多個(gè)工作表數(shù)據(jù)匯總的數(shù)據(jù)透視分析 36
案例1-23  把12個(gè)工作表數(shù)據(jù)匯總到一張工作表上，按部門、費(fèi)用
進(jìn)行分析 36
案例1-24  兩年制造費(fèi)用數(shù)據(jù)工作表按月、年同比分析 39
1.4  動(dòng)態(tài)圖表制作技能 41
1.4.1  繪制動(dòng)態(tài)圖表的基本原理 42
1.4.2  繪制動(dòng)態(tài)圖表的輔助區(qū)域方法 43
案例1-25  選擇某個(gè)產(chǎn)品自動(dòng)繪制該產(chǎn)品銷售數(shù)據(jù)圖表 43
1.4.3  繪制動(dòng)態(tài)圖表的動(dòng)態(tài)名稱方法 45
案例1-26  利用動(dòng)態(tài)名稱繪制動(dòng)態(tài)圖表 45
1.4.4  常用表單控件的使用方法 47
1.5  制作高效財(cái)務(wù)管理模板的理念和思路 50
第2章  資金需求敏感性分析模板
2.1  一個(gè)簡(jiǎn)單的現(xiàn)金流量監(jiān)控儀表盤 53
2.1.1  模板的特點(diǎn) 53
案例2-1  現(xiàn)金流量?jī)纛~敏感性分析模型 53
2.1.2  模板的制作方法 54
2.1.3  小知識(shí)：關(guān)于資金流動(dòng)圖 57
2.2  資金需求敏感性分析模板 58
案例2-2  季度資金需求敏感性分析模型 58
2.2.1  設(shè)計(jì)資金需求表格 58
2.2.2  繪制資金平衡圖 58
2.2.3  小知識(shí)：關(guān)于資金平衡圖 60
2.2.4  小知識(shí)：關(guān)于整體-組成圖 61
案例2-3  各個(gè)月的資金平衡圖 61
案例2-4  查看每個(gè)月各個(gè)部門的資金流入和流出及總金額 64
第3章  固定資產(chǎn)管理與分析模板
3.1  建立動(dòng)態(tài)固定資產(chǎn)管理表格 66
3.1.1  表格結(jié)構(gòu)設(shè)計(jì) 66
3.1.2  基礎(chǔ)數(shù)據(jù)輸入 66
3.1.3  創(chuàng)建計(jì)算公式 67
3.1.4  將普通區(qū)域轉(zhuǎn)換為表 68
3.1.5  固定資產(chǎn)管理表格的使用 68
3.2  制作折舊費(fèi)用分配表和記賬憑證清單 68
3.2.1  制作折舊費(fèi)用分配表 68
3.2.2  制作記賬憑證清單 69
3.3  制作固定資產(chǎn)卡片 69
3.4  制作部門固定資產(chǎn)清單 70
第4章  應(yīng)付賬款管理與分析模板
4.1  供應(yīng)商管理 72
4.1.1  供應(yīng)商基本信息 72
4.1.2  制作供應(yīng)商信息查詢表 72
4.2  期初余額 73
4.3  發(fā)票管理 73
4.3.1  建立發(fā)票記錄表 73
4.3.2  付款日提前提醒 74
4.3.3  發(fā)票查詢 75
4.4  付款管理 76
4.4.1  建立付款記錄表 76
4.4.2  付款查詢 76
4.5  應(yīng)付賬款核算報(bào)表 77
4.5.1  供應(yīng)商對(duì)賬單 77
4.5.2  期末余額表 79
第5章  應(yīng)收賬款管理與分析模板
5.1  應(yīng)收賬款信用政策模型 81
5.1.1  信用標(biāo)準(zhǔn)決策 81
案例5-1  從三個(gè)信用標(biāo)準(zhǔn)方案中選擇 82
5.1.2  信用條件決策 82
案例5-2  從三個(gè)信用條件方案中選擇 83
5.1.3  收賬政策決策 84
案例5-3  在不同收賬政策下做收賬決策 84
5.1.4  現(xiàn)金折扣決策 85
案例5-4  從三個(gè)現(xiàn)金折扣方案中選擇最優(yōu)方案 85
5.1.5  信用政策的綜合決策 86
案例5-5  從三個(gè)信用政策方案中選擇最佳方案 86
5.2  基于手工臺(tái)賬的應(yīng)收賬款管理與分析模板 87
5.2.1  建立購(gòu)貨方資料庫(kù) 87
5.2.2  建立應(yīng)收賬款發(fā)生基礎(chǔ)表 87
5.2.3  建立收款明細(xì)表 89
5.2.4  收款統(tǒng)計(jì)分析 89
5.2.5  未收款賬齡分析 91
5.2.6  制作對(duì)賬單 94
5.3  基于財(cái)務(wù)軟件數(shù)據(jù)的應(yīng)收賬款分析模板 95
5.3.1  導(dǎo)入基礎(chǔ)數(shù)據(jù) 96
5.3.2  客戶余額查詢表 96
5.3.3  賬齡分析表 97
5.3.4  制作對(duì)賬單 98
5.4  基于合同管理的銷售、回款和欠款分析模板 98
5.4.1  銷售、回款和欠款動(dòng)態(tài)分析圖表 99
5.4.2  應(yīng)收賬款賬齡分析 100
5.4.3  制作超過指定欠款比例和天數(shù)的應(yīng)收賬款明細(xì)表 101
第6章  管理費(fèi)用分析模板
6.1  全年管理費(fèi)用匯總分析 104
6.1.1  基于月度管理費(fèi)用二維表格的匯總分析模板 104
案例6-1  多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)據(jù)透視表匯總分析模板 104
6.1.2  基于月度管理費(fèi)用明細(xì)科目的匯總分析模板 109
案例6-2  對(duì)12個(gè)月的數(shù)據(jù)進(jìn)行各種匯總分析模板 109
6.2  與財(cái)務(wù)軟件整合的管理費(fèi)用月度靜態(tài)分析模板 113
6.2.1  與金蝶K3整合的管理費(fèi)用月度分析模板（一級(jí)明細(xì)） 113
案例6-3  查看指定費(fèi)用下各個(gè)部門費(fèi)用情況 113
6.2.2  與金蝶K3整合的管理費(fèi)用月度分析模板（多級(jí)明細(xì)） 114
案例6-4  通過動(dòng)態(tài)圖表對(duì)各個(gè)分公司、各個(gè)費(fèi)用項(xiàng)目進(jìn)行綜合分析 114
6.2.3  與用友NC整合的管理費(fèi)用月度分析模板 117
案例6-5  對(duì)用友NC導(dǎo)入的管理費(fèi)用科目余額表中費(fèi)用進(jìn)行匯總分析 117
6.3  與財(cái)務(wù)軟件整合的管理費(fèi)用月度跟蹤分析模板 119
6.3.1  與金蝶K3整合的管理費(fèi)用跟蹤分析模板（一級(jí)明細(xì)） 119
案例6-6  當(dāng)新的月份數(shù)據(jù)導(dǎo)入后自動(dòng)得到管理費(fèi)用 119
6.3.2  與金蝶K3整合的管理費(fèi)用跟蹤分析模板（多級(jí)明細(xì)） 120
案例6-7  當(dāng)新的月份數(shù)據(jù)導(dǎo)入后自動(dòng)得到管理費(fèi)用 120
6.3.3  與用友NC整合的管理費(fèi)用跟蹤分析模板 125
案例6-8  制作動(dòng)態(tài)跟蹤分析各項(xiàng)費(fèi)用變化趨勢(shì)模版 125
6.4  管理費(fèi)用預(yù)算執(zhí)行情況跟蹤分析模板 127
6.4.1  一個(gè)簡(jiǎn)單的管理費(fèi)用預(yù)算執(zhí)行情況跟蹤分析模板 127
案例6-9  指定部門各個(gè)月管理費(fèi)用和指定月份各個(gè)部門管理費(fèi)用
預(yù)算執(zhí)行情況 127
6.4.2  考慮累計(jì)值的預(yù)算執(zhí)行情況跟蹤分析 131
案例6-10  指定部門的管理費(fèi)用預(yù)算執(zhí)行情況動(dòng)態(tài)圖表（當(dāng)月數(shù)和
累計(jì)數(shù)） 131
6.4.3  與財(cái)務(wù)軟件整合的管理費(fèi)用預(yù)算執(zhí)行情況跟蹤分析模板 133
案例6-11  按費(fèi)用項(xiàng)目以及按部門分別跟蹤分析每個(gè)月的管理費(fèi)用
預(yù)算執(zhí)行情況 134
6.4.4  關(guān)于差異分析圖 136
第7章  成本管理與分析模板
7.1  分解固定成本和變動(dòng)成本模板 138
7.1.1  利用圖表分解成本 138
案例7-1  為總成本-產(chǎn)量曲線添加趨勢(shì)線 138
7.1.2  利用函數(shù)分解成本 139
案例7-2  應(yīng)用函數(shù)SLOPE和INTERCEPT得到單位可變成本和
固定成本 139
7.2  原材料采購(gòu)價(jià)格跟蹤分析模板 140
7.2.1  基于采購(gòu)流水賬的采購(gòu)價(jià)格跟蹤分析模板 140
案例7-3  對(duì)不同時(shí)間的采購(gòu)價(jià)格進(jìn)行分析 140
7.2.2  基于用友U8導(dǎo)入數(shù)據(jù)的物料采購(gòu)價(jià)格跟蹤分析模板 145
案例7-4  對(duì)每種物料的采購(gòu)價(jià)格進(jìn)行分析 145
7.2.3  基于金蝶K3導(dǎo)入數(shù)據(jù)的BOM跟蹤分析模板 147
案例7-5  對(duì)各個(gè)產(chǎn)品的材料單價(jià)進(jìn)行跟蹤分析 147
7.2.4  基于金蝶K3導(dǎo)入數(shù)據(jù)的BOM材料成本匯總核算模板 152
案例7-6  對(duì)很多品種的產(chǎn)品材料成本進(jìn)行匯總計(jì)算 152
7.3  人工成本滾動(dòng)匯總與分析模板 154
案例7-7  人工成本的分析與各個(gè)月薪資表的動(dòng)態(tài)鏈接和滾動(dòng)分析 154
7.3.1  薪資表的設(shè)計(jì) 154
7.3.2  人工成本項(xiàng)目趨勢(shì)分析 154
7.3.3  企業(yè)人工成本趨勢(shì)分析和部門間對(duì)比分析 156
7.3.4  重點(diǎn)人工成本項(xiàng)目分析 159
7.4  與財(cái)務(wù)軟件整合的產(chǎn)品成本動(dòng)態(tài)跟蹤分析模板 160
案例7-8  對(duì)指定產(chǎn)品的成本變動(dòng)趨勢(shì)和成本結(jié)構(gòu)分析 160
7.4.1  產(chǎn)品成本變動(dòng)趨勢(shì)跟蹤分析 161
7.4.2  產(chǎn)品成本結(jié)構(gòu)跟蹤分析 162
7.5  兩年成本同比分析模板 163
7.5.1  成本分析模板的設(shè)計(jì)思路 163
案例7-9  對(duì)兩年的成本進(jìn)行同比分析和環(huán)比分析 163
7.5.2  確定要控制分析的內(nèi)容 165
7.5.3  設(shè)計(jì)指定月份下兩年成本同比分析表格 166
7.5.4  指定月份、指定成本類別和項(xiàng)目下兩年成本同比分析 圖表 166
7.5.5  指定成本類別和項(xiàng)目下兩年成本的環(huán)比分析和同比分析圖表 167
第8章  利潤(rùn)管理與分析模板
8.1  利潤(rùn)因素分析模板 170
8.1.1  利潤(rùn)敏感性分析模型 170
案例8-1  通過滾動(dòng)條調(diào)節(jié)四個(gè)變量對(duì)利潤(rùn)的影響程度 170
8.1.2  本量利分析模型 172
案例8-2  改變產(chǎn)品單價(jià)、可變成本和固定成本，就可以觀察盈虧
平衡點(diǎn)的變化情況 172
8.2  利潤(rùn)滾動(dòng)跟蹤分析模板 174
案例8-3  根據(jù)每個(gè)月的利潤(rùn)表對(duì)其各個(gè)項(xiàng)目的變化趨勢(shì)進(jìn)行
跟蹤分析 174
8.2.1  獲取各月利潤(rùn)表數(shù)據(jù) 174
8.2.2  滾動(dòng)匯總分析各月的利潤(rùn)表數(shù)據(jù) 175
8.2.3  利潤(rùn)表各個(gè)項(xiàng)目的趨勢(shì)數(shù)據(jù) 175
8.2.4  某個(gè)月利潤(rùn)表資金流動(dòng)分析 176
8.2.5  重要損益指標(biāo)跟蹤分析 178
8.3  歷年毛利率跟蹤分析模板 181
案例8-4  對(duì)各個(gè)產(chǎn)品在各個(gè)年份的毛利率變化趨勢(shì)進(jìn)行綜合分析 182
8.3.1  指定產(chǎn)品的歷年毛利率趨勢(shì)分析 182
8.3.2  所有產(chǎn)品的毛利率對(duì)比分析 183