ACCT

VOCUSGROUPspreadsheet1-2.xls

Home >Business & Finance homework help >Accounting homework help >ACCT

Valuation

							Documentation
Scott Corporation							This is an area where we can put documentation.
DCF VALUATION
Years ended 30 June
	2009A	2010F	2011F	2012F	2013F	2014F
	$'000	$'000	$'000	$'000	$'000	$'000
Free cash flow (FCF)
Discount factor
Discounted FCF
PV of FCF
Continuing Value
Enterprise value
OI t+1
g	Enter g
RINOA
Economic Profit t+1
							Notes Area
Scott Corporation							This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.
ECONOMIC PROFIT VALUATION
Years ended 30 June
	2009A	2010F	2011F	2012F	2013F	2014F
	$'000	$'000	$'000	$'000	$'000	$'000
Economic profit
Discount factor
PV of economic profit
Continuing value
NOA
Enterprise value
Working Area
This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on.

Key Value Drivers

Documentation

Scott Corporation

The blue text represents hardwired calculation that should drop out upon entering appropriate data.

KEY VALUE DRIVERS

Years ended 30 June

2005A

2006A

2007A

2008A

2009A

2010F

2011F

2012F

2013F

2014F

2015F

Sales growth (%)

PM (%)

ATO (times)

0.00

$'000

Sales

0.0

NOA

FCF

Economic Profit

RNOA

WACC

Enter WACC

Enter g

Working Area

Notes Area

This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on.

This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.

Financial Statements

							Documentation
	Vocus Group						This is an area where we can put documentation.
	Statements of Movements in Equity
	Years ended 30 June
			2018	2017	2016	2015
			$'000	$'000	$'000	$'000
	Opening Balance		2,303,124	3,174,285	196,239	137,124
	Loss/profit after income tax expenses for the year		61,045	(1,464,870)	64,252	19,850
o	Other comprehensive income for the year, net of tax		(11,820)	322	9,599	291
	Total comprehensive income for the year		49,225	(1,464,548)	73,851	20,141
	Transaction with owners in their capacity as owners
	Contributions of equity, net of transaction costs		136	673,419	2,955,273	45,650
	Disposal			(161)
	Share based payments		911	6,752	2,077	917
	Arising upon business combinations				2,004
	Transfers		348
	Dividends paid			(86,623)	(55,159)	(7,593)
	Closing Balance		2,353,744	2,303,124	3,174,285	196,239
	Vocus Group						This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.
	Balance Sheets
	as at 30 June
			2018	2017	2016	2015
			$'000	$'000	$'000	$'000
	Assets
	Current Assets
of	Cash and Cash equivalents		57,914	50,194	128,629	15,170
o	Trade and other receivables		192,963	167,106	144,379	22,682
o	Prepayments		32,928	21,635
o	Inventories				12,924
o	Deferred costs		54,895	57,454
f	Available-for-sale financial assets				1,998
f	Derivative financial instruments		14,756	10,364	9,978	1,027
o	Current tax assets			6278
o	Other		11,853	14,454	35,776	3,051
	Total current assets		365,309	327,485	333,684	41,930
	Non-current assets
o	Plant and equipment		1,672,724	1,542,979	522,413	204,619
o	Intangibles		2,108,451	2,212,110	3,757,068	125,394
o	Accrued revenue		6,321	2,928
o	Deferred costs		15,521	18,352
o	Deferred tax		48,429	61,745	57,403	5,443
f	Investment in joint venture				4,934	3,708
o	Other		12,347	5,997	18,517	2,250
	Total non-current assets		3,863,793	3,844,111	4,360,335	341,414
	Total assets		4,229,102	4,171,596	4,694,019	383,344
	Liabilities
	Current liabilities
o	Trade and other payment		323,264	254,835	288,966	24,177
o	Provisions		39,604	56,974	25,020	10,127
o	Deffered revenue		52,240	62,337
f	Borrowings		11,244	13,661	13,729	1,764
f	Derivative financial instruments		1,330	5,426	3,972
o	Income tax		10,828		2,036	3,533
o	Other		796	3,517	66,841	1,277
	Total current liabilities		439,306	396,750	400,564	40,878
	Non-current liabilities
