Transcript Document

Session 4
Managerial Spreadsheet Modeling -- Prof. Juran
1
Outline
Principles of Spreadsheet Design
Cell Comments
Custom Number Formatting
Managerial Spreadsheet Modeling -- Prof. Juran
2
Adding Comments
•
Right-click on cell, and choose “Insert Comment”
or Shift-F2.
•
Or, under the Review tab, choose “New
Comment” from within the Comments group.
• At this point, you can add text to the comment.
– Change the default text now.
– Or later, right-click the cell and choose Edit Comment.
– Or under the Review tab, choose “Edit Comment” from within
the Comments group.
• Useful comments only; not “Profits = Revenues – Costs.”
Managerial Spreadsheet Modeling -- Prof. Juran
3
AutoFormulaBox Macro
• Works for formulas only.
– Works in cells starting with “=“
• http://www.columbia.edu/~dj114/autoformulabox.htm
Managerial Spreadsheet Modeling -- Prof. Juran
4
Changing the Appearance of Comments
• Right-click on the comment border and choose Format
Comment.
• Select the “Colors and Line” tab, and make whatever changes
you want.
• Excel 2003 style color palette!
Managerial Spreadsheet Modeling -- Prof. Juran
5
Changing Comment Shapes
• Superficial, but guaranteed to stand out!
• Need to add the “Change Shape” command to the QAT.
– Right-click on the QAT, and set “Choose command from” to
“Drawing Tools | Format Tab.”
– Select “Change Shape” and click Add >> to install it on the
QAT.
• Select the edge of the comment (as before), click on “Change
Shapes” on the QAT, and select a new shape.
– If you choose a Callout shape, move the callout “handle” (the
small yellow diamond) to the tip of the red triangle comment
indicator.
Managerial Spreadsheet Modeling -- Prof. Juran
6
Comments
• You can copy comments by copying the cell (Right-click
Copy, Home Copy, or Ctrl+C), and then Home | Paste (or
right-click) Paste Special | Comments.
• You can change the default comment label in File | Options |
General | User name.
• You can set pictures to be the background.
– Photographs of the products, sales reps, etc.
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
B
C
D
E
F
G
Juran:
A default comment
Managerial Spreadsheet Modeling -- Prof. Juran
Juran: A modified comment
7
Formatting a List of Numbers
A
1
2
3
4
5
6
7
8
9
10
B
Chapman
Redlands
La Verne
Cal Lutheran
Occidental
Pomona-Pitzer
Claremont-Mudd-Scripps
Whittier
Total
•
•
•
•
•
Version A
1163
500
15695
7863
37638
3550
645
5007
72061
C
D
Version B
1,163
500
15,695
7,863
37,638
3,550
645
5,007
72,061
Version C
1,163
500
15,695
7,863
37,638
3,550
645
5,007
72,061
E
Version D
1,163
500
15,695
7,863
37,638
3,550
645
5,007
72,061
F
Version E
$1,163
$500
$15,695
$7,863
$37,638
$3,550
$645
$5,007
$72,061
G
Version F
$
1,163
$
500
$
15,695
$
7,863
$
37,638
$
3,550
$
645
$
5,007
$
72,061
H
I
Version G
Version H
$ 1,163 $
1,163
$
500
500
$ 15,695
15,695
$ 7,863
7,863
$ 37,638
37,638
$ 3,550
3,550
$
645
645
$ 5,007
5,007
$ 72,061 $
72,061
J
Version I
1,163
500
15,695
7,863
37,638
3,550
645
5,007
72,061
Cell borders?
Emphasized totals?
Commas?
Alignment?
$’s?
Managerial Spreadsheet Modeling -- Prof. Juran
8
Custom Number Formatting
• How can we center numbers in the middle of a column, but
keep the decimal place aligned?
• Under the Home Tab, launch the Number dialog window
and choose “Custom” as the Category.
• Different possible formats:
–
–
–
–
–
–
0 Always a number
# A number if needed; otherwise nothing
? A number if needed; otherwise a blank space
_ Underscore: A blank the width of the following character
– En-dash (Alt-0150 on the number pad; laptop: use NumLock)
— Em-dash (Alt-0151), € (Alt-0128), ¥ (Alt-0165)
Managerial Spreadsheet Modeling -- Prof. Juran
9
Custom Number Formatting
• positive format; negative format; zero format
• _($ ?,??0.00_); [Red]($ ?,??0.00); _(—_)
– Separated by ;’s.
– zero format defaults to positive format if not separately specified.
Managerial Spreadsheet Modeling -- Prof. Juran
10
Custom Number Formatting
positive format; negative format; zero format
_($ ?,??0.00_); [Red]($ ?,??0.00); _(—_)
And a trailing space.
Always show the last digit and two decimal places.
Numbers, or spaces if < 999.
Then a $ and a space.
Leave a space the width of a “(” at the beginning.
Managerial Spreadsheet Modeling -- Prof. Juran
11
Custom Number Formatting
positive format; negative format; zero format
_($ ?,??0.00_); [Red]($ ?,??0.00); _(—_)
Always parentheses.
Can be a different color (black, blue, cyan, green, red, yellow or white)
positive format; negative format; zero format
Alt-0151
_($ ?,??0.00_); [Red]($ ?,??0.00); _(—_)
Leave a space the width of a “(” at the beginning and the end.
Managerial Spreadsheet Modeling -- Prof. Juran
12
Using Custom Number Formatting
• In the Home tab,
launch the Number
dialog box, and
choose Custom.
• Enter the format in
the “Type” box.
Managerial Spreadsheet Modeling -- Prof. Juran
13
Number Formatting Extensions
• A “,” in the format code divides the number displayed by
1,000.
• An “*” will repeat the following character to fill the cell.
• There’s a fourth format code for text, where “@” is the
placeholder for the text.
• The format code “;;;” will always display nothing.
1
2
3
4
5
6
7
8
A
B
C
D
Cell Value
12587
12587
12587631
You'll be happy
0
4.3
4.3
Custom Number Format
Displayed
12.59
13,000
13 million
You'll be happy in bed
Try it!
12587
12587
12587631
You'll be happy
0
4.3
4.3
0,.00
0,",000"
0,," million"
General;General;General;@" in bed"
General;General;
"4.2";General;General
"True";"True";"False"
Managerial Spreadsheet Modeling -- Prof. Juran
4.2
True
14
Hands-on Practice
• Install AutoFormulaBox Inserting a comment and changing
its appearance.
• Adding “Change Shape” to your QAT and apply it to your
comment.
• Using Home | Copy and Paste Special to copy your comment
to another (non-empty) cell.
• Experimenting with custom number formats.
– Exactly 45 and ………………….. 95/XX dollars
– (Hint: Use 2 adjacent cells that contain 45 and 95.)
– Accounting format with the Thai baht (฿)
– You can also find in the Calibri and Cambria fonts.
– Round the display to the nearest $1,000.
Managerial Spreadsheet Modeling -- Prof. Juran
15
For Next Time
• Install AutoFormulaBox
• Roth vs. Traditional IRA
Managerial Spreadsheet Modeling -- Prof. Juran
16