Financial Analysis with MS Excel in Nairobi, Kenya

Introduction:
Excel has a number of useful built in functions, but there are also some caveats about its computations. For this reason and to facilitate more flexibility, in this course we shall demonstrate some handcrafted techniques to help the delegates manage large data sets and improve their reporting efficiency.

The course sessions shall focus on how to effectively and efficiently utilize Microsoft Excel for data management, modeling and projection with a positive bias to lookups. Users will not only use Excel to build models, but also to crunch a large data dump. Learn how to minimize as much manual labor as possible, thereby saving time and performing more detailed analysis and projections quickly. Apply

commonly-used formulas in new and different ways; uncover often over-looked Excel features; streamline number crunching with pivot tables, functions, arrays, Data tools, Formatting, Macros and VBA programming.

Course Overview:

Module 1: Advanced Report Development

This module shows how spreadsheets can be used to enhance management reporting.

The course shows how to analyze and review data extracted from ERP systems and databases in order to prepare management reports, including using Excel’s graphing capabilities. The course studies Excel’s functions and how to use these effectively. The program also shows how to access the Visual Basic Editor and use macros and user defined functions in order to enhance Excel’s functionality. Half a day will also be spent showing attendees how to use Excel’s pivot table functionality. Pivot tables are often poorly understood and yet are the most powerful feature in Excel. A basic pivot table will allow you to summarize 50,000 rows of data in 30 seconds.

Module 2: Business Performance Analysis, Analyzing the Bottomline and Topline

The goal of business performance analysis is to identify improvement opportunities, understand their root causes, and taking action. These opportunities can come in many forms. They can be performance issues or areas of the business that perform extremely well and can be leveraged even further.

This course, introduces you to key concepts of business performance analysis. We focus on the analysis of the profit and loss (the P&L) statement and on the key dynamics you need to understand in order to interpret the performance of your business. Understanding this data will help you make informed decisions that benefit your company in the long run.

We will teach you how to analyze the top line (or revenue) of your company and identify areas of under- or over performance with Excel. We explore essential financial concepts that are necessary to understanding your company’s revenue data. We then introduce the tools and techniques you can use in Excel, including PivotTables and the invaluable SUMIF function, to prepare and analyze the data you have available. Plus, learn how to analyze your customer base, determine revenue per product, and identify upsell opportunities. We will also show how to make sure you’re treating your data fairly, by considering the impact of seasonality, recurrent vs. one-off business, and interdependencies.

In our Second section we will teach you how to analyze the bottom line (or profitability) of your company and identify areas of under- or over performance with Excel. We show you how to use the data your company generates every day to break down your gross margin and operating expenses. We introduce the tools and techniques you can use in Excel to prepare and analyze that data. Plus, you’ll learn to identify and analyze sales and marketing spending, staff expenditures, and productivity separately, so you can get a really clear picture of your data from multiple angles.

How to register:
To register, send an email to: [email protected] You can also visit our website on www.opencastlabs-africa.com  and fill an online application form and submit to us.

February 24th - 26th 2020
Register Individual: https://cutt.ly/YrQeePs
Register Group: https://cutt.ly/yrQeyek

April 06th - 08th 2020
Register Individual: https://cutt.ly/crQetbR
Register Group: https://cutt.ly/LrQeyIf

View Related Courses: http://opencastlabs-africa.com/school-of-data-science/

Contact Details:

Rwanda:

P.O Box 4543 Kigali

3rd Floor La Bonne Address House

Avenue de la Revolution

Tel: Kigali +250 788 470 532

The Training Coordination Office (Joab/Diana)
Capacity Building Division
Argwings Kodhek Road, opposite YAYA Center
P.o Box 30225 – 00100 , Nairobi, Kenya
Tel: +254 0204409651   Mobile: +254 723870644
Email : [email protected]

Language

Participants should be reasonably proficient in English.

Fee Exceptions

All international participants will cater for their, travel expenses, visa application, insurance, accommodation and other personal expenses.

Accommodation

Accommodation is arranged upon request. For reservations contact us below.

Email: [email protected]

Payment:

Payment should be transferred through bank 5 days before commencement of training.

Cancellation policy

• All requests for cancellations must be received in writing.
• Changes will become effective on the date of written confirmation being received.
• The appropriate cancellation charge will apply