o	Provisions		32,192	32,030	11,310	2,441
o	Deffered revenue		159,925	169,123
f	Borrowings		1,047,900	1,065,816	872,382	117,959
o	Deffered tax		171,850	194,534	216,320	21,299
f	Derivative financial instruments	Derivative financial instruments	13,349	1,545	5,390
o	Other		10,836	8,674	13,768	4,528
	Total non-current liabilities	Total non-current liabilities	1,436,052	1,471,722	1,119,170	146,227
	Total liabilities		1,875,358	1,868,472	1,519,734	187,105
	Net assets		2,353,744	2,303,124	3,174,285	196,239
	Equity
	Contributed equity	Contributed equity	3,775,454	3,774,834	3,100,738	144,244
	Reserves		11,658	22,703	16,306	3,847
	Accumulated losses	Accumulated losses	(1,433,368)	(1,494,413)
	Retained profit				57,080	48,148
	Equity attributable to the owners of Vocus Communications limited				3,174,124	196,239
	Non-controlling interest				161
	Total equity		2,353,744	2,303,124	3,174,285	196,239
	Vocus Group
	Income Statements
	Years ended 30 June
			2018	2017	2016	2015
			$'000	$'000	$'000	$'000
o	Revenue		1,898,159	1,820,578	830,825	149,799
o	Share of profits of joint ventures accounted for using the equity method		0	61	925	525
o	Other gains and losses		(5,732)	(30,925)	(20,651)	677
	Expenses
o	Network and services delivery		(1,108,904)	(1,059,657)	(440,427)	(64,538)
o	Employee benefits expenses		(201,507)	(193,187)	(120,772)	(25,919)
o	Depreciation and amortisation expenses		(229,608)	(193,565)	(78,487)	(18,684)
o	Administration and other expenses		(221,600)	(201,391)	(54,783)	(7,502)
o	Impairment		0	(1,532,118)
o	Net finance costs		(40,978)	(40,905)	(24,464)	(6,073)
	Total expenses		(1,802,597)	(3,220,823)	(718,933)	(122,716)
	Profit/loss before income tax expenses		89,830	(1,431,109)	92,166	28,285
of	Income tax expenses		(28,785)	(33,761)	(27,914)	(8,435)
	Profit/loss after income tax expenses for the year attrutable to the owners of vocus group limited		61,045	(1,464,870)	64,252	19,850
	Other comprehensive income
o	Foreign currency translation		(7,933)	2,031	3,209	(217)
o	Net movement on hedging transactions, net of tax		(3,220)	(1,709)	6,623	508
f	Loss on revalution of available-for-sale financial assets		(667)		(233)
o	Other comprehensive income for the year, net of tax		(11,820)	322	9,599	291
	Total comprehensive income for the year attributable to the owners of vocus group limited		49,225	(1,464,548)	73,851	20,141
	Working Area
	This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on.

Martin: Insert currency used in your firm's financial statements and it will change the whole spreadsheet for you.

Martin: You may need to adjust the years

Restated Financial Statements

											Documentation
Vocus Group											This is an area where we can put documentation.
Restated Statements of Movements in Equity
Years ended 30 June
	2004	2005	2006	2007	2008	2009	2018	2017	2016	2015
	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000
Opening Balance							2,303,124.0	3,174,285.0	196,239.0	137,124.0
Loss/profit after income tax expenses for the year							61,045.0	(1,464,870.0)	64,252.0	19,850.0
Operating comprehensive income
Foreign currency translation							(7,933.0)	2,031.0	3,209.0	(217.0)
Net movement on hedging transactions, net of tax							(3,220.0)	(1,709.0)	6,623.0	508.0
Loss on revalution of available-for-sale financial assets							(667.0)	0.0	(233.0)	0.0
Total operating comprehensive income							(11,820.0)	322.0	9,599.0	291.0
Financial comprehensive income
Total financial comprehensive income							0.0	0.0	0.0	0.0
Total CI							49,225.0	(1,464,548.0)	73,851.0	20,141.0
Transaction with owners in their capacity as owners
Contributions of equity, net of transaction costs							136.0	673,419.0	2,955,273.0	45,650.0
Disposal							0.0	(161.0)	0.0	0.0
Share based payments							911.0	6,752.0	2,077.0	917.0
Arising upon business combinations							0.0	0.0	2,004.0	0.0
Transfers							348.0	0.0	0.0	0.0
Dividends paid							0.0	(86,623.0)	(55,159.0)	(7,593.0)
closing banlance							2,353,744.0	2,303,124.0	3,174,285.0	196,239.0
											Notes Area
											This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.
