Deck 9: Financial Modeling Basics:

Question 1

What is financial modeling? How is it helpful in analyzing a firm's financial statements?

Answer 1

Financial modeling refers to the creation of a mathematical representation or model of the financial and operational characteristics of a business. Applications involving financial modeling include business valuation, management decision making, capital budgeting, financial statement analysis, and determining the firm's cost of capital. Such models are at best a simplistic representation of how the firm actually creates value for its shareholders. Their real value comes from forcing the model builder to think about the important relationships among the firm's financial statements and to focus on the key determinants of value creation and the assumptions underlying forecasts. Such models also enable an analyst to examine the financial implications for a firm of certain economic events.

Question 2

Based on your answers to questions 1, 2, and 3, what do you believe are the most important value drivers for Life Tech based on their impact on the firm's enterprise and equity values? List these variables in descending order in terms of their impact on increasing the magnitude of Life Tech's enterprise and equity values.

Answer 2

The most important value driver is the change in sales followed by the change in cost of sales, then change in capital structure, and finally changes in receivables and inventories. Note that this ordering is dependent on the magnitudes of the assumed changes in value drivers. Note also that the percent changes in enterprise and equity values reflect the cumulative effects of previous changes made to value drivers.

Question 3

How is financial modeling applied to mergers and acquisitions?

Answer 3

Financial modeling helps the analyst understand the underlying relationships among a firm's financial statements, the key determinants of value creation, provides a useful means of assessing alternative options and risks, and identifies how firm value is affected by different economic events. Such models enable an analyst to determine the economic value of a firm as a standalone business (i.e., one for which all known revenues and the costs and assets/liabilities required to produce those revenues are contained on the firm's financial statements). The standalone value of the target constitutes the minimum value of the business and provides the basis for calculating the maximum value of the business (i.e., minimum value plus synergy equals maximum value). As explained in detail in Chapter 14, the amount an acquirer is willing to pay in excess of the standalone value represents some percentage of the difference between the minimum and maximum values

Question 4

What are common size financial statements and how might they impact the financial model building process?

Question 5

What are the differences between GAAP based and Pro Forma financial statements?

Question 6

base case valuation reflects a constant 57 percent gross margin throughout the planning period. Based on the information given in the case study, do you believe that this is realistic? Why? Why not? How might this assumption have biased the estimates of enterprise and equity valuation in your answers to questions (1) to (4)? If they were biased, what would be the direction of the bias?

Question 7

the enterprise and equity valuations for Life Technology in the Excel spreadsheet model entitled Target Valuation Model on the companion website accompanying this book. View this as the base case. The CEO Greg Lucier asks his chief financial officer (CFO) to determine the impact of plausible assumption changes on the firm's valuation. The CFO asks you as a financial analyst to estimate the impact of a change in the firm's revenue growth rate and cost of sales as a percent of sales. On the Target Assumptions Worksheet, make the following changes and note their impact on Life Tech's enterprise and equity values on the Valuation Worksheet:
a. Increase the sales growth rate in 2014 by two percentage points
b. Retaining the assumption change made in (a), decrease the cost of sales as a percent of sales by
two percentage points in 2014
What is Life Tech's enterprise and equity value resulting from these changes? How do they compare to the base case? Briefly explain why each of these changes affects firm value. Do not undo the results of your changes to the model's base case.

Question 8

The purpose of this practice exercise is to underscore how small changes in terminal value assumptions result in disproportionately large changes in firm value. Using the Excel spreadsheet model in the file folder entitled Target Valuation Model found on the companion website to this book, locate the present value of the Target's enterprise and equity values on the Valuation Worksheet and write them down. Increase the terminal value growth rate assumption by one percentage point and reduce the discount rate by one percentage point. How does this impact the firm's enterprise and equity value? Explain how this might happen (Hint: Consider the definition of the constant growth valuation model.) Click the undo command to eliminate changes to the base case model or close model but do not save the results.

Question 9

