What If analysis to calculate the effect of new pricing

[fielding]

Current Sales contains product, pricing, and quantity info for 4 products. Each product has a different price. Each customer ordered a different quantity. We want to do a What If analysis to determine the effect that pricing changes would have. The tiered pricing schedule for the 4 products is in Pricing Schedule. There are 2 different price tiers depending on whether the company is a reseller or distributor.

What sort of formula can be used to determine this?

Example: In Current Sales, Distributor 1 (row 6) purchased 29,000 units of Product 1 at $24.95 each, 9,600 units of Product 2 at $26.95 and 45,000 units of Product 3 at $29.
If the tiered pricing in Pricing Schedule had been in effect (Distributor prices, so the correct section – rows 6-10 must be matched to the organization type), what would have been the resulting sales for each product and in total for all resellers and distributor?

If the distributor purchased multiple products, then the calculation needs to be made for each product using the prices in the quantity tiers for the type of company (distributor or reseller).
Spreadsheet, Desired Result, is an example of the desired output.

CURRENT SALES

Example calculation update.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1										Product 1		Product 2		Product 3		Product 4		Sum of Quantity	Sum of Amount
2	Org.	Product 1	Product 2	Product 3	Product 4	Last Order #	Last Order Date	# Orders 2021	Account	Quantity	Amount	Quantity	Amount	Quantity	Amount	Quantity	Amount			Average Price
3	Reseller 1	$18.95		$26.95		10	12/1/2021	20	AAA	10	$189.50		$0.00	10	$269.50		$0.00	20	$459.00	$22.95
4	Reseller 2				$28.95	20	4/30/2021	35	BBB	20	$0.00		$0.00		$0.00	15	$434.25	35	$434.25	$12.41
5	Reseller 3	$24.95				100	04/05/2021	949	CCC	949	$23,677.55		$0.00		$0.00		$0.00	949	$23,677.55	$24.95
6	Distributor 1	$24.95	$26.95	$29.00		1500	07/09/2021	83600	DDD	29000	$723,550.00	9600	$258,720.00	45000	$1,305,000.00		$0.00	83600	$2,287,270.00	$27.36
7	Distributor 2		$26.95		$30.00	10	8/3/2021	6200	EEE	0	$0.00	1200	$32,340.00		$0.00	5000	$150,000.00	6200	$182,340.00	$29.41
8	Distributor 3	$24.95	$18.95	$16.95	$19.95	12	10/19/2021	337800	FFF	2800	$69,860.00	162000	$3,069,900.00	48000	$813,600.00	125000	$2,493,750.00	337800	$6,447,110.00	$19.09
Current Sales

Cell Formulas
Range	Formula
Q3:Q8	Q3	=(E3*P3)
R3:R8	R3	=H3
S3:S8	S3	=(B3*J3)+(C3*L3)+(D3*N3)+(E3*(P3))
T3:T8	T3	=S3/R3
H3:H8	H3	=J3+L3+N3+P3
K3:K8	K3	=(B3*J3)
M3:M8	M3	=(C3*L3)
O3:O8	O3	=(D3*N3)

PRICING SCHEDULE

Example calculation update.xlsx
	A	B	C	D	E	F	G
1	Organization	Min Order	Max Order	Product 1	Product 2	Product 3	Product 4
2	Facility/Reseller	10	49	$28.95	$28.95	$49.95	$49.95
3	Facility/Reseller	50	119	$27.50	$27.50	$39.95	$39.95
4	Facility/Reseller	120	1,080	$26.95	$26.95	$31.95	$31.95
5	Facility/Reseller	1,200	no limit	$24.95	$24.95	$23.40	$23.40
6	Distributor	1,200	11,880	$20.00	$20.00	$24.00	$24.00
7	Distributor	12,000	35,880	$19.70	$19.70	$23.65	$23.65
8	Distributor	36,000	119,880	$19.50	$19.50	$23.40	$23.40
9	Distributor	120,000	no limit	$19.20	$19.20	$23.05	$23.05
Pricing Schedule

Cell Formulas
Range	Formula
C4	C4	=B4*9
B5	B5	=(C4+120)

DESIRED RESULT

Example calculation update.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1										Product 1		Product 2		Product 3		Product 4		Sum of Quantity	Sum of Amount
2	Org.	Product 1	Product 2	Product 3	Product 4	Last Order #	Last Order Date	# Orders 2021	Account	Quantity	Amount	Quantity	Amount	Quantity	Amount	Quantity	Amount			Average Price
3	Reseller 1	$28.95		$49.95		10	12/1/2021	20	AAA	10	$289.50		$0.00	10	$499.50		$0.00	20	$789.00	$39.45
4	Reseller 2				$49.95	20	4/30/2021	35	BBB	20	$0.00		$0.00		$0.00	15	$749.25	35	$749.25	$21.41
5	Reseller 3	$26.95				100	04/05/2021	949	CCC	949	$25,575.55		$0.00		$0.00		$0.00	949	$25,575.55	$26.95
6	Distributor 1	$19.70	$20.00	$23.40		1500	07/09/2021	83600	DDD	29000	$571,300.00	9600	$192,000.00	45000	$1,053,000.00		$0.00	83600	$1,816,300.00	$21.73
7	Distributor 2		$20.00		$30.00	10	8/3/2021	6200	EEE	0	$0.00	1200	$24,000.00		$0.00	5000	$150,000.00	6200	$174,000.00	$28.06
8	Distributor 3	$20.00	$19.70	$23.40	$23.05	12	10/19/2021	337800	FFF	2800	$56,000.00	162000	$3,191,400.00	48000	$1,123,200.00	125000	$2,881,250.00	337800	$7,251,850.00	$21.47
9
10
11
12	New pricing from Current Sales based on org type, product, and order quantity in Pricing Schedule
13
14	Updated calculation
Desired Result

