Advanced Excel Data Analytics

Are you an executive looking to leverage from data driven decision-making, optimise business processes, increase your company’s operational efficiency and drive the top-line while cutting costs? Is your Finance team out of options where to cut costs and remain efficient? Are you sleeping in the office trying to conduct sensitivity analysis for business case and optimisation decisions? Is reporting taking a lot of time?

What to expect

This package will equip you with skills required to build robust prescriptive, descriptive, and predictive analytics models, unearth hidden insights in your core systems, design optimisation solutions to maximise your portfolios, mitigate risk, and build presentation kits to demonstrate thought-provoking patterns in your company’s data to other executives and staff to enhance data driven decision making.

Who should attend?

1. Finance and Audit professionals analyzing risk in their financial, investment and banking models.
2. Managers and Executives who monitor and are required to produce reports and dashboards to present to their teams and board members.
3. Risk Management Professionals looking to develop robust risk assessment models, looking for areas to mitigate risk and potential grounds to leverage.
4. Entrepreneurs looking to assess investment decisions, prepare financial proposals and project future returns based on historical data.

Fees Ugx 900,000

1. Introduction to Advanced Excel Data Analytics

a) Data Analytics Methodologies in accounting, financial and risk management

b) Common tools and their application in business analytics

c) The Microsoft Excel toolkit to Advanced Analytics.

d) Proficiency Quotient Test

2. Advanced Formulas and Functions for Accounting, Financial, Operational and Risk

Management

a) Formula Auditing tools

b) Logical Functions

c) Lookups and Referencing

d) Currency Manipulation Functions

e) Time Intelligence Functions

f) Cleaning Functions

g) Developing Loan Amortization

h) Developing Depreciation Schedules

i) Investment Analysis Functions

j) Portfolio Optimization

k) Investment Appraisal

3.Table Structuring and Design

a) Introduction to Dynamic Tables

b) Advanced Conditional Formatting

c) Advanced Data Validation

d) Developing dynamic risk registers

e) Developing dynamic presentation tools to detect anomalies and highlight areas of key concern.

f) Sorting and Filtering using dynamic tables

4. Advanced Formatting Tools and Templating

a) Day One Test Assessment and Solution

b) Cell Referencing

c) Cell Formatting

d) Template Customization for report presentation and efficiency

5.Introduction to 360 analytics

a) PivotTables

b) Pivot Summary, Sorting and Filtering

c) Pivot Table Design

d) PivotTables

e) Pivot Summary, Sorting and Filtering

f) Pivot Table Design

g) Source Update and refreshing

h) Pivot Table calculus and implicit measures

i) Slicers and Timelines

j) Developing an FMCGProduct/Service volume analysis model part 1

k) Developing an FMCGProduct/Service volume analysis model part 2

6. Introduction to Business Intelligence

a) Connecting to external flat-files

b) Connecting to external folders

c) Introduction to Microsoft Excel Business Intelligence Workflow;Power Tools

d) Automating cleaning and structuring of external data using power query

e) Working with the audit trail

f) Developing a dynamic rolling calendar

g) Data Consolidation and merging

h) Connecting to the web for systematic factors

i) Connecting to live databases and cloud systems for insights

7.Advanced Excel Data Modelling

a) Connecting different core business applications for cross platform analytics.

b) Developing hierarchies and perspective

8.Data Analysis Expressions

a) Calculated Columns Vs Measures

b) Banding and Stratification

c) Developing aging analysis tools

d) Detecting gaps and duplicates in an invoice system

e) Filter functions

f) Iterators

g) Advanced Time-Intelligence functions

h) Data drill down

i) Developing and presenting KPIs

j) Automatic refreshing of data from the core systems

9.Financial Planning and Analysis

Developing a three-statement financial model

Integrating LBO, M&A and business valuation models.

Scenario planning and simple stress testing.

Simple Financial forecasting

Financial analysis and financial decision-making using data

10.Advanced Financial Forecasting and decision analysis

a) The data analysis toolpak.

b) Multiple regressions

c) Hedonic Forecast Models for stress testing financial decision making.

d) Hypothesis testing and selection of key drivers of growth and costs.

e) Dummy optimisation and forecasting during apandemic/crisis

f) Fraud-risk analytics

11.What-if Analysis

a) Break-even point analysis

b) Sensitivity Analysis of investment

options, tax fluctuations and

other business shocks.

c) Activating the solver module

12. Optimization and Scheduling

a) Branch optimization algorithms

b) Staff scheduling optimization algorithms

c) Cost optimization algorithms

d) Profit maximization algorithms

13.Data Visualization and Reporting

a) Best practices in data visualisation

b) Relationship visualisation and forecasting time series.

c) Customizing Visuals using industrial standards

d) Histograms and Pareto Charts for Operational Excellence

e) Areas Charts, Doughnuts, race tracks

f) Tree maps, sunbursts

g) Cashflow visualization

h) Marketing funnels

i) Dynamic heatmaps

j) Conditional Charts

14. Dashboard and report development and implementation

a) Excel-PowerBI Integration

b) KPI Cards and gauges

c) Drill through reports

d) Advanced Dashboards and Interactivity.

e) Hosting your dashboard for realtime manipulation

Register for the training here