The purpose of this practice exercise is to illustrate how a change in an input cell impacts variables on other financial statements. Using the Excel spreadsheet model in the file folder entitled Target Valuation Model on the companion website accompanying this book (see the beginning of this chapter for website address), note the values for 2018 of Target's net income (Target IS Worksheet), cash balance and shareholders' equity (Target's BS Worksheet), and enterprise value and equity value (Target Valuation Worksheet).
Change the Target's revenue growth rate assumption by one percentage point in 2014. On the Target's Assumptions Worksheet, increase the growth rate from 5.5 to 6.5 percent in the yellow input cell on the Sales Growth line for the year 2014. What are the new values in 2018 for net income, cash balance, shareholders' equity, enterprise value, and equity value following the increase in the growth rate assumption in the base case is increased by one percentage point? Explain why these variables increased (Hint: See Figure 9.1)? When done, click the undo command or close the model but do not save the output to restore the base case model results.

Question 10

Financial models normally are said to be in balance when total assets equal total liabilities plus shareholders' equity on the balance sheet. How are financial models often forced to balance automatically?

Question 11

What does it mean to normalize historical financial data and why is this an important part of the financial model building process?

Question 12

A firm's financial statements are tightly linked such that an increase in a key variable on one statement will impact the other financial statements. Assuming a firm's gross margin (i.e., sales less cost of sales) is positive and constant, describe how an increase in revenue will impact net income and in turn the other financial statements? Assume the firm does not pay preferred dividends.

Question 13

Given the results of the model from questions (1) and (2), assume Mr. Lucier and the Life Tech board increased their target debt to equity ratio from 30 percent to 75 percent. Recalculate the firm's weighted average cost of capital assuming that none of the assumptions about the cost of capital made in the base case have changed, with the exception of the levered beta. (Hint: The levered beta needs to be unlevered and then relevered to reflect the new debt to equity ratio). Without undoing the assumption changes made in questions (1) and (2), use your new estimate of the firm's WACC during the planning period to calculate Life Tech's enterprise and equity values given on the Valuation Worksheet. Briefly explain why the change in the debt to equity ratio affected firm value.

Question 14

scrupulous application of GAAP ensures both consistency in comparing one firm's financial
performance with another and the accuracy of the data.

Question 15

What is the difference between a firm's enterprise and equity values?

Question 16

Using the model results from question (1), the CFO believes that in addition to an increase in the sales growth rate and an improving cost position, Life Tech could employ its assets more effectively by better managing its receivables and inventory. Specifically, the CFO directs you as a financial analyst to make the following changes to days sales in receivables and days in inventory. On the Target Assumptions Worksheet, make the following changes and note their impact on Life Tech's enterprise and equity values on the Valuation Worksheet:
a. Reduce receivables days by ten days starting in 2014
b. Reduce inventory days by ten days starting 2014
What is Life Tech's enterprise and equity value resulting from these changes? How do they compare to the results in question one? Briefly explain why each of these changes affects firm value. Do not undo the changes to the model you have made.

Question 17

What are value drivers and why are they important?

Question 18

What is the appropriate number of years to project a firm's financial statement?

Question 19

order to normalize the historical financial data of the target firm, it may be necessary to subtract large
increases in and add back large decreases in non-recurring expenses from operating profits.

Question 20

Improper revenue recognition is the most common form of financial reporting fraud.

Question 21

Pro forma financial statements are simply another name for GAAP financial statements.

Question 22

Although public companies still are required to file their financial statements with the Securities and Exchange Commission in accordance with GAAP, companies increasingly are using pro forma statements to portray their financial performance in what they argue is a more realistic (and usually more favorable) manner.

Question 23

The accuracy of any valuation is heavily dependent on understanding the historical competitive dynamics of the industry, the historical performance of the company within the industry, and the reliability of the data used in the valuation.

Question 24

Projecting as many of the key income, cash flow, and balance sheet components as a percent of projected revenue helps to ensure the internal consistency of the model. True or False

Question 25

the target firm's ratio of bad debt reserves as a percent of projected revenue is decreasing, the analyst
should be concerned that projected net revenues could be higher than would actually be realized due to
inadequate future reserves for probable uncollectable accounts.

Question 26

Common size financial statements are useful for comparing businesses of different sizes in the same industry at different points in time.

Question 27

Competitive dynamics simply refer to the factors within the industry that determine industry profitability and cash flow.

Question 28

output of M&A models is only as good as the accuracy and timeliness of the numbers that are used to create the model and the quality of the assumptions used in making the projections.

Question 29

Complex models because of their greater sophistication are necessarily more accurate than simple models.

Question 30

Value drivers are factors such as product volume, selling price, and cost of sales that have a significant impact on the value of the firm whenever they are altered.

Question 31

Pro forma financial statements rarely deviate from those compiled in accordance with GAAP.

Question 32

Common size financial statements are among the most commonly used tools to uncover data irregularities.

Question 33

A clear statement of all assumptions underlying the model's projections forces the analyst to display their biases and to be prepared to defend their assumptions to others.

Question 34

Discrepancies between the way a firm records its financial statements and GAAP accounting standards are common and should be ignored.

Question 35

the target firm is an operating division of a larger firm, it is common for the parent to provide services to the target at below market prices. In calculating the target's standalone value, it is necessary to subtract the difference between the market price of these services and actual cost paid to the parent from the target firm's net income.

Question 36

While it is legitimate for a firm to follow different accounting practices for financial reporting and tax purposes, the relationship between book and tax accounting is likely to remain constant over time, unless there are changes in tax rules or accounting standards.

Question 37

A standalone business is one whose financial statements reflect all the costs of running the business and all of the revenues generated by the business.

Question 38

While GAAP does not ensure accuracy, it is helpful to the analyst in that statements that conform to GAAP rules must adhere to certain standards.

Question 39

Financial modeling refers to the application of spreadsheet software to define simple arithmetic relationships among variables within the firm's income, balance sheet, and cash-flow statements and to define the interrelationships among the various financial statements.

Question 40

normalizing historical data, monthly revenue may be aggregated into quarterly or even annual data to minimize possible distortions in earnings or cash flow due to inappropriate accounting practices. True or False

Question 41

Circular references are a series of cell references in which the last cell reference refers to the first resulting
in a closed loop.

Question 42

Value drivers are variables which exert the greatest impact on firm value.

Question 43

A cash flow statement summarizes the firm's cash inflows and outflows from operating, investing, and
financing activities during a specific time period.

Question 44

Since above average profit growth is not sustainable indefinitely, cash flows for such firms should be projected until they are expected to slow to the industry average.

Question 45

A financial model creates a set of projections about the future of a business in terms of the businesses'
income statement, balance sheet, and cash flow statement. Each statement is linked in such a way that
changing assumptions about one factor can result in changes only in the cash flow statement.

Question 46

Common size financial statements may be constructed by calculating the percentage each line item of the income statement, balance sheet, and cash flow statement is of annual sales for each quarter or year for which historical data are available.

Question 47

It is rarely useful to review more than one or two years of historical data for the acquiring or target firms.

Question 48

The income statement measures a firm's financial performance at a specific point in time.

Question 49

Financial modeling refers to the creation of a mathematical representation or model of the financial and operational characteristics of a business.

Question 50

Financial ratio analysis is the calculation of performance ratios from data in a company's financial statements to identify the firm's financial strengths and weaknesses.

Question 51

If the factors affecting sales growth historically are expected to change in the future due to the introduction of new products, total revenue growth may accelerate from its historical trend. In contrast, the emergence of additional competitors in the future may limit revenue growth by eroding the firm's market share and selling prices.

Question 52

By expressing the target's line-item data as a percentage of sales, it is possible to compare the target company with other companies' line item data expressed in terms of sales to highlight significant differences.

Question 53

Equal to the difference between sales and cost of sales as a percent of sales, gross margin per dollar of sales
is seldom used as a means of summarizing a firm's ability to create value.

Question 54

A simple model to project cash flow rarely involves the projection of revenue and the various components of cash flow as a percent of projected revenue.

Question 55

Examples of relevant historical relationships that are useful for forecasting cash flows include the relationship between fixed and variable expenses and the impact on revenue of changes in product prices and unit sales. If these relationships can reasonably be expected to continue through the forecast period, they can be used to project the earnings and cash flows used in the valuation process. However, it is important to ignore cyclical movements in the data.

Question 56

Financial modeling also provides a useful means of assessing alternative options and associated risks and
identifies how firm value is affected by different economic events.

Question 57

Historical cash flow may be adjusted by deducting unusually large increases in reserves or by adding back large decreases in reserves from free cash flow to the firm.

Question 58

Enterprise value often is defined as the sum of the market value of a firm's equity, preferred shares, debt, and non-controlling interest less total cash and cash equivalents.

Question 59

Trend extrapolation, which entails extending present trends into the future using historical growth rates or multiple regression techniques, is rarely used to forecast cash flow.

Question 60

Firms attempt to maintain minimum cash balances to meet short term working capital needs such as
payroll. Any cash from operating and investing activities in excess of that required to maintain the firm's
desired minimum cash balance may be used to repay any outstanding debt.

Question 61

Financial models are said to be in balance when

A) Net income is positive
B) Total assets equal total liabilities plus shareholders' equity
C) Total assets equal total liabilities less shareholders' equity
D) Cash flow is positive
E) None of the above

