Book summary “Financial Modeling and Valuation”: summary

Introduction

Master the art of building professional financial models and valuing companies from the ground up using real-world data.

You often struggle to translate raw financial data into reliable forecasts and accurate valuations. Investment banking and equity research require rigorous models that link income statements, balance sheets, and cash flow statements without errors. Without a clear methodology, you risk making critical projection mistakes that distort investment decisions.

Paul Pignataro brings extensive experience from Morgan Stanley and TH Lee Putnam Ventures to solve this problem. He provides a step-by-step blueprint for building a fully integrated financial model from scratch using Walmart as a comprehensive case study.

The book splits into two logical parts to guide your learning. The first part teaches you how to build the three main financial statements, debt schedules, and supporting schedules. The second part covers advanced valuation techniques, including discounted cash flow analysis and market multiples.

• How do you project revenues and operating expenses using historical averages?
• What causes circular references in debt schedules, and how do you resolve them?
• How do you calculate enterprise value and equity value for valuation?

1. Financial Statements and Projections

You must classify every income statement item into nine major categories, apply strict color-coding etiquette, and use historical averages or consensus data to project revenues and costs.

Income Statement Categories

Every income statement line item must find a home in one of nine buckets to enable proper financial analysis and comparisons between companies. An analyst categorizes any convoluted income statement into these standard buckets to compare different firms.

• Revenue (sales)
• Cost of goods sold
• Operating expenses
• Other income
• Depreciation and amortization
• Interest
• Taxes
• Non-recurring and extraordinary items
• Distributions

Analysts must make a consistent and defensible choice on whether to include non-core other income inside EBITDA calculations based on the analytical goal. A car manufacturer generates core revenue from selling cars, but earns additional other income from offering customer financing interest. Determine if the other income is consistent and recurring, consider the specific purpose of the analysis, and establish one defensible view.

Depreciation expense is often buried inside broader operational line items on the income statement. Walmart consolidated depreciation and amortization was found to be $7,157 in 2010, $7,641 in 2011, and $8,130 in 2012. Perform a word search for depreciation in the company annual report, check the cash flow statement, and reduce the host expense category.

Financial Modeling Best Practices

Financial models must follow strict color-coding etiquette to visually separate hardcoded user assumptions from dynamic formula calculations. Color-coding allows reviewers to immediately identify core drivers versus automated calculations.

• Enter all hardcoded numbers and assumption drivers in blue font.
• Enter all formulas in black font.

Using keyboard hotkeys rather than the mouse increases efficiency when building financial models in Excel. Highlight a range of cells and press Ctrl + R to copy formulas to the right, or press Alt + = to automatically sum a column of numbers.

Revenue and Cost Projections

Projecting future revenue requires combining pricing and volume drivers with external guidance from multiple consensus sources. Walmart posted $446,950 million in total revenue for 2012, representing a 6.0 percent increase from 2011. Review investor presentations, listen to management commentary during earnings calls, examine equity research reports, and consult consensus estimates.

Projecting future expenses requires determining whether costs are fixed or variable and applying historical trend methods conservatively. Walmart historical cost of goods sold remained consistent at around 75 percent of total revenue over a three-year period, indicating variable behavior.

• Take an average percentage of the last three years.
• Take a maximum percentage of the last three years as a conservative approach.
• Take a minimum percentage of the last three years as an aggressive approach.
• Take the last year percentage.
• Set percentages to steadily increase or decrease year over year.

The treasury stock method calculates diluted shares by adding in-the-money options to basic shares and reducing the count by the shares hypothetically repurchased with exercise proceeds. For Walmart, 13,596,000 options at a $50.49 strike price generated $686,462,040 in value, which when divided by the $73.82 stock price resulted in 9,299,133 shares bought back, leaving 4,296,867 net new shares.

• Obtain the basic share count from the most recent quarterly SEC filing.
• Locate all outstanding option and warrant tables in annual or quarterly filings.
• Multiply exercisable in-the-money options by their exercise price to find total proceeds.
• Divide total proceeds by the current stock price to determine the number of shares bought back.
• Subtract bought back shares from exercised options to find net new shares and add them to basic shares.

2. Cash Flow Statement and Projections

The income statement measures profitability on an accrual basis, but profit does not equal actual cash. Credit sales or unpaid bills affect revenue and expenses without immediate cash impact.

The cash flow statement bridges accrual accounting and actual cash movements by separating activities into operating, investing, and financing segments.

