IT TOOLS FOR MANAGEMENT GEI 2024

CHAPTER 1 INTRODUCTION

What is IT?
Information technology (IT) is a set of related fields that encompass computer
systems, software, programming languages, data, information processing, and storage.
IT forms part of information and communications technology (ICT).

An information technology system (IT system) is generally an information system, a

communications system, or, more specifically, a computer system (including all
hardware, software and peripheral equipment) operated by a group of IT users, in a
bunch of IT projects usually refers to the continuous implementation of IT systems.

Internet of things = all the things (information and data) that can be connected to a
software.

Company = place where you have a president (CEO), managers and it englobes
different departments such as the financial, marketing, sales, administrative,
productive, human resources… A company also can have an IT department/area,
whose function is to manage IT projects, these always needing help from other
departments such as financial advice.

IT systems play a vital role in facilitating efficient data management, enhancing

communication networks, and supporting organizational processes across the
companies. Moreover, successful IT projects require meticulous planning, seamless
integration, and ongoing maintenance to ensure optimal functionality and alignment
with organizational objectives.

Data management = collect all the information so that we can make an efficient
decision.

The profit margin is necessary to understand costumer behavior and accept or decline
its offers. First thing that is needed to know when selling a product.

SAP is the most important software in the world, called Enterprise Resource Planning.
ERP initials stand for Enterprise Resource Planning. It is a software that helps
companies manage themselves. SAP problems:

- Technological problems: people didn’t know how to use technology.

- Lack of IT departments.
- Small variety of the software’s that were available.
- Companies had to maintain documents for at least 5 years, and move them.

1
 IT TOOLS FOR MANAGEMENT GEI 2024

IT INFRASTRUCTURE

What is IT infrastructure?
IT infrastructure englobes different areas: software, such as CRM (Customer Relationship
Management, like Salesforce), maintenance, hardware, environment, consumption,
security, network, storage, capacity and assets.

Hardware: on prem vs cloud

ON PREMISE
Pros Cons
Full data control High acquisition cost
Full hard and software control Maintenance costs
Full access reliability Internal knowledge
No operating software costs Full responsibility
Performance and customization Long term reliability

ON CLOUD
Pros Cons
Easy setup Control limitations
Low acquisition and maint. costs Ongoing software costs
Accessibility and integration External dependencies
Updates and security Performance and personaliz. limitations
Flexibility and scability Mandatory internet access

On cloud = companies buy storage that is not in the company itself, you only pay
whatever you are using. If you want, the storage can be increased by an extra price.

On premise = you tend to pay the whole product even if you are not using the storage,
paying its maintenance.

ERP: service systems divided in modules

 Production: producing orders, optimize the usage of the sources, centralizes
data and documents and manages production processes. This module also
manages sales, finances, inventory…
 Inventory: controls productive processes, controls the stock using real-time
data (analyze quantity of raw materials, productions processes and final
products).
 Services: Helps workers with its services, supply chain management…
 Human Resources: updates workers CV’s, offers short courses about different
topics such as cybersecurity, controls extra hours and work, also holidays.
Moreover, controls social security information and sick leaves.
 MRP: connected with production and inventory purchasing.
 Purchasing: controls when the costumer wants to receive the product and the
service, how and when, controls sales… A very important part is called ABC
product (Pareto law = 80% of sales come from 20% of customers, who are the

2
 IT TOOLS FOR MANAGEMENT GEI 2024

best that the company has). ABC is a method of classifying products by priority
based on selected criteria, where A represents high priority, B is medium
priority, and C means low priority.
 Sales: CRM (customer relationship management, which is a system for
managing all of your company's interactions with current and potential
customers).
 Finance: contains all the financial information needed to manage the company
(related to accounting)

CRM: customer relationship management

Software solution that helps businesses manage and optimize their interactions with
customers and potential clients. Its main goal is to improve customer relationships,
increase satisfaction, and boost sales by organizing and automating processes.
Functions:
1. Marketing
- Run Campaigns: Launch targeted marketing efforts to reach specific audiences.
- Generate Leads: Identify and gather information about potential customers.
- Form a Database: Organize customer data for future engagement and analysis.
2. Sales
- Assign Leads: Distribute leads to sales team members.
- Qualify Leads: Evaluate which leads have potential for conversion.
- Convert Leads: Turn leads into actual paying customers.
- Track Opportunities: Monitor and follow up on sales opportunities.
3. Orders
- Deliver Products: Ensure timely delivery of goods or services.
- Produce Invoices: Generate and manage invoices for completed orders.
4. Support
- Manage Cases: Handle customer issues and inquiries efficiently.
- Conduct Trainings: Provide training for customers or support staff.
- Provide Service: Offer solutions and ongoing assistance to customers.
- Develop Knowledge Base: Create resources to help customers solve common
problems independently.

