Order # 13540

13540_FinancialForecasting_ModelingTemplate1.xlsx

Title: Starbucks

Paper type Essay

Paper format APA

Course level Master

Subject Area Business

# pages 1   ( or 300 words Minimum)

Spacing Double Spacing

# sources 2

Paper Details

Using the Starbucks tab on the attached file please answer the following:

5.Offer interpretive analysis of your company’s actual growth rate compared to its internal and sustainable growth rates. Discuss what these numbers mean regarding your firm’s ability to finance a growth strategy. Justify your projection of the sales growth rate for the next 5 years.

https://finance.yahoo.com/quote/SBUX/financials?p=SBUX This link can also be used as a source.

Forecast

ingTemplate

b

le>

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 30

3.

7 33

0.1 37

4.1 10

0% Growth 1

3.0% 422.7 477.7 539.8 Cost of Revenue 127.7 130.9 149.0 39.83%

% sales

4

0.0

% 169.1 19

1.1 21

5.9 Gross profit 17

6.0 19

9.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 15

1.4 166.2 19

5.7 52.31%

% sales

5

0.0% 2

11.4 238.8 269.9 Others 4.5

0.0

1

1.2 2.9 9%

% sales 3.0%

12.7 14.3 16.2 Earnings Before Interest and Taxes (EBIT) 19.6 3

2.5 17.7 29.0 32.7 3

7.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 1

9.5 3

1.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 2

1.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 5

1.3 1

3.7%

% sales

13.0

% 55.0 6

2.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

2

9.0% 12

2.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 2

20.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 2

1.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 1

5.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 4

2.0

% 39.7% 3

9.

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 10

1.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% 1

6.7% 19.1% Return on invested capital

7.7%

1

2.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

:

You can vary your assumptions to see what happens to your bottom line.

Hypothetical Three-Year Forecast Worksheet.

Assumptions Inputs Forecast

2016

2018 2019

($m) ($m) ($m) (% sales) ($m) ($m) ($m)
Income Statement

Revenue

Growth

Cost of Revenue

% sales 40.0%

Gross profit

Operating expenses
Research Development 0.0 0.0 0.0

0.2%

Selling General and Administrative

% sales

Others

% sales 3.0%

Earnings Before Interest and Taxes (EBIT)

Interest expense

% of debt 6.0%

Income before Tax

Income Tax expense

% of EBT

Net Income

Preferred stock and other adj. 0.0 0.0 0.0 0.00% 0.03%

Div Payout 30%

Retatined earnings

2016 2017 2018 2018 2019 2020 2021
Balance Sheet ($m) ($m) ($m) (% sales) ($m) ($m) ($m)
Assets

Cash and Cash Equivalents

10.0

10.0 10.0

Short Term Investments

1.0%

Accounts receivable

6.7% % sales 8.0%

Inventories

% sales 13.0%

Other current assets

% sales 4.7%

Longterm investment

% sales 1.0% 281802.3 321254.6 366230.3

Property Plant and Equipmen

% sales

Other assets

2.2% % sales 1.5%

Total assets

Liabilities and equity

Accounts payable

4.8% % sales 4.0%

Accruals 0.0 0.0 0.0 0.0% % sales 3.0% 845406.9 963763.9 1098690.8
Short/current long term debts

0.0

% LTD

1363530.0 1363530.0

Other current liabilities

Plug

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)

Long-term liabilities

Fixed

9090200.0 9090200.0 9090200.0

Other liabilities

carry forward 0.20%

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

carry forward

1169500.0 1169500.0

Total liabilities and equity

18697029.0 21314611.7

Trial assets

Trial liabs. & equity

Trial plug

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

40.0% 40.0% 40.0%

Accounts receivable as percent of sales

4.2% 6.7% 8.0% 8.0% 8.0%

Inventory as percent of sales

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

Net operating working capital (NOWC)

Total operating capital

Free cash flow (FCF)

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

3.6% 3.7%

Return on assets

5.9%

Return on equity

Return on invested capital

8.0%

b 0.70 0.70 0.70 69% 69% 69%
Internal growth rate

4.2% 4.5% 4.7%

Sustainable growth rate

Historical revenue grrowth rate 5.0% 10.4%

5%

10%

7.2%

9%

Starbucks Corporation (SBUX)		2019
	2017				2018		2020		2021
