Actual vs Budget Spreadsheet

[Peter Muller]

I have 1 consolidated budget for the group, with the following 'levels'

Level 1 has 4 companies (Co A, B, C, D)

Level 2 has several divisions (Div A, B, C, etc.)

Level 3 has 2 transaction types (Income, expense)

Level 4 has a number of different categories (salaries, maintenance, admin)

Level 5 has the detail, (for admin is IT support, legal fees, etc.)

I have the actual income/expenditure which I need to capture into a database, which is/must be offset against the budget.

I am thinking of using a form for this.

The problem is how to automatically compare the actual vs the budget

Budget is by month, actual is daily

Can this be done in Excel?

I am hoping that there is someone who can guide me through this process/give me ideas/point me in a direction.

 

[GraH]

Hi @Peter Muller ,

Yes it can be done. I would advice to load all data in the data model.
Normalize your data: 4 companies in a dimension or "lookup" table; same for divisions, transaction types, categories. and fees types.
Then 2 fact tables with budgets and actuals.
And add a calendar table for time intelligence.

Once loaded, the model basically can look something like below. I do suspect the transaction types can be linked to categories, then the model will be slightly different.

It allows to create pivots on multiple table. I also suggest to learn the basics of DAX formulas as well. You can create measures that compare actuals versus budget.
I hope this all helps and gives you a direction how to proceeed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		Companies		Transaction Types		Categories
4		A		Income		Salaries			Column Labels
5		B		Expense		Maintenance			2021
6		C				Admin			januari		februari		maart		april
7		D						Row Labels	Sum of Actuals	Sum of Budget	Sum of Actuals	Sum of Budget	Sum of Actuals	Sum of Budget	Sum of Actuals	Sum of Budget
8								Expense
9								Admin
10		Budget	Transaction Types	Companies	Categories	Month		A						500
11		100	Expense	A	Salaries	1/01/2021		B	25
12		500	Expense	A	Admin	1/03/2021		C					25
13		600	Expense	B	Salaries	1/01/2021		Salaries
14		1000	Income	B	Maintenance	1/04/2021		A		100			50
15		750	Income	C	Maintenance	1/04/2021		B		600			850
16								D			10
17								Income
18		Actuals	Companies	Categories	Date	Transaction Types		Maintenance
19		50	A	Salaries	29/03/2021	Expense		A			400
20		50	B	Salaries	30/03/2021	Expense		B								1000
21		25	C	Admin	25/03/2021	Expense		C								750
22		100	D	Maintenance	18/02/2021	Income		D			100
23		400	A	Maintenance	15/02/2021	Income		Grand Total	25	700	510		925	500		1750
24		800	B	Salaries	26/03/2021	Expense
25		25	B	Admin	13/01/2021	Expense
26		10	D	Salaries	19/02/2021	Expense
27
Sheet1

 

[Peter Muller]

Awesome, thank you so much

Will work on this and let you know once completed, and if any further questions, but marking it done for now