What you’ll learn:

• Multiple Excel techniques to prepare your sales reports and perform analytics
• Dynamic Report Generating, Formula, Functions and using Technique
• Pivot Table
• SUMIFS and Dynamic VLOOKUP for Slabs
• Date Formulas for After Sales Service
• Data Preparation for Analysis
• Data Consolidation
• Visualization without Charts

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.

1. Sales Visualization techniques WITHOUT using Charts Find correct sales by extracting Bank Account Codes/Distributor Codes/Sales person ID from unclean data dumps
2. Analysis Sales by Bucket sizes 0-1000, 1001-2000, and so on& Monthly Sales Achievement Report Daily/Weekly/Monthly.
3. Analysis Product wise Target and achievement.
4. 360 view Report and Identify Low performing Territory/Area/Region.
5. Combine Sales Ledgers of multiple months & Automate the process for next months
6. Analyze 3-years’ sales using Pivot Table. Top Revenue Generating Product Analysis
7. Forecast and trend analysis- Sales, production and distribution
8. Top/Bottom, Average By Performer By Product Analysis
9. Commission Calculation using different incentive periods
10. Allocating sales to different agents
11. Consolidating Sales of multiple products, multiple agents from different sheets

Course Outline:

Session-1: Getting Start with Excel

• Basic Calculation
• Case Change
• Cell Reference: (Relative & Absoulate)
• Joint Text
• Text Apart
• Formula Bar
• Rows, Columns, Cells
• Sheets, Zoom
• Hiding/ Unhiding Columns, Rows
• Wrap & Orientation
• Alignment

Session-2: Data Formation

• Format Painter
• Formatting Cell by Fill and Border
• Formatting Cell by Font
• Cell Merging
• Replace All
• Data Validation
• Find and Replace
• Paste Specials
• Freeze Panes
• Subtotal

Session-3: Data Summarizing:

• Advanced Filtered
• Filter Function
• Color Filtered
• Conditional Formating
• Sum Function
• Consolidation
• Sum product function
• Accending Descending
• Format Table
• Dynamic Slicer In Table
• Data Group/Ungroup

Session-4: Working With Dynamic Formula

• Protect Sheet/ Unprotect Sheet
• Column Function
• Column Arry Function
• Picture Reference
• Using Data Bars, Color, Scales and Icon sets for great visual
• Dynamic Vlookup
• Hlookup
• IF Function
• Autofill Options
• Flash Fill
• Nested IFO with other functions

Session-5: Advance Analysis Tool & Reporting

• Left, Right
• Using Data Bars, Color Scales and icon sets for great
• Use of AND, OR, NOT
• Sumif, Sumifs
• CountIfs, and AverageIfs, Functions
• Pivot Table
• Table, Slicer
• Avarage Value, Top/Bottom Analysis/Top/Bottom
• 3D Sum Function
• Concatenate
• Trim