EXCEL在财务中的应用（PPT42页).pptx

《EXCEL在财务中的应用（PPT42页).pptx》由会员分享，可在线阅读，更多相关《EXCEL在财务中的应用（PPT42页).pptx（42页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 在财务中的应用1目目 录录预备知识预备知识、ExcelExcel操作小结操作小结第一章、财务处理系统第一章、财务处理系统第二章、会计报表的编制第二章、会计报表的编制第三章、工资管理第三章、工资管理第四章、固定资产管理第四章、固定资产管理第五章、进销存管理第五章、进销存管理第六章、货币时间价值的计算第六章、货币时间价值的计算第七章、财务预测第七章、财务预测第八章、全面预算化编制第八章、全面预算化编制2预备知识预备知识、ExcelExcel操作小结操作小结 Excel是微软推出的一个功能强大的电子表格软件，具有强大的数据计算与分析处理功能，可以把数据用表格及各种统计图、透视图的形式表现

2、出来，使得制作出来的报表图文并茂，信息表达更清晰。3Excel的窗口界面的窗口界面 一般情况下，一般情况下，Excel窗口界面可通过窗口界面可通过“视图视图”菜单或菜单或“工具工具/选项选项” 菜单命令菜单命令下的下的“视图视图”选项卡来调整。选项卡来调整。问题：问题： Excel的编辑栏用于输入或修改工作表单元格数据的编辑栏用于输入或修改工作表单元格数据,你能打开你能打开/关闭编辑栏吗？关闭编辑栏吗？标题栏标题栏菜单栏菜单栏工具栏工具栏编辑栏编辑栏名称框名称框编辑公编辑公式按钮式按钮 状态栏状态栏工作表区工作表区工作表工作表标签标签 选项卡选项卡滚动滚动按钮按钮 滚滚动条动条 滚滚动条动条

3、4基本概念基本概念 1 1工作簿工作簿2、工作表、工作表问题：你知道工作表的名称显示在什么地方吗？（工作表标签）3单元格单元格4单元格区域单元格区域表示法：左上角单元格地址：右下角单元格地址左上角单元格地址：右下角单元格地址工作薄由工作表组成，工作表由单元格组成。 5数据输入数据输入l数值型：直接录入数值型：直接录入l字符型：一般也是直接录入（由数码字符型：一般也是直接录入（由数码0-9构成的字符量，构成的字符量，录入时用单引号开头）录入时用单引号开头）l日期日期/时间型：（录入格式）时间型：（录入格式）“年年/月月/日日”或或“年年-月月-日日”l使用使用“填充柄填充柄”可提高录入速度可提高

4、录入速度l录入数据时先不要考虑数据的格式，录入完毕后再行修改录入数据时先不要考虑数据的格式，录入完毕后再行修改其显示格式；其显示格式；l在计算机中，日期型的量是以整数形式存放的；在计算机中，日期型的量是以整数形式存放的；l修改数据可通过修改数据可通过“编辑栏编辑栏”来实现；来实现；6公式与函数公式与函数公式输入方法公式输入方法1、使用键盘直接输入、使用键盘直接输入2、使用鼠标选择输入、使用鼠标选择输入l公式以等号开头公式以等号开头l公式尽可能使用鼠标选择输入公式尽可能使用鼠标选择输入7相对地址与绝对地址相对地址与绝对地址 1、相对地址；例如：、相对地址；例如：A3、B5等等公式中如果使用相对地

5、址，则公式复制要相应调整公式中如果使用相对地址，则公式复制要相应调整（变化）：调整规律如下：（变化）：调整规律如下：新行（列）号新行（列）号=原行（列）号原行（列）号+行（列）号的变化值行（列）号的变化值2、绝对地址：例如：、绝对地址：例如：$A$3、$B$5等等公式中如果使用绝对地址，则公式复制后不会改变公式中如果使用绝对地址，则公式复制后不会改变3、混合地址：例如：、混合地址：例如：$A3、B$5等等提示：提示：进行地址换时可使用进行地址换时可使用F4键。键。8第一章、财务处理系统第一章、财务处理系统一、会计科目代码与科目名称 在计算机系统中一般使用会计科目代码作为代表会计科目的惟一标识。