21,315,900.0	22,386,800.0	24,719,500.0	100%	14.0%	28180230.0	32125462.2	36623026.9
14,573,300.0	15,527,600.0	17,367,700.0	70.26%	11272092.0	12850184.9	14649210.8
6,742,600.0	6,859,200.0	7,351,800.0	16908138.0	19275277.3	21973816.1
	0.00%	42270.3	48188.2	54934.5
1,408,900.0	1,450,700.0	1,755,400.0	7.	10%	50.0%	14090115.0	16062731.1	18311513.5
499,200.0	500,300.0	539,300.0	2.18%		845406.9		963763.9		1098690.8
4,834,500.0	4,908,200.0	5,057,100.0	1930345.8	2200594.2	2508677.3
(81,300.0)	(92,500.0)	(170,300.0)	548793.6 Chao and Emil: 6%*(LTD+STD)-6%*(cash)	549267.1	549806.8
4,915,800.0	5,000,700.0	5,227,400.0	1,381,552.1	1,651,327.1	1,958,870.6
1,379,700.0	1,432,600.0	1,262,000.0	30.0%	414465.6	495398.1	587661.2
	3,536,100.0		3,568,100.0		3,965,400.0	967086.5	1155929.0	1371209.4
8454.1	9637.6	10986.9
Ordinary dividends 30%	1,060,830.0	1,070,430.0	1,189,620.0	290125.9	346778.7	411362.8
	2,475,270.0		2,497,670.0		2,775,780.0		668,506.5		799,512.6		948,859.7
2,128,800.0	2,462,300.0	8,756,300.0	35.4%	Plug, min.$10m	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
134,400.0	228,600.0	181,500.0	0.7%		281802.3		321254.6		366230.3
768,800.0	938,400.0	1,648,500.0	2254418.4	2570037.0	2929842.2
1,378,500.0	1,364,000.0	1,400,500.0		5.7%	3663429.9	4176310.1	4760993.5
27,700.0	13,400.0	24,500.0	0.1%	1324470.8	1509896.7	1721282.3
1,496,200.0	1,023,900.0	602,400.0	2.4%
4,533,800.0	4,919,500.0	5,929,100.0	24.0%	29.0%	8172266.7	9316384.0	10620677.8
1,288,700.0	1,158,200.0	546,900.0	422703.5	481881.9	549345.4
11,756,900.0	12,108,300.0	19,089,700.0	77.2%	16400903.9		18697029.0		21314611.7
730,600.0	782,500.0	1,179,300.0	1127209.2	1285018.5	1464921.1
399,900.0	349,900.0	15.0%			1363530.0
2522000	2547200	3120300		2080190.8		3961253.1		6105664.0
3,185,300.0	3,932,600.0	9,090,200.0				9090200.0
689,700.0	755,300.0	8,152,600.0	33.0%	56360.5	64250.9	73246.1
5,884,000.0	5,450,100.0	1,169,500.0	$ 1,169,500.00			1169500.0
15,886,770.0	15,965,370.0	25,837,580.0	104.5%	16400903.8
16400893.9 Chao and Emil: Addup all asset items exceptplug	18697019.0	21314601.7
14320713.0 Chao and Emil: addup all LiabandE except the plug cell	14735775.9	15208947.6
	2080180.8		3961243.1		6105654.0
68.4%	69.4%	70.3%
	3.6%
6.5%	6.1%
967,086.5	1,155,929.0	1,371,209.4
3,707,600.0	4,224,200.0	10,832,000.0	3,906,183.0	4,643,941.4	5,484,986.0
8,241,400.0	9,143,700.0	16,761,100.0	12,078,449.7	13,960,325.5	16,105,663.8
2,665,800.0	(3,652,000.0)	5,649,736.8	(725,946.8)	(774,129.0)
16.6%	1	5.9%	16.0%	3.4%
30.1%	29.5%	20.8%	6.2%	6.4%
42.3%	65.5%	339.1%	82.7%	98.8%	117.2%
42.9%	39.0%	23.7%	8.3%	8.5%
26.7%	26.0%	17.0%
42.1%	84.6%	-172.8%	133.4%	216.1%	430.0%
2017 Growth in Sales (Revenue) Compared to 2016
2018 Growth in Sales (Revenue) Compared to 2017
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
2016 – 2018 Three Year Growth in Sales (Gross Profit)
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%