Excel for controllers

Brief introduction to the core features in Excel

• The importance of the cell approach versus the table approach in Excel for controllers.
• The "Format as table" feature and its many advantages - also when using functions.
• Calculating with speaking names.
• No calculations in the data table.

The genius of the pivot table when analyzing data

• Become a pivot table pro in 3 steps.
• The most important basic settings of the pivot table.
• Consolidate several table areas (ALT+N+P).
• The different layouts and their practical use in controlling.
• Grouping numbers, dates and texts.
• Typical calculations in day-to-day controlling, such as complex comparisons with the previous year.
• The hidden sorting and filter options in the pivot table.
• Create pivot charts.
• Practical example: ABC analysis.

 Excel applications in controlling

• Plan/actual comparisons.
• Cost element, cost center and cost unit accounting.
• Data evaluation, reporting.
• Practical examples and exercises.

The calculated column and its meaning when evaluating data

• The mathematical problem of rounding when calculating and long numbers (cost centers).
• The appropriate combination of functions and the pivot table using various practical examples such as evaluating a machine park or commission payments.
• The important if() function as a decision aid using various practical examples.
• The classic Sreference() and Wreference() functions and nested if() functions.
• The exact match and the interval search of the SVerweis() or XVerweis().
• Nested functions with if() and year() and month() using the practical example of a different financial year.

The best tips and tricks

• Grouping instead of hiding.
• Protection of Excel correctly understood incl. unlocking protection without password.
• Comparison of the clarity and performance aspects of various Excel solutions. 
• Error-proneness of certain Excel solutions in controlling.

Please bring your own laptop to the training . You can find more detailed installation instructions under Methods.

Brief introduction to the core features in Excel

• The importance of the cell approach versus the table approach in Excel for controllers.
• The "Format as table" feature and its many advantages - also when using functions.
• Calculating with speaking names.
• No calculations in the data table.

The genius of the pivot table when analyzing data

• Become a pivot table pro in 3 steps.
• The most important basic settings of the pivot table.
• Consolidate several table areas (ALT+N+P).
• The different layouts and their practical use in controlling.
• Grouping numbers, dates and texts.
• Typical calculations in day-to-day controlling, such as complex comparisons with the previous year.
• The hidden sorting and filter options in the pivot table.
• Create pivot charts.
• Practical example: ABC analysis.

 Excel applications in controlling

• Plan/actual comparisons.
• Cost element, cost center and cost unit accounting.
• Data evaluation, reporting.
• Practical examples and exercises.

The calculated column and its meaning when evaluating data

• The mathematical problem of rounding when calculating and long numbers (cost centers).
• The appropriate combination of functions and the pivot table using various practical examples such as evaluating a machine park or commission payments.
• The important if() function as a decision aid using various practical examples.
• The classic Sreference() and Wreference() functions and nested if() functions.
• The exact match and the interval search of the SVerweis() or XVerweis().
• Nested functions with if() and year() and month() using the practical example of a different financial year.

The best tips and tricks

• Grouping instead of hiding.
• Protection of Excel correctly understood incl. unlocking protection without password.
• Comparison of the clarity and performance aspects of various Excel solutions. 
• Error-proneness of certain Excel solutions in controlling.

Please bring your own laptop to the training . You can find more detailed installation instructions under Methods.