《财务会计Excel实务》-学习情境六

学习任务1

Excel销售预测与预算一、Excel移动平均预测并盖章【技能与理论准备】1.移动平均预测移动平均预测是从n期的销售量中选取m期（m<n/2）的数据，求其算术平均数，并不断向后移动，以最后一组平均数作为未来销售预测值的一种方法。其计算公式如下：下一页返回学习任务1

Excel销售预测与预算2.Excel日期与时间函数Excel提供了大量的日期与时间函数，可进行日期或时间的计算。如年份函数YEAR、月份函数MONTH、日函数DAY、当前日期函数TODAY、当前日期和时间函数NOW、星期函数WEEKDAY、小时函数HOUR、分钟函数MINUTE、秒钟函数SECOND等。3.Excel2003绘图工具Excel2003绘图工具栏及功能见图6-1。4.Excel2003艺术字工具Excel2003艺术字工具栏及功能见图6-2。上一页下一页返回学习任务1

Excel销售预测与预算5.Excel2007及Excel2013绘图工具Excel2007及Excel2013绘图工具的“格式”选项卡如图6-3所示，从左至右依序是插入形状、形状样式、艺术字样式、排列与大小5个功能组；所以它没有独立的艺术字工具栏。同时，该选项卡只有在需要时才显示，即在插入或选定了形状、艺术字后才出现。6.网格线的显示与隐藏Excel2003工作表中的网格线主要用于屏幕显示，以便进行表格编辑，但它不会被打印出来；若不愿显示，则选择“工具/选项”菜单命令，在“视图”卡片中取消其显示。上一页下一页返回学习任务1

Excel销售预测与预算在该界面还可选择是否显示行号列标、水平和垂直滚动条、工作表标签，是否显示编辑栏、状态栏等；若取消这些显示，则编辑工作表时较麻烦。Excel2007及Excel2013的上述选项设置，在“Office按钮/Excel选项/高级”中进行设置；也可在“视图”选项卡进行网格线的取消与显示。二、Excel回归直线函数技术回归直线法又称最小平方法，它是根据坐标系中时间序列中的销售量或销售额的观测数据，确定一条误差平方和最小的直线，据此直线预测分析未来销售量或销售额的方法。上一页下一页返回学习任务1

Excel销售预测与预算【技能与理论准备】1.Excel回归直线法Excel运用回归直线法的前提条件是，时间序列与销售数据之间，基本呈线性关系；因此，它们之间的关系可用直线方程Y=a+bX描述。Excel通过线性方程截距函数INTERCEPT确定参数a的值，线性方程斜率函数SLOPE确定参数b的值，它们的函数公式如下：=INTERCEPT（Known_y’s,Known_x’s）=SLOPE（Known_y’s,Known_x’s）2.Excel单元区域名称上一页下一页返回学习任务1

Excel销售预测与预算Excel对单元格区域的引用，可使用单元坐标，如B2:G2、$B$3:$G$3等方式进行单元格的相对引用或绝对引用；也可通过插入单元区域名称的方式进行单元区域的引用。单元名称的使用方法如下：（1）选定要定义单元名称的单元格或单元格区域，进行该单元区域的名称定义。以后要引用该区域时，直接在公式中键入相应的单元名称即可。（2）单元名称中的字符可以是字母、数字、句号和下划线；名称不能与单元坐标名称相同；名称可使用多个单词；名称中不能有空格；名称中不区分大小写；名称使用绝对单元格引用。上一页下一页返回学习任务1

Excel销售预测与预算（3）名称运用于同一工作簿的所有工作表，即在本工作簿的不同工作表之间，可直接键入单元名称而实现不同工作表之间的引用；所以在同一工作簿中不要定义重复的单元名称，否则后定义的名称将替代以前的名称。三、Excel相关回归分析技术回归分析用模拟的直线方程式Y=a+bX来预测销量，客观上需要知道销量（因变量）与时间序列（自变量）之间的相关程度。Excel中可将直线拟合函数LINEST作为数值引用函数INDEX的嵌套函数的方法，进行相关系数R2、截距a、斜率b的计算。【技能与理论准备】1.直线拟合函数LINEST上一页下一页返回学习任务1

Excel销售预测与预算2.用嵌套函数计算斜率b、截距a、相关系数R2的函数3.相关系数的含义在统计学中，相关系数R2的值为1则完全相关，为0则不相关；大于0.8则显著相关；在0.5~0.8则相关；小于0.5则弱相关。只有相关系数大于0.5时，回归直线法的预测结果才具有参考价值。上一页下一页返回学习任务1

Excel销售预测与预算四、Excel销售预算技术【技能与理论准备】销售预算是全面预算的起点，它以销售预测为基础，确定未来一年各月或各季的销售量、单价、销售收入；同时还应确定销售收入中的现金收入预计数，它为本期（月、季）现销额加上本期收回上期赊销额之和。【工作任务6-4】某公司生产经营一种产品，产品销售单价为240元。2008年12月中旬编制下年财务预算。经预测本年第4季度销售收入为200000万元，2009年各季销量（万台）分别为850、780、800、840。上一页下一页返回学习任务1

Excel销售预测与预算该公司现销比例在40%~70%，其余为赊销款，在下一季度内全部收回。请编制2009年的销售预算。【工作成果】在Excel中设计销售预算模型与滚动条，如图6-14所示。【工作过程】（1）建立一个工作表标签名为“销售预算”的工作表，并设计表格，录入已知数据，如图6-14所示。（2）设计滚动条与代码取值，方法如下：①插入滚动条。选择“视图/工具栏/窗体”菜单命令调出窗体工具栏（Excel2007为“开发工具/插入/表单控件”），单击其上的滚动条按钮，在F1单元格中拖动一个适当大小的滚动条控件。上一页下一页返回学习任务1

