Exam 9: Financial Modeling Basics:

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.

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

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

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

Tribune Company Acquires the Times Mirror Corporation in a Tale of Corporate Intrigue Background: Oh, What Tangled Webs We Weave. . . CEO Mark Willes had reason to be optimistic about the future. Operating profits had grown at a double-digit rate, and earnings per share had grown at a 55% annual rate between 1995 to 1999. Many shareholders appeared to be satisfied. However, some were not. Although pleased with the improvement in profitability, they were concerned about the long-term growth prospects of the firm. Reflecting this disenchantment, Times Mirror’s largest shareholder, the Chandler family, was contemplating the sale of the company and along with it the crown jewel Los Angeles Times. It had been assumed for years that the Chandler family trusts made a sale of Times Mirror out of the question. The Chandler’s super voting stock (i.e., stock with multiple voting rights) allowed them to exert a disproportionate influence on corporate decisions. The Chandler Trusts controlled more than two-thirds of voting shares, although the family owned only about 28% of the total shares of the outstanding stock. In May 1999 the Tribune Chairman John Madigan contacted Willes and made an offer for the company, but Willes, with the help of his then-chief financial officer (CFO), Thomas Unterman, made it clear to Madigan that the company was not for sale. What Willes did not realize was that Unterman soon would be serving in a dual role as CFO and financial adviser to the Chandlers and that he would eventually step down from his position at Times Mirror to work directly for the family. In his dual role, he worked without Willes’ knowledge to structure the deal with the Tribune. Following months of secret negotiations, the Chicago-based Tribune Company and the Times Mirror Corporation announced a merger of the two companies in a cash and stock deal valued at approximately $7.2 billion, including $5.7 billion in equity and $1.5 billion in assumed debt. The transaction, announced March 13, 2000, created a media giant that has national reach and a major presence in 18 of the nation’s top 30 U.S. markets, including New York, Los Angeles, and Chicago. The combined company has 22 television stations, four radio stations, and 11 daily newspapers—including the Los Angeles Times, the nation’s largest metropolitan daily newspaper and flagship of the Times Mirror chain. Transaction Terms: Tribune Shareholders Get Choice of Cash or Stock The Tribune agreed to buy 48% of the outstanding Times Mirror stock, about 28 million shares, through a tender offer. After completion of the tender offer, each remaining Times Mirror share would be exchanged for 2.5 shares of Tribune stock. Under the terms of the transaction, Times Mirror shareholders could elect to receive $95 in cash or 2.5 shares of Tribune common stock in exchange for each share of Times Mirror stock. Holders of 27.2 million shares of Times Mirror stock elected to receive Tribune stock, whereas holders of 10.6 million elected to receive cash. Because the amount of cash offered in the merger was limited and the cash election was oversubscribed, Times Mirror shareholders electing to receive cash actually received a combination of cash and stock on a pro rata basis (Table 1). As of June 12,2000 Transaction Value Times Mirror Shares Outstanding @ 3/13/00 59,700,000 No. of Times Mirror Shares Exchanged for 2.3 Shares of Tribune Stock 27,238,253 \ 2,587,634,03 No. of Times Mirror Shares Exchanged for Cash 10,648,318 \ 1,011,536,96 Times Mirror Shares Outstanding after Tender Offer 21,813,429 No. of New Tribune Shares Issued for Remaining Times Mirror Shares 54,533,57 \ 2,072,275,77 Equity Value of Offer \ 5,671,446,777 Market Value of Times Mirror on Merger Announcement Date \ 2,805,900,00 Premium 102\% $^127,238,253 \times 2.5 \times \$ 38 /$ share of Tribune stock. $^ 2 \$ 41.70$ in cash +1.4025 shares of Tribune stock $\times \$ 38$ per share for each Times Mirror share remaining $\times$ $10,648,318$ ${ }^{3}$ Equals 2.5 shares $\times 21,813,429 \times \$ 38$ per Tribune share. ${ }^{4}$ Times Mirror share price on announcement date of $\$ 47$ times $59,700,000$ . ${ }^{5}$ The total number of new Tribute shares issued equals $27,238,318 \times 2.5+10,648,318 \times 2.5+54,533,573$ or $137,537,013$ Newspaper Advertising Revenues Continue to Shrink Most U.S. newspapers are mired in the mature or declining phase of their product life cycle. For the past half-century, newspapers have watched their portion of the advertising market shrink because of increased competition from radio and television. By the early 1990s, all major media began taking a significant hit in their advertising revenue streams as businesses discovered that direct mail could target their message more precisely. Moreover, consolidation among major retailers further reduced the size of advertising dollar pool. The same has happened with numerous large supermarket chain mergers. Newspaper advertising revenues also have been threatened by increasing competition from advertising and editorial content delivered on the internet. Finally, newspapers simply have become less attractive places to advertise as readership continues to decline as a result of an aging population and new generations that do not see newspapers as relevant. Times Mirror: A Largely Traditional Business Model As essentially a traditional newspaper, Times Mirror publishes five metropolitan and two suburban daily newspapers, a variety of magazines, and professional information such as flight maps for commercial airline pilots. The Los Angeles Times, a southern California institution founded in 1881, is Times Mirror’s largest holding and operates some two dozen expensive foreign news bureaus—more than any other newspaper in the country. The Los Angeles Times has more than 1200 Los Angeles Times reporters and editors around the world (CNNfn, March 13, 2000). Tribune Company Profile: The Face of New Media? Unlike the Times Mirror, Tribune has built its strategy around four business groups: broadcasting, publishing, education, and interactive. The Tribune is also an equity investor in America Online and other leading internet companies, underscoring the company’s commitment to new-media technologies. Applying leading edge new-media technology has allowed the Tribune to transform they way it does business, and the technology commitment creates the opportunity for future growth. The internet has been the greatest driver for change, and the Tribune’s interactive business group continues to focus on capitalizing on emerging Web technologies. Throughout the company, new technologies have been applied aggressively to create new products, improve existing products, and make operations more efficient. The Tribune’s non-newspaper revenues accounted for more than half of its earnings by 2000. Anticipated Synergy Cost Savings: Opportunities Abound Cost savings are expected because of the closing of selected foreign and domestic news bureaus, a reduction in the cost of newsprint through greater volume purchases, the closing of the Times Mirror corporate headquarters, and elimination of corporate staff. Such savings are expected to reach $200 million per year (Table 2). Table 2. Annual Merger-Related Cost Savings Source of Value Annual Savings Bureau Closings \ 73,000,000 Newsprint Savings \ 93,000,000 \ 34,000,000 Total Annual Savings \ 200,000,000 ${ } ^ { 1 }$ Assumes Tribune will close overlapping bureaus in United States (9) and most of the Times Mirror's foreign bureaus ( 21 abroad). ${ } ^ { 2 }$ As a result of bulk purchasing and more favorable terms with different suppliers, $15 \%$ of the newsprint expense of the combined companies is expected to be saved. ${ } ^ { 3 }$ Layoffs of 120 L.A. Times Mirror Corporate Office personnel at an average salary of $\$ 125,000$ and benefits equal to $30 \%$ of base salaries. Total payroll expenses equal $\$ 19,500,000$ (i.e., $\$ 125,000 \times 1.3 \times 120$ ). Lease, travel and entertainment, and other support expenses added another $\$ 14.5$ million. Source: Moore, Kathryn, Tim Schnabel, and Mark Yemma, "A Media Marriage," paper prepared for Chapman University, EMBA 696, May 18,2000 , p. 9. Revenue: Great Potential . . . But Is It Achievable? The combined companies will have a major presence in 18 of the nation’s top 30 U.S. advertising markets, including New York, Los Angeles, and Chicago. The combined companies provide unprecedented opportunities for advertisers to reach major market consumers in any media form—broadcast, newspapers, or interactive. In addition, the combined companies will benefit consumers by giving them rich and diverse choices for obtaining the news, information, and entertainment they want anytime, anywhere. These factors provide an increased ability to capture national advertising in the most important U.S. population centers. The significantly greater breadth of the combined firm’s geographic coverage is expected to boost advertising revenues from about 3% to 6% annually. Integration Challenges: Cultural Warfare? Based on the current, traditional culture found at the Los Angeles Times and other Times Mirror properties, integration following the merger was likely to be slow and painful. Concerns among journalists about spreading their talents thin across three or four media—print, television, online, and radio—in the course of a day’s work raised the stress level. Although the Tribune has been able to make the transition to a largely multimedia company more rapidly than the more traditional newspapers, it has been costly. For example, development losses in 1999 were $30–35 million at Chicagotribune.com and an estimated $45 million in 2000. The bleeding was expected to continue for some time and to constitute a major distraction for the management of the new company. Financial Analysis The present values of the Tribune, Times Mirror, and the combined firms are $8.5 billion, $2.4 billion, and $16.5 billion, respectively; the estimated present value of synergy is $5.6 billion (Table 3). This assumes that pretax cost savings are phased in as follows: $25 million in 2000, $100 million in 2001, and $200 million thereafter. The cost savings are net of all expenses related to realizing such savings such as severance, lease buyouts, and legal fees. Table 4 describes how the initial offer price could have been determined and the postmerger distribution of ownership between Times Mirror and Tribune shareholders. $\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\text { Table 4. Offer Price Determination }$ Tribune Times Mirror Combined Incl. Synergy Value of Synergy Equity Valuations 8501.5 2375.0 16443.7 5567.3 Minimum Offer Price 2805.9 Maximum Offer Price 8373.2 Actual Offer Price 5671.4 \% Maximum Offer Price 67.7\% Purchase Price Premium 1.02 New Tribune Shares Issued 137.50 Ownership Distribution TM Shareholders 0.37 Tribune Shareholders 0.63 Market value of Times Mirror on the merger announcement date. Epilogue Only time will tell if actual returns to shareholders in the combined Tribune and Times Mirror company exceed the expected financial returns provided in the valuation models in this case study. Times Mirror shareholders earned a substantial 102% purchase price premium over the value of their shares on the day the merger was announced. Some portion of those undoubtedly “cashed out” of their investment following receipt of the new Tribune shares. However, for those former Times Mirror shareholders continuing to hold their Tribune stock and for Tribune shareholders of record on the day the transaction closed, it is unclear if the transaction made good economic sense. -Dose the Times Mirror-Tribune Corporation merger create value? If so, how much? What percentage of this value goes to Times Mirror shareholders and what percentage to Tribune shareholders? Why?

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

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

