Excel财务管理建模与应用（第2版）课件 第8、9章 全面预算编制、销售预测与利润规划

8全面预算编制本章主要内容销售预算模型生产预算模型直接材料采购预算模型直接人工预算模型制造费用预算模型产品成本预算模型销售费用预算模型管理费用预算模型财务费用预算模型2Excel会计应用8.1全面预算业务场景分析8.1.1预算管理内容（1）销售预算是以企业经营成果为核心，包括销售预算、产品预算、产品销售收入预算、销售费用预算等。（2）生产预算包括生产预算、直接材料预算、直接人工预算、制造费用预算、产品成本预算等。（3）财务预算是关于资金筹措和使用的预算，包括长期的资本预算和短期的现金收支与信贷预算。（4）其他预算。3Excel会计应用8.1.3预算管理的业务场景4Excel会计应用8.2Excel业务预算编制8.2.1业务预算编制内容分析（1）销售预算。（2）生产预算。（3）直接材料预算。（4）直接人工预算。（5）制造费用预算。5Excel会计应用6Excel会计应用8.2.2销售预算模型7Excel会计应用8.2.2销售预算模型8Excel会计应用8.2.3生产预算模型9Excel会计应用8.2.3生产预算模型8.2.4直接材料采购预算模型10Excel会计应用8.2.4直接材料采购预算模型11Excel会计应用8.2.4直接材料采购预算模型12Excel会计应用8.2.4直接材料采购预算模型13Excel会计应用冻结表格的行或列8.2.4直接材料采购预算模型14Excel会计应用组合表格的行或列8.2.5直接人工预算模型15Excel会计应用8.2.5直接人工预算模型16Excel会计应用8.2.6制造费用预算模型17Excel会计应用8.2.6制造费用预算模型18Excel会计应用8.2.6制造费用预算模型19Excel会计应用【公式】|【错误检查】8.2.6制造费用预算模型20Excel会计应用8.2.6制造费用预算模型追踪错误21Excel会计应用22Excel会计应用8.3Excel财务预算编制8.3.1财务预算编制内容分析（1）产品成本预算。（2）销售及管理费用预算。（3）现金预算。（4）预计利润表。23Excel会计应用8.3.2产品成本预算模型24Excel会计应用8.3.2产品成本预算模型25Excel会计应用8.3.2产品成本预算模型显示公式26Excel会计应用8.3.2产品成本预算模型追踪引用单元格27Excel会计应用8.3.2产品成本预算模型追踪从属单元格28Excel会计应用8.3.3销售费用预算模型【例】接上例，公司在预算年度内，预计销售费用各项开支数额及计算如下：工资每季均为5000000元，业务费、运输费、保管费、包装费每季均为预计销售额的0.1%，第二季度广告费开支144000元。编制销售费用预算表。29Excel会计应用8.3.3销售费用预算模型单元格公式注释C5=销售预算!$E$7*0.1%业务费C7=销售预算!$E$7*0.1%包装费C8=销售预算!$E$7*0.1%保管费C9=销售预算!$E$7*0.1%运输费C10=SUM(C4:C9)合计30Excel会计应用8.3.4管理费用预算模型【例】接上例，公司在预算年度内，预计管理费用各项开支数额如下：工资8000000元，折旧费300000元，办公费180000元，差旅费300000元，物料消耗50000元，无形资产摊销50000，工会经费120000元，印花税1000元，其他15000元。编制管理费用预算表。31Excel会计应用8.3.4管理费用预算模型32Excel会计应用8.3.4管理费用预算模型【公式】|【监视窗口】33Excel会计应用8.3.4管理费用预算模型【公式】|【监视窗口】34Excel会计应用8.3.5财务费用预算模型【例】接上例，公司在预算年度内，预计财务费用各项开支数额如下：利息支出3000000元，利息收入2800000元，汇兑损失40000元，汇兑收益36000元，手续费4000元，其他200000元。编制财务费用预算表。35Excel会计应用8.3.5财务费用预算模型36Excel会计应用8.3.6现金预算模型【例】接上例，公司在预算年度内，其他相关资料如下：预计预算期第四季度营业外收入为76000元；营业外支出第一季度为7600元，第二季度为18000元，第四季度为15600元。根据资本预算，该公司计划预算期第二季度以分期付款方式购入一套设备，价值817000元，该季度付款550000元，第三季度付款167000元，其余第四季度支付。预计各季度支付所得税2175000元，销售税金736000元，第二、四季度各支付股利400000元。假设该公司2018年末，资产负债表所示货币资金余额为100000元。编制现金预算表。37Excel会计应用8.3.6现金预算模型38Excel会计应用8.3.6现金预算模型【公式】|【显示公式】39Excel会计应用8.3.6现金预算模型条件格式40Excel会计应用8.3.6现金预算模型修改条件格式规则41Excel会计应用8.3.6现金预算模型最终效果9销售预测与

