Ateneo - UPO

The course introduces the advanced use of Microsoft Excel 365 and Power BI for the management, analysis, and visualization of Human Resources data. Its objective is to develop the quantitative and digital skills required to transform data from different sources into meaningful and actionable information supporting HR decision making and strategic workforce planning.Particular attention will be devoted to data organization, import, cleaning, and transformation, as well as to the automation of analytical and reporting activities using Excel, Power Query, and Power BI. Students will learn how to structure workforce datasets, integrate information from different sources, and develop efficient, transparent, and reproducible data update procedures.The course will examine the development and interpretation of key Human Resources performance indicators, including workforce composition and evolution, employee turnover, absenteeism, recruitment, training, internal mobility, employee performance, and workforce costs. These indicators will be used to identify trends, compare employee groups and organizational units, and investigate significant relationships among qualitative and quantitative variables.A substantial part of the course will focus on the design of interactive reports and dashboards. Students will learn how to identify and select relevant information, choose appropriate data visualizations, and communicate analytical findings clearly and effectively to audiences with different levels of technical expertise. Dashboards will be developed not only as descriptive reporting tools, but also as instruments for data exploration, performance monitoring, and decision support.The course will also introduce HR analytics techniques for investigating relationships among employee characteristics, organizational policies, and relevant business outcomes. Through practical applications and case studies, students will examine methods for identifying the factors associated with relevant HR outcomes, such as employee turnover, absenteeism, performance, and professional development, as well as basic models for analysing trends and forecasting their potential future evolution.By the end of the course, students will be able to use Excel 365 and Power BI in an integrated manner to organize and transform HR data, automate recurring activities, develop performance indicators and analytical models, create interactive reports and dashboards, and communicate relevant insights in support of operational and strategic Human Resources decisions.

The course will follow a predominantly interactive and applied teaching approach. Topics will be introduced through practical examples, guided demonstrations, and exercises carried out directly in Microsoft Excel 365. Students will be encouraged to participate actively in classroom activities and to use their own computers during lectures, reproducing the procedures presented by the instructors and applying them to Human Resources data and decision problems.

The course will make extensive use of web based learning resources, including online tutorials and supplementary materials. In particular, several YouTube video tutorials, primarily in English, will be selected by the instructors to complement the topics covered during lectures and support independent learning. Additional information, datasets, exercises, and teaching materials will be provided during the course and made available through the teaching platform.

Practical activities will be carried out using Microsoft Excel 365 for Windows, with particular emphasis on advanced functionalities for data organization, analysis, and visualization. The course will cover the new dynamic array functions available in Excel 365, Excel Tables, and tools for developing flexible, automated, and easily updatable analytical models.

A specific part of the course will be devoted to Microsoft Power Query, which will be used to import, clean, transform, integrate, and update data from different sources. Students will learn how to automate recurring data preparation procedures and develop transparent, reproducible, and efficient analytical workflows, with particular reference to Human Resources data.

Instructions, exercises, and teaching materials are specifically designed for the Windows version of Microsoft Excel 365. Some functionalities, procedures, and user interface features may differ or may not be available in versions of Excel for macOS or in alternative software used on Linux systems. Full compatibility of the teaching materials and practical exercises with operating systems other than Windows therefore cannot be guaranteed.

A useful reference for further study of Microsoft Excel functionalities is:

Michael Alexander, Richard Kusleika, and John Walkenbach, Microsoft Excel 365 Bible, Wiley, March 2022.

The textbook is recommended as a reference and supplementary resource. Additional materials and online resources will be indicated throughout the course.

Upon successful completion of the course, students should be able to:

1. use Microsoft Excel 365 at an advanced level as a tool for organizing, transforming, analysing, and visualizing data, as well as for supporting managerial and Human Resources decision making;

2. use advanced Excel 365 functionalities, Excel Tables, and Power Query to import, clean, integrate, and update data from different sources, automating recurring HR data preparation, analysis, and reporting activities;

3. understand and formalize decision problems in Human Resources management by identifying the objectives of the analysis, the relevant variables, the available sources of information, and the most appropriate indicators to support decision making;

