1..1.....All the question requires answer kindly, ,,

Summarize your key points for the Body Shop International case. What were the results of any analysis and what were your key takeaways/conclusions.

THE BODY SHOP INTERNATIONAL PLC 2001: AN INTRODUCTION TO FINANCIAL MODELING

Finance bored the pants off me. I fell asleep more times than not.1 ?Anita Roddick, founder,

The Body Shop International

Roddick, as self-righteous as she is ambitious, professes to be unconcerned [with financial results].... "Our business is about two things: social change and action, and skin care," she snaps. "Social change and action come first. You money- conscious people ... just don't understand." Well, maybe we don't, but we sure know this: Roddick is one hell of a promoter.... She and her husband, Gordon, own shares worth just under $300 million. Now that's social action.2

One of our greatest frustrations at The Body Shop is that we're still judged by the media and the City by our profits, by the amount of product we sell, whereas we want and have always wanted to be judged by our actions in the larger world, by the positive difference we make.3

?Anita Roddick

In the late 1990s, The Body Shop International PLC, previously one of the fastest growing manufacturer-retailers in the world, ran aground. Although the firm had an annual revenue growth rate of 20% in the early to middle 1990s, by the late 1990s, revenue growth slowed to around 8%. New retailers of the naturally based skin- and hair-care products entered the market, bringing intense competition for The Body Shop. Amidst the competition, The Body Shop failed to maintain its brand image by becoming something of a mass-market line as it

-2

expanded into "almost every mall in America, as well as virtually every corner on Britain's shopping streets."4

Anita Roddick, founder of The Body Shop, stepped down as chief executive officer (CEO) in 1998,5 after numerous unsuccessful attempts to reinvent the company. Patrick Gournay, an executive from the French food giant Danone SA came on board as CEO. However, problems persisted despite the management change. In fiscal year 2001, revenue grew 13%, but pretax profit declined 21%. Gournay said of the results, "This is below our expectations, and we are disappointed with the outcome."6

Nonetheless, Gournay was confident that a newly implemented strategy would produce improved results. The strategy consisted of three principal objectives: "To enhance The Body Shop Brand through a focused product strategy and increased investment in stores; to achieve operational efficiencies in our supply chain by reducing product and inventory costs; and to reinforce our stakeholder culture."7

Suppose that Anita Roddick, the Shop's founder and cochair of the board of directors, and Patrick Gournay, CEO, came to you in the spring of 2001, looking for assistance in short- and long-term planning for The Body Shop. As a foundation for this work, you will need to estimate The Body Shop's future earnings and financial needs. The challenge of this advisory work should not be underestimated. Anita Roddick is a strong-willed decision-maker with little taste for finance or financial jargon. Your projections must not only be technically correct, but they must also yield practical insights and be straightforward. What you have to say and how you say it are equally important.

If you feel comfortable using Exhibit 8 to prepare the next three years of financial statements and to demonstrate The Body Shop's debt financing needs, you might be better served by scanning the next few sections on basic financial modeling and concentrating on the last section of the case (Roddick Wants to Know). From experience, however, a vast number of students have found the following exercises to be invaluable in their early understanding of financial modeling.

An Overview of Financial Forecasting

-3-UVA-F-1349

In seeking to respond to Roddick's request, you can draw on at least two classic forecasting methods and a variety of hybrids that use some of each method. The two classic forecasting methods are as follows:

T-account forecasting: This method starts with a base year of financial statements, such as last year's. Entries through double-entry bookkeeping determine how each account will change and what the resulting new balances will be. While exactly true to the mechanics of how funds flow through the firm, this method is cumbersome and may require a degree of forecast information about transactions that are unavailable to many analysts outside (and even inside) the firm.