利润规划本章主要内容预测函数：FORECAST()、TREND()、GROWTH()、LINEST()和LOGEST()销售分布数据地图销售收入预测模型销售收入趋势预测模型规划求解的方法规划求解模型43Excel会计应用9.1销售预测与利润规划业务场景分析9.1.1销售预测1、定性预测法2、调查分析法3、趋势分析法：移动平均法、指数平滑法和季节预测法4、因果分析法44Excel会计应用9.1.2利润规划目标利润=预计收入－标准成本在利润规划的本、量、利分析中，存在着盈亏平衡点。在该点上，企业的总成本和总收入相等而出现不盈不亏的现象。45Excel会计应用9.1.3销售情况分析企业产品销售情况对企业是至关重要的，因此除了了解企业的销售总额外，常常还要对企业产品销售的分布情况进行统计分析，也就是对不同产品在不同地区的销售情况进行分析，从中寻找销售不利的原因和进一步扩大市场份额的途径。46Excel会计应用9.2Excel企业销售情况分析9.2.1创建销售分布数据地图47Excel会计应用48Excel会计应用9.3Excel销售收入预测9.3.1销售收入预测模型【例】已知2009年到2018年公司的销售收入如图所示，试预测该公司2019年的销售收入。49Excel会计应用9.3.1销售收入预测模型安装【数据分析工具】50Excel会计应用9.3.1销售收入预测模型计算移动平均数9.3.1销售收入预测模型一次平均和二次平均51Excel会计应用9.3.1销售收入预测模型输出图表52Excel会计应用9.3.2预测函数1．TREND函数语法：TREND(known_y's,known_x's,new_x's,const)功能：TREND()使用最小二乘法计算对已知数据进行最佳直线拟和，并返回描述此直线的数组。即找到适合给定的数组known_y's和known_x's的直线并返回指定数组new_x's值在直线上对应的y值。53Excel会计应用9.3.2预测函数54Excel会计应用【例】已知2009—2018年的销售收入，如图所示。试用TREND函数预测该公司2019年的销售收入。9.3.2预测函数55Excel会计应用2．FORECAST.LINEAR函数语法：FORECAST.LINEAR(x,known_y's,known_x's)功能：已知x值和y值，利用线性回归对未来值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。9.3.2预测函数56Excel会计应用【例】采用前面的资料，试用FORECAST.LINEAR函数预测该公司2019年的销售收入。9.3.2预测函数57Excel会计应用3．GROWTH函数语法：GROWTH(known_y's,known_x's,new_x's,const)功能：GROWTH工作表函数用通过拟合满足现有x值和y值的指数曲线预测未来值。9.3.2预测函数58Excel会计应用【例】采用前面的资料，试用GROWTH函数预测该公司2019年的销售收入。9.3.2预测函数59Excel会计应用4．LINEST函数语法：LINEST(known_y's,known_x's,const,stats)功能：使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。9.3.2预测函数60Excel会计应用5．LOGEST函数语法：LOGEST(known_y's,known_x's,const,stats)功能：在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。此函数返回数值数组，必须以数组公式的形式输入。9.3.3销售收入趋势预测模型61Excel会计应用【例】采用前面的资料，公司管理层希望得到一张图表以帮助预测和规划公司未来三年的销售收入状况。1.建立销售收入图表9.3.3销售收入趋势预测模型2.选择图表布局和图表样式62Excel会计应用9.3.3销售收入趋势预测模型3.添加销售收入趋势线63Excel会计应用9.3.4因果分析64Excel会计应用1.单变量求解【例】现在已知某公司2018年利润表，如图所示。分析在现有的水平下，将利润总额提高到5000000000元，相应的营业收入要增加多少?9.3.4因果分析65Excel会计应用1.单变量求解9.3.4因果分析66Excel会计应用2.单变量模拟运算表【例】仍然引用q前一例，要求得到不同营业收入下的营业利润。9.3.4因果分析输入引用列的单元格模拟运算结果67Excel会计应用9.3.4因果分析68Excel会计应用3.双变量模拟运算表【例】仍然引用前一例子采用的模拟数据，分析不同营业收入和不同投资收益的影响。9.3.4因果分析69Excel会计应用输入引用行的单元格、输入引用列的单元格9.3.4因果分析70Excel会计应用输入引用行的单元格、输入引用列的单元格9.4Excel利润规划71Excel会计应用9.4.1分析规划求解问题72Excel会计应用生产条件列表9.4.1分析规划求解问题73Excel会计应用建立生产规划模型9.4.1分析规划求解问题74Excel会计应用在Excel中建立生产规划基础模型9.4.2建立规划求解模型75Excel会计应用规划求解参数9.4.2建立规