Best practice for arranging and graphing three dimensions of data

[Tim500]

Hello all,

I would like to report on the monthly totals of two income streams and several expense lines for several activities, and present this in an easy to understand graph.

Firstly, I'd like to know if there are best practices on how to arrange the data. I've attached a couple of options that I've tried.

The first option is really cumbersome to enter data into. (I'm manually entering figures from a report at this stage until I figure out a way to pull it in from the finance package).
The second option is easier for data entry, but I don't know if this works for getting the data into a graph.

Then I'd like to know the best method for graphing the data. I'm thinking that a stacked bar chart would be the easiest for the readers to understand, with months along the X axis, and dollars on the Y, and each department having two stacked bars within each month: one with each type of income and the other with each type of expense. But I have no idea how to go about generating this graph!

And is there a more intuitive way to graph this data?

Thanks for your help.

This option is really cumbersome to enter data into.

Book1
	A	B	C	D	E
1	Month	Activity	Inc/Exp	Account	Amount
2	Jul-22	Activity 1	Income	Account 1	354.28
3	Jul-22	Activity 1	Income	Account 2	305.90
4	Jul-22	Activity 1	Expense	Account 4	7.62
5	Jul-22	Activity 1	Expense	Account 5	8.69
6	Jul-22	Activity 2	Income	Account 1	207.50
7	Jul-22	Activity 2	Income	Account 2	551.89
8	Jul-22	Activity 2	Expense	Account 5	13.24
9	Jul-22	Activity 3	Income	Account 1	47.88
10	Jul-22	Activity 3	Income	Account 2	54.57
11	Jul-22	Activity 3	Expense	Account 5	1.89
12	Aug-22	Activity 1	Income	Account 1	90.71
13	Aug-22	Activity 1	Income	Account 2	90.34
14	Aug-22	Activity 1	Expense	Account 5	2.57
15	Aug-22	Activity 2	Income	Account 1	138.42
16	Aug-22	Activity 2	Income	Account 2	346.43
17	Aug-22	Activity 2	Expense	Account 5	9.03
18	Aug-22	Activity 3	Income	Account 1	106.64
19	Aug-22	Activity 3	Income	Account 2	161.76
20	Aug-22	Activity 3	Expense	Account 5	3.18
21	Sep-22	Activity 1	Income	Account 1	189.10
22	Sep-22	Activity 1	Income	Account 2	242.69
23	Sep-22	Activity 1	Expense	Account 3	17.70
24	Sep-22	Activity 1	Expense	Account 5	7.46
25	Sep-22	Activity 1	Expense	Account 6	6.17
Sheet1

This is easier for data entry, but I don't know if this works for getting the data into a graph.

Book1
	A	B	C	D	E	F	G	H
1	Month	Activity	Income		Expenses
2			Account 1	Account 2	Account 3	Account 4	Account 5	Account 6
3	Jul-22	Activity 1	354.28	305.90		7.62	8.69
4	Jul-22	Activity 2	207.50	551.89			13.24
5	Jul-22	Activity 3	47.88	54.57			1.89
6	Jul-22	Activity 4
7	Aug-22	Activity 1	90.71	90.34			2.57
8	Aug-22	Activity 2	138.42	346.43			9.03
9	Aug-22	Activity 3	106.64	161.76			3.18
10	Aug-22	Activity 4
11	Sep-22	Activity 1	189.10	242.69	17.70		7.46	6.17
12	Sep-22	Activity 2	185.93	320.78			9.19	13.25
13	Sep-22	Activity 3	86.19	101.34			2.86	8.74
14	Sep-22	Activity 4		20.24				18.34
Sheet2

 

[Tim500]

Update: I have discovered that I can pull the data out of the accounting package similar to option 1 above, without the inc/exp column, but with positive and negative amounts instead.
Or alternatively I can put debits and credits into separate columns like this:

Activity trends.xlsx
	A	B	C	D	E
1	Month	Activity	Account	Debit	Credit
2	Jul-22	Activity 1	Account 1	354.28
3	Jul-22	Activity 1	Account 2	305.90
4	Jul-22	Activity 1	Account 4		7.62
5	Jul-22	Activity 1	Account 5		8.69
6	Jul-22	Activity 2	Account 1	207.50
7	Jul-22	Activity 2	Account 2	551.89
8	Jul-22	Activity 2	Account 5		13.24
9	Jul-22	Activity 3	Account 1	47.88
10	Jul-22	Activity 3	Account 2	54.57
11	Jul-22	Activity 3	Account 5		1.89
12	Aug-22	Activity 1	Account 1	90.71
13	Aug-22	Activity 1	Account 2	90.34
14	Aug-22	Activity 1	Account 5		2.57
15	Aug-22	Activity 2	Account 1	138.42
16	Aug-22	Activity 2	Account 2	346.43
17	Aug-22	Activity 2	Account 5		9.03
18	Aug-22	Activity 3	Account 1	106.64
19	Aug-22	Activity 3	Account 2	161.76
20	Aug-22	Activity 3	Account 5		3.18
21	Sep-22	Activity 1	Account 1	189.10
22	Sep-22	Activity 1	Account 2	242.69
23	Sep-22	Activity 1	Account 3		17.70
24	Sep-22	Activity 1	Account 5		7.46
25	Sep-22	Activity 1	Account 6		6.17
Sheet3