Order # 13540

13540_FinancialForecasting_ModelingTemplate1.xlsx

Home >Business & Finance homework help >Economics homework help >Order # 13540

ForecastingTemplate

The Body Shop 2013:				You can vary your assumptions to see what happens to your bottom line.
Hypothetical Three-Year Forecast Worksheet.
					Assumptions	Inputs	Forecast
	2011	2012	2013	2013			2014	2015	2016
	($m)	($m)	($m)	(% sales)			($m)	($m)	($m)
Income Statement
Revenue	303.7	330.1	374.1	100%	Growth	13.0%	422.7	477.7	539.8
Cost of Revenue	127.7	130.9	149.0	39.83%	% sales	40.0%	169.1	191.1	215.9
Gross profit	176.0	199.2	225.1				253.6	286.6	323.9
Operating expenses
Research Development	0.5	0.5	0.5	0.13%		0.2%	0.6	0.7	0.8
Selling General and Administrative	151.4	166.2	195.7	52.31%	% sales	50.0%	211.4	238.8	269.9
Others	4.5	0.0	11.2	2.99%	% sales	3.0%	12.7	14.3	16.2
Earnings Before Interest and Taxes (EBIT)	19.6	32.5	17.7				29.0	32.7	37.0
Interest expense	0.1	1.5	4.4		% of debt	6.0%	3.7 Chao and Emil: 6%(LTD+STD)-6%(cash)	3.7	3.7
Income before Tax	19.5	31.0	13.3				25.2	29.0	33.2
Income Tax expense	5.9	9.3	4.0		% of EBT	30.0%	7.6	8.7	10.0
Net Income	13.7	21.7	9.3				17.7	20.3	23.3
Preferred stock and other adj.	0.1	0.1	0.1	0.03%		0.03%	0.1	0.1	0.2
Ordinary dividends 30%	4.1	6.5	2.8		Div Payout	30%	5.3	6.1	7.0
Retatined earnings	9.5	15.1	6.4				12.2	14.1	16.1
	2011	2012	2013	2013			2014	2015	2016
Balance Sheet	($m)	($m)	($m)	(% sales)			($m)	($m)	($m)
Assets
Cash and Cash Equivalents	24.0	10.0	10.0	2.7%	Plug, min.$10m	10.0	10.0 Chao and Emil: If Trial plug<0, then -Trial plug, otherwise 10 In other words, if Assets<(D+E), then add the excess financing to the Cash cell. Otherwise, if Assets>(D+D), then EFN: financed by taking overdraft or STD	10.0	10.0
Short Term Investments	10.0	9.2	3.7	1.0%		1.0%	4.2	4.8	5.4
Accounts receivable	27.8	30.3	30.3	8.1%	% sales	8.0%	33.8	38.2	43.2
Inventories	38.6	44.7	51.3	13.7%	% sales	13.0%	55.0	62.1	70.2
Other current assets	12.5	15.6	17.5	4.7%	% sales	4.7%	19.9	22.5	25.4
Longterm investment	0.0	0.0	0.0	0.0%	% sales	1.0%	4.2	4.8	5.4
Property Plant and Equipmen	87.8	104.7	110.6	29.6%	% sales	29.0%	122.6	138.5	156.5
Other assets	4.9	6.0	5.7	1.5%	% sales	1.5%	6.3	7.2	8.1
Total assets	205.6	220.5	229.1	61.2%			256.0	288.0	324.2
Liabilities and equity
Accounts payable	13.0	20.5	10.7	2.9%	% sales	4.0%	16.9	19.1	21.6
Accruals	10.8	15.6	11.5	3.1%	% sales	3.0%	12.7	14.3	16.2
Short/current long term debts	11.0	8.0	10.0		% LTD	15.0%	9.2	9.2	9.2
Other current liabilities	10	8.7	7		Plug		21.4 Chao and Emil: If Trial plug>0, then plug, otherwise 0	Chao and Emil: If Trial plug<0, then -Trial plug, otherwise 10 In other words, if Assets<(D+E), then add the excess financing to the Cash cell. Otherwise, if Assets>(D+D), then EFN: financed by taking overdraft or STD	Chao and Emil: 6%(LTD+STD)-6%(cash)	47.6	77.2
Long-term liabilities	28.0	30.0	61.2		Fixed	61.2	61.2	61.2	61.2
Other liabilities	1.9	1.2	0.7	0.2%	carry forward	0.20%	0.8	1.0	1.1
Retained Earnings	9.5	15.1	6.4		RE		12.2	14.1	16.1
Shareholders' equity	121.4	121.4	121.6		carry forward	$ 121.60	121.6	121.6	121.6
Total liabilities and equity	205.6	220.5	229.1	61.2%			256.0	288.0	324.2
					Trial assets		246.0 Chao and Emil: Addup all asset items exceptplug	278.0	314.2
					Trial liabs. & equity		234.7 Chao and Emil: addup all LiabandE except the plug cell	240.4	247.0
					Trial plug		11.4	37.6	67.2
					Min. cash balance		10.0	10.0	10.0
					Plug		21.4	47.6	77.2
