Using Excel for Business Analysis : A Guide to Financial Modelling Fundamentals.
Title:
Using Excel for Business Analysis : A Guide to Financial Modelling Fundamentals.
ISBN:
9781119062448
Edition:
2nd ed.
Physical Description:
1 online resource (426 pages)
Contents:
Using Excel for Business Analysis -- 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: Power Pivot, Access, and Project -- The Final Decision -- 32-Bit versus 64-Bit Excel -- 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 -- Enter Data Only 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 -- How Long Should a Formula Be? -- Linking to External Files -- Why You Should Use Named Ranges in External Links -- 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 -- Summary of New Features -- Other Features -- Handy Excel Shortcuts -- Windows Shortcuts -- Mac Shortcuts -- Basic Excel Functions -- SUM() -- MAX () -- MIN() -- 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 -- Mixed Referencing Exercise -- 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 -- AVERAGEIF -- COUNTIFS -- SUMIFS -- AVERAGEIFS -- Filtering IFS Functions by a Variable Value -- LOOKUP Formulas -- VLOOKUP (Vertical Lookup) -- HLOOKUP (Horizontal Lookup) -- LOOKUP Function.
Nesting INDEX and MATCH -- Using INDEX and MATCH to Create a More Robust Formula -- OFFSET Function -- Regression Analysis -- Using a FORECAST or TREND Function -- CHOOSE Function -- Working with Dates -- Handy Functions -- Date Format Dilemma -- Financial Project Evaluation Functions -- Net Present Value -- 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 -- 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 -- 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 -- Useful Display Settings -- Minimising the Ribbon -- Restrict the Work Area -- Restricting Incorrect Data Entry with Data Validations -- Using Validations to Create a Drop-Down List -- 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 -- Structured Reference Tables -- PivotTables -- Using PivotTables in Financial Modelling -- How to Create a PivotTable -- Filtering and Using Slicers -- Macros -- Macro Settings -- Recording and Running a Simple Macro -- Creating Macro Buttons -- Macros in Financial Modelling Case Studies -- Dangers and Pitfalls of Using Macros -- 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 -- 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 -- 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 -- 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 -- Improving Model Performance -- Reducing File Size -- Improving Excel Memory and File Performance -- Auditing a Financial Model -- Informal Check -- QA Procedure -- QA Logic Testing -- Input Testing -- 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? -- 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 -- Two-Variable Data Table -- Advanced Conditional Formatting -- Comparing Scenario Methods -- Manual Sensitivity Analysis -- Creating Scenarios Using a Two-Variable Data Table -- Summary -- Chapter 12 Presenting Model Output -- Preparing an Oral Presentation for Model Results -- Summarizing 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 -- Another Example with More Data -- Working with Charts -- Changing the Type of Chart -- Changing the Source Data -- Saving a Chart as a Template -- Handy Charting Hints -- Dynamic Named Ranges -- Using a Dynamic Range Name in a Chart -- Charting with Two Different Axes and Chart Types -- Creating a Combo Chart in Excel 2013 -- Creating a Combo Chart in Previous Versions of Excel -- Bubble Charts -- Creating a Dynamic Chart -- Additional Exercise -- Waterfall Charts -- Creating a Basic Waterfall Chart Using the Dummy Stack Method -- Creating a Waterfall Chart Using Up/Down Bars -- Creating a Complex Waterfall Chart (with Positive and Negative Values Crossing the X-Axis) -- Summary -- About the Author -- About the Website -- Index -- EULA.
Abstract:
Utilise Excel 2013 capabilities to build effective financial models Using Excel for Business Analysis, Revised Edition provides practical guidance for anyone looking to build financial models. Whether for business proposals, opportunity evaluation, financial reports, or any other business finance application, this book shows you how to design, create, and test your model, then present your results effectively using Excel 2013. The book opens with a general guide to financial modelling, with each subsequent chapter building skill upon skill until you have a real, working model of your own. Financial tools, features, and functions are covered in detail from a practical perspective, and put in context with application to real-world examples. Each chapter focuses on a different aspect of Excel modelling, including step-by-step instructions that walk you through each feature, and the companion website provides live model worksheets that give you the real hands-on practice you need to start doing your job faster, more efficiently, and with fewer errors. Financial modelling is an invaluable business tool, and Excel 2013 is capable of supporting the most common and useful models most businesses need. This book shows you how to dig deeper into Excel's functionality to craft effective financial models and provide important information that informs good decision-making. Learn financial modelling techniques and best practice Master the formulas and functions that bring your model to life Apply stress testing and sensitivity analysis with advanced conditionals Present your results effectively, whether graphically, orally, or written A deceptively powerful application, Excel supports many hundreds of tools, features, and functions; Using Excel for Business Analysis eliminates the irrelevant to focus on those that are most useful to business finance users, with
detailed guidance toward utilisation and best practice.
Local Note:
Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2017. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.
Genre:
Electronic Access:
Click to View