Recording: MIS

Duration: 48.65 Minutes

Notetaking id:34ba6100-19b0-476f-80bc-e45c90d6378d

ANNOUNCEMENT

Reminders

LECTURE

Practical application for sales analysis:

 Business Law:
o Business law, also known as commercial law or corporate law, is a branch of
legal studies that pertains to the rights, relations, and conduct of individuals and
organizations engaged in commerce, trade, and business activities. It
encompasses a wide range of legal principles and regulations that govern
various aspects of business transactions, operations, and interactions.
 Key areas covered by business law include:
o Contract Law: This area deals with the formation, interpretation, and enforcement
of contracts. It defines the rights and obligations of parties involved in business
agreements.
 Corporate Law: Corporate law addresses the formation, governance, and dissolution of
corporations. It covers issues related to shareholders, directors, officers, mergers,
acquisitions, and corporate finance.

 Commercial Transactions: This area focuses on transactions related to the sale of goods
and services. It includes aspects like sales contracts, warranties, and the Uniform
Commercial Code (UCC).

 Intellectual Property: Business law protects intellectual property rights, including patents,
trademarks, copyrights, and trade secrets. It safeguards the intangible assets of
businesses.

 Employment Law: This area governs the relationship between employers and
employees. It covers matters like employment contracts, discrimination, wages, benefits,
and workplace safety.

 Tort Law: Tort law deals with civil wrongs and liabilities, including negligence, product
liability, and defamation. It addresses situations where one party may be held liable for
harm caused to another.

 Bankruptcy Law: This area addresses financial distress and insolvency. It outlines the
procedures for businesses or individuals facing financial difficulties to seek relief or
reorganization.
 Antitrust and Competition Law: These laws promote fair competition and prevent
monopolistic practices that may stifle competition. They aim to protect consumers and
maintain a level playing field in the marketplace.

 Regulatory Compliance: Business law includes compliance with various regulatory

bodies and government agencies. This ensures that businesses operate within legal
boundaries and adhere to industry-specific regulations.

 A pivot table is a powerful tool in spreadsheet software like Microsoft Excel, Google
Sheets, and others. It allows users to summarize and analyze large amounts of data in a
concise, organized, and interactive format. Here's a detailed explanation:

 Data Organization:

 Before creating a pivot table, you need to have a dataset with structured information.
The data should be organized in columns with headings, and each row represents a
separate record or entry.
 Purpose:

 Pivot tables are used for data summarization, aggregation, and analysis. They are
particularly useful when dealing with large datasets, making it easier to extract
meaningful insights.
 Creating a Pivot Table:

 In spreadsheet software like Excel, you can create a pivot table by selecting your
dataset and then going to the "Insert" or "Data" menu, depending on the software. From
there, you'll find the option to create a pivot table.
 Field Selection:

 After creating a pivot table, you'll be presented with a blank table along with a list of
fields (column headers) from your dataset. You can drag and drop these fields into
different areas of the pivot table.
 Rows and Columns:

 You can place fields into the "Rows" and "Columns" areas of the pivot table. This will
organize the data based on the unique values in those fields. For example, if you're
analyzing sales data, you might place the "Product" field in the "Rows" area to see sales
figures for each product.
 Values:

 The "Values" area is where you specify what kind of calculation or aggregation you want
to perform on the data. This could be a sum, count, average, etc. For example, you
 might want to see the total sales for each product, which would involve summing up the
sales figures.
 Filters:

 Filters allow you to narrow down the data displayed in the pivot table based on specific
criteria. For instance, you might want to filter the data to only show sales from a
particular region or within a certain time period.
 Updating Data:

 If the underlying dataset changes (new data added, existing data modified), you can
refresh the pivot table to reflect those changes. This ensures that your analysis is always
based on the latest information.
 Interactivity:

Pivot tables :
 Often come with interactive features. You can expand or collapse rows and columns,
apply different calculations, and change the layout dynamically. This makes it easy to
explore various aspects of the data.
 Visual Representation:
o Pivot tables can be accompanied by charts or graphs to provide a visual
representation of the summarized data. This can make it easier to spot trends
and patterns.
 Drill-Down:
o You can drill down into the data to see more detailed information. For example, if
you're looking at sales by region, you can drill down to see sales by individual
cities within a region.
 Benefits:
o Pivot tables save time and effort compared to manual data analysis. They allow
for quick and flexible exploration of data, making it easier to draw insights and
make informed decisions.
 Here's how you can use pivot tables in sales analysis:
o Sales Data Preparation:
o Begin by gathering your sales data. This typically includes information on
products, dates, quantities sold, prices, customers, and perhaps other relevant
metrics like regions or sales representatives.
 Opening the Spreadsheet Software:
o Use spreadsheet software like Microsoft Excel or Google Sheets to create your
pivot table. Open a new spreadsheet and paste your sales data into it.
 Creating a Pivot Table:
o Select your data and go to the "Insert" or "Data" menu (depending on your
software) to create a pivot table.
 Selecting Fields:
