I am new to VB in Excel. I have a unique requirement for capacity allocation which I want to automate using excel VB and facing hard time doing so, hope you can help.

The objective is to maximize profit by allocating maximum capacity to those products which have highest profit potential first.

Every Month I get demand along with backlogs of previous month. I need to allocate capacity to backlogs of previous month first and then only the remaining capacity for fresh demand.

There are two primary constraints: 1.The number of working days in a month (variable) 2. Not all products can be made on every production line and out of same product may be different for each production line

Also there will be losses whenever there is a change over from one SKU to another depending upon the Variant Type and size of next product. If there is variant change then 8 hours of production loss needs to be accounted and 4 hours in case of size change(8 hours in case of both).

I have attached sample data(Actual data has 10 production lines and 50 products) https://rapidshare.com/files/1822719405/Sample%20Data.xlsx?bin=1

Thanks in advance for help!