Excel数据分析案例：对多种产品进行批量定价分析
数据分析与量化
上一篇文章是对一种产品进行定价，现在进行扩展，让一家公司能够轻松地对成千上百种产品定价。每种产品需要的信息仅是单位成本、针对最低可能价格的估计需求、针对中间价格的估计需求以及针对最高可能价格的估计需求。
在完成上篇文章的需求曲线的拟合之后，可以使用规划求解来为每一种产品设置价格以最大化来自所有产品的总利润。步骤如下：
1、使用HLOOKUP函数切换单元格A11中的条目值来在E14:E16中放置每一种产品的需求。规划求解模型会为每种产品选中正好穿过其需求点的常量a、价格系数b以及价格平方的系数c。
2、使用规划求解表通过将单元格A11的值在1、2、……、n的范围内进行修改来循环处理每一种产品，其中n=产品的种类数量。
4、将公式=HLOOKUP($A$11,lookup,C14)从E14复制到E15:E16，以获得与A11中的产品索引相关的每种产品的需求。
5、将公式=$E$3+$E$4*D14+$E$5*(D14^2) 从F14复制到F15:F16，以获得与基于E3:E5中值得二次方需求曲线来计算预测需求。
6、将公式=(E14-F14)^2从G14复制到G15:G16，以计算每个价格需求预测中的方差。每个价格得估计误差就是实际需求-从二次方需求曲线得到的估计需求。
7、在单元格G12中使用公式=SUM(G14:G16)计算估计方差的合计值。
8、如果通过更改E3:E5来最小化G12，规划求解会得出让方差合计等于0的a、b和c的值。最小化方差合计值而不是最小化误差合计值的原因是，如果不对误差进行平方，那么正数误差的合计值会与负数误差的合计值互相抵消。
方差合计是包含0-26位小数的值，且二次方需求曲线是-73.625+195price+87.5price^2。
在得到规划求解模型之后，就能使用规划求解表来循环遍历一个或两个输入项的值，并跟踪想要的输出值。使用SolverTable，选择One-way Table，并设置如下：
规划求解首先会在输入单元格A11中放置一个1并运行规划求解来跟踪输出单元格（a、b、c和方差合计SSE）。然后规划求解会在A11中放置一个2，最后会在A11中放置一个3，最终计算结果如下：
现在可以设置一个判定每种产品利润最大化价格的规划求解模型了，步骤如下：
1、在区域F5:F7单元格中输入每种产品的实行价格。
2、基于F5:F7中的价格，将公式=B5+C5*F5+F5^2*D5从G5复制到G6:G7来计算每种产品的需求。
3、在H5:H7中输入每种产品的单位成本，将公式=(F5-H5)*G5从I5复制到I6:I7计算每种产品的利润。
4、用公式=SUM(I5:I7)在I2中计算所有产品的利润。
最终会发现产品1的售价应该是1.39元，而产品2和产品3的售价应该是1.37元，实现利润最大化。
我会在知识星球里面分享300个excel数据分析案例，目录：
数据分析与量化
商业分析案例、商业分析课程与咨询
