Excel在会计与财务中的应用课件：Excel在财务预测与预算中的应用

12.1Excel在销售预测中的应用12.2Excel在成本费用预测中的应用12.3Excel在利润预测中的应用12.4Excel在财务预算管理中的应用

Excel在财务预测与预算中的应用内容简介：本章主要讲解如何利用Excel在企业经营过程中进行财务预测与预算，主要包括对销售、成本、利润，及在财务管理中的应用，从而帮助企业更好的运营。学习目标：通过本章学习，学生在掌握财务管理中销售预测、成本费用预测和利润预测的基础上，熟练使用Excel中的趋势图、数组运算和Excel强大的梳理统计功能中的回归方程，如规划求解。

Excel的基础知识2024/4/18212.1Excel概述12.1.1趋势分析图法12.1.2回归直线法2024/4/18312.1.1趋势分析图法1.在Excel中可以使用图表中添加趋势线的方法来预测销售,称为趋势分析图法。2024/4/18412.1.1趋势分析图法例题12.1：张同学的B传媒公司2020年的销售情况如表12.1所示,要求对其进行销售预测模型设计。2024/4/185表12.1张同学的B传媒公司2020年的销售情况表单位:元月份123456789101112实际销售额16000019000020000022000021000019000023000024000023500026000026500027000012.1.1趋势分析图法操作步骤步骤1：在Excel中建立如图12.1所示的表格。2024/4/18612.1.1趋势分析图法步骤2：选中A3:M3单元格区域,“插入”选项卡下找到“图表”,插入折线图,选择“折线图”,系统自动生成折线图,如图12.2所示。2024/4/18712.1.1趋势分析图法步骤3：设置图表格式。在图表工具的“设计”选项下,找到“添加图表元素”→“轴标题”,将主要横坐标轴标题设为“月份”,主要纵坐标轴标题设为“实际销售额”,图表标题设为“实际销售额”,在右侧添加图例“实际销售额”,最终的设计轴坐标图，如图12.3所示。2024/4/18812.1.1趋势分析图法步骤4：单击绘制的实际销售额曲线,然后右击,在弹出的快捷菜单中选择“添加趋势线”命令。在Excel右侧“设置趋势线格式”中,“趋势线选项”为“线性”;“趋势线名称”单击选中“自定义”单选按钮,并在其文本框内输入趋势线的名称“预测销售额”;“趋势预测”中设置向前“1周期”,向后“0.5周期”,它们分别表示前后预测的月份,如图12.4所示。输出的预测销售额折线图如图12.5所示。2024/4/18912.1.1趋势分析图法步骤5：在添加图表元素中找到网格线，如图11.3所示,分别单击“主轴主要水平网格线”“主轴主要垂直网格线”“主轴次要水平网格线”“主轴次要垂直网格线”，“设计”，“添加图表元素”，“数据标签”，“上方”。如图12.6所示,从图中可以读出第12个月的预测销售额为27万元。2024/4/181012.1.2回归直线法3.通过任务栏退出回归直线法就是根据过去一定时期的销售量和资金总额,运用反映资金量和销售量之间关系的回归直线方程,并据此确定资金总额中的变动资金和固定资金的一种定量分析方法。用公式表示为y=a+bx","其中因变量"y"代表预测的资金需要量,"a"代表不变资金的部分,"b"代表变动资金的部分,自变量"x"则代表销售量(或销"售额)。2024/4/181112.1.2回归直线法例题2张同学的B传媒公司2012—2019年的销售额与广告费关系如表12.2所示,在此,假定产品销售额只受广告费支出大小的影响,2020年度预计广告费支出为155万元。要求:利用回归直线法预测2020年度销售额。表12.2张同学的B传媒公司销售额与广告费关系表单位:万元2024/4/1812年度销售额广告费20123250100201333001052014315090201533501252016345013520173500140201834001402019360015012.1.2回归直线法【操作步骤】：步骤1：在Excel中建立如图12.7所示的表格。2024/4/181312.1.2回归直线法步骤2：在数据选项卡里单击“数据分析”,弹出数据分析对话框,单击选中“回归”选项,然后单击“确定”按钮,弹出“回归”对话框。在该对话框的“Y值输入区域”和“Ⅹ值输入区域”文本框中分别输入“$B$3:$B$10”和“$C$3:$C$10”,然后在“输出选项”选项组内选中“输出区域”单选按钮,并在其文本框中输入“$B$17”,为使计算结果显示出线性拟合图,选中“线性拟合图”复选框,如图12.8所示。可以根据自己的需要选择是否需要残差，正态概率图等。（可能存在电脑上“数据”菜单中没有出现“数据分析”选项，此时点击“文件”，“选项”，“加载项”，“分析工具”点击确定）2024/4/181412.1.2回归直线法步骤3：设置完成之后,单击“确定”按钮,自动显示出计算结果和拟合图,如图12.9和图12.10所示。2024/4/181512.1.2回归直线法步骤4：计算2020年预计销售额。在“回归直线截距a”C11单元格中输入公式“=C33”;“回归直线斜率b”C12单元格中输入公式“=C34”;在预计销售额C14单元格中输入公式“C11+C12*C13”,结果如图12.11所示。2024/4/181612.2Excel在成本费用预测中的应用12.2.1高低法进行成本费用预测12.2.2函数法进行成本费用预测12.2.3预测工作表进行成本费用预测2024/4/181712.2.1高低法进行成本费用预测根据成本习性,固定成本和变动成本只是两种极端的类型,企业大量的费用项目属于混合成本,为了经营管理的需要,必须把混合成本分为固定与变动两个部分,高低点法就是其分解方法之一。高低点法,是以过去某一会计期间的总成本费用和业务量资料为依据,从中选取业务量最高点和业务量最低点,将总成本费用进行分解,得出成本性态的模型,进而根据预计的业务量预测未来的成本费用。2024/4/181812.2.1高低法进行成本费用预测例题12.3张同学的B传媒公司2019年成本费用与产量数据资料如表12.3所示,预计2020年1月产销量为125件,要求利用高低点法预测1月份的成本费用。表12.3

2019年成本费用与产量数据表2024/4/1819月份产销量(件)成本费用(元)145158462541613035226412456173215541687265317316762195438692314399833465101023451911114350781211938419

12.2.1高低法进行成本费用预测【操作步骤】：步骤1：设计高低点法成本费用预测模型,如图12.12所示。2024/4/182012.2.1高低法进行成本费用预测步骤2：设置高低点产销量、成本费用计算公式。在C17单元格中输入公式“=MAX(B3:B14)”,C18单元格中输入公式“MIN(B3:B14)”,C19单元格中输入公式“=VLOOKUP(C17,$B$3:$C$14,2,FALSE)”,C20单元格中输入公式“=VLOOKUP(C18,$B$3:$C$14,2,FALSE)”（需要注意，是产销量最高点对应的成本费用，该成本费用不一定是最大的，最低点同理）。步骤3：计算单位变动成本、固定成本。在C21单元格中输入公式“=(C19-C20)/(C17-C18)”,C22单元格中输入公式“=C19-C21*C17”。2024/4/182112.2.1高低法进行成本费用预测步骤4：设置预测成本费用计算公式。在C24单元格中输入公式“C22+C21*C23”。制作完成的成本费用预测模型如图12.13所示。2024/4/182212.2.2函数法进行成本费用预测运用Excel提供的函数也可以进行成本费用的预测,比如GROWTH、FORECAST、LINEST、TREND、SLOPE和INTERCEPT函数,在此只介绍GROWTH和TREND函数,其他函数的用法请参阅有关函数的帮助。2024/4/182312.2.2函数法进行成本费用预测例题12.4张同学的B传媒公司2019年成本费用与产量数据资料见表12.3,预计2020年1月产销量为124件,要求利用函数法预测1月份的成本费用。假设2-8月的产销量分别为118件、120件、122件、124件、126件、128件、130件,要求:利用函数法预测多期的成本费用。2024/4/182412.2.2函数法进行成本费用预测【操作步骤】：步骤1：建立函数法预测成本费用模型,如图12.14所示。2024/4/182512.2.2函数法进行成本费用预测步骤2：使用GROWTH函数进行成本费用预测。预测下期成本费用,在F4单元格中输入公式“=GROWTH(C3:C14,B3:B14,E4)”;预测多期成本费用,选中F8:F14单元格区域,在编辑栏中输入公式“=GROWTH(C3:C14,B3:B14,E8:E14)”,同时按“Ctrl+Shift+Enter”（表示数组运算）组合键。输入完成的成本费用预测模型如图12.15所示。2024/4/182612.2.2函数法进行成本费用预测步骤3：使用TREND函数进行成本费用预测。预测下期成本费用,在F4单元格中输入公式“TREND(C3:C14,B3:B14,E4)”;预测多期成本费用,选中F8:F14单元格区域,在编辑栏中输入公式“=TREND(C3:C14,B3:B14,E8:E14)”,同时按“Ctrl+Shift+Enter”组合键。输入完成的成本费用预测模型如图12.16所示。2024/4/182712.2.2函数法进行成本费用预测【温馨小提示】：在用GROWTH和TREND函数预测多期成本费用时,为什么要按“Ctrl+Shift+Enter”组合键来输入公式?在此,需熟悉一个概念“数组公式”。数组公式是指可以在数组的一项或多项上执行多个计算的公式,数组公式可以返回多个结果,也可返回一个结果。位于多个单元格中的数组公式称为多单元格公式,位于单个单元格中的数组公式称为单个单元格公式。使用数组公式时必须按“Ctrl+Shift+Enter”组合键,Excel将在公式两边自动加上大括号“{}”。不要自己输入大括号,否则,Excel会认为输入的是一个正文标签。编辑数组公式时,先选取数组区域并激活编辑栏,公式两边的大括号将消失,然后编辑公式,最后按“Ctrl+Shift+Enter”组合键。删除数组公式时,先选取数组公式所在的全部区域,按“Delete”键即可。2024/4/182812.2.3预测工作表进行成本费用预测运用Excel2016极其以后版本中新出了数据功能区中的预测选项卡功能，其中的预测表功能做出一个预测图表，同样也可以对成本费用进行预测。2024/4/1829例题12.5张同学更新了Excel版本从原来的03版更新到了2016版，发现了Excel的新功能，数据功能区的预测选项卡功能，张同学就发现运用该功能也可以对【例12.4】的2020年1月和2月的产销量和成本费用进行预测。2024/4/1830【操作步骤】：步骤1：在Excel中建立如图12.17所示的表格2024/4/1831步骤2：使用数据选项卡中的预测工作表，选中A2:B14单元格，单击“数据”，“预测工作表”，需要预测2020年1月和2月的，所以预测结束为选择14期，根据实际情况在“选项”中对其进行设置，点击“创建”。如图12.18所示2024/4/1832步骤3：创建传销量的预测表的结果，在置信区间为95%时，2020年1月和2月最低的产销量为108、99件，平均值为126、131件，最高为143、163件。产销量预测折线图可以根据需求更改为条形图，添加图表标题，数据标签。如图12.19所示。2024/4/1833步骤4：使用数据选项卡中的预测工作表，选中“A2:A14”，按Ctrl键，同时选中“C2:C14”单元格，重复预测产销量的步骤，其13、14期的成本预测结果如图12.29所示。2024/4/183412.3Excel在利润预测中的应用12.3.1目标利润分析模型12.3.2利润敏感性分析模型12.3.3利润最大化规划求解2024/4/183512.3.1目标利润分析模型1在短期规划中,通常把单价、单位变动成本和固定成本视为常量,只有销量和利润两个自有变量。给定销量时,可利用方程式直接计算出预期利润;给定目标利润时,可直接计算出应达到的销售量。Excel提供的单变量求解工具可以很方便地完成这类问题的计算,单变量求解是一种典型的逆运算,可用作假设分析的工具。用户可以在工作表上建立起所需的数据模型,通过变动某关键变量立刻得到相应的结果,根据利润与收入、成本、费用之间的因果关系,可以在Excel中建立分析模型。通过选择数据选项卡下的模拟分析,找到单变量求解命令,即可使用单变量求解功能。2024/4/183612.3.1目标利润分析模型例题12.6张同学的B传媒公司拓展业务，准备推出一种帮助企业推广的服务产品,单位售价为200元,单位变动成本为50元,固定成本为20000元,该公司2020年的目标利润预计为4480000元。要求:利用单变量求解工具进行分析,要想实现该目标,销量至少为多少?2024/4/183712.3.1目标利润分析模型【操作步骤】：步骤1：在工作表中建立目标利润分析模型,在B7单元格中输入公式“=B3*(B4-B5)-B6”,如图12.21所示。2024/4/183812.3.1目标利润分析模型步骤2：通过选择数据选项卡下的模拟分析,找到单变量求解命令,在弹出的“单变量求解”对话框目标单元格中输入“$B$7”,目标值输入“4480000”,可变单元格输入“$B$3”,如图12.22所示,单击“确定”,待求解完成后,再次单击“确定”。目标利润预计为4480000元时,要想实现该目标,销量至少为30000件,如图12.23所示。2024/4/183912.3.2利润敏感性分析模型利润敏感分析,是研究与分析各个因素变动对利润变化的影响程度。利润对某些因素的变化十分敏感,我们称这类因素为敏感因素;与此相反,利润对某些因素的变化反应较为迟钝,这类因素被称为不敏感因素。反映敏感程度的指标是敏感系数,其计算公式为:"敏感系数"="目标值变动百分比/参数值变动百分比"Excel可以建立动态分析模型来分析各因素变动对利润的影响情况,在模型中可以通过拖动滚动条或数值调节按钮来随意查看各因素变动时的成本、收入和利润值。2024/4/184012.3.2利润敏感性分析模型例题12.7张同学的B传媒公司生产一种产品,单位售价为200元,该年的销量为30000件,单位变动成本为50元,固定成本为20000元。假设这几种影响利润的因素的变化范围为-50%-50%,要求:在Excel中进行利润敏感性分析。2024/4/184112.3.2利润敏感性分析模型【操作步骤】：步骤1：在工作表中设置如图12.24所示的利润敏感性分析模型,同时按“Ctrl”和“1”键，设置D4:D7、C12:C15、E12单元格格式为百分比。2024/4/184212.3.2利润敏感性分析模型步骤2：点击“开发工具”功能区，“控件”选项卡，“插入”，“滚动条（窗体控件）”单击“确定”按钮,如图12.25所示。2024/4/184312.3.2利润敏感性分析模型步骤3：插入并设置滚动条格式。在快速访问工具栏中单击“滚动条(窗体控件)”按钮,将滚动条设置在C4单元格中,调节滚动条的大小,使其充满该单元格。在滚动条上右击,在弹出的菜单中选择“设置控件格式”。在“设置控件格式”对话框中,“当前值”框中,输入与滚动框在滚动条中的位置相对应的初始值。此值不得小于“最小值”(否则将使用“最小值”),不得大于“最大值”(否则将使用“最大值”)。在“最小值”框中,输入用户在将滚动框置于距垂直滚动条底端或水平滚动条左端最近的位置时可以指定的最小值。在“最大值”框中,输入用户在将滚动框置于距垂直滚动条顶端或水平滚动条右端最远的位置时可以指定的最大值。2024/4/184412.3.2利润敏感性分析模型在“步长”框中,输入值增加或减小的幅度,以及单击滚动条任意一端的箭头时使滚动框产生的移动程度。在“页步长”框中,输入值增加或减小的幅度,以及在单击滚动框与任一滚动箭头之间的区域时使滚动框产生的移动程度。“单元格链接”框表示该滚动条所调控的单元格,在其中输入包含滚动框当前位置的单元格引用。在本例中,我们将当前值设置为0,最小值设置为1,最大值设置为10000,步长设置为1,页步长设置为100,单元格链接为C4单元格,如图12.26所示。同理,在C5、C6、C7单元格中设置相同的滚动条。2024/4/184512.3.2利润敏感性分析模型步骤4：设置变动百分比单元格的公式,计算变动后数值和当前利润值。由于影响利润的因素的变化范围为-50%-50%,结合滚动条的设置,在D4单元格中输入公式“=(C4-5000)/10000”,此时拖动滚动条,D4单元格的变化范围为-50%-50%,将此公式利用填充柄向下填充至D7单元格。在E4单元格中输入公式“=B4*(1+D4)”,再将此公式利用填充柄向下填充至E7单元格。在B8单元格中输入公式“=B4*(B5-B6)-B7”。2024/4/184612.3.2利润敏感性分析模型步骤5：设置因素变动对利润的影响计算公式。在考虑某个因素变动时,其他因素都假定不变。在B12单元格中输入公式“=E4*($B$5-$B$6)-$B$7”,以此类推,计算出B13:B15单元格区域变动后利润的值。在C12单元格中输入公式“=(B12-$B$8)/$B$8”,将该公式利用填充柄向下填充至C15单元格。在D12单元格中输入公式“=E4*(E5-E6)-E7”。在E12单元格中输入公式“=(D12-B8)/B8”。最终结果如图12.27所示。2024/4/184712.3.3利润最大化规划求解【例12.8】

A集团生产产品的基本资料如表12.4所示,试在Excel中利用规划求解分析工具,建立利润最大化模型进行分析。表12.4产品基本资料2024/4/1848产品ABC单价(元/件)142045单位变动成本(元/件)101630最大生产能力(件)400003500035000甲车间的单位产品工时(小时)544乙车间的单位产品工时(小时)345丙车间的单位产品工时(小时)434甲车间最大工量限量(小时)400000乙车间最大工量限量(小时)350000丙车间最大工量限量(小时)300000产品总固定成本30000012.3.3利润最大化规划求解

【操作步骤】：步骤1：根据产品基本资料列出规划求解方程。目标函数:max{π}=(14-10)×Q1+(20-16)×Q2+(45-30)×Q3-300000约束条件:5Q1+4Q2+4Q3<=400000 3Q1+4Q2+5Q3<=350000 4Q1+3Q2+4Q3<=300000 0≤Q1≤40000 0≤Q2≤35000 0≤Q3≤35000其中:Q1、Q2、Q3分别为产品A、B、C的产销量,且为整数。2024/4/184912.3.3利润最大化规划求解步骤2：根据案例资料及方程,建立利润最大化模型,如图12.24所示。2024/4/185012.3.3利润最大化规划求解步骤2：设置实际工时、实际产量、利润计算公式。设置后如图12.29所示。在D8单元格中输入公式“=SUMPRODUCT($D$19:$F$19,D4:F4)”,利用填充柄将该公式向下填充至D10单元格。在D13、D14、D15单元格中分别输入公式“=D19”“=E19”“=F19”,其中D19、E19、F19单元格为可变单元格。在D23单元格中输入公式“=SUMPRODUCT(D19:F19,D20:F20-D21:F21)-D22”。2024/4/185112.3.3利润最大化规划求解步骤3：进行规划求解。找到数据选项卡下的“规划求解”命令,弹出“规划求解参数”对话框。设置目标单元格为利润单元格“$D$23”,求“最大值”;可变单元格为实际产量“$D$19:$F$19”单元格区域。设置完成之后添加约束条件,单击“添加”,打开添加对话框即可添加条件,根据模型当中约束条件进行添加，如图12.30。工时约束,设置如下:每个车间工时都不能小于零,同时也不能大于最大限量。如图12.31和图12.32所示。2024/4/185212.3.3利润最大化规划求解生产能力约束,设置如下:实际产量必须为整数,每个产品的实际产量都不能小于零,同时也不能大于最大限量。如图12.33、图12.34、图12.35和图12.36所示。2024/4/185312.3.3利润最大化规划求解步骤4：建立分析报告。上一步骤单击“求解”后,在“规划求解结果”对话框中,选中“报告”列表框中的“运算结果报告”选项,如图12.37所示,然后单击“确定”按钮,Excel会自动插入一张“运算结果报告1”工作表。2024/4/185412.3.3利润最大化规划求解注意:使用规划求解功能计算后,除了可以显示出求解结果之外,还可以建立分析报告以供参考,如运算结果报告、敏感性报告和极限值报告。要建立分析报告,只需在“规划求解结果”对话框的“报告”列表框中选择需要的选项即可。但是,具有整数约束条件的问题不生成敏感性报告及极限值报告。2024/4/185512.3.3利润最大化规划求解步骤5：方案管理。在“规划求解结果”对话框中,选择“保存方案”,打开“保存方案”对话框。输入方案名称“利润最大化基本模型”,单击“确定”即可,如图12.38所示。其结果如图12.39所示。2024/4/185612.3.3利润最大化规划求解温馨小提示如果要查看方案,需选择数据选项卡下的模拟分析,打开“方案管理器”对话框。可以进行显示方案结果、添加新方案、删除和编辑方案、查看方案摘要等操作。2024/4/185712.4Excel在财务预算管理中的应用12.4.1日常业务预算12.4.2企业财务预算2024/4/185812.4.1日常业务预算日常业务预算是指与企业日常经营活动直接相关的经营业务的各种预算,主要包括销售预算、生产预算、直接材料预算、直接人工预算、制造费用预算、产品成本以及期间费用预算,各预算之间相互衔接,是编制现金预算和财务报表预算的基础。主要包括：1.销售预算2.生产预算3.直接材料采购预算4.直接人工预算5.制造费用预算6.产品成本预算7.销售及管理费用预算2024/4/185912.4.1日常业务预算-销售预算【例12.9】

张同学的B传媒公司计划2020年度只生产和销售一种产品。预测销售情况如下:预计产品价格为每件250元,1-4季度预计销售量分别为2000件、2500件、3000件、2500件。每季度的产品销售货款有60%于当期收到现金,有40%于下一个季度收到现金,上年末的应收账款为175000元。要求:编制2020年度的销售预算表。2024/4/186012.4.1日常业务预算-销售预算【操作步骤】：步骤1：新建一个工作簿,将其命名为“预算管理.xls”。将“sheet1”工作表重命名为“销售预算”,并设计销售预算模型,如图12.40所示。2024/4/186112.4.1日常业务预算-销售预算步骤2：计算预计销售收入。在B6单元格中输入公式“=B4*B5”,利用填充柄将该公式向下填充至F6单元格;在F4单元格中输入公式“=SUM(B4:E4)”。步骤3：计算现金收入。在B8单元格中输入公式“=B6*0.6”,C8单元格中输入公式“=B6*0.4”,C9单元格中输入公式“=C6*0.6”;以此类推,计算出D9、D10、E10、E11单元格的销售收现。在F7单元格中输入公式“=SUM(B7:E7)”,利用填充柄将该公式向下填充至F12单元格;在B12单元格中输入公式“=SUM(B7:B11)”,利用填充柄将该公式向下填充至E12单元格。制作完成的销售预算如图12.41所示。2024/4/186212.4.1日常业务预算-销售预算步骤4：定义名称。将单元格区域“销售预算!$A$3:$F$12”定义为“销售预算”。2024/4/186312.4.1日常业务预算-生产预算【例12.10】

承[例12.9]张同学的B传媒公司2020年预计生产情况为年初结存产成品300件,计划1-4季度结存产成品分别为500件、550件、500件、400件。要求:编制2020年度的生产预算表。2024/4/186412.4.1日常业务预算-生产预算【操作步骤】：步骤1：将“sheet2”工作表重命名为“生产预算”,并设计生产预算模型,如图12.42所示。2024/4/186512.4.1日常业务预算-生产预算步骤2：计算预计需求量。这里用到了HLOOKUP函数,它的工作原理和VLOOKUP函数接近,区别在于HLOOKUP函数的查找表是水平排列,而不是垂直排列。语法:HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)。参数lookup_value为需要查找的数值。参数table_array为需要在其中查找数据的单元格区域。参数row_index_num为在table_array区域中待返回的匹配值的行序号(当row_index_num为2时,返回table_array第2行中的数值,为3时,返回第3行的值……)。参数range_lookup为一逻辑值,如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值;如果为FALSE,则返回精确匹配值,如果找不到,则返回错误值#N/A。选中B4单元格,输入公式“=HLOOKUP(B3,销售预算,2,FALSE)”,利用填充柄将该公式向右填充至F4单元格;选中F5单元格,输入公式“=E5”;选中B6单元格,输入公式“=B4+B5”,利用填充柄将该公式向右填充至F6单元格。2024/4/186612.4.1日常业务预算-生产预算步骤3：计算预计生产量。选中C7单元格输入公式“=B5”,利用填充柄将该公式向右填充至E7单元格;选中F7单元格输入公式“=B7”;选中B8单元格,输入公式“=B6-B7”,利用填充柄将公式向右填充至F8单元格。制作完成的销售预算如图12.43所示。2024/4/186712.4.1日常业务预算-生产预算步骤4：定义名称。将单元格区域“生产预算!$A$3:$F$8”定义为“生产预算”。2024/4/186812.4.1日常业务预算-直接材料和采购预算【例12.11】

