Step 5 Project Future Financial Statements (Excel) 40 points

Project the Balance sheet out 5 years (given are the previous 3 years for help) and add explanations on your projections for each year you project

Balance Sheet
Assets:
Cash and cash equivalents	9,135	8,705	6,867
- common size	4.5%	4.4%	3.5%
- rate of change		-4.7%	-21.1%
Accounts and notes
receivable - net	6,778	5,624	5,835
- common size	3.3%	2.8%	2.9%
- rate of change		-17.0%	3.8%
Inventories	45,141	44,469	43,046
- common size	22.2%	22.3%	21.7%
- rate of change		-1.5%	-3.2%
Prepaid expenses and other
current assets	2,224	1,441	1,941
- common size	1.1%	0.7%	1.0%
- rate of change		-35.2%	34.7%
Current assets	63,278	60,239	57,689
- common size	31.1%	30.2%	29.0%
- rate of change		-4.8%	-4.2%
Property and Equipment	177,395	176,958	179,492
- common size	87.2%	88.7%	90.3%
- rate of change		-0.2%	1.4%
Accumulated depreciation	-63,115	-66,787	-71,782
- common size	-31.0%	-33.5%	-36.1%
- rate of change		5.8%	7.5%
Goodwill	18,102	16,695	17,037
- common size	8.9%	8.4%	8.6%
- rate of change		-7.8%	2.0%
Other assets	5,455	6,131	9,921
- common size	2.7%	3.1%	5.0%
- rate of change		12.4%	61.8%
Total Assets	203,490	199,581	198,825
- common size	100.0%	100.0%	100.0%
- rate of change		-1.9%	-0.4%
Liabilities
Accounts payable	38,410	38,487	41,433
- common size	32.7%	33.2%	35.0%
- rate of change		0.2%	7.7%
Accrued liabilities	19,152	19,607	20,654
- common size	16.3%	16.9%	17.5%
- rate of change		2.4%	5.3%
Accrued income taxes	1,021.00	521	921
- common size	0.9%	0.4%	0.8%
- rate of change		-49.0%	76.8%
Short-term borrowings	1,592	2,708	1,099
- common size	1.4%	2.3%	0.9%
- rate of change		70.1%	-59.4%
Long-term debt - due within
one year	4,791	2,745	2,256
- common size	4.1%	2.4%	1.9%
- rate of change		-42.7%	-17.8%
Capital lease and financial
obligations due within one year	287	551	565
- common size	0.2%	0.5%	0.5%
- rate of change		92.0%	2.5%
Current liabilities	65,253	64,619	66,928
- common size	55.5%	55.7%	56.6%
- rate of change		-1.0%	3.6%
Long-term debt	40,889	38,214	36,015
- common size	34.8%	33.0%	30.4%
- rate of change		-6.5%	-5.8%
Accrued liabilities	19,152	19,607	20,654
- common size	16.3%	16.9%	17.5%
- rate of change		2.4%	5.3%
Long-term capital lease and
financing obligations	2,606	5,816	6,003
- common size	2.2%	5.0%	5.1%
- rate of change		123.2%	3.2%
Deferred income taxes and other	8,805	7,321	9,344
- common size	7.5%	6.3%	7.9%
- rate of change		-16.9%	27.6%
Deferred tax liabilites	9,415	9,055	10,127
- common size	8.0%	7.8%	8.6%
- rate of change		-3.8%	11.8%
Total liabilites	117,553	115,970	118,290
- common size	100.0%	100.0%	100.0%
- rate of change		-1.3%	2.0%

Here is the income statement for the previous 3 years and projected 5 to rely on:

	Actuals				Forecasts
Year	2015	2016	2017		2018	2019	2020	2021	2022
Income statement
Revenues	485,651	482,130	485,873		496,805	510,467	524,505	534,995	545,695
- Common Size	100.00%	100.00%	100.00%		2.25%	2.75%	2.75%	2.00%	2.00%
- Rate of change		-0.73%	0.78%
Cost of goods sold	-365,086	-360,984	-361,256		-370,120	-379,532	-387,714	-393,863	-401,632
- common size	-75.17%	-74.9%	-74.4%		-74.50%	-74.35%	-73.92%	-73.62%	-73.60%
- rate of change		-1.1%	0.1%
Gross profit	120,565	121,146	124,617		126,685	130,935	136,791	141,132	144,064
- common size	24.8%	25.1%	25.6%		25.50%	25.65%	26.08%	26.38%	26.40%
- rate of change		0.5%	2.9%
Selling, general, and
administrative expenses	-93,418	-97,041	-101,853		-108,800	-116,387	-124,308	-126,794	-129,330
- common size	-19.2%	-20.1%	-21.0%		-21.90%	-22.80%	-23.70%	-23.70%	-23.70%
- rate of change		3.9%	5.0%
Operating Profit	27,147	24,105	22,764		17,885	14,548	12,483	14,338	14,734
- common size	5.6%	5.0%	4.7%		3.60%	2.85%	2.38%	2.68%	2.70%
- rate of change		-11.2%	-5.6%
Interest Income	113	81	100		99	102	105	107	109
- common size	0.02%	0.02%	0.02%		0.02%	0.02%	0.02%	0.02%	0.02%
- rate of change		-28.3%	23.5%
Interest Expense	-2,348	-2,467	-2,267		-2,434	-2,501	-2,570	-2,621	-2,674
- common size	-0.48%	-0.51%	-0.47%		-0.49%	-0.49%	-0.49%	-0.49%	-0.49%
- rate of change		5.1%	-8.1%
Income before tax	24,799	21,638	20,497		20,369	20,419	20,456	20,330	20,191
- common size	5.1%	4.5%	4.2%		4.10%	4.00%	3.90%	3.80%	3.70%
- rate of change		-12.7%	-5.3%
Income tax expense	-7,985	-6,558	-6,204		-4,471	-4,594	-4,721	-4,815	-4,911
- common size	-1.6%	-1.4%	-1.3%		-0.90%	-0.90%	-0.90%	-0.90%	-0.90%
- rate of change		-17.9%	-5.4%
Net income	16,363	14,694	13,643		14,904	15,314	15,735	16,050	16,371
- common size	3.4%	3.0%	2.8%		3.00%	3.00%	3.00%	3.00%	3.00%
- rate of change		-10.2%	-7.2%
Net income attributable to
noncontrolling interests	-736	-386	-650		-696	-715	-734	-749	-764
- common size	-0.15%	-0.08%	-0.13%		-0.14%	-0.14%	-0.14%	-0.14%	-0.14%
- rate of change		-47.6%	68.4%
Other comprehensive income
items	-4,718	-4,970	-2,845		-4,968	-5,105	-5,245	-5,350	-5,457
- common size	-0.97%	-1.03%	-0.59%		-1%	-1%	-1%	-1%	-1%
- rate of change		5.3%	-42.8%
Comprehensive income
attributable to Walmart	12,191	10,265	11,008		11,427	11,741	12,064	12,305	12,551
- common size	2.5%	2.1%	2.3%		2.3%	2.3%	2.3%	2.3%	2.3%