SOFTWARE DEVELOPMENT

1. Software: Coding

 Writing and implementing code to develop applications or systems.

 Focuses on logic, programming languages, and functionality.

2. Database

 Structured system to store, manage, and retrieve data efficiently.

 Examples: SQL, NoSQL, relational and non-relational databases.

3. Team Roles

 Scrum Master: Facilitates Scrum processes and removes obstacles for the team.
3
 IT TOOLS FOR MANAGEMENT GEI 2024

 Product Owner: Defines product requirements and prioritizes tasks for the team.
 Team Leader: Manages and guides the team’s work and performance.
 Software Architect: Designs the overall system architecture and ensures
scalability.
 Operations Manager: Oversees system deployment, maintenance, and
operations.
 Analyst Developer: Analyzes requirements and develops solutions accordingly.
 Developer: Writes, tests, and maintains code for the software.
 Agile Methodology: Flexible and iterative project management approach focused
on collaboration, adaptability, and delivering value quickly.

CHAPTER 2 COPILOT ADOPTION

What is AI?
AI stands for artificial intelligence. AI also uses LLM, that means Large Language Model.
Artificial intelligence is creating new language models.

What is copilot adoption and what does it offer?

Copilot is Microsoft 365’s AI for businesses and an AI assistant for work. In general, it
offers us:

Improved productivity Expanded creativity Trusted security

Focus on what is most Get more deals Staying in control
important
Keeping up with the times Expand the customer base Maintain control and
more creatively security
Effective customer service Think faster Secure access to better
answers

Organizations across industries have shifted from talking about AI to deploying it at scale.
A statistic studio confirms that 70% of users said they were more productive and 68%
improved work quality. They saved 10 hours a month.

Copilot adoption process: questions

 Who do I give the first copilot licenses to?
 Where can my organization gain the most productivity?
 How do I get employees to adopt it quickly
 Why is initial boost important with Copilot?
 When will I start seeing business results?
 How is impact measured?

Generative AI
Generative Ai is a form of AI that can create original content from data and models, high-
level sponsorship is needed to drive the strategic vision and commitment of the
organization. It is also necessary to define scenarios where generative AI can maximize
the impact on business objectives, such as improving customer experience, increasing
productivity or generating new revenue systems.
 High-level sponsorship to drive the organization’s strategic vision and
commitment (define scenarios where generative AI can maximize the impact on
4
 IT TOOLS FOR MANAGEMENT GEI 2024

business objectives and information security to protect data, models and generated
content).

 Change management approach to facilitate cultural change, skills development

and best practices (technical track to ensure good governance, security,
regulatory compliance and end-to-end management, plus alignment and
coordination between tracks to ensure a smooth and effective transition).

Deployment path
- Choose initial workgroups to benefit from Copilot. Allocate necessary
resources and distribute Copilot seats strategically.
- Build a successful team to drive change. Incentivize AI skills development with
Copilot Lab.
- Evaluate Copilot’s impact with Dashboard and Survey Kit. Share results and
good practices with success stories and knowledge communities.
- Expand the use of Copilot to new business scenarios and processes. Recognize
and reward user success.

Methodology of the deployment path

STEP HOW
- Recognize key stakeholders.
- Assess technical and business readiness.
Evaluate - Promote the value and capabilities of Microsoft 365 Copilot.
- Identify key people, high-value use cases, and expected benefits.
- Create a roadmap for Microsoft 365 Copilot deployment.
- Develop use cases and determine pilot/target users.
- Adapt to local rules and policies.
- Plan adoption strategies, change plans, and key performance
indicators.
Prepare - Establish the Copilot Center of Excellence.
- Prepare your Microsoft 365 tenant.
- Implement security and compliance measures.
- Implement data protection strategies.
- Integrate with Critical systems.
- Launch of a pilot project for selected employees.
- Start the initial adoption campaign.
- Collect reviews.
Sail - Validate value through KPIs1
- Adjust the focus for wider deployment.
- Contribute to the implementation decision.
- Prepare the implementation plan.
- Enable Microsoft 365 Copilot for a broad community according
to the deployment plan.
Adopt

