Before you make a decision, you want to be sure to do your homework. Investing in a company is certainly no exception. Individuals evaluating whether to invest in an existing business routinely seek to understand the business’ ability to generate free cash flow (i.e., the ability to generate cash from operations for potential distribution to investors) and to identify opportunities for improving the business’ return on equity (ROE). Of course, this is often easier said than done. Calculating free cash flow can be a time-consuming task. Similarly, demonstrating and documenting how to improve ROE can be challenging, especially when proposed ROE improvements involve scenarios more complicated than just increasing profitability through expense reduction. For example, individuals with limited understanding of ROE may not readily appreciate how, under certain circumstances, it is possible for profitability to deteriorate and yet overall ROE to improve, such as when working capital reductions and increased distributions to common stockholders combine to offset the lower profits.
In order to facilitate financial analysis and due diligence on an existing business (including calculating free cash flow and documenting ROE improvement scenarios), this paper presents a tool that enables individuals even with limited financial background to perform a detailed financial analysis and to present the results in a clear and straightforward manner.
Swiss Army Knife[1] for Financial Analysis
The tool, which can be downloaded by clicking here, is an Excel spreadsheet that includes six interconnected worksheets in individual tabs. Products of this tool include:
Worksheets
A: Three-year horizontal and vertical income statement analysis
B: Three-year horizontal and vertical balance sheet analysis
C: Automatic calculation of three years of financial ratios [2]
D: Clear presentation of a free cash flow analysis
E: Three-part DuPont extended return on equity (ROE) calculation for three years[3]
F: Worksheet for generating “what-if” analyses to highlight and measure opportunities for improving ROE and return on assets (ROA)[4]
Worksheets A, B, C, and E are standard tools for gauging which aspects of an entity’s financial performance are improving or deteriorating across recent accounting periods. Worksheet D provides insights into an entity’s ability to generate funds from operations and potentially distribute cash to investors, even if that entity is not generating profits. Worksheet F facilitates what-if analysis for improving ROE and, while the worksheets generally reflect a corporate structure, sheet F’s what-if analysis can be performed on any type of business entity (sole proprietorship, partnership, etc.) by inserting into worksheet B total common equity figures rather than inputting values for individual common equity line items.
This discussion assumes readers are familiar with the basic functions of A, B, C, and E and accordingly concentrates on benefits provided by D and F.
Methodology: How it Works
For investors, the ability to generate cash from operations may serve as the ultimate barometer of an investment’s financial success or failure. Nevertheless, preparing a free cash flow analysis can be daunting because:
- Typically published financial statements do not conveniently isolate the two key components of a free cash flow analysis, namely net operating profit after tax (NOPAT) and changes in net operating capital (NOC).
- Published financial statements often buried within footnotes required capital expenditure, disposal, and depreciation information.
- The impact of working capital on cash flow may be confusing to users with limited financial expertise. For example, such users may not readily recognize the adverse cash impact of growing current assets, such as receivables, and the favorable impact of increasing non-interest bearing liabilities such as accounts payable.
Worksheet D’s free cash flow presentation addresses all these concerns by:
- Providing a clear trail for tracing NOPAT and NOC calculations back to underlying income statement and balance sheet components.
- Utilizing information on changes in net fixed assets, eliminating the need to research footnote information.
- Highlighting how specific working capital components impact fee cash flow.
Assuming worksheets A, B, C, and E expose areas of financial deterioration (or phrased more positively, opportunities for improvement), worksheet F can clearly quantify whether proposed remedial actions can improve ROE even if those actions adversely impact the organization’s profit dollars and profit margins. For example, suppose an entity’s days sales outstanding (DSO) has increased, impairing the organization’s total asset turnover (TAT) and causing investors to keep more equity in the organization than may otherwise be required. As a result, the organization may consider hiring additional collections-related personnel. Although this approach may increase salary expense and reduce profitability, the key issue is whether or not improved collections can sufficiently reduce receivables to allow for increased distributions to owners with the overall result being reduced assets, lower common equity, and improved ROE.
In addition, worksheet F offers a working area for easily running and documenting “what-if” scenarios for improving ROE and ROA. Clear documentation is especially important for those scenarios that may seem counter-intuitive at first, for example, the scenario described above where ROE improves despite increased expenses and lowered profitability.
Exhibit A: Starbucks
Starbucks Corporation’s financial information is employed here to illustrate how the tool works.[5] [6] Starbuck’s income statement and balance sheet data have been posted into worksheets A and B. The tool then automatically completes sheets C, D, E, and F (except for the “what-if” user input area of F).
The worksheets follow three conventions. First, Year 0 refers to the most recent year for which financial information is available, Year -1 refers to the prior year and Year -2 to the year before that. Second, in order for the sheets to calculate information correctly, Year 0 data must be posted in column M of worksheets A and B. Year-1 data must be posted to column H of these worksheets and Year-2 data to column F. And third, interest figures used on worksheets C and D represent the net of interest expense and interest income from worksheet A.
Note: The tool contains two versions of worksheet F. In the first version of worksheet F, the “what-if” (columns G, H, and I) area is left blank in order to allow users to practice running their own scenarios. In the second version (which includes the word “completed” in the sheet’s label) the “what-if” area has been completed to reflect the proposals outlined below.
We will start by examining how worksheet D identifies key components of Starbucks’ ability to generate free cash flow. The income tax rate employed here is assumed to be 35.3 percent, which is based on the actual average for the past three years as reflected on worksheet A. Individual circumstances may require other approaches for estimating the tax rate used on worksheet D.
In 2008, Starbucks produced $335.1 million in NOPAT (see cell H14) and increased its investment in net operating capital (cell H32) by $190.9 million from $2,985.0 million in 2007 to $3,175.9 in 2008. As result, Starbucks’ 2008 free cash flow (namely, the organization’s 2008 NOPAT minus the NOC change from 2007 to 2008) was $144.2 million (cell H34).
In 2009, Starbucks’ NOPAT was $373.7 million (cell L14), revealing growth of $38.6 million over 2008. However, in 2009, Starbucks’ free cash flow was $624.5 million (cell L34), accounting for growth of $480.3 million over 2008’s $144.2 million. How did Starbucks achieve such impressive 2009 free cash flow? Although 2009’s $373.7 million NOPAT was a major factor, the other major component was the $250.8 million decline in NOC to $2,925.1 million from $3,175.9 million in 2008. Closer inspection of NOC components reveals how, between 2008 and 2009, net operating working capital increased by $169.2 million (cell N24), but net PP&E declined by $420 million (cell N26).
Although all of worksheet D’s free cash flow information derives from the income statement (worksheet A) and the balance sheet (worksheet B), worksheets A and B alone do not identify the amount of free cash flow generated by an organization nor do they highlight the key factors impacting that free cash flow, for example, the key role played by Starbucks’ 2009 NOC reduction.
To summarize, benefits of worksheet D include automatic calculation of free cash flow and identification of specific components impacting that free cash flow, while avoiding the need to research footnote information for depreciation, disposal, and capital expenditure information.
Interpreting the Results
Next let’s see how the DuPont pro forma worksheet F can help quantify and document opportunities for improving ROE. Notice the DuPont extended worksheet (worksheet E) shows that, although PM improved from 3.04 percent in 2008 to 4.00 percent in 2009, 2009 PM still lagged considerably below 2007’s 7.15 percent. In addition, 2009’s 1.75 TAT was considerably below 2008’s 3.26 TAT. Can this information, along with information from worksheets A, B, and C, help to identify some opportunities for improving ROE?
For example, starting with PM, observe that in 2008 general and administrative expense (G&A) represented 4.4 percent of revenue (worksheet A, cell H37), whereas in 2009, G&A represented 4.6 percent revenue (cell M37). If Starbucks could have maintained a G&A target of 4.4 percent of revenue, then the company’s 2009 G&A would have been $9,774.6 million revenue * 4.4 percent, or $430.1 million, which is $22.9 million less than the actual 2009 figure. This cost reduction is posted in the P&L section of the Completed F Worksheet (cell G11). Given pretax income increases by $22.9 million as a result of this cost reduction, the income taxes should increase by $8.1 million (assuming a 35.3 percent rate, which is the average tax rate for past three years based on information from worksheet A). Posting this $8.1 million income tax increase into cell G16 results in 2008 net income increasing by $14.8 million. The worksheet automatically adjusts the equity section of the balance sheet for these increased net earnings, but in order to keep it in balance, we assume here that cash would have also increased due to the $14.8 million net of tax cost savings.
Are reasonable opportunities also available for balance sheet improvement? The income statement shows that between 2008 and 2009, total revenue declined by 5.9 percent (worksheet A, cell P14), but inventory declined by only 4.0 percent (worksheet B, cell P14). Notice also on sheet C that the inventory turnover ratio declined from 14.99 turns in 2008 to 14.70 turns in 2009. If Starbucks was able to maintain the 2008 inventory turnover ratio through 2009, then 2009 inventory would have equaled $9,774.6 total revenue divided by 14.99 turns, or $652.1 million, which is $12.8 million lower than the actual $664.9 million ending inventory. To be conservative, let’s assume that the organization would have needed to incur $4 million in higher expenses to sustain this higher inventory turnover ratio. If so, income taxes would decrease by $1.4 million (based on 35.3 percent average tax rate * $4 million) and net income would be $2.6 million lower (based on $4.0 million higher expenses offset partially by $1.4 million lower income taxes).
To reflect this information on the completed version of worksheet F, column H shows:
-$12.8 million for lower inventory (cell H26)
+$4.0 million increased expenses (cell H11)
-$1.4 million for lower income taxes (cell H16)
Assuming cash serves as the offset for all these items, then cash would change by -$2.6 million for lower net income and by +$12.8 million for lower inventory, a net of +$10.2 million.
The company would now have a total of $25 million additional cash (which is $14.8 million from G&A savings plus $10.2 million net from the inventory reduction effort). So assuming the company had actually implemented these proposed changes, the organization could presumably have returned this incremental cash to shareholders. To reflect this distribution, -$25 million is posted into cell I24.[7]
Cells B47 through G62 summarize the ROE impact of these pro forma changes. ROE increases by .46 percent to 13.29 percent from 12.83 percent. The improvement is achieved via a .12 percent increase in PM and a slight improvement in TAT from approximately 1.75 to 1.76.
Conclusion
In summary, this example based on Starbucks’ 2007 through 2009 financial statement information demonstrates how users can easily employ the attached tool to:
- Generate automatically a free cash flow analysis from underlying income statement and balance sheet information (worksheet D).
- Quantify how specific “what-if” scenarios can impact an entity’s ROE (worksheet F) and document clearly how these scenarios impact the income statement, balance sheet, and ROE components.
- Present results that can be readily understood by audiences with a range of financial expertise.
Anyone considering an investment in an existing business can generate a similar analysis by posting the business’ past three years of income statements and balance sheet information and allowing the tool to populate related sections throughout worksheets A through F. With this background analysis complete, an individual can then use worksheet F to propose, measure, and document detailed scenarios for improving the business’ ROE. Thus, this valuable tool can serve as both a translator of complex financial information and an indicator of opportunities for enhancing financial performance.
[1] Swiss Army Knife is a registered trademark of Victorinox AG and its affiliates, which have no relationship to the subject matter of this article.
[2] Definitions of financial ratios and free cash flow can vary slightly depending on the source. For example, some sources use average balances in the denominator when calculating turnover ratios, whereas other sources use ending balances. For two reasons the formulas employed here are based on information from Eugene F, Brigham and Michael C. Ehrhardt, Financial Management: Theory and Practice, 13 ed., Mason, Ohio: South-Western Cengage Learning, 2009, chapters 2 and 3. First, Pepperdine University Executive Programs finance classes regularly use this text and second, all data needed to complete these formulas can be found on the income statement and balance sheet, eliminating the need to research footnotes.
[3] The three-factor DuPont extended formula dissects the basic ROE formula into the underlying components of profit margin (PM), total asset turnover (TAT), and equity multiplier (EM):
ROE = Net income – preferred dividends/Common equity
ROE = PM * TAT * EM
Where
PM = (Net income – preferred dividends) / total sales
TAT = Total sales / total assets
EM = Total assets / common equity
[4] Although ROA is not a specific component of a typical three-part DuPont extended analysis, ROA can provide useful information when ROE is meaningless due to an entity’s negative common equity.
[5] Starbucks Corporation. (2008). 10-K Annual Report 2008. Retrieved Nov. 30, 2011 from SEC EDGAR website http://www.sec.gov/edgar.shtml.
[6] Starbucks Corporation. (2009). 10-K Annual Report 2009. Retrieved Nov. 30, 2011 from SEC EDGAR website http://www.sec.gov/edgar.shtml.
[7] Column I is reserved for distributions to shareholders, with distributions reflected as a reduction to cash in cell M24. The spreadsheet automatically generates an offsetting reduction to common equity in cell M40.
