Ch 2 dav

What Is a Pivot Table?

When spreadsheets start expanding beyond a few rows and columns, it

becomes difficult to keep track of the data. And when you lose track of the
data, you lose track of the meaning behind it. That's where a pivot table
comes in—it filters and summarizes your data based on criteria of your
choosing.

Let's say you diligently log all your expenses across the whole year in a single
spreadsheet. At the end of the year, when you sit down to review, you're going
to have a tough time sorting through hundreds of entries. This is what is called
a flat data—all you're seeing is a sea of rows and columns. To gain more
insight and meaning from the data, you need to see it dynamically. For
example, you might want to see how much money you spent on rent and
utilities in the last quarter. Using a pivot table, you can do just that.

And that's just a simple example. A pivot table can be used to analyze an
unlimited variety of data. That means that you won't have to create a new
spreadsheet for each analysis—you can use the same data and manipulate it
in the pivot table to get new insights each time.

How to Create a Pivot Table in Excel Online

 Here's a quick overview of how to use pivot tables (we'll dive deeper in the
next section).

Step 1: Open the Excel Online sheet and select all cells containing the data
you want to look at.

Step 2: Select Insert > PivotTable.

Step 3: From the pop-up, select New Worksheet and click OK.

Step 4: In the pivot table editor, drag the rows and columns that you want to
summarize to the appropriate box.

Step 5: In the Values section, select the fields that have the values you want
to add or calculate.

Step 6: If you only want to display values that meet certain criteria, use
the Filters section.

How to Create a Pivot Table chart

1. We must first select the data range to create a PivotChart in Excel.

2. Then, click the “Insert” tab within the ribbon.
3. Then, select the “PivotChart” dropdown button within the “Charts”
group. So, for example, if we want only to create a PivotChart,
choose “PivotChart” from the dropdown or if we are going to make
both a PivotChart and PivotTable, then select “PivotChart &
PivotTable” from the dropdown.
4. Here, we have selected and created both a PivotChart and
PivotTable. Then, make PivotChart, a dialog box appears, similar to
the “Create PivotTable” dialog box. It will ask for the options:
Table/Range or Use an external data source. By default, it selects
“Table/Range,” which will ask where to place a PivotTable chart.
Here, we always need to choose a new worksheet.
5. Clicking “OK” will insert PivotChart and PivotTable into a new
worksheet.
6. “PivotChart Fields” task pane appears on the left, containing various
fields: Filters, Axis (Categories), Legend (Series), and Values. Next, in
the PivotTable Fields pane, select the “Column” fields applicable to
the Pivot Table. Then, we can drag and drop, i.e., “Sales_person” to
the “Rows” section, “Region” to the “Columns” section, and “Sales”
to the “Values” section.
Then, the chart looks as given below.
7. We can name this sheet “SALES_BY_REGION” by clicking on the
PivotTable. Then, we can change the chart type in the “Change
Chart Type” option based on choice under the “Insert” tab. Select
“PivotChart, and” the “Insert Chart” popup window appears. Under
that, select the “Bar” and “Clustered” bar chart. Then, right-click on
the PivotChart, and choose “Change Chart Type.”
8. Under “Change Chart Type,” select “Column.” Then, select the
“Clustered Column” Chart.

9. Now, we can summarize the data with the help of interactive

controls present across the chart. For example, when we click on the
“Region” filter control, a search box appears with the list of all the
regions, where we can check or uncheck boxes based on the choice.
10. On the corner of the chart, we have an option to format chart
elements based on the choice.
11. We have an option to customize the PivotTable “Values.” By
default, Excel uses the SUM function to calculate the values
available in the table. Suppose we select only region values in the
chart; it will display each region’s total SUM sales.
12. We have an option to change the Excel chart style by clicking
the “Style” icon on the corner of the chart.
13. This chart will update when we change data sets in a
PivotTable. The following steps can optimize this option: right-click
and select the “PivotChart Options”.
In the above chart options, go to the “Data” tab and click on the
checkbox “Refresh data when opening a file.” So that refresh data gets
activated.

What Are Slicers in Excel?

Slicers in Excel are implemented along with the Excel tables and pivot tables to act as
one-click software filters to filter out the required information from an enormous
collection of data within a fraction of seconds.

By the end, you will have a comfortable understanding of the Excel Slicers and be
capable of designing a few slicers in Excel all by yourself.

Slicers in Excel are software filters used along with excel tables or pivot tables over a
large amount of data. Not just filtering out the data, but slicers also help you with an
easy understanding of the information being extracted and displayed on the screen.

