|
Excel制作--财务销售分析表
第12章 Excel 销售分析 第1节、销售预测相关函数 TREND函数
返回一条线性回归拟合线的值。即找到适合已知数组known_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's在直线上对应的y值。
函数语法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。
函数说明*可以使用TREND函数计算同一变量的不同乘方的回归值来拟合多项式曲线。例如，假设A列包含y值，B列含有x值。可以在C列中输入x^2，在D列中输入x^3，等等，然后根据A列，对B列到D列进行回归计算。
*对于返回结果为数组的公式，必须以数组公式的形式输入。
*当为参数（如known_x's）输入数组常量时，应当使用逗号分隔同一行中的数据，用分号分隔不同行中的数据。
FORECAST函数
根据已有的数值计算或预测未来值。此预测值为基于给定的x值推导出的y值。已知的数值为已有的x值和y值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。
函数语法FORECAST(x,known_y's,known_x's)X为需要进行预测的数据点。
Known_y's为因变量数组或数据区域。
Known_x's为自变量数组或数据区域。
函数说明*如果x为非数值型，函数FORECAST返回错误值#VALUE!。
*如果known_y's和known_x's为空或含有不同个数的数据点，函数FORECAST返回错误值#N/A。
*如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0!。 第2节、销售预测数据表制作步骤 步骤01创建历史数据表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。 步骤02添加折线图
选中B2:C13单元格，在“插入”选项卡中选择“折线图”里的“带数据标记的折线图”。 步骤03调整X轴
删除图形中的月份系列，通过“数据选择”中的水平轴数据源编辑，选择B3:B13为X轴数据源。还可以顺便给X和Y轴加上标题。 步骤04调整坐标轴的刻度 选中折线图，在“布局”选项卡中选择“坐标轴”下关于Y轴的相关项目，对Y轴的刻度进行设置。 步骤05调整坐标
上下限在弹出的对话框中，进行如图中的相关设置，主要是调整Y轴的上下限值。 步骤06设置数据系列格式
选中任意数据点，单击鼠标右键，选择“设置数据系列格式”。 步骤07设置点和线的颜色
在弹出的对话框中，对数据系列的点和折线的颜色进行设置。还可以对线型以及线宽等进行设置。 步骤08完成折线设置
单击“关闭”，完成对折线的设置，现在我们已经得到了已经设置好的折线图。 步骤08完成折线设置
单击“关闭”，完成对折线的设置，现在我们已经得到了已经设置好的折线图。 步骤09添加趋势线
鼠标右键单击折线，在弹出的菜单中选择“添加趋势线”。 步骤10趋势线设置
在弹出的对话框中，选择“线性”，并勾选“显示公式”。 步骤11完成趋势线设置
单击“关闭”完成对趋势线的设置，得到新的图形。 步骤12使用TREND函数预测销售量
在B17单元格输入“函数法”。
在C17单元格输入“预测方法一：使用TREND函数预测12月份销售量”。
在C18单元格输入公式：=TREND(C3:C13,B3:B13,12) 第3节、销售利润数据表中添加销售利润变化图 步骤01选择数据源
选中W2:AA2和W13:AA13两个区域，在“插入”选项卡中选择“柱形图”里的“簇状柱形图”。 步骤02生成柱形图
经过上一步，就可以得到一个柱形图。 步骤03进行一些微调
我们可以对柱形图进行一些微调，比如删除图例、添加标题等。 步骤04按正负设置颜色
在柱形图中把正负按不同的颜色区分开比较容易分辨，我们可以双击负值的数据点，单击鼠标右键，选择“设置数据点格式”，在弹出的对话框中进行设置。 步骤05最终结果
经过复制的步骤，我们终于得到了最终的结果。 第4节、创建增减变化数据表 接下来我们要制作单位成本增减变化数据表。这个部分将要使用到的公式： 利润变化＝本年实际总利润－上年同期总利润
销售影响＝（本年实际销量－上年同期销量）×上年同期单位利润
售价影响＝（本年实际售价－上年同期售价）×本年实际销量
税金影响＝（上年同期单位税金－本年实际单位税金）×本年实际销量
成本影响＝（上年同期单位成本－本年实际单位成本）×本年实际销量
品种影响，若上年同期销售数量为0，等于本年实际利润；若本年实际销售量为0，等于上年同期利润的负值。
增减变化数据表效果图
步骤01 设置增减变化数据表
我们紧接单位成本表输入增减变化数据表的标题，并设置格式等。 步骤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。 步骤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选项窗口，选择“高级”选项卡，将“在具有零值的单元格中显示零”的勾去掉。 步骤10 完成设置
通过上面的设置，数据表中的零值就不会显示了。 知识点： OR函数
在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。 函数语法
OR(logical1,logical2,...)
Logical1,logical2,...是1到255个需要进行测试的条件，测试结果可以为TRUE或FALSE。 函数说明
参数必须能计算为逻辑值，如TRUE或FALSE，或者为包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的区域中不包含逻辑值，函数OR返回错误值#VALUE!。可以使用OR数组公式来检验数组中是否包含特定的数值。若要输入数组公式，请按Ctrl+Shift+Enter。 第5节、创建单位成本表 有了销售数据表，接下来可以制作各种产品上年同期和本年实际的单位成本表。
单位成本表效果图
步骤01 设置单位成本表
我们紧接销售数据表输入单位成本表的标题，并设置格式等。 步骤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区域。 步骤05 计算本年总利润
在U3单元格输入公式：=ROUND(T3*G3,2)，复制公式到U4:U9区域。 步骤06 计算上年单位成本各项累计数
在L13单元格输入公式：=SUM(L3:L9)，复制公式到M13:P13区域。 步骤07 计算本年单位成本各项累计数
在Q13单元格输入公式：=SUM(Q3:Q9)，复制公式到R13:U13区域。 第6节、销售分析表-创建销售数据表 作为企业经营的重要环节，销售的重要性不言而喻，销售利润是企业追求的目标。对销售利润及其影响因素的分析有着非常重要的作用.同样，对于销售情况的预测也是一项十分重要的工作，本章也会在这方面进行讲解。
销售数据表效果图
要创建销售数据表，计算销售利润时要使用公式：销售利润＝销售收入－销售成本－销售税金 步骤01 新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题，并设置格式等。 步骤02 输入数据
在数据区域输入每个产品的相关数据。 步骤03 计算上年产品销售利润
在F3单元格输入公式：=C3-D3-E3，并复制公式至F4:F9区域。 步骤04 计算本年产品销售利润
在K3单元格输入公式：=H3-I3-J3，并复制公式至K4:K9区域。 步骤05 计算上年累计数
在A13单元格输入“合计”；在B13单元格输入公式：=SUM(B3:B9)，并复制公式至C13:F13区域。 步骤06 计算本年累计数
在G13单元格输入公式：=SUM(G3:G9)，并复制公式至H13:K13区域。
|
