 # MS EXCEL

Excel Data Analysis and Management System
Basic & Advanced Data Analysis using Excel 2019- 45 Hour Course
Introduction to MS Excel 2019-Basic & Advanced Module

Course Fees: 12999/-

Features of MS Excel 2019-Difference with Excel 2016, 2013, 2010 and Excel 2007
• Working on Cell-Importance of F4, Freezing Panes
• Basic Mathematical Application on MS Excel-Sum, Average, Count, StandardDeviation,
Max, Min, Median, Quartile, CountA, Count-Blank,
• Logical Function-IF, AND,OR, Nested Ifs, If-error, Is-black, Is-err, I-seven, Is-logical,
ISNA, Is-number, Is-odd
• Basic Text Functions-Proper, Upper, Lower, Trim, Len, Ceiling, Cell, Combin, Left
• Concatenate Function and & Function-Merging Cells with Formulas
• Math & Trigonometric Formulas
• Advanced Array Formulaes, Complicated Large Formulae creation, Report Automation,
• Conversion of Text into Columns
• Conditional Formatting
• Finding Current Date, Current Time, Age Calculation,Days360, Datedif, Datevalue,
Today, Year, YearFrac
• Page Setting and Form Creation-Hyperlink, Text & Symbol, Page Set up &
Manage View
Data Subtotal, Data Consolidation, Data Validation
• Based on cell values (text length, whole no., dates)
• Based on Formulas
• List Dropdown
• Circle Invalid Data
• Input & Error Messages
• Use of F5 command
• Application of CountIfs, Sumifs, Averageifs
• Combining different spreadsheet using data consolidation
Pivot Table and Pivot Charts & Lookup, H-lookup, Match, Index, Offset
• Introduction & Creation
• Slicer
• TimeLine (if 2019 available)
• Pivot Charts
• Calculated Fields
• Calculated Items
• Group
Database and Other Application Connectivity
• MS Access, Web data and Notepad Connectivity, SQL Query in MS Excel 2016
• File and Folder Directory access
Dashboard Report Automation
Statistical Data Visualization using MS Excel
• Types of Data in Excel – Text, Numbers, Date/Time, Logical (Excel)
• Understanding Data Types in mtcars Dataset (Excel)
• Installing Data Analysis Pack and Calculating Descriptive Statistics (Excel)
• Descriptive Statistics – Central Tendency (Excel)
• Histogram, Box and Whisker Chart, Scatter Diagram in Excel
• Calculate Factorial, Permutations and Combinations (Excel)
• Central Limit Theorem Demonstration (Excel)
• Normal Probability Distribution (Excel)
• Binomial Probability Distribution (Excel)
• Other Distributions Related to Binomial Distribution (Excel)
• Poisson Distribution (Excel)
• Test of Hypothesis using Excel-one sample, two sample z test, t-test, F test, Chi Square,
ANNOVA, Goodness of Fit Test
• Plotting Scatter Plot and Calculating Correlation Coefficient (Excel)
• Correlation Coefficient for Multiple Variables (Excel)
• Regression Related Functions – Slope, Intercept (Excel)
Financial Modeling Using EXCEL
• Learning PV, FV, PMT, NPV, IRR, MIRR, XNPV, XIRR functions
• Loan Amortization Schedule using PMT, IPMT, PPMT, Goal Seek, One Way & Two way
Data Table
• Bond Arithmetic-PRICE, COUPDAYBS, COUPDAYS,COUPDAYSNC,COUPNCD,
COUPNUM,COUPPCD
• LPP Problem by using SOLVER
• Monte Carlo Simulation-Determination of Pie-Stock Analysis
• Technical Analysis on Stocks-MACD, RSI,MFI, William %R, Stochastic Oscillator, ADX
etc
• Portfolio Management by using EXCEL- Portfolio Optimization by using SOLVER, Beta
Calculation