Model Inputs
Costs (excluding depreciation) as percent of sales	42.0%	39.7%	39.8%				40.0%	40.0%	40.0%
Accounts receivable as percent of sales	9.2%	9.2%	8.1%				8.0%	8.0%	8.0%
Inventory as percent of sales	12.7%	13.5%	13.7%				13.0%	13.0%	13.0%
Model Outputs
Net operating profit after taxes (NOPAT)	13.7	21.7	9.3				17.7	20.3	23.3
Net operating working capital (NOWC)	89.1	73.7	90.6				79.9	90.1	101.8
Total operating capital	176.9	178.4	201.2				202.5	228.7	258.3
Free cash flow (FCF)		20.2	(13.5)				16.4	(5.9)	(6.4)
External financing needed (EFN)							11.4	37.6	67.2
Ratio Analysis
Current ratio	2.5	2.1	2.9				2.0	1.5	1.2
Inventory turnover	30%	34%	34%				33%	33%	33%
Total assets turnover	1.5	1.5	1.6				1.7	1.7	1.7
Profit margin	4.5%	6.6%	2.5%				4.2%	4.2%	4.3%
Return on assets	6.6%	9.8%	4.1%				6.9%	7.0%	7.2%
Return on equity	10.4%	17.9%	7.7%				14.5%	16.7%	19.1%
Return on invested capital	7.7%	12.2%	4.6%				8.7%	8.9%	9.0%
b	0.69	0.70	0.69				69%	69%	69%
Internal growth rate	4.8%	7.3%	2.9%				5.0%	5.1%	5.2%
Sustainable growth rate	7.8%	14.2%	5.6%				11.2%	13.1%	15.3%
Historical revenue grrowth rate		8.7%	13.3%

Starbucks Corporation

Starbucks Corporation (SBUX) 2019:				You can vary your assumptions to see what happens to your bottom line.
Hypothetical Three-Year Forecast Worksheet.
					Assumptions	Inputs	Forecast
	2016	2017	2018	2018			2019	2020	2021
	($m)	($m)	($m)	(% sales)			($m)	($m)	($m)
