因素分析法应用-两年的销售利润分析

因素分析法，比如上年数量a，售价b

本年数量a1，售价b1.

因素分析是先算数量差异：

a1*b-ab

然后是价格影响数：

a1*b1-a1*b

两个因素加起来

=a1*b1-a1*b+a1*b-ab=a1*b1-ab

因素分析法没有那么神秘。就是依次替换其中的变量。

而且，还要分销售收入和成本，一般人不会有这个耐心和清晰的思路的。

贸易公司这些数据都是有现成的。如果我做贸易公司，必须按照这个讨论分析的。

若想知道做活动的特价到底影响多少。其实做活动之前就已经算好了。一方面是价格让利多少，另一方面是供货方可以支持多少。对自己的影响其实不大的。薄利多销，有可能单个利润少了，但因为销量多了，反而当月的利润是上升的。

我是模拟了10月和11月有降价促销。

要看影响结果如何，因素分析法已经按产品拆分出来具体金额了。

产品	月份	销售数量	销售单价	收入币种	汇率	原币收入	本币收入	成本价	成本币种	汇率	原币成本	本币成本
A	2019/1/31	152	100	CNY	1	15,200.00	15,200.00	82	CNY	1	12,464.00	12,464.00
B	2019/1/31	83	12.5	USD	6.8632	1,037.50	7,120.57	10.5	USD	6.8632	871.50	5,981.28
C	2019/1/31	28	8.5	USD	6.8632	238.00	1,633.44	7.5	USD	6.8632	210.00	1,441.27
D	2019/1/31	182	17	USD	6.8632	3,094.00	21,234.74	15	USD	6.8632	2,730.00	18,736.54
A	2019/2/28	34	100	CNY	1	3,400.00	3,400.00	82	CNY	1	2,788.00	2,788.00
B	2019/2/28	56	12.5	USD	6.7025	700.00	4,691.75	10.5	USD	6.7025	588.00	3,941.07
C	2019/2/28	42	8.5	USD	6.7025	357.00	2,392.79	7.5	USD	6.7025	315.00	2,111.29
D	2019/2/28	180	17	USD	6.7025	3,060.00	20,509.65	15	USD	6.7025	2,700.00	18,096.75
A	2019/3/31	49	100	CNY	1	4,900.00	4,900.00	82	CNY	1	4,018.00	4,018.00
B	2019/3/31	121	12.5	USD	6.6901	1,512.50	10,118.78	10.5	USD	6.6901	1,270.50	8,499.77
C	2019/3/31	41	8.5	USD	6.6901	348.50	2,331.50	7.5	USD	6.6901	307.50	2,057.21
D	2019/3/31	141	17	USD	6.6901	2,397.00	16,036.17	15	USD	6.6901	2,115.00	14,149.56
A	2019/4/30	85	100	CNY	1	8,500.00	8,500.00	82	CNY	1	6,970.00	6,970.00
B	2019/4/30	69	12.5	USD	6.7335	862.50	5,807.64	10.5	USD	6.7335	724.50	4,878.42
C	2019/4/30	99	8.5	USD	6.7335	841.50	5,666.24	7.5	USD	6.7335	742.50	4,999.62
D	2019/4/30	106	17	USD	6.7335	1,802.00	12,133.77	15	USD	6.7335	1,590.00	10,706.27
A	2019/5/31	166	100	CNY	1	16,600.00	16,600.00	82	CNY	1	13,612.00	13,612.00
B	2019/5/31	49	12.5	USD	6.7286	612.50	4,121.27	10.5	USD	6.7286	514.50	3,461.86
C	2019/5/31	76	8.5	USD	6.7286	646.00	4,346.68	7.5	USD	6.7286	570.00	3,835.30
D	2019/5/31	69	17	USD	6.7286	1,173.00	7,892.65	15	USD	6.7286	1,035.00	6,964.10
A	2019/6/30	28	100	CNY	1	2,800.00	2,800.00	82	CNY	1	2,296.00	2,296.00
B	2019/6/30	191	12.5	USD	6.8992	2,387.50	16,471.84	10.5	USD	6.8992	2,005.50	13,836.35
C	2019/6/30	87	8.5	USD	6.8992	739.50	5,101.96	7.5	USD	6.8992	652.50	4,501.73
D	2019/6/30	162	17	USD	6.8992	2,754.00	19,000.40	15	USD	6.8992	2,430.00	16,765.06
A	2019/7/31	155	100	CNY	1	15,500.00	15,500.00	82	CNY	1	12,710.00	12,710.00
B	2019/7/31	30	12.5	USD	6.8747	375.00	2,578.01	10.5	USD	6.8747	315.00	2,165.53
C	2019/7/31	129	8.5	USD	6.8747	1,096.50	7,538.11	7.5	USD	6.8747	967.50	6,651.27
D	2019/7/31	146	17	USD	6.8747	2,482.00	17,063.01	15	USD	6.8747	2,190.00	15,055.59
A	2019/8/31	16	100	CNY	1	1,600.00	1,600.00	82	CNY	1	1,312.00	1,312.00
B	2019/8/31	188	12.5	USD	6.8841	2,350.00	16,177.64	10.5	USD	6.8841	1,974.00	13,589.21
C	2019/8/31	85	8.5	USD	6.8841	722.50	4,973.76	7.5	USD	6.8841	637.50	4,388.61
D	2019/8/31	86	17	USD	6.8841	1,462.00	10,064.55	15	USD	6.8841	1,290.00	8,880.49
A	2019/9/30	131	100	CNY	1	13,100.00	13,100.00	82	CNY	1	10,742.00	10,742.00
B	2019/9/30	64	12.5	USD	7.0879	800.00	5,670.32	10.5	USD	7.0879	672.00	4,763.07
C	2019/9/30	67	8.5	USD	7.0879	569.50	4,036.56	7.5	USD	7.0879	502.50	3,561.67
D	2019/9/30	126	17	USD	7.0879	2,142.00	15,182.28	15	USD	7.0879	1,890.00	13,396.13
A	2019/10/31	111	90	CNY	1	9,990.00	9,990.00	82	CNY	1	9,102.00	9,102.00
B	2019/10/31	174	11	USD	7.0729	1,914.00	13,537.53	10.5	USD	7.0729	1,827.00	12,922.19
C	2019/10/31	54	7.9	USD	7.0729	426.60	3,017.30	7.5	USD	7.0729	405.00	2,864.52
D	2019/10/31	178	16	USD	7.0729	2,848.00	20,143.62	15	USD	7.0729	2,670.00	18,884.64
A	2019/11/30	157	90	CNY	1	14,130.00	14,130.00	82	CNY	1	12,874.00	12,874.00
B	2019/11/30	86	11	USD	7.0533	946.00	6,672.42	10.5	USD	7.0533	903.00	6,369.13
C	2019/11/30	133	7.9	USD	7.0533	1,050.70	7,410.90	7.5	USD	7.0533	997.50	7,035.67
D	2019/11/30	149	16	USD	7.0533	2,384.00	16,815.07	15	USD	7.0533	2,235.00	15,764.13
A	2019/12/31	166
B	2019/12/31	185
C	2019/12/31	195
D	2019/12/31	195