A company with $10 million in sales where all customers pay in cash generates $10 million in cash. If those sales are on credit, revenue is recorded without receiving cash. The sum of cash from operating, investing, and financing activities determines the total net change in cash for the period.

Operating Activities Adjustments

Cash from operating activities is derived from net income by removing all non-cash items and adjusting for revenues not received and expenses not paid in cash. Unpaid expenses like accrued expenses and accounts payable are added back, while uncollected revenues like accounts receivable are subtracted.

If a company has $8 million in net income after recording a $2 million unpaid SG&A invoice, that $2 million expense is added back to show $10 million in cash from operations. Depreciation is added back because it is an expense that is never actually paid.

Investing and Financing Activities

Investing activities capture cash spent on or generated from assets and businesses, whereas financing activities track cash flows related to debt and equity. Walmart noted that its capital expenditures for property and equipment in fiscal 2013, excluding business acquisitions, would range between $13.0 billion and $14.0 billion.

When projecting obscure or volatile line items on a financial model, you can choose from seven specific forecasting methods:

• Conservative: take the minimum of the past three years
• Aggressive: take the maximum of the past three years
• Average: take the average of the past three years
• Last year: use the most recent performance figure
• Repeat the cycle: project values to match historical cyclical swings from earlier years
• Year-over-year growth: apply a standard growth rate based on trends
• Project out as a percentage of an income statement or balance sheet line item based on historical consistency

3. Depreciation Schedule and Deferred Taxes

Depreciation schedules track asset aging and wear while generating deferred taxes and non-cash adjustments that link directly to the financial statements.

As you utilize assets to generate revenue, their value decreases. Accounting rules allow this reduction to be expensed as depreciation. Because depreciation is a non-cash expense, it reduces net income after taxes but must be added back to cash on the cash flow statement. A depreciation expense of $5,000 reduces net income after taxes, and since it is a non-cash expense, it is added back to cash.

Depreciation Methods and MACRS

The straight-line method evenly ages an asset over its expected useful life, optionally factoring in a residual value. Accelerated depreciation methods produce higher depreciation expenses in the earlier years of an asset's life to lower taxable net income and taxes. MACRS is the U.S. tax depreciation method that applies a predefined set of percentages based on asset useful life and placed-in-service conventions.

You can calculate straight-line depreciation using these steps:

• Subtract the residual value from the original asset value.
• Divide the result by the expected useful life of the asset.

You can compute accelerated depreciation percentages using these steps:

• Calculate declining balance percentage by dividing 1 by the asset life and multiplying by the accelerating multiplier.
• Calculate sum of the years' digits base percentage by dividing the remaining life digit by the sum of digits from 1 to asset life.

You apply tax depreciation by following these steps:

• Identify the asset's useful life and matching MACRS schedule table based on the quarter placed in service.
• Apply the predefined annual percentages directly to the original base value of the asset.

A car purchased for $50,000 with a 10-year useful life depreciates by $5,000 per year. If it has a $1,000 residual value, the depreciable base is $49,000, resulting in $4,900 depreciation per year. Using the mid-quarter convention placed in service in the first quarter for a 10-year useful life asset results in Year 1 depreciation of 17.50 percent, Year 2 of 16.50 percent, and Year 3 of 13.20 percent.

Deferred Taxes and PP&E Projections

A net operating loss occurs when expenses exceed sales, and it can create a deferred tax asset by allowing tax offsets in prior or future years. Temporary accounting differences between GAAP and tax income statements create a deferred tax liability. Projecting a company's depreciation requires building a tiered schedule combining current net PP&E with projected capital expenditures.

You manage tax offsets and projections through these steps:

• Apply the net operating loss against taxable income of prior years for a tax refund via carryback.
• Carry forward any remaining loss balance for up to 20 years to offset future taxes as a deferred tax asset.
• Calculate projected straight-line depreciation and projected accelerated depreciation.
• Subtract projected straight-line depreciation from accelerated depreciation.
• Multiply the resulting difference by the applicable tax rate to project deferred tax liability.
• Link beginning net PP&E from the balance sheet into the top of the depreciation schedule.
• Link projected CAPEX values from the cash flow statement as positive numbers into the schedule rows.
• Apply an initial useful life midpoint and compute straight-line depreciation using anchored column references.
• Compare projected total depreciation growth against historical trends and adjust PP&E or CAPEX useful life assumptions to fit.