Vocus Group
Restated Statements of Financial Position
as at 30 June
	2004	2005	2006	2007	2008	2009	2018	2017	2016	2015
	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000
Operating assets
Cash and Cash equivalents							18,981.6	18,205.8	8,308.3	1,498.0
Trade and other receivables							192,963.0	167,106.0	144,379.0	22,682.0
Prepayments							32,928.0	21,635.0	0.0	0.0
Inventories							0.0	0.0	12,924.0	0.0
Deferred costs							54,895.0	57,454.0	0.0	0.0
Current tax assets							0.0	6,278.0	0.0	0.0
Other							11,853.0	14,454.0	35,776.0	3,051.0
Plant and equipment							1,672,724.0	1,542,979.0	522,413.0	204,619.0
Intangibles							2,108,451.0	2,212,110.0	3,757,068.0	125,394.0
Accrued revenue							6,321.0	2,928.0	0.0	0.0
Deferred costs							15,521.0	18,352.0	0.0	0.0
Deferred tax							48,429.0	61,745.0	57,403.0	5,443.0
Other							12,347.0	5,997.0	18,517.0	2,250.0
Total operating asstes							4,175,413.6	4,129,243.8	4,556,788.3	364,937.0
Operating liabilities
Trade and other payment							323,264.0	254,835.0	288,966.0	24,177.0
Provisions							39,604.0	56,974.0	25,020.0	10,127.0
Deffered revenue							52,240.0	62,337.0	0.0	0.0
Income tax							10,828.0	0.0	2,036.0	3,533.0
Other							796.0	3,517.0	66,841.0	1,277.0
Provisions							32,192.0	32,030.0	11,310.0	2,441.0
Deffered revenue							159,925.0	169,123.0	0.0	0.0
Deffered tax							171,850.0	194,534.0	216,320.0	21,299.0
Other							10,836.0	8,674.0	13,768.0	4,528.0
Total operating liabilities							801,535.0	782,024.0	624,261.0	67,382.0
Net operating asstes(NOA)							3,373,878.6	3,347,219.8	3,932,527.3	297,555.0
Finance Obligation
Borrowings							11,244.0	13,661.0	13,729.0	1,764.0
Derivative financial instruments							1,330.0	5,426.0	3,972.0	0.0
Borrowings							1,047,900.0	1,065,816.0	872,382.0	117,959.0
Derivative financial instruments							13,349.0	1,545.0	5,390.0	0.0
Total Financial Obligation							1,073,823.0	1,086,448.0	895,473.0	119,723.0
Financial Assets
Cash and Cash equivalents							38,932.4	31,988.2	120,320.8	13,672.0
Available-for-sale financial assets							0.0	0.0	1,998.0	0.0
Derivative financial instruments							14,756.0	10,364.0	9,978.0	1,027.0
Investment in joint venture							0.0	0.0	4,934.0	3,708.0
Total financial Assets							53,688.4	42,352.2	137,230.8	18,407.0
Net financial obligations(NFO)							1,020,134.6	1,044,095.8	758,242.3	101,316.0
Equity
Contributed equity							3775454	3,774,834.0	3,100,738.0	144,244.0
Reserves							11,658.0	22,703.0	16,306.0	3,847.0
Accumulated losses							(1,433,368.0)	(1,494,413.0)	0.0	0.0
Retained profit							0.0	0.0	57,080.0	48,148.0
Equity attributable to the owners of Vocus Communications limited							0.0	0.0	3,174,124.0	196,239.0
Non-controlling interest							0.0	0.0	161.0	0.0
Total equity							2,353,744.0	2,303,124.0	3,174,285.0	196,239.0
Total NFO+Equity							3,373,878.6	3,347,219.8	3,932,527.3	297,555.0
Vocus Group
Restated Statements of Financial Performance
Years ended 30 June
	2004	2005	2006	2007	2008	2009	2018	2017	2016	2015
	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000	$'000
