Balance Sheet - YTD Calcuation

[NumberMuncher]

Hi,

I have a data table with movements every month. This is then summarised into a report using INDEX MATCH MATCH. This is perfect for month to month.

But how do I sum these so it will add the movement of the month to the sum of the previous months (YTD result)?

Result required	Jun 2015
Cash at Bank	13,250.00

<tbody>
</tbody>

Info provided	Jan 2015	Feb 2015	Mar 2015	Apr 2015	May 2015	Jun 2015
Cash at Bank	2,500.00	3,000.00	5,000.00	1,000.00	750.00	1,000.00

<tbody>
</tbody>

Thank you in advance!

 

[AhoyNC]

Here's one way:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Result required	Jun-15
2	Cash at Bank	13,250.00
3
4	Info provided	Jan-15	Feb-15	Mar-15	Apr-15	May-15	Jun-15
5	Cash at Bank	2,500.00	3,000.00	5,000.00	1,000.00	750	1,000.00
6
Sheet

 

[NumberMuncher]

Thank you AhoyNC!

May I expand on my initial query - how would the formula change with a slight complication to the scenario?

The complication being that Cash at Bank is a consolidated line for multiple rows in my dataset.

Thank you in advance for any help / suggestions!

 

[AhoyNC]

Could you post a small example? Not sure I follow how you have things set up. Do you have subtotals for each month in separate cells?

 

[NumberMuncher]

Hi AhoyNC,

Thank you for the previous formula, that was already a great help.

A	B	C	D
1			28/02/2015
2		ASSETS
3	Code	CURRENT ASSETS
4	0001	Cash and Cash Equivalents	1,000,000.00
5	0002	Trade and Other Receivables	1,125,000.00
6	0003	Inventories	300,000.00
7	0004	Other Assets	10,000.00
8		TOTAL CURRENT ASSETS	2,435,000.00

<colgroup><col><col><col><col span="3"><col></colgroup><tbody>
</tbody>

E	F	G	H	I
1	Movement per month		Jan 15	Feb 15
2	Operating Account	0001	500,000.00	100,000.00
3	Building Fund	0001	10,000.00	5,000.00
4	Library Fund	0001	15.00	0.00
5	Cash Management Account	0001	150,000.00	234,985.00

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

The first sheet is the end result that I am looking for, with reference to the Cash at Bank line.

The second sheet is the information provided. There are multiple accounts with movements that need to consolidate (year to date result)

Any suggestions on how to do the formula?

Thank you!

 

[AhoyNC]

Maybe something like this?

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1		ASSETS			Movement per month	Code	15-Jan	15-Feb	15-Mar	15-Apr
2	Code	CURRENT ASSETS			Operating Account	0001	500000	100000
3	0001	Cash and Cash Equivalents	1,000,000.00		Building Fund	0001	10000	5000
4	0002	Trade and Other Receivables	1,125,000.00		Library Fund	0001	15	0
5	0003	Inventories	300,000.00		Cash Management Account	0001	150000	234985
6	0004	Other Assets	10,000.00		Acct1	0002	62500	250000
7		TOTAL CURRENT ASSETS	2,435,000.00		Acct2	0002	250000	62500
8					Acct3	0002	250000	250000
Sheet

 

[NumberMuncher]

Excellent!

Thank you very much, that works perfectly.

 

[AhoyNC]

You're welcome. Glad that worked for you. Thanks for the feedback.