Microsoft Excel Slicers is compatible with Windows and Macintosh operating systems.
Moving forward, you will understand how to build Excel slicers in practical mode.

Slicing and dicing of data and pivot tables

Step 1: Prepare your data

Perhaps the most important step in using Excel pivot tables is to carefully
organize your data. The easiest way to do this is to use Excel tables, which let
you add rows that will be included with your pivot table whenever you hit
refresh. But at the very least, you want your data to be in tabular form with
informative column headers and with consistent data within columns.

For example, let’s say that you want to analyze sales information for your
company. You have six salespeople who sell three products across a number
of regions. Your products are tailored for each customer and so pricing varies.
Here’s a sample table with fictional information to give you an idea of how
data can be organized with a pivot table in mind.

The table was created simply by first entering the data, then selecting the
entire range, and then going to Insert > Table. Again, you don’t have to take
this step but it’s recommended if you want to add more rows of data later and
make it easier to update your pivot table.

Mark Coppock/Digital Trends

Mark Coppock/Digital Trends
Step 2: Try a recommendation

Excel is full of nifty tricks to make working with data easier, and whenever
possible it will try to guess what you want to accomplish and then
automatically carry out a few steps. This helpful nature is demonstrated here
by the Recommended PivotTables tool, which takes a look at your data and
offers up some logical options on how to analyze and present things.

To use a recommended pivot table, simply go to Insert > Recommended

PivotTables. Excel will present a few options for you to consider. In our
example, Excel offers to create 10 different pivot tables that take a look at a
number of different angles on our sales data. Note that how you label your
columns matters; Excel reads these headers and offers up recommendations
that make the most sense. If you want to look at sales prices, for example,
don’t use the term “cost,” because Excel will base its recommendation
accordingly.

One recommendation is “Sum of Total Sales by Customer.” If we choose that

option, then Excel will proceed to create the pivot table.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

Notice that the pivot table is displaying only the data that’s pertinent to our
present analysis. On the right-hand side, you’ll find the criteria that Excel used
to create it in the PivotTable Fields dialog. We’ll cover what each of these field
means in the next section on customization.

Step 3: Customize your pivot table

Each of the items in this dialog is important to determine how your pivot table
will work. Click the configuration cog to alter this dialog’s look to whatever
works best for you.

Fields selection

Here, you are choosing which columns to include in your pivot table. How that
data will populate in the pivot table is determined by the type of data that it
represents — Excel will figure out for you whether to add a column to your
pivot table or add the field’s data within the table. For example, if you select
“Items,” Excel assumes you want to add the number of items for each
customer.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

On the other hand, if you select “Date,” Excel places the data into the table
and organizes the sales by when they occurred.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

As you’re working on your own pivot tables, you can experiment to see how
added and removing fields affects the data that’s displayed. You’ll find that
Excel does a great job of making selections that make sense, but you can also
change things around if Excel’s choices don’t make sense.

Filters

Drag items from the fields selection list into the “Filters” section if you want to
limit which data is shown. For example, if you drag “Customer” into the
“Filters” section, you can easily show only the data from one or a selection of
customers.

Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Columns

By dragging fields into the “Columns” section, you can expand how your data
is being reported. Again, when you drag a field into this section, Excel will try
to figure out how you want the data presented. For example, if you drag
“Date” into the “Columns” section, then Excel will display the sales as
summarized for the most logical timeframe, which in this case is per month.
This would be helpful if your primary concern was how much was sold on a
monthly basis with an eye on customer purchasing patterns.

Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Rows

Similarly, you can drag fields into the “Rows” section to embed different data
into pivot table rows. Again, if we drag “Date” into the “Rows” section, Excel
will break out the sales by customer per month, but the data will be
summarized by customer and not by month as in the previous example. In this
case, you’re mostly worried about how much you sold to each customer, but
you also want to spot any time-based trends.

Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Values

Finally, the “Values” section determines how you’re analyzing your data. In all
of our examples so far, we’ve been looking at total sales. If you click on the
down arrow key in this section, you can configure your value field settings to
look at a different numerical calculation.

Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

For example, let’s say you want to look at sales averages instead of total sales.
You would simply select “Average” in the value field settings dialog. You can
also set the number format so that the results make the most sense.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

Now, instead of considering total sales by customer, and then calculating a

grand total, we’re looking at average sales by company and then average sales
across the company. This would be helpful in evaluating which customers are
above or below average in sales, for example, and therefore which deserve the
most (or least) attention. In our example, perhaps Acme Engineering and
Jones Roofing don’t merit as much sales attention as the others.