1
Key performance indicator, a quantifiable measure of performance over time for a specific objective

5
IT TOOLS FOR MANAGEMENT GEI 2024

- Initiate a comprehensive digital change and adoption campaign

to enable competent use of Microsoft 365 Copilot and maximize
benefits.
- Recognizing the potential and enhancing M365 Copilot with
mote integrations.
Extend - Develop custom Gen-AI solutions for Copilot.
- Assisting in AI-driven business transformation.
- Introduce the Office of Experience Management (XMO) to
measure and report benefits.

How to prepare for Copilot?

Three steps:

 Identify roles and liens of business that can benefit from Copilot. Analyze your
current M365 usage and select teams with high demand for Teams and
PowerPoint.

 Concentrate the deployment of Copilot licenses on those computers. Fast and

visible impact on productivity and creativity.

 Define objectives and metrics to improve in each functional area. Consult the
Scenario Library and measure the value that Copilot brings and adjust the
strategy.

CHAPTER 3 CYBERSECURITY

Layers of cybersecurity
Digital Surveillance. Network monitoring to detect infrastructure exposure,
cybercriminal activity and information leaks or exposure.

 Vulnerability monitoring of infrastructure or utilities.

 Monitoring or stolen passwords.
 Domain Registration, social media and cybercriminal forum monitoring.
 Search for emails published on the internet.

Perimeter protection. Solutions and services:

 Firewalls and protection solutions for published services.

 Protections against denial-of-service attacks.
 Securing remote access to your organization.
Corporate network protection:

 Network segmentation, upgrade of the electronics and secure IoT devices and
communications.
 Cable protection and business continuity.

6
 IT TOOLS FOR MANAGEMENT GEI 2024

Server and Endpoint protection. Protection of workstations and laptops

 Computer encryption, implementation of Endpoint Detection Response (EDR),

privileged access control, information leak protection, app update and system
patching (BOTH).
 Privileged access control, securing access to shared resources, information leak
protection, business continuity (Server protection ONLY).
 Email and navigation protection (Endpoint protection ONLY).

Physical protection and control of access to IT infrastructure:

 Organization access control.

 Data Center (DPC) Protection.
 Protection of public areas.
 Securing access to physical documentation.
 Protection and prevention systems.
 Business continuity.

User protection. Protection of users of the organization’s information systems:

 Education and awareness.

CHAPTER 4 UX/UI DESIGN

UX and UI meaning
UX design refers to the interaction design, wireframes and prototypes, information
architect, user research and scenarios.
UI design makes reference to visual design, colors, graphic designer, layouts and
typography.
Laws in UX/UI
Collection of good practices or fundamentals that are based on principles of human
psychology, ergonomics and user behavior with the aim of achieving the best user
experience possible. The application of these laws is called design patterns, since it
addresses both areas: the interface and the overall experience.

Knowing these affects allows designing a graphic solution that offers the best possible user
experience. In addition, this design applies to both physical and digital products, so
understanding these principles will make better the design of them. We can find some
laws that help us understand and design user interfaces and experiences:

o Hicks Law. User’s response is directly proportional to the number of options

and stimuli they receive, meaning this that when they have more options, they
will take longer to respond.

7
IT TOOLS FOR MANAGEMENT GEI 2024

o Fitts Law. Allows the calculation of the time it takes a user to point out a target
based on size and distance.

o Millers Law. Average person can only keep 7 items (2+-) in their working
memory.

o Jakob Law. Users prefer sites that work similar to those they use usually.

o Proximity Law. Objects that are close to each other tend to cluster together
(they are related).

o Aesthetic Usability. An aesthetically pleasing design is perceived more useful.

o Prägnanz Law. People identify, interpret, and perceive ambiguous or complex

images as the simplest know form possible.

o Law of similarity. Elements that are visually similar are perceived as related.

o Serial Position Effect. Users tend to remember the first and last items in a series
better.