Financial models are said to be in balance when

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

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.

Mars Buys Wrigley in One Sweet Deal Under considerable profit pressure from escalating commodity prices and eroding market share, Wrigley Corporation, a U.S.-based leader in gum and confectionery products, faced increasing competition from Cadbury Schweppes in the U.S. gum market. Wrigley had been losing market share to Cadbury since 2006. Mars Corporation, a privately owned candy company with annual global sales of $22 billion, sensed an opportunity to achieve sales, marketing, and distribution synergies by acquiring Wrigley Corporation. On April 28, 2008, Mars announced that it had reached an agreement to merge with Wrigley Corporation for $23 billion in cash. Under the terms of the agreement, which were unanimously approved by the boards of the two firms, shareholders of Wrigley would receive $80 in cash for each share of common stock outstanding, a 28 percent premium to Wrigley's closing share price of $62.45 on the announcement date. The merged firms in 2008 would have a 14.4 percent share of the global confectionary market, annual revenue of $27 billion, and 64,000 employees worldwide. The merger of the two family-controlled firms represents a strategic blow to competitor Cadbury Schweppes's efforts to continue as the market leader in the global confectionary market with its gum and chocolate business. Prior to the announcement, Cadbury had a 10 percent worldwide market share. As of the September 28, 2008 closing date, Wrigley became a separate stand-alone subsidiary of Mars, with $5.4 billion in sales. The deal is expected to help Wrigley augment its sales, marketing, and distribution capabilities. To provide more focus to Mars's brands in an effort to stimulate growth, Mars would in time transfer its global nonchocolate confectionery sugar brands to Wrigley. Bill Wrigley Jr., who controls 37 percent of the firm's outstanding shares, remained the executive chairman of Wrigley. The Wrigley management team also remained in place after closing. The combined companies would have substantial brand recognition and product diversity in six growth categories: chocolate, nonchocolate confectionary, gum, food, drinks, and pet care products. While there is little product overlap between the two firms, there is considerable geographic overlap. Mars is located in 100 countries, while Wrigley relies heavily on independent distributors in its growing international distribution network. Furthermore, the two firms have extensive sales forces, often covering the same set of customers. While mergers among competitors are not unusual, the deal's highly leveraged financial structure is atypical of transactions of this type. Almost 90 percent of the purchase price would be financed through borrowed funds, with the remainder financed largely by a third-party equity investor. Mars's upfront costs would consist of paying for closing costs from its cash balances in excess of its operating needs. The debt financing for the transaction would consist of $11 billion and $5.5 billion provided by J.P. Morgan Chase and Goldman Sachs, respectively. An additional $4.4 billion in subordinated debt would come from Warren Buffet's investment company, Berkshire Hathaway, a nontraditional source of high-yield financing. Historically, such financing would have been provided by investment banks or hedge funds and subsequently repackaged into securities and sold to long-term investors, such as pension funds, insurance companies, and foreign investors. However, the meltdown in the global credit markets in 2008 forced investment banks and hedge funds to withdraw from the high-yield market in an effort to strengthen their balance sheets. Berkshire Hathaway completed the financing of the purchase price by providing $2.1 billion in equity financing for a 9.1 percent ownership stake in Wrigley. -Speculate as to the potential sources of synergy associated with the deal. Based on this speculation what additional information would you want to know in order to determine the potential value of this synergy?

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.

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

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

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