承[例12.10],张同学的B传媒公司2020年预计材料耗用及采购情况如下:计划年度期初材料结存量720千克,1~4季度结存材料分别为820千克、980千克、784千克、860千克。购买材料货款于当季支付40%,剩余60%于下一个季度支付,应付账款期初余额为120000元。单位产品材料定额耗用量5千克,材料计划单价20元。要求:编制2020年度的直接材料和采购预算表。2024/4/1869【操作步骤】：步骤1：将“sheet3”工作表重命名为“直接材料和采购预算”,并设计直接材料和采购预算模型,如图12.44所示。2024/4/187012.4.1日常业务预算-直接材料和采购预算步骤2：计算预计材料采购量。选中B4单元格,输入公式“=HLOOKUP(B3,生产预算,6,FALSE)”,利用填充柄将该公式填充至F4单元格;选中B6单元格,输入公式“=B4*B5”,利用填充柄将该公式填充至F6单元格;选中B8单元格,输入公式“=B6+B7”,利用填充柄将该公式填充至F8单元格;选中C9单元格,输入公式“=B7”,利用填充柄将该公式填充至E9单元格;选中F9单元格,输入公式“=B9”;选中B9单元格,输入“=B8-B9”,利用填充柄将该公式填充至F10单元格。2024/4/187112.4.1日常业务预算-直接材料和采购预算步骤3：计算现金支出。选中B12单元格,输入公式“=B10*B11”,利用填充柄将该公式填充至F12单元格;选中B14单元格,输入公式“=B12*0.4”,选中C14单元格,输入公式“=B12*0.6”,选中C15单元格,输入公式“=C12*0.4”;同理,计算出D15、D16和E16、E17单元格的值;选中F12单元格,输入公式“=F10*F11”,利用填充柄将该公式填充至F18单元格;选中B18单元格,输入公式“=SUM(B13:B17)”,利用填充柄将该公式填充至E18单元格。制作完成的直接材料和采购预算表如图12.45所示。2024/4/187212.4.1日常业务预算-直接材料和采购预算步骤4：定义名称。将单元格区域“直接材料和采购预算!$A$3:$F$18”定义为“直接材料预算”。2024/4/187312.4.1日常业务预算-直接材料和采购预算12.4.1日常业务预算-直接人工预算【例12.12】