上年数据

产品	上年同期	上年同期售价	上年同期币种	上年同期汇率	上年同期销量	上年同期成本价	成本币种	上年同期汇率
A	2018/1/31	100	CNY	1	34	85	CNY	1
B	2018/1/31	12.5	USD	6.5342	59	11	USD	6.5342
C	2018/1/31	8.5	USD	6.5342	61	7.5	USD	6.5342
D	2018/1/31	17	USD	6.5342	70	15	USD	6.5342
A	2018/2/28	100	CNY	1	15	82	CNY	1
B	2018/2/28	12.5	USD	6.3339	8	11	USD	6.3339
C	2018/2/28	8.5	USD	6.3339	13	7.5	USD	6.3339
D	2018/2/28	17	USD	6.3339	20	15	USD	6.3339
A	2018/3/31	100	CNY	1	19	85	CNY	1
B	2018/3/31	12.5	USD	6.3294	68	11	USD	6.3294
C	2018/3/31	8.5	USD	6.3294	140	7.5	USD	6.3294
D	2018/3/31	17	USD	6.3294	58	15	USD	6.3294
A	2018/4/30	100	CNY	1	21	85	CNY	1
B	2018/4/30	12.5	USD	6.2881	41	11	USD	6.2881
C	2018/4/30	8.5	USD	6.2881	104	7.5	USD	6.2881
D	2018/4/30	17	USD	6.2881	136	15	USD	6.2881
A	2018/5/31	100	CNY	1	160	85	CNY	1
B	2018/5/31	12.5	USD	6.3393	179	11	USD	6.3393
C	2018/5/31	8.5	USD	6.3393	43	7.5	USD	6.3393
D	2018/5/31	17	USD	6.3393	86	15	USD	6.3393
A	2018/6/30	100	CNY	1	136	85	CNY	1
B	2018/6/30	12.5	USD	6.4144	63	11	USD	6.4144
C	2018/6/30	8.5	USD	6.4144	120	7.5	USD	6.4144
D	2018/6/30	17	USD	6.4144	43	15	USD	6.4144
A	2018/7/31	100	CNY	1	46	85	CNY	1
B	2018/7/31	12.5	USD	6.6166	153	10.5	USD	6.6166
C	2018/7/31	8.5	USD	6.6166	13	7.5	USD	6.6166
D	2018/7/31	17	USD	6.6166	41	15	USD	6.6166
A	2018/8/31	100	CNY	1	165	85	CNY	1
B	2018/8/31	12.5	USD	6.8165	150	10.5	USD	6.8165
C	2018/8/31	8.5	USD	6.8165	87	7.5	USD	6.8165
D	2018/8/31	17	USD	6.8165	101	15	USD	6.8165
A	2018/9/30	100	CNY	1	178	82	CNY	1
B	2018/9/30	12.5	USD	6.8246	15	10.5	USD	6.8246
C	2018/9/30	8.5	USD	6.8246	46	7.5	USD	6.8246
D	2018/9/30	17	USD	6.8246	157	15	USD	6.8246
A	2018/10/31	100	CNY	1	136	82	CNY	1
B	2018/10/31	12.5	USD	6.8792	33	10.5	USD	6.8792
C	2018/10/31	8.5	USD	6.8792	157	7.5	USD	6.8792
D	2018/10/31	17	USD	6.8792	46	15	USD	6.8792
A	2018/11/30	100	CNY	1	167	82	CNY	1
B	2018/11/30	12.5	USD	6.9646	9	10.5	USD	6.9646
C	2018/11/30	8.5	USD	6.9646	132	7.5	USD	6.9646
D	2018/11/30	17	USD	6.9646	15	15	USD	6.9646
A	2018/12/31	100	CNY	1	105	82	CNY	1
B	2018/12/31	12.5	USD	6.9357	126	10.5	USD	6.9357
C	2018/12/31	8.5	USD	6.9357	140	7.5	USD	6.9357
D	2018/12/31	17	USD	6.9357	74	15	USD	6.9357