A company suffers a $1,000 net loss in 2012 and carries it back to 2010 to get a $300 refund at a 40 percent tax rate, leaving a $250 balance to apply to 2011. A company with $100,000 EBITDA uses straight-line depreciation of $5,000 for GAAP and MACRS depreciation of $8,750 for tax purposes, creating a $1,500 deferred tax liability. Walmart's 2012 net PP&E of $109,603 was combined with property under capital lease of $2,721, and tested against historical depreciation trends growing at 6 to 7 percent per year.

4. Working Capital Fundamentals and Projections

Working capital measures your near-term liquidity by assessing whether current assets can cover current liabilities due within 12 months. You calculate working capital as current assets less current liabilities. If positive, there are sufficient funds for upcoming liabilities. If negative, it indicates a working capital deficit.

Operating working capital isolates day-to-day current assets and current liabilities to project near-term liquidity and cash flow impacts.

For modeling purposes, operating working capital narrows the definition by excluding cash, cash equivalents, and debts. Removing cash, cash equivalents, and debts leaves operating items like accounts receivable, inventory, and prepaid expenses for current assets. It also leaves accounts payable, accrued expenses, and accrued income taxes for current liabilities.

Historical Days Metrics

To measure the performance of operating working capital line items, you calculate historical days by dividing the item by its related income statement item and multiplying by 360. You use 360 days instead of 365 because it is divisible by 12, which simplifies monthly modeling. In actual analysis, the numerator should use an average of the current year and previous year's balance sheet ending balances.

• Take an average of the ending balances from the year being analyzed and the previous year for the numerator.
• Divide the averaged balance by its related income statement item.
• Multiply the resulting percentage by 360 to get equivalent days.

Walmart's 2012 accounts receivable balance was $25,000 and revenue was $100,000, resulting in 90 days outstanding. Walmart's 2012 historical calculations yielded 4.4 receivable days, 41.4 inventory days, 10.8 prepaid days, 37.8 payable days, 86.0 accrued liability days, and 29.9 accrued income tax days. Each operating working capital line item is matched to its corresponding income statement driver, such as revenue for receivables, COGS for inventory and accounts payable, and SG&A for prepaid expenses and accrued liabilities.

Cash Flow Linkages

Future operating working capital is projected by taking the previous year's days metric as an assumption driver and reverse-engineering the standard days formula. You divide the projected days assumption by 360 and multiply it by the projected related income statement item to estimate future balance sheet operating working capital items.

• Hardcode the projected days assumption into the schedule cell.
• Divide the projected days assumption by 360.
• Multiply the result by the projected related income statement item to get the projected balance.

Changes in operating working capital line items translate directly into cash flows, where asset increases cause cash outflows and liability increases cause cash inflows. Because operating working capital acts like a net asset, an increase in total operating working capital represents a cash outflow, while a decrease represents a positive cash flow.

• Link current asset changes to the cash flow statement using a negative sign so that increases result in cash decreases and decreases result in cash increases.
• Link current liability changes directly without a negative sign so that increases result in cash increases and decreases result in cash decreases.

5. Balance Sheet Projections and Error Checking

The balance sheet measures a company's financial position at a specific point in time and must always balance assets with the sum of liabilities and shareholders' equity. Assets represent resources held to produce economic benefits and are split into current and non-current. Liabilities are debts owed within or beyond one year. Shareholders' equity includes equity components like common stock, plus retained earnings and other comprehensive income.

Financial models should use the cash flow statement to drive the balance sheet rather than back-solving the cash flow statement from balance sheet changes.

Using the balance sheet to derive the cash flow statement can obscure underlying drivers like capital expenditures and depreciation. These drivers yield the same net change through multiple conflicting combinations of cash flows. Walmart's historical data includes line items such as cash, net receivables, inventories, prepaid expenses, property and equipment, goodwill, and various categories of debt and equity. Walmart's cash and cash equivalents are hardcoded as $7,395 for 2011 and $6,550 for 2012, based on page 33 of its Annual Report.

Asset, Liability, and Equity Projections

Except for cash, asset line items are projected by taking the prior year balance and subtracting the related cash flow statement change. Because cash outflows increase non-cash assets and cash inflows decrease them, a subtraction formula is required. Cash is the sole exception, where positive cash flow is added to the previous balance.

• Take last year's asset balance from the balance sheet.
• Subtract the corresponding cash flow statement change item.

Liabilities and shareholders' equity line items are projected by adding the related cash flow statement change to the prior year balance. Because generating cash increases liabilities and equity while spending cash decreases them, a consistent addition structure is applied across these balance sheet sections.

• Take last year's liability or equity balance from the balance sheet.
• Add the corresponding cash flow statement change item.

The NYSF Balancing Method

An unbalanced balance sheet stems from four specific mapping or totaling errors between the cash flow statement and the balance sheet. The four main failure types are: an unlinked cash flow item, a cash flow item linked multiple times, a cash flow item linking with the wrong sign or wrong year, or totaling errors within the schedules.

The NYSF method uses a paper-based differences column to systematically audit and match balance sheet line item changes against cash flow statement entries.

Creating a year-over-year difference column turns balance sheet changes into implied cash flows, which are then verified against the cash flow statement while crossing off items to prevent duplication or omission.

• Print out the cash flow statement and the balance sheet.
• Create a differences column on the balance sheet by subtracting the previous balancing year from the unbalanced year.
• Compare each difference number to the appropriate cash flows.
• Verify if the balance sheet line item is moving in the right direction.
• Cross off each cash flow statement line item as it is checked to ensure it is used only once.

6. Debt Schedule and Circular References

The debt schedule tracks every major debt type, interest expense, available cash, and interest income, and it must be built last to prevent complicating unbalanced balance sheets. In Walmart's model, five separate sections are created for short-term borrowings, long-term debt due within one year, capital lease obligations due within one year, long-term debt, and long-term capital lease obligations. Walmart lists $4,047 of short-term debt with an interest rate of 0.1 percent for Walmart in 2012, alongside $1,975 of long-term debt due within one year split into $1,475 at 4.8 percent and $500 at 5.2 percent.

The debt schedule tracks borrowings, interest, and cash availability, requiring iterative calculations to resolve circular reference loops in fully linked models.

Mandatory and Automatic Debt Paydowns

Issuances and retirements are separated into mandatory and non-mandatory categories to isolate scheduled payments from automated cash surplus paydowns. Total cash available to pay down debt is derived by taking beginning cash, adding cash flow before debt paydown, and subtracting the minimum cash cushion. Walmart's minimum cash cushion is estimated at $1,000 to ensure liquidity and avoid potential cash shortfalls against $6.5Bn of cash on Walmart's balance sheet. Interest expense and interest income should be calculated using the average of the beginning and ending balances of debt or cash, producing 2,365.5 total interest expense calculated for Walmart in 2013.

Automated non-mandatory debt paydowns use logical conditions and minimum functions to compare cash availability with outstanding debt balances. You can automate these paydowns with exact formulas:

• Set up logical conditions comparing cash available with the debt balance.
• Apply the formula '=-min(G10, G12)*$F$14' where F14 acts as an on/off switch.
• Enter 0 into the switch cell to turn the formula off or 1 to turn it on.

Iterative Calculations and Finalizing the Model

A fully linked financial model creates a circular reference loop between debt, interest income, net income, cash flow, and cash available to pay down debt. Excel requires iterative calculation settings to be explicitly enabled with a defined iteration limit to successfully compute models containing circular references. Typing a string instead of a number inside a circular reference loop generates propagation errors throughout the model that require specific repair steps.

You must follow precise procedures to configure Excel and resolve errors:

• Open Excel options via File in Excel 2010 or the Office icon in Excel 2007.
• Select Formulas and locate the Calculation options section.
• Check the box titled Enable iterative calculations.
• Set the maximum iterations to 100.
• Identify and change the mistaken string cell back to a number or zero.
• Highlight and delete the interest expense and interest income rows on the income statement to break the loop.
• Allow Excel to recalculate normally.
• Restore the deleted links by using Undo or typing Ctrl + Z.

Finalizing the model requires performing high-level sanity checks on revenue growth, earnings per share, and major cost drivers against Wall Street consensus estimates. Walmart's 2013 projected revenue of $473,767 matched Street averages compared to $472.5Bn Street average, while 2014 revenue projection reached $497,455.4 compared to $496.2Bn Street average. Projected 2013 EPS of 4.91 compared to a range of 4.84 to 5.01, and 2013 projected revenue used a 33 percent projected tax rate compared to 32.6 percent historical rate.

You can complete final checks using these actions:

• Verify projected revenue against Street estimates and scale down growth rates if consensus indicates slowing sales.
• Compare projected EPS against low and high analyst ranges.
• Review tax rates, net interest, and depreciation against historical levels.
• Examine COGS and operating expenses if EPS deviates significantly from consensus.

7. Valuation Methods and Discounted Cash Flow Analysis

Valuation relies on book value versus market value, enterprise value metrics, and discounting unlevered free cash flows using the Weighted Average Cost of Capital.

Corporate value is categorized into book value, which is derived from the balance sheet financials, and market value, which is determined by market pricing. Book value represents asset or business worth according to accounting books, such as Walmart's shareholder's equity. Market value is determined by the market, defined as market capitalization which multiplies shares outstanding by the share price.

Walmart trades at $73.82 per share with 3,365.7 million diluted shares outstanding, yielding a market capitalization and market value of $248,459.0 million, while its shareholder's equity book value is $71,315 million.

Enterprise Value and Valuation Multiples

Enterprise value measures the entire business including debt lenders and other obligations, representing the approximate value of core operating assets. Enterprise value is calculated by taking equity value and adding potential debts while subtracting cash.

You can calculate enterprise value using these steps:

• Start with equity value or market capitalization.
• Add short-term debts, long-term debts, and current portion of long-term debts.
• Add capital lease obligations and preferred securities.
• Add non-controlling interests and other non-operating liabilities such as unallocated pension funds.
• Subtract cash and cash equivalents to arrive at enterprise value.

Financial multiples compare business value to operations, and they must match the numerator with whether the metric is post-debt or pre-debt. Valuation relies on three major methods from different perspectives: comparable company analysis, precedent transactions analysis, and discounted cash flow analysis.

Unlevered Free Cash Flow and WACC

Unlevered free cash flow represents cash available to all capital providers based strictly on core business operations, excluding financing activities. To calculate it, you must remove financing activities and add after-tax net interest expense.

You can derive unlevered free cash flow using these steps:

• Start with net income or EBIT.
• Add depreciation and amortization, deferred taxes, and other non-cash items.
• Add working capital changes following the cash flow statement convention.
• Subtract capital expenditures from investing activities.
• Add after-tax net interest expense if starting from net income, or adjust for taxes if starting from EBIT.

The discount rate in a DCF model is the Weighted Average Cost of Capital, combining the cost of equity estimated via CAPM and the after-tax cost of debt. As of October 9, 2012, the 30-year U.S. Treasury risk-free rate was 2.93 percent, the U.S. market risk premium was 6.0 percent, and Walmart's Beta was 0.42, yielding a cost of equity of 5.45 percent.

Terminal Value Methods

Terminal value estimates the business worth after the final projection year, calculated using either an EBITDA multiple method or a perpetuity growth method. The multiple method applies a market EBITDA multiple to the final projected year's EBITDA and discounts it back to present value.

You can determine terminal value using these steps:

• Calculate the terminal value using either an EBITDA multiple applied to the final projected year or the perpetuity formula using final UFCF, WACC, and perpetuity growth rate.
• Discount the resulting terminal value back to present value.
• Add the present value of the terminal value to the sum of present value of projected cash flows to get total enterprise value.

10 Key Ideas

1. Every income statement line item must be classified into one of nine major buckets to enable standardized financial analysis and comparisons across companies.
2. Financial models must follow strict color-coding etiquette by using blue font for hardcoded assumptions and black font for formulas.
3. The cash flow statement bridges accrual accounting and actual cash movements by separating activities into operating, investing, and financing segments.
4. Depreciation schedules track asset aging and wear while generating deferred taxes and non-cash adjustments that link directly to financial statements.
5. Operating working capital isolates day-to-day current assets and current liabilities to project near-term liquidity and cash flow impacts.
6. The NYSF method uses a paper-based differences column to systematically audit and match balance sheet line item changes against cash flow statement entries.
7. The debt schedule tracks borrowings, interest, and cash availability, requiring iterative calculations to resolve circular reference loops in fully linked models.
8. Excel requires iterative calculation settings to be explicitly enabled with a defined iteration limit to successfully compute models containing circular references.
9. Enterprise value measures the entire business including debt lenders and other obligations, and it is calculated by taking equity value, adding debts, and subtracting cash.
10. Valuation relies on book value versus market value, enterprise value metrics, and discounting unlevered free cash flows using the Weighted Average Cost of Capital.