承[例12.11],张同学的B传媒公司2020年预计人工耗用情况如下:假设公司单位产品耗用工时为6小时,单位工时的工资率为5元。要求:编制2020年度的直接人工预算表。2024/4/1874【操作步骤】：步骤1：将工作表命名为“直接人工预算”,并设计直接人工预算模型,如图12.46所示。2024/4/187512.4.1日常业务预算-直接人工预算步骤2：计算预计直接人工成本。选中B4单元格,输入公式“=HLOOKUP(B3,生产预算,6,FALSE)”,利用填充柄将该公式填充至F4单元格;选中B6单元格,输入公式“=B4*B5”,利用填充柄将该公式填充至F6单元格;选中B8单元格,输入公式“=B6*B7”,利用填充柄将该公式填充至F8单元格。制作完成的销售预算如图12.47所示。2024/4/187612.4.1日常业务预算-直接人工预算步骤3：定义名称。将单元格区域“直接人工预算!$A$3:$F$8”定义为“直接人工预算”，选中A3:F18，在左上角直接输入“直接人工预算”，按回车键即可。2024/4/187712.4.1日常业务预算-直接人工预算【例12.13】

承[例12.12],张同学的B传媒公司2019年预计固定制造费用如表12.5所示,预计变动制造费用工时分配率如表12.6所示,要求:编制2020年度的制造费用预算表(变动制造费用根据直接人工工时分配)。表12.5固定制造费用表单位:元表12.6变动制造费用工时分配率表2024/4/187812.4.1日常业务预算-制造费用预算项目第1季度第2季度第3季度第4季度全年修理费用800120011009004000折旧1830018300183001830073200管理费用875087508750875035000保险费用15002000120013006000财产税7507507507503000项目间接人工间接材料维护费用水电费用机物料分配率0.20.10.150.250.0512.4.1日常业务预算-制造费用预算【操作步骤】：步骤1：新建工作表,将工作表重命名为“制造费用预算”,并设计制造费用预算模型,如图12.48所示。2024/4/1879步骤2：计算变动制造费用。选中B5单元格输入公式“=HLOOKUP(B3,直接人工预算,4,FALSE)*$B$24”,利用填充柄将该公式填充至F5单元格;选中B6单元格,输入公式“=HLOOKUP(B3,直接人工预算,4,FALSE)*$C$24”,利用填充柄将该公式填充至F6单元格;选中B7单元格,输入公式“=HLOOKUP(B3,直接人工预算,4,FALSE)*$D$24”,利用填充柄将该公式填充至F7单元格;选中B8单元格,输入公式“=HLOOKUP(B3,直接人工预算,4,FALSE)*$E$24”,利用填充柄将该公式填充至F8单元格;选中B9单元格,输入公式“=HLOOKUP(B3,直接人工预算,4,FALSE)*$F$24”,利用填充柄将该公式填充至F9单元格;选中B10单元格,输入公式“=SUM(B5:B9)”,利用填充柄将该公式填充至F10单元格。2024/4/188012.4.1日常业务预算-制造费用预算步骤3：计算固定制造费用和制造费用合计。选中B17单元格,输入公式“=SUM(B12:B16)”,利用填充柄将该公式填充至F17单元格;选中B18单元格,输入公式“=B10+B17”,利用填充柄将该公式填充至F18单元格;选中B19单元格,输入公式“=B13”,利用填充柄将该公式填充至F19单元格。步骤4：计算预计现金支出合计。选中B20单元格,输入公式“=B18-B19”,利用填充柄将该公式填充至F20单元格。制作完成的制造费用预算如图12.49所示。步骤5：定义名称。将单元格区域“制造费用预算!$A$3:$F$20”定义为“制造费用预算”。2024/4/188112.4.1日常业务预算-制造费用预算12.4.1【例12.14】

