Take your financial analysis to the next level with Excel's powerful tools! In this video, you'll learn how to master your finances using Excel's Power Query and Power Pivot. Discover how to manage data, transform, and analyze your financials, create stunning reports, and make data-driven decisions. Whether you're a financial analyst, accountant, or business owner, or just trying to take back control of your money, this video will show you how to unlock the full potential of Excel and take control of your finances. Get ready to boost your productivity, reduce errors, and make informed financial decisions with Excel's Power Query and Power Pivot. #excel #pmo #powerpivot #powerquery #personalfinance #dashboard #fyp
Delivered by @Changetipsandtools
So you can follow along the free support file can be found here: drive.google.com/drive/folders/10AyyDW02OkhcFwNpUls7feQfACiyBesD?usp=sharing
If you wish to buy the completed template please follow link to the Change Tips and Tools Patreon: www.patreon.com/ChangeTipsandTools/shop/personal-budget-template-890938?Link&
This Video contains:
00:00 - Intro
00:52 - The Starting Point i.e. the plan for the general layout
01:42 - Budget types Table and creating a named range.
03:38 - Indirect Function reference table ranges (for older versions of Excel)
04:23 - Create our Budget table to plan our annual Income and Expense
08:00 - Create the actuals table to capture our transactions from our bank accounts on a monthly, weekly or daily basis
11:56 - How to get the Power Pivot ribbon activated
12:35 - How to add an Excel table directly to Power Pivot data model (tblBudgetTypes)
13:19 - Move our tblBudget (Excel Table) into Power Query and transform it from using the unpivot function.
15:57 - Move our tblActuals into Power Query and create our actuals data
18:51 - Use Power Query to create our data by appending our Budget and Actuals data together to form one table and then load it to our Data Model in Power Pivot
20:13 - For our Charts, I need to add Year and Month to our new Actuals Power Query table
21:12 - Power Pivot - Add a calculated column for variances in our Actuals data and create our table relationships for our budget types.
22:36 - Power Pivot - Diagram View to create relationships between tables (note these can only be 'one-to-many' relationships in Power Pivot)
24:00 - Create our first Pivot Table and Chart from Power Pivot Data Model (Expense Category Chart).
30:27 - Create our Expenses chart for the Year
34:52 - Add our Income Chart for the Year
38:55 - Add an Actuals vs Budget Pivot Table on our report with visual indicators
46:32 - Dynamic Header - using conditional formatting data bars
57:11 - Create a chart to show our ending balance.
01:04:13 - Create to Optional Data Bars to track two Sub Categories in our Personal Budget Report header.
01:15:54 - Change the colors of your tabs for each sheet.
01:16:33 - Add a slicer to my actuals table for additional data analysis functionality
01:17:16 - Add more Actuals Data to our template
01:17:44 - How to Refresh Data to include new Actuals Data in our Dynamic Dashboard
01:18:11 - Fix the Time (Date) slicer report connections (always good practice to check your work).