4. translate organizational and managerial problems into coherent quantitative models by formulating appropriate assumptions and using data to compare employee groups, organizational units, HR policies, and alternative scenarios;

5. develop and interpret key Human Resources indicators relating, among other areas, to workforce composition and evolution, recruitment, employee turnover, absenteeism, training, internal mobility, employee performance, and workforce costs;

6. apply quantitative analytical tools to summarize and interpret HR data, identify trends and anomalies, and investigate significant relationships among qualitative and quantitative variables;

7. use analytical findings to support operational and strategic Human Resources decisions, critically evaluating the available evidence and recognizing the main assumptions and limitations of the methodologies employed;

8. design and develop interactive reports and dashboards that support data exploration, the monitoring of relevant indicators, and the clear and effective communication of analytical findings;

9. select appropriate charts, tables, and performance indicators according to the nature of the data, the objectives of the analysis, and the characteristics of the intended audience;

10. communicate and critically discuss the results of quantitative analyses, translating complex data into meaningful and actionable information for Human Resources professionals, managers, and other stakeholders involved in decision making.

Prerequisites:

Fundamental understanding of Excel, including basic operations and functions when working with worksheets.
Proficiency in Google Suite applications, such as Google Sheets, Docs, and Forms.

Teaching MethodsThe course combines interactive lectures with practical exercises. The presentation of theoretical concepts will be systematically supported by applied examples, guided demonstrations, and hands on activities. The course will focus on the use of Microsoft Excel 365 and Power Query for organizing, transforming, analysing, and visualizing data, with particular reference to problems and applications in Human Resources management.During the lectures, students will be actively involved in the development of quantitative models, the analysis of datasets, the construction and interpretation of Human Resources indicators, and the design of interactive reports and dashboards. Particular attention will be devoted to the critical interpretation of analytical results and to their effective communication in support of decision making.Practical activities will be carried out directly on students’ computers. Students will be encouraged to reproduce the procedures demonstrated by the instructor, apply the analytical tools introduced during the course to real world cases, and discuss and interpret the results obtained. Teaching activities may include the use of datasets, case studies, and online learning resources, including tutorials in English.Classes will take place in a standard classroom rather than a computer laboratory. Each student is therefore required to have a personal laptop with a fully functioning version of Microsoft Office 365 for Windows installed. Instructions, exercises, and teaching materials will be specifically designed for the Windows version of Microsoft Excel 365.Regular attendance and active participation in lectures and practical activities are strongly recommended.

Further details and updates will be provided on the course webpage (www.dir.uniupo.it) during the course.

Attending the course lessons in person is highly recommended.

Please note that the teaching instructions and materials are specifically designed for Windows PC users.

For face-to-face classes, you will be using a computer lab, and there is no need to bring your own device.

Students with physical disabilities, Learning Disabilities or Special Education Needs can request specific services and tools via the Staff Sviluppo e Coordinamento Carriere e Servizi alle Studentesse e agli Studenti, consulting the University webpage: https://www.uniupo.it/en/services/servicesstudents-physical-or-learning-disabilities Students with disabilities, learning disabilities or special education needs, once they have contacted the University Staff, can refer to the tutor in charge of the course to define the examination modalities.

Assessment will be based on active participation in classroom activities and the completion of a final project, undertaken either individually or in groups of no more than three students.

Participation will be assessed by considering students’ contribution to practical activities, their ability to apply the tools introduced during the course, their engagement in the discussion of case studies, and their interpretation of the results obtained.

The final project is intended to assess students’ understanding of the more advanced topics covered in the course and their ability to integrate and apply the knowledge and analytical tools acquired to complex Human Resources management problems.

The project will involve the analysis of a dataset related to a relevant HR issue. Students will be expected to demonstrate their ability to organize, import, clean, and transform data using Microsoft Excel 365, Power Query and PowerBI, select and develop appropriate indicators, and use quantitative and graphical methods to identify trends, anomalies, and significant relationships among the variables under investigation.

Particular attention will be devoted to students’ ability to transform data into meaningful and actionable information supporting decision making. The project will therefore include the development of an interactive dashboard designed to summarize and explore the main findings, monitor relevant indicators, and communicate analytical insights clearly and effectively.

