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)?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Result required[/TD]
[TD]Jun 2015[/TD]
[/TR]
[TR]
[TD]Cash at Bank[/TD]
[TD]13,250.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Info provided[/TD]
[TD]Jan 2015[/TD]
[TD]Feb 2015[/TD]
[TD]Mar 2015[/TD]
[TD]Apr 2015[/TD]
[TD]May 2015[/TD]
[TD]Jun 2015[/TD]
[/TR]
[TR]
[TD]Cash at Bank[/TD]
[TD]2,500.00[/TD]
[TD]3,000.00[/TD]
[TD]5,000.00[/TD]
[TD]1,000.00[/TD]
[TD]750.00[/TD]
[TD]1,000.00[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<colgroup><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/02/2015[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]ASSETS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Code[/TD]
[TD]CURRENT ASSETS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]0001[/TD]
[TD]Cash and Cash Equivalents[/TD]
[TD] 1,000,000.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]0002[/TD]
[TD]Trade and Other Receivables[/TD]
[TD] 1,125,000.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]0003[/TD]
[TD]Inventories[/TD]
[TD] 300,000.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]0004[/TD]
[TD]Other Assets[/TD]
[TD="align: right"]10,000.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]TOTAL CURRENT ASSETS[/TD]
[TD] 2,435,000.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Movement per month[/TD]
[TD="align: right"]
[/TD]
[TD]Jan 15[/TD]
[TD]Feb 15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Operating Account[/TD]
[TD="align: right"]0001[/TD]
[TD] 500,000.00[/TD]
[TD]100,000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Building Fund[/TD]
[TD="align: right"]0001[/TD]
[TD]10,000.00[/TD]
[TD]5,000.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Library Fund[/TD]
[TD="align: right"]0001[/TD]
[TD]15.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cash Management Account[/TD]
[TD="align: right"]0001[/TD]
[TD]150,000.00[/TD]
[TD]234,985.00[/TD]
[/TR]
</tbody>[/TABLE]

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.