Book Image

Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition

By : Shmuel Oluwa
Book Image

Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition

By: Shmuel Oluwa

Overview of this book

Financial modeling is a core skill required by anyone who wants to build a career in finance. Hands-On Financial Modeling with Excel for Microsoft 365 explores financial modeling terminologies with the help of Excel. Starting with the key concepts of Excel, such as formulas and functions, this updated second edition will help you to learn all about referencing frameworks and other advanced components for building financial models. As you proceed, you'll explore the advantages of Power Query, learn how to prepare a 3-statement model, inspect your financial projects, build assumptions, and analyze historical data to develop data-driven models and functional growth drivers. Next, you'll learn how to deal with iterations and provide graphical representations of ratios, before covering best practices for effective model testing. Later, you'll discover how to build a model to extract a statement of comprehensive income and financial position, and understand capital budgeting with the help of end-to-end case studies. By the end of this financial modeling Excel book, you'll have examined data from various use cases and have developed the skills you need to build financial models to extract the information required to make informed business decisions.

Related Content you might be interested in

Current Title:

Small book image
Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition
Shmuel Oluwa
Jun, 2022 | 346 pages
Small book image
Exploring Microsoft Excel’s Hidden Treasures
David Ringstrom
Sep, 2022 | 444 pages
Small book image
Building Interactive Dashboards in Microsoft 365 Excel
Michael Olafusi
Feb, 2024 | 420 pages
Small book image
Managing Data Integrity for Finance
Jane Sarah Lat
Jan, 2024 | 434 pages
Table of Contents (19 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the color images
Conventions used
Get in touch
Share Your Thoughts
1
Part 1 – Financial Modeling Overview
Part 1 – Financial Modeling Overview
Free Chapter
2
Chapter 1: An Introduction to Financial Modeling and Excel
Chapter 1: An Introduction to Financial Modeling and Excel
The main ingredients of a financial model
Understanding mathematical models
Definitions of financial models
Types of financial models
Limitations of Excel as a tool for financial modeling
Excel – the ideal tool
Summary
3
Chapter 2: Steps for Building a Financial Model
Chapter 2: Steps for Building a Financial Model
Discussions with management
Building assumptions
Building a template for your model
Uploading historical financial data
Projecting the balance sheet and profit and loss account
Additional schedules and projections
Cash flow statement
Preparing ratio analysis
Valuation
Summary
4
Part 2 – The Use of Excel Features and Functions for Financial Modeling
Part 2 – The Use of Excel Features and Functions for Financial Modeling
5
Chapter 3: Formulas and Functions – Completing Modeling Tasks with a Single Formula
Chapter 3: Formulas and Functions – Completing Modeling Tasks with a Single Formula
Understanding functions and formulas
Working with lookup functions
Utility type functions
Pivot tables and charts
Pitfalls to avoid
New functions in Excel 365
Summary
6
Chapter 4: Referencing Framework in Excel
Chapter 4: Referencing Framework in Excel
An introduction to the framework
Relative referencing
Absolute referencing
Mixed referencing
Summary
7
Chapter 5: An Introduction to Power Query
Chapter 5: An Introduction to Power Query
Highlighting common formatting mistakes
What is Power Query?
Different ways to use Power Query
Advantages of Power Query
Illustrative example
Summary
8
Part 3 – Building an Integrated 3-Statement Financial Model with Valuation by DCF
Part 3 – Building an Integrated 3-Statement Financial Model with Valuation by DCF
9
Chapter 6: Understanding Project and Building Assumptions
Chapter 6: Understanding Project and Building Assumptions
Understanding the nature and purpose of a project and discussions with management
Building assumptions
Selecting profit and loss account growth drivers
Selecting balance sheet growth drivers
Summary
10
Chapter 7: Asset and Debt Schedules
Chapter 7: Asset and Debt Schedules
Understanding the BASE and corkscrew concepts
Asset schedule
Approaches to modeling assets
Asset and depreciation schedule
Debt schedule
Creating a simple loan amortization schedule
Summary
11
Chapter 8: Preparing a Cash Flow Statement
Chapter 8: Preparing a Cash Flow Statement
Introduction to the cash flow statement
Cash flow from operations
Cash flow from investing activities
Cash flow from financing activities
Balancing the balance sheet
Troubleshooting
Circular references
Summary
12
Chapter 9: Ratio Analysis
Chapter 9: Ratio Analysis
Understanding the meaning and benefits of ratio analysis
Learning the various classes of ratios
Interpreting ratios
Understanding the limitations of ratio analysis
Summary
13
Chapter 10: Valuation
Chapter 10: Valuation
Understanding absolute valuation
Understanding relative valuation – comparative company analysis
Interpreting the results
Summary
14
Chapter 11: Model Testing for Reasonableness and Accuracy
Chapter 11: Model Testing for Reasonableness and Accuracy
Incorporating built-in tests and procedures
Troubleshooting
Understanding sensitivity analysis
Using direct and indirect methods
Understanding scenario analysis
Creating a simple Monte Carlo simulation model
Summary
15
Part 4 – Case Study
Part 4 – Case Study
16
Chapter 12: Case Study 1 – Building a Model to Extract a Balance Sheet and Profit and Loss from a Trial Balance
Chapter 12: Case Study 1 – Building a Model to Extract a Balance Sheet and Profit and Loss from a Trial Balance
Scenario for the case study
Preparing a WTB
Extracting groupings from the WTB
Preparing a model template for BS and PNL, and relevant extracts from notes and schedules
Populating BS, PNL, notes, and schedules from groupings
Preparing journal adjustments and linking to the WTB
Updating financial statements and troubleshooting to correct errors
Summary
17
Chapter 13: Case Study 2 – Creating a Model for Capital Budgeting
Chapter 13: Case Study 2 – Creating a Model for Capital Budgeting
Introduction
Understanding NPV
Understanding IRR
Understanding PI
Understanding PBP
Case study
Summary
Why subscribe?
18
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Preparing journal adjustments and linking to the WTB

You now need to address the adjustments in Part 3 of the requirements:

  1. Create a new worksheet and rename it JNLs. Prepare your journal adjustments in the following manner:

Figure 12.22 – Template for journals

  1. Enter the DR amount, then for the CR value, enter – (minus) in the cell with the DR value. This way, you reduce the exposure to typographic errors and ensure that your journals balance.

Note that the account name you use is from ACC DESCRIPTION and not ACC GRP.

Now, go to the AUDIT JOURNAL column of the WTB and enter the following formula in the first record in a cell:

Figure 12.23 – Syntax for SUMIF

The SUMIF function has three arguments:

  • Range – This refers to the range Excel will search for records matching the criteria. In this case, all of column C on the JNLs worksheet.
  • Criteria – All occurrences...
Previous Section
End of Section 7
Next Section