Ateneo - UPO

The course provides an introduction to the advanced use of Microsoft Excel 365 for data analysis, model development, and business decision support.

Particular attention will be devoted to the new dynamic functionalities available in Excel 365, the use of Excel Tables, and the design of flexible, automated, and easily updatable models. Students will also be introduced to the development of interactive dashboards for the effective visualization and communication of results, as well as to the creation of simple interactive web pages.

The course will cover the use of Power Query to import, transform, integrate, and automatically update data from different sources. It will also introduce the main tools for data analysis and selected forecasting models that can be implemented directly in Excel.

The topics will be developed through practical applications and the discussion of case studies relevant to business decision making. By the end of the course, students will be able to organize and transform data, build dynamic models, develop interactive dashboards, and use Excel as an integrated tool for data analysis, forecasting, and decision support.

The course is particularly recommended for students interested in applications in management, finance, and data analytics.

The course primarily uses materials prepared by the instructors and YouTube tutorials.Excel Business Analytics (Statistics & Math Model Building): a free course on youtube H. Guerrero Excel Data Analysis: Modeling and Simulation, Second Edition, Springer (2019) Additional useful reference: Microsoft Excel 365 Bible, Michael Alexander, Richard Kusleika, John Walkenbach, March 2022 The course is based on the use of Excel 2021 for Windows. The instructions and teaching materials are specifically tailored for Windows users. Adaptability to other operating systems (Linux or macOS) is not guaranteed.

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

1. use Microsoft Excel 365 at an advanced level as a tool for data analysis, quantitative modelling, and managerial decision support, making effective use of dynamic functionalities, Excel Tables, and tools for importing, transforming, and visualizing data;
2. translate decision problems into structured quantitative models by identifying the relevant variables, formulating appropriate assumptions, and using data and analytical tools to compare alternative scenarios, assess risks and opportunities, and support the decision-making process;
3. critically analyse and interpret quantitative results, assess their relevance for business decisions, and recognize the main assumptions and limitations underlying the models employed;
4. communicate the results of quantitative analyses clearly, concisely, and effectively by using appropriate tables, charts, performance indicators, and interactive dashboards tailored to both technical and non-technical audiences;
5. develop flexible, transparent, and easily updatable analytical models and tools for addressing relevant problems in management, finance, and business decision making.

Basic knowledge of Excel: basic operations and functions, use a worksheet

Lectures including both theory and exercises using Excel 365. Each student is required to have a laptop with Office 365.

Additional information and teaching materials will be provided during the lectures and made available on the course platform at www.dir.uniupo.it.Attendance at lectures is strongly recommended. Instructions, practical exercises, and teaching materials are specifically designed for users of PCs running the Windows operating system.Lectures will take place in a standard classroom rather than a computer laboratory. Students will therefore be required to bring their own laptop and use it during the learning activities.Students with disabilities, Specific Learning Disorders, SLDs, or Special Educational Needs, SENs, may request dedicated services, support, and specific learning arrangements by contacting the University Staff responsible for Student Careers and Student Services and consulting the dedicated webpage on the University website:https://uniupo.it/it/servizi/servizi-studentidisabili-e-dsaAfter contacting the relevant University Staff, students with disabilities, SLDs, or SENs may contact the course instructor to discuss the implementation of the approved measures and the corresponding examination arrangements.

Active participation in classroom activities.Completion of a final project, either individually or in groups of no more than three students, accompanied by a written report and a presentation of the results.The final project is intended to assess students’ understanding of the more advanced topics covered in the course and their ability to apply the knowledge and tools acquired to the analysis of multifaceted and complex problems.The written report and presentation should demonstrate the ability to understand, organize, and critically interpret the data under investigation, as well as to extract relevant and meaningful information through the development of a clear, effective, and interactive dashboard.Particular attention will be devoted to the ability to identify, analyse, and communicate significant relationships among the qualitative and quantitative variables under investigation by using appropriate analytical and data visualization tools. Where relevant, the project should also assess whether these relationships can be used to develop forecasting models and analyse possible future trends.The assessment will consider the methodological accuracy of the analysis, the quality and effectiveness of the interactive dashboard, the ability to interpret the results critically, and the clarity with which the main findings and their implications are communicated.

Below is the complete English version.

The course is structured into two closely integrated parts.

The first part focuses on spreadsheet modelling and the use of advanced Microsoft Excel 365 tools to organize, import, transform, and visualize data. The objective is to provide students with the skills required to develop clear, reliable, flexible, and easily updatable quantitative models, as well as to summarize and communicate information effectively through charts and interactive dashboards.

The second part focuses on quantitative data analysis and the use of Excel as a tool to support decision making. Techniques for analysing distributions and trends, investigating relationships among qualitative and quantitative variables, performing regression analysis, analysing time series, and developing forecasting models will be introduced. Monte Carlo simulation methods and optimization techniques will also be examined to assess uncertainty, compare alternative scenarios, and support business decisions.

The topics will be developed through practical applications and case studies involving business, managerial, and financial problems.

Part I, Spreadsheet Modelling, Data Organization, and Data Visualization

1. Introduction to Spreadsheet Modelling: The role of quantitative models in business decision making. Principles for designing clear, reliable, transparent, flexible, and easily updatable Excel models. Organization of inputs, calculations, and outputs. Use of relative, absolute, and mixed cell references. Error checking and model documentation.

