Learning goals. After completing this chapter, you should be able to (1) define a system; (2) recognize system properties included in coordinated financial statements (CFS); (3) connect changes in exogenous variables and parameters determined outside the CFS system to changes in endogenous variables calculated inside the CFS system; (4) conduct scenario analysis by answering ”what-if” and “how-much” kinds of questions; (5) endogenize exogenous variables to improve the credibility of opportunity and threat analysis; (6) simplify financial ratio analysis by exogenizing endogenous variables; and (7) identify trade-off and companion ratios using common size balance sheets and income statements.

Learning objectives. To achieve your learning goals, you should complete the following objectives:

• Learn what is a system
• Learn how CFS satisfy system requirements.
• Describe how (S)olvency, (P)rofitability, (E)fficiency,(L)iquidity, and (L)everage (SPELL) ratios answer the question: “what-is” the financial condition of the firm.
• Learn how to answer the question: what-if the value of an exogenous variable or parameter changes, then how will the values of endogenous variables change.
• Learn how to answer the question: how-much the value of an exogenous variable or parameter needs to change for the value of an endogenous variable to equal a specified value.
• Learn how to evaluate scenarios and evaluate the firm’s strengths and weaknesses using what-if and how-much analysis.
• Learn how to endogenize exogenous variables within a CFS system to increase the credibility of opportunity and threat analysis.
• Learn how to create subsystems of the CFS system that describes a firm’s rate of return on equity (ROE) and solvency by exogenizing certain endogenous variables.
• Learn how to find common size balance sheet ratios equal to balance sheet entries divided by total assets.
• Learn how to find common size income statement ratios equal to income statement entries divided by total revenue.
• Learn how to use common size balance sheets, income statements composed of common size ratios to examine financial trade-offs when conducting what-if and how much analysis.
• Learn how to use pro-forma common size statement to forecast future CFS values.

Introduction

In what follows we define and distinguish between different kinds of systems. Then we make the point that CFS are a system. Indeed, we have already used CFS system properties to answer the question: what-is the financial condition of the firm. We answered that question by using CFS data to find the firm’s SPELL ratios. However, the value of information gained from SPELL ratios has its limits. Answering what-is kinds of questions is a static (timeless) analysis because it focuses on the current financial condition of the firm. We also need information that is forward looking such as knowing how the financial condition of the firm may change in response to changes in its external environment described by exogenous variables and parameters.

The CFS system allows us to examine alternative scenarios by allowing us to answer what-if questions: what if a change in the firm’s exogenous variables occurs, how will its endogenous variables and SPELL ratios change. The CFS system also allows us to examine how-much questions: how-much an exogenous variable must change to produce a specified value for an endogenous variable. The CFS system allows us to change the relationship between exogenous and endogenous variables by endogenizing certain exogenous variables. The CFS system allows us to focus our analysis on key SPELL ratios by exogenizing certain endogenous variables. Finally, the CFS system allows us to create common size balance sheets and income statements that focus on common size ratios. These common size ratios are helpful because they provide comparisons of balance sheet and income statement entries relative to total assets and total revenue. As a result, the ratios can help describe the financial trade-offs facing the firm. To begin our applications of the CFS system, we must first describe the properties of a system.

Understanding Systems

What is a system. A system is an interacting and interdependent group of items forming a unified whole serving a common purpose. Every system has boundaries that separate activities that occur within the system from those that occur outside of the system. There are several kinds of systems. An abstract system uses variables to represent tangible or intangible things and may or may not have a real-world counterpart. On the other hand, physical systems are generally concrete operational systems made up of people, materials, machines, energy, and other physical things. Physical systems are the systems that abstract systems may attempt to represent.

Finally, systems may be closed or open. Open systems allow for exogenous forces outside of the system to influence activities within the system. Closed systems are immune to exogenous forces. Finally, systems may be stochastic or nonstochastic. For stochastic systems, endogenous outcomes within the system and their exogenous causes are described with probabilities. Meanwhile, nonstochastic systems connect endogenous and exogenous variables and parameters with certain (nonprobabilistic) relationships.

System Metaphors

A metaphor compares two ideas or objects that are dissimilar to each other in some ways and like each other in other ways. We introduce three metaphors to describe how changes in an exogenous variable or parameter—a shock—can change endogenous variables.

Balloons. One might compare the CFS to a balloon. If you squeeze one part of the balloon (an exogenous force), there will be an (endogenous) bulge somewhere else in the balloon. This action-reaction nature of a system (and balloons) leads to us examine shocks in pairs and answer the question: “what-if” a change in an exogenous variable occurs, “then” what happens to the endogenous variables of the system?

Predicting the weather. Predicting the financial future of the firm has characteristics in common with predicting the weather. Meteorologists look at where the weather fronts have been, the direction they have been traveling, and then predict where they will likely be in the future. To hedge their bets, they often predict future weather patterns with probabilities. Predicting the future financial condition of the firm also looks at the condition of the firm now, how it has changed over time, and then predicts with probabilities where it will be in the future.

The detective. Trying to describe how changes in an exogenous variable will affect endogenous variables is like a detective trying to put all the clues together to solve a case that explains who committed a crime. The detective observes a crime—an unusual condition different than what existed before the crime occurred. The financial manager observes changes in the firm’s endogenous variables and attempts to link them to changes in one of the exogenous variables and parameters. Most importantly, a detective (and a financial manager) compares the firm’s financial condition with industry standards or with other firms and asks: what is unusual, what is out of place?

Understanding the CFS System

