Course Outline
Initial Skills Assessment and Review
- Evaluation of participant Excel proficiency
- Warm-up exercises focusing on functions and navigation
- Review of essential concepts for structured datasets
Budgeting for Purchases and Report Generation
- Development of basic and dynamic budget templates
- Tracking of costs and purchase quantities
- Creation of comprehensive purchase summary reports
Formulas for Inventory and Cost Calculations
- Utilization of IF, SUMIF, and SUMIFS functions for logic-based analysis
- Application of VLOOKUP for referencing item details
- Combination of formulas to derive cost-per-unit and supplier-level insights
Handling and Analyzing Large Datasets
- Management of datasets with 5,000+ rows and multiple columns
- Sorting, filtering, and conditional formatting techniques
- Use of Excel Tables for dynamic range referencing
Building Purchase Lists and Supplier Reports
- Creation of structured input sheets by supplier
- Linking of inventory, quantity, and cost fields
- Summarization by category or raw material for government operations
Practical Inventory Tracking Exercise
- Simulation of consumption versus purchase (e.g., 1,000 vs. 900 bags of sugar)
- Reconciliation of usage data with purchase logs
- Identification of discrepancies and generation of alerts for government accountability
Pivot Tables for Reporting and Analysis
- Creation of pivot tables by month, item, or supplier
- Filtering, grouping, and custom summarization techniques
- Construction of dashboards with slicers and timelines for enhanced reporting
Automating Reports with Macros
- Recording and editing basic macros for government use
- Automation of repetitive tasks, such as monthly material consolidation
- Execution of macros for data refresh and report generation to streamline processes for government operations
Summary and Next Steps
Requirements
- An understanding of fundamental spreadsheet concepts
- Familiarity with file management and data entry procedures
- Prior experience with business or inventory processes
Audience for Government
- Inventory and purchasing staff
- Operations or logistics analysts
- Finance or administrative personnel
Testimonials (5)
well-prepared training, interesting topics
Maciek Boiski - Instytut Energetyki - Panstwowy Instytut Badawczy
Course - Microsoft Office Excel - poziom podstawowy
I was recently struggling with some pivot table issues and the course gave me the knowledge to work with pivot tables quicker, smarter and more efficiently.
Arina Moayed-Dzenisa - Universal-Investment-Gesellschaft mbH Branch Poland Sp. z o. o.
Course - Microsoft Office Excel - poziom średnio zaawansowany
The tips for many of the functions that the trainer presented, which we can easily remember and implement in our future work
Emilija Stoilova - EPFL HBP PCO
Course - Analysing Financial Data in Excel
The level of Excel knowledge was great followed by the high volume of material covered.
Humphrey Martino - Kneipp Corporation of America
Course - Excel in One Day
The guy was really helpful and knowledgeable and i really learned a lot in two days.
