5-year forecasted financial statements in Excel Workbook

ross__stores_financials.xlsx

Home >Business & Finance homework help >Accounting homework help > 5-year forecasted financial statements in Excel Workbook

Ross Stores Financials

	Forecasted Statements - Ross Stores Company
	$ mm, except per share
	Company Name	Ross Stores
	Ticker	ROST
	Share price	65.39
	(in Millions)
x	INCOME STATEMENT
	Fiscal year	2013.01.31 A	2014.01.31 A	2015.01.31 A	2016.01.31 A	2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Revenue	9,721	10,230	11,042	11,940	12,726	13,564	14,457	15,409	16,424
	COR	7,011	7,361	7,938	8,577	9,157	9,754	10,394	11,079	11,811
	Gross Profit	2,710	2,869	3,104	3,363	3,570	3,811	4,063	4,331	4,613
	Selling, marketning and advertisement	1,438	1,526	1,615	1,739	1,874	1,995	2,119	2,260	2,413
	Goodwill impairment charges	0	0	0	0	0	0	0	0	0
	Business realingment charges	0	0	0	0	0	0	0	0	0
	Operating income	1,272	1,343	1,488	1,624	1,696	1,815	1,944	2,071	2,201
	Other expense	0	0	0	0	0	0	0	0	0
	EBIT	1,272	1,343	1,488	1,624	1,696	1,815	1,944	2,071	2,201
	Interest expense	7	0	3	13	8	8	9	9	10
	Pretax profit (EBT)	1,265	1,343	1,485	1,612	1,688	1,807	1,935	2,062	2,191
	Provision for income taxes	478	506	561	591	637	682	730	778	827
	Net income	787	837	925	1,021	1,051	1,125	1,205	1,284	1,364
	EBITDA reconciliation
	Depreciation	185	206	233	275	257	288	320	353	386
	Amortization	0	0	0	0	0	0	0	0	0
	EBITDA	972	1,043	1,158	1,295	1,308	1,414	1,525	1,637	1,750
	Growth rates & margins
	Revenue growth		5.24%	7.93%	8.14%	6.58%	6.58%	6.58%	6.58%	6.58%
	COR % of Revenue	72.13%	71.95%	71.89%	71.83%	71.95%	71.91%	71.90%	71.90%	71.91%
	Selling, marketing & advertisement margin	14.79%	14.92%	14.63%	14.56%	14.73%	14.71%	14.66%	14.66%	14.69%
	Goodwill and other intangible assets margin	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
	Business realingment charges margin	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
	Tax rate	37.79%	37.68%	37.74%	36.67%	37.73%	37.73%	37.73%	37.73%	37.73%
	Interest expense
	Net income per share:
	Basic common stock	2.53	2.24	1.97	1.80
	Diluted common stock	2.51	2.21	1.94	1.77
	Basic class B stock	0.00	0.00	0.00	0.00
	Diluted class B stock	0.00	0.00	0.00	0.00
	Weighted average shares outstanding
	Basic common stock	438,000	425,761	413,553	403,034
	Basic class B stock	0	0	0	0
	Diluted	445,570	431,610	418,077	406,405
	Dividends paid on common stock	131	110	168	192
	Dividend per share common stock	0.00	0.00	0.00	0.00
	Divdends paid on class B stock	0	0	0	0
	Dividend per share of class B	0.00	0.00	0.00	0.00
