Course Overview

To make sure you have the most efficient and effective way to analyze your sales so that you can spend more time on understanding the numbers and less time on creating the numbers!
The delegate will be able to compare price lists to historical or competitive sources on a product by product basis. Include % movement calculations comparing to budget and last year in sales data. Establish what increase in volume will justify what level of price decrease; Link your Excel tender spreadsheets directly to the Word or PowerPoint tender documents. Highlight your average and below average products or sales people with a click of a button; and easily look up client’s information from different databases to remove duplicate content. 
Software tools enable us to be more productive and efficient. Learn how to leverage the powerful features of Excel for Sales, reporting and other Sales tasks.  Explore the use of Excel as an audit and analysis tool, as well as, selected features that help prepare accurate reports.

Course Methodology

This course is extremely practical with multitude of hands on exercises that Sales Executives, Sales Professionals will learn to apply in routine Sales work and present meaningful information and reports to the management.  

Course Objectives

By completely attending this course, participants will be able to:
  • Accessing Excel Functions so that you can find the functions that will help you in the shortest amount of time.
  • Understanding Excel formulas to know when to use a VLOOKUP to calculate commissions and when to use an IF function.
  • The uses of the IF function for determining regular versus volume pricing and more.
  • Tips and Tricks with Excel for Sales to save time and effort in your day to day excel work.
  • How sales errors happen in spreadsheets in order to avoid them.
  • Consolidating price lists or sales performance in a spreadsheet to save you time with repetitive copy and paste.
  • Comparing sales to historical results to spot trends and opportunities.
  • Add graphics to visualize what your sales information is saying.
  • Running Sensitivities in Excel to see what happens if circumstances changes, e.g. how much volume is required to justify a discount?
  • Cleaning up data received from sales or other systems for analysis and comparison.
  • How to join databases from different sales systems/ price lists together, compare pricing, historical results or product categories.
  • Understand how dates work in Excel to calculate working days between dates to determine sale days.
 

Target Audience

New and Experienced Sales professionals from Sales Engineer, Sales Managers, Direct Sales, Business to Business Sales, Service sales, Territory Representative, Anyone who perform Sales functions in their organizations on daily basis

Target Competencies

  • Reporting, analyzing, and reconciling
  • Data modeling
  • Integration with external data sources
  • Report structuring techniques
  • Automation and macros
  • Charting and visualization techniques
  • Utilizing Excel 2013 / 2016
  • Practicing pivot tables
  • Analyzing Sales data
 

Course Outline

Manage Listing Reports Using Custom Views

  • Create Repetitive Sales Reports
  • Create Customs Views
  • Manage Custom Views
  • Working with New Sales Data
  • Saving Filtering Settings

Pivot Tables and Pivot Charts

  • Working with PivotTables
  • Rearranging PivotTables
  • Formatting PivotTables
  • Using Pivot Charts

Cell and Sales range names

  • Creating and using name
  • Managing names
  • Creating 3-D Formula

Advanced formatting of Sales Data

  • Using special number formats
  • Using functions to format text
  • Working with styles
  • Working with themes
  • Other advanced formatting

Sales Data Validation

  • Validating cell entries: Input message, Error alert,
  • Validation rule
  • Cascade data validation
  • Using data validation with name range
  • List validation
  • Number validation
  • Text validation
  • Date validation
  • Custom validation

Analyze Sales Data for the Last 5 Years

  • Vvalues as percentage of Grand Total
  • Values as percentage of Years Previous
  • Values as percentage of Years 2007
  • Values as percentage Difference from Previous
  • Values as Actual Difference from Previous
  • Values as percentage of Column Total
  • Values as percentage of Row Total
  • Values as Running Total Rank Values
  • Pivot Tables from Multiple Sheets

Functions

  • Logical functions
  • Statistical functions
  • Calculate Sales Commission
  • Calculate Sales Target
  • Financial functions
  • Date and time functions
  • Array formulas
  • Displaying and printing formulas
  • Analyze Invoices Age
  • Sales Opportunities – Create Sales Opportunities
  • Create a Report – Financial Year
  • Create a Report Half Year wise
  • Using lookup functions: VLOOKUP,
  • HLOOKUP, CLOSEST MATCH, EXACT MATCH
  • Using MATCH and INDEX

Advance Sales Charting

  • Chart formatting options
  • Combination charts
  • Waterfall Chart
  • Graphical elements

Exporting & Importing Sales Data

  • Exporting and importing text files
  • Getting external data

Data Tables

  • Sorting and filtering data
  • Advanced filtering
  • Working with tables

Documenting & Auditing

  • Auditing features
  • Protection
  • Workgroup collaboration

What if Sales Analysis

  • Goal Seek
  • Solver
  • Scenario

 

Location & Dates
Call us at +971 4 430 8394