财务精英都是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.