The System helps companies to have autonomy on managing their assets . The System allows regular asset audit without much hassles.
FINANCIAL MODELLING USING EXCEL
Become a certified financial modeler by Sangana Africa
Excel Fundamentals
| MTS CODE | MAIN HEADER | DETAILS |
|---|---|---|
| 101 | Excel Environment | Ribbon, Tabs, Groups, Commands, Contextual Tabs,Dialog Box Launcher, Row Headers, Column Headers and Cells Creating workbooks, Entering Data in Excel, |
| 102 | Customizing Excel | Excel Options – Body Font & Font Size, Advanced Options, including Editing Custom Lists, Ribbon & Quick Access Toolbar |
| 103 | Working with Data in Excel | Adding & Editing Data Hiding vs Outlining Data Differentiation between Table and Range Conversion of range into table and vice versa Sparklines |
| 104 | Introducing Formulas & Functions | Difference Between a formula & a function The Functions Library Golden Rules of Functions including order of calculation |
| 105 | Key Formula Concepts | Copying Formulas: Referencing (Relative, Mixed & Absolute) Linking worksheets and Workbooks |
| 106 | Naming of Cells & Ranges | Static Naming Defining Names Applying Names Managing names |
| 107 | Date & Time Functions | Now, Today, Month, eomonth, datedif, day, week, Year, edate, Text Function |
| 108 | Conditional Formatting | Use of Highlight cell rules Use of Icon sets Use of Colour sets Top Bottom rules Data bars |
| 109 | Data Cleaning Techniques | Identifying and removing/replacing duplicates Cleaning Data from a database using Text Functions Text Functions – Text, Concatenate, Trim, Proper, Left, Value |
| 110 | Errors How to handle Errors | Types of Errors (#Value, #Ref, #Name etc) Handling Errors Iferror Function Information functions: Iserror, Istext, etc |
| 111 | Data Analysis | Sorting Data and Multiple column sort Filtering Data SubTotal Feature |
| 112 | Big Data Techniques | Linking Ms Suite, Summing through sheets, Worksheets consolidation |
| 113 | Data Validation | Validation of Excel inputs - Templates basics Validation with Vlookup or If function - intro to model dynamism |
| 114 | Printing | Choosing what to print Previewing and printing Page Setup Headers and footers |
| 115 | Pivot Tables | Creating a Pivot Table Formatting a Pivot Table 3 Dimensional Pivot table Slicing a Pivot Table Time lining the Pivot Table Refreshing a Pivot Table Changing Calculation Function Inserting a Pivot Chart Mini model development (Optional) |
Formulas & Techniques
| MTS CODE | MAIN HEADER | DETAILS |
|---|---|---|
| 201 | Selected Concepts from Excel | Excel environment, Range naming, Date & Time functions, Data cleaning Techniques |
| 202 | Managing Data/ Data preparation | Identifying and removing duplicates Cleaning Data from a database using Text Functions |
| 203 | Formatting & Styles | Cell Styles & Themes, Wrap Text, Sparklines, Number Formatting |
| 204 | Lookup Functions Pulling Data from Databases | Vlookup, Hlookup, Dynamic Vlookup Match & Index, Offset, GetPivotData |
| 205 | Errors & Error Handling | Types of Errors Handling Errors: Iferror, Istext, Iserror, Isna |
| 206 | Logical Functions | IF(s) IF(OR()&AND()) |
| 207 | Math & Trigonometry | Aggregation in Excel Sum & Average, Min & Max, Counta Vs Counta, Top & Bottom Countif(s), Sumif(s), Averageif(s) |
| 208 | Data Validation | Limit Data entry to desired criteria |
| 209 | Pivot Tables | Creating a Pivot Table Formatting a Pivot Table Slicing a Pivot Table Time lining the Pivot Table Refreshing a Pivot Table Changing Calculation Function Inserting of a calculated field Inserting a Pivot Chart |
| 210 | Charts | Types of Charts (Line, Bar, Pie, Histogram, Scatter, Combo Chart) Choosing Chart Types Linking Charts to drop-downs Dynamic Charts Formatting a Chart |
| 211 | Radio Buttons in Excel | Combo Box Option Button Text Box |
| 212 | Big Data Techniques & Work Review | Consolidating workbooks Linking MS suite for auto updates Getting external data into excel Work Protection & Hiding formulas Formula debugging |
| 213 | What If Analysis | Scenario Management (Best Case, Worst Case, Optimum) What if Analysis using Data Tables and Goal Seeking |
| 214 | Model Development | The class will have a practical model development experience using practical information The model development brings the learnt concepts into perspective and prompts contemporary data analysis techniques discussion. |
Dashboards & Reports In Excel
| MTS CODE | MAIN HEADER | DETAILS |
|---|---|---|
| 301 | GettingStarted | What is a dashboard Do's and Don’ts on dashboards Dashboard principles Dashboard Cycle Planning for the dashboard |
| 302 | Dashboard Design Principles | Advantages of dashboards Drawbacks of dashboards Steps in dashboard designing Sharing the dashboard Upgrading the dashboard |
| 303 | Data Cleaning | Trim Upper, Lower, Proper Left, Mid, Right Concatenate & Replace |
| 304 | Conditional Formatting | Use of Highlight cell rules Use of Icon sets Use of Colour sets Top Bottom rules Data bars & Colour scales |
| 305 | Dynamism on the Dashboard | Slicers & Time lines Dynamic text box Filtering using user developed filters Data validation Form controls |
| 306 | Navigation in the Dashboard | Text Hypelinks Objects Hypelinks Icons To Web Sites To Other Files |
| 307 | On demand Charts in Excel | Creating Charts Types of Charts Gauge Chart Animation Trendlines Dynamic Charts Waterfall charts |
| 308 | Radio Button in Excel | Combo Box Option Button Text Box |
| 309 | Work Protection on the Dashboard | Protecting Worksheet Protecting Range Selecting locked & Unlocked cells Hiding & unhiding formulas |
| 310 | Dashboards Development | Dashboard 1 using Pivot Tables Dashboard 2 using Formulas & Functions Delegates will develop these from practical examples and one can decide to take Tables or Formulas route since both can be used to reach same results as stand alone |
Visual Basic For Applications (VBA)
| MTS CODE | MAIN HEADER | DETAILS |
|---|---|---|
| 401 | VBA Environment | What is VBA Basic Features of VBA VBE Environment What kind of things can you do with it? Use this technology within existing projects. |
| 402 | Introduction to Macros | Defining Macros Macro Security Recording Macros Editing Macros Differentiating Sub Procedure from Functions Saving a macro enabled workbook |
| 403 | User Defined Functions | Structure of a Function Structuring new functions Coding user defined function Running a function Using excel functions in VBA |
| 404 | Drilling VBA as an OOP language | Listing Objects in Excel-VBA Objects properties and how to manipulate them Differentiation of properties, methods and events |
| 405 | Sub procedure | Structure of a Sub procedure Coding a Sub procedure Running a Sub procedure Automating sub procedure |
| 406 | Conditional Programming | Using IF in VBA Nested IF in VBA |
| 407 | Looping in VBA | Taking care of repeated tasks with VBA FOR Loop Do Loop Copying data from one sheet to another Calculations from different sheets Getting Data from Excel |
| 408 | User Forms in VBA | Creating a customised Userform in VBA How to record data into Excel. Validating input fields on the Userform |
| 409 | Project | Create a project for later reference The project is build-up of the learnt concepts and is from the industry |
Contemporary Decision Making (CDM)
| MTS CODE | MAIN HEADER | DETAILS |
|---|---|---|
| 601 | Introduction to Decision Making Under Uncertainity | Discuss Scenerios where resources are limited and the best options should be adopted. The maximise vs the Minimise scenerios using Excel |
| 602 | Introducing Excel Tools for Sessions | Discuss the Excel in-built features which can be utilised to generate information for decision making in an instatnt |
| 603 | What if Analysis | Scenerio Manager, Goal Seek, Data table Construct own what if analysis using functions |
| 604 | Linear Programming | What is Linear programming & Solver in Excel Discuss numerous scenerios for optimum solutions Solving how to get the best mix of variables to get the best outcome from a mix of variables |
| 605 | Simulation | Learn why & how to generate random numbers in excel Steps when building a Simulation model. Types of Simulation |
| 606 | Introduction to Regression | Approaches to line fitting, Least squares approach, Linear regression as a statistical model Multiple Linear regression, Matrix formulation Linear trend adjusting |
| 607 | Graphing regression | How to generate a linear equation Plotting linear equation values |
| 608 | Introduction to Forecasting | Why forecast? Where to apply forecasting in an organization? Forecasting methods & forecasting in Excel |
| 609 | Moving Average | smooth out the trend in data Select Moving Average and set the Period based on your data moving average line on your chart |
| 610 | Exponential Smoothing | Learn how to do smoothing of past data trends Algorithm for smoothing by detecting seasonality patterns and confidence intervals Use Excel to utilise Forecast Sheet feature (Excel 2016) |
Our Partners
