财务管理必会excel之销售分析.pdf

财务管理必会Excel应用100例办公室之 174 第12章 销售分析 作为企业经营的重要环节 销售的重要性不言而喻 销售利润是企 业追求的目标 对销售利润及其影响因素的分析有着非常重要的作用 同 样 对于销售情况的预测也是一项十分重要的工作 本章也会在这方面进 行讲解 本章要点 1 OR函数 2 TREND函数 3 FORECAST函数 销售数据表效果图 销售预测分析效果图 Excel支持自动填充功能 但如果要输入重复数据的单元格是离散的 或要输入的重复数据是星期几 几月几 日等 自动填充功能就不能完成任务了 这时该怎么办 175 销售分析CHAPTER 1 2 12 1 销售利润分析 销售利润受到许多因素的影响 比如产品的售价变动 成本的增减 销售数量的变 化等 本节将展示如何将利润的变化额以定量的形式分解到各个因素中 12 1 1 创建销售数据表 销售数据表效果图 要创建销售数据表 计算销售利润时要使用公式 销售利润 销售收入 销售成本 销售税金 步骤01 新建工作表 新建一个工作簿 并在工作簿中重命名其中一个工作表 删除其他的 输入表格的 标题 并设置格式等 步骤02 输入数据 在数据区域输入每个产品的相关数据 可以先选中所有要输入重复数据的单元格 然后输入该数据 最后在按住Ctrl键后打回车键 财务管理必会Excel应用100例办公室之 176 步骤03 计算上年产品销售利润 在F3单元格输入公式 C3 D3 E3 并复制公式至F4 F9区域 步骤04 计算本年产品销售利润 在K3单元格输入公式 H3 I3 J3 并复制公式至K4 K9区域 步骤05 计算上年累计数 在A13单元格输入 合计 在B13单元格输入公式 SUM B3 B9 并复制公式至 C13 F13区域 假如Excel工作表有33333列 如果第23456列的数据需要修改 怎么办 177 销售分析CHAPTER 1 2 步骤06 计算本年累计数 在G13单元格输入公式 SUM G3 G9 并复制公式至H13 K13区域 12 1 2 创建单位成本表 有了销售数据表 接下来可以制作各种产品上年同期和本年实际的单位成本表 单位成本表效果图 步骤01 设置单位成本表 我们紧接销售数据表输入单位成本表的标题 并设置格式等 无论用鼠标拖滚动条 还是用键盘上的翻页键 都需要很长时间 这时候 试一试如果按住Shift键拖滚动 条 很快就能找到 财务管理必会Excel应用100例办公室之 178 步骤02 计算上年同期单位成本各项指标 在L3单元格输入公式 IF B3 0 0 ROUND C3 B3 4 复制公式到L3 O3区域 步骤03 计算上年总利润 在P3单元格输入公式 ROUND O3 B3 2 复制公式到P4 P9区域 步骤04 计算本年同期单位成本各项指标 在Q3单元格输入公式 IF G3 0 0 ROUND H3 G3 4 复制公式到Q3 T9区域 很多用户在制作财务报表的时候 经常需要将数字表示为大写的数字金额 如果只依靠单纯的输入的话 将 会非常的麻烦 怎样自动转换大写金额 179 销售分析CHAPTER 1 2 步骤05 计算本年总利润 在U3单元格输入公式 ROUND T3 G3 2 复制公式到U4 U9区域 步骤06 计算上年单位成本各项累计数 在L13单元格输入公式 SUM L3 L9 复制公式到M13 P13区域 步骤07 计算本年单位成本各项累计数 在Q13单元格输入公式 SUM Q3 Q9 复制公式到R13 U13区域 首先鼠标右键单击需要用大写金额显示的单元格 在弹出的菜单中选择 设置单元格格式 选择 数字 选项卡 并且在 分类 列表中选择 自定义 在类型框中输入 DBNum2 0 百 0 拾 0 元 0 角 整 财务管理必会Excel应用100例办公室之 180 12 1 3 创建增减变化数据表 接下来我们要制作单位成本增减变化数据表 这个部分将要使用到的公式 利润变化 本年实际总利润 上年同期总利润 销售影响 本年实际销量 上年同期销量 上年同期单位利润 售价影响 本年实际售价 上年同期售价 本年实际销量 税金影响 上年同期单位税金 本年实际单位税金 本年实际销量 成本影响 上年同期单位成本 本年实际单位成本 本年实际销量 品种影响 若上年同期销售数量为0 等于本年实际利润 若本年实际销售量为0 等于上年同期利润的负值 增减变化数据表效果图 步骤01 设置增减变化数据表 我们紧接单位成本表输入增减变化数据表的标题 并设置格式等 如果直接在单元格输入前面带0的数字如 001 则确定后单元格内显示 1 前面的0被丢掉了怎么解 决 181 销售分析CHAPTER 1 2 步骤02 计算利润变化 在V3单元格输入公式 U3 P3 并复制公式到V4 V9 步骤03 计算销量影响 在W3单元格输入公式 IF OR B3 0 G3 0 0 ROUND G3 B3 O3 2 并复制公式 到W4 W9 步骤04 计算售价影响 在X3单元格输入公式 IF OR B3 0 G3 0 0 ROUND Q3 L3 G3 2 并复制公式 到X4 X9 解决的一般办法是先选中所有需要输入前面带0数字的单元格 再在任一被选中的单元格上单击右键 选 设 置单元格格式 在弹出的对话框中单击 数字 标签 再在 分类 框中选 文本 即可 财务管理必会Excel应用100例办公室之 182 步骤05 计算税金影响 在Y3单元格输入公式 IF OR B3 0 G3 0 0 ROUND M3 R3 G3 2 并复制公式 到Y4 Y9 步骤06 计算成本影响 在Z3单元格输入公式 IF OR B3 0 G3 0 0 ROUND N3 S3 G3 2 并复制公式 到Z4 Z9 步骤07 计算品种影响 在AA3单元格输入公式 IF B3 0 U3 IF G3 0 P3 0 并复制公式到AA4 AA9 步骤08 计算总量变化 在V13单元格输入公式 SUM V3 V9 并复制公式到W13 AA13 步骤09 设置零值不显示 选中整个数据表区域 打开Excel选项窗口 选择 高级 选项卡 将 在具有零值 的单元格中显示零 的勾去掉 在输入11位及以上数字 如身份证号码 时怎样才能正常显示 183 销售分析CHAPTER 1 2 步骤10 完成设置 通过上面的设置 数据表中的零值就不会显示了 知识点 OR函数 在其参数组中 任何一个参数逻辑值为 TRUE 即返回 TRUE 任何一个参数的逻辑 值为 FALSE 即返回 FALSE 函数语法 OR logical1 logical2 Logical1 logical2 是 1 到 255 个需要进行测试的条件 测试结果可以为 TRUE 或 FALSE 函数说明 参数必须能计算为逻辑值 如 TRUE 或 FALSE 或者为包含逻辑值的数组或引用 在数字前面加上一个英文状态下的 即能正常显示 反之以科学计数法显示 此时 该单元格格式为文 本形式 其 数值 只能排序 不能用于函数计算 财务管理必会Excel应用100例办公室之 184 如果数组或引用参数中包含文本或空白单元格 则这些值将被忽略 如果指定的区域中 不包含逻辑值 函数 OR 返回错误值 VALUE 可以使用 OR 数组公式来检验数组中是 否包含特定的数值 若要输入数组公式 请按 Ctrl Shift Enter 12 1 4 添加销售利润变化图 销售利润变化图 步骤01 选择数据源 选中W2 AA2和W13 AA13两个区域 在 插入 选项卡中选择 柱形图 里的 簇 状柱形图 步骤02 生成柱形图 经过上一步 就可以得到一个柱形图 如果需要输入的文本在同一列中前面已经输入过表快速录入数据 185 销售分析CHAPTER 1 2 步骤03 进行一些微调 我们可以对柱形图进行一些微调 比如删除图例 添加标题等 步骤04 按正负设置颜色 在柱形图中把正负按不同的颜色区分开比较容易分辨 我们可以双击负值的数据 点 单击鼠标右键 选择 设置数据点格式 在弹出的对话框中进行设置 右击下面的单元格 选 选择列表 选项 则上面输入过的文本以下拉菜单形式出现 用左键选中你需要的 文本 即可将其快速输入到选定的单元格中 财务管理必会Excel应用100例办公室之 186 步骤05 最终结果 经过复制的步骤 我们终于得到了最终的结果 12 2 销售预测分析 预测工作在财务管理中也是很重要的 除了本节要讲的销售预测 对于费用 成 本 利润等都要进行科学地预测 才能为管理者提供决策的依据 如何设置文件下拉窗口的最下面的最近运行的文件名个数 187 销售分析CHAPTER 1 2 销售预测分析效果图 12 2 1 制作步骤 步骤01 创建历史数据表 新建一个工作簿 并在工作簿中重命名其中一个工作表 删除其他的 输入表格的 标题 数据 并设置格式等 步骤02 添加折线图 选中B2 C13单元格 在 插入 选项卡中选择 折线图 里的 带数据标记的 折线图 打开 工具 选 选项 再选 常规 在 最近使用的文件清单 下面的文件个数输入框中改变文件 数目即可 若不在菜单中显示最近使用的文件名 则将 最近使用的文件清单 前的复选框去掉即可 财务管理必会Excel应用100例办公室之 188 步骤03 调整X轴 删除图形中的月份系列 通过 数据选择 中的水平轴数据源编辑 选择B3 B13为 X轴数据源 还可以顺便给X和Y轴加上标题 步骤04 调整坐标轴的刻度 在EXCEL中如何使它象WORD一样的自动定时保存文件 189 销售分析CHAPTER 1 2 选中折线图 在 布局 选项卡中选择 坐标轴 下关于Y轴的相关项目 对Y轴的 刻度进行设置 步骤05 调整坐标上下限 在弹出的对话框中 进行如图中的相关设置 主要是调整Y轴的上下限值 步骤06 设置数据系列格式 选中任意数据点 单击鼠标右键 选择 设置数据系列格式 步骤07 设置点和线的颜色 在弹出的对话框中 对数据系列的点和折线的颜色进行设置 还可以对线型以及线 宽等进行设置 点击 工具 菜单 自动保存 项 设置自动保存文件夹的间隔时间 如果在 工具 菜单下没有 自动保 存 菜单项 那么执行 工具 菜单下 加载宏 选上 自动保存 确定 然后进行设置即可 财务管理必会Excel应用100例办公室之 190 步骤08 完成折线设置 单击 关闭 完成对折线的设置 现在我们已经得到了已经设置好的折线图 步骤09 添加趋势线 鼠标右键单击折线 在弹出的菜单中选择 添加趋势线 巧用Excel中的符号栏 191 销售分析CHAPTER 1 2 步骤10 趋势线设置 在弹出的对话框中 选择 线性 并勾选 显示公式 步骤11 完成趋势线设置 单击 关闭 完成对趋势线的设置 得到新的图形 步骤12 使用TREND函数预测销售量 在B17单元格输入 函数法 在C17单元格输入 预测方法一 使用TREND函数预测12月份销售量 在C18单元格输入公式 TREND C3 C13 B3 B13 12 选择 视图 工具栏 符号栏 然后就可以从中选择相应的符号就可以了 财务管理必会Excel应用100例办公室之 192 步骤13 使用FORECAST函数预测销售量 在C20单元格输入 预测方法二 使用FORECAST函数预测12月份销量 在C21单 元格输入公式 FORECAST 12 C3 C13 B3 B13 步骤14 使用趋势方程法预测销售量 在B23单元格输入 线性法 在C23单元格输入 使用线性趋势方程预测12月份销 量 根据趋势线公式 在C24单元格输入公式 182 B14 5771 6 巧用字符映射表 193 销售分析CHAPTER 1 2 步骤15 最终结果 经过以上步骤 我们就得到了一个预测12月份销售情况的数据表 12 2 2 相关函数 TREND函数 返回一条线性回归拟合线的值 即找到适合已知数组 known y s 和 known x s 的直线 用最小二乘法 并返回指定数组 new x s 在直线上对应的 y 值 选择 开始 程序 附件 系统工具 字符映射表 单击特殊符号按钮 再通过上一 个 下一个按钮调出各类符号 选中后 单击选择按钮 再单击复制按钮 就可以粘贴到了Excel中了 财务管理必会Excel应用100例办公室之 194 函数语法 TREND known y s known x s new x s const Known y s是关系表达式 y mx b 中已知的 y 值集合 如果数组 known y s 在单独一列中 则 known x s 的每一列被视为一个独立的 变量 如果数组 known y s 在单独一行中 则 known x s 的每一行被视为一个独立的变 量 Known x s是关系表达式 y mx b 中已知的可选 x 值集合 数组 known x s 可以包含一组或多组变量 如果仅使用一个变量 那么只要 known x s 和 known y s 具有相同的维数 则它们可以是任何形状的区域 如果用到多个 变量 则 known y s 必须为向量 即必须为一行或一列 如果省略 known x s 则假设该数组为 1 2 3 其大小与 known y s 相同 New x s为需要函数 TREND 返回对应 y 值的新 x 值 New x s 与 known x s 一样 对每个自变量必须包括单独的一列 或一行 因 此 如果 known y s 是单列的 known x s 和 new x s 应该有同样的列数 如果 known y s 是单行的 known x s 和 new x s 应该有同样的行数 如果省略 new x s 将假设它和 known x s 一样 如果 known x s 和 new x s 都省略 将假设它们为数组 1 2 3 大小与 known y s 相同 Const为一逻辑值 用于指定是否将常量 b 强制设为 0 如果 const 为 TRUE 或省略 b 将按正常计算 如果 const 为 FALSE b 将被设为 0 零 m 将被调整以使 y mx 函