2. Data Organization and Structured Data Management: Structuring and organizing datasets. Use of Excel Tables and structured references. Sorting, filtering, and dynamic data management. Application of data validation rules and development of interactive models.

3. Advanced Dynamic Array Functions in Excel 365: Use of the main dynamic array functions for extracting, transforming, and organizing data. Creation and management of dynamic arrays. Applications of functions for filtering, sorting, searching, selecting, and aggregating data. Introduction to the development of custom functions.

4. Data Import, Cleaning, and Transformation Using Power Query: Importing data from external sources. Identifying and managing missing values, duplicate observations, and errors. Cleaning, transforming, and integrating datasets from different sources. Automating data import and update procedures.

5. Data Visualization and Communication: Principles for the clear and effective presentation of information. Selection and construction of appropriate charts according to the nature of the data and the objectives of the analysis. Use of dynamic and interactive charts. Identification and communication of trends, relationships, and anomalies.

6. Development of Interactive Dashboards: Design and development of dashboards for summarizing, exploring, and communicating results. Integration of Excel Tables, dynamic arrays, key performance indicators, charts, controls, and interactive tools. Principles of visual organization and effective communication of information to both technical and nontechnical audiences.

Part II, Quantitative Analysis, Forecasting, Simulation, and Optimization

7. Statistical Analysis and Data Summarization: Introduction to quantitative data analysis using Excel. Calculation and interpretation of the main measures of location, dispersion, and distributional shape. Means, quantiles, variance, standard deviation, skewness, and kurtosis. Analysis of frequency distributions and construction of histograms. Introduction to fundamental concepts of probability.

8. Analysis of Trends and Relationships Among Variables: Identification and interpretation of trends, changes over time, and patterns in data. Analysis of relationships among qualitative and quantitative variables. Use of tables, charts, and statistical indicators to identify associations, dependencies, and significant differences.

9. Linear Regression and Cross Sectional Data Analysis: Introduction to simple and multiple linear regression models. Estimation and interpretation of model coefficients. Assessment of the explanatory power of the model. Residual analysis and discussion of the main model assumptions. Applications to business, economic, and financial data.

10. Time Series Analysis and Forecasting Models: Analysis of the evolution of data over time. Identification of trends, seasonality, and irregular components. Use of moving averages, smoothing techniques, and regression models for forecasting. Development, evaluation, and comparison of forecasting models. Interpretation of forecasts and analysis of potential future trends.

Additional Topics, Subject to Time Availability

11. What If Analysis and Scenario Analysis: Use of What If Analysis tools to assess the effects of changes in model inputs on model outputs. Sensitivity analysis, Goal Seek, Data Tables, and comparison of alternative scenarios. Applications to business decision making.

12. Monte Carlo Simulation and Uncertainty Analysis: Introduction to Monte Carlo simulation. Generation of random scenarios and representation of uncertainty in decision models. Analysis of the distribution of possible outcomes and assessment of risk. Applications to business, managerial, and financial case studies.

13. Optimization Using Excel Solver: Formulation of decision problems through objective functions, decision variables, and constraints. Use of Excel Solver for optimization and decision selection. Analysis and interpretation of optimal solutions. Applications to resource allocation, planning, and the evaluation of business scenarios.

14. Management of Large Datasets Using Power Pivot: Introduction to the Excel Data Model and Power Pivot. Creation of relationships among tables and analysis of large datasets. Development of indicators and reports using the Data Model.

KNOWLEDGE AND UNDERSTANDING
Upon successful completion of the course, students will be able to:
1. understand the role of quantitative models and data analysis in supporting business and managerial decision making;
2.identify the quantitative models and analytical methodologies most appropriate to the nature of the problem, the available data, and the objectives of the decision making process;
3. understand the fundamental principles underlying the development of clear, reliable, transparent, flexible, and easily updatable Excel models;
4. understand and interpret the main techniques used for statistical data analysis, trend analysis, regression, and forecasting;
5. understand the role of deterministic models, sensitivity analysis, scenario analysis, simulation, and optimization in evaluating alternative decisions and addressing uncertainty;
6. understand the potential and limitations of the quantitative methodologies and analytical tools employed.

APPLYING KNOWLEDGE AND UNDERSTANDING
Upon successful completion of the course, students will be able to:
1. organize, structure, import, clean, and transform the data required to develop a coherent quantitative model;
2. translate a business or managerial problem into a quantitative model by identifying the relevant variables, formulating appropriate assumptions, and defining the relationships among inputs, calculations, and outputs;
3. use advanced Microsoft Excel 365 functionalities, including Excel Tables, dynamic array functions, and Power Query, to develop flexible, transparent, and easily updatable models;
apply statistical and quantitative tools to summarize data, identify trends, and analyse significant relationships among qualitative and quantitative variables;
4. develop, evaluate, and interpret regression and forecasting models applied to business, economic, and financial data;
5. use deterministic models, sensitivity analysis, scenario analysis and, where applicable, simulation and optimization techniques to compare alternative decisions and assess their potential outcomes;
6. critically interpret analytical results by considering the underlying assumptions, uncertainty, and the main limitations of the model;
present and communicate results effectively through tables, charts, key performance indicators, and interactive dashboards, highlighting the information most relevant to the decision making process.