承[例12.13],要求:在已经编制好的销售预算、生产预算、直接材料预算、直接人工预算和制造费用预算的基础上,编制张同学的B传媒公司2020年度的生产预算表。2024/4/188212.4.1日常业务预算-产品成本预算12.4.1日常业务预算-产品成本预算【操作步骤】：步骤1：新建工作表。将工作表重命名为“产品成本预算”,并设计产品成本预算模型,如图12.50所示。2024/4/188312.4.1日常业务预算-产品成本预算步骤2：计算单位产品成本。选中B7单元格,输入公式“=制造费用预算!F10/直接人工预算!F6”;选中B8单元格,输入公式“=制造费用预算!F17/直接人工预算!F6”;选中D5单元格,输入公式“=B5*C5”,利用填充柄将该公式填充至D8单元格;选中D9单元格,输入公式“=SUM(D5:D8)”。步骤3：计算生产成本、期末成本和销货成本。选中E5单元格,输入公式“=HLOOKUP("全年",生产预算,6,FALSE)*D5”,利用填充柄将该公式填充至E8单元格;选中F5单元格,输入公式“=HLOOKUP("全年",生产预算,3,FALSE)*D5”,利用填充柄将该公式填充至F8单元格;选中G5单元格,输入公式“=HLOOKUP("全年",销售预算,2,FALSE)*D5”,利用填充柄将该公式填充至G8单元格;选中E9单元格,输入公式“=SUM(E5:E8)”,利用填充柄将该公式填充至G9单元格。制作完成的产品成本预算如图12.50所示。2024/4/188412.4.1日常业务预算-产品成本预算步骤4：定义名称。将单元格区域“产品成本预算!$A$3:$G$9”定义为“产品成本预算”。2024/4/188512.4.1日常业务预算-销售及管理费用预算【例12.15】