Course Details: Module 1 

1.      Analyzing Data

• Using Automatic Outlining
• Consolidating Information by Position or Category
• Inserting Subtotals
• Creating an Advanced Filter
• Using Database Functions 

2.      Data Management

• Importing Data from a Text File
• Exporting Data
• Converting Text to Columns
• Connecting to Data in an Access Database
• Creating a Web Query
• Importing Web Data using a Saved Query
• Working with Hypertext Links

3.      Fundamentals of Excel Pivoting

• Understanding PivotTables
• Creating a PivotTable using Worksheet Data
• Creating a PivotTable using an External Data Connections
• Laying out a PivotTable on a Worksheet
• Modifying PivotTable Fields
• Using a Report Filter
• Refreshing a PivotTable
• Formatting a PivotTable

4.      Enhancing PivotTables

• Working with Summary Functions
• Sorting Items in a PivotTable
• Creating a Slicer
• Grouping Data
• Applying Label and Value Filters
• Creating a Calculated Field
• Creating a Calculated Item
• Creating Charts from PivotTables 

5.      Excel Business Analysis

• Creating Scenarios
• Working with Scenarios
• Using Goal Seek
• Using One-Input Data Tables
• Using Two-Input Data Tables
• Understanding Solver
• Defining a Problem
• Solving a Problem
• Generating a Report of Results and Alternate Solutions 

6.      Forecasting, Charting and Dashboards

• Inserting chart Trendline
• Choosing the right Trendline for the right data
• Linear forecasting
• Relationship equations for projection
• Using Excel for Budgeting
• Budgeting best practices Building & Linking Budget 

7.      Advanced Formulas and Functions

• Working with Functions
• Using the IF Function
• Nesting Functions
• Using Multiple Conditions with the IF Function
• Using Table Lookup Functions (HLookup and VLookup)
• Using Text Functions

8.      Business Intelligence Fundamentals

• Introduction to excel PowerBI tools• Importing Data
• Transforming, cleansing and Filtering Data
• Merging and Shaping Data
• Grouping and Aggregating Data
• Inserting Calculated Columns 

9.      Excel automation

• Recording a Macro
• Saving a Macro-Enabled Workbook
• Executing a Macro
• Creating a Macro Button
• Working with Macro Buttons

Course Details: Module 2

Summary blocks:

• An introduction to financial analysis
• Preparing your revenue information
• Comparing past performance
• Analyzing customer data
• Analyzing product information
• Identifying exceptional revenue
• Finding data points
• Prepping data
• Calculating standard cost and gross margins
• Analyzing overall gross margin performance
• Analyzing individual and overall expenses

Course Overview:

1.       Analyzing business performance

• Analyzing P&L components
• Know when to stop analyzing
• Controllable and uncontrollable factors
• Exceptional factors
• Analysis about decision making
• Analysis vs dashboard

2.       Preparing your revenue information

• The basics of revenue analysis
• Finding the data points
• Assembling the data
• Preparing the data
• Pivot tables and SUMIF
• Using Pivots
• Adding consolidation levels

3.       Past performance

• Past performance and seasonality
• Comparing to past performance

4.       Customer information

• Analyzing customer information
• Identifying current customers
• Determining revenue per customer
• New vs returning customers

5.       Product information

• Analyzing product information
• Determining revenue per product
• Determining upsell Opportunities

6.       Exceptional Revenue

• Identifying recurrent vs one-off business Isolating recurrent from one-off business 

7.       Identifying dependencies

• Analyzing further revenue with Key Performance Indicators (KPIs)

8.       Analyzing Gross margin

• Basics of gross margin analysis
• Finding data points
• Preparing the data
• Categorizing expenditure
• Allocating costs by category
• Calculating standard costs
• Using standard cost to calculate gross margin
• Performing a price mix analysis
• Analyzing overall gross margin performance
• The gross margin percentage levers

9.       Analyzing operating expenses

• The basics of operating expenses analytics
• Finding data points
• Analyzing overall operating expenses performance
• Analyzing sales and marketing
• Analyzing non sales staff expenditures
• Analyzing productivity
• Analyzing general expenses 

The 3 day course costs 450$ (45,000 KES), Exclusive of a 16% V.A.T, The Cost includes all training fees, materials, lunch and refreshments as well as certificates and 6 month post training support.