CFS are a system. The CFS are an abstract system whose variables and statements describe the financial condition of a firm using mathematical equations and numbers. The CFS are designed to represent the financial condition of the firm at the beginning and ending of a period with balance sheets and financial activities between the beginning and ending balance sheets using an AIS and a statement of cash flow (SCF). The CFS are an open system. They allow for an external environment represented by exogenous variables and parameters to influence activities within the firm represented by endogenous variables. Finally, for our purposes, we assume that the relationships between CFS and variable values included in the CFS system are deterministic.

CFS and Strengths, Weaknesses, Opportunities, and Threats (SWOT) analysis. Because CFS are a system, we can use them as our primary strengths, weaknesses, opportunities, and threats analysis tool. We summarize several reasons why the CFS system is important for financial managers conducting SWOT analysis:

• because it allows us to answer the question: what-is the financial condition of the firm reflected by its SPELL and common size ratios. Answering the what-is question is the primary means for conducting strengths and weakness analysis;
• because the relationships between CFS variables and financial statements are consistent (they don’t change and cannot produce a contradiction), we can check the accuracy of our data by looking for unusual numbers in the statements. If we observe unrealistic results, they can only be attributed to data inaccuracies;
• because it allows us to conduct opportunities and threats analyses by asking what-if questions. What-if analysis considers a possible change (opportunity or threat) in the external environment of the firm and noting changes in the financial condition of the firm;
• because it allows us to ask how-much questions and determine how-much of an external change is needed to change a particular endogenous variable by a specific amount. Answering how-much questions allows us to find the required response to opportunities and threats to achieve a firm’s goal;
• because it allows us to define subsystems to focus on parts of the system such as profitability and solvency;
• and, because it allows us to examine important financial trade-offs using common size balance sheets and income statements.

Endogenous and exogenous variables and parameters. To understand the CFS system, we must be able to distinguish between endogenous and exogenous variables and parameters. One way to distinguish between CFS endogenous and exogenous variables is to ask: was this variable calculated somewhere in the system? Or, was its value determined outside of the system? If the variable was calculated within the system, it is an endogenous variables. If the variable or parameter was determined outside of the system, it is an exogenous variable.

To illustrate, cash and marketable securities in the beginning period balance sheet is an exogenous variable. Its value was determined by activities in previous time periods. In contrast, ending period cash and marketable securities depend on their beginning period values and changes in the firm’s cash position calculated in the SCF. Therefore, the firm’s ending period cash and marketable securities is an endogenous variable.

Endogenous and exogenous variables and parameters also create interdependencies between CFS. In general, financial activities described by CFS link beginning and ending period balance sheet with an income statement and SCF. We illustrate these connections with two of several possible examples.

1) The difference in cash balances reported in the beginning and ending period balance sheets equals the change in cash position calculated in the SCF.

2) The difference in retained earnings reported in the beginning and ending period balance sheets equals the addition to retained earnings calculated in the firm’s AIS.

HQN exogenous variables and parameters. We illustrate CFS exogenous variables and parameters using HQN’s exogenous variables and parameters in Table 7.1. A special kind of exogenous variables are side-bar sums of exogenous variables. Because they are summed outside of CFS, side-bar calculations are also exogenous variables. One reason we sum exogenous variables in side-bar calculations is to create categories that can be used to compare a firm’s financial performance with similar firms, with the industry average in which it operates, and with its own performance over time. These comparisons could not occur unless financial data were organized into comparable categories. Another special kind of exogenous variables are parameters used to endogenize an exogenous variable such as the average tax rate.

Over-identified variables and systems. Suppose that we use the system properties of the CFS to find ending period cash and marketable securities. Then suppose we observe ending cash and marketable securities reported in our check book or other financial reports. What happens if ending cash and marketable securities determined within the CFS differs from ending cash and marketable securities reported by our bank statement and other financial records are not the same?

The problem is that the ending cash and marketable securities value is over identified. It can be calculated as an endogenous variable within the CFS or observed externally as an exogenous variable. When the two values differ, we say the system is inaccurate because the two values for the over-identified variables don’t agree. In such circumstances, the financial manager must employ his/her best effort to find the error in the data. Resolving data errors revealed by over-identified variables may be the most challenging task facing financial managers whose data is often incomplete and sometimes inaccurate. In some cases, the data errors revealed by over-identified variables provides financial managers opportunities to encourage the principals of the firm to reexamine their financial records and look for errors or missing data.

What-Is Analysis and SPELL Ratios

SPELL ratios and what-is analysis. Chapter 6 described the firm’s financial system using SPELL ratios. Alone, SPELL ratios help describe the financial condition of the firm and reveal its strengths and weaknesses. However, SPELL ratios are more useful when their interdependence are recognized. In other words, a change in the firm’s solvency is likely to change the firm’s liquidity. A change in the firm’s efficiency is likely to change the firm’s profitability. And the list of possible interdependencies continues. Since the variables in the financial system are interdependent, the SPELL ratios composed of system variables are also interdependent.

Profitability and solvency ratios. How do we proceed to examine the interdependencies of the firm? One approach is to focus on the firm’s bottom line—its profitability and solvency ratios. A firm can exist for many reasons. It may satisfy the firm owners’ desires to engage in a production activity. (For example, I just want to farm!) It may be organized to provide family members and others employment. It may exist to provide some public service. There are undoubtedly other reasons why firms exist. However, the firm financial manager is usually charged with only one mission—to ensure the firm’s survival and its profitability. This requires a proper balance between the firm’s return and its solvency.

In our view, solvency ratios described by the times interest earned (TIE) and debt-to-service (DSR) ratios and profitability ratios described by margin (m), return on equity (ROE), and return on asset (ROA) ratios are the most important SPELL ratios. However, efficiency, leverage, and liquidity ratios also matter to the firm because they influence its profitability measured by its ROE and ROA ratios and its solvency measured by its TIE and DSR ratios.