Question 62

The constant growth method of valuation often is used to estimate a firm's terminal growth period value. Small changes in the key underlying assumptions such as the terminal growth rate and the discount rate used during the terminal period seldom have very large impacts on the firm's enterprise and equity values.

Question 63

An increase in gross margin over time could indicate what about the firm:

A) It has been able to reduce its costs compared to sales
B) It has been able to raise prices
C) It has been able to achieve a combination of (a) and (b)
D) It was forced to lower its prices
E) a, b, or c

Question 64

Financial models normally are said to be in balance when total assets equal total liabilities minus shareholders' equity on the balance sheet.

Question 65

Common items found on a cash flow statement include all of the following except for which of the following:

A) Net income
B) Change in working capital
C) Principal repayments on outstanding debt
D) Retained earnings
E) The proceeds of asset sales

Question 66

An increase in revenue growth, assuming it is profitable, will increase the firm's equity value but not its enterprise value assuming nothing else changes.

Question 67

Which of the following is true of pro forma financial statements?

A) Pro forma statements purport to show what the combined firms would look adjusted for synergy and the terms of the deal
B) Pro forma statements are the same as GAAP statements
C) Pro forma statements show what the combined firms would look excluding the effects of synergy
D) a and b only
E) None of the above

Question 68

Cool Autos acquired Automotive Industries in a transaction that produced an NPV of $3.7 million. This NPV represents

