Limit Detailed amounts based on original balance

[Sandler]

Hi Everyone - Please Help

I am trying to automatically limit my Details by Profit Center based on the original Balance amount. In my example profit center 100 works out fine for me because I have an initial balance of 100,000 and then in the 5 agreements in my Accruals table the total balance is also 100,000.

For Profit Center 105 I have an initial balance of 50,000. The 5 agreements total to 95,000. So in my details column I need to pick up agreement 1 of 30,000, Agreement 2 of 10,000 and Agreement 3 of only 10,000 to get to my original 50,000. (So agreement 3 amount has to be modified from the initial amount to get it up to only 50,000 and Agreement 4 and Agreement 5 should return nothing because I am already at the total max of 50,000.







Accruals



Details




Profit Center
Balance

Profit Center
Agreement
Balance

Profit Center
Agreement
Balance


100
100,000.00

100
1
20,000.00

100
1
20,000.00


105
50,000.00

100
2
30,000.00

100
2
30,000.00





100
3
10,000.00

100
3
10,000.00





100
4
10,000.00

100
4
10,000.00





100
5
30,000.00

100
5
30,000.00





105
1
30,000.00

105
1
30,000.00





105
2
10,000.00

105
2
10,000.00





105
3
15,000.00

105
3
10,000.00





105
4
20,000.00

105
4






105
5
20,000.00

105
5

[SamT]

what to do if Details 105-2 was 15K and you don't have a 5K?

what to do if Details 105-2 was 40K and Details 105-3 and 105-4 were 10K?

[Sandler]

Hi SamT,

It's been a while. Glad to see you again.
I need to learn how to add attachments to the forum. I think I used to be able to do it.
In my example there are 3 sections. The first 2 sections I need to take the lower amount of.
So, the Profit Center 105 in the first section has a total of 50,000. The breakout in section 2 has a total of 95,000.
I need to fill in section 2 for agreements 1-5 in order until it meets the 50,000 threshold.

So, if 105-2 was 15,000 I would need the spreadsheet to return 105-1 - 30,000; 105-2 - 15,000; and 105-3 - 5,000.
If 105-2 was 40K (in the second section - Accruals), then 105-1 would stay at 30,000 and 105-2 would max out at 20,000 and then 105-3, 105-4, and 105-5 should be at zero.

[SamT]

Use the "Go Advanced" button to get to where you can manage attachments.

we should adjust the Values to suit???

[Sandler]

Hi SamT,

Thanks again, I think I posted the spreadsheet this time.

So if Allowed Deductions for the month are larger than Accruals, then the Details should look like the Accruals for the respective profit center.
If Not, it would need to fill the respective Profit Center agreements in Details up to the Allowed Deductions for the Month in agreement order.

The first example shows that Allowed Deductions for the Month in Profit Center 105 is 50,000. Accruals shows a total of 95,000. So, in the Details we would need to max out those agreements in numerical order up to the 50,000.
Accruals Agreement 1 is 30,000 --- Details Agreement 1 we will take 30,000
Now, we only have 20,000 left
Accruals Agreement 2 is 10,000 --- Details Agreement 2 we will take 10,000
Now, we only have 10,000 left
Accruals Agreement 3 is 15,000 --- Details Agreement 3 we will take 10,000
Now, we have nothing left.
Therefore Details Agreement 4 and Details Agreement 5 will remain null.



Allowed Deductions for the month



Accruals (open balances in Profit Center)

Details (Expected Outcome)


Profit Center
Balance


Profit Center
Agreement
Balance


Profit Center
Agreement
Balance


100
100,000.00


100
1
20,000.00


100
1
20,000.00


105
50,000.00


100
2
30,000.00


100
2
30,000.00






100
3
10,000.00


100
3
10,000.00






100
4
10,000.00


100
4
10,000.00






100
5
30,000.00


100
5
30,000.00






105
1
30,000.00


105
1
30,000.00






105
2
10,000.00


105
2
10,000.00






105
3
15,000.00


105
3
10,000.00






105
4
20,000.00


105
4







105
5
20,000.00


105
5