What-If Analysis, Charting, And Working With Large Worksheets Page 5
ADVERTISEMENT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87What-If Analysis, Charting, and Working with Large Worksheets
Excel Chapter 3
EX 141
Overview
Excel’s Usefulness
Just a short time ago,
As you read this chapter, you will learn how to create the worksheet shown in
a what-if question of
any complexity could
Figure 3–1 by performing these general tasks:
be answered only by
•
Create a series of month names
using a large, expensive
computer programmed
•
Use absolute cell references in a formula
by highly paid computer
•
Use the IF function to perform a logical test
professionals, and
generating a result could
•
Create Sparkline charts in a range of cells
take days. Excel gives the
•
Use the Format Painter button to format cells
noncomputer professional
the ability to get complex
•
Create a 3-D pie chart on a separate chart sheet
business-related questions
•
Answer what-if questions
answered instantaneously
and economically.
•
Manipulate large worksheets
General Project Decisions
Plan
While creating an Excel worksheet, you need to make several decisions that will determine the
Ahead
appearance and characteristics of the fi nished worksheet. As you create the worksheet required
to meet the requirements shown in Figure 3–2, you should follow these general guidelines:
1. Plan the layout of the worksheet. Worksheets that include fi nancial data associated
with time frames typically include dates, such as months, quarters, or years, as column
headers. What-if assumptions should not clutter the worksheet, but placing them in an
easily located portion of the worksheet allows for quicker creation of new projections.
2. Determine the necessary formulas and functions needed. Often, fi nancial calculations rely
on strict defi nitions and commonly accepted formulas for performing the calculations.
Look for such situations and always use the accepted formulas. When using a what-if
section on a worksheet, make certain to create formulas that use the what-if criteria.
When a requirement necessitates a calculation only under a certain condition, a function
can check for the condition and make the calculation when necessary.
3. Specify how to best utilize Sparkline charts. Sparkline charts allow worksheet users
quickly to visualize information in a small chart within a cell. The use of multiple
Sparkline charts in the worksheet will provide the user with a visual comparison of the
various data items for each month. The user, therefore, can see trends for each line item
over time and also compare relationships among various line items.
4. Identify how to format various elements of the worksheet. Format separate parts of
a worksheet, such as what-if assumptions, in a manner that indicates that they are
separate from the main area of the worksheet. Other fi nancial items, such as sales
revenue and expenses, are distinct categories of fi nancial data and should be separated
visually. Totals and subtotals should stand out to draw the reader’s attention.
5. Specify how charts should convey necessary information. As you have learned, different
chart types convey different messages and are appropriate in different situations. For
example, a 3-D pie chart is a good way to compare visually a small set of numbers. Often
one or two slices of a pie chart displays as exploded, meaning that the slice appears
pulled away from the cart, in order to emphasize the slice to the user. Format chart data
points so that the worksheet user’s eye is drawn to important information.
6. Perform what-if analysis and goal seeking using the best techniques. What-if analysis
allows you quickly to answer questions regarding various predictions. A what-if area of a
worksheet allows users of the worksheet effi ciently to ask questions. Goal seeking allows
you automatically to modify values in a what-if area of a worksheet based on a goal that
you have for another cell in the worksheet.
When necessary, more specifi c details concerning the above guidelines are presented
at appropriate points in the chapter. The chapter also will identify the actions you perform
and decisions made regarding these guidelines during the creation of the worksheet shown
in Figure 3–1 on page EX 139.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education