Case study Excel

NEWEXCEL.xls

Home >Business & Finance homework help >Accounting homework help >Case study Excel

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.

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)	+(B6B34)-(B6B28)
	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	13%	422.7	477.7	539.8
Cost of sales	127.7	42.0	130.9	39.7	149.0	39.8	% of Sales	40%	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		52%	219.8	248.4	280.7
–exceptional costs1	4.5	1.5	0.0	0.0	11.2	3.0			0.0	0.0	0.0
Restructuring costs2	16.6	5.5	2.7	0.8	1.0	0.3			0.0	0.0	0.0
Net interest expense	0.1	0.0	1.5	0.5	4.4	1.2	% of Debt	6%	3.4	4.1	4.8
Profit before tax	3.4	1.1	28.8	8.7	12.8	3.4			30.5	34.2	38.4
Tax expense	8.0	2.6	10.4	3.2	3.5	0.9	% of Profit b4 tax	30%	9.1	10.2	11.5
Profit (loss) after tax	(4.6)	(1.5)	18.4	5.6	9.3	2.5			21.3	23.9	26.9
Ordinary dividends	10.9	3.6	10.9	3.3	10.9	2.9	Fixed		10.9	10.9	10.9
Profit (loss) retained	(15.5)	(5.1)	7.5	2.3	(1.6)	(0.4)			10.4	13.0	16.0
	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	Plug		5.1	10	10
Accounts receivable	27.8	9.2	30.3	9.2	30.3	8.1		8%	33.8	38.2	43.2
Inventories	38.6	12.7	44.7	13.5	51.3	13.7		14%	59.2	66.9	75.6
Other current assets	12.5	4.1	15.6	4.7	17.5	4.7		4.9%	20.7	23.4	26.4
Net fixed assets	87.8	28.9	104.7	31.7	110.6	29.6		30%	126.8	143.3	161.9
Other assets3	0.0	0.0	6.0	1.8	6.7	1.8		1.8%	7.6	8.6	9.7
Total assets	200.7	66.1	220.5	66.8	230.1	61.5			253.2	290.4	326.9
Liabilities and equity
Accounts payable	13.0	4.3	20.5	6.2	10.7	2.9		4.2%	17.8	20.1	22.7
Taxes payable	11.3	3.7	11.7	3.5	7.1	1.9		2%	8.5	9.6	10.8
Accruals	10.8	3.6	15.6	4.7	11.5	3.1		3.7%	15.6	17.7	20.0
Overdrafts	0.0	0.0	0.3	0.1	0.7	0.2	Plug		0.0	16.3	28.0
Other current liabilities	21.6	7.1	13.3	4.0	16.9	4.5		4.2%	17.8	20.1	22.7
Long-term liabilities	28.0	9.2	36.7	11.1	61.2	16.4	Fixed		61.2	61.2	61.2
Other liabilities4	1.7	0.6	1.0	0.3	0.4	0.1		0.1%	0.4	0.5	0.5
Shareholders' equity	114.3	37.6	121.4	36.8	121.6	32.5			132.0	145.0	161.0
Total liabilities and equity	200.7	66.1	220.5	66.8	230.1	61.5			253.2	290.4	326.9
									2002	2003	2004
							Trial assets		248.1	280.4	316.9
							Trial liability and equity		253.2	274.1	298.9
							Trial Plug		-5.1	6.3	18.0
							Cash		10.0	10.0	10.0
							Plug		4.9	16.3	28.0

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.