MS Excel Intermediate Level

MS Excel - Intermediate Level (Duration: 5 hours)

Objective: To equip trainees to be able to develop and manage Databases effectively in MS Excel.

Course Content:

• Database guidelines
• Date functions (WEEKNO, MONTH, YEAR, TODAY)
• Paste Special options in Excel
• Naming cell range(s) in a worksheet
• Named ranges within formulas
• Creating and Removing subtotals
• Organizing and Analysing related data with Table tool
• Conditional formatting
• Creating Custom number formats
• Sorting internal Excel databases
• Creating own lists for sorts and fill sequences
• Using AutoFilter to query data
• Preventing and Removing Duplicates
• Linking Excel into a Word document
• Linking a chart from Excel into a Word document
• Working with Sparklines (charts in a single cell)
• 3D Consolidation of sheets

MS Excel Advanced Level

MS Excel - Advanced Level (Duration: 8hours)

Objective: To equip trainees to be able to design and develop systems and templates in MS Excel to automate certain
manual and repetitive processes.

Course Content:

• System Design and Development in Excel (for Sales, Operations, Stocks data etc.)
• Array Formulas (TRANSPOSE …)
• Logical functions (IF, AND, OR…)
• Summary functions (SUMIF, SUMIFS, COUNTA, COUNTIF…)
• Reference functions (VLOOKUP, MATCH & INDEX …)
• Text functions (PROPER, UPPER, CONCATENATE, LEFT…)
• Data Validation to eliminate errors when entering data
• Advanced Filtering
• Analysing large data with Pivot Tables and Pivot Charts
• More complex Conditional formatting
• Preventing wrong date formats
• Cell Locking and sheet Protection to minimize errors
• Automating regular tasks by recording and replaying Excel Macros

Financial Modeling in MS Excel

Financial Modeling in MS Excel (Duration: 6 hours)

Objective: To equip participants with relevant methods and techniques for building Financial Models and carrying out What-if
analysis in MS Excel.

Course Content:

• Building Financial Models
• Financial functions (FV, PV, NPV, PMT…)
• Using Goal Seek to -
  • - Investment decision-making
  • - Basic/Gross Salary determination given Net Salary
  • - Gross amount determination given Amount Payable
  • - Pricing decision-making etc.
•  Data Tables
  • - Single-Input Data Table
  • - Double-Input Data Table
• Using Scenario Manager for -
  • - Efficient and effective Budget Reviews
  • - Flexible Budgeting etc.
• Using Solver for -
  • - Optimal Product or Service Mix (i.e. Profit Maximization,
  • - Cost Minimization or Target Profit) given certain constraints
  • - Budget optimization and to determine if given targets are achievable

Excel Dashboard Development

Excel Dashboard Development (Duration: 6 hours)

Course Objective:
To train participants to design and build relevant dashboards to visually display Key Performance Indicators (KPIs) and trends in their organization; interactive and organized on a single sheet or screen so the information can be tracked at a glance to enhance management decision-making.

Course Requirements:

Each participant must already have at least an Intermediate level of knowledge of MS Excel.
Areas to be covered and applied are Intermediate & Advanced level topics including:

• Planning the Excel Dashboard Design
• Tables & PivotTables for Excel Dashboard Data Analysis
• PivotTables Summarize Values By calculations
• PivotTables Show Values As calculations
• GETPIVOTDATA function for Excel Dashboard Development
• Creating Sparklines (mini charts in a single cells) from Pivot Tables
• PivotTables Slicers (Visual filters)
• Connecting Slicers to PivotTable Reports to make the Excel Dashboard interactive
• Using Macros to enhance the interactivity of Excel Dashboards for presentation
• Adding new data to the Excel Dashboard
• Protecting your Excel Dashboard