Utilizing Microsoft Excel in Sales & Marketing for Data Analysis

Course Introduction

To be a successful marketer, one needs to have vital information and how to put together this information in a creative manner, in order to achieve the set objectives. Marketers need to be resourceful and understand the tools that are at their disposal. One such tool is Microsoft Excel. As marketers, the primary objective is to continuously keep existing customers while trying to build new ones. This is where Excel can be used in each step of the marketing process by segmenting, targeting and positioning the products.
Participants will be taught how Excel can help them in the areas of Marketing Management, Product Management, and Marketing Communications. Some of the areas included are forecasting and calculating average growth rates using Goal Seek; calculating the difference of given dates; analyzing databases with Databases functions; finding specific results using Lookup functions; scanning a segmented list with specific characters; calculating growth rates; and finally segmenting, targeting, and positioning with Pivot Tables.

  • Anyone involved in areas of sales and marketing and wanting to put theory into practice by identifying the marketing management process data and analyzing it using Excel’s built-in tools and techniques.

Equipping participants with the necessary knowledge and skills of doing the jobs of marketing management.

• Presentation/lecture/data sharing
• Case study
• Personal interaction
• Practical Assessments
• Digital statistic and recommendation

Formatting Data and Numbers
• Customizing display of numbers
• Creating drop down lists using Data Validation
• Filling up a Series
• Adding Subtotals
• Using Fixed values in calculations (i.e. using the $ sign in formulas)
• Adding data within Data Lists
• Customizing Excel Options

Compiling & Analyzing Survey Data
• Creating a random list
• Cleaning up the database using Filter and Advanced Filter options
• Finding specific records using Custom Filter options
• Extracting filtered records

Product Life Cycle Analysis
• Targeting profit margins from data list
• Highlighting specific data using Conditional Formatting
• Applying formulas within Conditional Formatting

Segmenting and Targeting the Market
• Using Excel wildcards to filter details
• Usage of SUMIF, AVERAGEIF and COUNTIF functions in single condition scenarios
• Application of SUMIFS, AVERAGEIFS, and COUNTIFS functions in multiple conditions scenarios

Disciplining Data
• Importing a text file
• Looking up specific values within imported text file
• When to use VLOOKUP and HLOOKUP functions
• Dealing with missing values
• Using Error function to insert user defined error message
• Combining MATCH and INDEX functions to retrieve intersection point of row and column
• Dealing with Dates
• Combining data from multiple cells
• Extracting specific characters from a data cell using Text functions

Competitive Analysis – Moving from Data to Information
• Summarizing data using Pivot Table
• Changing the look of Pivot Table
• Applying different functions within Pivot Table
• Using Pivot Table to generate unique Customer List

Presenting and Reporting the Data
• Understanding the numerous charts used
• Creating the following charts:
• Gantt Chart
• Histogram Chart
• Combining multiple charts within single Chart area
• Using Bar chart for comparison purposes

The Marketing Mix
• Applying decision making functions e.g. IF, AND, OR, NOT
• Predicting future values based on past records of sales figures
• Comparing preset objectives and goals using Scenario Manager
• Performing “What-If” analysis using Goal Seek
• One and Two Input Data Tables