因素分析法结果：

产品	月份	销量影响收入	售价影响收入	销量影响成本	采购价格影响成本	综合影响
A	2019/1/31	11,800.00	0.00	(10,030.00)	456.00	2,226.00
B	2019/1/31	1,960.26	341.34	(1,725.03)	(15.56)	561.01
C	2019/1/31	(1,832.84)	78.30	1,617.21	(69.09)	(206.42)
D	2019/1/31	12,441.12	1,017.93	(10,977.46)	(898.17)	1,583.41
A	2019/2/28	1,900.00	0.00	(1,558.00)	0.00	342.00
B	2019/2/28	3,800.34	258.02	(3,344.30)	(39.39)	674.67
C	2019/2/28	1,561.31	131.59	(1,377.62)	(116.11)	199.16
D	2019/2/28	17,228.21	1,127.92	(15,201.36)	(995.22)	2,159.54
A	2019/3/31	3,000.00	0.00	(2,550.00)	147.00	597.00
B	2019/3/31	4,193.23	545.56	(3,690.04)	(75.34)	973.41
C	2019/3/31	(5,326.19)	125.70	4,699.58	(110.92)	(611.83)
D	2019/3/31	8,930.78	864.60	(7,880.10)	(762.88)	1,152.40
A	2019/4/30	6,400.00	0.00	(5,440.00)	255.00	1,215.00
B	2019/4/30	2,200.84	384.15	(1,936.73)	(105.75)	542.50
C	2019/4/30	(267.24)	374.80	235.80	(330.71)	12.66
D	2019/4/30	(3,206.93)	802.61	2,829.65	(708.19)	(282.86)
A	2019/5/31	600.00	0.00	(510.00)	498.00	588.00
B	2019/5/31	(10,301.36)	238.45	9,065.20	(44.98)	(1,042.69)
C	2019/5/31	1,778.17	251.49	(1,568.98)	(221.90)	238.79
D	2019/5/31	(1,832.06)	456.65	1,616.52	(402.92)	(161.81)
A	2019/6/30	(10,800.00)	0.00	9,180.00	84.00	(1,536.00)
B	2019/6/30	10,263.04	1,157.46	(9,031.48)	(359.70)	2,029.33
C	2019/6/30	(1,799.24)	358.51	1,587.56	(316.33)	(169.50)
D	2019/6/30	12,976.33	1,335.14	(11,449.70)	(1,178.07)	1,683.70
A	2019/7/31	10,900.00	0.00	(9,265.00)	465.00	2,100.00
B	2019/7/31	(10,173.02)	96.79	8,545.34	(81.30)	(1,612.20)
C	2019/7/31	6,523.97	283.01	(5,756.44)	(249.71)	800.82
D	2019/7/31	11,810.63	640.61	(10,421.15)	(565.24)	1,464.86
A	2019/8/31	(14,900.00)	0.00	12,665.00	48.00	(2,187.00)
B	2019/8/31	3,237.84	158.87	(2,719.78)	(133.44)	543.48
C	2019/8/31	(115.88)	48.84	102.25	(43.09)	(7.89)
D	2019/8/31	(1,738.21)	98.83	1,533.71	(87.20)	(192.87)
A	2019/9/30	(4,700.00)	0.00	3,854.00	0.00	(846.00)
B	2019/9/30	4,180.07	210.64	(3,511.26)	(176.94)	702.51
C	2019/9/30	1,218.19	149.95	(1,074.87)	(132.31)	160.96
D	2019/9/30	(3,596.56)	563.99	3,173.44	(497.64)	(356.77)
A	2019/10/31	(2,500.00)	(1,110.00)	2,050.00	0.00	(1,560.00)
B	2019/10/31	12,124.59	(1,424.73)	(10,184.66)	(353.89)	161.31
C	2019/10/31	(6,022.74)	(140.25)	5,314.18	(78.44)	(927.25)
D	2019/10/31	15,436.92	(672.84)	(13,620.82)	(517.18)	626.09
A	2019/11/30	(1,000.00)	(1,570.00)	820.00	0.00	(1,750.00)
B	2019/11/30	6,703.43	(814.53)	(5,630.88)	(80.10)	177.93
C	2019/11/30	59.20	(462.58)	(52.23)	(88.48)	(544.10)
D	2019/11/30	15,865.36	(826.26)	(13,998.85)	(198.25)	842.00
A	2019/12/31
B	2019/12/31
C	2019/12/31
D	2019/12/31