x	BALANCE SHEET
	Fiscal year	2013.01.31 A	2014.01.31 A	2015.01.31 A	2016.01.31 A	2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Current assets
	Cash & equivalents	647	423	697	762	1,126	1,168	1,356	1,587	1,900
	Short-term investments	1	12	1	2	1	2	2	2	2
	Accounts receivable, net	60	63	73	74	81	87	93	99	105
	Inventories	1,209	1,257	1,373	1,419	1,549	1,650	1,759	1,874	1,998
	Current deferred income tax			0	0	0	0	0	0	0
	Prepaid expenses and other	74	81	107	116	124	132	140	150	160
	Total current assets			2,250	2,372	2,882	3,039	3,349	3,712	4,165
	Non-current assets
	Property, plant & equipment, net	1,493	1,875	2,274	2,343	2,625	2,914	3,212	3,517	3,831
	Goodwill	3	3	0	0	0	0	0	0	0
	Intangibles Net			0	0	0	0	0	0	0
	Long Term Investments	4	4	3	1	3	2	3	3	3
	Other Long Term Assests , Total	139	148	161	153	174	179	194	206	220
	Total non-current assets			2,438	2,497	2,801	3,095	3,409	3,726	4,054
	Total assets			4,687	4,869	5,683	6,134	6,759	7,437	8,219
	Current liabilities:
	Accounts payable	808	779	1,001	946	1,082	1,114	1,208	1,276	1,366
	Accrued Expenses	562	595	641	657	721	758	813	863	922
	Accrued income taxes	0	0	0	0	0	0	0	0	0
	Short-term debt		0	0	0	0	0	0	0	0
	Other Current Liabilities, net	54	18	17	0	0	0	0	0	0
	Total current liabilities	1,423	1,393	1,659	1,603	1,803	1,872	2,020	2,140	2,288
	Non-current liabilities
	Long-term debt	150	150	396	396	439	459	494	524	560
	Other long-term liabilities	0	0	280	268	601	627	672	719	766
	Deferred Income Tax	331	346	74	130	110	132	134	146	153
	Total non-current liabilities	481	496	749	794	1,150	1,218	1,299	1,390	1,479
	Total liabilities	1,904	1,890	2,408	2,397	2,953	3,090	3,320	3,529	3,768
	Common stock, shares issued	4	4	4	4	4	4	4	4	4
	Class B common stock, shares issued:			0	0	0	0	0	0	0
	Additional paid in capital	864	933	1,014	1,122	1,227	1,339	1,464	1,595	1,738
	Retained earnings	989	1,191	1,422	1,575	1,775	2,023	2,338	2,722	3,169
	Treasury common stock shares Svetislav Pejcic: Svetislav Pejcic: At cost: 143,124,384 in 2015 138,856,786 in 2014	(92)	(122)	(161)	(230)	(275)	(321)	(367)	(413)	(459)
	Other Equity , Total	1	0	0	0	0	(0)	(0)	(0)	(0)
	Total Ross Stores stockholders' equity	1,767	2,007	2,279	2,472	2,730	3,044	3,438	3,908	4,451
	Noncontrolling interest in subsidiaries	0	0	0	0	0	0	0	0	0
	Total stockholders' equity	1,767	2,007	2,279	2,472	2,730	3,044	3,438	3,908	4,451
	Total liabilities and stockholders' equity	3,671	3,897	4,687	4,869	5,683	6,134	6,758	7,437	8,219
				0	0	0	0	0	0	0
	Balance check			0	0	0	0	0	0	0
