Оглавление
Fairhurst Danielle Stein. Using Excel for Business Analysis
Preface
BOOK OVERVIEW
ACKNOWLEDGMENTS
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
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 FLOW CHARTING
STEPS TO BUILDING A MODEL
INFORMATION REQUESTS
VERSION-CONTROL DOCUMENTATION
SUMMARY
CHAPTER 3. Best Practice Principles of Modelling
DOCUMENT YOUR ASSUMPTIONS
LINKING, NOT HARD CODING
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
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
SUMMARY
CHAPTER 5. Using Excel in Financial Modelling
FORMULAS AND FUNCTIONS IN EXCEL
EXCEL VERSIONS
HANDY EXCEL SHORTCUTS
BASIC EXCEL FUNCTIONS
LOGICAL FUNCTIONS
NESTING: COMBINING SIMPLE FUNCTIONS TO CREATE COMPLEX FORMULAS
CELL REFERENCING BEST PRACTICES
NAMED RANGES
SUMMARY
CHAPTER 6. Functions for Financial Modelling
AGGREGATION FUNCTIONS
LOOKUP FORMULAS
NESTING INDEX AND MATCH
OFFSET FUNCTION
REGRESSION ANALYSIS
CHOOSE FUNCTION
WORKING WITH DATES
FINANCIAL PROJECT EVALUATION FUNCTIONS
LOAN CALCULATIONS
CHAPTER 7. Tools for Model Display
BASIC FORMATTING
CUSTOM FORMATTING
CONDITIONAL FORMATTING
SPARKLINES
s 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 CUMULATIVE 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 10.1: 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
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
WILEY END USER LICENSE AGREEMENT
Отрывок из книги
This book was written from course materials compiled over many years of training in analytical courses in Australia and globally – most frequently courses such as Financial Modelling in Excel, Data Analysis & Reporting in Excel, and Budgeting & Forecasting in Excel, both as face-to-face workshops and online courses. The common theme is the use of Microsoft Excel, and I’ve refined the content to suit the hundreds of participants and their questions over the years. This content has been honed and refined by the many participants in these courses, who are my intended readers. This book is aimed at you, the many people who seek financial analysis training (either by attending a seminar or self-paced by reading this book) because you are seeking to improve your skills to perform better in your current role, or to get a new and better job.
When I started financial modelling in the early nineties, it was not called financial modelling– it was just “using Excel for business analysis,” and this is what I’ve called this book. It was only just after the new millennium that the term financial modelling gained popularity in its own right and became a required skill often listed on analytical job descriptions. This book spends quite a bit of time in Chapter 1 defining the meaning of a financial model, as it’s often thought to be something that is far more complicated than it actually is. Many analysts I’ve met are building financial models already without realising it, but they do themselves a disservice by not calling their models, “models”!
.....
MS Project
MS Project is specifically for creating project plans and associated component tasks, assigning resources to those tasks, tracking progress, managing budgets, and monitoring workloads. The user can also create critical path schedules and Gantt charts.
.....