承[例12.14],张同学的B传媒公司2020年预计管理费用情况如表12.7所示,预计销售费用占销售额的百分比情况如表12.8所示,要求:编制该公司2020年度的销售及管理费用预算表。表12.7管理费用表单位:元表12.8销售费用占销售额的百分比情况表2024/4/1886项目第1季度第2季度第3季度第4季度管理人员工资12000120001200012000办公费4055405540554055工会经费300300300300无形资产摊销2500250025002500折旧费用12500125001250012500项目工资广告费运输费业务费保管费比率4.50%1.20%0.50%2.00%0.20%

【操作步骤】：步骤1：新建工作表,将工作表重命名为“销售及管理费用预算”,并设计销售及管理费用预算模型,如图12.51所示。2024/4/188712.4.1日常业务预算-销售及管理费用预算步骤2：计算销售费用及合计数。选中B5单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)*$B$24”,利用填充柄将该公式填充至F5单元格;选中B6单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)*$C$24”,利用填充柄将该公式填充至F6单元格;选中B7单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)*$D$24”,利用填充柄将该公式填充至F7单元格;选中B8单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)*$E$24”,利用填充柄将该公式填充至F8单元格;选中B9单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)*$F$24”,利用填充柄将该公式填充至F9单元格;选中B10单元格,输入公式“=SUM(B5:B9)”,利用填充柄将该公式填充至F10单元格。2024/4/188812.4.1日常业务预算-销售及管理费用预算步骤3：计算管理费用合计数及费用预算总额。选中F12单元格,输入公式“=SUM(B12:E12)”,利用填充柄将该公式填充至F17单元格;选中B17单元格,输入公式“=SUM(B12:B16)”,利用填充柄将该公式填充至E17单元格;选中B18单元格,输入公式“=B10+B17”,利用填充柄将该公式填充至F18单元格。2024/4/188912.4.1日常业务预算-销售及管理费用预算步骤4：计算销售及管理费用现金支出。选中B19单元格,输入公式“=B15+B16”,利用填充柄将该公式填充至F19单元格;选中B20单元格,输入公式“=B18-B19”,利用填充柄将该公式填充至F20单元格。制作完成的产品成本预算如图12.52所示。2024/4/189012.4.1日常业务预算-销售及管理费用预算步骤5：定义名称。将单元格区域“销售及管理费用预算!$A$3:$F$20”定义为“销售及管理费用预算”。2024/4/189112.4.1日常业务预算-销售及管理费用预算12.4.2企业财务预算企业财务预算是指一系列专门反映企业未来一定期限内预计财务状况、经营成果和现金收支等价值指标的各种预算的总称。它具体包括专门决策预算、现金预算、预计利润表、预计资产负债表等内容。主要包括：1.专门决策预算2.现金预算3.预算利润表4.预计资产负债表2024/4/189212.4.2企业财务预算2024/4/1893

