项目十八 制作产品销售明细表

制作产品销售明细表Office办公软件实用案例教程项目十八预测分析的方法主要有两种，即定量预测法和定性预测法。定量预测法是在掌握与预测与对象有关的各种要素的定量资料的基础上，运用现代数学方法进行数据处理，据以建立能够反映有关变量之间规律联系的各类预测方法体系，它可分为趋势外推分析法和因果分析法；定性预测法是指由有关方面的专业人员或专家根据自己的经验和知识，结合预测对象的特点进行分析，对事物的末来状况和发展趋势作出推测的预测方法，图18.1就是用Excel软件制作的产品销售明细表。项目简介知识点导入1．单元格区域命名：选中单元格区域，在名称框输入命名；2．数据分析：单击【数据】→【分析】组中的

按钮，打开“数据分析”对话框，在列框中选择“回归”选项，单击“确定”按钮。任务实施样张图18.1产品销售明细表重点步骤1．启动Excel2010，新建空白工作。2．将新建的文档保存在桌面上，文件名为“产品销售明细表.XLSX”。1．新建工作表新建工作表，并依次命名为个工作表的名称为“明细”、“销售预测”、“利润预测”、“成本预测”，如图18.2所示。

图18.2新建四个工作表重点步骤2．录入明细工作表（1）切换到“明细”工作表，输入标题、项目、基本数据，效果如图18.3所示。

图18.3输入基本数据重点步骤（2）计算销售额公式为：“销售额=销售量*售价”（3）计算销售成本公式为：“销售成本=销售额*0.8”（4）计算实现利润公式为：“实现利润=销售额-销售成本”，如图18.4所示。

图18.4计算销售额、销售成本、实现利润重点步骤（5）命名单元格区域①选中A3:A14单元格区域，在名称框输入月份如图18.5所示；②按相同的方法为其他单元格区域命名。3．预测销售数据（1）切换到“销售预测”工作表，在A1:B14单元格内创建框架数据，如图18.6所示；

图18.6框架图18.5对A3:A14单元格区重点步骤（2）月份引用选中A3:A14单元格区域，在编辑栏中输入“=月分”，按【Ctrl+Enter】组合键引用“明细”工作表中的数据。（3）预测“销售额”①选中B3:B14单元格区域，在编辑栏中输入“=销售额”，按【Ctrl+Enter】组合键引用“明细”工作表中的数据；②单击【数据】→【分析】组中的按钮，打开“数据分析”对话框，在列框中选择“回归”选项，单击“确定”按钮，如图18.7所示；

图18.7“回归”对话框重点步骤③在Y值输入区域和X值输入区域分别“$B$2:$B$14”和“$A$2:$A$14”,单击选中“输出区域”，将输出区域指定为“$D$2”,然后分别单击选中“标志”、“残差”、“线性拟合图”复选框，最后单击“确定”按钮，参数如图18.8所示；

图18.8参数设置重点步骤④此时将显示回归数据分析工具根据提供的单元格区域的数据，得到的预测结果，其中还配以散点图来直观地显示数据趋势。通过图表以及工作中的“SUMMARYOUTPUT”栏下的数据便可查看预测的销售数据及趋势，如图18.9所示。

图18.9预测结果重要步骤4．预测利润数据

（1）建立表格框架数据切换到“利润预测”工作表，在A1:C8单元格中建立表格框架数据，然后在C8单元格中输入目标利润数据。（2）计算销售量选中B3单元格，在编辑栏中输入“=SUM(销售量)”后按【回车】键，表示计算名称为“销售量”的单元格所有数据之和，如图18.10所示。

图18.10所示计算实际销售量重要步骤（3）计算售价选中B4单元格，在编辑栏中输入“=AVERAGE(售价)”后按【回车】键，表示计算名称为“售价”的单元格所有数据的平均值，如图18.11所示。

图18.11计算实际售价重要步骤（4）计算变动范围选中B5单元格，在编辑栏中输入“=AVERAGE(MAX(售价)-B4,B4-MIN(售价))”后按【回车】键，表示计算名称为“售价”的单元格所有数据的平均值，如图18.12所示。

图18.12计算变动范围重要步骤5）计算固定成本选中B6单元格，在编辑栏中输入“=SUM(销售成本)”后按【回车】键，如图18.13所示。

图18.13计算固定成本重要步骤（6）计算目前利润选中B7单元格，在编辑栏中输入“=SUM(实现利润)”后按【回车】键，如图18.14所示。

图18.14计算目前利润重要步骤（7）计算预测销售量选中C3单元格，在编辑栏中输入“=(C8+B6)/(B4-B5)”后按【回车】键，如图18.15所示。

图18.15预测销售量重要步骤（8）预测售价选中C4单元格，在编辑栏中输入“=(C8+B6)/B3”后按【回车】键，如图18.16所示。

图18.16预测售价重要步骤（9）预测固定成本选中C6单元格，在编辑栏中输入“=B3*（B4-B5）-C8”后按【回车】键，如图18.17所示；

图18.17预测固定成本重要步骤5.预测成本数据（1）创建框架数据切换到“成本预测”工作表，创建框架数据,如图18.18所示。

图18.18预测成本数据框架重要步骤（2）引用数据引用月份、销售量和销售成本数据，如图18.19所示。

图18.19引用的数据重要步骤（3）预测成本单击【数据】→【分析】组中的

按钮，打开“数据分析”对话框，在列框中选择“回归”选项，单击“确定”按钮，将Y值输入区域和X值输入区域分别“$C$2:$C$14”和“$B$2:$B$14”,单击选中“输出区域”单选项，将输出区域指定为“$E$2”，最后单击【确定】按钮，得到预测结果，如图18.20所示。

图18.20预测成本重要步骤1.

明细工作表选择A1:F1单元格区域，选择【开始】→【对齐方式】→【合并后居中】。2.设置标题文字单击标题，选择【开始】→【字体】，字体选择【宋体】，字号选择【20】，字形选择【加粗】，颜色选择【蓝色】。3.

设置单元格底纹选中A2:K2,单击鼠标右键→【设置单元格格式】→【填充】命令，弹出如图18.21所示的【填充】对话框中选择黄色，如图18.22所示。图18.21“填充”对话框重要步骤图18.22对明细工作表设置4.设置其他工作表按照“明细”工作表的设置方法对另外三个工作表进行设置。拓展项目制作投资公方案表样张图18.23投资方案表重点步骤1.新建并保存“投资方案表.xlsx”,输入并美化信贷方案的框架数据。2.输入相关内容，如图18.24所示。

图18.24基本数据重点步骤3．选中E3:E6单元格区域，在编辑栏中输入“=PMT(D3,C3,-B3)，按【Ctrl+Enter】组合键计算每年还款额；4．选中F3:F6单元格区域，在编辑栏中输入“=PMT(D3/4,C3*4,-B3)，按【Ctrl+Enter】组合键计算每季度还款额；5.选中G3:G6单元格区域，在编辑栏中输入“=PMT(D3/12,C3*12,-B3)，按【Ctrl+Enter】组合键计算每月还款额，如图18.25所示；

图18.25还款额课后练习1．制作成本预测趋势图，效果如图18.26所示。

图18.26成本预测趋势图