Excel Course: Master Microsoft Excel 2021 Intermediate Level
Welcome to the ultimate Excel Course, where you’ll truly master Microsoft Excel 2021 at an intermediate level. Whether you’re a clerk, officer, executive, supervisor, or manager, this course is tailored to enhance your Excel skills to an intermediate level. If you’ve already got a basic understanding of Excel, this is your next step towards becoming a spreadsheet pro.
What to Expect in Our Excel Course
In this interactive course, you’ll dive deep into Excel’s functionalities. Learn to create complex formulas and functions, including AVERAGEA, AVERAGEIFS, SUMIF, SUMIFS, IF functions, and even nested functions. Master data validation, sorting, and filtering techniques for more efficient data management. Discover how to visualize data with conditional formatting, data bars, icon sets, and create stunning charts that tell a compelling data story.
Why Choose Our Excel Course
At Info Trek, we’ve been honing our training and development expertise for over two decades. Our commitment to staying at the forefront of technology means you’ll be learning the latest Excel techniques. Join us in our modern training facilities equipped with high-end computing systems and flexible exam facilities. Don’t miss out on the opportunity to excel in Excel. Enroll in our Microsoft Excel 2021 Intermediate course today!
Course Details
Course Code: – Course Duration: 2 Days; Instructor-led
Audience
This is a 2-day hands-on workshop in forecasting and budgeting delivered in Microsoft Excel. Participants learn how to create forecasting and budgeting financial models using Excel.
Being an Excel-intensive class, participants are expected to possess a mid-level working knowledge of MS-Excel. The session teaches participants the Excel and statistics know-how in building a robust budgeting and forecasting financial model.
Financial Models covered are the Budgeting Model, Forecasting Model and the 3-Statement Model. The use of MS-Excel is extensive, and laptops affixed with Excel 2016 (or higher) with Power Query are required. This program is conducted with practical exercises and case studies.
Prerequisites
None
Methodology
Instructor-led follow-along workshop.
Datasets provided
Course Objectives
None
Outlines
Module 1: Financial Models
- Introduction & types of financial models
- Program scope
- Financial model structure
- Budgeting & forecasting
- The budgeting & forecasting process
Module 2: Building the financial model
- Building revenue & expense drivers
- Revenue & cost projection
- Modelling the Income Statement & supporting cost centre sheets
- Building the headcount & payroll model
- Projecting the balance sheet
Module 3: Forecasting methods
- Qualitative and Quantitative forecasting methods
- Naïve method, Simple Moving Average, Simple Weighted Moving Average, Exponential Smoothing, Correlation, Regression Analysis
- Forecast Sheet function (applies to Excel 2016 & above)
- Multiple Regression Analysis, Coefficient Correlation, Coefficient of Determination
- FORECAST, CORREL
Tracking forecasting accuracy as a KPI - Using Excel’s Data Analysis tools- Exponential Smoothing, Moving Average and Regression Analysis
Module 4: Price setting for products & services
- Understanding price and demand curves
- Price elasticity of demand computation methods
- Decisions in price setting
Module 5: Cashflow
- Working capital components, ratios & cash conversion cycle
- Cash flow projection using the direct method
- Free Cash Flow (FCF)
Module 6: Form Controls
- Essential Form Controls & overview
- Incorporating Form Controls into financial models & sensitivity analysis
Module 7: Extracting and consolidating historical accounting data
- Using Power Query to extract & consolidate historical data
- Building forecasting working templates from historical data
- Building sensitivity analysis into forecast templates
- The Analyze Data function
Module 8: NPV & IRR
- Time value of money & discounted cashflows
- Compounding and Discounting- what’s the difference?
- NPV vs IRR introduction
- Implications of NPV vs IRR in decision-making
Module 9: Important Excel knowledge
- Essential Excel functions in financial modeling- VLOOKUP, SUMIF, SUMIFS, IFERROR, INDIRECT, IF, IFS
- Utilize macros to automate simple tasks
Module 10: Managing multiple budget Version
- Version controlling your budget forecast spreadsheets
- Fitting in multiple budgets into your financial model
Module 11: Introduction to business intelligence tools in Excel
- Utilizing Power Query, Power Pivot and the OLAP Pivot Table to extract, transform and stratify historical data
