𝔖 Scriptorium
✦   LIBER   ✦

πŸ“

Using Excel for Business Analysis, + Website: A Guide to Financial Modelling Fundamentals

✍ Scribed by Danielle Stein Fairhurst


Publisher
Wiley
Year
2012
Tongue
English
Leaves
338
Category
Library

⬇  Acquire This Volume

No coin nor oath required. For personal study only.

✦ Synopsis


A clear, concise, and easy-to-use guide to financial modelling suitable for practitioners at every level Using a fundamental approach to financial modelling that's accessible to both new and experienced professionals, Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals + Website offers practical guidance for anyone looking to build financial models for business proposals, to evaluate opportunities, or to craft financial reports. Comprehensive in nature, the book covers the principles and best practices of financial modelling, including the Excel tools, formulas, and functions to master, and the techniques and strategies necessary to eliminate errors. As well as explaining the essentials of financial modelling, Using Excel for Business Analysis is packed with exercises and case studies to help you practice and test your comprehension, and includes additional resources online. Provides comprehensive coverage of the principles and best practices of financial modeling, including planning, how to structure a model, layout, the anatomy of a good model, rebuilding an inherited model, and much more Demonstrates the technical Excel tools and techniques needed to build a good model successfully Outlines the skills you need to learn in order to be a good financial modeller, such as technical, design, and business and industry knowledge Illustrates successful best practice modeling techniques such as linking, formula consistency, formatting, and labeling Describes strategies for reducing errors and how to build error checks and other methods to ensure accurate and robust models A practical guide for professionals, including those who do not come from a financial background, Using Excel for Business Analysis is a fundamentals-rich approach to financial modeling.