其中:

销量影响金额=(D3-U3)*(R3*T3)

单价影响金额=I3-T3*R3*D3

销量影响成本=(U3-D3)*V3*X3

采购价影响成本=D3*V3*X3-N3

总的影响数，四项相加。

透视表分析影响结果：

月份	产品	销量影响收入	售价影响收入	销量影响成本	采购价格影响成本	总体影响数
2019/1/31	A	11,800.00	0.00	-10,030.00	456.00	2,226.00
	B	1,960.26	341.34	-1,725.03	-15.56	561.01
	C	-1,832.84	78.30	1,617.21	-69.09	-206.42
	D	12,441.12	1,017.93	-10,977.46	-898.17	1,583.41
2019/1/31 汇总		24,368.53	1,437.56	-21,115.27	-526.82	4,164.01
2019/2/28	A	1,900.00	0.00	-1,558.00	0.00	342.00
	B	3,800.34	258.02	-3,344.30	-39.39	674.67
	C	1,561.31	131.59	-1,377.62	-116.11	199.16
	D	17,228.21	1,127.92	-15,201.36	-995.22	2,159.54
2019/2/28 汇总		24,489.85	1,517.52	-21,481.28	-1,150.72	3,375.38
2019/3/31	A	3,000.00	0.00	-2,550.00	147.00	597.00
	B	4,193.23	545.56	-3,690.04	-75.34	973.41
	C	-5,326.19	125.70	4,699.58	-110.92	-611.83
	D	8,930.78	864.60	-7,880.10	-762.88	1,152.40
