excel在产品定价中的应用4

Excel在产品定价中的应用 董利康 范永胜 （绵阳师范学院 数学与计算机科学学院 621000 绵阳） 摘要：在市场经济条件下，产品的信息日趋复杂，而且变换很快。公司管理越来越依赖计算机的 帮助，注重管理的自动化。在公司管理中如何给产品定价决策已成为企业普遍关注的热门课题。定 价决策中往往涉及到大量复杂的计算，很多财务工作者对于这部分计算都觉得比较困难。本文将讨 论把 EXCEL 的计算统计功能与这部分内容结合起来解决这一问题。 【关键词】 EXCEL；固定成本；产品定价 The Excel application in fixing the product price Dong-Likang Fan-Yongsheng (Mianyang Normal University College of Mathematics and Computer Science, Mianyang 621000) Abstract: Under the circumstance of the market oriented economy, the information of the product is not only complex day by day but also rapidly changeable. Meanwhile, the company more and more relies on the computer to manage and emphasizes the automation of the management. How to fix the product price in the companys management has became the heated issue to debate in public. The policy of fixing the price involves enormous complicated calculations. So, it is difficult to handle this kind of calculation even for the people who are specialized in the finance. However, this article aims to discuss how to deal with this problem by combining the functional statistics of Excel with this part. Keywords: Excel ；fixed costs； product pricing 随着社会经济的发展，小企业、小公司不断成立。为了在激烈的市场竞争中生存下来，实现自己 的营销战略和目标，小企业必须根据产品特点、市场需求及竞争情况，采取各种灵活多变的产品定 价策略，确定合理的产品销售价格，促使和扩大产品销售，提高企业的整体效益。而在现实中确定 最优销售价格的计算，具有复杂性、处理量大等特点，特别是对人员数值计算能力的要求比较高。 对于小企业来说，聘请高级财务顾问也不现实。如果能够让公司的经营管理者正确、灵活地使用 Excel 所带的函数进行计算，可使原本复杂的计算变得非常简单，并能大大减轻日常工作中有关指 标计算的工作量。 一、定价的基本原理 现在使用最多的定价原理是最优价格原理。其基本的根据是：公司效益是由商品销售量、价格 以及成本决定的，当成本一定时，价格越高，销售量越大，公司效益就越好。同时，销售量是要受 价格约束的，当价格越高时，商品销售量就越低；反之，就越大；从而商品销售量是随价格动态变 化的。若以 P，H 分别代表销售价格和销售量，则销售量与销售价格关系式为： H=F（P） （1） 其中 F（P）表示销售量 P 为变量的函数；销售效益 L 是由商品成本 Q、销售价格和销售量决定， 关系式为： L=(P-Q)*H （2） 把式（1）带入（2）式得： L=(P-Q)* F（P） （3） 从（3）式中可以得出销售效益只是关于价格的一元函数，可由函数求最值的方式求得最优价格 （注：在实际问题中，最优价格总是存在的，所以函数必然有最值），从而得到最佳销售效益。 二、价格的确立 从上面的原理分析可知，确定最优价格关键在销售成本和销售关系式，当销售成本及销售量确 定以后，可根据效益与价格关系式求解函数最值得到最优价格，从而得到商品最优售价以及最佳效 益。 （一）、商品销售成本统计 商品成本主要包括两个方面：（1）固定资产的投入，包括厂房、机器、办公设备的折旧，维 修费用。 （2）流动资产的投入，包括有原材料的购入，工人工资福利，水电费等。市场波动（如： 物价变化）算入流动盈亏，可以计入流动资产部分。公司的成本计算并不需要细致到每一时刻，一 般是一个销售周期计算一次，如一个星期，一个月，或一个季度等。 A B C D E F G H I J 1 固定资 产的折 旧（万 元） 维修 费用 （万 元） 原材 料 （万 元） 员工工 资及福 利（万 元） 水电 费 （万 元） 市 场 波 动 产品 数量 （万 件） 总计 成本 （万 元） 单位成 本（元） 2 第 一 周 0.5 0.01 100 2.4 0.5 1 0.19 83 103. 41 521.48 26021 3 第 二 周 0.5 0.02 120 2.8 0.58 1. 01 0.23 88 125. 139 524.03 26633 4 第 三 周 0.5 0.01 110 2.6 0.56 1 0.21 85 113. 67 520.22 8833 5 第 四 周 0.5 0.02 115 2.7 0.57 1 0.22 85 118. 79 519.86 8709 6 合 计 2 0.06 445 10.5 2.21 0.88 41 461. 009 521.44 44067 表一：某公司 9 月份的资产变动统计表 在以上表格中，我们使用了 Excel 的以下功能： 总计成本=市场波动*（固定资产折旧费+维修费+原料费+员工工资及福利+水电费）；计算第 一周时，可直接在 excel 表格 I2【注：I2 表示第 I 列、第 2 行，下同】中输入“=SUM(B2:F2)*G2”， 直接运算可以得到结果；在对第二、三、四周处理的时候运用公式复制直接进行。在以上的计算中 利用了 Excel 方便的公式输入功能和复制功能。 合计各项数据的总体情况，我们利用了 Excel 的求和函数，在 B6 中输入“=SUM(B2:B5)”， 对于 C6 到 I6 直接进行公式复制。 这月的基本销售价格（平均价格）=总价格总数量，我们利用 Excel 的直接输入公式完成， 在 J6 中输入 “=I6/(H6)”， 然后运算即得。而 J2 到 J5 的周基本销售价格（平均价格）亦可利用公 式复制完成。 于是，由上表可知，这月该公司生产的商品基本价格为 521.4444 元。这样我们就确定了商品 的基本销售成本。 （二） 、销售关系式的确立 销售关系的确立关键是看销售数量随销售价格变动的关系，我们可以根据销售表中的销售数量 与销售价格的对应数据，利用Excel能方便绘制图表的功能，对其做散点图，观察变化趋势，从而确 定基本的销售关系函数。 若销售关系函数是一元线性函数，则可根据表中已知数据，用excel做一元线性回归分析， 从而确定销售关系式。销售关系式的确立还不能说明这种相关关系是否可靠, 为了说明这种相关关 系的可靠性,还必须进行回归方程的显著性检验。一元线性回归方程的显著性检验一般有三种方式： F检验法、t检验法、相关系数检验法，其中t 检验法在回归模型的检验中极具代表性。 统计量 t 服从 t(n- 2)分布，根据一组样本量计算出 t 值, 再根据所给定的显著性水平 和 自由度 n- 2, 查 t 分布表, 找到相应的临界值 。若|t| ，|t|表示 t 绝1/2()tn1/2()tn 对值，表明 t 在统计上是显著的, 即总体的两个变量间存在线性关系, 这种关系是可靠的；否则就 认为两个变量间不存在线性关系。 2.若销售关系函数为一元非线性函数则需对其做线性化处理，化为一元线性方程形式，然后再 进行一元线性回归与检验。 A B C 1 月份 价格（元） 销售量（件） 2 一月 680 5368 3 二月 635 7602 4 三月 620 8732 5 四月 625 8800 6 五月 640 7823 7 六月 610 9600 8 七月 660 6895 9 八月 645 7426 表二： 该公司前八月的销售表 图 1 销售量和价格的散点图 、观察散点图可以看出, 本数据具有线性分布趋势,即销售量与销售价格满足线性关系，可表 示为H=a*P+b，其中a、b均为常数；于是，可利用excel对函数方程式进行回归分析求解。 、Excel回归分析： Excel 2003 进行回归分析的具体步骤如下： 、首先新建一个 Excel 文档，并输入数据同 “表二” ； 、 打开 “工具”下拉菜单, 用鼠标双击“数据分析”选项( 如果没有该选项, 需选择“加 载宏”选项分析工具) , 弹出“数据分析”对话框。然后 , 选择“回归”, 确定, 弹出选项 表: 图 2 回归对话框图 进行如下选择: X、Y 值输入区域分别输入$B$2:$B$9、$C$2:$C$9, 置信度( 95%) , 新工 作表组, 残差, 线性拟合图 (图 2)； 然后点击“确定”, 取得回归结果将会形成四个表： 回归统计 Multiple R 0.984558171 R Square 0.969354793 Adjusted R Square 0.964247258 标准误差 247.7640748 观测值 8 表三-1:回归分析表之回归统计 方差分析 df SS MS F Significance F 回归分析 1 11650595 11650595 189.7891786 9.09898E-06 残差 6 368322.22 61387.04 总计 7 12018918 表三-2：回归分析表之方差分析 Coeffi cients 标准 误差 t Stat P- value Lower 95% Upper 95% 下限 95.0% 上限 95.0% Intercept 44296.61417 2652 .057 2 16.7 0274 2.939 77E- 06 37807 .2638 7 50785. 964 37807 .2639 50785 .9645 X Variable 1 - 57.111 81102 4.14 5626 9 - 13.7 764 9.098 98E- 06 - 67.25 57944 8 - 46.967 83 - 67.25 5794 - 46.96 7828 表三-3:回归分析表之截距系数分析 误差输出 观测值 预测 Y 残差 1 5460.582677 -92.58268 2 8030.614173 -428.6142 3 8887.291339 -155.2913 4 8601.732283 198.26772 5 7745.055118 77.944882 6 9458.409449 141.59055 7 6602.818898 292.1811 8 7459.496063 -33.49606 表三-4:回归分析表之误差分析 读取回归分析结果为，斜率：a =-57.1118，截距:b =44296.6142，t值:t=-13.7764。 、回归方程的确立及显著性检验： 由上可知，回归方程为：H=-57.1118*P+44296.6142。当显著性水平=0.05，n=8时，查t分布表 可知 =2.4469，由于|t|=13.77642.4469，因此，在显著性水平 0.05下回归方程是显著10.5/2(8)t 的，即销售数量和销售价格两者之间存在线性关系，并且这种线性关系是可靠地。 （三） 、最优价格的确定 销售效益与销售价格的关系式为：L=(P-521.4444)* （-57.1118*P+44296.6142），当L=（- 57.1118*P+44296.6142）-57.1118(P-521.4444)=0时，即P=648.5283( 元)，可获得最佳销售效益 922374.39元。 三、总结 本文探讨了用Excel的基本功能来实现公司产品的最优定价的基本方法，从上述的销售价格确定 的研究可以看出，EXCEL具备灵活、简便而功能强大的数据分析和挖掘功能 ,而基于EXCEL数据库的维 护和更新也非常简单,财务人员