x	ASSETS
	Fiscal year	2013.01.31 A	2014.01.31 A	2015.01.31 A	2016.01.31 A	2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Short-term investments
	Beginning of period				1	0	1	2	2	2
	Increases / (decreases)				(1)	1	0	(0)	0	0
	End of period			1	0	1	2	2	2	2
	STI as % of sales			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Accounts receivable, net
	Beginning of period		60	63	73	74	81	87	93	99
	Increases / (decreases)		3	11	0	8	5	6	6	6
	End of period	60	63	73	74	81	87	93	99	105
	AR as % of sales		0.6%	0.7%	0.6%	0.6%	0.6%	0.6%	0.6%	0.6%
	Inventories
	Beginning of period		1,209	1,257	1,373	1,419	1,549	1,650	1,759	1,874
	Increases / (decreases)		48	116	46	130	101	108	116	124
	End of period	1,209	1,257	1,373	1,419	1,549	1,650	1,759	1,874	1,998
	Inv as % of COR		17.1%	17.3%	16.5%	16.9%	16.9%	16.9%	16.9%	16.9%
	Current deferred income tax
	Beginning of period		0	0	0	0	0	0	0	0
	Increases / (decreases)		0	0	0	0	0	0	0	0
	End of period		0	0	0	0	0	0	0	0
	Prepaid expenses and other
	Beginning of period		74	81	107	116	124	132	140	150
	Increases / (decreases)		7	26	9	7	8	9	9	10
	End of period	74	81	107	116	124	132	140	150	160
	PP Exp as % of COR		1.1%	1.3%	1.4%	1.3%	1.4%	1.4%	1.4%	1.4%
	Property, plant & equipment, net
	Beginning of period		1493	1875	2274	2343	2625	2914	3212	3517
	Plus capital expenditures		588	631	344	539	577	618	659	700
	Less depreciation		206	233	275	257	288	320	353	386
	End of period		1875	2274	2343	2625	2914	3212	3517	3831
	Capex:
	Capital additions		588	631	344	539	577	618	659	700
	Capital additions as % of operating income		44%	42%	21%	32%	32%	32%	32%	32%
	CapEx as % of Net PPE		31%	28%	15%	21%	20%	19%	19%	18%
	Depreciation and amortization:
	Depreciation		206	233	275	257	288	320	353	386
	Depreciation as % of Capex		35%	37%	80%	48%	50%	52%	54%	55%
	Depreciation as % of PP&E		11.0%	10.2%	11.7%	11%	11%	11%	11%	11%
	Depreciation as % of Sales		2%	2%	2%	2%	2%	2%	2%	2%
	Amortization			0	0	0	0	0	0	0
	Amortization as % of Capex			0%	0%	0%	0%	0%	0%	0%
	Amortization as % of intangible assets			0%	0%	0%	0%	0%	0%	0%
	Amortization as % of sales			0.000%	0.000%	0.0%	0.0%	0.0%	0.0%	0.0%
	Goodwill
	Beginning of period				0	0	0	0	0	0
	Increases / (decreases) (net of impairments)				0	0	0	0	0	0
	Impairments (2015)				0	0	0	0	0	0
	End of period				0	0	0	0	0	0
	Goodwill as % of COR			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Intangibles Net
	Beginning of period				0	0	0	0	0	0
	amort				0	0	0	0	0	0
	Increases / (decreases)				0	0	0	0	0	0
	End of period			0	0	0	0	0	0	0
	Other intangibles as % of COR			0.0%	0.0%	0%	0%	0%	0%	0%
	Long Term Investments
	Beginning of period				3	1	3	2	3	3
	Increases / (decreases)				(2)	2	(0)	0	0	0
	End of period			3	1	3	2	3	3	3
	OA as % of COR			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Other Long Term Assests , Total Svetislav Pejcic: Svetislav Pejcic: accounts for all the following erred tax assets: Post-retirement benefit obligations Accrued expenses and other reserves Stock-based compensation Derivative instruments Pension Lease financing obligation Accrued trade promotion reserves Net operating loss carryforwards Capital loss carryforwards Other Gross deferred tax assets Valuation allowance
	Beginning of period		139	148	161	153	174	179	194	206
	Increases / (decreases)		9	13	(8)	21	5	15	11	14
	End of period	139	148	161	153	174	179	194	206	220
	Other Long Term Assets as % to Sales	1.4%	1.4%	1.5%	1.3%	1.4%	1.3%	1.3%	1.3%	1.3%
