i need it in 10 hours!!!
Model Facts
| This Spreadsheet supports INSTRUCTOR 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 model intentionally incorporates circular logic. To resolve this circularity, please instruct Excel to iterate 20-30 times in recalculating the model. This is achieved by clicking on Tools/Options/Calculation, and on the iteration button. | |
| 3) As long as default spreadsheet calculation is "automatic" the impact of changing assumptions will be computed in real time. If calculation is set as "manual" you should press the F9 function key to | |
| recalculate results. To set numerical calculation settings to automatic look under tools, options, calculations menu. | |
| Copyright © 2001 by the Trustees of the University of Virginia Darden School Foundation. | |
| Revised March 7, 2006. |
Exhibit TN1
| Exhibit TN1 | |||||||||
| The Body Shop Plc 2001: | |||||||||
| Hypothetical Three-Year Forecast Worksheet, Drawing on Case Exhibit 8 | |||||||||
| Fiscal Year Ended February 28 | Assumptions | Forecast | |||||||
| 1999 | 1999 | 2000 | 2000 | 2001 | 2001 | ||||
| (GBP) | (% sales) | (GBP) | (% sales) | (GBP) | (% sales) | 2002–2004 | 2002 | 2003 | 2004 |
| Income Statement | |||||||||
| Turnover | 303.7 | 100.0 | 330.1 | 100.0 | 374.1 | 100.0 | |||
| Cost of sales | 127.7 | 42.0 | 130.9 | 39.7 | 149.0 | 39.8 | |||
| Gross profit | 176.0 | 58.0 | 199.2 | 60.3 | 225.1 | 60.2 | |||
| Operating expenses | |||||||||
| –excluding exceptional costs | 151.4 | 49.9 | 166.2 | 50.3 | 195.7 | 52.3 | |||
| –exceptional costs | 4.5 | 1.5 | 0.0 | 0.0 | 11.2 | 3.0 | |||
| Restructuring costs | 16.6 | 5.5 | 2.7 | 0.8 | 1.0 | 0.3 | |||
| Net interest expense | 0.1 | 0.0 | 1.5 | 0.5 | 4.4 | 1.2 | |||
| Profit before tax | 3.4 | 1.1 | 28.8 | 8.7 | 12.8 | 3.4 | |||
| Tax expense | 8.0 | 2.6 | 10.4 | 3.2 | 3.5 | 0.9 | |||
| Profit/(loss) after tax | (4.6) | (1.5) | 18.4 | 5.6 | 9.3 | 2.5 | |||
| Ordinary dividends | 10.9 | 3.6 | 10.9 | 3.3 | 10.9 | 2.9 | |||
| Profit/(loss) retained | (15.5) | (5.1) | 7.5 | 2.3 | (1.6) | (0.4) | |||
| Fiscal Year Ended February 28 | |||||||||
| 1999 | 1999 | 2000 | 2000 | 2001 | 2001 | ||||
| Balance Sheet | (GBP) | (% sales) | (GBP) | (% sales) | (GBP) | (% sales) | 2002 | 2003 | 2004 |
| Assets | |||||||||
| Cash | 34.0 | 11.2 | 19.2 | 5.8 | 13.7 | 3.7 | |||
| Accounts receivable | 27.8 | 9.2 | 30.3 | 9.2 | 30.3 | 8.1 | |||
| Inventories | 38.6 | 12.7 | 44.7 | 13.5 | 51.3 | 13.7 | |||
| Other current assets | 12.5 | 4.1 | 15.6 | 4.7 | 17.5 | 4.7 | |||
| Net fixed assets | 87.8 | 28.9 | 104.7 | 31.7 | 110.6 | 29.6 | |||
| Other assets | 0.0 | 0.0 | 6.0 | 1.8 | 6.7 | 1.8 | |||
| Total assets | 200.7 | 66.1 | 220.5 | 66.8 | 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 | 11.5 | 3.1 | |||
| Overdrafts | 0.0 | 0.0 | 0.3 | 0.1 | 0.7 | 0.2 | |||
| Other current liabilities | 21.6 | 7.1 | 13.3 | 4.0 | 16.9 | 4.5 | |||
| Long-term liabilities | 28.0 | 9.2 | 36.7 | 11.1 | 61.2 | 16.4 | |||
| Other liabilities | 1.7 | 0.6 | 1.0 | 0.3 | 0.4 | 0.1 | |||
| Shareholders' equity | 114.3 | 37.6 | 121.4 | 36.8 | 121.6 | 32.5 | |||
| Total liabilities and equity | 200.7 | 66.1 | 220.5 | 66.8 | 230.1 | 61.5 | |||
Exhibit TN2
| Exhibit TN1 | |||||||||||
| The Body Shop International Plc 2001: | |||||||||||
| Hypothetical Three-Year Forecast, | |||||||||||
| Drawing on Case Exhibit 8 | |||||||||||
| Fiscal Year Ended February 28 | Assumptions | Forecast | |||||||||
| Income Statement | 1999 | 2000 | 2001 | 2002–2004 | 2002 | 2003 | 2004 | ||||
| Turnover | 303.7 | 100.0% | 330.1 | 100.0% | 374.1 | 100.0% | Growth | 13.0% | 422.7 | 477.7 | 539.8 |
| Cost of sales | 127.7 | 42.0% | 130.9 | 39.7% | 149.0 | 39.8% | % sales | 40.0% | 169.1 | 191.1 | 215.9 |
| Gross profit | 176.0 | 58.0% | 199.2 | 60.3% | 225.1 | 60.2% | 253.6 | 286.6 | 323.9 | ||
| Operating expenses | |||||||||||
| –excluding exceptional costs | 151.4 | 49.9% | 166.2 | 50.3% | 195.7 | 52.3% | % sales | 52.0% | 219.8 | 248.4 | 280.7 |
| –exceptional costs | 4.5 | 1.5% | 0.0 | 0.0% | 11.2 | 3.0% | % sales | 0.0% | 0.0 | 0.0 | 0.0 |
| Restructuring costs | 16.6 | 5.5% | 2.7 | 0.8% | 1.0 | 0.3% | % sales | 0.0% | 0.0 | 0.0 | 0.0 |
| Net interest expense | 0.1 | 0.0% | 1.5 | 0.5% | 4.4 | 1.2% | % of debt | 6.0% | 5.1 | 7.8 | 10.8 |
| Profit before tax (PBT) | 3.4 | 1.1% | 28.8 | 8.7% | 12.8 | 3.4% | 28.7 | 30.5 | 32.4 | ||
| Tax expense | 8.0 | 2.6% | 10.4 | 3.2% | 3.5 | 0.9% | % of PBT | 30.0% | 8.6 | 9.1 | 9.7 |
| Profit/(loss) after tax | (4.6) | -1.5% | 18.4 | 5.6% | 9.3 | 2.5% | 20.1 | 21.3 | 22.7 | ||
| Ordinary dividends | 10.9 | 3.6% | 10.9 | 3.3% | 10.9 | 2.9% | Constant | 10.9 | 10.9 | 10.9 | 10.9 |
| Profit/(loss) retained | (15.5) | -5.1% | 7.5 | 2.3% | (1.6) | -0.4% | 9.2 | 10.4 | 11.8 | ||
| Balance Sheet | |||||||||||
| Cash | 34.0 | 11.2% | 19.2 | 5.8% | 13.7 | 3.7% | Plug, min.10mm | 3.6 | 10.0 | 10.0 | |
| Accounts receivable | 27.8 | 9.2% | 30.3 | 9.2% | 30.3 | 8.1% | % sales | 8.0% | 33.8 | 38.2 | 43.2 |
| Inventories | 38.6 | 12.7% | 44.7 | 13.5% | 51.3 | 13.7% | % sales | 13.7% | 57.9 | 65.4 | 74.0 |
| Other current assets | 12.5 | 4.1% | 15.6 | 4.7% | 17.5 | 4.7% | % sales | 4.7% | 19.9 | 22.5 | 25.4 |
| Net fixed assets | 87.8 | 28.9% | 104.7 | 31.7% | 110.6 | 29.6% | % sales | 30.0% | 126.8 | 143.3 | 161.9 |
| Other assets | 0.0 | 0.0% | 6.0 | 1.8% | 6.7 | 1.8% | % sales | 1.8% | 7.6 | 8.6 | 9.7 |
| Total assets | 200.7 | 66.1% | 220.5 | 66.8% | 230.1 | 61.5% | 249.6 | 288.0 | 324.2 | ||
| Accounts payable | 13.0 | 4.3% | 20.5 | 6.2% | 10.7 | 2.9% | % sales | 4.0% | 16.9 | 19.1 | 21.6 |
| Taxes payable | 11.3 | 3.7% | 11.7 | 3.5% | 7.1 | 1.9% | % sales | 2.0% | 8.5 | 9.6 | 10.8 |
| Accruals | 10.8 | 3.6% | 15.6 | 4.7% | 11.5 | 3.1% | % sales | 3.5% | 14.8 | 16.7 | 18.9 |
| Overdrafts | 0.0 | 0.0% | 0.3 | 0.1% | 0.7 | 0.2% | Plug | 0.0 | 20.4 | 36.0 | |
| Other current liabilities | 21.6 | 7.1% | 13.3 | 4.0% | 16.9 | 4.5% | % sales | 4.0% | 16.9 | 19.1 | 21.6 |
| Long-term liabilities | 28.0 | 9.2% | 36.7 | 11.1% | 61.2 | 16.4% | Fixed | 61.2 | 61.2 | 61.2 | |
| Other liabilities | 1.7 | 0.6% | 1.0 | 0.3% | 0.4 | 0.1% | % sales | 0.1% | 0.4 | 0.5 | 0.5 |
| Shareholders' equity | 114.3 | 37.6% | 121.4 | 36.8% | 121.6 | 32.5% | 130.8 | 141.2 | 153.0 | ||
| Total liabs. & equity | 200.7 | 66.1% | 220.5 | 66.8% | 230.1 | 61.5% | 249.5 | 287.8 | 323.6 | ||
| Trial assets | 246.0 | 278.0 | 314.2 | ||||||||
| Trial liabs. & equity | 249.5 | 267.4 | 287.6 | ||||||||
| Trial plug | (3.5) | 10.6 | 26.6 | ||||||||
| Min. cash blance | 10.0 | 10.0 | 10.0 | ||||||||
| Plug | 6.5 | 20.6 | 36.6 | ||||||||
Exhibit TN3
| Revenue Growth | 13% | ||||||
| COGS/Sales | 40% | ||||||
| Sensitivity Analysis of Overdrafts in 2004 | |||||||
| According to Variations in Growth Rate and COGS Ratio | |||||||
| Growth Rate in Revenues | |||||||
| COGS to Sales Ratio | 36.01227319245862 | 10% | 11% | 12% | 13% | 14% | 15% |
| 35% | 29.1 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| 36% | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| 37% | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| 38% | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 10.8 | |
| 39% | 15.9 | 0.0 | 0.0 | 11.8 | 16.9 | 21.9 | |
| 40% | 27.2 | 13.8 | 18.9 | 22.5 | 27.8 | 33.1 | |
| 41% | 38.5 | 24.2 | 29.5 | 33.2 | 38.7 | 44.2 | |
| 42% | 49.9 | 34.6 | 40.1 | 44.0 | 49.6 | 55.3 | |
| 43% | 61.2 | 45.0 | 50.7 | 54.7 | 60.6 | 66.4 | |
| 44% | 72.5 | 55.4 | 61.3 | 65.4 | 71.5 | 77.6 | |
| 45% | 83.8 | 65.9 | 71.9 | 76.2 | 82.4 | 88.7 | |
| 46% | 95.2 | 76.3 | 82.5 | 86.9 | 93.4 | 99.8 | |
| 47% | 106.5 | 86.7 | 93.1 | 97.6 | 104.3 | 110.9 | |
| 48% | 117.8 | 97.1 | 103.8 | 108.4 | 115.2 | 122.1 | |
| Note: bolded value is given in the "base case" of Exhibit TN2. |