fbpx

Financial Excel Course

Financial Excel Course2020-07-29T11:01:48+00:00

Project Description

Course Plan

Microsoft Office

Total Course Duration: 42 Hours
Per Class Duration: 3 Hours

Instructor Profile

Financial Excel Course Part-1

Course Overview

Demystify some of the most complex business relevant areas of Microsoft Excel.

Objective of this Training

Microsoft Excel is the most commonly used for all sorts of analysis in the present world. In job field like Finance, Accounts, Risk, Credit, Sales, Operations, HR, Compliance, Reporting, MIS, Product, Research etc. demand good command on Excel. In this training, you will receive a healthy understandings of the Business Intelligence concept and of the course be skilled in MS Excel. The training will also maximize your skill with Microsoft Excel.

As the training progresses you will be introduced with Powerful Technics that will take you from Novice to Professional. You will explore too many uncovered area of MS Excel that will help you to become more productive from day the day.

Methodology

PRACTICAL EXAMPLES, EXERCISES & CASE STUDIES WITH Q&A DISCUSSION SESSIONS

The course is designed for practical Excel applications at participant’s works rather than theoretical notes. Participants will go through with many examples that they can relate to their works.

Basic Excel:

  • Getting Started With Microsoft Excel 2013
  • Performing Calculations
    • Excel Formulas, Formula Bar, Elements of an Excel Formula
    • Common Mathematical Operators, The Order of Operations
    • Functions & Function Library, Common Functions in Excel
    • Formula AutoComplete, Cut, Copy, Paste, Move, Special Paste Option
  • Modifying a Worksheet
    • Undo & Redo Command, AutoFill Features, Transpose Option
    • Live Preview & The Clear Button
    • Insert, Delete, Hide, Unhide — Row, Column
    • Column Width, Row Height Alternation Method
    • Cell Names & Range Names, Find & Replace Command
  • Formatting a Worksheet
    • Modify Fonts (Font Dialog Box), Format Painter, Galleries, Live Preview, Mini Toolbar
    • Border, Sheet Background, Number Format
    • Alignment, Indent, Wrap Text, Orientation, Merge & Center, Cell Styles

Cell Reference (Absolute Vs Relative):

  • What is relative & absolute cell reference?
  • Different types of use of references (A4, $A4, A$4, $A$4)
  • Managing cell reference with complex formula in easy rule.

Use Essential Functions for Restructuring Usable Data Table

  • Joining multiple cells
  • Extracting the required portion of a cell value
  • Removing unwanted extra spaces
  • Convert a cell text to your required case
  • Making a function as a variable
  • Converting a Text number to a computable pure number or date
  • Rounding a cell value in different ways
  • Join a text with formatted number.

Functions (Text and String)

  • Convert texts to UPPER, LOWER or PROPER case
  • Join multiple cell values with CONCATENATE & Break or extract a portion of text with LEFT, MID & RIGHT function
  • Converting text to numbers quickly
  • Finding total number of characters in a text
  • LEN, TEXT & TRIM

Logical Functions and Operators

  • IF() Condition and expression of logical test
  • Use of AND, OR and NOT
  • Nested IF() with other functions

Database Functions:

  • The database functions perform basic calculations, such as sum, average, count, etc. but they also use criteria arguments that allow you to only perform the calculation for a specified subset of the records in your database. Other records in the database are ignored.

Naming in Excel

  • Examples of various types of name
  • Name type-Cell reference, range of cells, formula and constant value
  • Use name in a formula and avoid complexity

Financial Excel Course Part-2

Outcome:

After completion of the session participants will be able to:

  • Do some analytical activities through excel
  • Use various methods for data analysis in excel
  • Use different formulas, logic for various purposes
  • Reduce work time up to 40% in excel
  • Analyze data for strategic decision making
  • Present report and data in a professional manner
  • Create and maintain database for generating different report
  • Use excel functions in different database

 Who will be benefited?

  • Persons from any department and any level who are using excel

Error Handling and Cleaning Reports

  • Type of errors and when it occurs
  • Circular reference
  • Error handling using ISERROR & IFERROR function
  • Magic tips to find error cells and cleaning
  • Formula tracing and debugging techniques

Data Linking Techniques

  • Introduction of data linking
  • Data linking between multiple worksheets
  • Data linking between multiple workbooks
  • Managing linking and updating
  • Difference between direct and indirect linking

Data Consolidation from Multiple Data Range

  • Data Consolidation Using 3-D References
  • Data Consolidation Using “Data Consolidate”