6、 财政部对一级科目编码进行统一规定。9二、凭证的输入二、凭证的输入1、LOOKUP函数简介 P5格式：lookup(lookup_value, lookup_vector, result_vector) 功能：首先在lookup_vector区域中寻找lookup_value，找到相匹配的单元后，将此单元在result_vector区域对应单元的值返回。注意：注意： lookup_vector 和result_vector只能为一行或一列的值查什么在哪查返回对应的值102、科目信息的建立l输入时注意数据类型l输入余额时，借方余额为正数，贷方余额为负数。113、凭证信息的输入l对于应收或预收账款

7、、应付或预付账款科目将采用电算化系统中的“客户或供应商”辅助核算方法进行；l对于管理费用科目将采用电算化系统中的“部门和项目”辅助核算方法进行。l为了保证科目代码输入的有效性，使用“数据/有效性”功能进行相应设置。l为了在输入凭证时，输入科目代码后使得相对应的科目名称自动显示，可使用LOCKUP函数。l为了能使计算机给予输入提示防止输入出错，可以对“项目”和“部门”列加以有效性控制。l为了方便浏览数据，可以对工作进行“拆分”与“冻结窗格”处理。12三、凭证查询三、凭证查询 凭证查询主要利用EXCEL的筛选功能实现1、按凭证号进行查询2、按日期进行查询3、按科目进行查询4、按金额进行查询5、按部

8、门或项目进行查询问题：如何设置“科目性质”与“总账科目”栏的公式。科目性质：1-资产 2负债 3权益 4-成本 5-损益提示：使用字符（文本）函数提示：使用字符（文本）函数LEFT实现。实现。13四、明细账账与总账的形成四、明细账账与总账的形成1、SUMIF函数函数格式：格式：SUMIF（range，criteria，sum_ range）功能：在功能：在range数据区找到与数据区找到与criteria匹配的单元，对匹配的单元，对sum_ range区域中与此单元对应的单元进行求和区域中与此单元对应的单元进行求和在此区域中找与criteria匹配的单元对sum_ range区区域中与找到的对

9、域中与找到的对应单元求和应单元求和14四、明细账账与总账的形成（续）四、明细账账与总账的形成（续）2、利用、利用SUMIF函数计算明细账科目的发生额与余额函数计算明细账科目的发生额与余额提示：提示：l发生额栏公式设置发生额栏公式设置利用利用SUMIF函数函数 range-填写凭证时科目代码所在区域填写凭证时科目代码所在区域 criteria-余额表中的科目单元余额表中的科目单元 sum_ range-填写凭证时发生额或余额所在区域填写凭证时发生额或余额所在区域l余额栏公式设置余额栏公式设置余额余额=期初余额期初余额+借方发生额借方发生额-贷方发生额贷方发生额l累计发生额栏公式设置累计发生额栏公

10、式设置1月份的累计发生额与月份的累计发生额与1月份的发生额相等，以后各月份的累月份的发生额相等，以后各月份的累计发生额等于当月发生额加上上月的累计发生额。计发生额等于当月发生额加上上月的累计发生额。15四、明细账账与总账的形成（续）四、明细账账与总账的形成（续）3、利用、利用SUMIF函数计算按部门或项目明细核算科目的发生额函数计算按部门或项目明细核算科目的发生额提示：提示：l创建一个新的工作表创建一个新的工作表“管理费用明细管理费用明细”用于计算按部门或项用于计算按部门或项目明细核算科目的发生额目明细核算科目的发生额l 利用利用CONCATENATE函数将科目名称、项目与部门合并为函数将科目