What-if Analysis and SPELL Ratios

We emphasized earlier the interdependencies between endogenous and exogenous variables and parameters in the CFS system. Therefore, any change in an exogenous variable or parameter in one part of the CFS system will produce changes in endogenous variables in other parts of the system. Tracing the impact of a change in an exogenous variable on endogenous variables in the system is referred to here as what-if analysis.

What-if analysis may help the firm anticipate and plan for opportunities and threats. What-if analysis may also permit firm managers to virtually experiment with changes in exogenous variables before implementing an actual financial plan.

The first step in what-if analysis is to introduce the change in an exogenous variable. The second step is to recalculate the endogenous variables in the financial statements. The third step is to recalculate the SPELL ratios and compare them to the previous ratio values and to industry averages. Finally, the fourth step is to interpret the results described by changes in the firm’s SPELL ratios. Fortunately, steps one, two, and three can be automated using Excel CFS spreadsheets described in an Appendix to this chapter.

Describing the results of what-if analysis. To help analyze the results of what-if analysis, we use an Excel spreadsheet that describes exogenous variables and the CFS. We illustrate how to use an Excel spreadsheet to describe what-if analysis using HQN’s CFS. We represented HQN’s CFS for years 2017 and 2018 in Table 6.1. We repeat it here as Table 7.2 for convenience. It represents the financial conditions of HQN before any changes in exogenous variables and parameters are considered.

Now consider what-if cash sales increased by $1,000. The first thing to observe is that increasing cash sales by $1,000 increased cash receipts by the same amount, from $38,990 to $39,990. We report the what-if analysis results in Table 7.3.

After an increase in sales and cash receipts of $1,000, earnings before interest and taxes (EBIT) and earnings before taxes (EBT) all increased by $1,000. Change in cash and marketable securities also increased by $1,000 as did additions to retained earnings. Importantly, ROE increased from 8.5% to 58.5% while ROA increased from 6.5% to 16.5%.

Representing outcomes of what-if analysis using SPELL ratios. While we can report the results of what-if analysis as numbers in CFS, these are difficult to compare with other firms. Instead, we present the results using SPELL ratios. We want to compare SPELL ratios before and after the change described in the what-if analysis has occurred. The table that compares SPELL ratios before and after the change described in the what-if analysis has occurred is called an Activity table. An Activity table is described here and in the appendix to this chapter. An Activity table has four columns. The first column describes the SPELL ratios being compared. The second column describes the industry average SPELL ratios against which the firm can compare its own SPELL ratios. The third column describes the ratio’s value recalculated as a result of the what-if analysis. And finally, the fourth column presents the firm’s base—its SPELL ratios before the what-if changes were considered. We present an Activity table for HQN in Figure 7.4 that describes an in sales of $1,000. Note the changes in SPELL ratios ROE, ROA, TIE, and DSR. We present Activity Table 7.4 below. All the key ratios improve with an increase in sales.

Endogenizing Exogenous Variables and Parameters

CFS systems are not unique because every financial system may define its endogenous and exogenous variables differently. As a result, each system may define its interdependencies differently, which in turn defines how consistency is achieved in the system. One way we can change the nature of a system is by endogenizing an exogenous variable.

Endogenizing an exogenous variable. System definitions of endogenous and exogenous variables and parameters are arbitrarily defined depending on data availability and analytic needs of the firm’s financial manager. We illustrate next, using HQN data, how exogenous variables may be endogenized. In Figure 7.4, we described the what-if analysis of increasing sales by $1,000 which in turn increased cash receipts from $38,990 to $39,990. In this case, sales is considered an exogenous variable as is COGS. However, we should be concerned with the credibility of the results since we are confident that increasing sales will require an increase in COGS and possibly OEs, AR, interest, and taxes—to name a few exogenous variables affected by an increase in sales. The question is how-much do these other exogenous variables and parameters change and do they change in a systematic way with increases in sales?

To account for changes in exogenous variables and parameters expected with an increase in sales, we begin by considering how to internalize our exogenous variables and parameters when conducting what-if analysis. This is a critical task facing financial managers.

First consider the connection between current receipts and COGS. In 2018 cash COGS was 69% of original cash receipts ($27,000/$38,990). We might consider endogenizing cash COGS by replacing it with 69% of projected cash receipts. Then if cash receipts increase, so will COGS. We express the endogenized value of COGS below:

(7.1)  

Taxes for the previous year were reported to be $68, but this amount assumes original EBT of $170 and an average tax rate of 40%. We endogenize taxes in HQN’s income statement by replacing taxes in the exogenous variable page with the average tax rate parameter of 40% times projected EBT:

(7.2)  

After endogenizing taxes and COGS, we resolve the HQN-CFS template and find new ratios for the “what-if” analysis and report the results in the Activity Table below.

The main result of endogenizing taxes and COGS was to reduce ROE from 16.5% to 10.57% and ROA from 58.5% to 28.85%.

What-If Analysis and Scenarios

What-if analysis often begins when the financial manager considers possible scenarios facing its firm. As the financial manager attempts to describe scenarios and consider possible responses, the manager may do so using what-if analysis after changing one or more exogenous variables. Following these changes in exogenous variables we may follow these changes throughout the CFS using what-if analysis. Consider several scenarios that a typical firm might face. Also consider several questions firm managers might want to answer about the scenario using what-if analysis.