o Von Restorff's Law or Effect or Isolation. When you show multiple similar
objects, you're more likely to remember the one that differs from the rest.
People remember more the ones that stand out from the others.

o Zeigarnik Effect. Elements that are visually similar are perceived as related.

o Tesler Law. For any system there is a certain amount of complexity that cannot
be reduced.

o Geslat Theory.

8
IT TOOLS FOR MANAGEMENT GEI 2024

CHAPTER 1 THE INFORMATION SYSTEM IN THE COMPANY. THE BASIS

OF THE IS: DATABASES

Introduction: Data and information

Data: Any element that serves us as a starting point to perform a calculation or a
process. Represented by numbers, letters, other characters… Numbers or values that
by themselves have no meaning. They represent a fact, event or element of the real
world. Example = 28, “Peter” …

Information: Result of data processing. It is the data already processed and ordered,
so that they have a meaning for the person who receives them. Information = Data +
Context (adding value) + Utility (decreasing uncertainty). Example: Name: Peter, Age:
28, Salary: 5200.

Knowledge: A mixture of experience, values, information and “know-how” that serves

as a framework for the incorporation of new experiences and information, and is
useful for action. The fundamental objective is that it helps to MAKE DECISIONS. This
transformation occurs thanks to: Comparison with other elements, prediction of
consequences, search for connections, etc.

Data is the new oil. 73% of Spanish companies are thinking of using Big Data as a way
of doing business, and, by 2025, practically all companies will have implemented this
technology in some of their areas.

Data capture
 Generated by individuals: e-mail (200 million every minute) or WhatsApp
messages, Facebook…
 Data transactions: Billing, calls or transactions between accounts. All payments
we make with credit cards.
 E-marketing and web: generated when browsing the Internet. Mouse
movements are recorded through heat maps.
 Machine to Machine (M2M): Technologies that share data with devices: meters,
temperature sensors, GPS…
 Biometrics: Standardized measurements of living beings or biological processes.
Security, defense, intelligence services…
 Transformation: extracting the raw data from the data mentioned in the
capture, apply a series of cleaning and structuring transformations of the
information and finally load them as database. Known as ETL process.
Data types
 Structured data: Lenght and format are well defined, such as dates, numbers or
character strings. They are stored in tables. Example = relational databases.
 Unstructured data: Characterized by not having a specific format. Data in the
format in which they were collected, lacking a specific format. They cannot be
stored in a table. Example = PDF.
 Semi-structured data. Not limited to specific fields, but contains markers to
separate the different elements. It is not regular enough to be managed in a
standard way. Example = HTML.

9
 IT TOOLS FOR MANAGEMENT GEI 2024

Storage
 SQL (relational): Normalizes data in tables consisting of rows and columns.
Defines tables, rows, columns… Imposes the referential integrity between tables.
 No SQL (non-relational): Variety of data models, including documents, graphs,
key-values…

Data analysis
With data, we can get useful information that helps us personalize offers, optimize
campaigns, new products, improve customer service… Data has 5 characteristics:

 Volume. Size of the big data set. Most important one.

 Variety. Data comes from numerous resources. These entries (external or
internal) can cause variance in data set. External is hardly ever structural.
 Velocity. Heavy increase in data means it should be analyzed more swiftly. The
production rate of data is notably high.
 Veracity. Accuracy of the data, acquired from correct resources and provided
with security. Only authorized people should have access to it.
 Value. Result generated after the procedures and this should enrich the process.

Data is also used in mathematics (calculus, probability, statistics), computer science

(programming, databases) and business administration (accounting and finance).

Business Analytics analysis

It has the goal of increase the business value and performance. Three steps:

 Descriptive Analytics. What happened? Find possible business-related

opportunities.
 Predictive Analytics. What and why is happening and what will happen? Predict
opportunities in which the firm can take advantage.
 Prescriptive Analytics. How shall it be handled? Allocate resources to take
advantage of the predicted opportunities.

Why using data now?

The reasons for this new age are varied and complex:

o Computing perfect storm. Big Data analytics are the natural result of four
major global trends: Moore’s Law, mobile computing, social networking, cloud
computing…

o Data perfect storm. More volume, velocity and variety—the three Vs—of data
that has arrived in unprecedented ways. This perfect storm of the three Vs
makes it extremely complex and cumbersome with the current data
management and analytics technology and practices.

