Assistance with circular reference

[vernors]

Please help me with the following. This is in Excel 2003.
The actual business process revolves around loan funding and setting loan
advance amounts. Below A1 is borrowing limit, A2 is loan advance, A3 is loan
balance, A4 is advance availability
cell A1 = varying amount
cell A2 = A4 - 1
cell A3 = A2 + varying amount
cell A4 = A1-A3 + varying amount
This is circular, but I believe that I need that. We want the advance A2 to
be as large as possible and A4 to be 1 each day. And I would like these
figures to update when the varing amounts change. Thanks.

 

[vernors]

My question: The formula I show for A2 does not work. Is there a different
formula that would work or is there another solution to my problem? Thanks.

 

[JLatham]

Would you look at your original description and check it closely - something
doesn't look right to me (or else I should have stayed out of this entirely).

You say that A1 is the borrowing limit - a varying amount.

Down in A4 your say the formula is =A1-A3+varying amount
since the varying amount is in A1, isn't this the same as saying =(2*A1)-A3

But that doesn't seem correct, since A3 is the loan balance. Here's what
doesn't look right to me. Lets say we have the following (and ignore the
circular reference issues for the moment)
A1 = 25000
A2 =A4-1
A3 = A2+A1
A4 = A1 - A3 + A1

I guess I'm confused about A2, "Loan Advance" - what the heck is that? An
amount requested?

In any case, if they've already borrowed the limit of 25000, then in A4 you
have this calculation: =25000 - 25000 + 25000
which will show them still having 25000 as the amount of advance available.
They will never run out of credit until about the time they've borrowed twice
their limit? Can I get a card issued to me? <g>

Seems to me the simple set up may be:
A1 = Borrowing Limit
A2 = ?? Requested Advance (assumes so later)
A3 = Current Balance
A4 = A1 - A2+A3
and if A4 shows a negative amount, it means they're overdrawn already and
there is no advance available. Example with values in []
A1 = Borrowing Limit [25000]
A2 = Requested Advance [5000]
A3 = Current Balance [19000]
A4 = A1 - A2+A3 [25000 - (5000+19000) = 25000-24000 = 1000 Available]
So they can only have $1000 even though they requested $5000.

I think I might have set up the following:
A1 = Limit [25000]
A2 = Advance Requested [300]
A3 = Current Balance [20000]
A4 = Advance Available [=A1-A3] [5000]
A5 = Can Advance formula: =IF(A4<0,0,MIN(A2,A4))
[300; i.e. can advance the requested $300]

Another situation:
A1 = Limit [25000]
A2 = Advance Requested [6000]
A3 = Current Balance [20000]
A4 = Advance Available [=A1-A3] [5000]
A5 = Can Advance formula: =IF(A4<0,0,MIN(A2,A4)) [5000; i.e. can only
advance 5000 of the requested $6000]

 

[vernors]

Thanks. I wasn't sure if I should have kept it just a technical Excel
problem or explain the business issue. Here is more of the actual business
process. The spreadsheet projects loan advances on a receivables loan.
There is a column for each week. As the collateral moves up and down, the
borrowing limit(A1) is constantly changing throughout the weeks in the year.
A1=borrowing limit (varying amount)
A2=projected loan advance, how much money do we expect to get each week,
this is what I would like to solve for
A3=Loan balance, prior week's balance + loan advance (A2+variable amt)
A4=Loan advance available, (A1-A3)

As far as projecting goes, perhaps my best bet to avoid the circular
reference is to set A4 equal to my minimum threshold that I would like to
leave available, rather than use the formula, and then insert the formula
into the columns that are actuals (as the weeks go by). So, if I put $1m in
A4, B4, C4, etc. I will solve for A2 without the circ ref.

The circular problem is that - I want to project the advances based on
keeping the availability low, but the advance is included in the balance
calculation which is used to calculate the availability. I think I will try
that for now. I appreciate your response.

Would you look at your original description and check it closely - something
doesn't look right to me (or else I should have stayed out of this entirely).

You say that A1 is the borrowing limit - a varying amount.

Down in A4 your say the formula is =A1-A3+varying amount
since the varying amount is in A1, isn't this the same as saying =(2*A1)-A3

But that doesn't seem correct, since A3 is the loan balance. Here's what
doesn't look right to me. Lets say we have the following (and ignore the
circular reference issues for the moment)
A1 = 25000
A2 =A4-1
A3 = A2+A1
A4 = A1 - A3 + A1

I guess I'm confused about A2, "Loan Advance" - what the heck is that? An
amount requested?

In any case, if they've already borrowed the limit of 25000, then in A4 you
have this calculation: =25000 - 25000 + 25000
which will show them still having 25000 as the amount of advance available.
They will never run out of credit until about the time they've borrowed twice
their limit? Can I get a card issued to me? <g>

Seems to me the simple set up may be:
A1 = Borrowing Limit
A2 = ?? Requested Advance (assumes so later)
A3 = Current Balance
A4 = A1 - A2+A3
and if A4 shows a negative amount, it means they're overdrawn already and
there is no advance available. Example with values in []
A1 = Borrowing Limit [25000]
A2 = Requested Advance [5000]
A3 = Current Balance [19000]
A4 = A1 - A2+A3 [25000 - (5000+19000) = 25000-24000 = 1000 Available]
So they can only have $1000 even though they requested $5000.

I think I might have set up the following:
A1 = Limit [25000]
A2 = Advance Requested [300]
A3 = Current Balance [20000]
A4 = Advance Available [=A1-A3] [5000]
A5 = Can Advance formula: =IF(A4<0,0,MIN(A2,A4))
[300; i.e. can advance the requested $300]

Another situation:
A1 = Limit [25000]
A2 = Advance Requested [6000]
A3 = Current Balance [20000]
A4 = Advance Available [=A1-A3] [5000]
A5 = Can Advance formula: =IF(A4<0,0,MIN(A2,A4)) [5000; i.e. can only
advance 5000 of the requested $6000]

Please help me with the following. This is in Excel 2003.
The actual business process revolves around loan funding and setting loan
advance amounts. Below A1 is borrowing limit, A2 is loan advance, A3 is loan
balance, A4 is advance availability
cell A1 = varying amount
cell A2 = A4 - 1
cell A3 = A2 + varying amount
cell A4 = A1-A3 + varying amount
This is circular, but I believe that I need that. We want the advance A2 to
be as large as possible and A4 to be 1 each day. And I would like these
figures to update when the varing amounts change. Thanks.