Case study summary in 5 hours !!
Model Facts
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
Page &P
Exhibit 1
Input Data Exhibit 1
The Body Shop Plc 2001: Format for Developing a Spreadsheet Model
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50
INTEREST RATE 0.06
TAX RATE 0.30
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32
CURR. LIABS./SALES 0.28
FIXED ASSETS 110,600
STARTING EQUITY 121,600
INCOME STATEMENT 2002
SALES
COGS
OPERATING EXPENSES
INTEREST EXPENSE (INCOME)
PROFIT BEFORE TAX
TAX
PROFIT AFTER TAX
DIVIDENDS
EARNINGS RETAINED
BALANCE SHEET 2002
CURRENT ASSETS
FIXED ASSETS
TOTAL ASSETS
CURRENT LIABILITIES
DEBT
EQUITY
TOTAL LIAB. & NET WORTH
Exhibit 2
Exhibit 2
The Body Shop Plc 2001:
Spreadsheet Formulas to Forecast 2002 Financials:
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50
INTEREST RATE 0.06
TAX RATE 0.30
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32
CURR. LIABS./SALES 0.28
FIXED ASSETS 110,600
STARTING EQUITY 121,600
INCOME STATEMENT 2002
SALES +B3
COGS +B4*B16
OPERATING EXPENSES +B5*B16
INTEREST EXPENSE (INCOME) +B6*B33
PROFIT BEFORE TAX +B16-B17-B18-B19
TAX +B7*B20
PROFIT AFTER TAX +B20-B21
DIVIDENDS +B8
EARNINGS RETAINED +B22-B23
BALANCE SHEET 2002
CURRENT ASSETS +B9*B16
FIXED ASSETS +B11
TOTAL ASSETS +B28+B29
CURRENT LIABILITIES +B10*B16
DEBT +B30-B32-B34
EQUITY +B12+B24
TOTAL LIAB. & NET WORTH +B32+B33+B34
Exhibit 3
Exhibit 3
The Body Shop Plc 2001:
Basic Forecasting Results for 2002
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50
INTEREST RATE 0.06
TAX RATE 0.30
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32
CURR. LIABS./SALES 0.28
FIXED ASSETS 110,600
STARTING EQUITY 121,600
INCOME STATEMENT 2002
SALES 422,733
COGS 160,639
OPERATING EXPENSES 211,367
INTEREST EXPENSE (INCOME) (1,171)
PROFIT BEFORE TAX 51,899
TAX 15,570
PROFIT AFTER TAX 36,329
DIVIDENDS 10,900
EARNINGS RETAINED 25,429
BALANCE SHEET 2002
CURRENT ASSETS 135,275
FIXED ASSETS 110,600
TOTAL ASSETS 245,875
CURRENT LIABILITIES 118,365
DEBT (19,520)
EQUITY 147,029
TOTAL LIAB. & NET WORTH 245,875
Exhibit 4
Exhibit 4
The Body Shop Plc 2001:
Adjusting to Reflect Excess Cash
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50
INTEREST RATE 0.06
TAX RATE 0.30
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32
CURR. LIABS./SALES 0.28
FIXED ASSETS 110,600
STARTING EQUITY 121,600
INCOME STATEMENT 2002
SALES 422,733
COGS 160,639
OPERATING EXPENSES 211,367
INTEREST EXPENSE (INCOME) +(B6*B34)-(B6*B28)
PROFIT BEFORE TAX 40,706
TAX 14,247
PROFIT AFTER TAX 26,459
DIVIDENDS 10,900
EARNINGS RETAINED 15,559
BALANCE SHEET 2002
EXCESS CASH =IF(B40<0,-B40,0)
CURRENT ASSETS 135,275
FIXED ASSETS 110,600
TOTAL ASSETS +B29+B30+B28
CURRENT LIABILITIES 118,365
DEBT =IF(B40>0,+B40,0)
EQUITY 137,159
TOTAL LIAB. & NET WORTH +B33+B34+B35
TRIAL ASSETS +B29+B30
TRIAL LIABILITIES AND EQUITY +B33+B35
PLUG: DEBT (EXCESS CASH) +B38-B39
Exhibit 5
Exhibit 5
The Body Shop Plc 2001:
Finished Results for 2002, Reflecting Excess Cash
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50
INTEREST RATE 0.06
TAX RATE 0.30
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32
CURR. LIABS./SALES 0.28
FIXED ASSETS 110,600
STARTING EQUITY 121,600
INCOME STATEMENT 2002
SALES 422,733
COGS 160,639
OPERATING EXPENSES 211,367
INTEREST EXPENSE (INCOME) (1,171)
PROFIT BEFORE TAX 51,899
TAX 15,570
PROFIT AFTER TAX 36,329
DIVIDENDS 10,900
EARNINGS RETAINED 25,429
BALANCE SHEET 2002
EXCESS CASH 19,520
CURRENT ASSETS 135,275
FIXED ASSETS 110,600
TOTAL ASSETS 265,395
CURRENT LIABILITIES 118,365
DEBT 0
EQUITY 147,029
TOTAL LIAB. & NET WORTH 265,395
TRIAL ASSETS 245,875
TRIAL LIABILITIES AND EQUITY 265,395
PLUG: DEBT (EXCESS CASH) (19,520)
Exhibit 6
Exhibit 6
The Body Shop Plc 2001:
Set up for a Forecast with Data Table
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50 Sensitivity Analysis
INTEREST RATE 0.06 Of Debt and Excess Cash
TAX RATE 0.30 To COGS/SALES Ratio
DIVIDENDS (Thousand pounds) 10,900
CURR. ASSETS/SALES 0.32 COGS/SALES DEBT Ex. CASH
CURR. LIABS./SALES 0.28 =B34 =B28
FIXED ASSETS 110,600 0.35
STARTING EQUITY 121,600 0.38
0.40
INCOME STATEMENT 2002 0.42
0.44
SALES 422,733 0.45
COGS 160,639 0.48
OPERATING EXPENSES 211,367
INTEREST EXPENSE (INCOME) (1,171)
PROFIT BEFORE TAX 51,899
TAX 15,570
PROFIT AFTER TAX 36,329
DIVIDENDS 10,900
EARNINGS RETAINED 25,429
BALANCE SHEET 2002
EXCESS CASH 19,520
CURRENT ASSETS 135,275
FIXED ASSETS 110,600
TOTAL ASSETS 265,395
CURRENT LIABILITIES 118,365
DEBT – 0
EQUITY 147,029
TOTAL LIAB. & NET WORTH 265,395
TRIAL ASSETS 245,875
TRIAL LIABILITIES AND EQUITY 265,395
PLUG: DEBT (EXCESS CASH) (19,520)
Exhibit 7
Exhibit 7
The Body Shop Plc 2001:
Finished Forecast with Data Table
Input Data
SALES 422,733
COGS/SALES 0.38
OPERATING EXPENSES/SALES 0.50 Sensitivity Analysis
INTEREST RATE 0.06 Debt and Excess Cash
TAX RATE 0.30 By COGS/SALES
DIVIDENDS (Thousand pounds) 10,900.00
CURR. ASSETS/SALES 0.32 COGS/SALES DEBT Ex. CASH
CURR. LIABS./SALES 0.28 +B34 +B28
FIXED ASSETS 110,600 0.35 0 28,787
STARTING EQUITY 121,600 0.38 0 19,520
0.40 0 13,342
INCOME STATEMENT 2002 0.42 0 7,165
0.44 0 987
SALES 422,733 0.45 2,102 0
COGS 160,639 0.48 11,369 0
OPERATING EXPENSES 211,367
INTEREST EXPENSE (INCOME) (1,171)
PROFIT BEFORE TAX 51,899
TAX 15,570
PROFIT AFTER TAX 36,329
DIVIDENDS 10,900
EARNINGS RETAINED 25,429
BALANCE SHEET 2002
EXCESS CASH 19,520
CURRENT ASSETS 135,275
FIXED ASSETS 110,600
TOTAL ASSETS 265,395
CURRENT LIABILITIES 118,365
DEBT 0
EQUITY 147,029
TOTAL LIAB. & NET WORTH 265,395
TRIAL ASSETS 245,875
TRIAL LIABILITIES AND EQUITY 265,395
PLUG: DEBT (EXCESS CASH) (19,520)
Exhibit 8
Exhibit 8
The Body Shop Plc 2001:
Historical Financial Statements ( in GBP millions)
Fiscal Year Ended February 28 Assumptions 2002-2004 Forecast
1999 1999 2000 2000 2001 2001 2002 2003 2004
(GBP) (% sales) (GBP) (% sales) (GBP) (% sales)
Income Statement
Turnover 303.7 100.0 330.1 100.0 374.1 100.0 Growth rate X%
Cost of sales 127.7 42.0 130.9 39.7 149.0 39.8 % of Sales X%
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 costs1 4.5 1.5 0.0 0.0 11.2 3.0
Restructuring costs2 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 % of Debt
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 % of Profit b4 tax
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 2002 2003 2004
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 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 assets3 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 liabilities4 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
1 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. 2 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. 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.
