This is the fifth video of this training series where we will build the dynamic visuals on our Resource dashboard, learning more new tricks through this process of visualizing data with a speedo dial for example🚀
So welcome to this brand new Excel training series on Resource Capacity Planning. In this series of tutorials, I will be showcasing how to create a resource capacity planning template using Excel tables, Power Query, Power Pivot, and VBA.
Resource capacity planning is crucial for businesses to effectively manage their resources and ensure that projects are completed on time and within budget. By utilizing Excel's powerful tools, you can easily visualize and analyze your resource capacity in a dynamic way. I will walk you through step-by-step on how to set up the template, maintain and manipulate data using Power Query, create interactive visualizations with Power Pivot, and even add some automation using VBA. Don't miss out on this comprehensive Excel training series that will enhance your skills and make resource capacity planning a breeze.
Delivered by @Changetipsandtools
Please check out the demonstration video of the Resource Capacity Planning Template:
[ Ссылка ]
The Resource Capacity Planning file for Part 5 - Summary sheet and Speedo dial in this video, can be found here for reference:
[ Ссылка ]
If you can't wait until the end of these tutorials, you can find the completed template on the Change Tips and Tools Patreon:
[ Ссылка ]
Resource Capacity Planning Playlist: [ Ссылка ]
This Video includes:
00:00 - Intro
01:11 - Overview of what we will cover in this video
02:24 - Housekeeping, ensure pivot tables have been named for our Summary sheet
03:09 - Amend font in Availability pivot to show negative values in red
04:27 - Edit Slicers report connections to drive our dynamic visuals
08:08 - Resize and arrange charts created in previous video
10:24 - Arrange and resize slicers and row heights on Summary sheet
11:41 - Create the Availability Chart
16:58 - Create the Utilization Chart
21:54 - Create the Demand vs Capacity (hrs) totals summary chart
26:32 - Create the summary Demand vs Capacity (costs) totals summary chart
30:29 - Add headers for our summary charts using text boxes
33:41 - Format the header (top row) of the Summary sheet
34:44 - Insert and Icon or Graphic for our Summary sheet header
36:05 - Build the support data for our KPI Speedo Dial on our Summary Sheet
48:03 - Build the actual Speedo Dial chart and utilize Excel's linked picture functionality
01:03:18 - Create KPI Goal data and labels for utilization targets on Summary sheet
01:08:27 - Add KPI status by skillset pivot table to Summary sheet
01:11:57 - Link new KPI Status by skillset pivot table to slicers on Summary sheet
01:12:47 - Summary sheet formatting, amend column widths
01:13:48 - What we will cover in the next video
#excel #ResourceCapacityPlanning #ExcelTables #PowerQuery #PowerPivot #VBA #ExcelTraining #DynamicVisuals #ResourceManagement #DataAnalysis #DataVisualization #BusinessIntelligence #ExcelTips #Productivity #DataModelling #ExcelTemplates #MicrosoftExcel #DataAnalytics #SpreadsheetSkills #TechTraining #pmo #fyp #pmooffice #projectmanagement #xlookup #speedodial