Mars Buys Wrigley in One Sweet Deal Under considerable profit pressure from escalating commodity prices and eroding market share, Wrigley Corporation, a U.S.-based leader in gum and confectionery products, faced increasing competition from Cadbury Schweppes in the U.S. gum market. Wrigley had been losing market share to Cadbury since 2006. Mars Corporation, a privately owned candy company with annual global sales of $22 billion, sensed an opportunity to achieve sales, marketing, and distribution synergies by acquiring Wrigley Corporation. On April 28, 2008, Mars announced that it had reached an agreement to merge with Wrigley Corporation for $23 billion in cash. Under the terms of the agreement, which were unanimously approved by the boards of the two firms, shareholders of Wrigley would receive $80 in cash for each share of common stock outstanding, a 28 percent premium to Wrigley's closing share price of $62.45 on the announcement date. The merged firms in 2008 would have a 14.4 percent share of the global confectionary market, annual revenue of $27 billion, and 64,000 employees worldwide. The merger of the two family-controlled firms represents a strategic blow to competitor Cadbury Schweppes's efforts to continue as the market leader in the global confectionary market with its gum and chocolate business. Prior to the announcement, Cadbury had a 10 percent worldwide market share. As of the September 28, 2008 closing date, Wrigley became a separate stand-alone subsidiary of Mars, with $5.4 billion in sales. The deal is expected to help Wrigley augment its sales, marketing, and distribution capabilities. To provide more focus to Mars's brands in an effort to stimulate growth, Mars would in time transfer its global nonchocolate confectionery sugar brands to Wrigley. Bill Wrigley Jr., who controls 37 percent of the firm's outstanding shares, remained the executive chairman of Wrigley. The Wrigley management team also remained in place after closing. The combined companies would have substantial brand recognition and product diversity in six growth categories: chocolate, nonchocolate confectionary, gum, food, drinks, and pet care products. While there is little product overlap between the two firms, there is considerable geographic overlap. Mars is located in 100 countries, while Wrigley relies heavily on independent distributors in its growing international distribution network. Furthermore, the two firms have extensive sales forces, often covering the same set of customers. While mergers among competitors are not unusual, the deal's highly leveraged financial structure is atypical of transactions of this type. Almost 90 percent of the purchase price would be financed through borrowed funds, with the remainder financed largely by a third-party equity investor. Mars's upfront costs would consist of paying for closing costs from its cash balances in excess of its operating needs. The debt financing for the transaction would consist of $11 billion and $5.5 billion provided by J.P. Morgan Chase and Goldman Sachs, respectively. An additional $4.4 billion in subordinated debt would come from Warren Buffet's investment company, Berkshire Hathaway, a nontraditional source of high-yield financing. Historically, such financing would have been provided by investment banks or hedge funds and subsequently repackaged into securities and sold to long-term investors, such as pension funds, insurance companies, and foreign investors. However, the meltdown in the global credit markets in 2008 forced investment banks and hedge funds to withdraw from the high-yield market in an effort to strengthen their balance sheets. Berkshire Hathaway completed the financing of the purchase price by providing $2.1 billion in equity financing for a 9.1 percent ownership stake in Wrigley. -Why was market share in the confectionery business an important factor in Mars' decision to acquire Wrigley?

What are value drivers and why are they important?

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

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.

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.