Financial Modeling for Decision Making:Using MS-Excel in Accounting and Finance

Length: 360 pages
Edition: 1
Language: English
Publisher: Emerald Publishing
Publication Date: 2020-09-01
ISBN-10: 1789734142
ISBN-13: 9781789734140
Sales Rank: #4000979 (See Top 100 Books)

Most books on Microsoft Excel for accounting and finance serve primarily academic research purposes: they are relatively unstructured and dominated by discussions of rather esoteric and infrequently used applications. None addresses the growing need for an introduction to crucial data analytic skills (i.e., analysis of ‘big data’) and to the recent innovations by Microsoft Excel in this regard, including Power Query (data cleaning and management), Power Pivot (advanced pivot tables using databases) and Power BI (creating executive KPI dashboards).
In Financial Modeling for Decision Making: Using MS-Excel in Accounting and Finance, Ron Messer provides just such a practical, advanced-level guide to this essential program. Crucially, he focuses on using Excel in situations encountered by accounting and finance students and professionals, and he structures these in terms of the past, present and future in order to reflect a typical operating cycle, which includes initial planning, exercising control, and receiving feedback. Throughout this book, Messer discusses real-life applications of this important analytical tool, which has become the industry standard for spreadsheet software.
For its clear structure and emphasis on practical usage, Financial Modeling for Decision Making is essential reading for accounting and finance professionals, as well as accounting and finance students in post-secondary institutions.

Cover
Title
Copyright
Dedication
Contents
About the Author
    Snapshot
    Key Research
    Organizational Strategy
    Financial Planning
    Supply Chain Management
    Impact of Research
    What’s Next?
Brief Summary
Preface
    Unique Aspects of this Book
    Organization of this Book
Chapter 1: Better Learning Decisions
    How to Learn (and Teach) Financial Modeling
        Learning Financial Modeling
        Teaching Financial Modeling
    Steps in Developing a Financial Model
        Some Common Excel Terminology
    Good Financial Modeling Practices
    Demonstration Exercise
        Case Facts
Part 1: Decisions Made about the Future
    Planning
    Chapter 2: Break Even Decisions
        Background Theory
            Cost-Volume-Profit Analysis
            Determining Cost Functions
            Break-even Volumes
        Financial Management Techniques
        Relevant MS-Excel Functionality
        Demonstration Exercise
            Case Facts
    Chapter 3: Times Series Forecasting Decisions
        Background Theory
            Types of Forecasting Models
            Detecting Patterns in Time
            Time Series Forecasting Methods
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Case Facts
            Step 1: Create a Scatterplot to Assess whether a Pattern Exists
            Step 2: Perform a Moving Average Forecast for at Least Three Time Intervals
            Step 3: Perform an Exponential Smoothing Forecast Using at Least Two Dampening Factors
            Step 4: Optimize the Exponential Smoothing Dampening Coefficient
            Step 5: Calculate and Evaluate the MAPE for all Forecasts
    Chapter 4: Capital Budgeting Decisions
        Background Theory
            The Importance of Cash Flows
            NPV, IRR, and Payback Period
            The Discount Rate
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Case Facts
            Step 1: Determining the Discount Rate Using a Risk-adjusted Cost of Capital
            Step 2: Determining the Incremental Net Cash Flows for the Project
            Step 3: Developing a Base Case Scenario for to the Key Decision Variables
            Step 4: Determining the NPV, IRR and Payback Period for Each Scenario
    Chapter 5: Regression Analysis Decisions
        Background Theory
            Finding Correlations
            Determining Causation
            Regression Analysis
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Case Facts
            Step 1: Exploring Relationships
            Step 2: Creating Scatterplots
            Step 3: Measuring Correlations
            Step 4: Determine Dependent and Independent Variables
            Step 5: Create a Single-variable Regression
            Step 6: Create a Multivariable Regression
            Step 7: Optimize the Model
            Nonnumeric Variables
            Interaction Variables
Part 2: Decisions Made about the Present
    Control
    Chapter 6: Linear Programming Decisions
        Background Theory
            Some History
            Product Mix
            Production Scheduling
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise 1
            Case Facts
            Step 1: Create the Excel Worksheet Model
            Step 2: Complete the Excel Solver Dialog Box
            Step 3: Activate Solver and Generate the Sensitivity Report
        Demonstration Exercise 2
            Case Facts
    Chapter 7: Business Valuation Decisions
        Background Theory
        Analyzing the Cost of Capital
            Pro-forma Financial Statements
            Forecasting Assumptions
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Case Facts
            Step 1: Calculate Financial Ratios for Forecasting
            Step 2: Develop Reasonable Forecast Assumptions
            Step 3: Create the Forecast Income Statement and Balance Sheet
            Step 4: Convert Net Income into Cash Flows
            Step 5: Calculate the Present Value of the Cash Flows
            Step 6: Determine the Share Value and Test Sensitivities
    Chapter 8: Pivot Tables Decisions
        Background Theory
            Everything Old Is New Again
            Some Data Terminology
            Types of Data Analytics
        Financial Management Techniques
            Interpretation 1: Expressed as a Percentage of the Row Total
            Interpretation 2: Expressed as a Percentage of the Column Total
            Interpretation 3: Expressed as a Percentage of the Grand Total
        Relevant Excel Functionality
            Creating Data Tables
            Cleaning Data
        Demonstration Exercise
            Case Facts
            Descriptive Analytics
            Step 1: Identify the Fields Needed to Answer the Question
            Step 2: Put the Fields in Rows and Columns
            Step 3: Determine the Type of Values Function
            Step 4: Select a Field for the Values Function
            Step 5: Numeric Format for Results
            Diagnostic Analytics
            Some Additional Analytic Functions with Power Pivot
Part 3: Decisions Made about the Past
    FEEDBACK
    Chapter 9: Financial Dashboard Decisions
        Background Theory
            Some Common Financial Measures
            Some Dashboard Principles
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Step 1: Create the Database
            Step 2: Create the Data Engine
            Step 3: Create the References
            Step 4: Create the Dashboard
        About Power BI (Business Intelligence)
        Demonstration Exercise
            Loading Power BI
            Using Power BI
            Using Power Query
            Creating Data Visualizations
            Current Ratio Metric
            Profitability Metric
            Cash Flow Metric
            Return on Equity Metric
            Sales and Income Trends
    Chapter 10: Budget Management Decisions
        Background Theory
            Budget Development
            Budget Management
            Cash Flow Budgets
        Financial Management Techniques
        Relevant Excel Functionality
        Demonstration Exercise
            Case Facts
            Preparing the Income Statement Budget
            Preparing the Monthly Income Statement Budget
            Preparing the Cash Flow Budget
            Preparing the Analysis of Variances
    Chapter 11: Amortization Table Decisions
        Background Theory
            Corporate Bonds
            Bank Borrowing
            Property, Plant, and Equipment
        Financial Management Techniques
        Using MS-Excel Functionality
        Demonstration Exercise
            Corporate Bonds
            Bank Borrowing
            Property, Plant, and Equipment
Part 4: Decisions Made in Time
    PUTTING IT ALL TOGETHER
    Chapter 12: Making a Lot of Decisions
        Background Theory
        (Capstone) Demonstration Exercise
            Case Facts
                Planning Decisions
                Control Decisions
                Feedback Decisions
Appendix 1: Excel Shortcut Keys
Appendix 2: Common Financial Ratios
Index