Excel销售预测与预算②设置控件格式。右击F1单元格中的滚动条进入“设置控件格式”界面，如图6-15所示，在“控制”卡片的最小值、最大值、步长、页步长、单元格链接中分别键入40、70、1、10、$F$1，并勾选“三维阴影”选项。③代码取值与隐藏。设计代码的取值公式为G1=F1/100。将F1单元格中的代码值居中隐藏于滚动条之后。（3）键入计算公式。销售收入为销量乘销售单价；收上季款为上季销售收入乘赊销比例（1-现销百分比）；收本季款为本季销售收入乘现销百分比。单元公式请参阅图6-14。上一页返回学习任务2

Excel生产预算与规划求解一、Excel生产预算技术【技能与理论准备】1.Excel表间取数企业全面预算的最大特点是以销定产、以产定耗、以耗定购，所以生产预算必须根据销售预算编制。Excel提供的表间取数方法非常适合这种数据之间的链接计算，当数据源变动时，引用这些数据的其他工作表也会随之变动，不必逐一重算。引用表间数据可用单元名称引用；也可用单元坐标引用，即甲工作表中需引用乙工作表的数据时，应在甲工作表的单元格中键入：=乙工作表!XX，其中表名后要加英文的“!”，XX表示要引用的单元或单元区域。下一页返回学习任务2

Excel生产预算与规划求解2.Excel保护工作表编制的预算表需要在企业的各个部门之间传递、执行，为了防止无权限者修改数据，可以进行工作表的保护。默认情况下，保护工作表后不能对各单元格进行编辑（即锁定），但可以选择单元格或单元格区域，选定后工作表上部的编辑框中也会显示单元格的数值或公式。若不愿在编辑框中显示数值或公式，则应进行单元格内容的隐藏；隐藏是指隐藏数值、公式等在编辑框中的显示，工作表中各单元格的计算结果是不会隐藏的。二、Excel生产规划求解技术【技能与理论准备】上一页下一页返回学习任务2

Excel生产预算与规划求解1.Excel规划求解Excel提供了功能强大的单变量求解、双变量求解、规划求解、模拟运算表、数据透视图和数据透视表等数据分析工具。规划求解是一组命令的组成部分，这些命令有时也称为假设分析工具，它可以求出目标单元格中公式的最优值。从功能角度讲，规划求解可以求解各种优化问题，如线型规划、整数规划和网络规划等。从工作原理角度讲，它是通过调整可变单元格中的数值，从目标单元格的公式中求得所需结果，这样重复迭代，直到获得最优的数值解。在迭代求取最优解的过程中，各种约束条件对获得最优解的效率和精度都有很大的影响；使用者可以对可变单元格的数值应用约束条件，而且约束条件可以引用其他单元格。上一页下一页返回学习任务2

Excel生产预算与规划求解2.工作簿的加密工作簿（由多个工作表组成）的加密包括加密保存和加密保护。加密保存是用密码将工作簿保存起来，没有密码将无法打开工作簿，从而防止单位商业秘密的泄露。加密保护是指对工作簿编辑等权限的保护，加密保护工作簿后能够打开工作簿，但限制其对工作簿的结构、窗口进行编辑或改变。其中的结构保护是指禁止对工作簿中的各工作表进行复制、移动、插入、重命名、隐藏和取消隐藏等操作。窗口保护是指禁止在每次打开工作簿时，改变工作簿的固定位置和大小；这种保护下，打开的Excel窗口的右上角只有程序窗口的最大化、最小化等按钮，没有工作簿窗口的最大化、最小化、关闭等按钮。上一页返回学习任务3

Excel本量利动态分析一、Excel本量利分析模型【技能与理论准备】1.本量利分析含义本量利分析是指成本、业务量和利润三者依存关系分析的简称，它是在成本性态分析的基础上，通过对成本、业务量和利润三者的关系分析，建立数学化的分析模型和图式，进而揭示变动成本、固定成本、销售量、销售单价和利润等因素之间的内在规律性联系，以便为企业进行预测、决策、规划和控制提供有效的财务信息的一种定量分析方法。2.本量利分析公式下一页返回学习任务3

Excel本量利动态分析保利点销量=（固定成本总额+目标利润）÷（销售单价-单位变动成本）实际盈亏=（销售单价-单位变动成本）×实际销售量-固定成本总额保本点销量=固定成本总额÷（销售单价-单位变动成本）保本作业率=保本点销量÷预计销量安全边际率=1-保本作业率3.Excel运用技巧Excel中的滚动条、微调按钮、单选按钮、列表框、复选框、条件函数、绝对值函数等，能适应复杂多变的经济事项的分析、计算，所以本量利分析时必须掌握它们的运用技巧。上一页下一页返回学习任务3

Excel本量利动态分析二、Excel本量利敏感分析模型【技能与理论准备】1.敏感分析敏感分析是在求得某个数学模型的最优解后，研究该模型中某个或若干个参数允许变化到何种范围，仍能使原最优解保持不变；或当参数变化超出允许范围，原最优解已不能保持最优性时，提供一种简便的计算方法，重新求得最优解。在进行利润敏感分析时，为了简化计算，假设利润只受销售单价、单位变动成本、销售量和固定成本总额的影响，并且假设各因素均独立变动，即其中一个因素变动不会引起其他因素的变动。上一页下一页返回学习任务3

Excel本量利动态分析反映利润敏感性的指标是敏感系数，它说明各有关因素变动对利润的影响程度。敏感系数计算公式如下：某参数敏感系数