财务精英都是Excel控:CFO手把手教你学管理会计

CHAPTER01当管理会计遇上Excel1

1.1管理会计与Excel的不了情...............................................................................2

1.1.1当会计遇上管理所迸发的火花.............................................................................2

1.1.2管理会计？还是财务会计.....................................................................................3

1.1.3与君初相识，蹒跚初学步.....................................................................................3

1.2至快至强，快人一步..........................................................................................7

1.2.1快入——从源头开始的快速输入..........................................................................7

1.2.2快选——单元格的快速选择...............................................................................10

1.2.3快找——单元格的快速定位与查找替换.............................................................12

1.3去粗存精，去伪存真........................................................................................14

1.3.1查与删——显现和成批删除重复项....................................................................14

1.3.2禁与输——重复项目的禁止和批量输入.............................................................16

1.3.3一起找不同——双工作簿比较...........................................................................17

1.3.4快乐修理工——不规则文本的修整与转换.........................................................20

1.3.5快乐修理工——日期和时间的整理与转换.........................................................21

1.3.6快乐修理工——合并单元格的反合并.................................................................21

1.4批量处理，事半功倍........................................................................................22

1.4.1批量单元格——多单元格区域和整行整列.........................................................22

1.4.2批量单元格——Ctrl_F快算月度小计与年度累计...............................................24

1.4.3批量工作表——多表填充、清除、汇总与合并计算...........................................25

1.4.4批量工作簿——开关、打印和透视....................................................................27

1.5不忘初心，学无止境........................................................................................27

1.5.1让你的管理会计在Excel中666........................................................................27

1.5.2玩转Excel，为自己疯狂打CALL吧..................................................................30

CHAPTER02集团营销管理大会的筹办31

2.1办公室来了个小白............................................................................................32

2.1.1小公“举”的芳龄.............................................................................................32

2.1.2办公室的这群人.................................................................................................33

2.2新人到岗的头把火............................................................................................33

2.2.1着急的小吴........................................................................................................33

2.2.2公司的销售新政.................................................................................................34

2.2.3让小白更明白.....................................................................................................35

2.3财务视角的销售管理........................................................................................38

2.3.1营销管理、销售管理和销售业绩管理.................................................................38

2.3.2财务管理推动销售管理......................................................................................38

2.4一行公式搞定业绩提成....................................................................................39

2.4.1实现自动查找的LOOKUP、VLOOKUP和HLOOKUP函数.............................39

2.4.2用数组函数解决累进加成的业绩计算.................................................................42

2.4.3用TEXT函数进行业绩提成计算修正................................................................42

2.5用VLOOKUP等函数实现跨文件取数并取得机械版块全部提成......................45

2.5.1原样复制表格的两种方法...................................................................................45

2.5.2用VLOOKUP函数从关联文件中自动取值........................................................47

2.5.3找回Excel的实心黑十字光标............................................................................50

2.5.4嵌套CHOOSE函数自动分配业绩任务数..........................................................51

2.5.5简单的SUM函数求得完整的机械版块业绩提成................................................52

2.6用函数实现多家二级公司明细汇总..................................................................53

2.6.1INDIRECT函数和CELL函数初接触.................................................................53

2.6.2待汇总文件路径、名称及单元格分析.................................................................55

2.6.3INDIRECT函数和CELL函数在业绩汇总中的综合运用....................................55

2.7数据透视表生成之基于当前工作表指定区域....................................................57

2.7.1基于当前工作表指定区域快速生成数据透视表..................................................57

2.7.2美化和调整数据透视表......................................................................................58

2.7.3弹指间，多维度多角度的业绩分析了然于胸......................................................61

2.7.4数据透视表汇总的那些事儿...............................................................................62

2.7.5从数据透视表中速查明细...................................................................................64

2.7.6批量修改数据透视表求和等多种统计方式.........................................................64

2.8数据透视表生成之基于当前工作表动态区域....................................................65

2.8.1移动的矩形块：OFFSET函数............................................................................65

2.8.2非空单元格统计者：COUNTA函数....................................................................66

2.8.3新建一个动态区域名称......................................................................................66

2.8.4基于当前工作表动态区域名称快速生成数据透视表...........................................67

2.8.5检验新增销售明细后动态数据源数据透视表的变化...........................................67

2.9静享下午茶时光：多重区域拼合行数列数都不同的多表..................................68

2.9.1瞧这一家人一天的开支......................................................................................68

2.9.2将数据透视表向导找出来...................................................................................69

2.9.3利用数据透视表向导生成多重区域餐饮透视表..................................................69

2.10数据透视表生成之基于同一工作簿文件的不同工作表....................................71

2.10.1初识简单的SQL语句......................................................................................71