Percentage-of-sales forecasting: This method starts with a forecast of sales and then estimates other financial statement accounts based on some presumed relationship between sales and that account. While simple to execute, this technique is easily misused. For instance, some naive analysts may assume that operational capacity can increase in fractional amounts parallel to increases in sales, but can an airline company really buy only half a jumbo jet? Operational capacity usually increases in lump amounts, rather than in smooth amounts. The lesson here is that when you use this technique, you should scrutinize the percentage-of-sales relationships to make sure they are reasonable.

The most widely used approach is a hybrid of these two. For instance, T-accounts are used to estimate shareholders' equity and fixed assets. Percentage-of-sales is used to estimate income statements, current assets, and current liabilities, because these latter items may credibly vary with sales. Other items will vary as a percentage of accounts other than sales. Tax expense will usually be a percentage of pretax income, while dividends will vary with after-tax income, and depreciation will usually vary with gross fixed assets.

A Pencil-and-Paper Forecast

As an introduction to financial modeling, we will walk through the construction of a forecasted income statement and balance sheet, first with pencil and paper (just visualizing the steps may suffice) and later with a spreadsheet. In either case, you are preparing a pro forma, or projected, income statement and balance sheet for The Body Shop for 2002 (income statement for the entire year and balance sheet for year-end). All values should be in British pounds (GBP). Use the following assumptions as a guide:

Sales:GBP422.733 million (a 13% increase over 2001)

Cost of goods sold(COGS): 38%ofsales

Operating expenses:50% of sales

Interest expense:6% of debt (about the current interest rate)

Profit before tax: Tax:Sales ? COGS ? Depreciation and amortization (D&A) ? Interest

Tax:30% of profit before tax (the going corporate tax rate in Britain)

Dividends:GBP10.9 million (same as the three previous years)

Earnings retained:Profit after tax ? Dividends

Current assets:32% of sales

Fixed assets:GBP110.6 million

Total assets:Current assets ? Fixed assets

Current liabilities:28% of sales T

Debt:Total assets - Current liabilities + Shareholders' equity

Common equity:GBP121.6 million + Retentions to earnings

Income statement: Begin with sales, and use it to estimate COGS and operating expenses. For the time being, leave interest expense at zero, since we do not yet know the amount of debt. Estimate profit before tax, tax expense, profit after tax, dividends, and earnings retained.

Balance sheet: Estimate current assets as 32% of sales and add that to GBP110.6 million to get an estimate for total assets. Next, estimate current liabilities as 28% of sales and common equity. Debt becomes the "plug" figure that makes the two sides of the balance sheet balance. This amount is your estimate of the external financing needed by The Body Shop by year-end 2002. Estimate the plug by subtracting the amounts for current liabilities and common equity from total assets.

Iterate: Initially, you entered an interest expense of zero on the income statement, but this cannot be correct if debt is outstanding or if excess cash is invested in interest-earning instruments. This is a classic finance problem arising from the income statement and balance sheet's dependence on each other. Interest expense is necessary to estimate retained earnings, which is necessary to estimate debt. Let's call this the problem of circularity. The way to deal with this problem is to insert your best estimate of interest expense in the income statement (using 6% debt), then to re-estimate the plug figure, then re-estimate interest expense, and so on. By iterating through the two statements five or six times, you will come to estimates of interest expense and debt that do not change very much. Stop iterating when changes become small.

A Spreadsheet Model Forecast

Fortunately, the tedium of iterating can be eliminated with the aid of a computer and spreadsheet software such as Microsoft Excel. The specific commands reviewed here relate to

Excel 2000. (These commands will appear in table form within the text.) The adaptation to other spreadsheet programs should be straightforward. Now, try the same forecast for The Body Shop using a computer spreadsheet.