✦ Table of Contents


Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals
Contents
Preface
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
Is Excel Really the Best Option?
Evaluating Modelling Tools
Budgeting and Forecasting
Microsoft Office Tools: Excel, Access, and Project
The Final Decision
What Skills Do You Need to Be a Good Financial Modeller?
Spreadsheet and Technical Excel Skills
Industry Knowledge
Accounting Knowledge
Business Knowledge
Aesthetic Design Skills
Communication and Language Skills
Numeracy Skills
Ability to Think Logically
The Ideal Financial Modeller
What's the Typical Background for a Financial Modeller?
Training Courses
Do You Really Need an Advanced Excel Course?
Summary
Chapter 2: Building a Model
Model Design
Practical Example 1β€”Assumptions Layout
Practical Example 2β€”Summary Categorisation
The Golden Rules for Model Design
Separate Inputs, Calculations, and Results, Where Possible
Use Each Column for the Same Purpose
Use One Formula per Row or Column
Refer to the Left and Above
Use Multiple Worksheets
Include Documentation Sheets
Design Issues
The Workbook Anatomy of a Model
Workbook Anatomy Issues
Project Planning Your Model
How Long Does It Take to Build a Financial Model?
Building a Model under Pressure
Model Layout Flow Charting
Steps to Building a Model
The Streamlined Version
The Team Version
Information Requests
Version-Control Documentation
File Structure
Summary
Chapter 3: Best Practice Principles of Modelling
Document Your Assumptions
Linking, Not Hard Coding
Only Enter Data Once
Avoid Bad Habits
Use Consistent Formulas
Format and Label Clearly
Methods and Tools of Assumptions Documentation
In-Cell Comments
Footnoting
Hyperlinks
Hard-Coded Text
Linked Dynamic Text Assumptions Documentation
Practical Exercise 1
Practical Exercise 2
Practical Exercise 3
What Makes a Good Model?
Standards in Financial Modelling
Summary
Chapter 4: Financial Modelling Techniques
The Problem with Excel
Error Avoidance Strategies
Avoiding Simple Formula Errors
Avoiding Logic Errors
How Long Should a Formula Be?
Linking to External Files
Why You Should Use Named Ranges in External Links
Dealing with Links and the Potential Errors They Can Cause
Linking Do's and Don'ts
Building Error Checks
Error Check Exercise
Allowing Tolerance for Error
Error-Check Alerts
Avoid Error Displays in Formulas
Circular References
How to Fix Circular References
Circular References in Interest Calculations
Enabling Iterative Calculations
Summary
Chapter 5: Using Excel in Financial Modelling
Formulas and Functions in Excel
Excel Versions
Handy Excel Shortcuts
Windows Shortcuts
Mac Shortcuts
Basic Excel Functions
SUM()
MAX()
MIN()
COUNT()
AVERAGE()
Combining Basic Functions
Logical Functions
IF Statement
AND Statement
OR Statement
Nesting: Combining Simple Functions to Create Complex Formulas
Nested IF Functions
Cell Referencing Best Practices
Relative and Absolute Referencing
Mixed Referencing
Named Ranges
Why Use a Named Range?
Finding, Using, Editing, and Deleting Named Ranges
Summary
Chapter 6: Functions for Financial Modelling
Aggregation Functions
COUNTIF
SUMIF
COUNTIFS
SUMIFS
AVERAGEIF
AVERAGEIFS
LOOKUP Formulas
VLOOKUP (Vertical Lookup)
HLOOKUP (Horizontal Lookup)
LOOKUP Function
Other Useful Functions
Using INDEX and MATCH to Create a More Robust Formula
OFFSET Function
CHOOSE Function
Regression Analysis with a FORECAST or TREND Function
Working with Dates
Handy Functions
Date Format Dilemma
Financial Project Evaluation Functions
NPV (Net Present Value)
IRR (Internal Rate of Return)
What Difference Does an X Make? XNPV and XIRR
Loan Calculations
Loan-Interest Calculation Method
Nominal and Effective Interest Rates
Loan Repayment According to an Amortisation Schedule
Why Does the Interest Amount Decrease on a Fixed Interest Rate?
Summary
Chapter 7: Tools for Model Display
Basic Formatting
Custom Formatting
Custom Currency Symbols
Understanding Excel's Number Formats
Useful Formatting Options
Custom Formatting in Reporting
Conditional Formatting
To Apply Conditional Formatting
To Remove Conditional Formatting
Data Bars
Icon Sets and Colour Scales
Enhancements to Conditional Formatting in Excel 2010
Sparklines
Editing Sparklines
Changing Properties for a Group of Sparklines
Bulletproofing Your Model
Protection
Protect the File
Protect the Structure
Protect the Worksheet
Customising the Display Settings
Removing Row and Column Headers
Removing Sheet Tabs
Removing the Formula Bar
Minimising the Excel 2007/10 Ribbon
Restrict the Work Area
Restricting Incorrect Data Entry with Data Validations
Using Validations to Create a Drop-Down List
Referencing Source Data on Another Sheet
Form Controls
Accessing Form Controls
Showing the Developer Tab in the Ribbon
Check Boxes
Option Button
Spin Buttons
Combo Boxes
Boolean Logic (Binary Code)
Form Controls versus ActiveX Controls
Summary
Chapter 8: Tools for Financial Modelling
Hiding Sections of a Model
Columns and Rows
Sheets
Errors Caused by Hiding
Grouping
Array Formulas
Advantages and Disadvantages of Using Array Formulas
Array Formula Uses
Transposing Data Using an Array
Goal Seeking
Pivot Tables
Using Pivot Tables in Financial Modelling
Other Things You Should Know about Pivot Tables
How to Create a Pivot Table
Macros
Macro Settings
Recording and Running a Simple Macro
Creating Macro Buttons
Macros in Financial Modelling Case Studies
Dangers and Pitfalls of Using Macros
User-Defined Functions (UDFs)
Benefits of UDFs
Drawbacks of UDFs
Reusability: UDFs as Add-Ins
Summary
Chapter 9: Common Uses of Tools in Financial Modelling
Escalation Methods for Modelling
Using Absolute (Fixed) Growth Rate
Using Relative (Varying) Growth Rates
Using Exponential Operations on an Absolute (Fixed) Growth Rate
Practical Usage of Exponential Growth Rates
Understanding Nominal and Effective (Real) Rates
Adjusting Loan Rates with NOMINAL and EFFECT Functions
Exercise: Comparing Two Nominal and Effective Interest Rate Offers
Calculating Cumulative Totals
How to Calculate a Payback Period
Simple Payback Calculation
More Complex Payback Calculation
Weighted Average Cost of Capital (WACC)
How to Calculate the WACC
Exercise: Calculating the WACC in Excel
Building a Tiering Table
Flat Tiering Structure
Progressive Tiering Structure
Modelling Depreciation Methods
Why Depreciate?
Depreciation Methods
Declining Balance Value Methods
Calculating Depreciation at the End of Useful Life
Break-Even Analysis
Calculating Break-Even Point
Example Using Excel
Charting the Break-Even Point
Calculating Break-Even Using a Formula
Break-Even Analysis Using Goal Seek
Summary
Chapter 10: Model Review
Rebuilding an Inherited Model
Removing Redundant Assumptions and Source Data in a Model
Formula Auditing
Auditing a Financial Model
Informal Check
QA Procedure
Input Testing
Appendix 10.1: QA Log
Summary
Chapter 11: Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling
What's the Difference between Scenario, Sensitivity, and What-If Analysis?
Scenarios and Sensitivity Analysis in a Business Case
Stress-Testing a Financial Model versus a Business
Overview of Scenario Analysis Tools and Methods
Manual Drop-Downs
Scenario Manager
Using Data Tables for Sensitivity Analysis
Advanced Conditional Formatting
Comparing Scenario Methods
Manual Sensitivity Analysis
Summary
Chapter 12: Presenting Model Output
Preparing an Oral Presentation for Model Results
Summarising and Displaying Model Results
Preparing a Graphic or Written Presentation for Model Results
Additional Tips for Charting
Chart Types
Choosing a Chart Type
Summary of Common Charts and Applications
Detailed Chart Types
Working with Charts
Changing the Type of Chart
Changing the Source Data
Saving a Chart as a Template
Handy Charting Hints
Dynamic Range Names
Using a Dynamic Range Name in a Chart
Charting with Two Different Axes and Chart Types
Bubble Charts
Waterfall Charts
Creating a Basic Waterfall Chart (with Only Positive Values)
Creating a Waterfall Chart (with Positive and Negative Values)
Creating a Complex Waterfall Chart (with Positive and Negative Values Crossing the x-Axis)
Summary
About the Author
About the Website
Index


