Answered step by step
Verified Expert Solution
Question
1 Approved Answer
PLEASE SHOW FORMULA USING EXCEL. THANK YOU Income Statement Assumptions: Interest and investment income is 2.5% of the ending Cash & Short-Term Investments in the
PLEASE SHOW FORMULA USING EXCEL. THANK YOU
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% A B D E F G H 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. FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income 88,519 -58,254 30,265 -16,801 -1,690 11,774 166 94,595 100,904 -62,282 -66,548 32,313 34,356 -17,132 -17,864 -1,754 -1,811 13,427 14,681 36 74 -972 Interest expense Pre-Tax Income -919 11,021 -4,012 7,009 -1,057 13,698 -5,068 8,630 12,491 -4,534 7,957 Income taxes Net earnings / loss 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% A B D E F G H 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. FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 Net sales Cost of goods sold Gross profit Selling, general and administrative (SG&A) Depreciation and amortization Operating income (EBIT) Interest and investment income 88,519 -58,254 30,265 -16,801 -1,690 11,774 166 94,595 100,904 -62,282 -66,548 32,313 34,356 -17,132 -17,864 -1,754 -1,811 13,427 14,681 36 74 -972 Interest expense Pre-Tax Income -919 11,021 -4,012 7,009 -1,057 13,698 -5,068 8,630 12,491 -4,534 7,957 Income taxes Net earnings / lossStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started