Power Excel

Dates: 15th – 17th April, 2020 | Time: 9AM - 1PM | Place: Mercure Hotel, Elizabetes street 101 |  Registration fee: 330 EUR + VAT


To whom:

Managers, finance, sales, marketing, quality leaders and experts, who daily use MS Excel for data processing, tracking, reporting, and analysis. 

Challenge:

Inspirational stories about computing's endless capabilities, machine learning and artificial intelligence are often distributed on social media. However, the real life of data processing tends to be much more grounded.

Daily reporting takes an increasing part of the day. Data from different sources must be collected manually. Files received from colleagues require extensive reformat efforts. People in the IT department are friendly, but practical help from them sometimes has to wait for weeks and months.

Good news – these situations can be avoided! But, on the way to a solution, must answer more nuanced questions. What to do with oversize Excel files? How to fix nonfictional links between different file forever? What to do if the data exceeds Excel limits? How to simply automate routine reporting?

ALet us find answers to these and other questions at Elchin Jafarov workshop Power Excel.”.

Gains:

Improved effectiveness of processing participant data in an Excel environment through:

  • skills to reduce manual work;
  • tools for automated data collection from different sources;
  • the ability to optimise the analysis of data that enhances decision-making;
  • minimise the risk of miscalculations and erroneous decisions;
  • developing the most efficient solutions to your company's challenge.

Form:

  • In training, all participants must work with their personal computer.
  • The training files and structure are stored in the cloud platform. After lessons, the “solutions” of the tasks will be available on the platform. You will be able to download all files to your personal computer and use them after courses.
  • Participant's issues from real practice will be heard and dealt with in practice.

Trainer:
Elchin Jafarov Mg.Sc. (Stockholm University) 

  • 15 + years of experience in financial management working in General Electric Group (USA) un Citadele Group (USA/Latvia), etc. 
  • Teaching at Stockholm School of Economics Riga, BA school of Business and Finance/ Swiss Business School, etc. 
  • Qualifications in mathematical statistics and financial mathematics (Institute of Actuaries of UK).
  • Daily using Excel, PowerQuery, Power BI un R programming, noble at R, VBA, DAX, C#, SQL, HTML/CSS other technologies.
  • Developed applications, databases, web-solutions, consulting data flow arrangement, automatization and IT system implementation.
  • Has conducted seminars for Swedbank, Twino, Latvenergo, UPB, 4Finance, ALTUM, Citadele Bank, Signet Bank, BlueOrange Bank and other teams.


Program:
3 half days, 12 hours 

TOPICS

CONTENT

EXERCISES

Session I
DATA EXTRACTION

Half day, 4 academic hours

Automation of data extraction

  • Data from www, data bases, Excel, .csv, etc.

Data transformation

  • Power Query tools for work with data

Quick data table appending

  • Merge un Append

Uncomfortable data table changing

  • Wide data long data

Power Query with M language syntax

  • Full ETL (extract, transform, load) cycle

Exercises:

Getting data from IMDB

Currency exchange rates from Bank of Latvia webpage

Uncomfortable data source transformation to comfortable tables

Multiple table merging from different sources 

Session II
ANALYTICS

Half day, 4 academic hours

Complicated formula use case scenarios

  • Massive formulas, its usage

Measures
  • Ordinary and composite measures

KPI (Key Performance Indicators)
  • KPI report automation

Data models and relationships
  • Creation of unite data model

PowerPivot
  • Advanced PivotTable functions

Exercises:

What to create when SUMIFS cannot help?

Automation of Lookups

Analysing vet clinic

Session IIII
VIZUALIZATION OF ANALYTICS

Half day, 4 academic hours

                                               

Building dashboard

  • Business Intelligence

Datu ērta un vizuāla filtrēšana
  • Slicers, Timelines and usage of these applications

Automatically refreshed charts
  • Pivot Charts and look in Power View

OLAP Cube functions
  • How to make PivotTable to desired format

Advanced conditional formatting
  • KPI visualization and other decorations                                       

Exercises:

Visualizing a trading business

Practice Slicers

Making interactive charts

Creating my own Dashboard                                                         

Registration Form















 .