Excel reporting: The big five reports in controlling

Transformation and structuring of basic data for report preparation

• Access to external data with Power Query.
• Remove unnecessary table contents.
• Create additional grouping characteristics.
• Customization of data types and column headings.

 Important fields of action for setting up dynamic evaluations in Excel

• Requirements for the structuring of workbooks.
• Structure of dynamic control functions in a report.

Calculation of rankings and frequency distributions in the status report

• RANG(), KGROESSTE(), HÄUFIGKEIT() and others.

Important calculation functions for conditional calculations and the creation of dynamic time series in the YTD report

• EDATE(), END-OF-MONTH(), INDEX()/COMPARISON(), SUMMEWENNS(), IFERROR().
• Create conditional calculations in Excel.
• Use different reference functions in Excel.
• Generate time series in workbooks using calculation functions.
• Use functions for handling error values.

 Integration of classic and new dynamic matrix functions in YoY evaluations

• SEQUENCE(), UNIQUE(), FILTER(), SORT BY() and others.
• What do the new dynamic matrix functions in reports do and how do they work?

 Data relations and best practices for their visualization of key figures

• Basic rules for data visualization.
• Case studies for data visualization (status report and deviation report).

Dynamic control of report content without programming knowledge and pivot tables using the example of target/actual comparison and forecast

• CHOICE(), FIRST VALUE(), WENNS(), TREND(), forecast sheet, moving average, exponential smoothing.
• Excursus: Limits of pivot tables in reporting.
• Use of calculation functions to perform alternative calculations (e.g. selection of different plan values, time periods or regions).
• Integrate control functions to protect against incorrect inputs.

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