Maximize Allocation Based on Price

northtexas

New Member
Joined
Aug 18, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Attachments

  • Screenshot (30).png
    Screenshot (30).png
    57.9 KB · Views: 12

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
ABCDEF
1PartCustomerSelling PriceCapacity by CustomerSales ForecastAllocated
2A1011008020080
3A102758020080
4A103604020040
5A10450402000
6B201807511075
7B202407511035
8B20320751100
9C30150252020
10C3024025200
11D40150503535
12D4022550350
13D4031550350
Sheet1
Cell Formulas
RangeFormula
F2:F13F2=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))
 
Upvote 0
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
ABCDEF
1PartCustomerSelling PriceCapacity by CustomerSales ForecastAllocated
2A1011008020080
3A102758020080
4A103604020040
5A10450402000
6B201807511075
7B202407511035
8B20320751100
9C30150252020
10C3024025200
11D40150503535
12D4022550350
13D4031550350
Sheet1
Cell Formulas
RangeFormula
F2:F13F2=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
 

Attachments

  • Screenshot (31).png
    Screenshot (31).png
    16.4 KB · Views: 10
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top