11、名称、项目与部门合并为一新栏目，以便通过此列利用一新栏目，以便通过此列利用SUMIF函数，对不同部门、不函数，对不同部门、不同项目管理费用的借方或贷方发生额进行汇总。同项目管理费用的借方或贷方发生额进行汇总。l计算按部门或项目明细核算科目的借贷方发生额计算按部门或项目明细核算科目的借贷方发生额提示：提示： 利用利用SUMIF函数函数l设置设置“预算预算-实际实际”栏目公式，当进行凭证输入时，可随时监控栏目公式，当进行凭证输入时，可随时监控费用是否超出预算。费用是否超出预算。l设置设置“预算预算-实际实际”栏目数据显示格式，当超过预算时（负数）栏目数据显示格式，当超过预算时（负数）用红色表示。用

12、红色表示。16四、明细账账与总账的形成（续）四、明细账账与总账的形成（续）4、利用、利用SUMIF函数计算总账科目的发生额与余额函数计算总账科目的发生额与余额提示：提示：l期初额栏公式设置期初额栏公式设置利用利用SUMIF函数函数 range-填写总账科目所在区域填写总账科目所在区域 criteria-填写总账科目代码所在单元填写总账科目代码所在单元 sum_ range-填写对应期初余额所在的区域填写对应期初余额所在的区域l借贷方发生额栏目公式设置借贷方发生额栏目公式设置l期末余额栏公式设置期末余额栏公式设置余额余额=期初余额期初余额+借方发生额借方发生额-贷方发生额贷方发生额l累计发生额栏

13、公式设置累计发生额栏公式设置1月份的累计发生额与月份的累计发生额与1月份的发生额相等，以后各月份的累月份的发生额相等，以后各月份的累计发生额等于当月发生额加上上月的累计发生额。计发生额等于当月发生额加上上月的累计发生额。17四、明细账账与总账的形成（续）四、明细账账与总账的形成（续）5、利用分类汇总功能计算明细科目的余额与发生额、利用分类汇总功能计算明细科目的余额与发生额6、利用分类汇总功能计算总账科目的余额与发生额、利用分类汇总功能计算总账科目的余额与发生额7、利用分类汇总功能计算资产、负债、所有者权益及末结利、利用分类汇总功能计算资产、负债、所有者权益及末结利润润18四、明细账账与总账的形

14、成（续）四、明细账账与总账的形成（续）8、利用数据透视表与分析管理费用某部门或某项目的发生额、利用数据透视表与分析管理费用某部门或某项目的发生额、余额余额提示：输入凭证，并在提示：输入凭证，并在“管理费用明细管理费用明细”表中设置公式，按部表中设置公式，按部门或项目明细核算科目的计算出发生额后，再利用数据透视门或项目明细核算科目的计算出发生额后，再利用数据透视表与分析管理费用某部门或某项目的发生额。表与分析管理费用某部门或某项目的发生额。19四、明细账账与总账的形成（续）四、明细账账与总账的形成（续）9、期末损益结转、期末损益结转10、下月账薄的建立、下月账薄的建立提示：提示：1）、将上月的工

15、作表复制一份作为）、将上月的工作表复制一份作为2月月“基础基础”表表2）、编写公式计算）、编写公式计算2月份期初余额（等于上月的期末余额）月份期初余额（等于上月的期末余额）3）、将上月填写的凭证删除后，再编辑本月业务凭证）、将上月填写的凭证删除后，再编辑本月业务凭证4）、编辑公式计算所累计借（贷）方发生额（等于上月累计）、编辑公式计算所累计借（贷）方发生额（等于上月累计发生额发生额+本月发生额）本月发生额）20五、会计数据的保护五、会计数据的保护1、对工作薄进行保护与撤消保护、对工作薄进行保护与撤消保护2、对工作表进行保护与撤消保护、对工作表进行保护与撤消保护3、工作表（公式）区域的保护、工作

