ULTIMATE Personal Budget & Interactive Dashboard in Excel (FREE Template)
ฝัง
- เผยแพร่เมื่อ 1 มิ.ย. 2024
- ➡️ Download and customize a FREE automated personal budget template here: bit.ly/pbtemplate2024
With the cost of living crisis affecting everyone, it is essential to get control of your finances so you don't leave yourself short. I've put together this interactive personal budget tracking spreadsheet designed for use in future years. It's super quick and easy to use for multiple bank accounts, all in one place, and it gives you a handle on your financial position at a glance. It highlights problem areas, and you can drill down to identify the cause and take corrective action to get back on track.
Resources mentioned in the video:
1. Excel Tables as Source for Data Validation Lists: www.myonlinetraininghub.com/e...
2. Power Query Course: bit.ly/powerqcourse
3. Excel Dashboard Course: bit.ly/dashboardscourse
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 What you can expect
0:37 Requirements & bonus
1:07 Creating lists of income and expenses
2:58 Preparing the budget
4:17 Recording actual spend
5:53 Recap and important information
6:28 How to gather and clean data
10:40 How to merge tables
13:18 How to analyze data & create report
25:40 Renaming PivotTables
26:07 Inserting a timeline slicer
27:37 Simplify data with this feature
29:08 Surplus or deficit
#PersonalBudget #BudgetTracker #ExcelTutorial - วิทยาศาสตร์และเทคโนโลยี
❓What are your financial goals for this year?
Marry into money 💰😜
@@rob4433 lololololol ☝️
🤣👌🏼@@rob4433
Not rely on the bank of Mum & Dad
Get overview of income and expenses during 2024 because I retire mid year. This tutorial will help with that! (I've done Excel budget for many years but very manual and timeconsumming). I'll add a function and graph of withdrawing on my savings accounts/pension funds on a two-three decades overview. I am not sure how to organise or present the use of savings yet, but something with extending the budget to future years added with inflation. I would like to know what happens in economy when I either have to replace my car (every 8-10 years), or my spouse dies (loss of expenses and income). My pension funds get paid either lifelong or 10 years (when I say start) or a lump sum. I would be good to know if I can afford a new PC laptop to my new hobby in Amateur Astronomy Imaging (expensive hobby) or a PowerQuery cource with you eg. ;-)
If you have any tips I would like to know?
This is not only the best budget spreadsheet I have seen, but the best step by step tutorials I have ever seen. You have gone out of your way to make the whole thing as simple as possible to understand. I have seen lots of these over the years and the vast majority of people make them too cumbersome to use and don't give proper explanations either
Wow, thank you! So pleased you liked it 😊🙏
I absolutely love this budget worksheet. I have been customizing my spreadsheet for 10 to 15 years so I have to stay with that one, but I am also going to use this one for a big picture overview. I would suggest adding a savings rate area and a net worth area.
I have seen a lot of videos on how to construct personal budgets to control your finances and this is by far the best one.
Awesome to hear, George! Thank you 😊
I just finished watching your personal finance video from 2 years ago and it was really well made. Having this updated video is even more amazing. Thank you so much for all the effort, honestly it helps a lot
Wonderful to hear! 🙏
This is absolutely fantastic, I was sat here pondering creating something similar for 2024 and beyond and here is it. Thank you.
Glad it was helpful! 😊
You already had the best personal finance dashboard with your previous iteration. This one is even better. And, you make it so user friendly with this fantastic, easy-to-follow tutorial that I've been able to add some customization. I replaced the income donut with a donut that shows mortgage information: equity, dynamic balance, and dynamic payoff date. Kudos to you, Mynda!
Wow, that sounds great 😊
Professional, informative, easy to follow instructions, as always Mynda. Thank you and Happy New Year!
Thank you for watching and happy New Year 🥳
Mynda, I have been following you and your work for about a year now and I just wanted to chime in and tell you what I am sure many people have told you already and that is that you are a true Excel gem!. I love your videos, I have downloaded many of your cheat sheets and handouts and templates and now I am checking out this wonderful interactive budget with dashboard that I have been looking for to finalize my killer budget. I still have a very large learning curve with Excel, but you make it all worth learning it. Thank you so much for what you do.
Wow, thanks for your kind words, Mark! Sincerely appreciated 🥰
A piece of art as always Mynda - and agree on comments before, the best budgetting spreadsheet and simple tool I've seen also and have been hunting last few months! Thanks as always
Awesome to hear 🙏😊
Excellent job, Mynda, as usual, and extremely important for the beginning of this year. Every year I make a budget, but with this tutorial and your template I will have a very effective tool to be able to monitor all expenses and income on a monthly basis. Thank you very, very much and have a great new year.
Thanks for your support, Henry! It's great to hear you budget every year. Awesome discipline!
Mynda you are an international treasure!
Thanks so much! Glad you enjoyed it 😊
Happy New Year Mynda! This is excellent, thank you. Gives me some ideas for my own budget tracker that I've been using for years
Glad to read that 😊
This is honestly the best thing I could have found! I was trying to look for some sort of budget template and this one was the best, thank you!
You're so welcome! 😊
Great timing and with no doubt great content. Thanks alot Minda and happy new year.
Thanks for your support! Happy new year to you too 😊
Thank you for getting back to me promptly and for the proposed PFD... Cheers
Thank you so much for continuing to present practical Excel solutions...much appreciated.
🙏 So glad it was helpful!
Bloody legend! Thanks so much for the beautiful, easy to use, and free excel sheet, as well as the best tutorial to along with it 🙏
So pleased you liked it 😊😁
Brilliant! What a nice way to start my year more organised.
So pleased you can make use of it 😊
Bless you for making this! I don't know how many hours you just saved me--you are a godsend!
Glad I could help 😊
Thank you so much for this video. I used it as a tutorial to develop my own version that fits my needs perfectly.
Love you content!
Great to hear!
Thank you for this amazing template! Happy new year!
Thank you! 😊
Awesome, thank you! And happy new year to you and yours.
Happy New Year to you too 😃
Happy New Year, Mynda. This is an excellent video, as always. I saw your previous version of the Budget Dashboard, but this one is more refined, and using PowerBi makes more sense.
I was aiming to improve on my previous personal finance dashboard, so I'm glad you noticed 😁
Was searching to build exactly something like this the last few months. Perfection 👌.
Thanks so much!
As always. at least a couple of things I have been doing in a more complicated manner until now. Thank you.
So glad you can make use of these techniques.
Fantastic, thanks Mynda….you are a legend
Thanks so much 🙏😁
Thanks Mynda for the template.
One day, I wish we could use CDR Open Banking APIs to directly pull transaction data from various banks into this Excel template (perhaps using built-in Python magic), so we could seamlessly refresh bank transactions each time the Excel file is opened or on button trigger, rather than csv importing once a month, which I think is too longer time gap to reconcile.
Beautiful work. Thank you ❤
Thank you! 😊
I did something like this a few years ago, but, this takes it to another level, thank you very much.
Glad you liked it! 🙏😊
Incroyable, merci Mynda !
🙏 thanks so much! Hope you find it useful.
I love how attractive it is - not as clunky and off-putting as so many spreadsheets. I will be sharing this around in various budgeting forums I've been visiting! One embarrassingly basic question - how do I rename the bank accounts? I know it's technically correct but "Checking" is not the name I'd choose with our bank setup.
Awesome to hear 🙏 when you enter your own bank transactions, the report will pick up the name of the bank accounts you enter in the transactions table 😉
I think people who get put off on hearing Power Query don't know what they are missing out on...this is fantastic work❤
Thanks so much!
Love your templates 😎
Thanks so much! 😁
This is simply beyond outstanding, I watched 22 minutes, and it is absolutely mind blowing. Definitely, I am going to purchase one of your courses, I can't still decide which one.
I want to create a very nice dashboard like this one, I have several things going on my mind on how to make it work, since I use my credit card for daily expenses and I paid the with credit card monthly without causing interest, I also share the household with my wife, she transfers me money and I paid for everything. I know with your videos I can make it work, thanks Mynda!
Wonderful to hear!🙏 Be sure to watch to the end 😁
I'd probably enter the money from your wife as a reduction to expenses, rather than income and the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss.
If it's reports you want to build, I recommend my Excel Dashboard course: www.myonlinetraininghub.com/excel-dashboard-course
@@MyOnlineTrainingHub That's the one I was thinking of yesterday, I am going to go with that one. Thanks for replying to me and the tips. You're awesome, genius and dedicated🤗
@@MyOnlineTrainingHub Hi Mynda, fantastic work! I have been trying to use YNAB, and frankly, I still feel blind. This is clean, and comprehensive, and I'm an excel guy, so I want to adopt this going forward. I am confused about how to handle credit card transactions. I see in Transactions where to add CC transactions as a Credit Account, but how do you add payments? Simply as a Debit from Checking? What do you Categorize it as? I am unclear what you mean above when you say "the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss." Regarding CC debt (or any loan debt), do you have an add-on sheet to track that as well? I'm going to get started as is, leaving these off for now, but getting some clarity on the above would be incredibly helpful. Thx!!
Hey thank you so much for this video ! I am a student trying to figure out how to manage my finances and I needed a budget spreadsheet that I could customise to my likings ! Very grateful for this, thank you :)
Glad it was helpful!
Wow thank you SOOOOO much for sharing this valuable information 😊
Can't wait to learn more from your channel 😊
Thanks
You're so welcome! 😊
excellent and Happy New Year 2024 Mynda
Thank you! Same to you!
Great tutorial in every sense 👌
Glad you think so!
Simply superb !!
Thanks a lot 😊🙏
I've done something similar for my own finances this year. It's an excellent exercise in so many excel skills. My best suggestion would be to setup a table that maps descriptions to subcategories so that reoccurring transactions are automatically mapped. That way you only need to map the transactions with new descriptions.
Great tip! Thanks for sharing 🙏
how would i go about doing that?
@AntonioGonzalez-no2hk make a table which contains the transaction description in one column and the corresponding categories in another column. You only need to do each description once. Then load it to PowerQuery and merge it to the transactions table. This will essentially do a Xlookup between the transaction table and the new table using the descriptions as the common factor. If you don't know about PowerQuery this is a great simple thing to get you introduced to it.
This makes the one I set up a couple of years ago look like primary school.........thank you!
😁 thanks for watching!
This is the first time I come across one of your videos. Where have you been all my life? I have a lot of catching up to do. You are amazing
Wow, thank you so much! Glad you enjoyed my video and hope you find more that are helpful to you 😊🙏
Thanks for sharing!
My pleasure 😊
Just three words Wow! Wow! and WOW!
Thank you! Happy to read that 😃
Hi. You are THE TH-cam channel I go to to learn new additional features in MS Excel and I've seen a lot of your, and other channels, videos on personal expenses. But I would say almost all, if not all, only consider entire years. If I'm in, for example, June-2022, I would like to see my last 12 months and compare them to their previous 12 months (a 24 month comparison). How does one set time slides and Pivottables when one wants to choose the report on a specific month, say 3 months ago? Thank you so much for your wonderful work. The way you explain everything is just so clear!!! And love you use dd/mm/yyyy date format: it makes it SO obvious!
Thanks for your kind words and support 🙏🥰 check out this post on rolling 12 month periods: www.myonlinetraininghub.com/excel-slicers-for-rolling-periods
This is amazing! The template I downloaded also includes a Savings tab which is not mentioned in the video. Just need to clear some 2023 filters and add Jan to Dec 2024.
Have fun with it! 😁
I spent quite a while trying to make a cumulative chart not reset on the new year as yours seemed to do on the savings sheet. Then I realized it doesn't, it just looks like it does because the Jan saved is larger than for Dec. 😋I want to do something slightly different so will work around it. Thanks for the great content, I always learn something.
Glad you found it helpful 😊🙏
Thank you Mynda for this Tutorial. I LOVE IT ❤❤❤❤🙏🙏
One tiny tiny remark: Some Expenses at the top don't match with the table below (Living, Dining out, and Discretionary)
Thanks so much! Oops! Thankfully the headline figures in the completed file that you can download match.
Another GREAT tutorial from the GOAT of EXCEL. Many thanks for providing another educational video on "How To." Mynda, do you think it would be worthwhile developing a comparison follow-on video that creates the dashboard using Power BI vice Excel? All the best to you and your team!
Thanks for your kind words 🙏😊 interesting suggestion. Maybe not for this video as it’s aimed at personal use, so not many people will want to pay for Power BI for this. But maybe for other examples. 👍
This is amazing! Would love a dashboard that tracks multiple home loans 😊
Great idea!
LOVE this! So very helpful and useful. I'm curious how best to expand on this to not only record these various transactions from multiple accounts but also capture across multiple institutions. Your thoughts?
Thank you! You could add a column to your transactions and budget tables for the institution and then add that as a Slicer in the report so you can see one institution at a time.
Great video! How would you go about splitting a transaction between multiple budget categories?
Thank you! If the transaction relates to two categories, then enter the amounts on separate lines with the different categories, so that the two amounts add up to the one transaction in your bank account.
Absolutely love this spreadsheet and your easy to follow instructional videos. I have used Mint for many years and I'm sure like most of us, I was scrambling to find an alternative when they decided to discontinue it. Is it possible to rollover budget surplus or deficit amounts to the next month's budget? Can you please provide some direction?
Glad it was helpful! You can keep a running total of the surplus/deficit, but it's not trivial to explain how and largely depends on where you want to see it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi! This is a game changer, thank you! Do you have a tutorial for the savings tab? When I try to create it the pivot table isnt picking up the month and year from the "monthly savings amount" list.
Glad you like it! I didn't do a video for the savings tab but there are notes on that tab as to how to use it. It's very straight forward. Just enter your data in the table and press Refresh All on the Data tab of the ribbon. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
thankyou so much i feel over the moon when i see yours videos
Aw, thanks so much 🥰
Thank you ❤❤😍😍
You're welcome 😊
Wow Mynda! this must be a lot of work, creating this file and tutorial. thumbs up for you. This was excellent, but you also could have added a small kind of liquidity plan: your bank account ant the upcomming payments and receivings, so you know you are (or are not) running out of cash....
Yes, a cash flow report would be a nice addition. Good idea. Fortunately, there's always something more that can be done 😁
Hi and big thank you for great content again. One question though - when combining tables in PQ would the solution where you connect tables and load them to datamodel and have power pivot afterwards to create reports later also work and be less „cluttered“ ?
You're welcome. You can use Power Pivot, but it wouldn't be less cluttered as such. It would just load the data to the Power Pivot data model instead of the Pivot cache. Both are held in Excel's memory rather than the worksheet.
Super 👌
Thank you! I’m glad you liked it 😊
Thank you, Mynda, for your brilliant work. Do you have much use for Sankey diagrams in budgeting and finance, or are they more gimmicky? I couldn't find anything Sankey related on your channel.
Thanks for your kind words 😊 I don’t find a lot of use for Sankey diagrams. They’re useful for showing the movement of data from one point to the next.
Mynda thank you so much for your efforts.
how to prepare excel with daily schedule,weekly plan,to-do list,smart calender-task tracker like a Google sheet ?
You're welcome! I'll keep your request in mind for future videos.
Wow. Thanks.
You're welcome 😁
Excellent
Thank you so much 😀
All of these tutorials are awesome! We're all lucky they are available on TH-cam! I have one question please: when I import my bank transaction data, the transaction description is split across 2 or 3 rows most of the time, but not all, and the'useful bit' is not always in the same position (sometimes the first row, sometimes the second etc). However, the date and amount are always in line with the first row. Do you have any advice for automating cleaning this up please? I know there are techniques for merging columns of text into a row but I've only seen manual methods. If you have any tips or tricks for speeding this up or automating it I would be very grateful!
Great to hear, Anthony! I'd use Power Query to clean your banking data. If you get stuck, you're welcome to post your question and sample Excel file containing dummy data on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you so much for putting this together, Mynda! I'm really new to Excel and seeing you walk step by step through this has helped me enormously! I downloaded your free template and have been trying to get it to look like yours but with of course my own data. I've been going through the tabs, starting with the Categories tab, then the Budget and have now completed entering all my own transactions for the month of January'24 in the Transactions tab. But when I refresh the data in the "Reports" tab the charts look all wrong. Is it because I've skipped the "Power Query Editor" section? I can only assume so! Will have to pluck up the courage to go to that step next! Thanks again for your time and for being such a saint to us beginners!
Great to hear, Jack! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for this video, can I change the language to my own language, if so is there a formula I must change for this
Excel will automatically change the language for the functions and formulas etc. to that of your PC upon opening the file.
Hello, first of all, congratulations because your tutorial videos are awasome. I really like the way that all is explain and show. The second is a question regarding power query. I have done all your steps in the same way, and I also saw that in your video the budget amounts have a "null". My problem is that the budget values are not showing in the pivot table, or shows are grouped in a blank category. How do you fix this? I am not very familiar with Power Query but looking foward to learn more about it.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Such a helpful tool, thanks so much.
Bit of an excel noob so hoping someone will be able to help me figure out a couple of things now I've been through the process of adding my categories, budget and transctions:
1) For each cell in my budget where I've added data, there's a green triangle in the top left hand corner. Everything seems to work fine otherwise.
2) In the Variance column for the income section of the P&L statment, I just have a row of ##### - any ideas?
3) Is there a way to change the currency throughout the spreadsheet that won't risk screwing up all of the infrastructure?
Thanks in advance!
Great to hear!
1. ignore the green triangle. It just means that the formulas in the column are not the same, which is fine for the budget.
2. try making the column wider to display the contents of the cell
3. you can change the currency. It's just formatting.
Very beautiful dashboard!
In order to automate mapping subcategories to transactions based on counterparties, should i create a mapping table TblCounterparties?
Thank you! Yes, you could add a mapping table for the counterparties and do the mapping with a Power Query merge.
What’s the best way to document the transaction of paying off your credit card bill? Is that a subcategory that should be added or what is the best approach?
That's a transfer from your bank to your credit card. It shouldn't be included in your report because you've already accounted for those amounts in the transactions. You can just tag it with a new category type called 'Transfer' and exclude this from your Profit & Loss.
I love wathching this video since i wanted to do something similar. After watching the video and doing everything step by step i have a question. I did not filled out the budget table until the end of the year since a lot of my expenses categories and income change is there a way that once i fill it out the profit loss pitvot table can update on the budget column? any help will be appreciated. Thanks
I would just enter zeros for your budget items until such time that you have the data. That way, the queries and PivotTable will work without any errors.
Hello Mynda, Love this as it's a very insightful goal planner🙂however I'm struggling with the pivot, because I have blanks seeing that the power query is not reading the budget categories when I load the pivot resulting data not showing under the budget column in the pivot instead blanks that have all the budget figures. Please advise🙌🤲
Blanks in your budget shouldn't be an issue. I guess it's not clear where they are. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Okay will do, thanks for the prompt reply, much appreciated☺
Hi Brenda, Thank you for explaining of the budget spreadsheet. I do have a question though, how would you do a visual of the top 5 sub-category's expenses in this budget spreadsheet?
I'd put them in a bar chart 😉or if you wanted to visualise them over time, then a line chart would probably be ok.
@@MyOnlineTrainingHub Oh thanks for the advice!
Hi,
Thank you for this template. It is a great guide to create your own personal financial dashboard board. I have a question regarding the timeline slicer, I want the slicer to only show a particular date range. For example- just from April 2023- March 2024. how can I do that? My tables only have data from April 2023, but in the timeline slicer I am getting data from January 2023. How can I get this custom date range?
Thanks.
Thank you! The timeline slicer always shows complete years.
Hi, I have created a budget based on your previous video's and it been working Great!!. However I always like to upgrade and improve. I am struggling to display the transactions linked to the category for the period selected. Meaning, For February I Spend 100 on Takeout's but where, so a short descriptive Section on the side would help understand instead of jumping back to the transactions sheet and start filtering. And as you select your period and category then the data changes as per what is logged in your transactions table. I Know how to do it in Power BI as it would be simple. But How do I go about this in excel. Just taking a shot here to ask you.
Great to know you're making use of these techniques. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Good evening! I have one question about Excel versatility. If I have a MacBook, does Excel work with Mac OS in the same way it does for Windows? Does it have all the functions? Thank you for your time and great job on your channel I love your content.
The short answer is no! Excel for Mac is getting closer to Excel for windows but it still falls behind with Power Query (very limited functionality) and Power Pivot (non existent). If you need these tools then use Parallels or similar to run a windows virtual machine on your Mac.
@@MyOnlineTrainingHub thank you very much for your answer, it is a shame really to not have the possibility to do the functions your are showing here on a Mac.
This is amazing! I've set it up and because I've got so many sub-categories I'd prefer the budget to be at category level not sub-category. So I created the budget table with categories... and then in the merged query I joined using "Category" as they are still common to both tables - for some reason this is causing each transaction to be duplicated 4 times in the end results! I've spent ages trying to work out why but it's defeated me. Clearly I don't expect you to try and understand / debug what I've done but does anything obvious spring to mind? Or do you have any quick tips for having the budget at Category level? Thanks so much for the fantastic template and sorry for bothering you with my question!
Glad you’re making use of these techniques. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks - I'll do that
To begin with. I love you. Second of all put the link of ur website in the about sectioj since i noticed that u gave course which of course iam going to buy some. I love the way you teach execl❤
Glad you're enjoying my videos! The link to our site is in the channel description, but here it is again for your convenience: www.myonlinetraininghub.com/ Please reach out via email if you have any questions as I won't see follow-up comments on this thread: website at MyOnlineTrainingHub.com
Hi...Thanks for this great tutorial...
Need 1 advice: Say i transfer balance between my accounts.How do i reflect that.
Example: I transferred 4k from my account to my wallet. And used cash for multiple expenses...How to cater that.
Hope i am able to explain it
In the Transactions list the Description will be 'Transfer to xyz account' and the Sub-category will be 'Transfer'. For cash you will 'Transfer' money from say your 'Saving' account to your 'Cash' account. Then when you spend cash, the account will be 'Cash'. You'll also want to exclude the 'Transfer' Sub-category from your report.
Hello, thank you very much for this amazing template. I dind't found the way to update the sub-category slicer in the Transactions sheet when they were changed in the Categories sheet.
If refreshing all (data tab) doesn't work, then try deleting the Slicer and inserting it again.
@@MyOnlineTrainingHub indeed the refreshing all does not work. Thanks
Hello, I'm having problems generating the PP&L Pivot Table from external data source... Firstly, you need to know that I'm using Microsoft 365 for Mac. When I select Choose Connection I do not see my queries that I have created earlier. That said am I missing a step or is it simply because I'm using the Mac version of Excel. Thank you in advance for your assistance. BTW superb template... Cheers
Mac is the issue, sorry. The queries won't run on Excel for Mac...yet. Maybe this personal finance dashboard will suit you better: th-cam.com/video/MRtHNqafufg/w-d-xo.html
Can someone explain why Appending the Transactions and Budget queries at 10m:20s is appropriate? Seems like merging queries would be more appropriate… Or would they both work? Thanks!
With this data you can use either. In the file I made available for download, you'll see I merged them 😉
Thank you for this. I have been watching all sorts of videos on how to do a budget tracker. This version is the best for me and I am already benefiting from it. I just turned 65 and it's about time! The Bonus Savings Tracker that you mention in the beginning is also very attractive. Is that still available? Can you direct me to find it? Thanks again - :)
Glad it was helpful! The savings tracker is included in the file you can download from our site. The link is in the video description.
Hello Tracey, i saw your courses 9n your website. I came across a finane course which iam super exicted to take it. I have a small question. Do by end of this course, will.i be able to do financial statements on excel or this course is just to learn the fundamentals
Thanks for your interest in my courses. Please reach out via email and let me know which course you're referring to: website at MyOnlineTrainingHub.com
Simply Fantastic, Myanda. Thank you for putting this out! Question: can this be "converted" into Google sheets? 🙂
Glad you like it! You can't use this with Google Sheets because Sheets doesn't have Power Query.
Ok, thanks
This tutorial is great! I have a question about sinking funds. I budget for discretionary expenses each month into sinking funds (clothes, vacation, medical, etc). For example, I transfer $300 into a clothing sinking fund each month. If I spend $50 on clothes from my main checking account, I transfer $50 from my clothing account back into my main checking. The remaining $250 stays in the sinking fund. What would the reporting category look like for transactions such as this?
Would it be sub-category "clothes"; category-type "Expense" for the initial $300 and the same for the $50 purchase. Then what does the transfer of $50 look like once it is transferred back into the main checking account?
Assuming you only want to track actual spend in your personal Profit & Loss statement, I would track the $300 debit transfer as a savings sub-category e.g. 'Sinking Fund - Clothing' that is not included in the P&L i.e. Category Type: Balance Sheet. Then when you spend the $50, it is entered as a debit on sub-category 'Clothing' that is included in the P&L and the transfer back from the sinking fund to your checking account is entered as a credit against the Sinking Fund - Clothing sub-category.
@MyOnlineTrainingHub Yes! Thank you! I will try doing it this way.
@MyOnlineTrainingHub I just want to make sure I am setting this up correctly:
1. initial $300: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Balance Sheet
2. spent $50: sub-category "Clothing"; Category "Discretionary"; Category "Expense"
3. transfer $50 from the sinking fund back to checking: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Credit"
I would have the following category types: income, expense, balance sheet, and credit?
How would you take into account 401k and HSA savings as pre-tax deductions from your salary? Do you account for these as an expense even though they are going into two separate savings accounts?
you could enter the gross amount and then add another transaction with the deduction.
Nice
Glad you liked it. Thanks for watching 😊
Hello, would it be possible to make this workbook without pivot tables? I find it cumbersome to refresh data. Thank you kindly for your outstanding efforts.
Sure, you can use formulas 👍
May I ask for help on how to do this please?@@MyOnlineTrainingHub
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@27:33 I noticed there were two Transport values, but later they were combined (211 and -102). Is that because a refresh all was done?
When I demoed using emojis in the categories, I accidently chose a different car to the one I had used for MV Loan that I later copied in, so I ended up with two Transport categories. 🤦♀️ In the file download there is only one though 😉
In your Profit & Loss PivotTable, could you add YTDBudget & Budget (whole year) accordingly?
Maybe. See if this technique will work for you: www.myonlinetraininghub.com/excel-pivottable-calculated-items-by-position
@@MyOnlineTrainingHub
It would be helpful to add the Category in the Transactions table - how do I do this? Reason is for using a slicer on categories and expanding the analysis further.
Sure, you can add a lookup formula (XLOOKUP/VLOOKUP) to find the sub-category in the Categories table and return the category. e.g. =XLOOKUP([@[Sub-category]],TblCategories[Sub-category],TblCategories[Category])
I have already tried that, but that leaves me with the error "[Expression.Error] The field 'Category' already exists in the record". @@MyOnlineTrainingHub
Sounds like you're doing this in Power Query instead of the Table in the Excel worksheet. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Out of curiosity, does your transactions table have a year’s worth of transactions?
When I got to that portion (creating the first pivot chart around 16:43, all my expenses are barely visible because I only have a handful of days in that transaction table…wondering if that’s to be expected
Ha nevermind you answered my question about 15 seconds later 😂
😁 glad I was following the right path to answer your question 😉
awesome video🙏🙏
sorry for the silly or repeat question! is there a google sheet version of this?
No, Google Sheets doesn't have Power Query or an equivalent, so you'd need to use Excel for this spreadsheet. If you don't have Excel, you can try this Personal Finance Dashboard instead: th-cam.com/video/MRtHNqafufg/w-d-xo.html
Ok fair enough ... Makes sense
Thanks you
I'm following on a MacBook and this version of excel does have full power query capabilities. Does anyone have any advice on how to continue without it? Or should I find another tutorial? This one has been so comprehensive so far and I love how mine is turning out, I'd hate to stop now.
Yes, Mac is a bit behind Windows Excel, unfortunately. This personal budget will work for you though: th-cam.com/video/MRtHNqafufg/w-d-xo.html
@@MyOnlineTrainingHub thanks, love you!
Hi could you incorporate debt payoff with the budget? I was thinking with any left over money each month to maybe be debt free one day. Thank you for all of your efforts.
Great idea!
@@MyOnlineTrainingHub I only have the idea but do not know how to make it work. Could you perhaps make a video?
I am having trouble downloading the excel spreadsheet. Do you have n easier link without a 1000 ads on it? Thanks
You're welcome to reach out via email and we can send the file. Note: you can't use the file on a phone (which I presume is where you're trying to download it to given your ads comment). If you go to the link on a PC the ads shouldn't cause you any issues, but by all means reach out via email if it's easier: website at MyOnlineTrainingHub.com
Hi, nothing is changing in the report or analysis when I change the numeric value of a transaction. Could you maybe have an idea why?
You have to click the 'Refresh All' button on the Data tab of the ribbon to update the report.
God send ❤
Glad you'll find it useful 😊
I LOVE the looks of this budget template - however the I use now has a few more things I would love to see added on to this. I'll try to figure out how to combine the 2 but if you at all interested in more ideas, please let me know.
Glad you like it. Feel free to add more features to it for your needs. I won't see follow-up replies here, but if you want to share your ideas via email, you can send them to website at MyOnlineTrainingHub.com
I'm interested to know! What are your additional ideas?
@@markryan4850 The workbook I currently use has a tab for 'accounts' where I list everything - every bank account, credit card, etc.... In the transactions tab I can select how a transaction was paid - by credit card or by bank account. Then I have a record & balance of EVERY account I use in 1 sheet. It also has 2 balance columns, one that I put in everything in including future payments so I know what my balance will be after the bills that are coming due are paid, and another column that doesn't subtract the amount until I mark it as 'reconciled' - this one will show me what my bank account should currently show for a balance so I know I'm on track.