企业财务预算是指一系列专门反映企业未来一定期限内预计财务状况、经营成果和现金收支等价值指标的各种预算的总称。它具体包括专门决策预算、现金预算、预计利润表、预计资产负债表等内容。12.4.2企业财务预算1.专门决策预算专门决策预算主要是长期投资预算,又称资本支出预算,通常是指与项目投资决策相关的专门预算,它往往涉及长期建设项目的资金投放与筹集,并经常跨越多个年度。编制专门决策预算的依据是财务可行性分析资料,以及企业筹资决策资料。2024/4/189412.4.2企业财务预算-专门决策预算【例12.16】张同学的B传媒公司2020年预计企业资本投资情况如下:年度内安装一条新的生产线,并于年内安装完毕,年末投入使用,有关投资与筹资预算见表12.9。要求:编制2020年度的专门决策预算表。

表12.9专门决策预算表2024/4/1895项目第1季度第2季度第3季度第4季度投资支出预算50000400007000080000借入长期借款

8000012.4.2企业财务预算【操作步骤】：步骤1：新建工作表,将工作表重命名为“专门决策预算”,并设计专门决策预算模型,如图12.53所示。2024/4/1896图12.53专门决策预算模型12.4.2企业财务预算-专门决策预算12.4.2企业财务预算步骤2：计算全年合计数。选中F4单元格,输入公式“=SUM(B4:E4)”,利用填充柄将该公式填充至F5单元格。制作完成的产品成本预算如图12.54所示。2024/4/1897图12.54专门决策预算计算结果12.4.2企业财务预算-专门决策预算12.4.2企业财务预算步骤3：定义名称。将单元格区域“专门决策预算!$A$3:$F$5”定义为“专门决策预算”。2024/4/189812.4.2企业财务预算-专门决策预算12.4.2企业财务预算2.现金预算现金预算是以日常业务预算和专门决策预算为依据编制的,反映预算期内预计现金收入与现金支出,包括现金收入、现金支出、现金多余或不足的计算,以及不足部分的筹措和多余部分的利用方案。2024/4/189912.4.2企业财务预算-现金预算12.4.2企业财务预算【例12.17】承[例12.16],张同学的B传媒公司2020年预计现金收支情况如下:公司年初现金余额为80000元,每季支付各种流转税35000元,前三季度每季度预交所得税50000元,年末汇缴89440元,年末支付股利250000元,最低现金持有量为50000元。要求:编制2020年度的现金预算表。2024/4/1810012.4.2企业财务预算-现金预算12.4.2企业财务预算【操作步骤】：步骤1：新建工作表,将工作表重命名为“现金预算”,并设计现金预算模型,如图12.55所示。2024/4/1810112.4.2企业财务预算-现金预算步骤2：计算可供使用现金。选中C4单元格,输入公式“=B29”,利用填充柄将该公式填充至E4单元格;选中F4单元格,输入公式“=B4”;选中B6单元格,输入公式“=HLOOKUP(B3,销售预算,10,FALSE)”,利用填充柄将该公式填充至F6单元格;选中B8单元格,输入公式“=B4+B6+B7”,利用填充柄将该公式填充至F8单元格。2024/4/1810212.4.2企业财务预算-现金预算步骤3：计算现金支出和现金余缺。选中B11单元格,输入公式“=HLOOKUP(B3,直接材料预算,16,FALSE)”,利用填充柄将该公式填充至F11单元格;选中B12单元格,输入公式“=HLOOKUP(B3,直接人工预算,6,FALSE)”,利用填充柄将该公式填充至F12单元格;选中B13单元格,输入公式“=HLOOKUP(B3,制造费用预算,18,FALSE)”,利用填充柄将该公式填充至F13单元格;选中B14单元格,输入公式“=HLOOKUP(B3,销售及管理费用预算,18,FALSE)”,利用填充柄将该公式填充至F14单元格;选中F15单元格,输入公式“=SUM(B15:E15)”,利用填充柄将该公式填充至F17单元格;选中B18单元格,输入公式“=HLOOKUP(B3,专门决策预算,2,FALSE)”,利用填充柄将该公式填充至F18单元格;选中B19单元格,输入公式“=SUM(B11:B18)”,利用填充柄将该公式填充至F19单元格;选中B20单元格,输入公式“=B8-B19”,利用填充柄将该公式填充至F20单元格。2024/4/1810312.4.2企业财务预算-现金预算步骤4：计算期末现金余额。选中B22单元格,输入公式“=HLOOKUP(B3,专门决策预算,3,FALSE)”,利用填充柄将该公式填充至F22单元格;选中F23单元格,输入公式“=SUM(B23:E23)”,利用填充柄将该公式填充至F28单元格;选中E25单元格,输入公式“=E22*0.1*0.25”;选中B29单元格,输入公式“=B20+B22+B23+B24-B25-B26-B27-B28”,利用填充柄将该公式填充至F29单元格。2024/4/1810412.4.2企业财务预算-现金预算步骤5：设置B20:F20单元格区域、B29:F29单元格区域的格式为“数值”,小数位数为“0”,同时负数用红色括号显示的格式。此时,企业的年末现金持有量为53802元,大于企业最低现金持有量50000元。制作完成的现金预算如图12.56所示。2024/4/1810512.4.2企业财务预算-现金预算步骤6：定义名称。将单元格区域“现金预算!$A$3:$F$29”定义为“现金预算”。步骤7：假设公司前三季度进行短期投资分别为10000元、40000元和80000元,此时,年末的现金持有量为负数,如图12.57所示。2024/4/1810612.4.2企业财务预算-现金预算这种情况下,公司应该将前三季度的短期投资出售,以缓解公司现金不足的情况,维持公司的正常生产运营,假设第四季度出售短期投资130000元(期间较短,我们假设收益为零),则计算结果如图12.58所示。2024/4/1810712.4.2企业财务预算-现金预算【例12.18】

