Asset Tagging and Tracking System

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