Excel高效财务管理与分析模板应用大全

第1章 建立高效财务管理模板的必备技能

1.1 日常数据的高效处理技能 2

1.1.1 表格的标准化和规范化 2

1.1.2 系统导入表格的标准化和规范化 3

案例1-1 利用“分列”工具修改非法日期 3

案例1-2 利用定位填充公式法快速填充数据 5

案例1-3 利用空格分隔符进行分列 7

案例1-4 利用文本分隔符进行分列 10

案例1-5 使用文本函数分列 11

1.2 常用函数的使用技能 12

1.2.1 制作模板常用的函数 12

1.2.2 逻辑判断函数 13

1.2.3 分类汇总函数：SUMIF函数 13

案例1-6 按照供应商和材料分类汇总（精确匹配和模糊匹配汇总） 13

1.2.4 分类汇总函数：SUMIFS函数 14

案例1-7 按客户按地区和按客户按产品汇总(精确匹配和模糊匹配多

条件汇总) 14

1.2.5 分类汇总函数：SUMPRODUCT函数 14

案例1-8 从管理费用科目余额表中汇总每个费用项目每个月的

总金额 15

1.2.6 查找引用函数：VLOOKUP函数 16

案例1-9 根据固定资产清单制作固定资产查询表 17

案例1-10 根据应收账款清单中的逾期天数判断账龄 18

1.2.7 查找引用函数：MATCH函数和INDEX函数 18

案例1-11 从导入的产品成本数据制作产品成本汇总表 19

1.2.8 查找引用函数：INDIRECT函数（表内滚动循环查找） 20

案例1-12 从应收账款清单制作某个指定客户的明细对账单 21

1.2.9 查找引用函数：INDIRECT函数（表外滚动汇总查找） 24

案例1-13 动态的管理费用预算执行情况滚动跟踪分析 24

1.2.10 查找引用函数：OFFSET函数 25

案例1-14 动态显示两年中各个月份管理费用同比分析 26

1.3 数据透视表应用技能 27

1.3.1 创建动态数据源的数据透视表 27

案例1-15 使用OFFSET函数让数据源变化的时候实时更新 27

1.3.2 通过设置字段汇总依据分析数据 29

案例1-16 制作体现各个部门人数、最低工资、最高工资、人均工资的

报表 29

1.3.3 通过设置字段显示方式分析数据 29

案例1-17 制作各个地区销售额以及各自占比的报表 29

1.3.4 通过自定义计算字段分析数据 30

案例1-18 在透视表中通过公示对字段进行计算 30

1.3.5 通过自定义计算项分析数据 31

案例1-19 在某个字段下做项之间的计算对数据进行分析 31

1.3.6 通过组合字段项目分析数据 33

案例1-20 根据销售流水账制作按年、季度、月进行汇总的报告（组合

日期） 33

案例1-21 对员工的年龄进行组合（组合数字） 34

案例1-22 对原始数据按照费用、季度、月进行灵活分析（组合

文本） 34

1.3.7 基于多个工作表数据汇总的数据透视分析 36

案例1-23 把12个工作表数据汇总到一张工作表上，按部门、费用

进行分析 36

案例1-24 两年制造费用数据工作表按月、年同比分析 39

1.4 动态图表制作技能 41

1.4.1 绘制动态图表的基本原理 42

1.4.2 绘制动态图表的辅助区域方法 43

案例1-25 选择某个产品自动绘制该产品销售数据图表 43

1.4.3 绘制动态图表的动态名称方法 45

案例1-26 利用动态名称绘制动态图表 45

1.4.4 常用表单控件的使用方法 47

1.5 制作高效财务管理模板的理念和思路 50

第2章 资金需求敏感性分析模板

2.1 一个简单的现金流量监控仪表盘 53

2.1.1 模板的特点 53

案例2-1 现金流量净额敏感性分析模型 53

2.1.2 模板的制作方法 54

2.1.3 小知识：关于资金流动图 57

2.2 资金需求敏感性分析模板 58

案例2-2 季度资金需求敏感性分析模型 58

2.2.1 设计资金需求表格 58

2.2.2 绘制资金平衡图 58

2.2.3 小知识：关于资金平衡图 60

2.2.4 小知识：关于整体-组成图 61

案例2-3 各个月的资金平衡图 61

案例2-4 查看每个月各个部门的资金流入和流出及总金额 64

第3章 固定资产管理与分析模板