Income Statement
Revenue	21,315,900.0	22,386,800.0	24,719,500.0	100%	Growth	14.0%	28180230.0	32125462.2	36623026.9
Cost of Revenue	14,573,300.0	15,527,600.0	17,367,700.0	70.26%	% sales	40.0%	11272092.0	12850184.9	14649210.8
Gross profit	6,742,600.0	6,859,200.0	7,351,800.0				16908138.0	19275277.3	21973816.1
Operating expenses
Research Development	0.0	0.0	0.0	0.00%		0.2%	42270.3	48188.2	54934.5
Selling General and Administrative	1,408,900.0	1,450,700.0	1,755,400.0	7.10%	% sales	50.0%	14090115.0	16062731.1	18311513.5
Others	499,200.0	500,300.0	539,300.0	2.18%	% sales	3.0%	845406.9	963763.9	1098690.8
Earnings Before Interest and Taxes (EBIT)	4,834,500.0	4,908,200.0	5,057,100.0				1930345.8	2200594.2	2508677.3
Interest expense	(81,300.0)	(92,500.0)	(170,300.0)		% of debt	6.0%	548793.6 Chao and Emil: 6%(LTD+STD)-6%(cash)	549267.1	549806.8
Income before Tax	4,915,800.0	5,000,700.0	5,227,400.0				1,381,552.1	1,651,327.1	1,958,870.6
Income Tax expense	1,379,700.0	1,432,600.0	1,262,000.0		% of EBT	30.0%	414465.6	495398.1	587661.2
Net Income	3,536,100.0	3,568,100.0	3,965,400.0				967086.5	1155929.0	1371209.4
Preferred stock and other adj.	0.0	0.0	0.0	0.00%		0.03%	8454.1	9637.6	10986.9
Ordinary dividends 30%	1,060,830.0	1,070,430.0	1,189,620.0		Div Payout	30%	290125.9	346778.7	411362.8
Retatined earnings	2,475,270.0	2,497,670.0	2,775,780.0				668,506.5	799,512.6	948,859.7
	2016	2017	2018	2018			2019	2020	2021
Balance Sheet	($m)	($m)	($m)	(% sales)			($m)	($m)	($m)
Assets
Cash and Cash Equivalents	2,128,800.0	2,462,300.0	8,756,300.0	35.4%	Plug, min.$10m	10.0	10.0 Chao and Emil: If Trial plug<0, then -Trial plug, otherwise 10 In other words, if Assets<(D+E), then add the excess financing to the Cash cell. Otherwise, if Assets>(D+D), then EFN: financed by taking overdraft or STD	10.0	10.0
Short Term Investments	134,400.0	228,600.0	181,500.0	0.7%		1.0%	281802.3	321254.6	366230.3
Accounts receivable	768,800.0	938,400.0	1,648,500.0	6.7%	% sales	8.0%	2254418.4	2570037.0	2929842.2
Inventories	1,378,500.0	1,364,000.0	1,400,500.0	5.7%	% sales	13.0%	3663429.9	4176310.1	4760993.5
Other current assets	27,700.0	13,400.0	24,500.0	0.1%	% sales	4.7%	1324470.8	1509896.7	1721282.3
Longterm investment	1,496,200.0	1,023,900.0	602,400.0	2.4%	% sales	1.0%	281802.3	321254.6	366230.3
Property Plant and Equipmen	4,533,800.0	4,919,500.0	5,929,100.0	24.0%	% sales	29.0%	8172266.7	9316384.0	10620677.8
Other assets	1,288,700.0	1,158,200.0	546,900.0	2.2%	% sales	1.5%	422703.5	481881.9	549345.4
Total assets	11,756,900.0	12,108,300.0	19,089,700.0	77.2%			16400903.9	18697029.0	21314611.7
Liabilities and equity
Accounts payable	730,600.0	782,500.0	1,179,300.0	4.8%	% sales	4.0%	1127209.2	1285018.5	1464921.1
Accruals	0.0	0.0	0.0	0.0%	% sales	3.0%	845406.9	963763.9	1098690.8
Short/current long term debts	399,900.0	0.0	349,900.0		% LTD	15.0%	1363530.0	1363530.0	1363530.0
Other current liabilities	2522000	2547200	3120300		Plug		2080190.8 Chao and Emil: If Trial plug>0, then plug, otherwise 0	Chao and Emil: If Trial plug<0, then -Trial plug, otherwise 10 In other words, if Assets<(D+E), then add the excess financing to the Cash cell. Otherwise, if Assets>(D+D), then EFN: financed by taking overdraft or STD	Chao and Emil: 6%(LTD+STD)-6%(cash)	3961253.1	6105664.0
Long-term liabilities	3,185,300.0	3,932,600.0	9,090,200.0		Fixed	9090200.0	9090200.0	9090200.0	9090200.0
Other liabilities	689,700.0	755,300.0	8,152,600.0	33.0%	carry forward	0.20%	56360.5	64250.9	73246.1
Retained Earnings	2,475,270.0	2,497,670.0	2,775,780.0		RE		668,506.5	799,512.6	948,859.7
Shareholders' equity	5,884,000.0	5,450,100.0	1,169,500.0		carry forward	$ 1,169,500.00	1169500.0	1169500.0	1169500.0
Total liabilities and equity	15,886,770.0	15,965,370.0	25,837,580.0	104.5%			16400903.8	18697029.0	21314611.7
					Trial assets		16400893.9 Chao and Emil: Addup all asset items exceptplug	18697019.0	21314601.7
					Trial liabs. & equity		14320713.0 Chao and Emil: addup all LiabandE except the plug cell	14735775.9	15208947.6
					Trial plug		2080180.8	3961243.1	6105654.0
					Min. cash balance		10.0	10.0	10.0
					Plug		2080190.8	3961253.1	6105664.0
