BUSINESS ANALYTICS – ADVANCED EXCEL FOR MANAGERS

“ The goal is to turn data into information, and

information into insight”
- Carly Fiorina, Former CEO of HP

Excelarating Growth
 FOREVISION
Business Analytics – Advanced Excel for Managers
ForeVision Corporate Profile

About ForeVision
❑ Skills Enhancement Training & Consulting Organization
❑ Enhancing Performance by Developing Competencies
❑ Hands-on, Practical & Activity Based Learning with Industry Exposure
❑ Trainer Pool of Working Professionals from Leading Organizations & Top Institutes
❑ Customized Trainings with Online Support
❑ ForeVision Training Centers across Mumbai – Dadar, Bandra & Andheri
❑ High quality Learning experience with 80% repeat customers

Few of Our Clients

8000+ 250+ 100k + 4.5+ / 5

Happy Candidates Projects Completed Hours Trained Avg Feedback Rating

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Case study based approach and hands-on experience

Course Module & Features Excel Overview

• Excel Overview & Basic Excel tools • Excel is one of the most widely used tool in
• Basic & Advanced Excel Formulae corporates across sectors and across
• Nested & Dynamic Formulas functions
• Charts – Basic, Multi Axis, Advanced & Dynamic • It has always been user friendly and feature
• Pivot Table, Pivot Charts & Pivot Dashboards rich and now has been expanded to provide
• Business Simulations – Sensitivity Analysis / an even wider array of tools helping
Scenario Manager / Solver businesses right from simple work to
• Management Dashboards complex problem solving and decision
• Macro Recording, Macro Objects & VBA Coding making
• Shortcut techniques for easy working • Excel has become an integral part of
• Case Study based extensive hands on learning everyday operations, making it a necessity for
• Wide range of managerial examples continuous flow of business

Why Excel?
• Corporates feel that a proper understanding of an Analytical tool like Excel helps in
better Conceptualization of Business Problems and helps one to provide better solutions to
companies
• Corporates also believe that they will Save a lot of time, cost and energy if new recruits are well
versed with Excel
• In an online B-School Survey we found that only 15% of the students were confident about Excel
Skills
• Students felt that a hands on experience in Excel is a must and it is very important to know the
various features that a tool like Excel can provide and how the same can be used to provide
effective and efficient solutions to Business Problems

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Training workshop full of practical case studies, exercises & live
demos for maximum benefit

Module 1 – Excel Overview, Basic Excel Tools Module 4 - Visualization Charts, Pivot Tables
• Introduction to Excel - Menu commands / Working
& Management Dashboards
with worksheets / Formatting functions • Creating Chart and Graphs - Bar Charts, Line Charts,
• Cell Referencing - Relative / Absolute / Mixed Scatter Charts, etc.
• Conditional Formatting - Basic rules / Advanced • Designing charts / Modifying chart options
Formula based rules for different applications / • Special Charts – Candle Stick / Bubble / Waterfall /
Managing rules / Data Bars / Colour Scales / Icon Sets
Gantt / Radar / Pareto
• Paste special - Basic / Operations
• Customized Chart options for creative visualization
• Data Sort - Vertical Sort & Horizontal Sort / Multiple
• Pivot Tables - Creating & updating Pivot Tables /
Sorting
• Data Filter - Basic & Multiple filter / Custom Auto Performing Calculation / Grouping options / Value
Filter / Advanced Filter Field Settings / Sorting & Filters in pivot tables
• Go To Special - Basic & Advanced Application • Pivot Charts / Slicer / Timelines / Linking Multiple
Pivot Tables
• Dynamic graphs & Management Dashboards

Module 2 - Basic & Advanced Formulae Module 5 - Automation - Macros & VBA
• Basic Formulae & Logical Functions – IF / AND / OR • Introduction to Macros & Visual Basic for Applications
• Nesting of Formulas – NESTED IF • Recording Macros - Automating basic functions
• Numeric & Statistical Functions – COUNT / COUNTA / through simple macros / Application of recording
SUMIF / SUMIFS / CORRELATION / STD DEV / macros / How to record macros & view recorded
VARIANCE / CAGR code
• Finance Functions – PMT / NPV / IRR / XNPV / XIRR • Running Macros / Editing the recorded Macro
• Lookup & Reference Functions – VLOOKUP / • Assigning macro to a button or object / macro
HLOOKUP shortcut
• Dynamic Lookups - MATCH & INDEX • Basics of The Visual Basic Editor
• Date & Text Functions – EDATE / EOMONTH / • Writing codes in VBA
UPPER/CONC • Keying & Fetching Critical Data from Database
• Miscellaneous Functions - IFERROR, RANK, etc • Looping - The concept of looping / Types of loops -
• Trace Precedents & Dependents / Formula Evaluation If…End If / For… Next
• Name Manager & Application of defining names • Examples of Application based advanced VBA coding

Module 3 - Advanced Excel Tools &

Business Simulation
• Data Validation – Creating dynamic Data
• What if Analysis - Goal Seek / Sensitivity Analysis
• Scenario Manager
• Solver
• Protection of worksheet & workbook with exceptions
• Other Tools - Freeze panes, Split worksheet,
advanced printing setup

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