3.1 建立动态固定资产管理表格 66

3.1.1 表格结构设计 66

3.1.2 基础数据输入 66

3.1.3 创建计算公式 67

3.1.4 将普通区域转换为表 68

3.1.5 固定资产管理表格的使用 68

3.2 制作折旧费用分配表和记账凭证清单 68

3.2.1 制作折旧费用分配表 68

3.2.2 制作记账凭证清单 69

3.3 制作固定资产卡片 69

3.4 制作部门固定资产清单 70

第4章 应付账款管理与分析模板

4.1 供应商管理 72

4.1.1 供应商基本信息 72

4.1.2 制作供应商信息查询表 72

4.2 期初余额 73

4.3 发票管理 73

4.3.1 建立发票记录表 73

4.3.2 付款日提前提醒 74

4.3.3 发票查询 75

4.4 付款管理 76

4.4.1 建立付款记录表 76

4.4.2 付款查询 76

4.5 应付账款核算报表 77

4.5.1 供应商对账单 77

4.5.2 期末余额表 79

第5章 应收账款管理与分析模板

5.1 应收账款信用政策模型 81

5.1.1 信用标准决策 81

案例5-1 从三个信用标准方案中选择 82

5.1.2 信用条件决策 82

案例5-2 从三个信用条件方案中选择 83

5.1.3 收账政策决策 84

案例5-3 在不同收账政策下做收账决策 84

5.1.4 现金折扣决策 85

案例5-4 从三个现金折扣方案中选择最优方案 85

5.1.5 信用政策的综合决策 86

案例5-5 从三个信用政策方案中选择最佳方案 86

5.2 基于手工台账的应收账款管理与分析模板 87

5.2.1 建立购货方资料库 87

5.2.2 建立应收账款发生基础表 87

5.2.3 建立收款明细表 89

5.2.4 收款统计分析 89

5.2.5 未收款账龄分析 91

5.2.6 制作对账单 94

5.3 基于财务软件数据的应收账款分析模板 95

5.3.1 导入基础数据 96

5.3.2 客户余额查询表 96

5.3.3 账龄分析表 97

5.3.4 制作对账单 98

5.4 基于合同管理的销售、回款和欠款分析模板 98

5.4.1 销售、回款和欠款动态分析图表 99

5.4.2 应收账款账龄分析 100

5.4.3 制作超过指定欠款比例和天数的应收账款明细表 101

第6章 管理费用分析模板

6.1 全年管理费用汇总分析 104

6.1.1 基于月度管理费用二维表格的汇总分析模板 104

案例6-1 多重合并计算数据区域数据透视表汇总分析模板 104

6.1.2 基于月度管理费用明细科目的汇总分析模板 109

案例6-2 对12个月的数据进行各种汇总分析模板 109

6.2 与财务软件整合的管理费用月度静态分析模板 113

6.2.1 与金蝶K3整合的管理费用月度分析模板（一级明细） 113

案例6-3 查看指定费用下各个部门费用情况 113

6.2.2 与金蝶K3整合的管理费用月度分析模板（多级明细） 114

案例6-4 通过动态图表对各个分公司、各个费用项目进行综合分析 114

6.2.3 与用友NC整合的管理费用月度分析模板 117

案例6-5 对用友NC导入的管理费用科目余额表中费用进行汇总分析 117

6.3 与财务软件整合的管理费用月度跟踪分析模板 119

6.3.1 与金蝶K3整合的管理费用跟踪分析模板（一级明细） 119

案例6-6 当新的月份数据导入后自动得到管理费用 119

6.3.2 与金蝶K3整合的管理费用跟踪分析模板（多级明细） 120

案例6-7 当新的月份数据导入后自动得到管理费用 120

6.3.3 与用友NC整合的管理费用跟踪分析模板 125

案例6-8 制作动态跟踪分析各项费用变化趋势模版 125

6.4 管理费用预算执行情况跟踪分析模板 127

6.4.1 一个简单的管理费用预算执行情况跟踪分析模板 127

案例6-9 指定部门各个月管理费用和指定月份各个部门管理费用

预算执行情况 127

6.4.2 考虑累计值的预算执行情况跟踪分析 131

案例6-10 指定部门的管理费用预算执行情况动态图表（当月数和

累计数） 131

6.4.3 与财务软件整合的管理费用预算执行情况跟踪分析模板 133