o Convergence perfect storm. Traditional data management and analytics

software and hardware technologies, open-source technology, and commodity
hardware are merging to create new alternatives for IT and business
executives.

10
 IT TOOLS FOR MANAGEMENT GEI 2024

CHAPTER 2 INFORMATION SYSTEMS AND DATABASES

Information system and its components

The information system provides support to the production system and, at the same time,
extracts data, information and knowledge. The information system includes the
technological tools or infrastructure (computer systems). Information and technology
constitute a binomial.

The IS of an organization encompasses all those tasks and human services and material
resources that are oriented to provide the information of the decision system and the
operating or production system.

Information system: formal set of processes that, operating on a collection of data

structured according to the needs of the company, collects, elaborates and distributes
the necessary information (or part of it) for the operation of the company and for
corresponding management and control activities, supporting, at least in part, the
decision-making processes needed to perform the company’s business functions and
processes in accordance with its strategy.

Summarized, it is a set of people, data, processes and information technology that interact
to collect, process, store and provide the information necessary for the proper
functioning of the organization. The following components make up an IS:

 People: Managers, users, analysts, designers…

 Data: Raw material to create useful information.
 Processes: Business activities that generate information.
 Information technologies: hardware and software that supported the previous
three components.

Relational database
A database is a series of organized and interrelated data, which are collected and
exploited by information systems. They use the SQL language (Structured Query
Language). Steps of databases:

 Requirements analysis: identify its purpose. Understanding it will determine

your choices throughout the design process. Be sure to look at the database
from all perspectives.

 Organization of data in tables. Information stored in tables related to each

other, relationship established through a common field and information
queried through these relationships.

BBDD: Components and entity/relationship models

BBDD components are fields, records and tables.

 Fields: Elemental information of the same type (text, numbers, date, OLE…).
 Record: Grouping of fields corresponding to the same element.

11
 IT TOOLS FOR MANAGEMENT GEI 2024

 Table: Set of records, each one of an element.

Entity/relationship model: specification of the primary keys and analysis of the

relationships.

o Entity: about which we want to store information.

 Entities are composed of attributes that define them (products, sales,

customers…).
 Each table must contain its own data on a unique subject.
 Each table must have a primary key field. If not possible sequential
number.
 Key element requirements: unique, knowledgeable, minimum size
(easy). Used in indexes, relations, integrity checks, etc.

o Relationship: association between entities.

 3 types of relationships:
 • One to one (1:1): Student  Academic record
 • One to many (1:N): Company  Employees
 • Many to many (M:N): Students  Subjects

Switch from the E/R scheme to tables

For each schema entity, a table will be created with as many fields as attributes the entity
has.

One-to-one relationship
When between 2 tables there is a unique correspondence between each of their records.

Relationship one is to many

A record in one table is related to several records in the other table.

 Primary key.
 Foreign key. The primary key appears as an attribute.

A supplier can supply more than one product, but each product has only one supplier.

Relationship many to many

It associates several elements of a table with several elements of the other table.
Unfortunately, it is not possible to directly implement this type of relationship in a
database. You must split it into two one-to-many relationships. Normally you must
decompose it into an intermediate table (link table, join table, ...) to link the 2 original
tables.

 Primary key of the orders table.

12
IT TOOLS FOR MANAGEMENT GEI 2024

 Primary key of the products table.

An order can have many products, and each product can appear in several orders.

Database manager
A database manager (DataBase Managenent System) is a system that allows the
creation, management and administration of databases, as well as the choice and
handling of the structures necessary for storing and searching information in the most
efficient way possible. Nowadays, there are a multitude of DBMS, most of them
relational. One of the most widely used database managers is Microsoft Access.

Microsoft Access
It is a relational database management system created by Microsoft (DBMS) for
personal use by small organizations. It allows the creation of database files that can be
consulted by other programs. Among the main noteworthy functionalities, we can
indicate that:

 It allows the creation of indexed data tables.

 Modify data tables.
 Relationships between tables (creation of relational databases).
 Creation of queries and views.
 Cross-reference queries and action queries (INSERT, DELETE, UPDATE).
 Forms and reports.
 Programming environment through VBA.

CHAPTER 3 DATABASE CREATION AND REFERENTIAL INTEGRITY

Creating a database in Access

To create a database in Access it is important to follow the following steps:

1. Start the program.

2. Create a new blank database and indicate a safe location. Make sure that the
extension is compatible with all devices.
3. Create the database structure.
4. Enter the data.
5. Create queries, reports and forms.

University database
We want to create a DB to store all the university's enrollment information: subjects in
which each student is enrolled in a course and his or her grade. What do we need?

- TEACHERS: DNI, Name, Last Name, Telephone, E-mail.

- STUDENTS: ID, First Name, Last Name, Date of Birth, Sex, Street, City, Province,
Postal Code, Telephone, E-mail.
- SUBJECTS: Subject Code, Name, Description, Type, Credits, Credits.
- ENROLLMENT/QUALIFICATIONS: Qualification.

13
 IT TOOLS FOR MANAGEMENT GEI 2024

Creating a tables links in Access

Once the main structure of our database has been created, it will be necessary to establish
the links between the tables. To do this, the main keys must be associated to the
tables with which they are related, according to their type: 1:1, 1:N or M:N.

To be able to carry out this relationship, secondary keys with identical characteristics must
first be created, in order to link them to the main key.

Search Wizard
Once an identical field has been created to which the other table is to be associated, i.e.
the secondary field, the search wizard option must be enabled. The search wizard
delimits the following steps:

1. Select the table to be referenced.

2. Indicate the fields to be included in the search.
3. Define the order in which the data is presented.
4. Specify the data to be displayed in the search results.
5. Enables referential integrity, if necessary.

Referential integrity
Referential integrity is a set of rules used by all database managers in their relationships
to ensure that the database maintains information consistently at all times. When
referential integrity is required, the system will ensure that the user cannot perform
any action that compromises the consistency of the database. Types of referential
integrity:

 Cascading deletion: when a record is deleted in the parent table, all related
records in the child table are automatically deleted.
 Restricted deletion: it is not allowed to delete a record from the parent table if
there are related records in the child table.

DATABASE QUERY CREATION

What is a query?
A query is a question that we formulate to a database to extract information. It also
allows us to perform calculations with the selected data.

14
IT TOOLS FOR MANAGEMENT GEI 2024

We collect data, formulate a query and obtain information. We observe the information
and obtain knowledge. Type of queries:

 Selection queries
 Parametric queries
 Total calculation queries
 Calculated field queries
 Action queries

Selection queries
Select from one or more tables the information of interest and display it in the order set
by the user. Criteria:

 Like or =: Searches for records that exactly match a value.

 <>: Filter records that do not match a value.
 > or <: More or less than.
 Between: Searches for values within a specific range. We can also use “?” or “*”
when we do not know the exact value.
 “?” implies one character
 “*” implies many characters.
 We can also use “and” or “or” to specify more than one condition.

Parametric queries
A parametric query is a type of query that prompts the user to provide a value or
criterion before the query is executed.

This approach is useful when you want to work with dynamic or customizable data
without changing the query structure. Parameters can be pre-created or entered
directly as criteria.

Total calculation queries

We use them when we want to group or count results of a query. Calculations you can
do:

 Group by: Groups data based on the values in a specific field, allowing
aggregation functions to operate on each group separately.
 Count: Count the number of the same elements in a field.
 Add: Adds more numbers in a field.
 Average: Sets the average of the numbers found in that field.
 Max: Sets the highest value entered in that field.
 Minimum: Sets the lowest value entered in that field.

15
 IT TOOLS FOR MANAGEMENT GEI 2024

 First: Sets the first value entered in that field. Last: Sets the last value entered
in that field.

Action queries
Action queries are those that do not return any records, they are in charge of actions such
as adding, deleting and modifying records. Be careful, these queries can cause
irreversible changes.

• Add records: this operation allows you to add records from one table to
another with the same structure. It is also possible to add some data from
other tables even if they do not have the same structure.

• Delete records: this operation allows you to delete many records in a single
operation.

• Update data: this operation allows you to modify the data of one or more
fields. If the field contains numeric data, it is possible to update it with
operations involving the table data itself.

• Create a new table: this operation allows you to create a new table and store
the selected data in it. Its most important utility is that, from a query that acts
on several tables at the same time, a new table can be created with data from
each of them.

Calculated field in a query

They allow you to create table fields that calculate values. Calculations can include the
values of fields in the table itself, as well as built-in Access functions.

