FIN 317 – HOMEWORK 7
You are given comparative income statements and balance sheets for Best Buy (in millions). Based on the given forecasting assumptions, prepare forecasted income statement, balance sheet, and cash flow statement for year 3. (Show your calculation like A/B=C or (A+B)/C=D etc. in given cells).
PROJECTED INCOME STATEMENT (30 points)
Best Buy Income Statement
|
|
|
|
|
($ in millions)
|
Forecasting step
|
Year 3 Estimate
|
Year 2
|
Year 1
|
Net sales
|
|
|
15,326
|
12,494
|
Cost of goods sold
|
|
|
12,267
|
10,101
|
Gross profit
|
|
|
3,059
|
2,393
|
Selling, general and administrative expense
|
|
|
2,251
|
1,728
|
Depreciation and amortization expense
|
|
|
167
|
103
|
Income before tax
|
|
|
641
|
562
|
Income tax expense
|
|
|
245
|
215
|
Net income
|
|
|
396
|
347
|
Forecasting assumptions (income statement)
|
|
Sales growth
|
0.2267
|
Gross profit margin
|
0.1996
|
Selling, general and administrative expense/sales
|
0.1469
|
Depreciation expense/Gross prior year PPE
|
0.1528
|
Income tax expense/pretax income
|
0.3822
|
PROJECTED BALANCE SHEET (40 points)
Best Buy Balance Sheet
|
|
|
|
|
($ in millions)
|
Forecasting step
|
Year 3 Estimate
|
Year 2
|
Year 1
|
Cash
|
|
|
746
|
751
|
Receivables
|
|
|
313
|
262
|
Inventories
|
|
|
1,767
|
1,184
|
Other current assets
|
|
|
102
|
41
|
Total current assets
|
|
|
2,928
|
2,238
|
PPE
|
|
|
1,987
|
1,093
|
Accumulated depreciation
|
|
|
543
|
395
|
Net PPE
|
|
|
1,444
|
698
|
Other assets
|
|
|
466
|
59
|
Total assets
|
|
|
4,838
|
2,995
|
|
|
|
|
|
Accounts payable
|
|
|
2,473
|
1,704
|
Short term debt and current portion of long-term debt
|
|
|
114
|
16
|
Income tax liabilities
|
|
|
127
|
65
|
Total current liabilities
|
|
|
2,714
|
1,785
|
Long-term debt
|
|
|
303
|
115
|
Total long-term liabilities
|
|
|
303
|
115
|
Common stock
|
|
|
20
|
20
|
Additional paid in capital
|
|
|
576
|
247
|
Retained earnings
|
|
|
1,225
|
828
|
Shareholders' Equity
|
|
|
1,821
|
1,095
|
Total liabilities and SHs' Equity
|
|
|
4,838
|
2,995
|
Forecasting assumptions (balance sheet)
|
|
Accounts receivable turnover rate
|
48.96
|
Inventory turnover rate
|
6.94
|
Other current assets
|
no change
|
CAPEX (in millions)
|
1,262
|
Other assets
|
no change
|
Accounts payable turnover rate
|
4.96
|
Short term debt and current portion of LT debt
|
no change
|
Taxes payable/tax expense
|
0.5184
|
Long term debt
|
189
|
Common stock
|
no change
|
Additional paid in capital
|
no change
|
Dividend payments
|
0
|
CASH FLOW STATEMENT PREPARATION (10 points)
Based on your forecasted balance sheet and income statement, in order to be able to prepare forecasted cash flow statement in the next step,for relevant accounts, calculate changes from Year 2 to Year 3, state whether those changes are cash inflow or outflow, and categorize them as operating, financing or investing activities (write your answers in the corresponding cells for relevant accounts).
Best Buy Projected Statement of Cash Flows
|
|
|
|
|
|
($ in millions)
|
Year 3 Estimate
|
Year 2
|
Change from Year 2 to Year 3
|
cash inflow or outflow
|
operating, investing or financing
|
Cash
|
|
746
|
|
|
|
Receivables
|
|
313
|
|
|
|
Inventories
|
|
1,767
|
|
|
|
Other current assets
|
|
102
|
|
|
|
Total current assets
|
|
2,928
|
|
|
|
PPE
|
|
1,987
|
|
|
|
Accumulated depreciation
|
|
543
|
|
|
|
Net PPE
|
|
1,444
|
|
|
|
Other assets
|
|
466
|
|
|
|
Total assets
|
|
4,838
|
|
|
|
|
|
|
|
|
|
Accounts payable
|
|
2,473
|
|
|
|
Short term debt and current portion of long-term debt
|
|
114
|
|
|
|
Income tax liabilities
|
|
127
|
|
|
|
Total current liabilities
|
|
2,714
|
|
|
|
Long-term debt
|
|
303
|
|
|
|
Total long-term liabilities
|
|
303
|
|
|
|
Common stock
|
|
20
|
|
|
|
Additional paid in capital
|
|
576
|
|
|
|
Retained earnings
|
|
1,225
|
|
|
|
Shareholders' Equity
|
|
1,821
|
|
|
|
Total liabilities and SHs' Equity
|
|
4,838
|
|
|
|
PROJECTED CASH FLOW STATEMENT (20 points)
Prepare the projected cash flow statement of Best Buy Company for Year 3. (write your answers in the corresponding cells) (there are more cells than you need).
Best Buy Projected Statement of Cash Flows
|
|
($ in millions)
|
|
Net income
|
|
Items to adjust income to cash flows
|
|
|
|
|
|
|
|
|
|
|
|
Net cash flow from operations
|
|
|
|
|
|
|
|
Net cash flow from investing activities
|
|
|
|
|
|
|
|
Net cash flow from financing activities
|
|
|
|
Net change in cash
|
|
Beginning cash
|
|
Ending cash
|
|
">