案例6-11 按费用项目以及按部门分别跟踪分析每个月的管理费用

预算执行情况 134

6.4.4 关于差异分析图 136

第7章 成本管理与分析模板

7.1 分解固定成本和变动成本模板 138

7.1.1 利用图表分解成本 138

案例7-1 为总成本-产量曲线添加趋势线 138

7.1.2 利用函数分解成本 139

案例7-2 应用函数SLOPE和INTERCEPT得到单位可变成本和

固定成本 139

7.2 原材料采购价格跟踪分析模板 140

7.2.1 基于采购流水账的采购价格跟踪分析模板 140

案例7-3 对不同时间的采购价格进行分析 140

7.2.2 基于用友U8导入数据的物料采购价格跟踪分析模板 145

案例7-4 对每种物料的采购价格进行分析 145

7.2.3 基于金蝶K3导入数据的BOM跟踪分析模板 147

案例7-5 对各个产品的材料单价进行跟踪分析 147

7.2.4 基于金蝶K3导入数据的BOM材料成本汇总核算模板 152

案例7-6 对很多品种的产品材料成本进行汇总计算 152

7.3 人工成本滚动汇总与分析模板 154

案例7-7 人工成本的分析与各个月薪资表的动态链接和滚动分析 154

7.3.1 薪资表的设计 154

7.3.2 人工成本项目趋势分析 154

7.3.3 企业人工成本趋势分析和部门间对比分析 156

7.3.4 重点人工成本项目分析 159

7.4 与财务软件整合的产品成本动态跟踪分析模板 160

案例7-8 对指定产品的成本变动趋势和成本结构分析 160

7.4.1 产品成本变动趋势跟踪分析 161

7.4.2 产品成本结构跟踪分析 162

7.5 两年成本同比分析模板 163

7.5.1 成本分析模板的设计思路 163

案例7-9 对两年的成本进行同比分析和环比分析 163

7.5.2 确定要控制分析的内容 165

7.5.3 设计指定月份下两年成本同比分析表格 166

7.5.4 指定月份、指定成本类别和项目下两年成本同比分析 图表 166

7.5.5 指定成本类别和项目下两年成本的环比分析和同比分析图表 167

第8章 利润管理与分析模板

8.1 利润因素分析模板 170

8.1.1 利润敏感性分析模型 170

案例8-1 通过滚动条调节四个变量对利润的影响程度 170

8.1.2 本量利分析模型 172

案例8-2 改变产品单价、可变成本和固定成本，就可以观察盈亏

平衡点的变化情况 172

8.2 利润滚动跟踪分析模板 174

案例8-3 根据每个月的利润表对其各个项目的变化趋势进行

跟踪分析 174

8.2.1 获取各月利润表数据 174

8.2.2 滚动汇总分析各月的利润表数据 175

8.2.3 利润表各个项目的趋势数据 175

8.2.4 某个月利润表资金流动分析 176

8.2.5 重要损益指标跟踪分析 178

8.3 历年毛利率跟踪分析模板 181

案例8-4 对各个产品在各个年份的毛利率变化趋势进行综合分析 182

8.3.1 指定产品的历年毛利率趋势分析 182

8.3.2 所有产品的毛利率对比分析 183

本书精选数十个基于Excel的高效财务管理和分析的典型模板，为您介绍这些模板的主要设计思路、方法和技巧，所有案例均来自于作者培训第一线，让您不仅得到一套拿来即可应用到企业财务管理的现成模板，还可训练您学习掌握高效财务模板的制作技能，包括Excel基本技能、资金需求敏感性分析模板、固定资产管理与分析模板、应付账款管理与分析模板、应收账款管理与分析模板、管理费用分析模板、成本管理与分析模板、利润管理和分析模板。通过这些模板的实际制作演练，快速提高您的Excel使用水平，并结合自己企业的实际情况，制作个性化的财务模板。本书适合企事业单位的各类财务管理者，也可作为大专院校经济类本科生、研究生的MBA学员的教材或参考书。

本书由上海倍讯企业管理咨询有限公司首席讲师、资深实战型Excel培训师、应用解决方案专家倾情打造，旨在快速准确对海量财务数据进行全方位、有深度的分析和追踪，既是一套拿来即用的企业财务管理现成模板，也可学习定制符合自己企业的个性化财务模板。
Excel学习乐园QQ群：223192862
范例文件下载地址：http://www.tdpress.com/zyzx/tsscflwj