2019/3/31 汇总		10,797.82	1,535.86	-9,420.56	-802.14	2,110.98
2019/4/30	A	6,400.00	0.00	-5,440.00	255.00	1,215.00
	B	2,200.84	384.15	-1,936.73	-105.75	542.50
	C	-267.24	374.80	235.80	-330.71	12.66
	D	-3,206.93	802.61	2,829.65	-708.19	-282.86
2019/4/30 汇总		5,126.66	1,561.57	-4,311.29	-889.65	1,487.30
2019/5/31	A	600.00	0.00	-510.00	498.00	588.00
	B	-10,301.36	238.45	9,065.20	-44.98	-1,042.69
	C	1,778.17	251.49	-1,568.98	-221.90	238.79
	D	-1,832.06	456.65	1,616.52	-402.92	-161.81
2019/5/31 汇总		-9,755.25	946.59	8,602.74	-171.80	-377.71
2019/6/30	A	-10,800.00	0.00	9,180.00	84.00	-1,536.00
	B	10,263.04	1,157.46	-9,031.48	-359.70	2,029.33
	C	-1,799.24	358.51	1,587.56	-316.33	-169.50
	D	12,976.33	1,335.14	-11,449.70	-1,178.07	1,683.70
2019/6/30 汇总		10,640.13	2,851.11	-9,713.62	-1,770.10	2,007.53
2019/7/31	A	10,900.00	0.00	-9,265.00	465.00	2,100.00
	B	-10,173.02	96.79	8,545.34	-81.30	-1,612.20
	C	6,523.97	283.01	-5,756.44	-249.71	800.82
	D	11,810.63	640.61	-10,421.15	-565.24	1,464.86
2019/7/31 汇总		19,061.58	1,020.40	-16,897.25	-431.25	2,753.48
2019/8/31	A	-14,900.00	0.00	12,665.00	48.00	-2,187.00
	B	3,237.84	158.87	-2,719.78	-133.44	543.48
	C	-115.88	48.84	102.25	-43.09	-7.89
	D	-1,738.21	98.83	1,533.71	-87.20	-192.87
2019/8/31 汇总		-13,516.25	306.53	11,581.18	-215.74	-1,844.28
2019/9/30	A	-4,700.00	0.00	3,854.00	0.00	-846.00
	B	4,180.07	210.64	-3,511.26	-176.94	702.51
	C	1,218.19	149.95	-1,074.87	-132.31	160.96
	D	-3,596.56	563.99	3,173.44	-497.64	-356.77
2019/9/30 汇总		-2,898.31	924.58	2,441.31	-806.88	-339.30
2019/10/31	A	-2,500.00	-1,110.00	2,050.00	0.00	-1,560.00
	B	12,124.59	-1,424.73	-10,184.66	-353.89	161.31
	C	-6,022.74	-140.25	5,314.18	-78.44	-927.25
	D	15,436.92	-672.84	-13,620.82	-517.18	626.09
2019/10/31 汇总		19,038.78	-3,347.82	-16,441.29	-949.51	-1,699.85
2019/11/30	A	-1,000.00	-1,570.00	820.00	0.00	-1,750.00
	B	6,703.43	-814.53	-5,630.88	-80.10	177.93
	C	59.20	-462.58	-52.23	-88.48	-544.10
	D	15,865.36	-826.26	-13,998.85	-198.25	842.00
2019/11/30 汇总		21,627.99	-3,673.37	-18,861.96	-366.83	-1,274.17
2019/12/31	A					0.00
	B					0.00
	C					0.00
	D					0.00
2019/12/31 汇总						0.00
总计		108,981.53	5,080.55	-95,617.29	-8,081.42	10,363.37