IF using the value calculated in an IF function and how to avoid a circular reference

[lepin]

Hi,

Ok I have an issue that I have put into a simplified table below.

The premise is that a business is purchasing units (of whatever) for a given value, however the supplier can only produce 1,000 units per month at max capacity.

The business will reinvest all the profit each month until it reaches the maximum output of the supplier.

At the end of month 6 the supplier output has almost maxed out and hence rather than reinvest the full profit, only the balance required to reach the 1,000 unit maximum would be carried forward as net cash and the remainder would be taken as a dividend.

The net cash figure is calculated as Profit minus Dividend.

Now, were I to use an IF formula that states if the cap is reached then value less cap is the figure to be entered into the dividend column that would be one thing, however the dividend column is a monetary value and not a unit value.

Also I want to be able to use the resultant figure calculated again in the spreadsheet herein shown as the JV dividend and dividend per partner (and this has caused issue in attempting even to copy and paste as value only).

All this can be achieved HOWEVER, I of course now need to have the net cash figure reduced at the end of month 6 to reflect the reduced investment requirement required to be taken forward into month 7 and this is where I get an understandable circular reference.

The net cash figure is affected by the dividend column, however this only calculates for month 7 once month 6 has been calculated and an exceedance reached and therefore reflects back into the month 6 calculations.

In the below I have manually input the required resultant figures but need a formula to resolve the matter as the unit price and unit output maximum can change and hence need the ability to automatically update.

I hope this makes sense

Many thanks for any assistance.

Unit price
Sales price	Max cap
		Max cap $

<tbody>
[TD="class: xl63, width: 79"] 100 [/TD]
[TD="width: 79"]Profit/unit[/TD]
[TD="width: 86, align: right"]150[/TD]
[TD="width: 79"][/TD]
[TD="width: 87"][/TD]
[TD="width: 82"][/TD]
[TD="width: 82"][/TD]

[TD="class: xl63"] 250[/TD]

[TD="class: xl63"] 1,000[/TD]

[TD="class: xl63"] 100,000[/TD]

[TD="class: xl70, width: 72"]Month[/TD]
[TD="class: xl64, width: 79"] Opening cash[/TD]
[TD="class: xl64, width: 79"] Units purchased & sold[/TD]
[TD="class: xl64, width: 86"] Profit[/TD]
[TD="class: xl64, width: 79"] Dividends[/TD]
[TD="class: xl64, width: 87"] Net cash[/TD]
[TD="class: xl64, width: 82"] JV Dividend[/TD]
[TD="class: xl64, width: 82"] JV Dividend per party[/TD]