CONDITIONAL FORMATTING

Use Conditional Formatting to easily Group Data for better analysis and
data management

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

FORMULAE – BASICS TO ADVANCED

Absolute & Mixed Referencing to

IF & NESTED IF
ease calculations

Employee Name Salary p.m. Grade

=IF(I6>100000,$I$15,IF
(I6>50000,$I$14,$I$13
Salman 45000 ))

Date Formulae VLOOKUP – Simple to Complex

PROJECT FINANCING
Start of construction 01-10-15 01-10-15
Construction Period
18 18
(months)
Project Completion
31-03-17 =EDATE(U5,U6)
Date
Rupee Term Loan -
01-10-15 =U5
Start Date
Tenor (years) 7 7
Rupee Term Loan -
30-09-22 =EOMONTH(U8,U9*12)
End Date

Using Formulae for Basic to Advanced Operations and eliminating

human error. Thus improving efficiency and effectiveness

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

FORMULAE – NEXT LEVEL

INDEX & MATCH

Rank Company Name

=INDEX($A$42:$F$71,
24 MATCH(H49,$F$42:$F
$71,0),2)
19 BHARTI ARTL
3 SUN PHARMA.
28 STERLITE IN

Complex Formulae for Easy Solutions

Using Formulae for Basic to Advanced Operations and eliminating

human error. Thus improving efficiency and effectiveness

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

CHARTS – BASIC TO CREATIVE

2 Axis Combo Charts

1,500 60,000

1,000 40,000

500 20,000

0 Sales Quantity 0
Sales (Rs. Crore)

Technical
Knowledg
e
5
Meeting Punctualit
Deadlines 0 y

Problem Team
Solving
Ashish Player
Ramesh
Suresh

Business
Presentations is all
about Charts. Stand
out by making
creative charts

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

PIVOT TABLES

Create Instant Dashboard for Data Analytics using Pivot Tables.

(P.S. – Data currently represents performance of August month)

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

CREATING MANAGEMENT DASHBOARDS

Create Dashboards for Higher Management - make analysing data

flexible to make informed business decisions
Build Business Scenarios instantly using Data Tables.

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

BUSINESS SIMULATIONS

Business simulations using Scenario Manager and Analysis Toolpak

Solver.

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Preview

AUTOMATION USING EXCEL – MACROS AND VBA

Sub taxcalc()
Dim income As Double
Dim tax As Double
income = Sheets(“Sheet4").Range(“A13").Value
Select Case income
Case Is <= 250000
tax = 0
Case Is <= 500000
tax = 0.05 * (income - 250000)
Case is <=1000000
tax = 0.05 * 250000 + 0.2* (income - 500000)
Case Else
tax = 0.1 * 250000 + 0.2 * 500000 + 0.3 *
(income – 1000000)
End Select
Sheets(“Sheet4").Range(“B13").Value = tax
End Sub

Invoice Date SalesRepProduct Price Units Sales(cr)

10500 01/05/2012 Joe Majestic 30 25 750
10501 01/05/2012 Moe Majestic 30 9 270
10501 01/05/2012 Moe Quad 32 21 672
10501 02/05/2012 Moe Alpine 22 7 154
10501 02/05/2012 Moe Carlota 25 11 275
10502 02/05/2012 Moe Majestic 30 5 150
10502 02/05/2012 Moe Carlota 25 25 625

Automate Mundane tasks by Recording Macros for Scorecard and MIS

preparation by integrating with backend systems along with basic level
VBA coding

www.forevision.co.in
info@forevision.co.in
 FOREVISION
Business Analytics – Advanced Excel for Managers
Advantage & Testimonials

Customized Courses created

by Subject Matter Experts
High Impact Hands
on learning
24 x 7 Online experience
Support

Real time practical

examples / case-
studies
Access to Digital
Resources
ADVANTAGE Trainer pool of
certified industry
experts
Smart Sheets for
Quick Learning
Blended & Flexible
Learning – Offline +
Industry Recognized Online
Certification Courses

Testimonials
• “Kudos, keep up the good work, loved the session by sir. He has a very simple and student friendly
way of teaching” – Saurabh Dhaniwal, NMIMS
• “The workshop was very helpful as it covered concepts from the basics upto the advanced level”.
– Harishma, IIM Trichy
• “It was a good workshop and the delivery was even better !“ - Adil Jain, SIMSREE
• “The workshop was very useful and gave us a lot of information. A lot of topics were covered and
the trainers were very helpful” – Jaydeep, SJMSOM
• “Cost effective and interesting session.Got to learn a lot” – Ankita, Amity
• "Practical approach to Microsoft Excel. Brought the theory alive with practical examples and
applications“ - Akshay Jain, Sr. Analyst, Sushil Finance
• “The faculty was very good. He was able to relate each concept to a real life situation. Would like
to hear him again” - Aditya Khema, WeSchool
• "Good workshop to understand Excel with good number of examples. Examples were well
covered, helps to understand the concept better“ - Priyanka Sharma, JBIMS

www.forevision.co.in
info@forevision.co.in