Scenario 1. The firm has not been replacing its long-term assets. As a result, its cost of goods sold (COGS) has been increasing due to increased repairs and maintenance costs. What conditions may have prompted the firm to not replace its long-term assets. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 2. A financial manager is risk-averse and decides to increase the firm’s current assets to reduce the risk of insolvency. What actions can the firm manager take to increase its level of current assets. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 3. Suppose the firm decides to increase the time it takes to repay its notes payable. What are the advantages/disadvantages of adopting such a strategy? What conditions facing the firm might prompt it to increase the time it takes to repay its notes payable? What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 4. To boost its revenue, the firm offers easy credit terms to its customers. What are the implications for the firm? How would you expect the firm’s credit policies to be reflected in the firm’s financial statements? What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 5. Market conditions have reduced the demand for the firm’s products. As a result, cash receipts are falling. Unfortunately, most of the firm’s overhead expenses (OE) are fixed and don’t adjust to changing output levels. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 6. The firm’s owners face serious medical costs and to pay for these, they must extract funds from the business. They want to know how to extract the required funds in such a way that least jeopardizes the firm’s rate of return and solvency. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 7. The firm makes a major investment in long-term assets to improve its efficiency. One impact of the change is to reduce its taxes because of increased depreciation. The firm owners are interested in knowing what other changes in the firm’s financial condition if it makes major investments in long-term assets. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 8. Hard economic times have reduced the firm’s customers’ ability to pay for their purchases in the usual amount of time. The firm wants to know how to respond to its decreased liquidity. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 9. Cash receipts have been inadequate for the firm to meet its notes payable and current long-term liabilities. As a result, it is forced to sell off some of its long-term assets at values less than reported on its balance sheet. The firm wants to know what other strategies it can adopt to meet its solvency demands. What are the consequences of this scenario on the financial condition of the firm reflected in its SPELL ratios?

Scenario 10. Reduced sales without changes in production levels have led to increased inventories. To meet its financial demands, the firm has restructured its debt, decreasing the current portion of the long-term debt. The firm wants to know how these changes will be reflected in its SPELL ratios.

Scenario 4 analysis. Performing what-if analysis for each scenario requires that we assume specific numbers for our exogenous variables. This approach is an alternative to endogenizing one or more of its exogenous variables. In our illustration assume that as a result of offering easy credit terms, cash receipts (CR) increased by 5% from $38,990 to $40,940. Then, because production has increased, assume that cash COGS increase by 8% from $27,000 to $29,160. There may be other changes in exogenous variables and parameters, but these are enough to illustrate how to conduct scenario analysis. After making the changes, we resolve the CFS template and report the consequences in Activity Table 7.6.

Note that increasing HQN’s cash receipts by 5% and it COGS by 8% reduced its ROA from 6.5% to 4.4% and turned its ROE from 8.5% to a negative two percent.

How Much Analysis and Goal Seek

CFS system’s properties allow us to ask and answer important what-if kinds of questions by changing an exogenous variable or parameter and observing its effect on the endogenous variables of the system. Goal Seek is an important Excel function that allows us to ask and answer how-much kinds of questions that take the form: how much of a change is required in an exogenous variable x for variable y to reach a specified value, a goal, equal to a? To illustrate using HQN data, suppose we asked: how much must HQN’s CR increase for ROE to equal 9%?

To answer this question using an Excel spreadsheet that describes HQN’s CFS, we press the [Data] tab and the [What-if Analysis] button. Finally, we press “Goal Seek” in the drop-down menu. Goal Seek asks us to supply three pieces of information: the cell where the goal value is located, the numeric value for the variable identified in the goal cell, and the cell location of the variable we wish to change to achieve our goal. The number we wish to change must be an exogenous variable. We want to change the endogenous variable ROE located in cell H50 to a value of 9% by changing cash sales of landscaping services an exogenous variable located in cell J4 located on the exogenous variables and parameters page. We record this information in the Goal Seek menu below.

Then we click [OK] and that find cash receipts from landscaping services must increase to $30,010 for HQN to earn an ROE of 9%. Furthermore, increasing cash receipts from landscaping services to $30,010 increases total cash receipts to $39,000. Changes in the endogenous variables included in system are described next in Activity Table 7.7.

Note that increasing CR from landscaping services to $39,000 increased of ROE to 9.00%. Of course, there were other consequences. ROA increased to 6.6%. The TIE solvency ratio increased slightly from 1.35 to 1.38. These and other changes we observe by comparing the activity and goal seek columns with the HQN base column.

Creating Subsystems by Exogenizing Endogenous Variables

Sometimes, it is helpful to answer what-if and how-much questions in simpler terms. We can simplify our analysis by creating subsystems of CFS. Indeed, all systems are reduced versions of some larger system. One method for creating subsystems from larger systems is to exogenize some endogenous variables.

We can construct many subsystems. However, we focus on the two that matter most to the firm: those that describe the firm’s ROE and those that describe its solvency. To illustrate, suppose we wanted to build a subsystem around the firm’s ROE. We might begin by assuming that the firm sells each item of what it produces at an exogenously determined price p, whose marginal cost is c, whose fixed overhead expense is b, and whose interest costs are iD where i is the average cost of its debt and D is the sum of the firm’s liabilities determined in the previous period. Finally, letting the number of physical units sold equal S, we define our ROE subsystem by assuming all other variables except ROE to be exogenous. To create an ROE subsystem, we first define EBT as:

(7.3)  

Now we can write the ROE subsystem as:

(7.4)  

Having defined an ROE subsystem, we are prepared to ask what-if questions such as: what would happen to the firm’s ROE if we could increase the ATO by increasing cash receipts? Since our subsystem has defined all its interdependencies, we can find the answer to this what-if question by observing the change in the firm’s ROE in response to changes in the system’s exogenous variables and parameters. We illustrate this approach using HQN’s data. Initially, HQN’s ROE equals:

(7.5)  

Suppose the value of the exogenous variable cash receipts increased to $40,100? The results on the firm’s ROE can be found to equal:

(7.6)  

