CFC1005: Training on Advanced Excel for Sales and Marketing Professionals

Onsite Price

Ksh: 112,000.00

$: 1,120.00

Online Price

Ksh: 64,000.00

$: 640.00

Duration:

5 Days

Description

Day 1: Advanced Data Management

Lesson 1: Data Validation and Conditional Formatting

Creating drop-down lists with Data Validation.
Setting up input messages and error alerts.
Applying Conditional Formatting rules to sales and marketing data.
Using formulas in Conditional Formatting.

Lesson 2: Advanced Filtering and Sorting

Using Advanced Filter to create complex criteria.
Custom sorting options (by color, by custom list).
Using the Subtotal function to summarize filtered data.

Lesson 3: Excel Tables and Structured References

Creating and managing Excel Tables.
Using structured references in formulas.
Leveraging Table features for dynamic data ranges and analysis.

Day 2: Advanced Formulas and Functions

Lesson 4: Lookup and Reference Functions

Using VLOOKUP, HLOOKUP, and XLOOKUP.
Understanding INDEX and MATCH for more flexible lookups.
Applying INDIRECT for dynamic references.
Using OFFSET for creating dynamic ranges.

Lesson 5: Statistical and Forecasting Functions

Using AVERAGE, MEDIAN, MODE for central tendency.
Applying STDEV, VAR for variability analysis.
Using FORECAST, TREND, and LINEST for sales forecasting.
Utilizing CORREL and COVAR for correlation and covariance analysis.

Day 3: Pivot Tables and Dashboards

Lesson 6: Creating and Customizing Pivot Tables

Creating Pivot Tables from sales and marketing data.
Customizing fields and layouts for insightful reports.
Grouping data (by dates, categories).
Using calculated fields and items.

Lesson 7: Pivot Charts and Dashboards

Creating Pivot Charts.
Designing interactive sales and marketing dashboards.
Using slicers and timelines for dynamic data filtering.
Best practices for dashboard design.

Day 4: Power Query and Data Analysis

Lesson 8: Introduction to Power Query

Accessing Power Query in Excel.
Importing data from various sources (Excel, CSV, databases).
Basic data transformations (filtering, sorting, merging).
Loading transformed data into Excel.

Lesson 9: Advanced Data Analysis with Power Query

Combining queries (merge and append).
Creating custom columns and conditional logic.
Grouping data and performing aggregations.
Using Power Query formulas (M language).

Day 5: Automation with Macros and VBA

Lesson 10: Introduction to Macros

Recording basic macros.
Running and editing recorded macros.
Understanding the VBA Editor interface.

Lesson 11: Basic VBA Programming for Sales and Marketing Automation

Writing simple VBA scripts.
Using loops and conditional statements.
Creating user-defined functions (UDFs).
Automating repetitive tasks (data entry, report generation).

Description

Learn to control data entry and highlight key sales and marketing metrics.

Master complex data filtering and sorting techniques.

Understand the benefits of using Excel Tables for sales and marketing data.

Learn to retrieve and reference data efficiently.

Master functions to analyze and forecast sales data.

Summarize and analyze large sales datasets with Pivot Tables.

Visualize sales and marketing data and create interactive dashboards.

Understand Power Query for data extraction and transformation.

Perform advanced data analysis and transformations.

Learn to record and use macros to automate tasks.Automate sales and marketing tasks with basic VBA programming.

General Notes

GENERAL NOTES
i. The clients have many options on where they would wish to have the training
ii. The client should communicate their preferred training venue at least seven days before the training date failure to which the training shall take place in Nairobi.
iii. This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
iii. Training manuals and additional reference materials are provided to the participants.
iv. Upon successful completion of this course, participants will be issued with a Chania finance consultancy certificate.
v. The onsite training fee covers tuition fees, training materials, tea at 10am, lunch, tea at 4pm, training venue and a certificate on completion. Accommodation and travel may be arranged for our participants upon request.
vi. The online training fee covers tuition fees, training materials, and a certificate on completion.