Setup: Start with a clean spreadsheet. Set the recalculation mode to MANUAL so that the model will iterate only when you press CALC (F9). Also, set the number of iterations to 1 so that you will be able to see Excel re-estimate the plug figure and interest expense. You can set the number of iterations higher (Excel's default is 100), but Excel will converge on a solution after five or six iterations, so a setting of 1 is best to see the iterations in action. The commands are listed in Table 1.

Saving: As you develop your model, be sure to save it every five minutes or so, just in

Format: Use the format in Exhibit 1 as a guide to plan your worksheet. To facilitate sensitivity analysis, it is generally best to place the Input Data at the top of the worksheet. Next, develop the income statement just as you did on pencil and paper. Use Exhibit 2 as your guide. Be sure to tie the cells to the proper percentage rate in the Input Data section. The first time through, enter 0 for the interest. (This is very important for the iteration to work properly.) We will return to it later.

Now do the balance sheet. Again, be sure to tie the balance sheet together by formulas. With the basic format laid out, go back and enter the formula to calculate interest as "Interest rate Debt." Press the (F9) key, and you should see the worksheet change. You should be able to press the (F9) key several more times until the numbers stop changing, which means the model has converged to a solution. You should have interest as exactly 6% of long-term liabilities and a balance sheet that balances.

Note: Changing your iterations setting, combined with the circularity of the debt plug and interest expense (later we'll add the circularity of data tables), can lead to some confusing situations. It is easy to forget where you have your iterations set (more data tables lead to more circularity). When comparing your work to someone else's, be sure that both of you have the same iterations setting and have hit (F9) the same number of times (be sure you either have no data tables or the same data tables). Your worksheet should now look like Exhibit 3.

Projecting Farther

So far, you have managed to project The Body Shop's financial statements through 2002. Now, extend your projection to years 2003 and 2004. See Table 3 for Excel commands. A simple way is to copy your model for two additional years. Before copying the formulas from column B to columns C and D, make sure that any references to your Input Data (cells B3 through B12) are absolute references as opposed to relative references. An absolute reference means that when you copy cells B16 through B35 to other parts of your spreadsheet, the cells are still linked back to the originals (i.e., B5). Otherwise, the program assumes that the cells should be linked to new cells, such as C5. To make a reference absolute, put in dollar signs?$B$3, instead of B3.

Note that you will have to change the equity formula for 2003 and 2004. For 2003, make the formula equal to 2002's equity plus 2003's additions to retained earnings. In addition, you should make sales grow by compounding. To do that, take 2002's sales 2003's expected sales growth rate (say 13%). As you enter those changes, you should see the effect ripple through your model.

When debt is negative

Now modify the model to deal with the situation where the plug for debt is negative? this can happen routinely for firms with seasonal or cyclical sales patterns. Negative debt can be interpreted as excess cash. However, this is an odd way to show cash. A nonfinancial manager (like Anita Roddick) might not appreciate this type of presentation. The solution is to add a line for excess cash on the assets' side of the balance sheet and then to set up three new lines below the last entry in the balance sheet.

Explore Sensitivities

After your model replicates the exhibit, you are ready to conduct a sensitivity analysis on the pro forma years by seeing how variations in the forecast assumptions will affect the financing requirements. A financial analyst might want to try the following variations, or more than one in combination.

?Suppose sales in 2002 will be GBP500 million.

?Suppose COGS runs at 45% of sales.

?Suppose dividends are increased to 60% of net income.

? Suppose The Body Shop must double its manufacturing capacity by adding a new GBP100-million facility in 2002.

?Assume inventories run higher than expected (model this by increasing current assets to 40% of sales).

?Assume that accounts receivable collections improve so that current assets run at 28% of sales.

?Assume that operating expenses increase faster than sales.

What happens to the plug value (i.e., debt) under these different circumstances? In general, which assumptions in the Input Data section of your spreadsheet seem to have the biggest effect on future borrowing needs?

The data table is an invaluable tool for conducting a sensitivity analysis. It automatically calculates debt, or whatever else you want it to focus on, as it varies across different values for a particular assumption?for instance, growth rates. In Excel, you can enter the data table using a two-step process illustrated in the following examples. Suppose you want to estimate The Body Shop's debt required and excess cash generated at COGS/SALES ratios of 0.35, 0.38, 0.40, 0.42, 0.44, 0.45, and 0.48.

The additional circularity brought about by data tables can lead to some confusing results. To avoid this, be sure at this point to set the number of iterations to at least 10. The result should look like Exhibit 7.

The data table in Exhibit 7 reveals that at COGS/SALES ratios of 45%, the firm will need to borrow. This should trigger questions in your mind about what might cause that to happen, such as a price war or a surge in materials costs. Your spreadsheet format can tell you about some more sophisticated data-table formats. No financial analyst can afford to ignore that valuable tool. Armed with that tool, it is easy to go back and try the variations in other input assumptions listed previously.

Note: Remember that data tables add more calculations that need to be iterated in your worksheet. When comparing your work to that of a fellow student, make certain that your iterations are set the same and that you have roughly the same data tables in your files.

Roddick Wants to Know

Now that you have completed a simplified forecast, generate forecast based on the full range of accounts as actually reported by The Body Shop in 2001. Exhibit 8 presents the results for the past three years. Please forecast all of the accounts individually for the next three years. You will see many familiar accounts, as well as some unusual accounts like minority interests.

For most accounts, you should extrapolate by using the same percentage of sales borne out by the preceding years' experience. You might use an average of the three historical years. You might want to use only the most recent year, or if you notice a significant upward or downward trend in an account, try growing or shrinking the percentage in the future years, according to your judgment. Whatever assumptions you decide upon, you should again isolate them at the top of your worksheet, so you can easily change an assumption and then have it flow through your worksheet. Additionally, this is very important for calculating sensitivities later, as you want to be able to point to one cell as the Column Input Cell in a data table.

Make overdrafts the plug figure and base interest expense (at 6%) on the overdrafts, current portion of long-term debt, and long-term liabilities. If you skipped to this section without doing the exercise above, you may differ from your fellow students in your treatment of the case where debt is negative.

Make your own assumptions regarding sales growth. Make other assumptions as needed. Be prepared to report to Roddick your answers to these questions.

1.How did you derive your forecast? Why did you choose the base case assumptions that you did?

2.Based on your pro forma projections, how much additional financing will The Body Shop need during this period?

3. What are the three or four most important assumptions or key drivers in this forecast? What is the effect on the financing need of varying each of these assumptions up or down from the base case? Intuitively, why are these assumptions so important?

4. Why are your findings relevant to a general manager like Roddick? What are the implications of these findings for her? What action should she take based on your analysis?

In discussing your analysis with Roddick, do not permit yourself to get mired in forecast technicalities or financial jargon. Focus your comments on your results. State them as simply and intuitively as you can. Do not be satisfied with simply presenting results. Link your findings to recommendations, such as key factors to manage, opportunities to enhance results, and issues warranting careful analysis. Remember that Roddick plainly admits she finds finance boring. Whenever possible, try to express your analysis in terms that she finds interesting, including people, customers, quality of natural products, and the health and dynamism of her business.

Define the following measures of investment risk: style (i) variance of return [1] (ii) downside semi-variance of return [1] (iii) shortfall probability [1] (iv) Value at Risk. [1] [Total 4] Adam, Barbara and Charlie are all offered the choice of investing their entire portfolio in either a risk-free asset or a risky asset. The risk-free asset offers a return of 0% po, whereas the returns on the risky asset are uniformly distributed over the range -5% to +10% po. Assuming that each individual makes their investment choice in order to minimise their expected shortfall, and that they have benchmark returns of -2%, 0% and +2% pa respectively, who will choose which investment? Comment briefly on your answer. (i) Define 'shortfall probability' for a continuous random variable. (ii) An investor holds an asset that produces a random rate of return, R , over the course of a year. Calculate the shortfall probability using a benchmark rate of return of 1%, assuming: (a) R follows a lognormal distribution with / =5% and of =(5%) (b) R follows an exponential distribution with a mean return of 5%. (iii) Explain with the aid of a simple numerical example the main limitation of the shortfall probability as a basis for making investment decisions. 4 Consider a zero-coupon corporate bond that promises to pay a return of 10% next period. tyle Suppose that there is a 10% chance that the issuing company will default on the bond payment, in which case there is an equal chance of receiving a return of either 5% or 0%. (i) Calculate values for the following measures of investment risk: (a) downside semi-variance (b) shortfall probability based on the risk-free rate of return of 6%% (c) the expected shortfall below the risk-free return conditional on a shortfall occurring. [5] (ii] Discuss the usefulness of downside semi-variance as a measure of investment risk for an investor. [3] [Total 8]Exhibit 8 THE BODY SHOP INTERNATIONAL PLC 2001: AN INTRODUCTION TO FINANCIAL MODELING Historical Financial Statements (GBP in millions) Fiscal Year Ended February 28 1900 1900 2000 2000 2001 2001 (GBP) (% sales) (GBP) (% sales) (GBP) (% sales) Income Statement Turnover 303.7 100.0 330.1 1 00 0 374.1 1 00.0 Cost of sales 127.7 42.0 130.9 39.7 149.0 398 Gross profit 176.0 58.0 199.2 603 225.1 60.2 Operating expenses: excluding exceptional costs 151.4 19.9 166.2 195.7 52 3 exceptional costs 4.5 1.5 11.2 Restructuring costs 16.6 5.5 2.7 1.0 Net interest expense 0.1 0.0 15 05 4.4 1.2 Profit before tax 3.4 1.1 28 8 8.7 12.8 Tax expense 3.0 2.6 3.2 3.5 Profit (loss) after tax (4.6) (1.5) 5.6 25 Ordinary dividends 10.9 3.6 109 33 10.9 2.9 Profit (loss) retained (15.5 (5.1) 7.5 23 1.6) Exceptional costs in 2001 included redundancy costs ($4.6 million), costs of supply chain development ($2.4 million) and impairment of fixed assets and goodwill ($4.2 million). The exceptional costs of $4.5 million in 1999 were associated with closing unprofitable shops and an impairment review of the remaining shops in the United States. Restructuring costs in 2001 and 2000 relate to the sale of manufacturing plants in Littlehampton, England, and to associated reorganization costs. Restructuring costs in 1999 arose from the realignment of the management structure of the business in the United States and the United Kingdom.Historical Financial Statements (GBP in millions) Fiscal Year Ended February 28 1900 2000 2000 2001 2001 Balance Sheet (GBP) [(% sales) (GBP) (% sales) (GBP) (% sales) Assets Cash 34.0 11.2 19.2 5.8 13.7 3.7 Accounts receivable 27.8 9.2 30 3 9.2 303 Inventories 38.6 12.7 44.7 13.5 513 13.7 Other current assets 12.5 4.1 15.6 4.7 17.5 1.7 Net fixed assets 37.8 28.9 104 7 31.7 110.6 29 6 Other assets 0.0 67 Total assets 200.7 56.1 220 5 663 230.1 61.5 Liabilities and equity Accounts payable 13.0 4.3 20.5 6.2 10.7 2.9 Taxes payable 11.3 3.7 11.7 3.5 7.1 1.9 Accruals 10.8 3.6 15.6 4.7 115 3.1 Overdrafts 0.0 0.0 0.7 0.2 Other current liabilities 21.6 7.1 133 4.0 16.9 Long-term liabilities 28.0 9.2 36.7 11.1 61.2 16.4 Other liabilities 1.7 0.6 1.0 D.4 0.1 Shareholders' equity 114.3 17.6 121.4 36.8 121.6 32 5 Total liabilities and equity 200.7 56.1 220 5 66.8 230.1 61.5 Other assets in 2001 and 2000 represented receivables related to the sale of the company's Littlehampton manufacturing plant. *Other liabilities included mostly deferred taxes