Describe Your Data Using PivotTables, PivotChart and Slicers

  • Data mapping to prepare a PivotTable
  • Understanding PivotTable Field List
  • Row Labels, column labels, values area, report filter
  • Examples of various types of PivotTable in different angles
  • Customizing subtotal at any row label data and subtotal category
  • Changing PivotTable data source
  • Inserting a calculated field and calculated item
  • Sorting and filtering techniques in PivotTable data
  • What is slicer in PivotTable?
  • Creating, changing and formatting slicer
  • Make your PivotTable dynamic using slicer
  • Creating PivotChart from existing workbook data

Convert Number in Words Automatically (Condition Apply)

Procedure to convert a number into words

Functions (Finance)

  • Calculate Future value of an investment with FV function
  • Calculate Present value of an investment with PV function
  • Calculate EMI or Periodic Payment with PMT function
  • Net Present Value with NPV function
  • Calculate Interest Rate per period (RATE)
  • Calculate Number of payment periods (NPER)
  • Calculates the Interest part of the regular scheduled payment (IPMT)
  • Calculates the Principal part of the regular scheduled payment (PPMT)

Lookup & References

  • Type a Product ID, Excel will return its description automatically matching Product ID from a defined range
  • Vertical Lookup function- VLOOKUP
  • Horizontal Lookup function- HLOOKUP
  • Complex uses of VLOOKUP/HLOOKUP
  • 2-way lookups- auto matching lookup value and column heading
  • MATCH function and INDEX function
  • Combination of MATCH and INDEX function

Date Functions:

  • Do you know how many mistakes you are doing every day for date??
  • Separating DAY, MONTH and YEAR from a complete date
  • Date formatting using formula
  • Presenting day name and month name of a date
  • Combining date with a text (Report Date: 31/Dec/2016)
  • Completion a date from separate DAY, MONTH and YEAR columns
  • Calculating future date adding 3 years 8 months and 18 days
  • Calculating date difference between two dates (Difference of Days, Months and Years)
  • Preparing age calculator (Example: Your age is 25 Years 05 Months and 18 Days)

Bullet-Proofing Your Excel Workbook or Report (Protection & Security)

  • Protect your workbook so that it can be used by anyone with a limited use of Excel
  • Locking and protecting cells
  • Restriction incorrect data entry with data validations
  • Creating list with static values or a dynamic range
  • Creating error validation messages
  • Create your Workbook password protected
  • Hide formulas inside cells from others

Decorate Your Report Using Advanced Conditional Formatting

  • Format automatically when your required criteria meet true
  • Using multiple Conditional Formatting in a range
  • Using Data Bars, color scales and icon sets for great presentation
  • Advanced formula based Conditional Formatting

Advanced Data Sorting & Filtering

  • Multi-level data Sorting using multiple conditions in different angles
  • Finding unique records in a range and paste at another place
  • Removing Duplicates at the same range
  • Data filtering using multiple conditions
  • Multi-level data filtering with customized conditions
  • Customized filtering using IF () condition

Data Summarizing Techniques from Large Data

  • Data summarizing from a large dataset using functions
  • Basic & advanced SUMPRODUCT function
  • COUNT,COUNTIF,COUNTIFS, SUMIFS and AVERAGEIFS functions
  • Use these functions with multiple criteria and conditions

Creating Chart and Visual Presentation

  • Data visualization using Sparkline’s
  • Choosing the perfect chart for your data
  • Creating various types of Charts-Column, Bar, Pie, Line etc.
  • Customize your chart’s labels, axes and background
  • Use different chart type for different series within a chart

Business Modeling Using What-If Analysis

  • The GOAL SEEK command
  • Sensitivity Analysis with DATA TABLE
  • Business projection using SOLVER
  • Using SCENARIO manager

Macros (Basic): Automate Your Excel Action

  • Overview of macro in Excel step by step
  • Create your first macro without having any programming knowledge
  • Automation techniques using macro
  • Creating macro to generate report using criteria
  • Carefulness of macros!

Exporting Excel Data.

  • Export Excel Data. (XML, HTML, TXT, CSV, PDF, XPS)
  • Import a Delimited Text File.
  • Integrate Excel Data with the Web.

Tips and Tricks

  • Format Painter, Wrap Text, Merge & Center
  • Printing Excel & Print Titles
  • Transposing Tricks, Scaling, Gridlines
  • Show Negative numbers in Red Color and Inside Brackets
  • Headlines, Background adding
  • Name Manager, Copying Sheets, Freezing Panes
  • Function: COUNTA, MAX, MIN, AVERAGE, ROUND, ROUNDUP, ROUNDDOWN, TRUE, FALSE …………
  • Function: WEEKDAY, NOW, TODAY, PERCENTILE, STDEV, RANK, GROWTH …………
  • Problem Solving Session
  • Share your Excel problem
  • The facilitator will share some critical real cases
  • Group work with brainstorming

Course Resource Person

Mohammed Shihab Uddin
Mohammed Shihab UddinSr. Technical Trainer (Programming)