Hi,

This is a general business problem and I used excel just for data entry.
My problem is, I have some loan account which I have granted and some securty values offered by the customer whom the loan were taken. When I take a single customer he/she may have number of loans as well as different securities which I have taken to cover my position. Basically secuirites either can cover 100% of the loans or may be less than the loan amount. The securities and loan values are as follows and can vary from customer to customer.

Security Value Loan Value
S1 550000 L1 580000
S2 500000 L2 300000
S3 100000 L3 140000
S4 50000 L4 160000
Total 1200000 L5 240000


Allocation of securities against the loans are as follows.

Loan Security1 Security2 Security3 Security4
L1 S1 S4
L2 S2 S3
L3 S2 S3
L4 S1 S3
L5 S1 S2

I cannot change combination of the securities but can change the percentage or the proposion of security allocated to a particular loan subject to maximum value of security.This is the proposions I allocated to the above combination.

Loan S1 S2 S3 S4 Uncovered Total
L1 580000 530000 50000 0
L2 300000 300000 0 0
L3 140000 140000 0 0
L4 160000 0 100000 60000
L5 240000 20000 60000 160000
Total 1420000 550000 500000 100000 50000 220000

My requirment is to find a set of rules to minimize the uncovered total (which is 220000 in above case)

Is any one there who can help me please?