Advance Excel with MIS

INTRODUCING EXCEL

  • Introduction to MS Excel, Quick review on MS Excel Options, Ribbon and Sheets
  • Excel Overview & Basic calculation.
  • Understanding Workbook and worksheets.
  • Saving Excel File as PDF, CSV and Older versions
  • Using Excel Shortcuts with Full List of Shortcuts
  • Copy, Cut, Paste, Hide, Unhide, and Link the Data in Rows, Columns and Sheet
  • Using Paste Special Options
  • Formatting Cells, Rows, Columns and Sheets
  • Protecting & Un-protecting Cells, Rows, Columns and Sheets with or without Password
  • Page Layout and Printer Properties
  • Inserting Pictures and other objects in Worksheets

EXCEL FUNCTIONS

  • Logical Functions
    • IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
  • Text Functions
    • UPPER, LOWER, PROPER, TRIM, LEN, LEFT, RIGHT, MID, CONCATENATE, FIND, & SEARCH.
  • Date & time Functions
    • DAY, MONTH, YEAR, EOMONTH, EDATE, NETWORKDAYS, TODAY, WEEKDAY, WEEKNUM, WORKDAY, DAYS360 & DATEDIF.
  • Lookup & Reference Functions
    • VLOOKUP, HLOOKUP, MATCH, INDEX, OFFSET, HYPERLINK, CHOOSE, TRANSPOSE, COLUMN & ROW.
  • Math Functions
    • SUM, SUMIF, SUMIFS, ABS, CEILING, FLOOR, COMBIN, ROUND, MOD, QUOTIENT, PRODUCT, SUMPRODUCT & ABS etc.
  • Statistical Functions
    • COUNT, COUNTA, COUNT BLANK, COUNTIF, COUNTIFS, AVERAGE, AVERAGEIF, AVERAGEIFS, MAX, MAXA , LARGE, MIN, SMALL & MEDIAN
  • Information Functions
    • CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE etc.
  • Database Functions
    • DSUM, DMAX, DMIN, DAVERAGE, DCOUNT, DCOUNTA & DGET DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP

Conditional Formatting

  • Using Conditional Formatting with Multiple Cell Rules
  • Using Color Scales and Icon Sets in Conditional Formatting
  • Creating New Rules using formulas and Managing Existing Rules

USEFUL RIBBON COMMANDS

  • Inserting Chart & Dynamic Chart,
  • What if Analysis (Data table, Goal Seek, Scenario Manger).
  • Creating User Form Function using Macro.
  • Data Validation
  • Advance Filter & Sorting
  • Group & Ungroup

PIVOT TABLES& Pivot Charts

  • Creating Pivot Tables
  • Using Pivot Table Options
  • Changing and Updating Data Range
  • Formatting Pivot Table and Making Dynamic Pivot Tables
  • Creating Pivot Charts
  • Types of Pivot Charts and Their Usage
  • Formatting Pivot Charts and Making Dynamic Pivot Charts

PROFESSIONAL REPORT CREATION

  • Employee schedule & time report using advance restrictions.
  • Employees Attendance Performance report.
  • Sales & Stock reports.
  • Shrinkage report sample of sales dashboard.

PAYROLL PROCESSING

  1. ESI ACT- 1948 & PF ACT-1952
  • ESI ACT
  • Eligibility
  • Contribution from both employee & employer
  • Filling (monthly & half yearly)
  • Calculations
  • Filling the form
  • Benefits
  1. GRATUITY ACT-1972 & BONUS ACT-1965
  • Coverage & eligibility
  • Calculations
  • Tax exemptions limits

3. PAYROLL PROCESSING

  • The pay structure
  • Leave management(CL,EL,SL,ML)
  • HRA, DA, conveyance, medical allowance, lunch allowance etc.
  • ESL, PF calculations
  • Calculation of gross, net salary & CTC
  • Preparation of breakup salary, designing CTC and salary breakup and discussion on compensation and benefits.

4. TAX PLANNING FOR SALARIED EMP

  • PT- slab process
  • TDS- (salaried people)
  • Slab process of TDS
  • Calculation of TDS for men, women, senior citizens.
  • Education cess
  • Routine tax planning
  • Tax exemptions under deferent sections.

Professional Reports

  • PF report in excel sheet
  • ESI report in excel sheet
  • Profession tax report