Another subsystem might involve solvency and the TIE ratio. To analyze this subsystem, we begin with the EBT defined earlier and remove interest costs to obtain earnings before interest and taxes (EBIT) equal to:

(7.7)  

Next we write a DuPont type equation focused on TIE equal to:

(7.8)  

where D/E is the debt-equity leverage ratio. Having now defined a solvency subsystem reflected by the firm’s TIE ratio, we can ask the following what-if question. What-if the firm increased its debt D? Then, what would be the effect on the firm’s solvency? To answer this what-if question, we substitute the simplified EBIT formula into equation (7.8) to obtain:

(7.9)  

To illustrate, we substitute HQN’s data into equation (7.8) to find its initial TIE value. Making the substitution we find:

(7.10)  

Now suppose we ask: what-if the firm’s equity falls by $1,000? In response to this change in an exogenous variable, HQN’s TIE ratio would decline to:

(7.11)  

And what-if the firm’s interest rate increased by one percent to 7.0%? Then its TIE ratio becomes:

(7.12)  

It is important to recognize that the answers to our what-if questions provided by our subsystems are only approximations of what would happen if we considered the entire system. Nevertheless, the subsystem approach provides some useful intuitive explanations that may be disguised in a full system analysis.

Common Size Balance Sheets and Income Statements

When comparing a firm’s financial condition with other firms, with itself over time, or with the average firm in the industry in which it operates, it is often useful to make comparisons using standardized measures such as SPELL ratios. But what if the comparisons we wish to make involves entries in the firm’s financial statements, including balance sheets and income statements? To address the need for standardized measures of balance sheet and income statement entries, we express all items in the balance sheet as a percentage of total assets and all items in the income statement as a percentage of total revenue. We refer to the results as common size balance sheets and common size income statement ratios. Common size balance sheets and income statements are composed of common size ratios that sum to 100%.

Common size balance sheet insights. Consider what we can learn from common size balance sheets and their common size ratios. Suppose that a firm’s cash and marketable securities was $100,000 at the end of 2017 and $110,000 at the end of 2018. In addition, suppose the firm’s total assets were $2,000,000 at the end of 2017 and $3,000,000 at the end of 2018. The firm’s cash and marketable securities increased by $10,000 over the year which might suggest the firm is now more liquid. However, the firm’s cash and marketable securities must now support a larger amount of total assets. The common size cash and marketable securities ratio was 5% at the end of 2017 and only 3.67% at the end of 2018. Thus, the amount of cash and marketable securities available per dollar of assets decreased during the year. Stated differently, cash and marketable securities relative to total assets declined between 2017 and 2018 while increasing in absolute amounts. We report HQN common size balance sheets and ratios in Table 7.8.

Common size balance sheets and what-is analysis. HQN’s common size balance sheet ratios can be examined by comparing them with other firms in the industry described in the last column of Table 7.8. Doing so provides information critical for what-is analysis. HQN’s common size current asset ratio is above the industry average and rising primarily as a result of relatively high and increasing common size inventories ratio. Accounts receivable ratios are low compared to the industry’s average as are long-term asset ratios. Current liabilities ratios are well above the industry average and rising mostly as a result of increasingly accounts payable ratios. Although falling, long-term debt ratios are still above the industry average; owner equity ratios are well below the average firm in the industry. Finding where one’s firm differs from the industry or from other firms using common size balance sheets is one way for understanding what-is the financial condition of the firm.

Common size income statements and what-is analysis. HQN’s common size income statement ratios can be examined by comparing them with other firms in the industry described in the last column of Table 7.9. HQN’s COGS common size ratio in 2018 was close to the industry average. However, its overhead expenses ratio (OE) were much higher than the industry average in both 2017 and 2018. As a result, its EBIT ratios were much lower than the industry average. Furthermore, its interest costs ratios were almost double the industry average. HQN’s high OE and high interest costs ratios relative to the industry average were somewhat mitigated by HQN’s lower than industry ratios for depreciation and taxes. Still, HQN’s net income after taxes (NIAT) ratio in 2018 was low compared to the industry average: 0.25% for HQN versus 2.28% for the industry.

Finding where one’s firm differs from the industry or from other firms using common size income statements common size income statement ratios is one important way to determine what-is the financial condition of the firm.

What-if Analysis and Trade-offs

Common size statement ratios sum to 100%. This fact implies that we cannot increase one variable in the statements without decreasing the relative importance of other variables. This required trade-off in response to changes in exogenous variable provides us an important tool for conduction what-if analysis using common size statements. There are various ways we can describe these trade-offs.

The squeeze versus the bulge. One way to examine trade-offs in common size ratios is to assume the financial system has some characteristics similar to a balloon. If we squeeze a balloon, a corresponding bulge will occur—because balloons require equal pressure on its surface. This balloon-like characteristic is evident in common size financial statements because common size ratios must sum to 100%.

For example, suppose the firm wishes to increase its liquidity by increasing its accounts receivable ratio. However, increasing the accounts receivable and depreciable assets ratios will require that the long-term assets ratio decreases—and profitability and perhaps efficiency may suffer.

CFS and trade-offs. Trade-offs are obvious within common size statements. Some usual trade-offs are summarized in the table that follows. Consider the left-hand column as the “squeeze” and the right-hand column as the “bulge.” However, the “squeeze” and “bulge” comparisons described below are only qualitative possibilities. To find out the quantitative connections, we must look at the change in common size ratios after a change in an exogenous variable or parameter has occurred.