2.10.2西南区销售明细多表合一的SQL语句.............................................................72

2.10.3多表的各列的个数不同、顺序不同时的SQL语句...........................................73

2.10.4新建数据连接生成基于同一工作簿多工作表的数据透视表..............................73

2.10.5利用现有的数据连接生成基于多工作表的数据透视表.....................................77

2.10.6编辑和删除现有的数据透视表数据连接...........................................................77

2.11数据透视表生成之基于不同工作簿文件不同工作表.......................................78

2.11.1进一步整理的SQL语句...................................................................................78

2.11.2新建数据连接制作来源于多工作簿、多工作表的明细清单..............................80

2.11.3利用现有的数据连接动态生成基于多工作簿的明细清单..................................81

2.11.4新建数据连接生成基于多工作簿、多工作表的数据透视表..............................82

2.12锦上添花：让数据透视表更具创意性.............................................................83

2.12.1无中生有之横向插入新计算字段......................................................................84

2.12.2无中生有之横向插入新计算项.........................................................................85

2.12.3字段组合之用纵向、横向组合分组来分析存货季度性销售..............................86

2.12.4字段组合之用金额分区分析销售大单的频次....................................................88

2.12.5字段组合之插入组合字段分类汇总并显示在顶部或底部..................................89

2.12.6分级显示之那些“父辈”们的事儿..................................................................90

2.12.7数据透视表之三“生”三“示”、十里桃花.....................................................92

CHAPTER03给集团生产总监送去的头疼解药97

3.1开足马力抓生产...............................................................................................98

3.1.1易总布置的新任务.............................................................................................98

3.1.2生产控制和成本预测、决策的财务知识.............................................................98

3.2用SQL语句和函数取得历年有效订单并预测其增长.......................................99

3.2.1新年新布局，获取历年有效订单总数.................................................................99

3.2.2巧妙选中数据透视表不连续的区域..................................................................104

3.2.3利用SLOPE函数和INTERCEPT函数取得订单增长的规律...........................104

3.2.4利用FORECAST函数预测订单增长数并验证................................................106

3.3用直观靓丽的图表进行历年订单趋势预测......................................................106

3.3.1美美的Excel订单预测图形.............................................................................106

3.3.2利用图表工具插入订单趋势图.........................................................................108

3.3.3添加和编辑趋势图的数据系列.........................................................................109

3.3.4让订单趋势图更加赏心悦目的技巧...................................................................111

3.4用扩展数组和条件求和函数搞定货品订单预测和分周序时进度......................113

3.4.1SUMIF函数和SUMIFS函数............................................................................113

3.4.2SUMIFS函数在订单预测中的实际应用............................................................113

3.4.3SUMPRODUCT函数和WEEKNUM函数........................................................115

