Home Page Icon
Home Page
Table of Contents for
Table of Contents
Close
Table of Contents
by Danielle Stein Fairhurst
Using Excel for Business and Financial Modelling, 3rd Edition
Cover
Preface
BOOK OVERVIEW
ACKNOWLEDGEMENTS
CHAPTER 1: What is Financial Modelling?
WHAT'S THE DIFFERENCE BETWEEN A SPREADSHEET AND A FINANCIAL MODEL?
TYPES AND PURPOSES OF FINANCIAL MODELS
TOOL SELECTION
WHAT SKILLS DO YOU NEED TO BE A GOOD FINANCIAL MODELLER?
THE “IDEAL” FINANCIAL MODELLER
SUMMARY
NOTES
CHAPTER 2: Building a Model
MODEL DESIGN
THE GOLDEN RULES FOR MODEL DESIGN
DESIGN ISSUES
THE WORKBOOK ANATOMY OF A MODEL
PROJECT PLANNING YOUR MODEL
MODEL LAYOUT FLOWCHARTING
STEPS TO BUILDING A MODEL
INFORMATION REQUESTS
VERSION-CONTROL DOCUMENTATION
SUMMARY
CHAPTER 3: Best-Practice Principles of Modelling
DOCUMENT YOUR ASSUMPTIONS
LINKING, NOT HARDCODING
ENTER DATA ONLY ONCE
AVOID BAD HABITS
USE CONSISTENT FORMULAS
FORMAT AND LABEL CLEARLY
METHODS AND TOOLS OF ASSUMPTIONS DOCUMENTATION
LINKED DYNAMIC TEXT ASSUMPTIONS DOCUMENTATION
WHAT MAKES A GOOD MODEL?
SUMMARY
NOTE
CHAPTER 4: Financial Modelling Techniques
THE PROBLEM WITH EXCEL
ERROR AVOIDANCE STRATEGIES
HOW LONG SHOULD A FORMULA BE?
LINKING TO EXTERNAL FILES
BUILDING ERROR CHECKS
CIRCULAR REFERENCES
SUMMARY
NOTES
CHAPTER 5: Using Excel in Financial Modelling
FORMULAS AND FUNCTIONS IN EXCEL
EXCEL VERSIONS
HANDY EXCEL SHORTCUTS
CELL REFERENCING BEST PRACTICES
NAMED RANGES
BASIC EXCEL FUNCTIONS
LOGICAL FUNCTIONS
NESTING LOGICAL FUNCTIONS
SUMMARY
CHAPTER 6: Functions for Financial Modelling
AGGREGATION FUNCTIONS
LOOKUP FUNCTIONS
NESTING INDEX AND MATCH
OFFSET FUNCTION
REGRESSION ANALYSIS
CHOOSE FUNCTION
WORKING WITH DATES
FINANCIAL PROJECT EVALUATION FUNCTIONS
LOAN CALCULATIONS
SUMMARY
CHAPTER 7: Tools for Model Display
BASIC FORMATTING
CUSTOM FORMATTING
CONDITIONAL FORMATTING
SPARKLINES
BULLETPROOFING YOUR MODEL
CUSTOMISING THE DISPLAY SETTINGS
FORM CONTROLS
SUMMARY
CHAPTER 8: Tools for Financial Modelling
HIDING SECTIONS OF A MODEL
GROUPING
ARRAY FORMULAS
GOAL SEEKING
STRUCTURED REFERENCE TABLES
PIVOTTABLES
MACROS
SUMMARY
CHAPTER 9: Common Uses of Tools in Financial Modelling
ESCALATION METHODS FOR MODELLING
UNDERSTANDING NOMINAL AND EFFECTIVE (REAL) RATES
CALCULATING A CUMULATIVE SUM (RUNNING TOTALS)
HOW TO CALCULATE A PAYBACK PERIOD
WEIGHTED AVERAGE COST OF CAPITAL (WACC)
BUILDING A TIERING TABLE
MODELLING DEPRECIATION METHODS
BREAK-EVEN ANALYSIS
SUMMARY
CHAPTER 10: Model Review
REBUILDING AN INHERITED MODEL
IMPROVING MODEL PERFORMANCE
AUDITING A FINANCIAL MODEL
SUMMARY
APPENDIX: QA LOG
CHAPTER 11: Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling
WHAT ARE THE DIFFERENCES BETWEEN SCENARIO, SENSITIVITY, AND WHAT-IF ANALYSES?
OVERVIEW OF SCENARIO ANALYSIS TOOLS AND METHODS
ADVANCED CONDITIONAL FORMATTING
COMPARING SCENARIO METHODS
ADDING PROBABILITY TO A DATA TABLE
SUMMARY
CHAPTER 12: Presenting Model Output
PREPARING AN ORAL PRESENTATION FOR MODEL RESULTS
PREPARING A GRAPHIC OR WRITTEN PRESENTATION FOR MODEL RESULTS
CHART TYPES
WORKING WITH CHARTS
HANDY CHARTING HINTS
DYNAMIC NAMED RANGES
CHARTING WITH TWO DIFFERENT AXES AND CHART TYPES
BUBBLE CHARTS
CREATING A DYNAMIC CHART
WATERFALL CHARTS
SUMMARY
About the Author
About the Website
DOWNLOADABLE RESOURCES
Index
End User License Agreement
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Using Excel for Business and Financial Modelling
Table of Contents
Cover
Preface
BOOK OVERVIEW
ACKNOWLEDGEMENTS
CHAPTER 1: What is Financial Modelling?
WHAT'S THE DIFFERENCE BETWEEN A SPREADSHEET AND A FINANCIAL MODEL?
TYPES AND PURPOSES OF FINANCIAL MODELS
TOOL SELECTION
WHAT SKILLS DO YOU NEED TO BE A GOOD FINANCIAL MODELLER?
THE “IDEAL” FINANCIAL MODELLER
SUMMARY
NOTES
CHAPTER 2: Building a Model
MODEL DESIGN
THE GOLDEN RULES FOR MODEL DESIGN
DESIGN ISSUES
THE WORKBOOK ANATOMY OF A MODEL
PROJECT PLANNING YOUR MODEL
MODEL LAYOUT FLOWCHARTING
STEPS TO BUILDING A MODEL
INFORMATION REQUESTS
VERSION-CONTROL DOCUMENTATION
SUMMARY
CHAPTER 3: Best-Practice Principles of Modelling
DOCUMENT YOUR ASSUMPTIONS
LINKING, NOT HARDCODING
ENTER DATA ONLY ONCE
AVOID BAD HABITS
USE CONSISTENT FORMULAS
FORMAT AND LABEL CLEARLY
METHODS AND TOOLS OF ASSUMPTIONS DOCUMENTATION
LINKED DYNAMIC TEXT ASSUMPTIONS DOCUMENTATION
WHAT MAKES A GOOD MODEL?
SUMMARY
NOTE
CHAPTER 4: Financial Modelling Techniques
THE PROBLEM WITH EXCEL
ERROR AVOIDANCE STRATEGIES
HOW LONG SHOULD A FORMULA BE?
LINKING TO EXTERNAL FILES
BUILDING ERROR CHECKS
CIRCULAR REFERENCES
SUMMARY
NOTES
CHAPTER 5: Using Excel in Financial Modelling
FORMULAS AND FUNCTIONS IN EXCEL
EXCEL VERSIONS
HANDY EXCEL SHORTCUTS
CELL REFERENCING BEST PRACTICES
NAMED RANGES
BASIC EXCEL FUNCTIONS
LOGICAL FUNCTIONS
NESTING LOGICAL FUNCTIONS
SUMMARY
CHAPTER 6: Functions for Financial Modelling
AGGREGATION FUNCTIONS
LOOKUP FUNCTIONS
NESTING INDEX AND MATCH
OFFSET FUNCTION
REGRESSION ANALYSIS
CHOOSE FUNCTION
WORKING WITH DATES
FINANCIAL PROJECT EVALUATION FUNCTIONS
LOAN CALCULATIONS
SUMMARY
CHAPTER 7: Tools for Model Display
BASIC FORMATTING
CUSTOM FORMATTING
CONDITIONAL FORMATTING
SPARKLINES
BULLETPROOFING YOUR MODEL
CUSTOMISING THE DISPLAY SETTINGS
FORM CONTROLS
SUMMARY
CHAPTER 8: Tools for Financial Modelling
HIDING SECTIONS OF A MODEL
GROUPING
ARRAY FORMULAS
GOAL SEEKING
STRUCTURED REFERENCE TABLES
PIVOTTABLES
MACROS
SUMMARY
CHAPTER 9: Common Uses of Tools in Financial Modelling
ESCALATION METHODS FOR MODELLING
UNDERSTANDING NOMINAL AND EFFECTIVE (REAL) RATES
CALCULATING A CUMULATIVE SUM (RUNNING TOTALS)
HOW TO CALCULATE A PAYBACK PERIOD
WEIGHTED AVERAGE COST OF CAPITAL (WACC)
BUILDING A TIERING TABLE
MODELLING DEPRECIATION METHODS
BREAK-EVEN ANALYSIS
SUMMARY
CHAPTER 10: Model Review
REBUILDING AN INHERITED MODEL
IMPROVING MODEL PERFORMANCE
AUDITING A FINANCIAL MODEL
SUMMARY
APPENDIX: QA LOG
CHAPTER 11: Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling
WHAT ARE THE DIFFERENCES BETWEEN SCENARIO, SENSITIVITY, AND WHAT-IF ANALYSES?
OVERVIEW OF SCENARIO ANALYSIS TOOLS AND METHODS
ADVANCED CONDITIONAL FORMATTING
COMPARING SCENARIO METHODS
ADDING PROBABILITY TO A DATA TABLE
SUMMARY
CHAPTER 12: Presenting Model Output
PREPARING AN ORAL PRESENTATION FOR MODEL RESULTS
PREPARING A GRAPHIC OR WRITTEN PRESENTATION FOR MODEL RESULTS
CHART TYPES
WORKING WITH CHARTS
HANDY CHARTING HINTS
DYNAMIC NAMED RANGES
CHARTING WITH TWO DIFFERENT AXES AND CHART TYPES
BUBBLE CHARTS
CREATING A DYNAMIC CHART
WATERFALL CHARTS
SUMMARY
About the Author
About the Website
DOWNLOADABLE RESOURCES
Index
End User License Agreement
List of Tables
Chapter 3
TABLE 3.1 Comparison of In-Cell Comment Methods
Chapter 4
TABLE 4.1 Why External Links are Not Used
Chapter 5
TABLE 5.1 Useful Windows Keyboard Shortcuts for Financial Modellers
Chapter 6
TABLE 6.1 Advantages and Disadvantages of LOOKUP Functions
Chapter 9
TABLE 9.1 Depreciation for a $200,000 Piece of Machinery
Chapter 12
TABLE 12.1 What Type of Chart Best Fits Your Data?
List of Illustrations
Chapter 2
FIGURE 2.1 Model Layout Option 1
FIGURE 2.2 Model Layout Option 2
FIGURE 2.3 Model Categorisation Option 1
FIGURE 2.4 Model Categorisation Option 2
FIGURE 2.5 Model Structure
FIGURE 2.6 Using the Styles Menu to Format Input Cells
FIGURE 2.7 Commonly Used Formatting for Inconsistent Formulas
FIGURE 2.8 Completed Customer Support Pricing Model
FIGURE 2.9 Sample Flowchart of Model Calculations
FIGURE 2.10 Sample File Structure
Chapter 3
FIGURE 3.1 Assumptions Documentation Using In-Cell Comment
FIGURE 3.2 Assumptions Documentation Using Data Validation Input Messages
FIGURE 3.3 Data Validation Input Message Dialog Box
FIGURE 3.4 Example of Manual Footnoting in Excel
FIGURE 3.5 Hardcoded Assumptions Documentation
FIGURE 3.6 Linking Text Using Ampersand
FIGURE 3.7 Linked Dynamic Assumptions Documentation
FIGURE 3.8 Format Cells Dialog Box
FIGURE 3.9 Practical Commentary Exercise
FIGURE 3.10 Practical Commentary Exercise (Completed)
Chapter 4
FIGURE 4.1 Common Excel Error in Modelling
FIGURE 4.2 Clicking Elsewhere Instead of Using the Enter Key Can Cause Errors
FIGURE 4.3 Showing Formula Source Cells
FIGURE 4.4 Methodology Documentation
FIGURE 4.5 Sense-Checking Methodology Using the Sum Total
FIGURE 4.6 Very Long Formula Broken into Several Lines
FIGURE 4.7 Formula Linking to a Closed External File
FIGURE 4.8 Revenue Calculated Two Different Ways
FIGURE 4.9 Breaking Links on Edit Links Dialog Box
FIGURE 4.10 Summary Report
FIGURE 4.11 Error-Check Example
FIGURE 4.12 Error-Check Alert Formula
FIGURE 4.13 Formula Creating a Circular Reference
FIGURE 4.14 Circular Reference Notification in the Status Bar
FIGURE 4.15 Finding the Circular Reference Auditing Tool in the Ribbon
FIGURE 4.16 Finding the Circular Reference Auditing Tool in the Ribbon in Excel...
FIGURE 4.17 Circular Reference in Interest Calculations
FIGURE 4.18 Enabling Iterative Calculations
FIGURE 4.19 Enabling Iterative Calculations in Excel for Mac
Chapter 5
FIGURE 5.1 Insert Function Dialog Box
FIGURE 5.2 Formula Builder in Excel for Mac
FIGURE 5.3 Compounding Growth Rate Calculation Using a Helper Row
FIGURE 5.4 Compounding Growth Rate Calculation Without a Helper Row
FIGURE 5.5 Shortcut Keys Shown After Pressing the ALT Key
FIGURE 5.6 Relative Cell Referencing
FIGURE 5.7 Absolute Cell Referencing
FIGURE 5.8 Copied Absolute Cell Referencing
FIGURE 5.9 Mixed Referencing Exercise
FIGURE 5.10 Answer to Mixed Referencing Exercise
FIGURE 5.11 The Name Box
FIGURE 5.12 Finding a Named Range Using the Name Box
FIGURE 5.13 Using the SUM Function
FIGURE 5.14 Using the MAX Function
FIGURE 5.15 Using the MIN Function
FIGURE 5.16 Using the AVERAGE Function
FIGURE 5.17 Combining Functions to Calculate the Deviation
FIGURE 5.18 Using an IF Statement for Decision Analysis
FIGURE 5.19 Using an IF Statement to Create a Spend Schedule
FIGURE 5.20 Spot Checking the Block of Data Using the F2 Shortcut Key
FIGURE 5.21 Completed Nested IF and AND Formula
FIGURE 5.22 Showing the IF Insert Function Dialog Box on a Nested Formula
FIGURE 5.23 Showing the AND Insert Function Dialog Box on a Nested Formula
FIGURE 5.24 Volume Pricing Table
FIGURE 5.25 Highlight and Copy IF Statement
FIGURE 5.26 Completed Nested IF Function
FIGURE 5.27 IFS Insert Function Dialog Box
Chapter 6
FIGURE 6.1 Sales List
FIGURE 6.2 COUNTIF Insert Function Dialog Box
FIGURE 6.3 Completed COUNTIF Function
FIGURE 6.4 SUMIF Insert Function Dialog Box
FIGURE 6.5 Completed SUMIF Function
FIGURE 6.6 Incorrect SUMIF Calculation
FIGURE 6.7 COUNTIFS Dialog Box
FIGURE 6.8 Completed COUNTIFS Function
FIGURE 6.9 Completed SUMIFS Function
FIGURE 6.10 AVERAGEIFS Insert Function Dialog Box
FIGURE 6.11 Sense Checking the AVERAGEIFS Function
FIGURE 6.12 Completed Filtered Sales Report
FIGURE 6.13 SUMIFS Function with a Minimum Filter
FIGURE 6.14 AVERAGEIFS Function with a Minimum Filter
FIGURE 6.15 VLOOKUP Insert Function Dialog Box
FIGURE 6.16 Creating a Named Range
FIGURE 6.17 Using the F3 Shortcut to Paste Name into a Formula
FIGURE 6.18 Completed VLOOKUP Function
FIGURE 6.19 MATCH Function Dialog Box
FIGURE 6.20 Use of the HLOOKUP Function
FIGURE 6.21 HLOOKUP Insert Function Dialog Box
FIGURE 6.22 LOOKUP Function Option
FIGURE 6.23 LOOKUP Insert Function Dialog Box
FIGURE 6.24 Completed Pricing Calculation
FIGURE 6.25 Sample Data
FIGURE 6.26 INDEX Function Options
FIGURE 6.27 INDEX Insert Function Dialog Box
FIGURE 6.28 Completed INDEX Function Using Named Ranges in Separate Workbooks
FIGURE 6.29 Example Data
FIGURE 6.30 OFFSET Insert Function Dialog Box
FIGURE 6.31 Calculating a Dynamic Cash Flow Using the OFFSET Function
FIGURE 6.32 OFFSET Function with Error and Text Values
FIGURE 6.33 Completed Dynamic Cash Flow Using a Nested OFFSET Formula
FIGURE 6.34 Inserting a Linear Trend Line
FIGURE 6.35 Linear Trend Line
FIGURE 6.36 FORECAST Insert Function Dialog Box
FIGURE 6.37 Completed Line Chart Showing Results of FORECAST
FIGURE 6.38 Select the Forecast Sheet Button to Bring Up the Create Forecast Wo...
FIGURE 6.39 Completed Forecast Sheet with FORECAST.ETS Function
FIGURE 6.40 CHOOSE Insert Function Dialog Box
FIGURE 6.41 Using a Formula to Calculate Dates
FIGURE 6.42 Using the EOMONTH Function
FIGURE 6.43 The EOMONTH Function Calculates a Leap Year Correctly
FIGURE 6.44 The WEEKDAY Function Returns the Day of the Week
FIGURE 6.45 The MONTH Function Used to Aggregate Data
FIGURE 6.46 Using a DAY Function to Return the Calendar Day of the Month
FIGURE 6.47 Shortcut Date Formatting Drop-Down
FIGURE 6.48 Using the NPV Function
FIGURE 6.49 Using the IRR Function
FIGURE 6.50 IRR Calculation with Multiple Results
FIGURE 6.51 XNPV Function
FIGURE 6.52 Loan Template
FIGURE 6.53 PMT Insert Function Dialog Box
FIGURE 6.54 IPMT Function
FIGURE 6.55 Loan with Completed PMT, IPMT, and PPMT Functions
FIGURE 6.56 Principal (PPMT) and Interest (IPMT) Components of Loan Repayments ...
Chapter 7
FIGURE 7.1 Formatting in the Ribbon
FIGURE 7.2 Showing the Current Date and Time Using the =NOW() Function
FIGURE 7.3 Format Cells Dialog Box
FIGURE 7.4 Changing the Date Format
FIGURE 7.5 Sample Report
FIGURE 7.6 Custom Formatting Using the Format Cells Dialog Box
FIGURE 7.7 Using the ROUND Function to Truncate Values
FIGURE 7.8 Applying Conditional Formatting
FIGURE 7.9 Applying Data Bars
FIGURE 7.10 Accessing Icon Sets
FIGURE 7.11 Sample Report without Formatting
FIGURE 7.12 Sample Report Using Colour Scales
FIGURE 7.13 Edit Formatting Rule to Hide Icons
FIGURE 7.14 Applying Multiple Types of Conditional Formatting to the Same Range
FIGURE 7.15 Choosing Sparklines from the Ribbon
FIGURE 7.16 Sparklines Dialog Box
FIGURE 7.17 Completed Report with Sparklines
FIGURE 7.18 Edit Sparklines Dialog Box
FIGURE 7.19 Hidden and Empty Cell Settings Dialog Box
FIGURE 7.20 Model with Customised Display Settings
FIGURE 7.21 Minimising the Ribbon
FIGURE 7.22 A Worksheet with Restricted Work Area
FIGURE 7.23 Data Validation Comment
FIGURE 7.24 Creating a Customised Error Message
FIGURE 7.25 Customised Popup Error Message
FIGURE 7.26 Creating a Drop-Down List
FIGURE 7.27 Enter the Source Data Range
FIGURE 7.28 Completed Drop-Down List
FIGURE 7.29 Creating a Drop-Down List Using a Named Range
FIGURE 7.30 The Developer Tab
FIGURE 7.31 Insert Controls Icon
FIGURE 7.32 Form Controls in Excel for Mac
FIGURE 7.33 Inserting the Checkbox
FIGURE 7.34 Selecting Format Control to Assign Checkbox Options
FIGURE 7.35 Format Control Dialog Box
FIGURE 7.36 Checkboxes in a Financial Model
FIGURE 7.37 Randomly Arranged Checkboxes
FIGURE 7.38 Inserting the Option Button
FIGURE 7.39 Option Button in Excel for Mac
FIGURE 7.40 Worksheet with Option Button
FIGURE 7.41 Inserting the Spinner
FIGURE 7.42 Completed Spin Button in a Financial Model
FIGURE 7.43 Format Control Dialog Box
FIGURE 7.44 Completed Combo Box
FIGURE 7.45 Inserting the Combo Box
FIGURE 7.46 Drawing the Combo Box
FIGURE 7.47 Scenario Source Data
FIGURE 7.48 Using the Combo Box Output in a Formula
FIGURE 7.49 Checked Checkboxes Drive Calculation
FIGURE 7.50 Unselected Checkboxes Drive Calculation
Chapter 8
FIGURE 8.1 Unhiding Rows 1 and 2
FIGURE 8.2 Unhiding Rows
FIGURE 8.3 Viewing the Source Code
FIGURE 8.4 Changing the Visibility Options in the Visual Basic Editor
FIGURE 8.5 Collapsed Grouping
FIGURE 8.6 Expanded Grouping
FIGURE 8.7 Entering an Array Formula
FIGURE 8.8 Completed Array Formula
FIGURE 8.9 Temperature Data
FIGURE 8.10 Transposed Temperature Data
FIGURE 8.11 Paste Special Dialog Box
FIGURE 8.12 Creating a TRANSPOSE Array Formula
FIGURE 8.13 Loan Repayment Calculation Using PMT Function
FIGURE 8.14 Goal Seek Dialog Box
FIGURE 8.15 Creating a Structured Reference Table
FIGURE 8.16 Source Data and PivotTable
FIGURE 8.17 PivotTable Options Dialog Box
FIGURE 8.18 Recommended PivotTables
FIGURE 8.19 Creating a PivotTable
FIGURE 8.20 Completed PivotTable with Field List
FIGURE 8.21 Grouping the Dates in a PivotTable
FIGURE 8.22 Grouping by Month
FIGURE 8.23 Filter in a PivotTable
FIGURE 8.24 Selecting Multiple Items in a Filter
FIGURE 8.25 Slicer in a PivotTable
FIGURE 8.26 Increasing the Number of Columns in a Slicer
FIGURE 8.27 Macro Launch Button
FIGURE 8.28 Accessing Record Macro from the Ribbon
FIGURE 8.29 Naming the Recorded Macro
FIGURE 8.30 Viewing the Recorded Macro in the VBA Editor
FIGURE 8.31 Macro-Free Workbook Warning
Chapter 9
FIGURE 9.1 Fixed, Compounding Exercise
FIGURE 9.2 Completed Fixed, Compounding Exercise
FIGURE 9.3 Fixed, Non-compounding Exercise
FIGURE 9.4 Relative, Compounding Exercise and FVSCHEDULE Function
FIGURE 9.5 Relative, Non-compounding Exercise
FIGURE 9.6 Complex Escalation
FIGURE 9.7 Completed Staff Calculation Using Exponential Growth Rates
FIGURE 9.8 Assumption Documentation in Growth Calculations
FIGURE 9.9 Comparison of Interest Rates
FIGURE 9.10 Comparison of Rates with Changed Compounding Periods
FIGURE 9.11 Method 1: Cumulative Total Using Inconsistent Formulas
FIGURE 9.12 Method 2: Cumulative Totals Using a Consistent Formula
FIGURE 9.13 Manually Calculating the Payback Year
FIGURE 9.14 Completed Simple Payback Calculation
FIGURE 9.15 Payback Period Workings
FIGURE 9.16 Completed Payback Calculation
FIGURE 9.17 WACC Calculation Layout
FIGURE 9.18 WACC Completed Calculation
FIGURE 9.19 Completed Flat-Tiered Pricing Calculation
FIGURE 9.20 Tiered Personal Australian Tax Calculation Example
FIGURE 9.21 Completed Progressive Tiered Calculation
FIGURE 9.22 Comparison of Different Depreciation Methods
FIGURE 9.23 Asset Depreciation Example
FIGURE 9.24 Calculating Fixed Declining Depreciation
FIGURE 9.25 Calculating Double Declining Depreciation
FIGURE 9.26 Calculating Sum of the Years' Digits Depreciation
FIGURE 9.27 Completed Depreciation Calculation on Fixed Assets
FIGURE 9.28 Break-Even Calculations
FIGURE 9.29 Chart Showing Break-Even Point
FIGURE 9.30 Break-Even Number of Units Using a Formula
FIGURE 9.31 Using Goal Seek to Calculate Break-Even Point
FIGURE 9.32 Completed Break-Even Goal Seek
FIGURE 9.33 Break-Even Goal Seek with Changed Inputs
Chapter 10
FIGURE 10.1 Trace Precedents in Formula Auditing Icons in the Ribbon
FIGURE 10.2 Using Trace Precedents on a Formula
FIGURE 10.3 Error-Checking Tools Showing Error in Sum Formula
FIGURE 10.4 Editing Error-Checking Options
FIGURE 10.5 Error-Checking Feature
FIGURE 10.6 Tracing the Error Source
FIGURE 10.7 Evaluate Formula Dialog Box
FIGURE 10.8 Viewing the Value of Part of the Formula
FIGURE 10.9 Show Formulas Option in the Ribbon
FIGURE 10.10 Disabling Direct Editing in Cells
FIGURE 10.11 Double-Clicking with Direct Editing Disabled
FIGURE 10.12 Accessing Inspect Workbook
FIGURE 10.13 Save File in .xlsx Excel Workbook Format
Chapter 11
FIGURE 11.1 Data Validation Drop-Down Box
FIGURE 11.2 Combo Box Drop-Down
FIGURE 11.3 Scenario Manager Dialog Box
FIGURE 11.4 Scenario Manager Example
FIGURE 11.5 Loan Calculation Layout
FIGURE 11.6 One-Variable Data Table
FIGURE 11.7 Completed One-Variable Data Table
FIGURE 11.8 Two-Variable Data Table
FIGURE 11.9 Completed Two-Variable Data Table
FIGURE 11.10 Data Table with Colour Scales
FIGURE 11.11 Completed Data Table Using Advanced Conditional Formatting
FIGURE 11.12 Highlighting Selected Interest Scenario Using Conditional Formatti...
FIGURE 11.13 Conditional Formatting Rule Dialog Box
FIGURE 11.14 Conditional Formatting Rule Manager Dialog Box
FIGURE 11.15 Conditional Formatting Rule Showing Intersection of Inputs
FIGURE 11.16 Model Layout for Drop-Down Scenario Method
FIGURE 11.17 Model Scenario Inputs
FIGURE 11.18 Data Validation Drop-Down List Dialog Box
FIGURE 11.19 Data Validation Drop-Down List
FIGURE 11.20 Completed Data Validation Drop-Down Box Model with Scenario Formul...
FIGURE 11.21 Combo Box Format Control Dialog Box
FIGURE 11.22 Model with Combo Box Drop-Down
FIGURE 11.23 Completed Combo Box Model
FIGURE 11.24 Combo Box Drop-Down with Horizontally Oriented Source Data
FIGURE 11.25 Model Layout for Data Validation Scenario Method
FIGURE 11.26 Creating a Two-Variable Data Table
FIGURE 11.27 Completed Data Table
FIGURE 11.28 Completed Probability-Weighted Predicted Outcome
Chapter 12
FIGURE 12.1 Line Chart with Multiple Series
FIGURE 12.2 Chart on Two Different Axes and Chart Types (Combo Chart)
FIGURE 12.3 Comparison of Single Series Chart Types
FIGURE 12.4 Comparison of Multi-series Chart Types
FIGURE 12.5 Donut Chart
FIGURE 12.6 Combination Chart
FIGURE 12.7 Map Chart
FIGURE 12.8 Bubble Chart
FIGURE 12.9 Editing from the Chart
FIGURE 12.10 Adding Chart Elements in Excel for Mac
FIGURE 12.11 Pie Chart Depicting Units Sold Data
FIGURE 12.12 Changing the Data Source to Depict Expenses
FIGURE 12.13 Single-Series Column Chart
FIGURE 12.14 Double-Series Clustered Column Chart
FIGURE 12.15 Edit Series Dialog Box with Incorrect Series Values Data
FIGURE 12.16 Using a Chart Template
FIGURE 12.17 Changing the Hidden and Empty Cells Option
FIGURE 12.18 Line Chart with Data Table
FIGURE 12.19 New Data Not Included in Formula
FIGURE 12.20 New Data Included in Named Range, and Formula
FIGURE 12.21 Having a Title in Column A Will Expand the Named Range by One Row
FIGURE 12.22 Chart with Variable Number of Tenants
FIGURE 12.23 Chart with Fixed Number of Tenants
FIGURE 12.24 Create Three Dynamic Named Ranges, One for Each Series in the Char...
FIGURE 12.25 Referring to the Named Range in the Chart Series
FIGURE 12.26 Selecting Non-consecutive Ranges by Holding Down the Control Key
FIGURE 12.27 Insert Chart Dialog Box
FIGURE 12.28 Completed Combo Chart
FIGURE 12.29 Data Shown in a Two-Dimensional Chart
FIGURE 12.30 Inserting the Bubble Chart
FIGURE 12.31 Changing the Labels
FIGURE 12.32 Completed Bubble Chart
FIGURE 12.33 Creating the “Active Range”
FIGURE 12.34 Creating a Chart Based on the Active Range
FIGURE 12.35 Completed Dynamic Chart
FIGURE 12.36 Completed Dynamic Chart with Linked Text Box
FIGURE 12.37 Completed Company Profit Waterfall
FIGURE 12.38 Cumulative Bar Chart
FIGURE 12.39 Creating a Waterfall Chart
FIGURE 12.40 Editing the Source Data Range of the Waterfall Chart
FIGURE 12.41 Setting the Total Column
FIGURE 12.42 Completed Waterfall Chart
Guide
Cover
Table of Contents
Begin Reading
Pages
ii
iii
iv
v
xi
xii
xiii
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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
397
399
400
401
403
404
405
406
407
408
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset