Excel 2016 - Expert (Business)

Bucharest 18 June 2019 - 24 May 2019

Trainer: Dana-Maria Boldeanu

Details

The program aims to acquire intermediate and advanced concepts and techniques on the practical use of Microsoft Office software - Microsoft Excel, enabling end users from financial field to operate in a professional way using spreadsheet tools.

Participants in this course will be allocated 20 credit points related to continuing professional training for damage finding specialists.

The course can be accessed only once in the professional training program for the damage assessment specialists.

Target group

The direct beneficiaries of the program are persons working in the financial field (in the field of insurance, private pensions and financial investments, related or similar activities, as well as risk management, human resources management and management of the organization) with at least a medium level of knowledge in using Microsoft Excel.

Course objectives

The program provides the opportunity to acquire skills in modeling the economic and financial issues using tabular calculation, use of the most important categories of functions, auditing formula and data validation, sorting and filtering data, synthesizing information, using graphical presentations, simulation, optimization and forecasting based on different case studies tailored to the financial field using Microsoft Excel 2016 application.

Subjects

Module 1: Microsoft Excel 2016 spreadsheet – general features and new features brought from Microsoft Excel 2016:

Details

- Working with Fluent interface (Ribbon)    

- Excel worksheet: cells; range of cells; relative, absolute and mixed addresses for identifying and specifying in formulas both the bidimensional and tridimensional cell ranges;

- Data entry; data series; applying simple, matrix and predictable Excel formulas; circular references;

- Copying formulas (including copying formats, transposing vertical data ranges in horizontal ones and vice versa, conversion to values);

- Printing in Microsoft Excel (Page Break Preview tool, breaks, setting print area, etc.);

- End of module study case.

 

Module 2: Formatting the Excel workspace with the aim to customize and format the financial reports:

Details

- Custom formats for numerical data:

  • displaying or removing the leading zeros;
  • displaying a character string in any custom format simply by using two quotation-marks;
  • setting out the color of characters;
  • supplying a placeholder to fill a cell containing numerical data when the reports use tables with no grid;
  • supplying a placeholder to fill a cell containing text when the reports use tables with no grid;
  • displaying  completely the dates;
  • displaying the dates using a custom format or even certain conditional operators;
  • removing certain types of data entries in general custom format (so-called four-part-format);
  • applying different formats depending on the type of data entry for: positive or negative numbers, null values, character strings;

- Conditional formatting of financial reporting:

  • highlighting values above average;
  • highlighting insurance products with sales of values above average;
  • highlighting dates (daily, weekly, monthly, of the current month, annual, of the current year, by quarters, semesters, and so on)
  • highlighting of increasing, decreasing or stagnation trend of any parameter);
  • revealing numerical data graphically (using bars proportional to the size values);

- End of module study case.

 

Module 3: Calculation of values using the most important functions (mathematical functions, statistical functions, logic, date and time, financial, text, lookup and reference, etc.) with application on financial field

Details

- Calculation of values using mathematical functions:

  • simple functions
  • calculation of values using simple mathematical and conditional functions: adding, counting, calculating the average for:

-                     values (sales) that meet a condition;

-                     values which the price meet a condition;

  • Rounding values (constants or arising from calculations):

-                     rounding a decimal value to a specified number of decimal places;

-                     rounding numbers up or down to the specified number of decimal places (positional constant);

-                     rounding up or rounds down a number to the nearest multiple;

- Calculating values using statistical functions:

  • calculating of arithmetical, geometrical, harmonic averages, as well as, the median value;
  • counting of items in a data series if only all the items meet a condition;
  • supplying the correlation coefficient of two array cell ranges that contain values;
  • identifying that value which appears most frequently in a set of numbers
  • Identifying the “nth” largest or smallest value of a statistical numeric series using the conditional formatting of a position in a hierarchy (which was the highest / lowest price of a series of data?)
  • calculating the number of times specified values appear within a statistical numeric series (by calculation and/or graphical diagram);