The project must be accompanied by a written report and an oral presentation. The written report should describe the objectives of the analysis, the characteristics of the data, the procedures adopted, the methodologies applied, the results obtained, and their implications for Human Resources management. The presentation should demonstrate the ability to select the most relevant information, critically interpret the findings, and communicate them effectively to both technical and nontechnical audiences.

Assessment will consider students’ understanding of the course topics, the methodological accuracy of the analysis, the appropriateness of the tools employed, the quality and effectiveness of the interactive dashboard, the ability to interpret the results critically, and the clarity of both the written report and the presentation.

The course introduces the integrated use of Microsoft Excel 365, Power Query, and Microsoft Power BI for organizing, transforming, analysing, and visualizing Human Resources data.The course is divided into two closely integrated parts. The first focuses on the advanced use of Excel 365 for quantitative modelling, data management and analysis, the automation of recurring activities, and the development of interactive reports and dashboards. The second introduces the principles of Business Intelligence and the use of Power BI to integrate data from different sources, develop relational data models, and create interactive reports and dashboards supporting HR decision making.Topics will be developed through practical exercises, applications, and case studies concerning workforce composition and evolution, recruitment, employee turnover, absenteeism, training, performance, employee engagement, and workforce costs.Part I, Data Analysis and Modelling Using Excel 3651. Introduction to Spreadsheet ModellingThe role of data and quantitative models in Human Resources decision making. Principles for designing clear, reliable, transparent, flexible, and easily updatable Excel models. Organization of inputs, calculations, and outputs. Error checking and model documentation.2. Data Organization and Structured Data ManagementStructuring HR datasets. Use of Excel Tables and structured references. Data sorting, filtering, searching, and selection. Data validation and development of interactive models.3. Advanced Functionalities and Dynamic Array Functions in Excel 365Use of the main dynamic array functions to extract, select, sort, aggregate, and transform data. Creation and management of dynamic arrays. Lookup and reference functions. Introduction to advanced formulas and custom functions.4. HR Data Analysis and SummarizationDevelopment and interpretation of key indicators relating to workforce composition and evolution, recruitment, employee turnover, absenteeism, training, internal mobility, employee performance, and workforce costs.Use of aggregation functions, PivotTables, and PivotCharts to summarize data, compare employee groups and organizational units, and identify trends, anomalies, and significant relationships.5. Data Visualization and CommunicationPrinciples of data visualization. Selection and development of static, dynamic, and interactive charts according to the nature of the data and the objectives of the analysis. Use of summary indicators and Key Performance Indicators, KPIs. Principles of data storytelling and effective communication of analytical findings to technical and nontechnical audiences.6. Development and Publication of Interactive DashboardsDesign of dashboards for monitoring and analysing key HR indicators. Integration of Excel Tables, PivotTables, dynamic arrays, KPIs, charts, filters, slicers, and interactive controls. Integration of Excel dashboards, charts, and other content into simple web pages and Google Sites.7. Data Import, Cleaning, and Transformation Using Power QueryImporting data from Excel workbooks, CSV files, and other sources. Management of missing values, duplicate observations, errors, and inconsistent data formats. Cleaning, transformation, aggregation, and integration of datasets from different sources. Automation of data import and update procedures.Part II, Business Intelligence and HR Analytics Using Power BI1. Introduction to Business Intelligence and Data Driven CultureThe role of data in business decision making and Human Resources management. From data to information, from information to insight, and from insight to action. Principles of Business Intelligence and People Analytics.Definition of analytical objectives and identification of the information and indicators required to reduce uncertainty, monitor performance, identify risks and opportunities, and support operational and strategic decisions.2. Introduction to Microsoft Power BIArchitecture and main components of the Power BI ecosystem. Power BI Desktop, Power BI Service, and mobile applications. Introduction to the Power BI Desktop interface and its Report, Data, and Model Views. Creation and management of a Power BI project.3. Data Connection, Import, and TransformationConnection to Excel workbooks, CSV files, folders, and other data sources. Use of Power Query within Power BI to clean, transform, and integrate data. Management of data types, missing values, errors, and duplicate observations. Creation of new variables, aggregation of information, and merging and appending of tables. Automation of data update procedures.4. Data Modelling in Power BIPrinciples of relational data modelling. Tables, keys, and relationships. Introduction to dimensional models and the distinction between fact and dimension tables. Design of clear and efficient data models. Creation of hierarchies and use of date dimensions to analyse HR indicators over time.5. Data Visualization and Design of Interactive ReportsPrinciples for developing effective data visualizations. Use of charts, tables, matrices, cards, indicators, and maps. Use of filters, slicers, hierarchies, drill down functions, and interactions among visualizations.Application of data visualization and data storytelling principles to identify and communicate patterns, trends, relationships, differences among groups, and potential anomalies.6. Development of HR Dashboards in Power BIDesign of interactive dashboards for monitoring key HR indicators. Organization of information according to decision making objectives and users’ information requirements. Development of summary and detailed analytical pages. Analysis by time period, organizational unit, professional role, and other relevant characteristics.Development of dashboards relating to workforce composition, recruitment, employee turnover, absenteeism, training, employee performance, and workforce costs.7. Publication, Sharing, and Updating of ReportsPublication of reports through Power BI Service. Sharing and accessing analytical results through web browsers and mobile devices. Data updating and alternative methods for distributing information. Introduction to workspaces, collaboration, and access management.8. Human Resources Business Intelligence Case StudiesIntegrated application of the knowledge and skills acquired during the course to HR datasets and decision problems. Definition of the decision problem, data preparation, development of the data model, identification of relevant KPIs, dashboard design, and communication of the main findings.Descriptive and exploratory analysis of HR data. Identification of trends, relationships, differences among employee groups, and potential factors associated with employee turnover, absenteeism, engagement, and performance. Critical interpretation of the results and development of evidence based recommendations supporting HR decision making.

