- Shopping Bag ( 0 items )
Ships from: Westminster, MD
Usually ships in 1-2 business days
Ships from: LUTON, United Kingdom
Usually ships in 1-2 business days
A comprehensive guide to building financial models
Building Financial Models with Microsoft Excel + CD-ROM provides beginning or intermediate level computer users with step-by-step instructions on building financial models using Microsoft Excel-the most popular spreadsheet program available. The accompanying CD-ROM contains Excel worksheets that track the course of the book and allow readers to build their own financial models. This comprehensive resource also covers important topics such as the concept of valuation, the concept of sensitivity analysis, the concepts of contribution margin and financial ratios and the basics of building and using a Capitalization Table.
K. Scott Proctor, CFA, is the Director of Investor Analytics at SNL Financial, a financial information provider.
PART ONE: THE MASTER BUDGET.
Chapter 1. Overview of Budgets and Financial Models.
Chapter 2. Operating Budget—Assumptions, Sales, and Collections.
Chapter 3. Operating Budget—Cost of Goods Sold, Inventory, and Purchases.
Chapter 4. Operating Budget—Operating Expenses.
Chapter 5. Operating Budget—Income Statement.
Chapter 6. Financial Budget—Capital Budget and Cash Budget.
Chapter 7. Financial Budget—Balance Sheet.
PART TWO: FINANCIAL STATEMENTS AND FREE CASH FLOWS.
Chapter 8. Consolidated Financial Statements.
Chapter 9. Free Cash Flows and Dashboard.
PART THREE: ANALYSIS OF A FINANCIAL MODEL.
Chapter 10. Sensitivity Analysis.
Chapter 11. Contribution Margin Analysis.
Chapter 12. Financial Ratios Analysis.
Chapter 13. Valuation.
Chapter 14. Capitalization Chart.
Answers to Chapter Questions.
Appendix: General Overview of Microsoft Excel Features and Functionality.
About the CD-ROM.
The Cambridge Dictionary defines a budget as "a plan to show how much money a person or organization will earn and how much they will need or be able to spend." Businesses use several different types of budgets to manage their operations. Whatever form various budgets may take, the primary goal of all budgets is to provide a tangible and quantifiable estimate of the receipt and allocation of resources. In the context of this book, a budget represents a core element of a financial model; financial models are discussed later in the chapter.
Businesses use several types of budgets for planning purposes. These budgets are typically categorized by the timeframe that they cover. A "long-range plan," one type of budget, typically forecasts financial statements out 5 to 10 years into the future. Long-range plans usually evolve from "strategic plans," which define the overall mission and goals for a business. These long-range plans are coordinated with Capital Budgets, which map out large monetary commitments for things such as facilities and large pieces of equipment.
From a budgeting perspective, this book is focused on the "Master Budget," which forecasts a business's complete operations over the medium-term (1-5 years). The MasterBudget consists of many interrelated financial and operating schedules, including sales, purchases, and operating expenses, among many others. While some of the key outputs of a Master Budget are the consolidated financial statements (Balance Sheet, Income Statement, and Statement of Cash Flows), a vast array of supporting schedules are also part of the Master Budget. Figure 1.1 outlines the various components of the Master Budget.
As Figure 1.1 indicates, there are two key components of the Master Budget: the Operating Budget and the Financial Budget.
The operating budget focuses on the Budgeted Income Statement and its supporting components and schedules-these items are described below.
Sales and Collections Budget The Sales and Collections Budget represents one of the first steps in the budgeting process, as items such as inventory levels and operating expenses are driven off of the Sales and Collections Budget. Effective sales budgeting is a key factor in building a useful and representative financial model for a business. Regardless of the nature of your business (for example, whether it is product- or service-based), this book takes a unit-based approach in which forecast sales are based on (1) projected unit sales and (2) projected unit prices. This topic is covered in detail later in the book.
Cost-of-Goods-Sold Budget The Cost-of-Goods-Sold Budget decomposes, or breaks down, the components of a business's cost of goods sold (in some cases referred to as the cost of revenues). This budget breaks out each separate factor underlying the cost of goods sold for a business.
Inventory and Purchases Budget The Inventory and Purchases Budget, which represents what a business plans to buy and how much inventory it intends to hold over a given timeframe, is based on three factors: a business's desired ending inventory, cost of goods sold, and beginning inventory. A business's desired ending inventory will drive that business's budgeted purchases over a given period of time. A larger desired ending inventory will typically lead to a larger Purchases Budget and vice-versa. While the Purchases Budget, a component of the Inventory and Purchases Budget, represents an estimate of future purchases, this is an accrual-based accounting figure, and it is the Disbursements for Purchases Budget (another component of the Inventory and Purchases Budget) that drives a company's cash flows. This concept is discussed in detail later in the book.
Operating Expenses Budget The Operating Expenses Budget forecasts all of the elements of a business's operating expenses, such as salaries, rent, depreciation, and others. Some of these expenses are fixed and some are variable (in other words, based on another metric, such as revenues); this concept of fixed versus variable costs is discussed in detail later in the book. While the Operating Expenses Budget represents an estimate of future expenses, this is an accrual-based accounting figure, and it is the Disbursements for Operating Expenses Budget, a component of the Operating Expenses Budget, that drives a company's cash flows. This concept is also discussed in detail later in the book.
Budgeted Statement of Income The Budgeted Statement of Income (also referred to as the Budgeted Income Statement) integrates components of each of the other Operating Budget schedules. The Income Statement compares a business's revenues and costs for a given period of time and often serves as a benchmark for the performance of a business.
The Financial Budget is focused on capital expenditures (large purchases of assets such as equipment and facilities) and on a business's budgeted cash position and Balance Sheet.
Capital Budget A business's Capital Budget forecasts large expenditures for items such as machinery. Different companies set different thresholds for what qualifies as a capital expenditure (versus an expense). If the purchase of an item (such as a piece of machinery) is classified as a capital expenditure, it is then depreciated (or amortized in some cases) over a predetermined period of time. The Capital Budget covers Capital Expenditures, Disbursements for Capital Expenditures, and Depreciation Budgets.
Cash Budget The Cash Budget tracks a business's anticipated cash receipts and disbursements. This is a very detailed and important schedule that draws on information in the Operating Budget.
Budgeted Balance Sheet The budgeted Balance Sheet represents the final step in building the Master Budget as outlined in Figure 1.1. The budgeted Balance Sheet integrates components from both the Operating and the Financial Budgets.
A financial model is a quantitative representation of a company's past, present, and future business operations. This quantitative representation is expressed through the use of accounting-the language of business. Finance, which may be broadly defined as the science of managing money and other assets, is based on accounting. As such, it is important to recognize the central role accounting, or the enumeration of business transactions, plays in building financial models. While this book does not cover or address accounting concepts in any level of detail, it is worth noting that the consolidated financial statements (Balance Sheet, Income Statement, and Statement of Cash Flows) represent the product of a series of accounting transactions.
A financial model is a required component of any business plan. Anyone interested in starting a new business, starting a new line of business within an existing company, assessing the operations of an existing or proposed business, and/or comparing the operations of two or more businesses, among other tasks, should know how to build, use, and modify a financial model.
While there are a variety of approaches to building financial models, this book will focus on the inclusion of the following sections in a financial model: (1) a Master Budget (which is made up of an Operating Budget and a Financial Budget), (2) the consolidated financial statements (Balance Sheet, Income Statement, and Statement of Free Cash Flows), (3) a free cash flow analysis, (4) a sensitivity analysis of the model's outputs versus inputs, (5) a contribution margin analysis, (6) a financial ratios analysis, (7) a valuation analysis, and (8) a capitalization chart.
For the sake of illustration, sample templates for each of these sections are shown below. Please note that no numbers/values have been inserted into these templates-over the course of this book, I will walk through the process of filling in all of these templates one step at a time.
A financial model integrates all of the components of a Master Budget into a working model of a company's planned financial activities for a given time period. As this represents a significant amount of information, the components of a financial model are presented in several figures.
As discussed earlier, the components of the Master Budget are broken into the two primary budgets-the Operating Budget and the Financial Budget. Please note that the areas shaded in gray in the screenshots represent the areas in which I will fill in values to build a financial model over the course of this book. These figures are presented as a road map for the next several chapters of the book.
Master Budget-Operating Budget
The following figures represent components of the Operating Budget. Note the following convention used throughout the book for time periods: "1Q X4" is to be interpreted as "the first quarter of a year ending in the number 4." The use of "X4" for a year is a common practice in accounting and finance-it is meant to refer to a specific year without referring to an exact time period such as "94" or "04." I also use the following convention throughout the book: "X4" is to be interpreted as "the year X4." Again, this is meant to refer to a particular year without referring to an exact time period.
Sales and Collections Budget The Sales and Collections Budget, shown in Figure 1.2, consists of a Sales Budget and a Collections Budget.
Cost-of-Goods-Sold Budget The Cost-of-Goods-Sold Budget, shown in Figure 1.3, breaks out each component of a business's cost of goods sold.
Inventory and Purchases Budget The Inventory and Purchases Budget, shown in Figure 1.4, consists of an Inventory Budget and a Purchases Budget.
Operating Expenses Budget The Operating Expenses Budget, shown in Figure 1.5, consists of an Operating Expenses Budget and a Disbursements for Operating Expenses Budget.
Budgeted Statement of Income The Budgeted Statement of Income, shown in Figure 1.6, compares a business's revenues and expenses.
Master Budget-Financial Budget
The following figures represent components of the Financial Budget.
Capital Budget The Capital Budget, shown in Figure 1.7, consists of three components: the Capital Expenditures Budget, the Disbursements for Capital Expenditures Budget, and the Depreciation Budget.
Cash Budget The Cash Budget, shown in Figure 1.8, offers a detailed reconciliation of a business's beginning and ending cash balances for a given period of time.
Budgeted Balance Sheet The Budgeted Balance Sheet, shown in Figure 1.9, compares a business's Assets, Liabilities, and Owners' Equity.
Additional Components of a Master Budget
A working financial model should include several additional schedules beyond those presented in Figures 1.2 through 1.9. These schedules include an Assumptions and Dashboard worksheet and Headcount worksheets, among others.
Consolidated Financial Statements
The consolidated financial statements consist of the Balance Sheet, the Income Statement, and the Statement of Cash Flows. Publicly traded companies are required to report these statements to the SEC (U.S. Securities and Exchange Commission) on a regular basis, so many readers may be familiar with each of these statements. Templates for each of these financial statements are provided below.
Balance Sheet A Balance Sheet, shown in Figure 1.10, offers a view of a business's financial position in terms of its Assets, Liabilities, and Owners' Equity.
Income Statement An Income Statement, shown in Figure 1.11, presents a summary of a business's results of operations in terms of its revenues and expenses.
Statement of Cash Flows A Statement of Cash Flows, shown in Figure 1.12, reconciles a business's net income to its change in cash position over a given time period in terms of Cash Flows from Operating Activities, Cash Flows from Investing Activities, and Cash Flows from Financing Activities.
Free Cash Flow Analysis
The concept of free cash flows is central to modern finance. Broadly speaking, free cash flows represent the amount of cash a business generates (or, in some cases, consumes) over a given timeframe after paying all of its "required" costs for that period. I will discuss free cash flows in Chapter 9, but technically speaking, free cash flows represent the cash available to all providers of capital (providers of both debt and equity). Figure 1.13 presents a view of the free cash flows worksheet. All of the terms in this worksheet will be explained and discussed in Chapter 9.
Sensitivity analyses are used to model the effect of changing input variables on some output of interest, such as net income or free cash flows. It is often helpful to build a series of sensitivity analyses to get a sense for what input variables will have a significant influence on your output metric of interest (for example, net income). Figure 1.14 shows a data table template that could be used to test the effect of varying the assumed growth rate in revenues on net income. Chapter 10 is devoted entirely to the coverage of sensitivity analyses.
Contribution Margin Analysis
Contribution margin is defined as the extent to which each unit sale contributes to a business's fixed cost base. This is calculated as unit price - variable costs per unit. Key operating metrics, such as operating leverage (calculated as fixed costs/total costs), breakeven value in units (how many units must be sold before the business reaches "breakeven," or the point at which revenues cover all costs), and breakeven value in dollars (the level of sales, as measured in dollars, at which the business reaches breakeven), are covered in detail in Chapter 11. Figure 1.15 highlights several of these metrics.
Financial Ratios Analysis
Financial ratios, such as gross margin (calculated as gross profit/sales), net profit margin (calculated as net income/sales), and return on equity (calculated as net income/owners' equity), among others, are often used to analyze financial models. Figure 1.16 highlights several of the financial ratios used in Chapter 12.
Business valuation is the process of determining how much a company is worth-in other words, determining its value. The valuation of a business is a complex subject-many books have been written on this topic alone. This book will cover the concept of "triangulation," in which several well-known valuation techniques are used-and are weighed appropriately-to estimate the value of a business. Figure 1.17 highlights a model in which various valuation techniques are used to triangulate on the value of a business.
Excerpted from Building Financial Models with Microsoft Excel by K. Scott Proctor Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Posted January 20, 2007
Posted May 10, 2009
No text was provided for this review.
Posted November 5, 2009
No text was provided for this review.