Summing end of month account balances based on percentages that vary line by line

[MattDRT]

I work for a small nonprofit and a percentage of our donations come into our savings account. All other income and expenses happen out of our checking account. I am building a cashflow tool to forecast when we will need to make a transfer from savings to checking. What formula(s) can I use in cells D22 & D23 to arrive and an end of month sum for each account?

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Month:		Jan		Acct 1	Acct 1 %	Acct 2	Acct 2 &
3
4		Account Begin Balance
5		Checking		100000
6		Savings		50000
7		TOTAL		150000
8
9		Revenues
10		Tickets		800		Checking	100%
11		Donations		500		Checking	50%	Savings	50%
12		Grants		1500		Checking	100%
13		TOTAL		2800
14
15		Expenses
16		GenOp		1400		Checking	100%
17		Program		900		Checking	100%
18		Fundraising		100		Checking	100%
19		TOTAL		2400
20
21		Account Ending Balance
22		Checking		?
23		Savings		?
24		TOTAL
25
Sheet1

 

[SanjayGMusafir]

Check this and revert -

Book2
	A	B	C	D	E	F	G	H	I
1
2		Month:		Jan		Acct 1	Acct 1 %	Acct 2	Acct 2 &
3
4		Account Begin Balance
5		Checking		1,00,000
6		Savings		50,000
7		TOTAL		1,50,000
8
9		Revenues
10		Tickets		800		Checking	1		0
11		Donations		500		Checking	0.5	Savings	0.5
12		Grants		1,500		Checking	1		0
13		TOTAL		2,800
14
15		Expenses
16		GenOp		1,400		Checking	1		0
17		Program		900		Checking	1		0
18		Fundraising		100		Checking	1		0
19		TOTAL		2,400
20
21		Account Ending Balance
22		Checking		1,00,150
23		Savings		50,250
24		TOTAL		1,50,400
Sheet1

Cell Formulas
Range	Formula
I10:I12,I16:I18	I10	=1-G10
D22	D22	=D5+SUMPRODUCT(D10:D12,G10:G12)-SUMPRODUCT(D16:D18,G16:G18)
D23	D23	=D6+SUMPRODUCT(D10:D12,I10:I12)-SUMPRODUCT(D16:D18,I16:I18)
D24	D24	=SUM(D22:D23)

 

[MattDRT]

Check this and revert -

Book2
	A	B	C	D	E	F	G	H	I
1
2		Month:		Jan		Acct 1	Acct 1 %	Acct 2	Acct 2 &
3
4		Account Begin Balance
5		Checking		1,00,000
6		Savings		50,000
7		TOTAL		1,50,000
8
9		Revenues
10		Tickets		800		Checking	1		0
11		Donations		500		Checking	0.5	Savings	0.5
12		Grants		1,500		Checking	1		0
13		TOTAL		2,800
14
15		Expenses
16		GenOp		1,400		Checking	1		0
17		Program		900		Checking	1		0
18		Fundraising		100		Checking	1		0
19		TOTAL		2,400
20
21		Account Ending Balance
22		Checking		1,00,150
23		Savings		50,250
24		TOTAL		1,50,400
Sheet1

Cell Formulas
Range	Formula
I10:I12,I16:I18	I10	=1-G10
D22	D22	=D5+SUMPRODUCT(D10:D12,G10:G12)-SUMPRODUCT(D16:D18,G16:G18)
D23	D23	=D6+SUMPRODUCT(D10:D12,I10:I12)-SUMPRODUCT(D16:D18,I16:I18)
D24	D24	=SUM(D22:D23)

Thank you! I am new to SUMPRODUCT and I am playing around with it now. This gives me exactly what I need.

 

[SanjayGMusafir]

Thank you! I am new to SUMPRODUCT and I am playing around with it now. This gives me exactly what I need.

That's the learning curve which keeps me glued to this forum.

Glad to help you and thanks for the feedback.