KNOWLEDGE AND UNDERSTANDINGUpon successful completion of the course, students will be able to:understand the role of Business Intelligence and HR Analytics in supporting operational and strategic decision making in Human Resources management;understand the main stages of the data analysis process, from the definition of the decision problem and the collection, organization, cleaning, and transformation of data to their analysis, visualization, and interpretation;identify the analytical methodologies, performance indicators, and Business Intelligence tools most appropriate to the nature of the HR problem, the available data, and the objectives of the decision making process;understand the fundamental principles of data modelling, the integration of information from different sources, and the development of coherent, transparent, and easily updatable analytical models;understand and interpret key Human Resources indicators relating, among other areas, to workforce composition and evolution, recruitment, employee turnover, absenteeism, training, employee performance, engagement, and workforce costs;understand the principles of data visualization, data storytelling, and the design of interactive reports and dashboards, recognizing the importance of communicating information clearly and effectively to support decision making;understand the potential and limitations of the quantitative methods and technological tools used in HR data analysis.APPLYING KNOWLEDGE AND UNDERSTANDINGUpon successful completion of the course, students will be able to:organize, import, clean, transform, and integrate Human Resources data, preparing them appropriately for analysis;use advanced Microsoft Excel 365 functionalities, Excel Tables, dynamic array functions, and Power Query to develop efficient, transparent, reproducible, and easily updatable analytical procedures;use Microsoft Power BI to connect data from different sources, develop relational data models, and create interactive reports and dashboards;translate a managerial or organizational problem into a coherent analytical process by identifying the decision objectives, relevant variables, appropriate data sources, and performance indicators;develop and interpret Human Resources indicators and Key Performance Indicators, KPIs, using them to monitor performance, compare employee groups and organizational units, and identify trends, anomalies, and potential areas for intervention;apply quantitative, graphical, and exploratory methods to analyse HR data and identify meaningful relationships among qualitative and quantitative variables;design and develop interactive reports and dashboards in Excel and Power BI, selecting the information and visual representations most appropriate to the objectives of the analysis and the requirements of the intended audience;critically interpret analytical findings by recognizing the main assumptions, limitations, and sources of uncertainty, and use the results to develop evidence based recommendations supporting operational and strategic decisions;communicate analytical findings clearly and effectively, transforming complex data into meaningful and actionable information for Human Resources professionals, managers, and other stakeholders involved in decision making.