πŸ“œ SIMILAR VOLUMES


Using Excel for Business Analysis, + Web
✍ Danielle Stein Fairhurst πŸ“‚ Library πŸ“… 2012 πŸ› Wiley 🌐 English

<b>A clear, concise, and easy-to-use guide to financial modelling suitable for practitioners at every level</b><p>Using a fundamental approach to financial modelling that's accessible to both new and experienced professionals, <i>Using Excel for Business Analysis: A Guide to Financial Modelling Fund

Using Excel for business analysis : a gu
✍ Fairhurst, Danielle Stein πŸ“‚ Library πŸ“… 2015 πŸ› Wiley 🌐 English

<p><b>Utilise Excel 2013 capabilities to build effective financial models</b></p> <p><i>Using Excel for Business Analysis, Revised Edition</i> provides practical guidance for anyone looking to build financial models. Whether for business proposals, opportunity evaluation, financial reports, or any o

Using Excel for Business and Financial M
✍ Danielle Stein Fairhust πŸ“‚ Library πŸ“… 2019 🌐 English

A hands-on guide to using Excel in the business context First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and ton

Building Financial Models with Microsoft
✍ K. Scott Proctor πŸ“‚ Library πŸ“… 2009 πŸ› Wiley 🌐 English

If this book is of any value to anyone,it would new banking associates/management consultants who will be reviewing the work of others. Amazingly, the book contains numerous errors and inconsistencies among the various figures presented in each chapter. Tracking down the source of the differences b