Clearly, pivot tables offer a slew of options to make slicing and dicing your
data easy. The trick to using pivot tables effectively is to decide exactly what
you want to see before you start applying options. You also want to make sure
that your data corresponds to how you’ve laid out your table and how you’ve
named your headers. The more careful you are in setting things up, the more
effective your pivot tables will be.

Step 4: Create your own pivot tables from

scratch

Once you’ve played around with the pivot table feature and gained some
understanding of how the various options affect your data, then you can start
creating a pivot table from scratch. The process is similar to using a
recommendation, only you go to Insert > PivotTable and then manually select
your data as your first step.

In our case, that means selecting Table1, but we could also select a range of
data or pull from an external data source. We can also decide if we want to
create a new worksheet or place the pivot table next to our data at a certain
location on the existing worksheet.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

Once we’ve made our selection, we’re presented with a blank pivot table and
our PivotTable Fields dialog.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

Creating our own pivot table is then just a simple matter of selecting fields
and determining how we want the data calculated and displayed. Let’s say we
want to see how salespeople performed per month, with a grand total of sales
for the year. We would select the “Salesperson,” “Date,” and “Total Sales”
fields, drag the “Salesperson” field to the “Filters” section, and configure the
values to display as currency. Excel automatically adds the relevant date items
to the “Rows” section and assumes that we want to see sums.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

By default, Excel will show all of the data for all salespeople. We can also select
a single salesperson to see just his or her data. In this case, we see that Judy
French had sales in only three months, even though her sales totals were
significant. That could mean that her sales process was longer because she
was going after whales instead of fishes — a valuable conclusion, if accurate.
Perhaps investing in an assistant to help Judy close her sales more quickly
would be a good idea.
Mark Coppock/Digital Trends
Mark Coppock/Digital Trends

Step 5: Invest in some learning

If you want to get really good at using Excel pivot tables, invest some time in
learning a bit more about how it uses the various data types. Microsoft offers
its own training resources and there are a host of third-party trainers to
consider.
Generally, though, this means digging into Excel in a way that’s beyond the
scope of this guide. Nevertheless, hopefully you now see how pivot tables can
be a powerful tool in analyzing your data, and it’s relatively easy to get started
as long as you have your data configured into the right kind of table. And we
can’t stress enough how important it is to know what you want to accomplish
with your pivot table before you begin.

Sensitivity Analysis: “What if” Analysis

A financial model is a great way to assess the performance of a business on both
a historical and projected basis. It provides a way for the analyst to organize a
business’s operations and analyze the results in both a “time-series” format
(measuring the company’s performance against itself over time) and a “cross-
sectional” format (measuring the company’s performance against industry peers).
Typically, once an analyst inputs both historical financial results and assumptions
about future performance, he/she can then calculate and interpret various ratio
analyses, and other operational performance metrics such as profit
margins, inventory turnover, cash collections, leverage and interest coverage
ratios, among others.
Sensitivity Tables, Part 1: Building an Appropriate Financial Model

Before you do anything else, you must have a financial model or other analysis
where several key inputs or assumptions directly affect the output.

For example, in our setup for this Walmart DCF, it’s easy to see that assumptions such
as the Discount Rate and the Terminal Multiple affect the company’s implied share
price.

The Terminal Multiple affects its Terminal Value, and then the Discount Rate affects the
Present Value (PV) of the Terminal Value and the PV of the Free Cash Flows in the
projection period, and those flow into the Implied Enterprise Value and then the Implied
Equity Value and Share Price:
If you do not have a setup like this, where a few key cells affect the output of your
model, sensitivity analyses will not work.

Sensitivity Tables, Part 2: Formatting

Next, start by formatting the cells the way you want. We usually pick blue colors for the
background/fill of the outer cells, with white font color on top, and then a standard white
background with black font color for the middle area:
This is just formatting, so you should look at our tutorial on how to color code in
Excel for more.

Sensitivity Tables, Part 3: Enter the Row and Column Numbers You Want to Sensitize

This step is very important: when you enter the numbers in the row and column of the
table that you want to sensitize, you cannot link them directly to anything in the
model!

So, you must hard-code all these numbers in Excel, or you must start the row and
column with a hard-coded number and then add or subtract in each column or row after
that.

Here they are for Walmart:

Sensitivity Tables, Part 4: Link to the Output Variable in the Top-Left Corner

Next, go to the top-left corner of the table – cell D102 here – and enter a direct link to
the output variable that you want to display in the table.

In this case, it’s the company’s Implied Share Price under the Perpetuity Growth Rate
method of calculating Terminal Value:
This is the only part of the sensitivity table that should be linked to something in the
model.
Sensitivity Tables, Part 5: Select the Entire Range and Create the Table