16、表（公式）区域的保护提示：提示：1）、选择不被保护区域）、选择不被保护区域2）、使用）、使用“快捷菜单快捷菜单/设置单元格格式设置单元格格式”，选择，选择“保护保护”选项，取选项，取消消“锁定锁定”3）、保护工作表）、保护工作表21第二章、会计报表的编制第二章、会计报表的编制一、资产负债表的编制 通过数据引用方法直接从第一章相关月份账薄中取数即可-引用账薄中的期初与期末余额。知识点：1、数据引用的三种方式：A、同一工作表中不同单元格的数据引用；B、同一工作薄中的不同工作表的数据引用；C、不同工作薄间的数据引用。2、名称的定义与引用22二、损益表的编制 通过数据引用方法直接从第一章相关月份账薄中

17、取数即可-引用账薄中的借方或货方发生额。会计报表的编制会计报表的编制23三、现金流量表的编制 通过数据引用方法直接从第一章相关月份账薄中取数即可-引用账薄中的借方或货方发生额。1、准备工作1）、建立现金流量项目代码与名称间的对应关系；2）、在凭证项目中，加入“现金流量代码”和“现金流量名称”两个项目；3）、对“现金流量代码”列设置有效性控制；4）、通过lookup函数，使得“现金流量名称”自动产生；5）、输入凭证时，“现金”与“银行存款”科目要输入“现金流量项目代码”（现金流量项目名称自动对应生成）会计报表的编制会计报表的编制24现金流量表的编制2、现金流量表的编制 使用SUMIF函数直接从第

18、一章相关月份账薄中按条件求和即可-引用账薄中的借方或货方发生额。会计报表的编制会计报表的编制25四、合并报表的编制1、合并报表的前题条件 合并报表的源数据表的结构应相同，否则，合并报表结果无意义。2、操作提示：1）、将母公司与子公司报表进行汇总处理；l利用EXCEL中的合并计算功能实现l合并计算完成后，源区域数据中的更改不会对合并计算表（结果）影响，如果希望更改源区域数据后，合并计算表的数据也随之自动更改，可将合并计算表链接到源区域上。2）、建立调整分录输入的项目，并输入调整会计分录；3）、建立合并报表格式；4）、设置合并报表单元取数公式，并自动完成取数。 会计报表的编制会计报表的编制26五、

19、财务报表分析1、操作分析： 使用EXCEL的图表功能实现。2、分析类型： 趋势分析柱型图或折线图 结构变化分析-堆积柱型图 结构分析-饼图会计报表的编制会计报表的编制27第三章、工资管理第三章、工资管理一、工资基本数据的输入操作提示： 为了输入方便并防止出错，可对“部门”、“性别”、“职工类型”等栏目添加有效性规则设置。 28工资管理工资管理二、基本工资项目设置1、IF函数介绍格式：if(logical_test,value_if_true, value_if_false)功能：根据对指定条件逻辑判断的真假而返回不同的值。2、主要内容设置：1）利用IF函数对岗位工资、福利费、副食补贴、奖金、事

20、假扣款、病假扣款、所得税等项目进行设置、2）利用SUM函数等对应发合计、扣款合计、应发工资、实发工资等项目进行设置。29工资管理工资管理三、工资数据的查询1、利用筛选功能进行工资数据的查询自动筛选：适用于多个筛选条件间为逻辑与关系（多个条件同时满足）的情况。高级筛选：一般用于多个筛选条件间为逻辑或关系（多个条件中只要一个满足）的情况。 30工资管理工资管理三、工资数据的查询2、利用VLOOKUP函数进行工资数据的查询知识点- VLOOKUP介绍格式：格式：vlookup(lookup_value,table_array,col_index_num,rang_lookup) 其中：lookup_

