Maximize Allocation Based on Price

[northtexas]

Hello,

I am not sure if it is possible to do in Excel, but is there a formula or way to use Solver to allocate based on the selling price, and is contingent upon (1) the allocated amount must be less than or equal to the capacity, and (2) the sum of the allocated amount by part number must be equal to the sales forecast by part number. The Sales Forecast is the total per part number, but per customer, this would be how many Part A's I expect to sell in total.

For example: The manual solution for Part A would be 80 units for customer 101, 80 units for customer 102, and 40 units for customer 103, and 0 units for customer 104.

Thank you for taking the time to read this post, and I appreciate any insight you may have

 

[SanjayGMusafir]

Check if this is what you want -

Also a suggestion, Please upload the sample data using XL2BB utility so that we don't have to recreate whole data to set up a formula

All Records.xlsb
	A	B	C	D	E	F
1	Part	Customer	Selling Price	Capacity by Customer	Sales Forecast	Allocated
2	A	101	100	80	200	80
3	A	102	75	80	200	80
4	A	103	60	40	200	40
5	A	104	50	40	200	0
6	B	201	80	75	110	75
7	B	202	40	75	110	35
8	B	203	20	75	110	0
9	C	301	50	25	20	20
10	C	302	40	25	20	0
11	D	401	50	50	35	35
12	D	402	25	50	35	0
13	D	403	15	50	35	0
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=LET(AA,SUMIFS($D$2:$D2,$A$2:A2,A2),AB,SUMIFS($F$1:$F1,$A$1:A1,A2),IFS(AA<=E2,D2,AND(E2-AA<0,E2-AB<0),0,TRUE,E2-AB))

 

[northtexas]

Check if this is what you want -

Also a suggestion, Please upload the sample data using XL2BB utility so that we don't have to recreate whole data to set up a formula

All Records.xlsb
	A	B	C	D	E	F
1	Part	Customer	Selling Price	Capacity by Customer	Sales Forecast	Allocated
2	A	101	100	80	200	80
3	A	102	75	80	200	80
4	A	103	60	40	200	40
5	A	104	50	40	200	0
6	B	201	80	75	110	75
7	B	202	40	75	110	35
8	B	203	20	75	110	0
9	C	301	50	25	20	20
10	C	302	40	25	20	0
11	D	401	50	50	35	35
12	D	402	25	50	35	0
13	D	403	15	50	35	0
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=LET(AA,SUMIFS($D$2:$D2,$A$2:A2,A2),AB,SUMIFS($F$1:$F1,$A$1:A1,A2),IFS(AA<=E2,D2,AND(E2-AA<0,E2-AB<0),0,TRUE,E2-AB))

Sanjay,

This is close, however I need to take this one step further. It appears as though that formula allocates to the first line-items until capacity is fulfilled. In reality, my data does not sort by the price from highest to lowest. Please view the attached screenshot to see what it would really look like and what I mean.

P.S. I tried to download the XL2BB add-in but it will not work on my laptop, I am sorry for the inconvenience

Ryan