Companion ratios. We now conduct what-if analysis using common size ratios by looking for interesting things in pairs. To begin, look at HQN’s common size inventories ratio: 50% in 2018 versus an industry average of 25.6%. We have found a squeeze. The bulge? Look at its accounts receivable ratio: 11.54% versus an industry standard of 26.4%. Does this suggest that the firm has adopted a stringent credit policy that has discouraged some customers by forcing them to pay in cash, reducing sales and increasing unsold inventories? Perhaps. It’s an area the firm should explore. If HQN’s stringent credit policy were indeed affecting cash receipts, then its inventory turnover ratio (ITO) would be affected. Compare, but this ratio isn’t too far from the industry average: 10.67% versus the industry average of 7.7%. However, the ITO upper quartile for the industry is 14.9%, suggesting a large variability for the industry. So, the firm’s credit policy is still a concern.

Consider companion SPELL ratios. HQN’s debt to equity ratio is 4.0 in 2018 versus the industry average of 1.9. Unfortunately for HQN, a high leverage ratio hasn’t increased profits or rates of return as much as might be expected because of its low efficiency. Continuing, if HQN has unusually high levels of debt relative to its equity, we should expect its interest payments to be above the industry average. They are 1.2% of cash receipts in 2018 versus an industry average of .55%. Already we are alarmed; high leverage is usually accompanied by high risk. One reason that high leverage implies high risk is that the firm’s equity relative to its liability is small and not able to survive a market reversal. Is HQN’s equity low relative to the industry? Very much so: 17.45% in 2018 versus the industry average of 43.6%.

Common Size Statements and Forecasting

Using historical data, we attempt to look ahead to financial conditions the firm is likely to experience in the future. This effort is different than what-if analysis because we are predicting what-is likely to be the financial conditions of the firm in the future. Common size statements can sometimes be helpful when forecasting the future. We introduce the topic of forecasting here and in more detail in Chapter 11. In what follows we describe two forecasting method using common size statements. The first one is trend analysis. The second method is pro-forma analysis.

Trend analysis. Consider the common size balance sheets reported earlier in Table 7.8. Trend analysis begins by looking for significant changes or trends in the asset or liability ratios. Current asset ratios have increased over the three-year period mostly due to increases in inventory levels. Cash and marketable securities common ratios declined during the three years for the same reason—current assets being tied up in inventory. Long-term assets have fallen primarily as a result of declining values of the firm’s property, plant, and equipment. The worrisome result of this trend is that it may project increased maintenance costs associated with aging machinery.

On the debt side of the balance sheet, current liabilities ratios have increased during the three-year period mostly as a result of increases in accounts payable ratios. Long-term debt ratios have declined, and owner equity ratios have remained relatively constant. The question associated with these trends is, can increasing dependence on notes payable be sustained? Are there less expensive sources of financing available?

Examining HQN’s common size income statement, reported in Table 7.9, we see that both EBIT and NIAT ratios increased in 2018. Comparing HQN’s income statement ratios with other firms in the industry, we note that HQN’s EBIT and NIAT ratios were low compared to industry averages, primarily as a result of relatively high overhead expenses and interest expenses ratios. High OE, COGS, and interest costs ratios have reduced HQN’s taxes ratio. Trends in both common size balance sheets and income statements can be used to predict their future value, assuming the trends continue; or, they may be used to identify barriers to the trends continuing.

Pro forma financial statements. Pro forma income statements and balance sheets are forecasts of what these statements will look like in the future and provide essential planning information. There are several ways to construct pro forma statements. The usual technique is to select a key variable and predict its future value. Then assume constant SPELL ratios which include the key variable and solve for other values using other ratios. We demonstrate this approach in what follows.

Assume that HQN wants to achieve a projected level of cash receipts. Also assume that SPELL ratios will remain constant even in the face of projected total revenue increases. Specifically, assume that next year’s projected total revenue for HQN will equal $42,000, an increase of 5%. What does this imply for HQN’s level of inventory? From HQN’s SPELL ratios, we see that the inventory turnover ratio was 10.67%. Assuming this year’s ratios will not change next year, we can use the projected level of cash receipts to forecast the pro forma levels of inventories (INV). The first step in these types of problems is to write out the definition of each ratio and their assumed values:

(7.13)  

From the inventory turnover (ITO) equation we find:

(7.14)  

Next, we use the projected cash receipts level of $42,000 and divide by ITO to find projected HQN’s inventory in 2019:

(7.15)  

We observe an interesting result. Inventories increased by 5% to $4,038.46, the same percentage increases as was projected in total revenue. This result, of equal percentage increases, occurs whenever the ratio is held fixed. When one number of the ratio is increased by some percent, the other number in the ratio must increase by the same percent. To illustrate, consider the m ratio:

(7.16)  

If the m ratio is constant and cash receipts increases to $42,000, then EBIT will increase by 5% to $178.50. Again, the technique is to assume that the historical financial relationships will hold in the future and then project the future value of one variable, usually cash receipts. This allows us to calculate the projected values of the remaining financial variables based on the historical financial relationships. Of course, we could create pro forma income statements and balance sheets by increasing all the variables by some common percent, or just the exogenous variables by the common percent. However, increasing all the exogenous variables by a common percent is confusing, and it is highly unrealistic. That explains why what-if analysis is often applied to the firm’s CFS.

Summary and Conclusions

We began this chapter by describing the CFS as a system in which changes in one part of the system affect other parts of the system. We answered the question, what-is the financial condition of the firm by computing SPELL ratios using CFS data and comparing these with industry averages of similar firms. Because SPELL ratios are computed using CFS data, they reflect the interdependencies inherent in the CFS.

While SPELL ratios can describe what-is the firm’s strengths and weaknesses, they can also answer what-if and how-much kinds of questions by changing CFS exogenous variables that reflect scenarios the financial managers may face—and recomputing the SPELL ratios. Comparing SPELL ratios before and after the changes in exogenous variables and parameters allows us to examine the firm’s strengths and weaknesses. We also answered how-much questions with the help of Excel’s goal seek. How-much questions help us find the requirements for reaching specific financial goals.