3.4.4神秘的IF({1}..和IF({1,0}.................................................................................116

3.4.5利用IF({1,0}..和SUMIFS找出订单量*多和*少的城市.................................117

3.4.6函数、SQL语句和数组在序时生产订单进度中的综合运用.............................120

3.5用内嵌的迷你图和条件格式强化分周序时进度和畅销品分析.........................123

3.5.1利用迷你图和数据条的条件格式嵌入炫酷的序时订单图..................................123

3.5.2设置条件格式的管理规则让每个内嵌图表都听话.............................................126

3.5.3利用TRANSPOSE函数转置可更新的单元格区域..........................................127

3.5.4利用条件格式的管理规则找出滞销和畅销的单品.............................................128

3.5.5利用马尔柯夫法预测单品滞销和畅销互转的概率.............................................129

3.6用多种方法预测未来各单品成本发展水平和质量控制....................................131

3.6.1VLOOKUP函数也能玩逆向查找.....................................................................131

3.6.2“两点法”力挺“单”品总成本........................................................................132

3.6.3“二乘法”劲怼“单”品总成本........................................................................133

3.6.4“回归”分析工具也和单品总成本“铆”上了..................................................135

3.6.5“散点图”探索单品维修费与工时的内在逻辑..................................................138

3.6.6“帕累托图”掌控产品品质分析........................................................................140

CHAPTER04钱从哪里来145

4.1用Excel模板预测资金需求量........................................................................146

4.1.1寻求具有操作性的现金流预测方法..................................................................146

4.1.2用销售百分比法预测资金需求量......................................................................147

4.1.3用销售百分比法预测资金需求增量..................................................................148

4.1.4用回归分析法预测资金需求量.........................................................................150

4.2用模拟分析测试不同利率和期限下的企业贷款偿还能力................................151

4.2.1运用PMT函数测算固定利率、固定期限下的月度还款金额和本息之和..........152

4.2.2运用方案管理器比较不同利率、不同期限的多种融资方案..............................153

4.2.3运用模拟运算表的二维模拟运算测算多种利率、多种期限下的偿还能力.........155

4.2.4运用色阶让月度偿还利息负担更具明确的等级性和警示性..............................157

4.2.5运用单变量求解测算固定偿还能力下的可贷额度.............................................158

4.3用表单控件实时计算不同贷款额度和利率区间下的利息支付.........................159

4.3.1表单控件（窗体控件）的工作原理..................................................................160

4.3.2认识活泼的小伙伴们——常见表单控件...........................................................160

4.3.3让表单控件的小伙伴和单元格在贷款测算的舞台上一起“嗨”.........................166

4.4两个等额，哪个更划算..................................................................................169

4.4.1IPMT函数与PPMT函数.................................................................................169

4.4.2等额本息与等额本金与IPMT函数与PPMT函数的结合运用..........................170

4.4.3用数据条凸显对比各期贷款成本和差异分析....................................................172

4.4.4贷款期间遇到利率调整的应对方法..................................................................173

4.4.5等额本息与等额本金对比计算表的通用性扩展................................................173

4.5组合贷款在规划求解下的巧妙设计................................................................174

4.5.1资金的时间价值、贴现与PV函数...................................................................174

4.5.2运用规划求解工具搞定组合贷款的期限设计....................................................175

4.5.3运用规划求解工具测算组合贷款的贷款利率设计.............................................181

4.5.4运用规划求解工具搞定组合贷款的贷款额度分配.............................................183

4.6用Excel模板助力多种模式下的公司债券发行...............................................186

4.6.1企业债券发行的定价策略和IRR、NPV函数...................................................186

4.6.2分期付息发行模式下的溢价发行定价与内在报酬率.........................................188

4.6.3分期付息发行模式下的折价发行定价与内在报酬率.........................................191

4.6.4一次性还本付息发行模式下的溢价发行定价与内在报酬率..............................192

4.6.5一次还本付息发行模式下的折价发行定价与内在报酬率..................................194

4.6.6用智能目录纵览多种发行模式下的发行定价与内在报酬率..............................196

4.7债权转股权（债转股）与股权融资的模型设计..............................................198

4.7.1股权融资与债务重组的点滴知识......................................................................198

4.7.2INDEX函数和MATCH函数............................................................................198

4.7.3债转股的Excel模板的构建.............................................................................201

4.7.4用“演变”工具完成债转股方案的行权时机推演.............................................202

4.7.5股权融资的定价模型设计.................................................................................204

4.8与股权相关Excel文档的美化设计、安全加固和发送....................................206

4.8.1股份结构及组织架构图的设计.........................................................................206

4.8.2股份结构及组织架构图的美化和插图Excel文件的“瘦身”.............................210

4.8.3用Excel玩截图及股东会议会址地图的抓取....................................................212

4.8.4将股权结构表等超过一屏的单元格区域截取为带行号列标的图形...................213

4.8.5对股东会议的Excel表格的五重保护...............................................................215

4.8.6用Excel发送PDF/XPS安全邮件...................................................................220

CHAPTER05花钱也是一门大学问223

5.1非贴现、贴现类投资决策模板的构建.............................................................224

5.1.1ISNUMBER函数与静态回收期投资决策模板构建...........................................224

5.1.2会计收益率投资决策模板的构建......................................................................226

5.1.3动态回收期投资决策模板构建.........................................................................227

5.1.4净现值法投资决策模板的构建.........................................................................228

5.1.5现值指数法投资决策模板的构建......................................................................229

5.1.6IRR法投资决策模板的构建.............................................................................230

5.1.7利用二进制约束搞定资金限量条件下的*投资组合.....................................231

5.2投资敏感性分析.............................................................................................233

5.2.1对“敏感性”的分析........................................................................................233

5.2.2投资敏感性分析的要素....................................................................................235

5.2.3REPT与REPLACE(B)、SUBSTITUTE函数的悲喜剧...................................236

5.2.4投资敏感性分析模板的构建与ROW(1:r)魔术.................................................237

5.2.5投资敏感三要素的一维模拟运算......................................................................242

5.2.6用“雷达”快速扫描投资三要素的敏感性.......................................................247

5.2.7投资敏感三要素的敏感临界点测算..................................................................250

5.3用VBA代码来拓宽投资渠道.........................................................................252

5.3.1借由“一行代码”闯入VBA的殿堂................................................................253

5.3.2保存一个带有VBA代码的Excel文件.............................................................254

5.3.3VBA语言的三种算法结构、调用Excel公式与录制宏.....................................257

5.3.4设计带帮助的VBA自定义函数和过程.............................................................261

5.3.5让公式无所遁形的自定义追踪函数——WhoseCT函数...................................264

5.3.6使用快捷键让WhoseCT函数追踪公式...........................................................264

5.3.7创建个人加载宏并设置、修改其帮助和快捷键................................................266

5.3.8懒人的Excel公式追踪利器──“Excel公式一键显影软件”..........................268

5.4用自定义VBA函数助力投资要素的敏感分析................................................269

5.4.1多因素敏感对NPV的模板设计.......................................................................270

5.4.2利用NPV4ME函数计算NPV..........................................................................271

5.4.3多因素敏感对NPV的综合影响.......................................................................272

5.4.4单因素敏感对NPV的综合影响.......................................................................273

CHAPTER06资产管理“平衡”术275

6.1管理会计的“边际”........................................................................................276

6.1.1那些关于“边际”的管理“闲话”....................................................................276

6.1.2边际贡献与固定成本的“溢、缺、平”对比....................................................277

6.2零部件采购、产能利用与产品定价决策.........................................................277

6.2.1用量固定时的零部件外购、自制决策...............................................................278

6.2.2用量不确定时的零部件外购、自制决策...........................................................278

6.2.3无追加成本时新品投产的剩余产能利用...........................................................279

6.2.4需追加成本时新品投产的剩余产能利用...........................................................279

6.2.5以成本为导向的产品定价方法.........................................................................280

6.2.6以市场为导向的产品定价方法.........................................................................281

6.2.7量价关系求导的产品定价方法.........................................................................283

6.3存货经济订货量基础模型和扩展模型.............................................................284

6.3.1那些关于“经济”的管理“闲话”....................................................................284

6.3.2存货的取得与储存的成本闲话.........................................................................284

6.3.3经济订货量基础模型及动态验证......................................................................286

6.3.4允许缺货的经济订货量模型及动态验证...........................................................294

6.4资产管理盈亏临界点分析模型........................................................................297

6.4.1资产管理的量、本、利公式推导及其分析.......................................................297

6.4.2盈亏平衡模型的创建及结果解析......................................................................298

CHAPTER07用好家里的大件儿301

7.1固定资产管理的基础信息准备........................................................................302

7.1.1在同一列防止重复输入固定资产类别、状态等................................................302

7.1.2数据校验中的“忽略空值”究竟是个什么鬼....................................................304

7.2用VBA代码确保固定资产数据输入的快而准................................................305

7.2.1将动态数组赋值给数据校验中的公式...............................................................305

7.2.2设定的AciveX组合框引用基础信息................................................................306

7.3Excel内置的折旧函数与自定义累计折旧函数...............................................308

7.3.1折旧政策与SLN、VDB、DDB和SYD等折旧函数........................................308

7.3.2固定资产折旧的明细建档与累计折旧的depSYD和depDB函数.....................311

7.4固定资产自动化管理系统的构建....................................................................313

7.4.1自动创建固定资产管理卡与多折旧方法逐期比对表.........................................313

7.4.2自动生成固定资产折旧与累计折旧汇总报表....................................................315

7.4.3利用隔行汇总、公式转数值和快速空行整理折旧明细.....................................316

7.5固定资产更新决策与模型...............................................................................318

7.5.1新旧固定资产更新决策因素的分析..................................................................318

7.5.2搭建固定资产更新模型决策新资产是否投资....................................................319

CHAPTER08集团财务的报表自动汇总分析难题323

8.1报表公式设置及表间公式校验........................................................................324

8.1.1资产负债表报表原理及公式讲解......................................................................324

8.1.2利润表及利润分配表报表原理及公式讲解.......................................................326

8.1.3现金流量表报表原理及公式讲解......................................................................328

8.2利用代码实现下属机构的空白报表自动化下发..............................................332

8.2.1对下属分支机构整体规划.................................................................................332

8.2.2设计自动下发空白报表的VBA代码................................................................334

8.2.3下属分支机构填报报表与公式、数字混合列的清除.........................................335

8.2.4下属分支机构报表下发后的收回......................................................................337

8.3会计报表自动（实时）汇总系统的打造.........................................................338

8.3.1集团上报报表汇总的要求.................................................................................338

8.3.2多个报表汇总的解决方案分析.........................................................................338

8.3.3动态、可追溯的自动报表汇总系统设计...........................................................339

8.3.4自动报表汇总系统的汇总测试.........................................................................342

8.3.5自动报表汇总系统的修改与汇总数据清除.......................................................344

8.3.6自动报表汇总系统的数据备份.........................................................................345

8.4会计报表财务分析指标及自动化的实施.........................................................346

8.4.1以ROE为核心的财务分析的方法...................................................................346

8.