A B C D E F G 4. Create projected statements of cash flows based on the projected income statement and balance sheet 1 information. 2 3 FY2018 FY2019 FY2020 FY2021 FY2022 4 Operating Activities 5 Net Income 6 Depreciation and amortization 7 Short-Term Receivables 8 Inventories 9 Other Current Assets 10 Deferred Tax Assets 11 Other Assets 12 Accounts Payable 13 Income Tax Payable 14 Other Current Liabilities 15 Deferred Tax Liabilities 16 Other Liabilities 17 Cash Flow from Operations 18 19 Investing Activities 20 Property and equipment, at cost 21 Intangible Assets 22 Cash Flow from Investments 23 24 Financing Activities 25 ST Debt & Curr. Portion LT Debt 26 Long-Term Debt 27 Dividend Payout 28 Cash Flow From Financing 29 30 Net Increase (Decrease) in Cash 31 32 33 H A B D E F G H J K 1 Operating Assumptions: 2 Sales will grow at 7% per year in the next 5 years. 3 Cost of sales will be 67% of total sales in the next 5 years. 4 SG&A will be 17% of total sales in the next 5 years. 5 Annual Depreciation and Amortization increases by 60 in each of the next 5 years. 6 Capital expenditure (CapEx) will be 1,200 in each of the next 5 years. 7 8 Income Statement Assumptions: 9 Interest and investment income is 2.5% of the ending Cash & Short-Term Investments in the previous year. 10 Interest rate on debt is 5% based on the ending ST Debt & Curr. Portion LT Debt and Long-Term Debt in the previous year. 11 Tax rate is assumed to be 21%. 12 1,184 million common shares outstanding. 13 Dividend payout ratio = 50% 14 15 Working Capital Assumptions 16 In the next 5 years, AR to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 17 In the next 5 years, Inventory to COGS ratio remains the same as that in the most recent fiscal year (FY2017). 18 In the next 5 years, Other current assets to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 19 In the next 5 years, AP to COGS ratio remains the same as that in the most recent fiscal year (FY2017). 20 In the next 5 years, Income Tax Payable to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 21 In the next 5 years, Other current liabilities to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 22 23 Assets Assumptions: 24 Intangible assets grows at the same rate as sales (7%) in the next 5 years 25 Deferred tax assets grows at the same rate as sales (7%) in the next 5 years 26 Other assets grows at the same rate as sales (7%) in the next 5 years 27 Deferred tax liabilities grows at the same rate as sales (7%) in the next 5 years 28 Other liabilities grows at the same rate as sales (7%) in the next 5 years 29 30 Debt Assumptions 31 Short-term debt increases by 2% in each year. 32 Long-term debt decreases by 8% each year. 33 34 35 36 37 L M N O AI projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provi A B DE F G H J K L M N C 3. Create projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the 1 information provided in the Assumptions worksheet. 2 ***Hint: Working capital accounts may be calculated based on the working capital ratios in FY2017 (See Working Capital Assumptions). ***Hint: Cash account information may be obtained based on the completed cash flow statement (Q4). 3 4 FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2017 5 Assets AR to Sales ratio 6 Cash & Short-Term Investments 2,216 2,538 3,595 Inv to COGS ratio 7 Short-Term Receivables 1.890 2,029 1,952 Other current asset to Sales 8 Inventories 12,748 AP to COGS ratio 11,809 12,549 1,078 9 Other Current Assets 608 Income Tax Payable to Sales 638 18,933 10 Total Current Assets 16,993 17,724 Other current liabilities to Sales 11 Property and equipment, at cost 39,266 40,426 41,414 12 Accumulated depreciation and amortization 17,075 18,512 19,339 13 Net Property, Plant & Equipment 22,191 21,914 22,075 14 Intangible Assets 2,275 2,665 2,093 48 15 Deferred Tax Assets 91 119 16 Other Assets 652 1,144 1,127 17 Total Assets 42,549 42,966 44,529 18 19 Liabilities & Shareholders' Equity 20 ST Debt & Curr. Portion LT Debt 427 1,252 2,761 21 Accounts Payable 6,565 7,000 7,244 22 Income Tax Payable 25 54 36 5,498 23 Other Current Liabilities 5,856 6,135 24 Total Current Liabilities 12,526 14,133 16,194 25 Long-Term Debt 20,888 26 Deferred Tax Liabilities 854 22,349 24,267 296 440 1,965 1,855 2,174 36,233 38,633 43,075 27 Other Liabilities 28 Total Liabilities 29 Common Equity 6,316 4,333 1,454 30 Total Shareholders' Equity 6,316 4,333 1.454 31 Total Equity 6,316 4,333 1,454 32 Total Liabilities & Shareholders' Equity 42,549 42,966 44,529 33 34 35 36 37 38 A B C D E F G 4. Create projected statements of cash flows based on the projected income statement and balance sheet 1 information. 2 3 FY2018 FY2019 FY2020 FY2021 FY2022 4 Operating Activities 5 Net Income 6 Depreciation and amortization 7 Short-Term Receivables 8 Inventories 9 Other Current Assets 10 Deferred Tax Assets 11 Other Assets 12 Accounts Payable 13 Income Tax Payable 14 Other Current Liabilities 15 Deferred Tax Liabilities 16 Other Liabilities 17 Cash Flow from Operations 18 19 Investing Activities 20 Property and equipment, at cost 21 Intangible Assets 22 Cash Flow from Investments 23 24 Financing Activities 25 ST Debt & Curr. Portion LT Debt 26 Long-Term Debt 27 Dividend Payout 28 Cash Flow From Financing 29 30 Net Increase (Decrease) in Cash 31 32 33 H A B D E F G H J K 1 Operating Assumptions: 2 Sales will grow at 7% per year in the next 5 years. 3 Cost of sales will be 67% of total sales in the next 5 years. 4 SG&A will be 17% of total sales in the next 5 years. 5 Annual Depreciation and Amortization increases by 60 in each of the next 5 years. 6 Capital expenditure (CapEx) will be 1,200 in each of the next 5 years. 7 8 Income Statement Assumptions: 9 Interest and investment income is 2.5% of the ending Cash & Short-Term Investments in the previous year. 10 Interest rate on debt is 5% based on the ending ST Debt & Curr. Portion LT Debt and Long-Term Debt in the previous year. 11 Tax rate is assumed to be 21%. 12 1,184 million common shares outstanding. 13 Dividend payout ratio = 50% 14 15 Working Capital Assumptions 16 In the next 5 years, AR to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 17 In the next 5 years, Inventory to COGS ratio remains the same as that in the most recent fiscal year (FY2017). 18 In the next 5 years, Other current assets to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 19 In the next 5 years, AP to COGS ratio remains the same as that in the most recent fiscal year (FY2017). 20 In the next 5 years, Income Tax Payable to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 21 In the next 5 years, Other current liabilities to Sales ratio remains the same as that in the most recent fiscal year (FY2017). 22 23 Assets Assumptions: 24 Intangible assets grows at the same rate as sales (7%) in the next 5 years 25 Deferred tax assets grows at the same rate as sales (7%) in the next 5 years 26 Other assets grows at the same rate as sales (7%) in the next 5 years 27 Deferred tax liabilities grows at the same rate as sales (7%) in the next 5 years 28 Other liabilities grows at the same rate as sales (7%) in the next 5 years 29 30 Debt Assumptions 31 Short-term debt increases by 2% in each year. 32 Long-term debt decreases by 8% each year. 33 34 35 36 37 L M N O AI projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provi A B DE F G H J K L M N C 3. Create projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the 1 information provided in the Assumptions worksheet. 2 ***Hint: Working capital accounts may be calculated based on the working capital ratios in FY2017 (See Working Capital Assumptions). ***Hint: Cash account information may be obtained based on the completed cash flow statement (Q4). 3 4 FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2017 5 Assets AR to Sales ratio 6 Cash & Short-Term Investments 2,216 2,538 3,595 Inv to COGS ratio 7 Short-Term Receivables 1.890 2,029 1,952 Other current asset to Sales 8 Inventories 12,748 AP to COGS ratio 11,809 12,549 1,078 9 Other Current Assets 608 Income Tax Payable to Sales 638 18,933 10 Total Current Assets 16,993 17,724 Other current liabilities to Sales 11 Property and equipment, at cost 39,266 40,426 41,414 12 Accumulated depreciation and amortization 17,075 18,512 19,339 13 Net Property, Plant & Equipment 22,191 21,914 22,075 14 Intangible Assets 2,275 2,665 2,093 48 15 Deferred Tax Assets 91 119 16 Other Assets 652 1,144 1,127 17 Total Assets 42,549 42,966 44,529 18 19 Liabilities & Shareholders' Equity 20 ST Debt & Curr. Portion LT Debt 427 1,252 2,761 21 Accounts Payable 6,565 7,000 7,244 22 Income Tax Payable 25 54 36 5,498 23 Other Current Liabilities 5,856 6,135 24 Total Current Liabilities 12,526 14,133 16,194 25 Long-Term Debt 20,888 26 Deferred Tax Liabilities 854 22,349 24,267 296 440 1,965 1,855 2,174 36,233 38,633 43,075 27 Other Liabilities 28 Total Liabilities 29 Common Equity 6,316 4,333 1,454 30 Total Shareholders' Equity 6,316 4,333 1.454 31 Total Equity 6,316 4,333 1,454 32 Total Liabilities & Shareholders' Equity 42,549 42,966 44,529 33 34 35 36 37 38