What-is analysis assumes relationships among exogenous variables are fixed. However, when we change an exogenous variable required by what-if and how-much analysis, we may need to consider interdependencies between exogenous variables. We demonstrated this interdependencies when we considered an increase in cash sales that would require an increase in COGS and taxes. As a result, to improve the credibility of our scenario analysis using what-if and how-much, we may need to endogenize certain exogenous variables.

We faced the fact that the world is a complicated system and we can sometimes gain insights about it by creating subsystems that assume or define some endogenous variables as exogenous variables and parameters. In effect, we create subsystems from systems by reducing the number of endogenous variables and increasing the number of exogenous variables and parameters­—allowing us to describe a subsystem within a system with a reduced number of endogenous variables.

We found our subsystems to be useful because they allow us to understand the connections between some of the most important parts of the financial system such as a firm’s rates of return and solvency. While there are many subsystems we could create and examine, we emphasized the firm’s TIE ratio and its ROE ratios that reflect a firm’s solvency and rate of return on equity. We expressed our sub-systems using DuPont type equations.

Another important set of ratios we considered was common size ratios. We found common size ratios by dividing CFS balance sheet entries by total assets and CFS income statement entries by total revenue. As a result, common size balance sheet ratios and common size income statement ratios sum to 100%. They reflect what-is the financial condition of the firm by comparing the relative important of each balance sheet and income statement entry to total assets or total revenue.

That common size ratios in balance and income statements sum to 100% facilitates a special kind of what-if analysis: the analysis of trade-offs in the relative importance of common size ratios. We perform trade-off analysis by changing an exogenous variable and recalculating common size ratios and noting the changes in relative importance of each ratio relative to total assets or total revenue.

Finally, we facilitated what-is, what-if, and how much analysis using CFS and common size statements entered into Excel spreadsheets described in the appendix to this chapter.

Questions

1. Describe a system that is different than the CFS system described in this chapter.
2. Refer to Table 7.1 that lists exogenous variables and parameters. Since determining which variables are endogenous and exogenous is somewhat arbitrary, especially when some variables are overidentified, describe factors that influence which variables are exogenous in any particular CFS.
3. SPELL and common size ratios can be used to describe what-is the financial condition of the firm. What-if and how-much analysis examine possible scenarios, counter-factual conditions that differ from the actual condition of the firm. Explain the benefits and limitations of what-is versus what-if analysis.
4. Explain why it may be necessary to endogenize some exogenous variables and parameters when conducting what-if and how-much analysis to improve credibility. Explain why it may be necessary to exogenize some endogenous variables when the goal is to provide a simpler view of the firm. Give examples from the text of endogenizing some exogenous variables and exogenizing some endogenous variables.
5. Choose three of the ten scenarios described in the text. Then perform what-if analysis using the Excel CFS template described in the appendix to this chapter. Describe what changes in exogenous variables or what exogenous variables should be endogenized to improve the credibility of the analysis. Recognize that some scenario analyses may require more than one exogenous variable be changed. Finally, write a brief opportunities and threats report about how the conditions described in the scenario would change the firm’s opportunities and threats.
6. Compare HQN’s 2017 and 2018 common size balance sheet in Table 7.8 with the industry average. Based on these comparisons, describe what is the financial condition of the firm. How does what-is analysis using common size balance sheets and income ratios differ from conducting what-is using SPELL ratios.
7. Refer to Table 7.8 that describe HQN’s common size balance sheets at the end of years 2016, 2017, and 2018. Notice that cash and marketable securities were well above the industry average in 2016 and then declined in both 2017 and 2018 to percentages below the industry average.
8. Common size statement are interdependent. And increase in one common size ratio requires corresponding decreases in other ratios. By referring to other ratios in the common size balance sheet, explain what changes in the firm likely account for HQN’s decline in liquidity reflected by a decrease in the relative importance of cash and marketable securities.
9. Refer to Table 7.8 and observe that HQN’s current liabilities increased from 59.58% in 2017 to 63.46% in 2018. In all years, current liabilities were well above the industry average of 43%. If high levels of current liabilities in HQN’s balance sheets (the squeeze), what is other ratios are below the industry average that would be the companion ratio (the bulge). Is this change a strength or weakness for HQN?
10. Focusing on HQN’s common size income statement in Table 7.9, observe that overhead expenses were increasing between 2017 and 2018 while depreciation was low. Are the two connected? Can you connect this change to changes in the firm’s long-term assets in the common size balance sheets?
11. The common size income statement’s base is cash receipts. Recalculate the common size income statements for 2017 and 2018 using COGS as the new base. What is the effect of changing the base in the calculation of the common size income statement?
12. In Table 7.9, NIAT is especially low compared to the industry average. Can you explain why?
13. Compute a pro forma income statement for HQN for 2019. Assume cash receipts equals $42,000 and the relationships described by the common size income statement for 2018 are maintained.

Appendix to System Analysis

Coordinated financial statements (CFS) and ratios. This appendix operationalizes the calculation of CFS using HQN data reported in Chapter 5, calculates SPELL ratios derived in Chapter 6, and common size ratios derived in this chapter. CFS include interdependent beginning and ending period balance sheets linked with income and cash flow statements. CFS for HQN described in Chapter 5 and HQN SPELL ratios described in Chapter 6 are reported below. Open the HQN Coordinated Financial Statement template in MS Excel.