- Applications with logical functions on finance data (Logical functions: IF, AND, OR, NOT, TRUE, FALSE)

- Setting the conditions of conditional formatting with simple and nested conditions (on simple, cumulative and progressive trances);

- Calculations with dates and times (Date&Time functions):

  • Specifying the dates-type elements (day, week, month, quarter, semester, year);
  • Calculations depending on the promotional times;
  • Calculations for date of payment and promotions (with banking days, due days)
  • Calculations with fractions of year;
  • Interest on steady date.

- Financial calculations (Financial functions):

  • Calculations based on annuities (present value, future value, amount of payment, interest rate);
  • Drawing a table of a loan repayment;
  • Simulating the amount of payment by varying the number of payment periods and interest rates;

- Identifying information from Excel tables or databases according to a lookup key (Lookup&Reference functions):

  • Simple lookup (not test required) using the following functions: CHOOSE, COLUMN, COLUMNS, ROW, ROWS, MATCH, INDEX, LOOKUP, VLOOKUP, HLOOKUP
  • Lookup with test referring to the existence of lookup key in the table or database

- Using the following Text functions in applications with product codes, services and string-type elements: LOWER, UPPER, PROPER, VALUE, TEXT, CONCATENATE, LEFT, RIGHT, MID, LEN, EXACT, SUBSTITUTE, FIND, SEARCH, REPLACE;

- End of module study case.


Module 4: Formula Auditing, checking the content of the cells and identifying types of errors

Details

- Presenting Formula Auditing toolbar

- Features for checking errors and formulas (Watch Window and Evaluate Formula)

- Checking the content of the cells;

- Displaying information related to cells or ranges of cells;

- Types of errors in Excel (#N/A, #DIV/0!, #NUM, #NAME, #VALUE, #REF, #NULL);

- End of module study case.

 

 

Teaching method:

A PowerPoint presentation and Microsoft Excel files with specific examples represent the teaching support. The following references have been used for the program’s support.

Trainer

Dana-Maria Boldeanu is associate professor at the Management Information Systems Department within the Faculty of Accounting and Management Information Systems – Bucharest University of Economic Studies Management Information Systems. She has over 15 years of experience in teaching and research in specializations: Professional Spreadsheets, Business databases, Business Intelligence, Project Management and Business Services Industry. She obtained the professional qualification of Microsoft Office Specialist certification in 2016 from Microsoft through Learning Solution Training in Romania and also has ITIL® Foundation Certificate (2016) in service management through EXIN/Axelos Global Best Practice. She graduated in 2012 from “Research and Teacher Education for Business & Economics (EDU-RES)” interdisciplinary master program at the Faculty of International Business and Economics. From 2009 Dana Boldeanu is a member of the organizing committee of Accounting and Management Information Systems (AMIS) International Conference and starting with 2014 she is a member of the program committee of IBIMA Conference.

Between 2007 and 2009 she was trainer for ECDL Romania at the National Institute for Administration for: Module 1 and 2 (Computer Essentials), Module 4 and 5 (Excel, Access). She was active since 2002 in numerous research projects gained by competition and financed by the National Research Council or with structural and EU funds. For 2009 Dana-Maria Boldeanu is CEO of Centre for Advanced Services on Electronic Services (E-CAESAR), a non-profit association with researches focus on e-services. She was involved in setting up the research centre, acquiring projects and partnerships, working as leader or member in several projects.

Duration / Period

The duration of the program will have 12 hours.

6.05.2019 (Between: 13.00-16.00)

7.05.2019 (Between: 13.00-16.00)

10.05.2019 (Between: 9.00-12.00)

13.05.2019 (Between: 13.00-16.00)

 

 

Investment

The investment for this program is:

  • 400 lei + VAT / participant
  • 450,42 lei + VAT / participant, with the fee for validation and registration of credits included.

Fees include: course materials, coffee breaks, participation certificates.

Apply for course
Helpful information to complete the form.




 Newsletter ISF

Abonează-te acum pentru a primi ultimele noutăți. Suntem prezenți și pe rețelele de socializare și