x	LIABILITIES
	Fiscal year	2013.01.31 A	2014.01.31 A	2015.01.31 A	2016.01.31 A	2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Accounts payable
	Beginning of period		808	779	1,001	946	1,082	1,114	1,208	1,276
	Increases / (decreases)		(28)	221	(55)	136	32	94	69	90
	End of period	808	779	1,001	946	1,082	1,114	1,208	1,276	1,366
	Accounts payable as % of COR		10.6%	12.6%	11.0%	11.8%	11.4%	11.6%	11.5%	11.6%
	Accrued Expenses
	Beginning of period		562	595	641	657	721	758	813	863
	Increases / (decreases)		34	46	16	64	37	55	51	59
	End of period	562	595	641	657	721	758	813	863	922
	Accrued liabilities as % of COR		8.1%	8.1%	7.7%	7.9%	7.8%	7.8%	7.8%	7.8%
	Accrued income taxes
	Beginning of period				0	0	0	0	0	0
	Increases / (decreases)				0	0	0	0	0	0
	End of period			0	0	0	0	0	0	0
	TP as % of sales			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	TP as % of tax expense			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Short-term debt
	Beginning of period				0	0	0	0	0	0
	Increases / (decreases)				0	0	0	0	0	0
	End of period			0	0	0	0	0	0	0
	Short term debt as % of COR			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Short term debt as % total liab			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Short term debt as % total assets			0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Other Current Liabilities, net
	Beginning of period				17	0	0	0	0	0
	Increases / (decreases)				(17)	0	0	0	0	0
	End of period			17	0	0	0	0	0	0
	Curr portion of LT debt as % of LT debt			4.3%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Curr portion of LT debt as % of total debt			4.2%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
	Long-term debt
	Beginning of period		150	150	396	396	439	459	494	524
	Increases / (decreases)		0	246	0	43	20	35	30	36
	End of period	150	150	396	396	439	459	494	524	560
	Debt as % Sales		1%	4%	3%	3%	3%	3%	3%	3%
	Interest rate as a percent of operating income			0.2%	0.8%	0.5%	0.5%	0.5%	0.5%	0.5%
	Interest rate as a percent of Debt			0.7%	3.2%	2.0%	2.0%	2.0%	2.0%	2.0%
	Other long-term liabilities
	Beginning of period		0	0	280	268	601	627	672	719
	Increases / (decreases)		0	280	(11)	333	26	44	48	47
	End of period	0	0	280	268	601	627	672	719	766
	Other long-term liabilities % of operating income		0.0%	18.8%	16.5%	37%	37%	37%	37%	37%
	Other long-term liabilities % of total Liab		0.0%	11.6%	11.2%	20.4%	20.3%	20.2%	20.4%	20.3%
	Deferred Income Tax
	Beginning of period		331	346	74	130	110	132	134	146
	Increases / (decreases)		15	(273)	56	(20)	22	2	13	7
	End of period	331	346	74	130	110	132	134	146	153
	Deferred Income Tax as a % of EBT		25.8%	5.0%	8.1%	6.5%	7.3%	6.9%	7.1%	7.0%
x	STOCKHOLDER EQUITY
	Fiscal year	2013.01.31 A	2014.01.31 A	2015.01.31 A	2016.01.31 A	2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Paid in Capital
	Beginning of period		869	938	1,018	1,126	1,227	1,339	1,464	1,595
	Stock Based Comp		47	53	71	64	71	78	81	87
	Common Stock Issued Other		47	52	62	61	66	72	75	80
	Common Stock Repurchased		(24)	(25)	(25)	(25)	(25)	(25)	(25)	(25)
	End of period	869	938	1,018	1,126	1,227	1,339	1,464	1,595	1,738
	Stk comp as % pretax profit		3%	4%	4%	4%	4%	4%	4%	4%
	Common Issued other as % pretax profit		3%	3%	4%	4%	4%	4%	4%	4%
	Retained earnings
	Beginning of period		989	1,191	1,422	1,575	1,775	2,023	2,338	2,722
	Net income		837	925	1,021	1,051	1,125	1,205	1,284	1,364
	Dividends		(110)	(168)	(192)	(176)	(202)	(215)	(225)	(242)
	Common Stock Repurchased		(525)	(525)	(675)	(675)	(675)	(675)	(675)	(675)
	End of period	989	1,191	1,422	1,575	1,775	2,023	2,338	2,722	3,169
	Dividend of common stock as % of net income		13%	18%	19%	17%	18%	18%	18%	18%
	Common Issued other as % pretax profit		39%	35%	42%	39%	39%	40%	39%	39%
	Treasury common stock shares
	Beginning of period		(92)	(122)	(161)	(230)	(275)	(321)	(367)	(413)
	Treasury Stock Other		(30)	(39)	(69)	(46)	(46)	(46)	(46)	(46)
			0	0	0	0	0	0	0	0
	End of period		(122)	(161)	(230)	(275)	(321)	(367)	(413)	(459)
	Comprehensive Inc
	Beginning of period		1	0	0	0	0	(0)	(0)	(0)
	Increases / (decreases)		(0)	(0)	(0)	(0)	(0)	(0)	(0)	(0)
	End of period		0	0	0	0	(0)	(0)	(0)	(0)
	Non-controlling Interest
	Beginning of period				0	0	0	0	0	0
	Increases / (decreases)				0	0	0	0	0	0
	End of period			0	0	0	0	0	0	0
