- 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
- 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
- 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.
- ESI ACT- 1948 & PF ACT-1952
- ESI ACT
- Contribution from both employee & employer
- Filling (monthly & half yearly)
- Filling the form
- GRATUITY ACT-1972 & BONUS ACT-1965
- Coverage & eligibility
- 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.
- PF report in excel sheet
- ESI report in excel sheet
- Profession tax report