[TD="class: xl71"]1[/TD]
[TD="class: xl74"] 1,000[/TD]
[TD="class: xl65"] 10[/TD]
[TD="class: xl65"] 1,500[/TD]
[TD="class: xl65"] -[/TD]
[TD="class: xl65"] 1,500[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl79"][/TD]

[TD="class: xl72"]2[/TD]
[TD="class: xl75"] 2,500[/TD]
[TD="class: xl66"] 25[/TD]
[TD="class: xl66"] 3,750[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 3,750[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]3[/TD]
[TD="class: xl75"] 6,250[/TD]
[TD="class: xl66"] 63[/TD]
[TD="class: xl66"] 9,375[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 9,375[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]4[/TD]
[TD="class: xl75"] 15,625[/TD]
[TD="class: xl66"] 156[/TD]
[TD="class: xl66"] 23,438[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 23,438 [/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]5[/TD]
[TD="class: xl75"] 39,063[/TD]
[TD="class: xl66"] 391[/TD]
[TD="class: xl66"] 58,594[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 58,594[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]6[/TD]
[TD="class: xl75"] 97,656[/TD]
[TD="class: xl66"] 977[/TD]
[TD="class: xl66"] 146,484[/TD]
[TD="class: xl66"] 144,141[/TD]
[TD="class: xl66"] 2,343 [/TD]
[TD="class: xl67"] 144,141[/TD]
[TD="class: xl80"] 72,071[/TD]

[TD="class: xl72"]7[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]8[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]9[/TD]
[TD="class: xl75"] 100,000 [/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]10[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]11[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl73"]12[/TD]
[TD="class: xl76"] 100,000[/TD]
[TD="class: xl68"] 1,000[/TD]
[TD="class: xl68"] 149,999[/TD]
[TD="class: xl68"] 149,999[/TD]
[TD="class: xl68"] -[/TD]
[TD="class: xl69"] 149,999[/TD]
[TD="class: xl81"] 75,000[/TD]

</tbody>

 

[Scott T]

Calculate the dividends on the profit not the net cast

where D3 has the max cap$ and the Profit header is in D4 and column E is the dividends

=IF(SUM($D$5:D5)<$D$3,0,IF(E4=0,D5-($D$3-SUM($D$4:D4)),D5))

 

[lepin]

Hi Scott T,

Thanks a million for the formula, which is no wonder I could not get my head around it as a fairly amateur excel user.

The formula works, almost, as I input it into E5 as the first entry under under the Dividend column, however as you will note from the below copy and paste of the result, the units exceed by 10 and whilst this is a whole lot better than I myself have been able to achieve, am I missing something that will take this down to an exact 1,000 unit figure?

My actual tables are more complicated than this and the units higher in value and finance involved much greater and therefore I need to be able to be as exact as possible to make the model work.

Thank you again for your time and sorry if I am being a bit dim witted.

Best regards

Sales price	Max cap
		Max cap $

<colgroup><col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:2889; width:59pt" span="2" width="79"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" span="2" width="82"> </colgroup><tbody>
[TD="width: 72"]Unit price[/TD]
[TD="class: xl65, width: 79"] 100 [/TD]
[TD="width: 79"]Profit/unit[/TD]
[TD="width: 86, align: right"]150[/TD]
[TD="width: 79"][/TD]
[TD="width: 87"][/TD]
[TD="width: 82"][/TD]
[TD="width: 82"][/TD]

[TD="class: xl65"] 250 [/TD]

[TD="class: xl65"] 1,000 [/TD]

[TD="class: xl65"] 100,000 [/TD]

[TD="class: xl75, width: 72"]Month[/TD]
[TD="class: xl66, width: 79"] Opening cash [/TD]
[TD="class: xl66, width: 79"] Units purchased & sold [/TD]
[TD="class: xl66, width: 86"] Profit [/TD]
[TD="class: xl66, width: 79"] Dividends [/TD]
[TD="class: xl66, width: 87"] Net cash [/TD]
[TD="class: xl66, width: 82"] JV Dividend [/TD]
[TD="class: xl66, width: 82"] JV Dividend per party [/TD]

[TD="class: xl67"]1[/TD]
[TD="class: xl68"] 1,000 [/TD]
[TD="class: xl68"] 10 [/TD]
[TD="class: xl68"] 1,500 [/TD]
[TD="class: xl84, align: right"]0[/TD]
[TD="class: xl68"] 1,500 [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl69"]2[/TD]
[TD="class: xl70"] 2,500 [/TD]
[TD="class: xl70"] 25 [/TD]
[TD="class: xl70"] 3,750 [/TD]
[TD="class: xl82, align: right"]0[/TD]
[TD="class: xl70"] 3,750 [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl81"] [/TD]

[TD="class: xl69"]3[/TD]
[TD="class: xl70"] 6,250 [/TD]
[TD="class: xl70"] 63 [/TD]
[TD="class: xl70"] 9,375 [/TD]
[TD="class: xl82, align: right"]0[/TD]
[TD="class: xl70"] 9,375 [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl81"] [/TD]

[TD="class: xl69"]4[/TD]
[TD="class: xl70"] 15,625 [/TD]
[TD="class: xl70"] 156 [/TD]
[TD="class: xl70"] 23,438 [/TD]
[TD="class: xl82, align: right"]0[/TD]
[TD="class: xl70"] 23,438 [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl81"] [/TD]

[TD="class: xl69"]5[/TD]
[TD="class: xl70"] 39,063 [/TD]
[TD="class: xl70"] 391 [/TD]
[TD="class: xl70"] 58,594 [/TD]
[TD="class: xl82, align: right"]0[/TD]
[TD="class: xl70"] 58,594 [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl81"] [/TD]

[TD="class: xl69"]6[/TD]
[TD="class: xl70"] 97,656 [/TD]
[TD="class: xl70"] 977 [/TD]
[TD="class: xl70"] 146,484 [/TD]
[TD="class: xl83, align: right"]143141[/TD]
[TD="class: xl70"] 3,344 [/TD]
[TD="class: xl71"] 143,141 [/TD]
[TD="class: xl79"] 71,570 [/TD]

[TD="class: xl69"]7[/TD]
[TD="class: xl70"] 101,000 [/TD]
[TD="class: xl70"] 1,010 [/TD]
[TD="class: xl70"] 151,500 [/TD]
[TD="class: xl82, align: right"]151500[/TD]
[TD="class: xl70"] - [/TD]
[TD="class: xl71"] 151,500 [/TD]
[TD="class: xl79"] 75,750 [/TD]

[TD="class: xl69"]8[/TD]
[TD="class: xl70"] 101,000 [/TD]
[TD="class: xl70"] 1,010 [/TD]
[TD="class: xl70"] 151,500 [/TD]
[TD="class: xl82, align: right"]151500[/TD]
[TD="class: xl70"] - [/TD]
[TD="class: xl71"] 151,500 [/TD]
[TD="class: xl79"] 75,750 [/TD]

[TD="class: xl69"]9[/TD]
[TD="class: xl70"] 101,000 [/TD]
[TD="class: xl70"] 1,010 [/TD]
[TD="class: xl70"] 151,500 [/TD]
[TD="class: xl82, align: right"]151500[/TD]
[TD="class: xl70"] - [/TD]
[TD="class: xl71"] 151,500 [/TD]
[TD="class: xl79"] 75,750 [/TD]

[TD="class: xl69"]10[/TD]
[TD="class: xl70"] 101,000 [/TD]
[TD="class: xl70"] 1,010 [/TD]
[TD="class: xl70"] 151,500 [/TD]
[TD="class: xl82, align: right"]151500[/TD]
[TD="class: xl70"] - [/TD]
[TD="class: xl71"] 151,500 [/TD]
[TD="class: xl79"] 75,750 [/TD]

[TD="class: xl69"]11[/TD]
[TD="class: xl70"] 101,000 [/TD]
[TD="class: xl70"] 1,010 [/TD]
[TD="class: xl70"] 151,500 [/TD]
[TD="class: xl82, align: right"]151500[/TD]
[TD="class: xl70"] - [/TD]
[TD="class: xl71"] 151,500 [/TD]
[TD="class: xl79"] 75,750 [/TD]

[TD="class: xl72"]12[/TD]
[TD="class: xl73"] 101,000 [/TD]
[TD="class: xl73"] 1,010 [/TD]
[TD="class: xl73"] 151,500 [/TD]
[TD="class: xl85, align: right"]151500[/TD]
[TD="class: xl73"] - [/TD]
[TD="class: xl74"] 151,500 [/TD]
[TD="class: xl80"] 75,750

[/TD]

</tbody>

 

[Scott T]

How is units being calculated?

 

[Scott T]

Assuming you are using Opening Cash to calculate how many units can be produced. Where B5 is opening cash, B1 is the cost to produce an unit and, D2 is the max units.

=IF(ROUNDDOWN(B5/$B$1,0)>$D$2,$D$2,ROUNDDOWN(B5/$B$1,0))

 

[lepin]

Hi again and many thanks Scott T.
You are correct that Opening cash at B5 is the value that can calculate the number of units to be purchased and where B1 is the cost per unit and that D2 is the cap in production numbers.
The Opening cash column at B6 onwards is calculated by taking the previous opening cash value (ie B5) plus the net cash value (ie F5).
That being the case where would I input the code you have kindly provided in order that it would not affect the Opening cash calculation or cause a circular reference?
I am probably sounding really dumb here but please bear with me.
Best regards

 

[Scott T]

it would go in your Units purchased & sold column.

 

[lepin]

Hi again,
Got it and that certainly provides the necessary calculation froward as far as I can tell, however (always an however isn't there! ) I note the net cash at F10 is showing 4,000 and therefore when added to B10 gives us 101,000 in B11. The code thereafter correctly reduces C11 to the cap of 1,000 units however as the purchase price for each unit is 100 we have a cash overage of 1,000 in C11 that is not taken into consideration thereafter in the table and therefore continues throughout the B column.
I realise that if I attempted to take that back into dividends this would cause issue with our original code and as such would not work and also if we were to code into column B to take account of the overage, this would affect the formula therein.
To that end even though the profit column is coded as =SUM(C5*D$1) etc. would using a code to take into account any overage in column B and adding that to the profit column be straightforward or would that not then affect column E dividends into which our original code was input?
Or would a code into column B work (i believe this would cause issue if this code alluded to the overage going to dividends or profit) if the resultant overage were calculated into the JV dividend column as that currently is simply =SUM(E10) as shown on the first occasion of a dividend being paid?
I hope that makes sense in some way or another or am I simply overcomplicating everything?
Many thanks again and sorry for taking so much of your time.
Best regards

 

[lepin]

Sorry cash overage is shown in B11 not C11 that is not taken into account elsewhere.

 

[Scott T]

Use this as your dividends formula. This will add the 1,000 opening cash to the dividends.

=IF(SUM($D$5:D5)<$D$3,0,IF(E4=0,D5-($D$3-SUM($D$4:D4))+$B$5,D5))