21、value：指需要在数据表首列搜索的值。table_array：指需要在其中搜索数据的数据表。col_index_num：指在数据表的首列搜索到满足条件的单元后，此函数返回数据表中与此单元格同行，列号为col_index_num单元格的值。ang_lookup：指定在搜索时是精确匹配还是大致匹配，如果是TRUE，为大致匹配，要求数据表首列按升序排序，如果为FALSE，为精确匹配，不要求数据表首列按升序排序。如果此值省略，表示选择TRUE。功能：功能：在table_array数据表首列，寻找与lookup_value匹配的单元，如果找到，返回数据表中与此单元同行的第col_index_num列的

22、值。31工资管理工资管理四、工资数据的汇总分析 通过数据透视表（图）方法实现。1、依据部门和职工类别的统计分析2、依据性别的统计分析3、依据年龄段或基本工资段的统计分析4、以月份为依据的统计分析 32第四章、固定资产第四章、固定资产一、原始卡片录入1、为了输入数据的方便性，应对“增加方式”、“减少方式”、“使用状态”、“部门”等项进行数据有效性设置。2、折旧方法项目的输入约定：1-平均年限法、2-工作量法、3-双倍余额递减法、4、年数总和法3、输入原始卡片时可以使用记录单来完成4、计算固定资产净值5、为了方便浏览，可使用窗口拆分与冻结方法。 33固定资产固定资产二、固定资产新增的业务处理 与原

23、始卡片录入相似：1、可以直接以数据行方式录入2、也可以使用记录单来完成 34固定资产固定资产三、固定资产部门调拨的业务处理1、当月的处理：l使用筛选功能找待处理的资产项目l将此资产项目的“减少方式”栏修改为“部门调拨”2、下月的处理复制上月的相应固定资产项目数据行。将此固定资产项目的“增加方式”栏修改为“部门调拨”将此固定资产项目的“部门”栏修改为新的部门值将此资产项目的“减少方式”栏修改为“空” 35固定资产固定资产四、固定资产减少的业务处理1、使用筛选功能找到减少的资产项目。2、在“减少方式”一栏输入相应的减少方式内容。 36固定资产固定资产五、固定资产折旧的计提业务处理一）预备知识：1、

24、DDB（）函数-双倍余额递减法计提折旧DDB（cost,salvage,life,period,factor） Cost：原值Salvage：残值Life：预计使用年或月值Period：折旧计提年或月数Factor：默认表示2，即双倍余额递减，可以为其他整数，为如3，表示3倍余额递减37固定资产固定资产五、固定资产折旧的计提业务处理预备知识：2、模拟运算表和使用3、SYD（）函数-年数总和法计提折旧SYD （cost,salvage,life,per） Cost：原值Salvage：残值Life：预计使用年或月值Period：折旧计提年或月数38固定资产固定资产五、固定资产折旧的计提业务处理二

25、）、固定资产折旧计提前的准备1、在”部门”栏后面插入“当前日期”栏并输入当前值2、在”开始使用日期”栏后面插入“已计提折旧月份”栏并使用函数DAYS360（）计算该栏目值。3、在”已计提折旧月份”栏后面插入“已计提折旧年份”栏并使用函数计算该栏目值。4、对按工作量法计提折旧的固定资产输入当月工作量。39固定资产固定资产五、固定资产折旧的计提业务处理三）、固定资产折旧计提1、计提注意事项：l不同的固定资产采用不同的折旧方法计提。l新增固定资产当月不计提折旧。l折旧已经计提完毕仍然使用的固定资产不应再计提折旧。l在最后一个月计提折旧时，可能会出现固定资产的剩余价值小于搂正常公式计算的折旧值，这时垢折旧值应为固定资产的剩余价值。40固定资产固定资产五、固定资产折旧的计提业务处理三）、固定资产折旧计提2、当前月份的计提折旧处理：按不同的固定资产进行计提折旧的处理，同时注意以上几种情况的处理。3、下月份的计提折旧处理。41固定资产固定资产六、固定资产查询使用筛选方法进行查询处理1、查询新增的固定资产。2、查询减少的固定资产。3、 按部门查询固定资产。4、 按“原值”和“已使用年限”查询固定资产。5、 按月折旧额大小查询固定资产。42