x	CASH FLOW STATEMENT
	Fiscal year					2017.01.31 A	2018.01.31 A	2019.01.31 A	2020.01.31 A	2021.01.31 A
	Cash from operating activities
	Net income				1,021	1,051	1,125	1,205	1,284	1,364
	Depreciation				275	257	288	320	353	386
	Deferred Income Tax
	Stock Based Comp				71	64	71	78	81	87
	Short-term investments				1	(1)	(0)	0	(0)	(0)
	Accounts receivable, net				(0)	(8)	(5)	(6)	(6)	(6)
	Inventories				(46)	(130)	(101)	(108)	(116)	(124)
	Current DTA				0	0	0	0	0	0
	Prepaid expenses and other				(9)	(7)	(8)	(9)	(9)	(10)
	Accounts payable				(55)	136	32	94	69	90
	Accd Exp Pay				16	64	37	55	51	59
	Accrued income taxes				0	0	0	0	0	0
	Total				1,271	1,426	1,438	1,630	1,705	1,846
	Cash from investing activities
	Capex:				(344)	(539)	(577)	(618)	(659)	(700)
	Goodwill				0	0	0	0	0	0
	Intangibles Net				0	0	0	0	0	0
	Long Term Investments				2	(2)	0	(0)	(0)	(0)
	Other Long Term Assests , Total				8	(21)	(5)	(15)	(11)	(14)
	Total				(334)	(562)	(582)	(634)	(670)	(715)
	Cash from financing activities
	Short-term debt				0	0	0	0	0	0
	Other Current Liabilities, net				(17)	0	0	0	0	0
	Long-term debt				0	43	20	35	30	36
	Other long-term liabilities				(11)	333	26	44	48	47
	Deferred Income Tax				56	(20)	22	2	13	7
	Pay dividends				(192)	(176)	(202)	(215)	(225)	(242)
	Common Stock Issued Other				62	61	66	72	75	80
	Treasury Stock Other				(69)	(46)	(46)	(46)	(46)	(46)
	Common Stock Repurchased				(700)	(700)	(700)	(700)	(700)	(700)
	Comprehensive Inc				(0)	(0)	(0)	(0)	(0)	(0)
	Non-controlling interest changes				0	0	0	0	0	0
	Total				(871)	(505)	(814)	(808)	(805)	(818)
	Net change in cash during period Svetislav Pejcic: Svetislav Pejcic: Even thought there is a negative cash flow, it balances. I have chosen to let this stay since on the 10k they have experienced this as well for the past years	Svetislav Pejcic: Svetislav Pejcic: accounts for all the following erred tax assets: Post-retirement benefit obligations Accrued expenses and other reserves Stock-based compensation Derivative instruments Pension Lease financing obligation Accrued trade promotion reserves Net operating loss carryforwards Capital loss carryforwards Other Gross deferred tax assets Valuation allowance	Svetislav Pejcic: Svetislav Pejcic: At cost: 143,124,384 in 2015 138,856,786 in 2014				67	359	42	188	231	313