Please use the attached excel spread sheet to answer the 4 questions to this case: The Body Shop International PLC 2001: An Introduction to Financial Modeling

Below is a link to the full case study direction which is labeled the case 2 body shop.docx

Attached is the work out of the forecast of the case in excel

=

Attached are the 4 questions (also provided in the case)

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 this case, the student is cast in the role of adviser to Anita Roddick, the managing director of the Body Shop. The student must prepare a threeyear forecast of the firm's income statements and balance sheets. The case is intended to introduce percentageofsales forecasting and walks the student through the preparation of a simplified forecast, first using pencil and paper, then using a spreadsheet program on the personal computer. The case emphasizes the importance of being able to speak plainly about one's financial forecast and the insights that are of use to the general manager. The case presents a selfcontained exposition of forecasting, and will require two to four hours of preparation by a novice student. The following are potentially valuable complements to this case. Higgins, Robert C. \"Financial Forecasting\" in Analysis for Financial Management. 6th ed. (Burr Ridge, IL: Irwin/McGrawHill, 2001). This is one of the best concise discussions of financial forecasting, and would be especially useful with executive audiences. Current corporate finance textbooks offer presentations that link the mechanics of forecasting to the larger subject of financial planning. Two good discussions are these: Brealey, Richard A., and Stewart C. Myers, \"Financial Analysis and Planning.\" chap. 28 in Principles of Corporate Finance. 6th ed. (Burr Ridge, IL: Irwin/McGrawHill, 2000). Ross, Stephen, Westerfield, Randolph, and Jaffe, Jeffrey. \"Corporate Financial Models and LongTerm Planning.\" chap. 28 in Corporate Finance. 6th ed. (Burr Ridge, IL: Irwin/McGrawHill, 2002). Some novices will ask for more practice drills in forecasting mechanics. The following is a CDROM- based tutorial that walks students through the preparation of spreadsheet forecasting models for three companies: Bruner, Robert F., Kenneth M. Eades, and Robert S. Harris. Finance Interactive. (Burr Ridge, IL: Irwin/McGrawHill, 1997). Students welcome the selfguided aspect of this tutorial, and show quicker mastery when they use it. We provide this resource after students have received the introduction in the Body Shop case. Attached is the work out of the forecast of the case in excel Attached are the 4 questions (also provided in the case) This spreadsheet supports student analysis of the case, "The Body Shop International Plc 2001" (Case 9) Please note: 1) This is a working model. Assumptions / Inputs presented can be changed to vary the results. 2) This spreadsheet incorporates circular logical reference. To resolve this circularity, please instruct Excel to "iterate" several times in calculating the results. This is accomplished by clicking on Tools/Options/Calculation, and then checking the iteration box. 3) As long as default spreadsheet calculation is set as "automatic" impact of changing assumptions will be computed in real time. Alternatively the F9 function key may need to be invoked to recalculate results. To set the numerical calculation settings to automatic, please click on Tools/Options/Calculations menu, and check the automatic calculation option. Copyright 2001 by the Trustees of the University of Virginia Darden School Foundation. Revised, March 7, 2006. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 B C D E F G H I Exhibit 1 Input Data The Body Shop Plc 2001: Format for Developing a Spreadsheet Model SALES 422,733 COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 INCOME STATEMENT 2002 SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED BALANCE SHEET CURRENT ASSETS FIXED ASSETS TOTAL ASSETS CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH 2002 J A B C 1 2 Exhibit 2 3 The Body Shop Plc 2001: Spreadsheet Formulas to Forecast 2002 Financials: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY 422,733 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED 2002 +B3 +B4*B16 +B5*B16 +B6*B33 +B16-B17-B18-B19 +B7*B20 +B20-B21 +B8 +B22-B23 BALANCE SHEET 2002 CURRENT ASSETS FIXED ASSETS TOTAL ASSETS +B9*B16 +B11 +B28+B29 CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH +B10*B16 +B30-B32-B34 +B12+B24 +B32+B33+B34 A B C D 1 2 Exhibit 3 3 The Body Shop Plc 2001: Basic Forecasting Results for 2002 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED BALANCE SHEET 422,733 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 2002 422,733 160,639 211,367 (1,171) 51,899 15,570 36,329 10,900 25,429 2002 CURRENT ASSETS FIXED ASSETS TOTAL ASSETS 135,275 110,600 245,875 CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH 118,365 (19,520) 147,029 245,875 E F A B C 1 2 Exhibit 4 3 The Body Shop Plc 2001: Adjusting to Reflect Excess Cash 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY 422,733 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED 2002 422,733 160,639 211,367 +(B6*B34)(B6*B28) 40,706 14,247 26,459 10,900 15,559 BALANCE SHEET 2002 EXCESS CASH CURRENT ASSETS FIXED ASSETS TOTAL ASSETS =IF(B400,+B40,0) 137,159 +B33+B34+B35 TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) +B29+B30 +B33+B35 +B38B39 A B C 1 2 Exhibit 5 3 The Body Shop Plc 2001: Finished Results for 2002, Reflecting Excess Cash 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED BALANCE SHEET 422,733 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 2002 422,733 160,639 211,367 (1,171) 51,899 15,570 36,329 10,900 25,429 2002 EXCESS CASH CURRENT ASSETS FIXED ASSETS TOTAL ASSETS 19,520 135,275 110,600 265,395 CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH 118,365 0 147,029 265,395 TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) 245,875 265,395 (19,520) A B C D E F G 1 2 Exhibit 6 3 The Body Shop Plc 2001: Set up for a Forecast with Data Table 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED BALANCE SHEET 422,733 0.38 0.50 0.06 0.30 10,900 0.32 0.28 110,600 121,600 2002 422,733 160,639 211,367 (1,171) 51,899 15,570 36,329 10,900 25,429 2002 EXCESS CASH CURRENT ASSETS FIXED ASSETS TOTAL ASSETS 19,520 135,275 110,600 265,395 CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH 118,365 147,029 265,395 TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) 245,875 265,395 (19,520) Sensitivity Analysis Of Debt and Excess Cash To COGS/SALES Ratio COGS/SALES 0.35 0.38 0.40 0.42 0.44 0.45 0.48 DEBT =B34 Ex. CASH =B28 A B C D E F G 1 2 Exhibit 7 3 The Body Shop Plc 2001: Finished Forecast with Data Table 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Input Data SALES COGS/SALES OPERATING EXPENSES/SALES INTEREST RATE TAX RATE DIVIDENDS (Thousand pounds) CURR. ASSETS/SALES CURR. LIABS./SALES FIXED ASSETS STARTING EQUITY INCOME STATEMENT SALES COGS OPERATING EXPENSES INTEREST EXPENSE (INCOME) PROFIT BEFORE TAX TAX PROFIT AFTER TAX DIVIDENDS EARNINGS RETAINED BALANCE SHEET 422,733 0.38 0.50 0.06 0.30 10,900.00 0.32 0.28 110,600 121,600 2002 422,733 160,639 211,367 (1,171) 51,899 15,570 36,329 10,900 25,429 2002 EXCESS CASH CURRENT ASSETS FIXED ASSETS TOTAL ASSETS 19,520 135,275 110,600 265,395 CURRENT LIABILITIES DEBT EQUITY TOTAL LIAB. & NET WORTH 118,365 0 147,029 265,395 TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) 245,875 265,395 (19,520) Sensitivity Analysis Debt and Excess Cash By COGS/SALES COGS/SALES 0.35 0.38 0.40 0.42 0.44 0.45 0.48 DEBT +B34 0 0 0 0 0 2,102 11,369 Ex. CASH +B28 28,787 19,520 13,342 7,165 987 0 0 H Exhibit 8 The Body Shop Plc 2001: Historical Financial Statements ( in GBP millions) Fiscal Year Ended February 28 1999 (GBP) 1999 (% sales) 2000 (GBP) 2000 (% sales) 2001 (GBP) 2001 (% sales) 303.7 127.7 176.0 100.0 42.0 58.0 330.1 130.9 199.2 100.0 39.7 60.3 374.1 149.0 225.1 100.0 39.8 60.2 151.4 49.9 166.2 50.3 195.7 52.3 4.5 1.5 0.0 0.0 11.2 3.0 Restructuring costs2 Net interest expense Profit before tax Tax expense Profit (loss) after tax 16.6 0.1 3.4 8.0 (4.6) 5.5 0.0 1.1 2.6 (1.5) 2.7 1.5 28.8 10.4 18.4 0.8 0.5 8.7 3.2 5.6 1.0 4.4 12.8 3.5 9.3 0.3 1.2 3.4 0.9 2.5 Ordinary dividends Profit (loss) retained 10.9 (15.5) 3.6 (5.1) 10.9 7.5 3.3 2.3 10.9 (1.6) 2.9 (0.4) Income Statement Turnover Cost of sales Gross profit Operating expenses: -excluding exceptional costs -exceptional costs 1 Fiscal Year Ended February 28 Balance Sheet Assets Cash Accounts receivable Inventories Other current assets Net fixed assets Other assets3 Total assets Liabilities and equity Accounts payable Taxes payable Accruals Overdrafts Other current liabilities Long-term liabilities Other liabilities4 Shareholders' equity Total liabilities and equity 1999 (GBP) 1999 (% sales) 2000 (GBP) 2000 (% sales) 2001 (GBP) 2001 (% sales) 34.0 27.8 38.6 12.5 87.8 11.2 9.2 12.7 4.1 28.9 19.2 30.3 44.7 15.6 104.7 5.8 9.2 13.5 4.7 31.7 13.7 30.3 51.3 17.5 110.6 3.7 8.1 13.7 4.7 29.6 0.0 200.7 0.0 66.1 6.0 220.5 1.8 66.8 6.7 230.1 1.8 61.5 13.0 11.3 10.8 0.0 21.6 28.0 4.3 3.7 3.6 0.0 7.1 9.2 20.5 11.7 15.6 0.3 13.3 36.7 6.2 3.5 4.7 0.1 4.0 11.1 10.7 7.1 11.5 0.7 16.9 61.2 2.9 1.9 3.1 0.2 4.5 16.4 1.7 114.3 200.7 0.6 37.6 66.1 1.0 121.4 220.5 0.3 36.8 66.8 0.4 121.6 230.1 0.1 32.5 61.5 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 USA. 1 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 US and the UK. 2 3 Other assets in 2001 and 2000 represented receivables relating to the sale of the company's Littlehampton manufacturing plant. 4 Other liabilities included mostly deferred taxes