Exam 9: Financial Modeling Basics:

Use the Microsoft Excel model in a file folder entitled Target Valuation Model on the companion site to this book to address the following questions. The model already contains data and an estimate of Life Tech’s enterprise and equity valuations based on this data and a set of assumptions about the planning period spanning 2014 through 2018, as well as the years beyond. In answering the following questions, assume the valuation provided in this model represents the firm’s base case and reflects what the firm could do if it continued the business strategy in effect in 2012. -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.

When 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.

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.

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

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.

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

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.

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

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.

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

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.

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

Use the Microsoft Excel model in a file folder entitled Target Valuation Model on the companion site to this book to address the following questions. The model already contains data and an estimate of Life Tech’s enterprise and equity valuations based on this data and a set of assumptions about the planning period spanning 2014 through 2018, as well as the years beyond. In answering the following questions, assume the valuation provided in this model represents the firm’s base case and reflects what the firm could do if it continued the business strategy in effect in 2012. -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.

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

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.

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

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.