Model Inputs
Costs (excluding depreciation) as percent of sales	68.4%	69.4%	70.3%				40.0%	40.0%	40.0%
Accounts receivable as percent of sales	3.6%	4.2%	6.7%				8.0%	8.0%	8.0%
Inventory as percent of sales	6.5%	6.1%	5.7%				13.0%	13.0%	13.0%
Model Outputs
Net operating profit after taxes (NOPAT)	3,536,100.0	3,568,100.0	3,965,400.0				967,086.5	1,155,929.0	1,371,209.4
Net operating working capital (NOWC)	3,707,600.0	4,224,200.0	10,832,000.0				3,906,183.0	4,643,941.4	5,484,986.0
Total operating capital	8,241,400.0	9,143,700.0	16,761,100.0				12,078,449.7	13,960,325.5	16,105,663.8
Free cash flow (FCF)		2,665,800.0	(3,652,000.0)				5,649,736.8	(725,946.8)	(774,129.0)
External financing needed (EFN)							2080180.8	3961243.1	6105654.0
Ratio Analysis
Current ratio	1.2	1.5	2.6				1.4	1.1	1.0
Inventory turnover	9%	9%	8%				33%	33%	33%
Total assets turnover	1.8	1.8	1.3				1.7	1.7	1.7
Profit margin	16.6%	15.9%	16.0%				3.4%	3.6%	3.7%
Return on assets	30.1%	29.5%	20.8%				5.9%	6.2%	6.4%
Return on equity	42.3%	65.5%	339.1%				82.7%	98.8%	117.2%
Return on invested capital	42.9%	39.0%	23.7%				8.0%	8.3%	8.5%
b	0.70	0.70	0.70				69%	69%	69%
Internal growth rate	26.7%	26.0%	17.0%				4.2%	4.5%	4.7%
Sustainable growth rate	42.1%	84.6%	-172.8%				133.4%	216.1%	430.0%
Historical revenue grrowth rate		5.0%	10.4%
2017 Growth in Sales (Revenue) Compared to 2016	5%
2018 Growth in Sales (Revenue) Compared to 2017	10%
2016 - 2018 Three Year Growth in Sales (Revenue)	16%
2017 Growth in Sales (Gross Profit) Compared to 2016	1.7%
2018 Growth in Sales (Gross Profit) Compared to 2017	7.2%
2016 - 2018 Three Year Growth in Sales (Gross Profit)	9%
2017 Growth in Sales (Net Income) Compared to 2016	0.9%
2018 Growth in Sales (Net Income) Compared to 2017	11%
2016 - 2018 Three Year Growth in Sales (Net Income)	12%