Capacity Allocation

Hi All, hope you help

I have a unique requirement for capacity allocation which I want to automate using excel VB

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)
rapidshare.com/files/1281848862/Sample.xlsx?bin=1

Thanks in advance for help!

Also posted here: mrexcel.com/forum/excel-questions/662017-capacity-allocation.html#post3281107

Please ignore the file in previous post. Refer the new attachment and new url
rapidshare.com/files/1822719405/Sample%20Data.xlsx?bin=1