A) Synergy
B) Book value
C) Investment value
D) Diversification
E) None of the above

Question 69

Common items found on a balance sheet include all of the following except for which of the following:'

A) Receivables b Cost of sales
C) Inventory
D) Long-term debt
E) Payables

Question 70

Financial modeling may be applied in which of the following situations:

A) Business valuation
B) Management decision making
C) Capital budgeting
D) Financial statement analysis
E) All of the above

Question 71

Target is a wholly owned subsidiary of MegaCorp Inc. MegaCorp supplies a number of services to target. Target sells some of its products to other MegaCorp subsidiaries. Target also buys products from other MegaCorp subsidiaries that are used as inputs in producing Target's products. Which of the following adjustments should the acquirer make to Target's financial statements before valuing the firm?

A) Deduct the actual cost of services required by Target that are being supplied by the parent without charge from target's cost of sales.
B) Deduct the difference between the cost of products purchased from other MegaCorp subsidiaries at below market prices and the actual market prices for such products from Target's cost of sales.
C) Deduct the difference between the cost of products purchased from other MegaCorp subsidiaries at above market prices and actual market prices from Target's cost of sales
D) A and B only.
E) None of the above.

Question 72

target firm's standalone value is best defined by which of the following statements:

A) What a business would be worth as part of another firm
B) What a business would be worth as a going concern following a takeover bid
C) What a business would be worth as a going concern in the absence of a takeover bid
D) What the target is worth after the closing date following a takeover
E) None of the above

Question 73

Which of the following is not true about generally accepted accounting principles (GAAP)?

A) GAAP provide specific guidelines as to how to account for specific events impacting the financial performance of the firm.
B) The scrupulous application GAAP accounting rules ensures consistency in comparing one firm's financial performance to another.
C) It is customary for definitive agreements of purchase and sale to require that a target company represent that its financial books are kept in accordance with GAAP.
D) GAAP guarantees that a firm's financial books are accurate.
E) Differences between how a firm records actual financial transactions and how they should be recorded based on GAAP may indicate fraud or mismanagement.

Question 74

Which of the following is not true about common size financial statements?

A) Such statements are used to uncover data irregularities.
B) Such statements are constructed by calculating the percentage each line item of the income statement, balance sheet, and cash flow statement is of annual sales.
C) Such statements are useful for comparing businesses of different sizes in the same industry at different moments in time.
D) Common size statements applied over a number of consecutive periods may be used to determine if the target firm is deferring necessary spending.
E) Common size statements may be calculated for both quarterly and annual financial data.

Question 75

Value drivers are best described by which of the following statements:

A) Variables which exert the greatest impact on firm value
B) Only involve revenue-related variables
C) Only involve cost-related variables
D) Refer to the weighted average cost of capital and cost of equity only
E) None of the above

Question 76

A 10% reduction in the cost of sales as a percent of sales will have the same impact on a firm's enterprise and equity values as a 10% increase in revenue.

Question 77

Which of the following future events could affect projections of a firm's cash flow?

A) Introduction of new products by the firm
B) The emergence of products that are substitutes for the firm's products
C) The emergence of additional competitors to the firm
D) Improvements in the firm's productivity
E) All of the above

Question 78

The financial modeling process used to value a firm consists of a series of steps. These include which of the following:

A) Analyzing the target firm's historical statements to identify the primary determinants of cash flow.
B) Project three-to-five years (or more) of annual pro forma financial statements. This three-to-five year period is called the planning period.
C) Estimating the present value projected pro forma cash flows during the planning period.
D) Estimating the terminal value.
E) All of the above

Question 79

The 10k contains detailed income, balance sheet, and cash flow statements as well as numerous footnotes explaining these financial statements.

Question 80

Common items found on an income statement include all of the following except for which of the following:

A) Revenue
B) Retained earnings
C) Cost of sales
D) Sales, general and administrative expenses
E) Net income