Management Decision Making Microsoft Excel Tips & Techniques

Learn various Tips & Techniques in Performing Projections, Creating Simple Payroll System, Look Up Specific Values from Raw Data etc

Microsoft Excel Tips and Techniques for Management Decision Making

Trainer

Target Audience: Anyone with basic understanding of Excel wanting to know
the finer points in terms of formatting and solving issues on
calculations using the functions and features of Excel.

Learning Outcome: Equipping participants with the relevant skills on performing
projections, applying what-if calculations, creating a simple
payroll system, looking up specific values from raw data, and
performing advanced calculations by combining functions in
Excel.

Pre-requisite: Understanding basic aspects of formatting numbers and
applying functions such as SUM; MAX; MIN; AVERAGE;
COUNT and simple IF statements.

Training Benefits: Participants will be able to understand the use of logical
functions; solve loan calculation problems; forecast future
values for sale projections; and format text data. Participants
will see Excel differently when they are able to master the
various functions taught in this program.

Tips & Technique

Training / Course Outline:

1. USEFUL TIPS, TECHNIQUES, AND FEATURES OF EXCEL

Learn various shortcuts and techniques in formatting
Validate a specific range of cells
Applying Conditional Formatting
Password protect a range of cells and workbook
Applying Filters and Advanced Filter
Performing calculations using Subtotals
Converting text to numbers from external data file
Absolute Cell Referencing (appropriate use of the $ sign in formulas)
Understanding and applying macros for automation of tasks
Changing text orientation
Creating a watermark

2. UNDERSTANDING THE USE OF LOGICAL FUNCTIONS

Applying the IF statement for comparison purposes
Applying the Nested IF statement for more than a single condition

3. SEARCHING SPECIFIC VALUES USING LOOKUP FUNCTION

Applying VLOOKUP and HLOOKUP functions to search specific values from a
database
Calculating bonuses for multi-tier payment scheme

4. DECISION MAKING USING FINANCIAL FUNCTIONS

Performing loan calculations using Straight Line and Reducing Balance
approaches

5. ANALYZING DATA USING PIVOT TABLE

Use of Pivot Table
Updating and modifying Pivot Table
Changing calculated fields in Pivot Table

6. APPLICATION OF TEXT FUNCTIONS

Comparing contents of one cell with another
Replacing specific text with another
Removing unwanted spaces within a cell content
Combining contents of various cells
Finding length of text within a cell

7. DATE CALCULATIONS

Calculating differences of two dates and converting it to years and months
Understanding various date formats and performing calculations accordingly

8. FORECASTING TECHNIQUES

Understanding types of charts for forecasting
Forecasting future values based on previous results (e.g. sales forecasting,
market analysis, etc.)

9. OTHER EXCEL FUNCTIONS

Ranking variables in descending or ascending order
Performing calculations based on a given condition