Cell Formulas
Range	Formula
Q3:Q8	Q3	=(E3*P3)
R3:R8	R3	=H3
S3:S8	S3	=(B3*J3)+(C3*L3)+(D3*N3)+(E3*(P3))
T3:T8	T3	=S3/R3
H3:H8	H3	=J3+L3+N3+P3
K3:K8	K3	=(B3*J3)
M3:M8	M3	=(C3*L3)
O3:O8	O3	=(D3*N3)

 

[GlennUK]

There seems to be an error in PRICING SCHEDULE - there is a gap between 1,080 and 1,200 - so there is no pricing information for sales of 1,081 to 1,199. If you can explain that, or rather, say what the correction should be, then I can see a way to do this.

 

[fielding]

Thanks, Glenn. There are gaps in Min Order in rows 7, 8, and 9. That is because quantities in rows 6, 7, 8, and 9 are based on cases and there are 120 units per case. All distributor orders are case quantities. However, for the formulas you may eliminate the gaps. For example, pricing in row 7 would be based on quantities from 11,881 to 35,880. Row 8 min quantity would start at 35,881, etc.

 

[GlennUK]

Thanks for info. Take a look at this:

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1										Product 1		Product 2		Product 3		Product 4		Sum of Quantity	Sum of Amount
2	Org.	Product 1	Product 2	Product 3	Product 4	Last Order #	Last Order Date	# Orders 2021	Account	Quantity	Amount	Quantity	Amount	Quantity	Amount	Quantity	Amount			Average Price
3	Reseller 1	$ 28.95	$ -	$ 49.95	$ -	10	01/12/2021	20	AAA	10	$ 289.50		$ -	10	$ 499.50		$ -	20	$ 789.00	$ 39.45
4	Reseller 2	$ 28.95	$ -	$ -	$ 49.95	20	30/04/2021	35	BBB	20	$ 579.00		$ -		$ -	15	$ 749.25	35	$ 1,328.25	$ 37.95
5	Reseller 3	$ 27.50	$ -	$ -	$ -	100	05/04/2021	949	CCC	949	$ 26,099.87		$ -		$ -		$ -	949	$ 26,099.87	$ 27.50
6	Distributor 1	$ 20.00	$ 20.00	$ 23.65	$ -	1500	09/07/2021	83600	DDD	29000	$580,000.00	9600	$ 192,000.00	45000	$ 1,064,250.00		$ -	83600	$ 1,836,250.00	$ 21.96
7	Distributor 2	$ -	$ 20.00	$ -	$ 24.00	10	03/08/2021	6200	EEE	0	$ -	1200	$ 24,000.00		$ -	5000	$ 120,000.00	6200	$ 144,000.00	$ 23.23
8	Distributor 3	$ 20.00	$ 19.20	$ 23.65	$ 23.05	12	19/10/2021	337800	FFF	2800	$ 56,000.00	162000	$ 3,110,400.00	48000	$ 1,135,200.00	125000	$ 2,881,250.00	337800	$ 7,182,850.00	$ 21.26
9
10
11	Organization	Min Order	Max Order	Product 1	Product 2	Product 3	Product 4
12	Facility/Reseller		10	28.95	28.95	49.95	49.95
13	Facility/Reseller	10	49	28.95	28.95	49.95	49.95
14	Facility/Reseller	50	119	27.5025	27.5025	39.95001	39.95001
15	Facility/Reseller	120	1080	26.95245	26.95245	31.94802	31.948023
16	Facility/Reseller	1200	1200	24.94719	24.94719	23.4	23.4
17
18	Distributor		1200	20	20	24	24
19	Distributor	1200	11880	20	20	24	24
20	Distributor	12000	35880	19.7	19.7	23.65	23.65
21	Distributor	36000	119880	19.5	19.5	23.4	23.4
22	Distributor	120000	120000	19.2	19.2	23.05	23.05
Sheet10

Cell Formulas
Range	Formula
B3:B5	B3	=IFNA(INDEX(D$12:D$16,MATCH(J3,$C$12:$C$16,1)),0)
C3:C5	C3	=IFNA(INDEX(E$12:E$16,MATCH(L3,$C$12:$C$16,1)),0)
D3:D5	D3	=IFNA(INDEX(F$12:F$16,MATCH(N3,$C$12:$C$16,1)),0)
E3:E5	E3	=IFNA(INDEX(G$12:G$16,MATCH(P3,$C$12:$C$16,1)),0)
B6:B8	B6	=IFNA(INDEX(D$18:D$22,MATCH(J6,$C$18:$C$22,1)),0)
C6:C8	C6	=IFNA(INDEX(E$18:E$22,MATCH(L6,$C$18:$C$22,1)),0)
D6:D8	D6	=IFNA(INDEX(F$18:F$22,MATCH(N6,$C$18:$C$22,1)),0)
E6:E8	E6	=IFNA(INDEX(G$18:G$22,MATCH(P6,$C$18:$C$22,1)),0)
Q3:Q8	Q3	=(E3*P3)
R3:R8	R3	=H3
S3:S8	S3	=(B3*J3)+(C3*L3)+(D3*N3)+(E3*(P3))
T3:T8	T3	=S3/R3
H3:H8	H3	=J3+L3+N3+P3
K3:K8	K3	=(B3*J3)
M3:M8	M3	=(C3*L3)
O3:O8	O3	=(D3*N3)
B16	B16	=((C15+120))*1

 

[fielding]

Copying cell by cell is quite tedious.

 

[Fluff]

You do not need to copy cells individually, you can copy/paste the entire mini-sheet in one go using the copy icon

 

[fielding]

Terrific. Thank you!