Please show formula!

Operating Assumptions: Sales will grow at 7% per year in the next 5 years. Cost of sales will be 67% of total sales in the next 5 years. SG&A will be 17% of total sales in the next 5 years. Annual Depreciation and Amortization increases by 60 in each of the next 5 years. Capital expenditure (Capex) will be 1,200 in each of the next 5 years. Income Statement Assumptions: Interest and investment income is 2.5% of the ending Cash & Short-Term Investments in the previous year. Interest rate on debt is 5% based on the ending ST Debt & Curr. Portion LT Debt and Long-Term Debt in the previous year. Tax rate is assumed to be 21%. 1,184 million common shares outstanding. Dividend payout ratio = 50% Working Capital Assumptions In the next 5 years, AR to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Inventory to COGS ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Other current assets to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, AP to COGS ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Income Tax Payable to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Other current liabilities to Sales ratio remains the same as that in the most recent fiscal year (FY2017). Assets Assumptions: Intangible assets grows at the same rate as sales (7%) in the next 5 years Deferred tax assets grows at the same rate as sales (7%) in the next 5 years Other assets grows at the same rate as sales (7%) in the next 5 years Deferred tax liabilities grows at the same rate as sales (7%) in the next 5 years Other liabilities grows at the same rate as sales (7%) in the next 5 years Debt Assumptions Short-term debt increases by 2% in each year. Long-term debt decreases by 8% each year. Valuation Assumptions Future annual cash flow from assets (CFFA) growth rate (beyond 2022) is assumed to be 5%. Weighted average cost of capital (WACC) is 9%. Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Income taxes Net earnings / loss FY2015 FY2016 FY2017 88,519 94,595 100,904 -58,254 -62,282 -66,548 30,265 32,313 34,356 -16,801 -17,132 -17,864 -1,690 -1,754 -1,811 11,774 13,427 14,681 166 36 74 -919 -972 -1,057 11,021 12,491 13,698 -4,012 -4,534 -5,068 7,009 7,957 8,630 2. Create projected income statements for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provided in the Assumptions worksheet. Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Income taxes Net earnings / loss FY2015 FY2016 FY2017 FY2018 88,519 94,595 100,904 107,967 -58,254 -62,282 -66,548 (72,338) 30,265 32,313 34,356 35,629 - 16,801 -17,132 -17,864 (18,354) -1,690 -1,754 -1,811 (1,871) 11,774 13,427 14,681 15,404 166 36 74 79 -919 -972 -1,057 (1,230) 11,021 12,491 13,698 14,253 -4,012 -4,534 -5,068 (5,423) 7,009 7,957 8,630 8,830 FY2019 FY2020 115,525 123,612 (77,402) (82,820) 38,123 40,792 (19,639) (21,014) (1,931) (1,991) 16,553 17,787 85 91 (1,316) (1,408) 15,322 16,469 (5,802) (6,209) 9,519 10,261 FY2021 FY2022 132,265 141,523 (88,617) (94,820) 43,647 46,703 (22,485) (24,059) (2,051) (2,111) 19,111 20,533 97 104 (1,507) (1,612) 17,702 19,024 (6,643) (7,108) 11,059 11,916 Projection Based on assumption Cash and short term investment 1440 2960 3167 0.031388 3388.9 0.031388 3626.127 0.031388 3879.956 0.031388 4151.553 0.031388 4,442.16 0.031388 19440 24597.87 26319.72 28162.10 30133.45 32242.79 short term & long term debt Growth rate in short term & long term debt Short term & long term debt to sales ratio 21140 22988.66 1.08745 0.227827 0.227827 Income tax to sales ratio -0.05023 -0.05023 -0.05023 -0.05023 -0.05023 -0.05023 4. Create projected statements of cash flows based on the projected income statement and balance sheet information. FY2018 FY2019 FY2020 FY2021 FY2022 14,253.05 15,321.73 (1,871.00) (1,931.00) 16,469.43 17,701.66 19,024.34 (1,991.00) (2,051.00) (2,111.00) Operating Activities Net Income Depreciation and amortization Short-Term Receivables Inventories Other Current Assets Deferred Tax Assets Other Assets Accounts Payable Income Tax Payable Other Current Liabilities Deferred Tax Liabilities Other Liabilities Cash Flow from Operations Investing Activities Property and equipment, at cost Intangible Assets Cash Flow from Investments Financing Activities ST Debt & Curr. Portion LT Debt Long-Term Debt Dividend Payout Cash Flow From Financing Net Increase (Decrease) in Cash FY2017 AR to Sales ratio 3. Create projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provided in the Assumptions worksheet. ***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 (04). FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 Assets Cash & Short-Term Investments 2,216 2,538 3,595 Short-Term Receivables 1,890 2,029 1,952 Inventories 11,809 12,549 12,748 Other Current Assets 1,078 608 638 Total Current Assets 16,993 17,724 18,933 Property and equipment, at cost 39,266 40,426 41,414 Accumulated depreciation and amortizatior 17,075 18,512 19,339 Net Property, Plant & Equipment 22,191 21,914 22,075 Intangible Assets 2,665 2,093 2,275 Deferred Tax Assets 48 91 119 Other Assets 652 1,144 1,127 Total Assets 42,549 42,966 44,529 Inv to COGS ratio Other current asset to Sales AP to COGS ratio Income Tax Payable to Sales Other current liabilities to Sales Liabilities & Shareholders' Equity ST Debt & Curr. Portion LT Debt Accounts Payable Income Tax Payable Other Current Liabilities Total Current Liabilities Long-Term Debt Deferred Tax Liabilities Other Liabilities Total Liabilities Common Equity Total Shareholders' Equity Total Equity Total Liabilities & Shareholders' Equity 427 6,565 36 5,498 12,526 20,888 854 1,965 36,233 6,316 6,316 6,316 42,549 1,252 2,761 7,000 7,244 25 54 5,856 6,135 14,133 16,194 22,349 24,267 296 440 1,855 2,174 38,633 43,075 4,333 1,454 4,333 1,454 4,333 1,454 42,966 44,529 Operating Assumptions: Sales will grow at 7% per year in the next 5 years. Cost of sales will be 67% of total sales in the next 5 years. SG&A will be 17% of total sales in the next 5 years. Annual Depreciation and Amortization increases by 60 in each of the next 5 years. Capital expenditure (Capex) will be 1,200 in each of the next 5 years. Income Statement Assumptions: Interest and investment income is 2.5% of the ending Cash & Short-Term Investments in the previous year. Interest rate on debt is 5% based on the ending ST Debt & Curr. Portion LT Debt and Long-Term Debt in the previous year. Tax rate is assumed to be 21%. 1,184 million common shares outstanding. Dividend payout ratio = 50% Working Capital Assumptions In the next 5 years, AR to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Inventory to COGS ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Other current assets to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, AP to COGS ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Income Tax Payable to Sales ratio remains the same as that in the most recent fiscal year (FY2017). In the next 5 years, Other current liabilities to Sales ratio remains the same as that in the most recent fiscal year (FY2017). Assets Assumptions: Intangible assets grows at the same rate as sales (7%) in the next 5 years Deferred tax assets grows at the same rate as sales (7%) in the next 5 years Other assets grows at the same rate as sales (7%) in the next 5 years Deferred tax liabilities grows at the same rate as sales (7%) in the next 5 years Other liabilities grows at the same rate as sales (7%) in the next 5 years Debt Assumptions Short-term debt increases by 2% in each year. Long-term debt decreases by 8% each year. Valuation Assumptions Future annual cash flow from assets (CFFA) growth rate (beyond 2022) is assumed to be 5%. Weighted average cost of capital (WACC) is 9%. Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Income taxes Net earnings / loss FY2015 FY2016 FY2017 88,519 94,595 100,904 -58,254 -62,282 -66,548 30,265 32,313 34,356 -16,801 -17,132 -17,864 -1,690 -1,754 -1,811 11,774 13,427 14,681 166 36 74 -919 -972 -1,057 11,021 12,491 13,698 -4,012 -4,534 -5,068 7,009 7,957 8,630 2. Create projected income statements for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provided in the Assumptions worksheet. Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Income taxes Net earnings / loss FY2015 FY2016 FY2017 FY2018 88,519 94,595 100,904 107,967 -58,254 -62,282 -66,548 (72,338) 30,265 32,313 34,356 35,629 - 16,801 -17,132 -17,864 (18,354) -1,690 -1,754 -1,811 (1,871) 11,774 13,427 14,681 15,404 166 36 74 79 -919 -972 -1,057 (1,230) 11,021 12,491 13,698 14,253 -4,012 -4,534 -5,068 (5,423) 7,009 7,957 8,630 8,830 FY2019 FY2020 115,525 123,612 (77,402) (82,820) 38,123 40,792 (19,639) (21,014) (1,931) (1,991) 16,553 17,787 85 91 (1,316) (1,408) 15,322 16,469 (5,802) (6,209) 9,519 10,261 FY2021 FY2022 132,265 141,523 (88,617) (94,820) 43,647 46,703 (22,485) (24,059) (2,051) (2,111) 19,111 20,533 97 104 (1,507) (1,612) 17,702 19,024 (6,643) (7,108) 11,059 11,916 Projection Based on assumption Cash and short term investment 1440 2960 3167 0.031388 3388.9 0.031388 3626.127 0.031388 3879.956 0.031388 4151.553 0.031388 4,442.16 0.031388 19440 24597.87 26319.72 28162.10 30133.45 32242.79 short term & long term debt Growth rate in short term & long term debt Short term & long term debt to sales ratio 21140 22988.66 1.08745 0.227827 0.227827 Income tax to sales ratio -0.05023 -0.05023 -0.05023 -0.05023 -0.05023 -0.05023 4. Create projected statements of cash flows based on the projected income statement and balance sheet information. FY2018 FY2019 FY2020 FY2021 FY2022 14,253.05 15,321.73 (1,871.00) (1,931.00) 16,469.43 17,701.66 19,024.34 (1,991.00) (2,051.00) (2,111.00) Operating Activities Net Income Depreciation and amortization Short-Term Receivables Inventories Other Current Assets Deferred Tax Assets Other Assets Accounts Payable Income Tax Payable Other Current Liabilities Deferred Tax Liabilities Other Liabilities Cash Flow from Operations Investing Activities Property and equipment, at cost Intangible Assets Cash Flow from Investments Financing Activities ST Debt & Curr. Portion LT Debt Long-Term Debt Dividend Payout Cash Flow From Financing Net Increase (Decrease) in Cash FY2017 AR to Sales ratio 3. Create projected balance sheet for years 2018, 2019, 2020, 2021, and 2022 based on the historical financial statements as well as the information provided in the Assumptions worksheet. ***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 (04). FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 Assets Cash & Short-Term Investments 2,216 2,538 3,595 Short-Term Receivables 1,890 2,029 1,952 Inventories 11,809 12,549 12,748 Other Current Assets 1,078 608 638 Total Current Assets 16,993 17,724 18,933 Property and equipment, at cost 39,266 40,426 41,414 Accumulated depreciation and amortizatior 17,075 18,512 19,339 Net Property, Plant & Equipment 22,191 21,914 22,075 Intangible Assets 2,665 2,093 2,275 Deferred Tax Assets 48 91 119 Other Assets 652 1,144 1,127 Total Assets 42,549 42,966 44,529 Inv to COGS ratio Other current asset to Sales AP to COGS ratio Income Tax Payable to Sales Other current liabilities to Sales Liabilities & Shareholders' Equity ST Debt & Curr. Portion LT Debt Accounts Payable Income Tax Payable Other Current Liabilities Total Current Liabilities Long-Term Debt Deferred Tax Liabilities Other Liabilities Total Liabilities Common Equity Total Shareholders' Equity Total Equity Total Liabilities & Shareholders' Equity 427 6,565 36 5,498 12,526 20,888 854 1,965 36,233 6,316 6,316 6,316 42,549 1,252 2,761 7,000 7,244 25 54 5,856 6,135 14,133 16,194 22,349 24,267 296 440 1,855 2,174 38,633 43,075 4,333 1,454 4,333 1,454 4,333 1,454 42,966 44,529