Excel 2016 - Expert (Business)

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 “n^th” 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.