Question 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.
I need the answers only for the green part in question 3 with the formulas, please. All the information is given. I appreciate it. Thank you.
4. Create projected statements of cash flows based on the projected income statement and balance sheet information. 3. Create projected balance sheet for years 2018,2019,2020,2021, and 2022 based on the historical financial statements as well as the nformation provided in the Assumptions worksheet. Balance Sheet All figures in millions of U.S. Dollar except per share items. \begin{tabular}{|l|l|l|} \hline FY2015 & FY2016 & FY2017 \end{tabular} Assets Cash \& Short-Term Investments Short-Term Receivables \begin{tabular}{|r|r|r|} \hline 2,216 & 2,538 & 3,595 \\ \hline 1,890 & 2,029 & 1,952 \\ \hline 11,809 & 12,549 & 12,748 \\ \hline 1,078 & 608 & 638 \\ \hline 16,993 & 17,724 & 18,933 \\ \hline 39,266 & 40,426 & 41,414 \\ \hline 17,075 & 18,512 & 19,339 \\ \hline 22,191 & 21,914 & 22,075 \\ \hline 2,665 & 2,093 & 2,275 \\ \hline 48 & 91 & 119 \\ \hline 652 & 1,144 & 1,127 \\ \hline 42,549 & 42,966 & 44,529 \\ \hline \end{tabular} 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 \begin{tabular}{|r|r|r|} \hline 427 & 1,252 & 2,761 \\ \hline 6,565 & 7,000 & 7,244 \\ \hline 36 & 25 & 54 \\ \hline 5,498 & 5,856 & 6,135 \\ \hline 12,526 & 14,133 & 16,194 \\ \hline 20,888 & 22,349 & 24,267 \\ \hline 854 & 296 & 440 \\ \hline 1,965 & 1,855 & 2,174 \\ \hline 36,233 & 38,633 & 43,075 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 42,549 & 42,966 & 44,529 \\ \hline \end{tabular} Income Statement All figures in millions of U.S. Dollar except per share items. Net sales Cost of goods sold Gross profit \begin{tabular}{|r|r|r|} \hline FY2015 & FY2016 & \multicolumn{1}{|c|}{ FY2017 } \\ \hline 88,519 & 94,595 & 100,904 \\ \hline58,254 & 62,282 & 66,548 \\ \hline 30,265 & 32,313 & 34,356 \\ \hline \end{tabular} Selling, general and administrative (SG\&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Incometaxes Net earnings / loss \begin{tabular}{|r|r|r|} \hline16,801 & 17,132 & 17,864 \\ \hline1,690 & 1,754 & 1,811 \\ \hline 11,774 & 13,427 & 14,681 \\ \hline 166 & 36 & 74 \\ \hline-919 & -972 & 1,057 \\ \hline 11,021 & 12,491 & 13,698 \\ \hline4,012 & 4,534 & 5,068 \\ \hline 7,009 & 7,957 & 8,630 \\ \hline \end{tabular} 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 8 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. \begin{tabular}{lrrr} & FY2015 & FY2016 & FY2017 \\ \hline Profit margin & 7.92% & 8.41% & 8.55% \\ ROA & 16.47% & 18.52% & 19.38% \\ ROE & 110.97% & 183.64% & 593.54% \\ Debt-to-equity ratio & 5.74 & 8.92 & 29.63 \\ Accounts receivable days & 8 & 8 & 7 \\ Days inventory outstanding & 74 & 71 & 69 \\ Accounts payable days & 41 & 40 & 39 \\ \hline \end{tabular} Project based on assumption Cash and short term investment \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline 1440.00 & 2960.00 & 3167.20 & 3388.90 & 3626.13 & 3879.96 & 4151.55 & 4442.16 \\ \hline & & 0.0314 & 0.0314 & 0.0314 & 0.0314 & 0.0314 & 0.0314 \\ \hline \end{tabular} 4. Create projected statements of cash flows based on the projected income statement and balance sheet information. 3. Create projected balance sheet for years 2018,2019,2020,2021, and 2022 based on the historical financial statements as well as the nformation provided in the Assumptions worksheet. Balance Sheet All figures in millions of U.S. Dollar except per share items. \begin{tabular}{|l|l|l|} \hline FY2015 & FY2016 & FY2017 \end{tabular} Assets Cash \& Short-Term Investments Short-Term Receivables \begin{tabular}{|r|r|r|} \hline 2,216 & 2,538 & 3,595 \\ \hline 1,890 & 2,029 & 1,952 \\ \hline 11,809 & 12,549 & 12,748 \\ \hline 1,078 & 608 & 638 \\ \hline 16,993 & 17,724 & 18,933 \\ \hline 39,266 & 40,426 & 41,414 \\ \hline 17,075 & 18,512 & 19,339 \\ \hline 22,191 & 21,914 & 22,075 \\ \hline 2,665 & 2,093 & 2,275 \\ \hline 48 & 91 & 119 \\ \hline 652 & 1,144 & 1,127 \\ \hline 42,549 & 42,966 & 44,529 \\ \hline \end{tabular} 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 \begin{tabular}{|r|r|r|} \hline 427 & 1,252 & 2,761 \\ \hline 6,565 & 7,000 & 7,244 \\ \hline 36 & 25 & 54 \\ \hline 5,498 & 5,856 & 6,135 \\ \hline 12,526 & 14,133 & 16,194 \\ \hline 20,888 & 22,349 & 24,267 \\ \hline 854 & 296 & 440 \\ \hline 1,965 & 1,855 & 2,174 \\ \hline 36,233 & 38,633 & 43,075 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 6,316 & 4,333 & 1,454 \\ \hline 42,549 & 42,966 & 44,529 \\ \hline \end{tabular} Income Statement All figures in millions of U.S. Dollar except per share items. Net sales Cost of goods sold Gross profit \begin{tabular}{|r|r|r|} \hline FY2015 & FY2016 & \multicolumn{1}{|c|}{ FY2017 } \\ \hline 88,519 & 94,595 & 100,904 \\ \hline58,254 & 62,282 & 66,548 \\ \hline 30,265 & 32,313 & 34,356 \\ \hline \end{tabular} Selling, general and administrative (SG\&A) Depreciation and amortization Operating income (EBIT) Interest and investment income Interest expense Pre-Tax Income Incometaxes Net earnings / loss \begin{tabular}{|r|r|r|} \hline16,801 & 17,132 & 17,864 \\ \hline1,690 & 1,754 & 1,811 \\ \hline 11,774 & 13,427 & 14,681 \\ \hline 166 & 36 & 74 \\ \hline-919 & -972 & 1,057 \\ \hline 11,021 & 12,491 & 13,698 \\ \hline4,012 & 4,534 & 5,068 \\ \hline 7,009 & 7,957 & 8,630 \\ \hline \end{tabular} 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 8 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. \begin{tabular}{lrrr} & FY2015 & FY2016 & FY2017 \\ \hline Profit margin & 7.92% & 8.41% & 8.55% \\ ROA & 16.47% & 18.52% & 19.38% \\ ROE & 110.97% & 183.64% & 593.54% \\ Debt-to-equity ratio & 5.74 & 8.92 & 29.63 \\ Accounts receivable days & 8 & 8 & 7 \\ Days inventory outstanding & 74 & 71 & 69 \\ Accounts payable days & 41 & 40 & 39 \\ \hline \end{tabular} Project based on assumption Cash and short term investment \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline 1440.00 & 2960.00 & 3167.20 & 3388.90 & 3626.13 & 3879.96 & 4151.55 & 4442.16 \\ \hline & & 0.0314 & 0.0314 & 0.0314 & 0.0314 & 0.0314 & 0.0314 \\ \hline \end{tabular}
