creating a bimonthly financial report

[lionelnz]

Hi all. I am in a quandary about creating a bimonthly (every 2 mths) financial report.

I am the treasurer of a small group of volunteers which organises events & promotions on behalf of member groups. We get voluntary donations from the groups which are very haphazard & sometimes not at all from some groups. Our expenses are minimal but variable.

I enter all the donations by month for ease of tracking on one spreadsheet, download our bank account for the 2 months from our on-line account and save that to a 2nd sheet.

What I want to do is have a bi-monthly sub-total for each groups' donations and expenses and also have a running total for each group and expense item. I also have show the book account & bank account balancing.

Any suggestions on how to do this? I don't think a pivot table will work. I am thinking of using match & offset to add the donations to the fin report sheet from the donations and possibly could do the same for some expense items too.

I also have to give the time period that the financial report refers to. I want to do this so that when I copy the sheet I will be able to change dates in key cells and then it will find & update the headings for the time period & then update the financial data. Once I can do this manually I will create a macro to do it for me.

Any pointers or suggestions would be helpful.

Thanks

 

[Akashwani]

Hi,

I guess the reason you have not received a reply is due to the lack of sample data and expected results.
You can post sample data of your input sheet, bank account sheet and your "results" sheet by using one of the links in my signature...

Example of sample data....

Excel Workbook
	A	B	C	D	E
1	ID Number	Name	Amount	Date
2	AA10000	Bart Simpson	368.00	01/01/2011
3	AA10001	Lisa Simpson	277.00	02/01/2011
4	AA10002	Grandpa Simpson	474.00	03/01/2011
5	AA10002	Lisa Simpson	163.00	04/01/2011
6	AA10004	Marge Simpson	330.00	05/01/2011
7	AA10005	Maggie Simpson	346.00	01/01/2011
8	AA10006	Milhouse Van Houten	472.00	07/01/2011
9	AA10000	Bart Simpson	343.00	08/01/2011
10	AA10000	Bart Simpson	300.00	09/01/2011
11	AA10006	Milhouse Van Houten	343.00	10/01/2011
12	AA10010	Homer Simpson	100.00	01/01/2011
13	AA10000	Bart Simpson	301.00	12/01/2011
14	AA10001	Lisa Simpson	147.00	01/01/2011
15	AA10002	Grandpa Simpson	267.00	04/01/2011
16	AA10001	Lisa Simpson	458.00	05/01/2011
17	AA10004	Marge Simpson	436.00	06/01/2011
18	AA10005	Maggie Simpson	299.00	02/01/2011
19	AA10010	Homer Simpson	455.00	09/01/2011
20	AA10000	Bart Simpson	399.00	01/01/2011
21	AA10010	Homer Simpson	425.00	02/01/2011
22
Sheet1

Example results....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Start Date	End Date			ID Number	Name	Amount for time period
2		30/12/2010	04/01/2011			AA10000	Bart Simpson	767.00
3						AA10001	Lisa Simpson	424.00
4	11 Records for this time period, with a total of 3265					AA10002	Grandpa Simpson	904.00
5						AA10005	Maggie Simpson	645.00
6	ID Number	Name	Amount	Date		AA10010	Homer Simpson	525.00
7	AA10000	Bart Simpson	368.00	01/01/2011
8	AA10001	Lisa Simpson	277.00	02/01/2011		ID Number	Name	Total Amount
9	AA10002	Grandpa Simpson	474.00	03/01/2011		AA10000	Bart Simpson	1,711.00
10	AA10002	Lisa Simpson	163.00	04/01/2011		AA10001	Lisa Simpson	1,045.00
11	AA10005	Maggie Simpson	346.00	01/01/2011		AA10002	Grandpa Simpson	741.00
12	AA10010	Homer Simpson	100.00	01/01/2011		AA10004	Marge Simpson	766.00
13	AA10001	Lisa Simpson	147.00	01/01/2011		AA10005	Maggie Simpson	645.00
14	AA10002	Grandpa Simpson	267.00	04/01/2011		AA10006	Milhouse Van Houten	815.00
15	AA10005	Maggie Simpson	299.00	02/01/2011		AA10010	Homer Simpson	980.00
16	AA10000	Bart Simpson	399.00	01/01/2011
17	AA10010	Homer Simpson	425.00	02/01/2011
18
Sheet3

I'm sure that if yu can provide suitable sample data and your expected results for a given time period then someone will be able to help.

I hope this helps and good luck.

Ak

 

[lionelnz]

Thanks AK.

I think I have resolved my problem.

I save my bi-monhtly report sheet as mmmyy. I use this formula =EOMONTH(MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,LEN(CELL("filename",B2))-FIND("]",CELL("filename",B2)))*1,0)
to place the sheet name in a helper cell and create a date.

I then use =Index(match, match)+Index(match, match) formula to sum the 2 values for the current 2 month period for each of the groups.
=SUM(INDEX(GrpDon!$A$1:$AI$17,MATCH(B9,GrpDon!$A$1:$A$17,0),MATCH(Jun13!$H$2,GrpDon!$A$1:$Q$1,0)))+(INDEX(GrpDon!$A$1:$AI$17,MATCH(B9,GrpDon!$A$1:$A$17,0),MATCH(Jun13!$H$1,GrpDon!$A$1:$Q$1,0)))

Someone may have a better way of simplifying this last formula by using Match with Offset maybe.

I will post examples when I have played around a bit more.

Thanks again.