Exogenous and endogenous data. Data included in CFS and used to find SPELL ratios can be separated into endogenous and exogenous variables and parameters. Exogenous variables and parameters and their values are determined outside of CFS. These are used to find the values of endogenous variables whose values are calculated within CFS. It is somewhat arbitrary which variables are endogenous and endogenous. The distinction depends on what data are available outside of CFS and which data must be calculated. A third data category included in CFS are repeated values of endogenous and exogenous variables and parameters that are reported in more than one location. For example, cash receipts are calculated using exogenous data that are summed and then reported in the statement of cash flow and the income statement. Table 7.A2 below describes exogenous data included in CFS.

Exogenous data reported in Table 7.A2 can be separated into three categories. The first category includes exogenous data included in beginning and ending balance sheets. The second category includes exogenous data included in the statement of cash flow, and the third category includes exogenous data used for side-bar calculations required to combine multiple entries of similar data into a single category that can be used to facilitate comparisons between firms, a firm and the industry in which it operates, and the same firm over time. Side-bar calculated sums are treated as exogenous data in CFS. There is no exogenous data included in income statements in Table 7.A2 since it is created using data transferred from the balance sheets or statement of cash flow or calculated. Exogenous data reported in Table 7.A2 are collected from outside of CFS from such sources as tax records, records of financial transactions including purchases and sales of depreciable and non-depreciable assets, and operating data include product sales receipts and records operating expenses.

Whenever there is a question about a datum source in the CFS, one need only hover the cursor over the cell in question. If the source refers one back to the exogenous data sheet, the variables is an exogenous variable including side bar calculations. If the cell begins with an “=” sign indicating a calculation, the datum is an endogenous variable. If the cell indicates another cell location within the CFS, the data is being transferred from another location and may be either an exogenous or endogenous variable.

SPELL ratios. SPELL ratios included in the CFS were described in Chapter 6. Figure 7.A1 reports two categories of SPELL ratios. The first category describes exogenously determined values that report industry standard ratios. The second SPELL ratios category describes endogenously determined values that reflect the financial condition of the firm being examine. Comparing the two categories of SPELL ratios helps financial managers determine the financial condition of the firm. The formula for each financial ratio reported in the CFS for the firm being examined can be discovered by hovering over the cell containing the ratio of interest. For example, hovering over the ROE cell, we find the ratio is calculated as: J21/C28. Then referring to the CFS, we find J21 equals Earnings Before Taxes of $170 divided by C28 equal to total equity at the beginning of the period of $2,000. ROE then is found as the ratio $170/$2000=8.5%.

What-if analysis. One of the many advantages of using an Excel spreadsheet to describe CFS and SPELL ratios is the ability to considers the consequences of changes in exogenous variables and parameters on the value of endogenous variables. We describe the consequences of change(s) in the value of exogenous variable(s) on endogenously determined SPELL ratios and report the results in Table 7.A3.

The first column in Table 7.A3 describes the financial ratio being reported. The second column describes the average ratio value for the industry to which the firm belongs. Ratios in this column provides the firm a standard against which it can compare itself. The third column describes the value of the firm’s SPELL ratios after the change in the exogenous variable has occurred. We refer to this column as the activity column. Finally, the fourth column describes the value of the ratios before the change in the exogenous variable has occurred. We label this column base values. Of course, what-if analysis can accommodate more than one exogenous variable change. For example, the scenarios described in Chapter 7 may require several changes in exogenous variables and parameters to be adequately described.

For Table 7.A3, the activity column and the base column are equal since no change in an exogenous variable or parameter has been introduced. Now introduce a change. Suppose that cash sales increased from $18,000 to $18,100. We describe the consequences of this change in an exogenous variable will influence HQN’s SPELL ratios in Table 7.A4. While several SPELL ratios change, the most important change increased ROA from 6.5% to 7.5% and increased the ROE ratio from 8.5% to 13.5%. Since the activity column is updated after each “what-if” analysis, it may be convenient to copy each “what-if” table and record it in a separate Excel page.

Goal seek and “how-much” analysis. Finally, we introduce “Goal Seek”, an Excel function that allows us to ask the question: how-much of a change in exogenous variable X is required for an endogenous variable Y to equal a specified value? We refer to this analysis as how-much analysis. The details of how to use Goal Seek and how-much analysis were described in Chapter 7. The main point here is that Goal Seek and how much analysis, like what-if analysis, analyzes the consequences of a change in an exogenous variable on an endogenous variable. The difference is that the size of the change in the exogenous variables is determined by a separate analysis that uses goal seek. The values of SPELL ratios after introducing a change in an exogenous variable whose amount was determined by goal seek is reported in the what-if table, Table 7.A3. For example, suppose we are interested in discovering how-much of a change in labor costs is required to increase ROE to 9.0%. Using goal-seek, we find that decreasing labor costs to $7990 will increase ROE to 9%. Other changes associated with decreasing labor costs to $7,990 are reported in Table 7.A5.

Common size statements and “what-is” analysis. Common size statements introduced in this chapter are an important financial analysis tool that can be used for what-is, what-if, and how-much analysis. Entries in common size statements are ratios like the SPELL ratios but differ because they sum to 100% and therefore emphasis trade-offs between elements of common size balance sheets and income statements. The only point that distinguishes SPELL ratios from common size ratios is that the latter set of ratios care created using the same base for balance sheet and income statement ratios, total assets versus total revenue. Common size ratios using CFS base numbers provide the basis for what-is analysis. Conducting what-if and how-much analysis using CFS after changes in exogenous variables provide the basis for common size statement what-if and how-much analysis. We include an Excel template that describes common size balance sheets in Figure 7.A6. The activity column headed 12/31/2018 reflects a what-if analysis that examines an increase in sales of $1,000.

Finally, we include a common size income statement in Table 7.A7. Note that we have few industry averages to compare the AIS entries, partly because there is less standardization for income statements than for balance sheets.