Build an ADVANCED Budget vs. Actuals Dashboard
ฝัง
- เผยแพร่เมื่อ 25 ก.ค. 2024
- Create an interactive personal finance budget dashboard from scratch on Excel.
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/62926b...
In this video you'll build a personal finance budget dashboard from scratch using pivot tables to make it dynamic.
First, you'll learn to input transactions either as an expense or income. Then, using pivot tables and conditional formatting, you'll create databars tracking your budget vs your actual expenses by categories such as transport, groceries, leisure etc.
Thirdly, you'll learn to create dynamic charts with pivot tables such as a column chart and a bar chart.
Lastly, we'll insert slicers to make the dashboard interactive such that you can change the months you want to see.
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
📝 Investment Banking Recruiting Course: learn.careerprinciples.com/co...
SOCIALS:
📸 Instagram - / kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains...
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Adding Transactions
1:34 - Actual vs. Budget Databars
11:27 - Expense by Category
14:20 - Cash Balance
16:43 - Income & Expense Comparison
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
👉 Learn Finance & Valuation: www.careerprinciples.com/courses/finance-valuation-course
Llll
Lll
Lolllll
Hello, please I'm interested in the course, the issue I'm having here is that I can't make payment through my cards cause my country card is restricted to transaction of $25 only. Please can I pay via crypto?
Hi Kenji! I’ve been following your TH-cam channel for a bit and you’ve helped me so much with my internship search! I just received an offer from Deloitte and I know your videos played a massive role.. thank you!
Kenji thank you so much for this. I have been using your video and help to update my budgets, it is so much better than one I brought from Ebay and it has given me more tools on excel. Keep up the great work. Tyrone
Thank you so much for attaching the template! Absolute best!
Great content Kenji, you are a great resource to the finance community, thank you!
Thanks so much Christian!
Thank you Kenji! You make learning Excel easy.
Simple and powerful - thank you!!!!
I've been wanting to revisit pivot tables, and there Kenji comes! You're the absolute best, man! I wonder if there is a way for expenses to be automatically recorded after every purchase so even the $2 water is accounted for...
Thanks Kenji! Going to try matching the sheets to the dashboard! Hopefully, I can figure out the shortcuts for macOS!
As usual, you always make it simple, thank you
Thanks for the awesome comment!
Kenji, more videos like this. Thanks for this video bro!
perfect ,greatest, thanks Mr. Kenji
Sweet dashboard! The budget data bars look clean!!!
I think so too!
Great content. As I've just started to dig in to pivot tables and slicers. The only thing that I would of changed is doing just one slicer for both actual and budget, would save a bit of a confusion. And possibly combine the two bar graphs so the actual and planned bars are next to each other to get a better visual comparison.
Love the content, as it gives me a chance to improve my skills as well. Thank you for the videos.
Exactly
Thanks for this info, helped me with creating multiple pivot tables ❤
Well here comes a new personal project. Thanks Bro. Great content as always.
Thanks man! Hope you enjoy it!
Mind blowing stuff! Keep making such things ahead 💖💖💖💖
Thank you so much 😀
Thank you Kenji. appreciated. Great lesson
Glad you liked it!
Please can you do some advanced functions on a personal budget beyond what is shown here like a part 2. Love the content!!
Very helpful Kenji, thanks!😁
Happy to help!
awesome video, keep it up
Huge thanks Kenji for presenting such a useful content. I have a question though what do I need to change if I only want income vs expenses without budget vs actual?
Wow really helpful!! Thanks
You're welcome!
brooooo you helped a lotttt thanksss
I have watched all of your videos, very useful 👌👌👍👍, please make videos on Powerpoint as well.
Always on point
thanks for always supporting man!
Wow. Just wow.
Great work!
How can we adjust the conditional formatting if the actual is higher than budget?
Hi Kenji, when I made the slicer, the rent value became "0", I just don't know how to make it related to the pivot table and not to the cell, thanks :)
I didn't like Excel at all. But your videos make it look like a really good tool that's convenient to use. Thanks for that!
You are inspiration for commerce student ❤💖💙
Wow, thank you!
How do you create sub categories for further breakdown like if groceries is a category under expenses a sub category would be bread, milk, eggs to track rising prices
u see that little cog option button on the bottom near the fullscreen button? if u click on that u can chose to slow it down or speed it up. i
Thanks!
Thank you for supporting the channel Roy! Much appreciated :)
Brilliant
Thank you!
How to add new data on top row in a table ?? can you explain pls
Hey Kenji, any preference on doing it on excel rather than Power BI?
Great job Kenji, congrats. One question in case you inmput data in actual eg for may how update could update automatically all pivots tables?
You need to go to the ribbon in the pivot table tab and hit refresh all
Hey Kenji, I cannot copy the slicer to the dashboard. What can I do?
Thanks!
What am I doing wrong?
I reach the step where I am assigning the Rent category on the dashboard to the total on the RentA pivot table. However, when I type "=" and then assign the total column from the pivot table as shown in the video, it assigns the value as Cell G4 from the pivot table, rather than as the GETPIVOTDATA formula that auto populates when done in the video.
As a result, my slicers don't work since, once sliced, the data I need is no longer in Cell G4.
I can type in the formulas manually to make it work, but that seems tedious.
At 12:00, Kenji adds a pivot table for income and expenses. Mine has "Expense" and "#NAME?" ... What did I miss? My pivot table isn't working correctly because of the issue I stated. About 3 min later, he copies and pastes this table to create another one so my missing data is creating problems. Any tips?
Is there a way to like the months. So you only have to click on one instead of 2 things
do you have a discord? I have an inquiry about a concept of budgeting . How can you show in a dashboard if the budget is subject for adjustments per Quarter? Like realignment of funds
I bombed the excel portion of an interview this week. I know a ton of stuff in excel but you never know what kind of data you will be getting. Can you do one of this with much more complex data?
Hey sorry to hear that...noted thanks for the video suggestion!
Can i use it for google sheet?
How can we make it so the data bars somehow call out in another color through conditional formatting how much $$ we're over budget?
Great video! Can you make one on dividends?
Great suggestion!
question - why aren't those graphs created directly from the Pivots?
Is it possible to do this by linking debits and credits from a bank account ?
I'm in quarintine (like everyone) and wanted to learn sotNice tutorialng new. I thought I could just figure it out but soft is super confusing for a
Hey thanks for the video. I have a question. Instead of having 2 separate slicers for budget and actuals, can't we have a single one? This would make it much easier to manage.
Playing with this today and I just found out that a slicer can only be connected to pivot tables with the same data source so budget and actual would have to be in the same table.
@@sinshoe5870 ya I saw that. Only if we can put both budget and actuals as a part of the same table we can solve this problem.
You should be able to do this by loading the Actuals and Budget tables to the excel data model.
Create a third table called "Calendar" with a single column containing 12 values; Jan-Dec.
load the 3 tables to data model: curser inside Actuals > Data > Get Data > from table/range. repeat for budget and calendar.
In Excel Data Model, make a relationship between Calendar and Budget, Calendar and Actuals. Back on the dashboard, insert slicer, use the Calendar table.
create pivot tables just like in the video but use the excel data model, not the tables themselves.
All pivot tables created from excel data model will update whether they contain budget or actuals.
@@twitchyfingerss Thanks Carlos Diaz. Great idea I have used it before it works
I am not able to download the practice file, I don't know why.
Could anyone please share the file here by uploading somewhere else? TIA
Your excel charts in the downloadable file has name error instead of income and expense. It would be helpful if you corrected that.
Hi Kenji, the months in the slicer come out in an alphabetical order, how can I put them chronologically plz
hey! I see.. it's going to get tricky as you'll need a custom list instead. Here's some instructions: www.sagecity.com/blog/b/excel-tips-tricks-list/posts/how-to-custom-sort-a-slicer
Hi Kenji, seems the Slicer and its connections are not displaying the correct data in the dashboard like in @5:21 (it shows $0 in all categories), what do you think may be the issue for this?
Got it! need the GETPIVOTDATA formula in the categories cells in the Dashboard!
@@cathyambrocio5252 I am having the same issue; however, I can't figure out how to get the GETPIVOTDATA formula to autofill as he does. I am having to type each one individually. Is there another workaround that I am missing?
Hi Kenji, I have a problem. The slicer was not working for me. When I choose any month the amounts under the catergory in dashboard are going to zero. Not sure what mistake I am making. Can you help.
Anyone in slicer povint tabels are not showing any idea
Excellent review of a personal budget. Everything works fine until I start adding new values, new monts, new year. The dashboard does not change. What do I do wrong?
You probably need to hit refresh on the pivot tables
@@KenjiExplains Ok, thanks Kenji
Is your finance and valuation course for non us market residence also?
Yes it is available for people outside the US as well!
THAT IS THE LONGEST POSSIBLE WAY TO DO IT.
Out of curiosity, can all of this be done on Google Sheets as well?
Most probably yes. They do have some great budget templates on google sheets so check those out too!
The download doesnt work anymore :(
Downloaded the excel spreadsheet and there are all sorts of errors. Not quite sure what I did wrong. In the Actuals tab under Income/Expenses all I see is #NAME? and it opened like that... Have I done something?
I had the same problem. If you go to 1:01 you'll see the actual formula for the cells. The formula is wrong of course from the download, it should be =xlookup([@category]... etc.
Good luck!
Hi Kenji, the link hasn’t sent me the download.
see your downloads on the browser/computer
@@KenjiExplains nada ☹️
You did not even talked about the auto input of expense at your last entry as to how it was fornulated...
and kids! thats how Power BI was made
❤☘️🫶
very fast don't understand