The Expression Builder provides easy access to the names of database fields and controls,
and to many of the built-in functions available for writing expressions. It is activated
with the Generate button.

 Instructions and Help link.

 Expression box. Expression items can be added by double-clicking on items in
the item lists.
 Expression Elements list by clicking on an element type. You can see its
categories in the Expression Categories list.
 Expression Categories list by clicking on a category. You can see its values in the
Expression Values list.
 Expression Values list Double-click on a value to add it into the Expression box.
 Help and information about the selected expression value.

16
IT TOOLS FOR MANAGEMENT GEI 2024

EXCEL THEORY

What is Excel?
Excel is a spreadsheet application that allows us to create workbooks with calculations,
compare data, perform statistical calculations, create graphical representations, design
forms, create organizational charts. It also has other functions like:

 Analyze and simulate scenarios.

 Manage data.
 Link with other programs.

Excel spreadsheet

Excel cell categories

 General: The cell has no specific format.
 Number: Numeric format.
 Currency Monetary values with symbols.
 Date: Numbers representing dates and times.
 Percentage: Values in percentage format with symbols.
 Text: All cell values are treated as text, even numeric ones.
 Custom: Allows creating custom structures.

Cell formatting and references

Conditional Formatting = rules to highlight cells, percentage rules, data bars, color
scales, icon sets, new custom rule.

References = they allow us to assign the same value to one cell as another without
manually entering the value. Three types:

 Relative references: They change of position when the formula is moved.

Example: =B3.
 Mixed references: Fix one of the two attributes (row or column). Mixed by row:
=B$3. Mixed by column: =$B3
 Absolute references: Fix both the column and the row. Example: =$B$3.

17
 IT TOOLS FOR MANAGEMENT GEI 2024

Name manager
Assigning names allows us to assign a word to a cell, enabling the use of absolute
references without remembering the cell number.

Name manager tutorial

Charts in excels
We can find several types of charts in excel. The most common ones are bar charts, line
charts, XY charts, pie charts, combo charts, sparklines, map charts…

 Combo charts. Helps us emphasize the similarities or differences between the

data series shown.
 Sparkline. Small charts that fit inside individual cells on a sheet. Because of
their condensed size, sparkline can reveal patterns in large data sets in a
concise and highly visual way. Example = trends in a series of values, such as
periodic increases or decreases and business cycles.

Functions in Excel
Excel functions are formulas or actions predefined by the program, which allow us to
perform tasks in a simpler way. Default format: =Function_name (Parameter1;
Parameter2; ...). Types:

 Financial functions: =RATE (interest rate per period), =PV (actual value of an
investment), =NPV (current value of non-constant payments), etc.
 Mathematical functions: =SUM, =RAND (), =FACT, =LN, =SIN, =POWER,
=PRODUCT, =SQRT, etc.
 Statistical functions: =MIN, =MAX, =AVERAGE, =MEDIAN, =LINEST, =STDEV,
=VAR, =COV, etc.

18
IT TOOLS FOR MANAGEMENT GEI 2024

 Date, logic and text functions: =IF(logical_test; value_if_true; value_if_false),

=VLOOKUP(lookup_value; table_array; col_index_num), =TODAY (), =NOW (),
etc.

Subtotals
Returns a subtotal in a list or database. It is usually easiest to create a list with
subtotals using the Subtotals command in the Schema group of the Data tab. Once you
have created the list of subtotals, you can modify the SUBTOTALS formula and change
the list.

Goal seeks
Use Find Target to find the result you want by adjusting an input value. For example,
suppose you need a loan. You already know how much money you want, how long you
want to take to repay the loan, and how much you can afford to pay each month.

Scenario Manager
A scenario is a set of values that Excel saves and can automatically substitute into the
cells of a spreadsheet. You can create and save several
sets of values in a spreadsheet and then switch to any of
these new scenarios to see the different results.

Pivot tables

A pivot table is an advanced tool for

calculating, summarizing and analyzing data
that allows you to see comparisons,
patterns and trends in the data.

In pivot tables we can create new

calculated fields, new items and custom
filters.

Forms
A form is an environment where users can easily enter and retrieve information.
Forms in Excel can contain controls, such as boxes or drop-down lists, that can make it
easier for people using the spreadsheet to specify or edit data.

19