Now, select the entire range and go to Alt, D, T or Data, What-If Analysis, and Data
Table, and enter the row and column input cells (the Terminal Growth Rate and
Discount Rate here):

If the table does not refresh right away, press F9 to force a spreadsheet update and see
the results.

Sensitivity Tables, Part 6: Check Your Work

The results should look like this:

You should check your work by reviewing the table and looking at the following points:

-Do the numbers change in each cell? If not, you’re doing something wrong. You
should not see rows or columns where the output is the same.

-In valuation tables, does the company’s implied value increase as its revenue, revenue
growth, or margins increase? Higher numbers should mean a higher valuation.

-As the Discount Rate increases, the company’s implied value should decrease.

The list goes on: make sure you understand how each assumption should affect the
output and then see if it works that way in your table.

Sensitivity Analysis Excel: Key Requirements to Set

Up the Tables
To recap and summarize this article, here are the key requirements for Excel-based
sensitivity analysis:

1) The input variables and output must be on the same spreadsheet as the table.
You cannot use assumptions or drivers from other sheets, such as the 3-statement
model, in this table.

2) The numbers in the input row and column cannot be linked to or from anything
that’s in the model. Start each input row or column with a hard-coded number and
then hard-code the rest or make them change by simple percentages or numbers.

3) The row and column inputs and the output must be related in some way. If the
inputs do not affect the output, the table will show no changes as you vary the numbers.
 4) Set “Workbook Calculation” in Options or Preferences to “Automatic except
for data tables” or your spreadsheet will slow down, especially with many tables. You
can then press F9 to refresh or update the tables.

5) Enter a direct link to the output you want to sensitize in the top-left-hand
corner of the table. And then, select everything and go through the steps shown
above. “Row Input Cell” should be a direct link for the input in the top row, and “Column
Input Cell” should be a direct link for the input in the left column.

6) You cannot modify individual cells in the table once it has been created. If you
want to change something or select different inputs or outputs, you must delete and re-
enter the entire table.

What is Goal Seek in Excel?

Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a
formula impacts another. More precisely, it determines what value you should enter in
an input cell to get the desired result in a formula cell.

The best thing about Excel Goal Seek is that it performs all calculations behind the
scenes, and you are only asked to specify these three parameters:

 Formula cell
 Target/desired value
 The cell to change in order to achieve the target

The Goal Seek tool is especially useful for doing sensitivity analysis in financial
modeling and is widely used by management majors and business owner. But there are
many other uses that may prove helpful to you.

For instance, Goal Seek can tell you how much sales you have to make in a certain
period to reach $100,000 annual net profit (example 1). Or, what score you must
achieve for your last exam to receive an overall passing score of 70% (example 2). Or,
how many votes you need to get in order to win the election (example 3).

On the whole, whenever you want a formula to return a specific result but are not sure
what input value within the formula to adjust to get that result, stop guessing and use
the Excel Goal Seek function!

Note. Goal Seek can process only one input value at a time. If you are working on an
advanced business model with multiple input values, use the Solver add-in to find the
optimal solution.
How to use Goal Seek in Excel
The purpose of this section is to walk you through how to use the Goal Seek function.
So, we'll be working with a very simple data set:

The above table indicates that if you sell 100 items at $5 each, minus the 10%
commission, you will make $450. The question is: How many items do you have to sell
to make $1,000?

Let's see how to find the answer with Goal Seek:

1. Set up your data so that you have a formula cell and a changing cell dependent on the
formula cell.
2. Go to the Data tab > Forecast group, click the What if Analysis button, and select Goal

Seek…
3. In the Goal Seek dialog box, define the cells/values to test and click OK:
o Set cell - the reference to the cell containing the formula (B5).
o To value - the formula result you are trying to achieve (1000).
o By changing cell - the reference for the input cell that you want to adjust (B3).
4. The Goal Seek Status dialog box will appear and let you know if a solution has been found. If
it succeeded, the value in the "changing cell" will be replaced with a new one. Click OK to
keep the new value or Cancel to restore the original one.

In this example, Goal Seek has found that 223 items (rounded up to the next integer)
need to be sold to achieve a revenue of $1,000.

If you are not sure you will be able to sell that many items, then maybe you can reach
the target revenue by changing the item price? To test this scenario, do Goal Seek
analysis exactly as described above except that you specify a different Changing

cell (B2):
 As the result, you will find out that if you increase the unit price to $11, you can reach

$1,000 revenue by selling only 100 items:

Tips and notes:

 Excel Goal Seek does not change the formula, it only changes the input value that you supply
to the By changing cell box.
 If Goal Seek is not able to find the solution, it displays the closest value it has come up with.
 You can restore the original input value by clicking the Undo button or pressing the Undo
shortcut (Ctrl + Z).

Examples of using Goal Seek in Excel

Below you will find a few more examples of using the Goal Seek function in Excel. The
complexity of your business model does not really matter as long as your formula in
the Set cell depends on the value in the Changing cell, directly or through intermediate
formulas in other cells.

Example 1: Reach the profit goal

Problem: It is a typical business situation - you have the sales figures for the first 3
quarters and you want to know how much sales you have to make in the last quarter to
 achieve the target net profit for the year, say, $100,000.

Solution: With the source data organized like shown in the screenshot above, set up the
following parameters for the Goal Seek function:

 Set cell - the formula that calculates the total net profit (D6).
 To value - the formula result you are looking for ($100,000).
 By changing cell - the cell to contain the gross revenue for quarter 4 (B5).
 Result: The Goal Seek analysis shows that in order to obtain $100,000 annual net profit,

your fourth-quarter revenue must be $185,714.

Example 2: Determine the exam passing score

Problem: At the end of the course, a student takes 3 exams. The passing score is 70%.
All the exams have the same weight, so the overall score is calculated by averaging the
3 scores. The student has already taken 2 out of 3 exams. The question is: What score
does the student need to get for the third exam to pass the entire course?

Solution: Let's do Goal Seek to determine the minimum score on exam 3:

 Set cell - the formula that averages the scores of the 3 exams (B5).
 To value - the passing score (70%).
 By changing cell - the 3rd exam score (B4).
Result: In order get the desired overall score, the student must achieve a minimum of

67% on the last exam:

Example 3: What-If analysis of the election

Problem: You are running for some elected position where a two-thirds majority
(66.67% of votes) is required to win the election. Assuming there are 200 total voting
members, how many votes do you have to secure?
 Currently, you have 98 votes, which is quite good but not sufficient because it only

makes 49% of the total voters:

Solution: Use Goal Seek to find out the minimum number of "Yes" votes you need to
get:

 Set cell - the formula that calculates the percentage of the current "Yes" votes (C2).
 To value - the required percentage of "Yes" votes (66.67%).
 By changing cell - the number of "Yes" votes (B2).

Result: What-If analysis with Goal Seek shows that to achieve the two-thirds mark or

66.67%, you need 133 "Yes" votes:

 What is a Scenario Manager in
Excel?
 Scenario manager in Excel is a part of three what-if-analysis tools in
Excel, which are built-in in Excel. In simple terms, you can see the impact
of changing input values without changing the actual data. Like a Data
Table in excel, you now input values that must change to achieve a
specific goal.
 A scenario is a set of input values that you can substitute in a worksheet to
perform what-if analysis. For example, you could create scenarios to show
various interest rates, loan amounts, and terms for a mortgage. Excel’s scenario
manager lets you create and store different scenarios in the same worksheet.
 Scenario manager in Excel allows you to change or substitute input
values for multiple cells (maximum up to 32). Therefore, you can view the
results of different input values or different scenarios at the same time.
 For example: What if I cut down my monthly traveling expenses?
How much will I save? Here, we can store scenarios to apply them with
a mouse click.

Use Scenario Manager

1. Select the cells that contain values that could change.

2. Click the Data tab on the ribbon.

3. Click the What-If Analysis button.
4. Select Scenario Manager.

The Scenario Manager dialog box appears with the message “No
Scenarios defined. Choose Add to add scenarios.

5. Click the Add button to add a new scenario.

6. Type a name for the new scenario.

If you already have cells selected, the Changing cells field will already be
populated with your selection. If you didn’t select cells up front, you’ll have
to specify the cells here.

7. Click OK.
 The Scenario Values dialog box appears, showing each of the variable
cells you selected.

If you name the worksheet cells you're changing, the cell names appear
here, making it easy to tell what value you're working with.

8. Update any values you want to see for the given scenario.
 To make sure you don’t lose the original values for the changing cells, use
the original cell values in the first scenario you create.

9. Click OK.

The scenario is added and is listed in the Scenario Manager. If you click
Add, the Add Scenario dialog box appears again so you can add another
scenario.

Repeat steps 5-9 to add all the desired scenarios.

 10. Select the scenario you want to view.

11. Click the Show button.

The worksheet’s values are changed to the values you specified in the scenario.
You can select any scenario here to update the values in your spreadsheet.