| Introduction |
|
1 | (4) |
|
|
|
1 | (1) |
|
|
|
2 | (1) |
|
|
|
2 | (1) |
|
|
|
3 | (1) |
|
|
|
3 | (2) |
|
PART 1 GETTING STARTED WITH FINANCIAL MODELING |
|
|
5 | (92) |
|
Chapter 1 Introducing Financial Modeling |
|
|
7 | (8) |
|
Defining Financial Modeling |
|
|
7 | (1) |
|
|
|
8 | (1) |
|
|
|
9 | (1) |
|
|
|
10 | (1) |
|
Looking at Examples of Financial Models |
|
|
10 | (1) |
|
|
|
11 | (1) |
|
|
|
12 | (1) |
|
Integrated financial statement models |
|
|
12 | (1) |
|
|
|
12 | (1) |
|
|
|
13 | (2) |
|
Chapter 2 Getting Acquainted with Excel |
|
|
15 | (20) |
|
Making Sense of the Different Versions of Excel |
|
|
15 | (1) |
|
A rundown of recent Excel versions |
|
|
16 | (7) |
|
|
|
23 | (1) |
|
|
|
23 | (2) |
|
Recognizing the Dangers of Using Excel |
|
|
25 | (1) |
|
|
|
26 | (1) |
|
|
|
27 | (1) |
|
|
|
28 | (3) |
|
Looking at Alternatives and Supplements to Excel |
|
|
31 | (4) |
|
Chapter 3 Planning and Designing Your Financial Model |
|
|
35 | (18) |
|
Identifying the Problem That Your Financial Model Needs to Solve |
|
|
35 | (4) |
|
Designing How the Problem's Answer Will Look |
|
|
39 | (6) |
|
Gathering Data to Put in Your Model |
|
|
45 | (1) |
|
Documenting the Limitations of Your Model |
|
|
46 | (1) |
|
Considering the Layout and Design of Your Model |
|
|
47 | (2) |
|
Structuring your model: What goes where |
|
|
49 | (1) |
|
Defining inputs, calculations, and output blocks |
|
|
50 | (1) |
|
Determining your audience |
|
|
51 | (2) |
|
Chapter 4 Building a Financial Model by the Rulebook |
|
|
53 | (18) |
|
Document Your Assumptions |
|
|
53 | (6) |
|
Create Dynamic Formulas Using Links |
|
|
59 | (2) |
|
|
|
61 | (1) |
|
Model with Consistent Formulas |
|
|
62 | (2) |
|
|
|
64 | (2) |
|
Allowing tolerance for error |
|
|
66 | (1) |
|
Applying conditional formatting to an error check |
|
|
67 | (1) |
|
Format and Label for Clarity |
|
|
68 | (3) |
|
Chapter 5 Using Someone Else's Financial Model |
|
|
71 | (26) |
|
Considering Templates for Building a Financial Model |
|
|
72 | (1) |
|
Why templates can be appealing |
|
|
72 | (1) |
|
What's wrong with using templates |
|
|
72 | (2) |
|
Why you should build your own model |
|
|
74 | (1) |
|
Inheriting a File: What to Check For |
|
|
75 | (1) |
|
Meeting a model for the first time |
|
|
76 | (1) |
|
|
|
77 | (7) |
|
Using Audit Tools to Find and Correct Errors |
|
|
84 | (1) |
|
Checking a model for accuracy |
|
|
85 | (3) |
|
Making sense of the formulas |
|
|
88 | (7) |
|
Sharing and Version Control |
|
|
95 | (2) |
|
PART 2 DIVING DEEP INTO EXCEL |
|
|
97 | (130) |
|
Chapter 6 Excel Tools and Techniques for Financial Modeling |
|
|
99 | (32) |
|
|
|
100 | (1) |
|
Relative cell referencing |
|
|
101 | (2) |
|
Absolute cell referencing |
|
|
103 | (3) |
|
|
|
106 | (3) |
|
|
|
109 | (1) |
|
Understanding why you may want to use a named range |
|
|
109 | (1) |
|
|
|
110 | (1) |
|
Finding and using named ranges |
|
|
111 | (2) |
|
Editing or deleting a named range |
|
|
113 | (1) |
|
|
|
113 | (1) |
|
|
|
114 | (1) |
|
|
|
115 | (2) |
|
|
|
117 | (3) |
|
|
|
120 | (3) |
|
Restricting and Validating Data |
|
|
123 | (1) |
|
Restricting user data entry |
|
|
124 | (1) |
|
Creating drop-down boxes with data validations |
|
|
125 | (1) |
|
Protecting and locking cells |
|
|
126 | (1) |
|
|
|
127 | (1) |
|
Limiting project costs with a goal seek |
|
|
128 | (1) |
|
Calculating a break-even point with a goal seek |
|
|
129 | (2) |
|
Chapter 7 Using Functions in Excel |
|
|
131 | (42) |
|
Identifying the Difference between a Formula and a Function |
|
|
131 | (1) |
|
Finding the Function You Need |
|
|
132 | (1) |
|
Getting Familiar with the Most Important Functions |
|
|
133 | (1) |
|
|
|
134 | (1) |
|
|
|
135 | (4) |
|
|
|
139 | (1) |
|
|
|
140 | (6) |
|
Round, Roundup, and Rounddown |
|
|
146 | (7) |
|
|
|
153 | (3) |
|
|
|
156 | (2) |
|
Reporting sales with SUMIF |
|
|
158 | (3) |
|
Vlookup, Hlookup, and Xlookup |
|
|
161 | (9) |
|
Being Aware of Advanced Functions and Functionality |
|
|
170 | (3) |
|
Chapter 8 Applying Scenarios to Your Financial Model |
|
|
173 | (22) |
|
Identifying the Differences among Types of Analysis |
|
|
174 | (1) |
|
Building Drop-Down Scenarios |
|
|
175 | (1) |
|
Using data validations to model profitability scenarios |
|
|
175 | (3) |
|
Applying formulas to scenarios |
|
|
178 | (3) |
|
Applying Sensitivity Analysis with Data Tables |
|
|
181 | (1) |
|
Setting up the calculation |
|
|
181 | (1) |
|
Building a data table with one input |
|
|
182 | (2) |
|
Building a data table with two inputs |
|
|
184 | (2) |
|
Applying probability weightings to your data table |
|
|
186 | (3) |
|
Using Scenario Manager to Model Loan Calculations |
|
|
189 | (1) |
|
|
|
189 | (2) |
|
Applying Scenario Manager |
|
|
191 | (4) |
|
Chapter 9 Charting and Presenting Model Output |
|
|
195 | (32) |
|
Deciding Which Data to Display |
|
|
196 | (2) |
|
Conveying Your Message by Charting Scenarios |
|
|
198 | (2) |
|
Deciding Which Type of Chart to Use |
|
|
200 | (1) |
|
|
|
201 | (5) |
|
|
|
206 | (3) |
|
|
|
209 | (2) |
|
|
|
211 | (3) |
|
Charts in newer versions of Excel |
|
|
214 | (5) |
|
|
|
219 | (1) |
|
Building the chart on formula-driven data |
|
|
219 | (1) |
|
Linking the chart titles to formulas |
|
|
220 | (1) |
|
|
|
221 | (4) |
|
|
|
225 | (2) |
|
PART 3 BUILDING YOUR FINANCIAL MODEL |
|
|
227 | (64) |
|
Chapter 10 Building an Integrated Financial Statements Model |
|
|
229 | (34) |
|
Getting to Know the Case Study |
|
|
230 | (1) |
|
|
|
231 | (1) |
|
|
|
232 | (1) |
|
|
|
233 | (1) |
|
|
|
234 | (1) |
|
|
|
234 | (1) |
|
|
|
235 | (2) |
|
|
|
237 | (1) |
|
|
|
238 | (1) |
|
|
|
238 | (1) |
|
|
|
239 | (1) |
|
Depreciation and amortization |
|
|
240 | (3) |
|
Building the Income Statement |
|
|
243 | (5) |
|
Building the Cash Flow Statement |
|
|
248 | (4) |
|
Building the Balance Sheet |
|
|
252 | (6) |
|
|
|
258 | (1) |
|
Entering your scenario assumptions |
|
|
258 | (1) |
|
|
|
259 | (1) |
|
Building the scenario functionality |
|
|
260 | (3) |
|
Chapter 11 Building a Discounted Cash Flow Valuation |
|
|
263 | (10) |
|
Understanding How the Discounted Cash Flow Valuation Works |
|
|
264 | (1) |
|
Step 1 Calculating Free Cash Flow to Firm |
|
|
265 | (3) |
|
Step 2 Calculating Weighted Average Cost of Capital |
|
|
268 | (1) |
|
Step 3 Finding the Terminal Value |
|
|
269 | (1) |
|
Discounting Cash Flows and Valuation |
|
|
270 | (3) |
|
Chapter 12 Budgeting for Capital Expenditure and Depreciation |
|
|
273 | (18) |
|
|
|
274 | (1) |
|
Making a reusable budget model template |
|
|
274 | (3) |
|
|
|
277 | (1) |
|
Output 1 Calculating Cash Required for Budgeted Asset Purchases |
|
|
277 | (5) |
|
Output 2 Calculating Budgeted Depreciation |
|
|
282 | (1) |
|
|
|
283 | (1) |
|
|
|
284 | (1) |
|
The depreciation schedule for the current year |
|
|
285 | (2) |
|
Depreciation in prior periods |
|
|
287 | (1) |
|
Output 3 Calculating the Written-Down Value of Assets for the Balance Sheet |
|
|
288 | (3) |
|
|
|
291 | (24) |
|
Chapter 13 Ten Strategies for Reducing Error |
|
|
293 | (10) |
|
|
|
293 | (1) |
|
|
|
294 | (1) |
|
|
|
295 | (1) |
|
Getting Someone Else to Check Your Work |
|
|
296 | (1) |
|
|
|
297 | (1) |
|
Documenting Methodology with a Flowchart |
|
|
297 | (1) |
|
Stress-Testing with Sensitivity Analysis |
|
|
298 | (1) |
|
Conducting a Scenario Analysis |
|
|
299 | (1) |
|
Taking Note of Excel Error Values |
|
|
300 | (2) |
|
|
|
302 | (1) |
|
Chapter 14 Ten Common Pitfalls to Avoid |
|
|
303 | (12) |
|
|
|
303 | (1) |
|
You're Getting #REF! Errors |
|
|
304 | (1) |
|
You Have Circular References |
|
|
304 | (3) |
|
The Model Has Too Much Detail |
|
|
307 | (1) |
|
The File Size Is Out of Control |
|
|
307 | (2) |
|
Your Model Is Full of "Spaghetti" Links |
|
|
309 | (2) |
|
The Formulas Are Unnecessarily Long and Complicated |
|
|
311 | (1) |
|
No One Is Paying Attention to the Model |
|
|
312 | (1) |
|
|
|
313 | (1) |
|
Someone Messes Up Your Model |
|
|
313 | (2) |
| Index |
|
315 | |
Lisainfo e-raamatute kohta