|
Multi-Precedents for an entire worksheet
In addition to selecting a block of cells, we can also select the entire
worksheet.
Selection of an entire worksheet can be done by clicking on the square on the
top left corner, above row 1 and to the left of column A, as shown in the
following figure.
Figure 27. Selection of an entire Worksheet
Multi-Precedents can then be applied to the selection.
As for “Trace Precedents”, each click of “Multi-Precedent” will show another
level of Precedents.
Remove Multi-Precedents
The button to remove multi-precedent is labeled “Less-MultiPre”
. Each click
removes one level of the precedent arrows.
Multi-Dependents
The “Multi-Dependents” and “Less-MultiDep” buttons work in a similar way
to the “Multi-Precedents” and “Less-MultiPre”.
As example is shown below. We first select 3 cells, B2:D2, then we click on
“Multi-Dependents.” The result is the drawing of 3 arrows from the selected
cells. With Excel’s “Trace Dependents”, we have to select B7, click “Trace
Dependents”, and repeat for C7, and D7.
Figure 28. Multi-Dependents Example
8. Examples of Overviews
Often, when we are faced with a spreadsheet, we have no idea how the
spreadsheet is organized and where we can start. The following examples
illustrated how an overview can be easily generated.
An overview allows us to have a high level view, and identify where to zoom
in for closer looks.
Example 1: A simplified balance sheet
The next figure shows a much simplified balance sheet. , which shows a
standard format for a company’s balance sheet. This is how it normally looks
without an influence chart.
Figure 29. A Balance Sheet
The next figure shows the same balance sheet with an influence chart drawn
over. The figure is obtained in two clicks. One click selects the entire sheet.
The second click is on “MultiPrecedent” button.
The overview shows 4 components. Each component is a “group” of cells
connected by arrows, and there are no arrows across groups.
Figure 30. A Balance Sheet with Components Highlighted
Example 2: A company’s consolidated balance sheet
This example uses a consolidated balance sheet provided by a big corporation.
The next figure shows how it looks like normally, without the influence chart.
The subsequent figure, with the influence chart showing the components and
precedent arrows, is produced in 2 steps:
·
Select the entire worksheet
·
Click the “Multi-Precedent” button
The overview created by the blue precedent arrows shows 8 separate
components, with two for each quarter. Each quarter has one component for
assets and one component for liabilities.
Figure 31. A Company consolidated balance sheet
Figure 32. Quarterly Balance Sheet with Precedent Overview
Example 3: A company’s statement of income
This example is from the same corporation. It shows how a worksheet with
one component can show separated components when only part of the
worksheet is selected for analysis.
The next few diagrams show the following:
·
The worksheet as it appears normally.
·
The entire worksheet with precedents shown (by clicking “Multi-
Precedent” button”
·
The partial worksheet with only quarterly columns selected, and with a
click on “Multi-Precedent” button”. It is quite clear that the quarterly
data are connected through the annual total in the rightmost column. But
without the rightmost column, the quarterly columns are separated
components. A partial selection can also present a less cluttered view.
Figure 33. Income Statement
Figure 34. An Overview of the Income Statement
Figure 35. A Quarterly Analysis of the Income Statement
Sometimes, it is better to analyze only part of the worksheet. As in this case,
the arrows and color boxes for each quarter are not messed up by another set
of horizontal arrows for the annual totals.
In general, it is good to try overviews or partial overviews, to allow us to
view the Excel model with different perspectives.
Example 4: A Personal Budget Model
This example of a personal budget spreadsheet is from
vertex42.com
/
Figure 36. A Personal Budget Model
With the Multi-Precedent button, we can get the following figure with
precedent arrows with 2 clicks (select the entire worksheet and click on the
Multi-Precedent button).
This picture shows us the overall structure of the model. For example, there
are many tabular structures and all arrows eventually point to the upper right
corner.
We see that sub-totals within each table are used in the total calculation, at the
top right corner. We also see that budget-actual differences in each table are
not used for the total difference.
Figure 37. Precedents for Personal Budget Model
9. Special Cases for Trace Precedents and Trace
Dependents
For some types of formula, trace precedents and trace dependents may give
results that are not so natural, intuitive or even misleading. Some examples are
discussed in this chapter.
Power Formula Auditing, which builds upon the original trace precedents and
trace dependents, have the same limitations.
Offset formula in Excel
One example is the Offset formula. A explanation for this formula is at
Microsoft support (
How to use the Offset formula in Excel
).
The offset formula can be used to calculate, say, weekly data when we have
daily data. The formula will, e.g., sum the first 7 days, and each subsequent 7
days.
In the next figure, the top rows are the daily usage of 3 items (milk, sugar and
butter). For clarity, only the milk entries have data.
To get the weekly summation for week 1, we enter this formula into cell B8
=SUM(OFFSET($B3,0,(B$8-1)*7,1,7)).
This formula can be copied and pasted for subsequent weekly summations. For
example, after copy and paste, cell C8 will have this formula:
=SUM(OFFSET($B3,0,(C$8-1)*7,1,7))
Without the offset formula, we have to design and type a different formula for
each weekly summation. So the offset formula saves a lot of mental calculation
and typing.
The effortful method is illustrated in the bottom half of the figure. Cell B17
contains =SUM(B12:H12), and cell C17 contains =SUM(I12:O12). Note that
we cannot just copy the formula from B17 and paste to C17. With this method,
the precedent arrows show very clearly the daily cells that are being summed
into the weekly cells (the precedent arrows in the bottom half of the figure).
In contrast, the precedent arrows for the offset formula do not give much
information. They show only the cells that are explicitly mentioned in the offset
formula, but not the ultimate cells that are used in the summation.
Tracing precedents become much harder with the offset formula. Cells that are
used by the offset formula but not explicitly included in the formula will show
up as stand-alone cells, as shown in the next figure.
The result could be visually misleading, unless we are aware of it.
Do'stlaringiz bilan baham: | 
