Actual vs Budget Spreadsheet

Peter Muller

Board Regular
Joined
Oct 15, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
1624716223718.png


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
ABCDEFGHIJKLMNOPQ
1
2
3CompaniesTransaction TypesCategories
4AIncomeSalariesColumn Labels
5BExpenseMaintenance2021
6CAdminjanuarifebruarimaartapril
7DRow LabelsSum of ActualsSum of BudgetSum of ActualsSum of BudgetSum of ActualsSum of BudgetSum of ActualsSum of Budget
8Expense
9Admin
10BudgetTransaction TypesCompaniesCategoriesMonthA500
11100ExpenseASalaries1/01/2021B25
12500ExpenseAAdmin1/03/2021C25
13600ExpenseBSalaries1/01/2021Salaries
141000IncomeBMaintenance1/04/2021A10050
15750IncomeCMaintenance1/04/2021B600850
16D10
17Income
18ActualsCompaniesCategoriesDateTransaction TypesMaintenance
1950ASalaries29/03/2021ExpenseA400
2050BSalaries30/03/2021ExpenseB1000
2125CAdmin25/03/2021ExpenseC750
22100DMaintenance18/02/2021IncomeD100
23400AMaintenance15/02/2021IncomeGrand Total257005109255001750
24800BSalaries26/03/2021Expense
2525BAdmin13/01/2021Expense
2610DSalaries19/02/2021Expense
27
Sheet1
 
Upvote 0
Solution
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 (y)
 
Upvote 0

Forum statistics

Threads
1,224,000
Messages
6,175,896
Members
452,681
Latest member
jlcm0924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top