Operating Income
Revenue							1,898,159.0	1,820,578.0	830,825.0	149,799.0
Share of profits of joint ventures accounted for using the equity method							0.0	61.0	925.0	525.0
Other gains and losses							(5,732.0)	(30,925.0)	(20,651.0)	677.0
Total operating income							1,892,427.0	1,789,714.0	811,099.0	151,001.0
Operating Expenses
Network and services delivery							(1,108,904.0)	(1,059,657.0)	(440,427.0)	(64,538.0)
Employee benefits expenses							(201,507.0)	(193,187.0)	(120,772.0)	(25,919.0)
Depreciation and amortisation expenses							(229,608.0)	(193,565.0)	(78,487.0)	(18,684.0)
Administration and other expenses							(221,600.0)	(201,391.0)	(54,783.0)	(7,502.0)
Impairment							0.0	(1,532,118.0)	0.0	0.0
Total Expenses							(1,761,619.0)	(3,179,918.0)	(694,469.0)	(116,643.0)
Tax Expenses
Tax Reported							(28,785.0)	(33,761.0)	(27,914.0)	(8,435.0)
Tax benefit							(12,293.4)	(12,271.5)	(7,339.2)	(1,821.9)
Net Tax Expense							(41,078.4)	(46,032.5)	(35,253.2)	(10,256.9)
Operating comprehensive Income
Foreign currency translation							(7,933.0)	2,031.0	3,209.0	(217.0)
Net movement on hedging transactions, net of tax							(3,220.0)	(1,709.0)	6,623.0	508.0
Total comprehensive Income							(11,153.0)	322.0	9,832.0	291.0
Comprehensive Operating Income after Tax(OI)							78,576.6	(1,435,914.5)	91,208.8	24,392.1
Net financial Expenses
Net finance costs							(40,978.0)	(40,905.0)	(24,464.0)	(6,073.0)
Finance income							0.0	0.0	0.0	0.0
NFE before Tax							(40,978.0)	(40,905.0)	(24,464.0)	(6,073.0)
Tax benefit							12,293.4	12,271.5	7,339.2	1,821.9
NFE before Financial CI							(28,684.6)	(28,633.5)	(17,124.8)	(4,251.1)
Financial Comprehensive Income
Loss on revalution of available-for-sale financial assets							(667.0)	0.0	(233.0)	0.0
Total financial Comprehensive Income							(667.0)	0.0	(233.0)	0.0
Net financial Expenses after Tax (NFE)							(29,351.6)	(28,633.5)	(17,357.8)	(4,251.1)
Comprehensive Net Profit after Tax(CI)							49,225.0	(1,464,548.0)	73,851.0	20,141.0
Comprehensive income (CI)							49,225.0	(1,464,548.0)	73,851.0	20,141.0
Operating profit before tax							130,808.0	(1,390,204.0)	116,630.0	34,358.0
Income tax expense							(28,785.0)	(33,761.0)	(27,914.0)	(8,435.0)
Tax Benefit*							(12,293.4)	(12,271.5)	(7,339.2)	(1,821.9)
							(41,078.4)	(46,032.5)	(35,253.2)	(10,256.9)
Other operating comprehensive income							(11,153.0)	322.0	9,832.0	291.0
Comprehensive operating income after tax (OI)							78,576.6	(1,435,914.5)	91,208.8	24,392.1
Net financial expenses
Finance costs							(40,978.0)	(40,905.0)	(24,464.0)	(6,073.0)
Interest income (see footnote to Revenue in Income Statement)							0.0	0.0	0.0	0.0
							(40,978.0)	(40,905.0)	(24,464.0)	(6,073.0)
Tax Benefit *							12,293.4	12,271.5	7,339.2	1,821.9
Other financial comprehensive income							(667.0)	0.0	(233.0)	0.0
Net financial expenses after tax (NFE)							(29,351.6)	(28,633.5)	(17,357.8)	(4,251.1)
Comprehensive income after tax (CI)							49,225.0	(1,464,548.0)	73,851.0	20,141.0
Company tax rate							30%	30%	30%	30%
Consideration of allocation of cash to operating vs Financing							2018	2017	2016	2015
Cash and Cash equivalents							$ 57,914.00	$ 50,194.00	$ 128,629.00	$ 15,170.00
Revenue							$ 1,898,159.00	$ 1,820,578.00	$ 830,825.00	$ 149,799.00
percentage of cash out of operational revenue							3.1%	2.8%	15.5%	10.1%
Allocation of cash to operating							$ 18,981.59	$ 18,205.78	$ 8,308.25	$ 1,497.99
Allocation of cash to financing							$ 38,932.41	$ 31,988.22	$ 120,320.75	$ 13,672.01
							$ 57,914.00	$ 50,194.00	$ 128,629.00	$ 15,170.00