承[例12.17],要求:根据上述例题资料编制的预算表,编制2020年度的预计利润表。步骤1：新建工作表,将工作表重命名为“预计利润表”,并设计预计利润表模型,如图12.59所示。2024/4/1810812.4.2企业财务预算-预计利润表步骤2：计算销售毛利。选中B4单元格,输入公式“=HLOOKUP(B3,销售预算,4,FALSE)”,利用填充柄将该公式填充至F4单元格;选中B5单元格,输入公式“=HLOOKUP(B3,销售预算,2,FALSE)*产品成本预算!$D$9”,利用填充柄将该公式填充至F5单元格;选中B6单元格,输入公式“=B4-B5”,利用填充柄将该公式填充至F6单元格。2024/4/1810912.4.2企业财务预算-预计利润表步骤3：计算营业利润。选中B7单元格,输入公式“=HLOOKUP(B3,销售及管理费用预算,8,FALSE)”,利用填充柄将该公式填充至F7单元格;选中B8单元格,输入公式“=HLOOKUP(B3,销售及管理费用预算,15,FALSE)”,利用填充柄将该公式填充至F8单元格;选中B9单元格,输入公式“=HLOOKUP(B3,现金预算,23,FALSE)”,利用填充柄将该公式填充至F9单元格;选中B10单元格,输入公式“=B6-B7-B8-B9”,利用填充柄将该公式填充至F10单元格。2024