o In the pivot table options, you'll see a list of the fields (column headers) from your
sales data. Drag and drop these fields into the different areas of the pivot table.
 Rows and Columns:
o Place fields like "Product" or "Category" in the "Rows" area. This will give you a
breakdown of sales by different products or categories. You can also use the
"Columns" area for further categorization.
 Values:
o In the "Values" area, place the field that you want to analyze, such as "Sales
Amount" or "Quantity Sold." This will provide you with the numerical data you're
interested in.
 Aggregation:
o Choose the type of calculation you want for your values, such as sum, average,
count, etc. For example, if you want to see the total sales for each product, you
would choose the sum function for the "Sales Amount" field.
 Filters:
o Add filters to narrow down your analysis. For instance, you might want to focus
only on sales from a specific region or within a certain time frame. This helps you
target specific segments of your sales data.
 Updating Data:
o If you have new sales data to incorporate, simply refresh the pivot table. This
ensures that your analysis is always based on the latest information.
 Comparisons and Trends:
o Pivot tables allow you to easily compare different aspects of your sales data. For
example, you can compare sales performance across different time periods,
products, or regions.
 Visual Representation:
o Complement your pivot table with charts or graphs to provide a visual
representation of the data. This can make it easier to identify trends and patterns.
 Drill-Down for Details:
o If you need more detailed information, you can drill down into the data. For
instance, if you're looking at sales by region, you can drill down to see sales by
individual cities within a region.
 Identifying High Performers and Underperformers:
o Pivot tables can help you quickly identify your top-selling products, most
profitable customers, or high-performing sales representatives. This information
can be used to allocate resources effectively.
 Forecasting and Planning:
o By analyzing historical sales data using pivot tables, you can make more
informed forecasts and create strategic plans for future sales efforts.
Creating a Pivot Table:
 A pivot table is a powerful tool that allows you to summarize and analyze data in a
spreadsheet. Here's how you can create one:
 Select Data:
o Open your spreadsheet program (like Excel or Google Sheets) and select the
data you want to analyze. This data should be organized in rows and columns.
 Insert Pivot Table:
o In Excel, go to the "Insert" tab and select "Pivot Table." In Google Sheets, go to
the "Data" tab and select "Pivot table."
 Select Data Range:
o A dialog box will pop up. Verify that the "Select a table or range" field displays the
correct range of your data. If not, manually input the range.
 Choose Where to Place the Pivot Table:
o Decide whether you want the pivot table to be on a new worksheet or in the
existing worksheet.
 Create Pivot Table:
o Click "OK." This will create a blank pivot table on your selected location.
o Ribbons for Pivot Tables:
o In Excel, once you create a pivot table, a new set of ribbons (menus) appears
that are specific to working with pivot tables:

 PivotTable Analyze (Excel 2016 and later):

o This ribbon allows you to manipulate and analyze the data within your pivot table.
You can change the layout, refresh the data, and perform various calculations.
 PivotTable Tools Options (Excel 2016 and later):
o This ribbon appears when you select the pivot table. It offers additional tools for
customizing the look and behavior of the pivot table.
 Manipulating Pivot Table Fields:
o After creating a pivot table, you can start populating it with data:
 Choose Rows and Columns:
o Drag fields from your data into the "Rows" and "Columns" areas. For instance, if
you're analyzing sales data, you might put "Product" in the "Rows" area and
"Region" in the "Columns" area.
 Add Values:
o Place the numerical data you want to analyze in the "Values" area. For example,
you might want to analyze "Total Sales" or "Quantity Sold."
 Aggregation Functions:
o Once you add a field to the "Values" area, you can choose how Excel should
aggregate the data (e.g., sum, average, count, etc.).
 Filters:
o You can add filters to narrow down the data you're analyzing. For example, you
might want to focus only on sales from a specific region or within a certain time
frame.
  Pivot Table Fields Pane:
o On the right side of the screen, you'll see the "Pivot Table Fields" pane. This is
where you drag and drop fields to build your pivot table.
 Moving and Rearranging Fields:
o You can drag fields between different areas of the pivot table to rearrange the
layout. For example, you can move a field from "Rows" to "Columns" to change
the pivot table's orientation.
 Removing Fields:
o If you no longer need a field in your pivot table, simply remove it by dragging it
out of the "Rows," "Columns," or "Values" areas.
 Updating Data:
o If your source data changes, you can refresh the pivot table to reflect the updated
information.
o By skillfully manipulating

 Changing the Order of Categories:

o In a pivot table, the order of categories can be adjusted to display the data in a
way that is most meaningful to you. Here's how you can do it:
 Row Labels:
o If you want to change the order of categories in the "Rows" section, simply click
on the category label you want to move.
 Drag and Drop:
o Once you've selected the label, drag it to the desired location within the "Rows"
section. You'll see a visual indicator showing where the label will be placed.
 Column Labels:
o If you're working with categories in the "Columns" section, the process is the
same. Click and drag the label to rearrange it.
 Values:
o If you want to change the order of values in the "Values" section, you can do so
by dragging the field to a new position.
Alternative Method for Moving Categories:
 If you find that dragging and dropping categories is not providing the level of control you
need, you can use alternative methods:
 Field List:
o In Excel, you can use the "Field List" pane to control the placement of categories.
o Click anywhere inside the pivot table. On the right side of the screen, you'll see
the "Pivot Table Field List" pane. Here, you can check or uncheck boxes to
include or exclude categories. You can also drag and drop fields between
different areas of the pivot table.
 Pivot Table Options:
o In Excel, you can access "PivotTable Options" by right-clicking anywhere inside
the pivot table and selecting "PivotTable Options."
o In the "Display" tab, you can choose to "Classic PivotTable layout" which allows
for more manual control over the layout.
  Sort and Filter Options:
o For sorting data within categories, you can use the sort and filter options
available within the pivot table itself. This allows you to sort data in ascending or
descending order.
These steps and concepts provide a practical foundation for conducting sales analysis
using pivot tables in Excel. By customizing and organizing data, businesses can gain
valuable insights into their performance and make informed decisions to drive growth.
 Changing Category Order:
o Click and drag categories to rearrange them in the pivot table for a more
meaningful display.
 Adjusting Value Headings:
o Click on a value field and select "Value Field Settings" to change the name of the
heading for clarity and brevity.
 Formatting Numbers:
o Use the "Number Format" option to customize how numerical values are
displayed, including decimal places and thousand separators.
 Reordering Values:
o Click and drag value fields to change their order in the pivot table, providing
flexibility in presentation.
 Business Goal:
o The primary objective of businesses is to increase revenue and demonstrate
growth year over year.
 Comparing Sales Data:
o Use a simple mathematical formula (subtraction) in a calculated field to compare
sales data from different years.
 Creating a Calculated Field:
o Navigate to "Pivot Tools Analyze" and select "Field, Items, & Sets" to create a
calculated field for performing custom calculations.
 Naming the Calculated Field:
o Assign a meaningful name to the calculated field to reflect the specific calculation
being performed (e.g., "Number of Startups").
The practical application and significance of pivot tables in business data analysis,
emphasizing their dynamic nature and the need for data accuracy through regular
refreshing:
 Creating a Calculated Field:
o Use the "Field, Items, & Sets" option to generate a calculated field for performing
custom calculations in pivot tables.
 Performing Subtraction in a Calculated Field:
o For example, to compare revenue between two years, subtract the revenue of
the earlier year from the later year in the calculated field.
 Refreshing Pivot Tables:
o Always click the "Refresh" button after making changes to the underlying data,
especially when collaborating with others, to ensure accurate calculations.
  Using Pivot Charts:
o Pivot charts can be created directly from the "Pivot Table Analyze" menu. They
are linked to the corresponding pivot table and update simultaneously.
 Filtering Pivot Tables and Charts:
o Pivot tables and charts share filters. Changes in one are reflected in the other.
 Applying Pivot Tables to Real-world Examples:
o Pivot tables are highly effective for synthesizing and analyzing large datasets,
making them a valuable tool for tasks such as attendance reporting.
 Understanding Pivot Table Dynamics:
o The relationship between pivot tables and charts is dynamic, allowing changes in
one to directly impact the other, enhancing data analysis capabilities.
 Critical Importance of Data Refreshing:
o Refreshing pivot tables is vital to ensure that any updates or changes in the
underlying data are accurately reflected in the analysis.

Utilizing pivot tables for efficient data analysis and reporting, including specific steps for
handling non-numeric data, transposing information, and ensuring dynamic updates:

 Managing Large Datasets:

o Pivot tables are invaluable for handling massive amounts of data, allowing for
efficient analysis and reporting.
 Utilizing the COUNT Function:
o When dealing with non-numeric data, like words (e.g., attendance categories),
the COUNT function is crucial to obtain the frequency of each category.
 Creating Pivot Tables:
o Pivot tables are created via the "Insert" tab, providing a powerful tool for
organizing and summarizing data.
 Customizing Pivot Table Fields:
o Fields can be placed in various areas like rows, columns, or values to tailor the
report to specific needs.
 Applying Filters:
o Filters can be added to pivot tables, allowing for easy focus on specific data
subsets, enhancing clarity and relevance.
 Transposing Data:
o In cases where data is structured horizontally, transposing it into columns can
greatly facilitate pivot table creation.
 Using the Transpose Feature:
o After copying the data, use the "Paste Special" option and select "Transpose" to
restructure the data for pivot table analysis.
 Dynamic Updating:
o Pivot tables maintain a dynamic connection to the underlying data, ensuring that
any changes made to the source data are accurately reflected in the analysis.
 Optimizing Reporting Efficiency:
o Pivot tables save significant time in data processing, enabling swift generation of
reports with just a few clicks.
 Upcoming Schedule:
o The presenter won't be available for the upcoming sessions, but will return on the
specified date.
 Further Learning:
o Consider exploring additional features such as split sheets, which may offer
additional tools for data manipulation and analysis.