Company Tax Rate							30%	30%	30%	30%

Ratios

						Documentation
Vocus Group						This is an area where we can put documentation.
RATIOS
Years ended 30 June
		2018	2017	2016	2015
Profitability Ratios
Gross Profit Margin	Gross profit/sales				19.26%
Net Profit Margin	Net profit after tax/sales
Return on Assets	Net profit after tax/total assets
Efficiency (or Asset Management) Ratios
Inventory Turnover Ratio	Cost of goods sold/ending inventory				189.20%
Days of Inventory	Inventory/av.daily cost of goods sold
Total Asset Turnover Ratio	Sales/total assets
Current Asset Turnover Ratio	Sales/current assets
Average Collection Period (Days Receivable)	Accounts receivable/av.daily credit sales
Liquidity Ratios
Current Ratio	Current assets/current liabilities					Notes Area
Quick Ratio 1	(Current assets - inventory - prepayments)/current liabilities				0.90%	This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.
Quick Ratio 2	(Current assets - inventory - prepayments - receivables)/current liabilities
Financial Structure Ratios
Debt/Equity Ratio	Debt/equity
Equity Ratio	Equity/total assets
Times Interest Earned	Earnings before interest & tax/interest
Market Ratios
Earnings per Share (EPS)	Net profit after tax/nos of issued ordinary shares
Dividends per Share (DPS)	Dividends/number of issued ordinary shares
Dividend Yield Ratio	Dividends per share/market price per share
Price Earnings Ratio	Market price per share/earnings per share
Net Asset Backing per Share Ratio	Net assets (owners equity)/number of shares issued
Market/Book Ratio	Market price per share/net asset backing per share				5.20%
Ratios Based on Reformulated Financial Statements
Dividend Payout Ratio	Dividends/comprehensive income
Return on Equity (ROE)	Comprehensive Income/shareholders' equity
Return on Net Operating Assets (RNOA)	Operating income after tax (OI)/net operating assets (NOA)
Net Borrowing Cost (NBC)	Net fin. expenses after tax/net financial obligations
Profit Margin (PM)	Operating income after tax (OI)/sales
Operating Liability Leverage (OLLEV)	Av.operating liabilities/av.net operating assets
Financial Leverage (FLEV)	Av.net financial obligations/av.shareholders' equity
Return on Operating Assets (ROOA)	(OI after tax + implicit interest after tax)/av.operating assets
Operating Liability Leverage Spread (OLSPREAD)	ROOA - short-term borrowing rate (after tax)
Asset Turnover (ATO)	Sales/net operating assets (NOA)
Growth in Sales	Change in sales/prior period's sales
Growth in Operating Income	Change in OI after tax/prior period's OI after tax
Growth in Net Operating Assets	Change in NOA/opening NOA
Growth in Shareholders' Equity	Change in shareholders' equity/opening shareholders' equity
Economic profit	(RNOA - cost of capital) x net operating assets (NOA)
Working Area
This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on.

Working

Working Area

Notes Area

This is a place where you can copy and paste forumlas, play with calculations and generally have fun without messing up your other worksheets.

This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag.

NPV & IRR

EXAMPLE FOR WESFARMERS
ALL FIGURES ARE EXPRESSED IN MILLIONS AUD
OPTION 1: BUNNINGS
Time period	0	1	2	3	4	5	6	7	8	9	10
Cashflow	-77	-4	5	15	20	20	20	25	25	10	13
NPV	$5.88
IRR	11.4%
Time period	0	1	2	3	4	5	6	7	8	9	10
Cashflow	-77	-4	5	15	20	20	20	25	25	10	13
Cumulative Cashflow	-77	-81	-76	-61	-41	-21	-1	24	49	59	72
Payback Period	6 years	14.6	days
		0.48	months
		0.04	years
OPTION 2: COLES
Time period	0	1	2	3	4	5	6	7	8	9	10
Cashflow	-135	-5	-1	10	15	15	20	20	20	25	13.5
NPV	-$66.61
IRR	-0.3%
Time period	0	1	2	3	4	5	6	7	8	9	10
Cashflow	-135	-5	-1	10	15	15	20	20	20	25	13.5
Cumulative Cashflow	-135	-140	-141	-131	-116	-101	-81	-61	-41	-16	-2.5
Payback Period	Investment is never paid back within 10 years