Advanced Excel for Sales Managers | Automation & Analytics

Name: Advanced Excel for Sales Managers | Automation & Analytics
Rating: 4.6 (64 reviews)

This course is NOT for Beginners.

Created byYoda Learning

Last updated 5/2020

English

What you'll learn

Multiple Excel techniques to prepare your sales reports and perform analytics
Pivot Table
SUMIFS and VLOOKUP for Slabs
Date Formulas for After Sales Service
Data Preparation for Analysis
Data Consolidation
Visualization without Charts

Course content

1 section • 10 lectures • 1h 2m total length

Sales Visualization - without Charts4:42
In this lesson, you will learn Visualization of Sales Data.
Techniques Used in this lesson are:
Conditional Formatting - Data Bars, Heat Map
Format Cells ;;;
Trend Sparkline
REPT()
Sales Data Consolidation5:41
In this lesson, you will learn how sales made by different sales personnel across various product categories and months.
Techniques Used in this lesson are:
Consolidate
Text to Columns
Go To Special - Visible Cells ( Alt ; )
After Sales Support - TAT4:07
In this lesson, you will learn Ticket Resolution TAT - Deadline and Back-Testing of historical data.
Techniques Used in this lesson are:
WORKDAY.INTL
NETWORKDAYS.INTL
After Sales Support - Warranty Date Expiry1:53
In this lesson, you will learn to calculate Warranty Expires after say 3m, 6m, 12m.
Techniques Used in this lesson is EDATE.
Sales Allocation by Sales Person (PQ)8:36
In this lesson, you will learn Single Service Project = Multiple team members and to Divide the fees equally amongst all.
Techniques Used in this lesson are:
Power Query
COUNTIF
Pivot Table
Pivot 1 - 3y Sales - Append Files4:37
in this lesson, you will learn 3 years' sales data in 3 respective files of a folder and also also Append to bring all 3 in 1 file.

Techniques Used in this lesson are:
Power Query (in-built in O365 and 2016; Add-in to download for 2010-2013)
Google - Download Power Query
Pivot 2 - 3y Sales - Analysis11:10
In this lesson, you will learn 3 years sales data using Pivot Table.

Techniques Used in this lesson is Pivot Table
Commission Calculation - Date Buckets10:12
In this lesson, you will learn to calculate SALE made on:
Green Dates = Commission (Bonus)
Red Dates = No Commisssion (No Bonus)
Techniques Used in this lesson are:
SUMIFS = Multiple criteria
SUMIFS = Mathematical Operator for "between" calculation i.e. ">="&D3
VLOOKUP = Using TRUE for Slabs based calculation
$ = To make the formula dynamic
Sales Analysis by Ticket Size3:14
In this lesson, you will learn to Divide Sales Transactions in multiple buckets,
Eg - 0-100, 101-200, 201-300 etc.
Techniques Used in this lesson are:
Pivot Table - Grouping
Chart
Region-Wise Bank Accounts for Sales Collection7:58
In this lesson, you will learn to Consolidated Report of Cash Collection by Region and Each region has multiple Bank A/c.
Techniques Used in this lesson are:
FlashFill
SUBSTITUTE
Paste Special - Value
Text to Columns
VLOOKUP
Pivot Table
Chart (may be)

Requirements

PC with Installed Microsoft Excel 2007 or Excel 2010, Excel 2013, Excel 2016, Excel 2019.

Description

You can improve ONLY if you can measure. This course uses multiple mini-projects to teach you how to work with multiple Excel techniques to prepare your sales reports and perform analytics.

Sales Visualization techniques WITHOUT using Charts
Find correct sales by extracting Bank Account Codes from unclean data dumps
Analysis Sales by Bucket sizes 0-1000, 1001-2000, and so on
Combine Sales Ledgers of multiple months & Automate the process for next months
Analyze 3-years' sales using Pivot Table
Commission Calculation using different incentive periods
Allocating sales to different agents
Consolidating Sales of multiple products, multiple agents from different sheets
After Sales Service Tracking - Contract Expiry Date, TAT & Response Times

Who this course is for:

Intermediate to Advanced Excel User