Answered step by step
Verified Expert Solution
Question
1 Approved Answer
H15 fx A B C D E F G H | J L L N 0 P Q 1 WK Corp Do not insert, delete,
H15 fx A B C D E F G H | J L L N 0 P Q 1 WK Corp Do not insert, delete, move or name any rows, columns, or range. Save your work often. 2 3 4 Do all calculations in the cells of the provided items. Do not add any additional items. Set calculation to Automatic and Enable Iteration (File, Options, Formulas) to handle circular references. To save time, build your formulas for 20x4 so that you can copy them over to 20x5. Note that totals for actual results may appear off due to rounding. INCOME STATEMENT (in thousands of $s) For the years ended Actual 20x1 Actual 20x2 Actual 20x3 Forecast 20x4 Forecast 20x5 Assumptions Net revenues Cost of Goods Sold Gross profit Sales Growth % of Sales 6.50% 42.40% Please enter all expenses as positive #s. 8,816,213 3.725529 5,090,684 9,551,415 4,088,731 5,462,683 9,883,078 4,168,000 5,715,078 Same for Taxes. 5 6 7 8 9 10 11 IS 12 13 13 14 14 15 o 16 17 ir 18 19 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 19.80% 8.10% 11.70% A negative number for an expense would indicate a credit. Selling, general & administration expense Research & development expense Advertising expenses Amortization of intangibles Goodwill impairment expense Restructuring expense Other operating expense (income) Operating earnings (EBIT) % of Sales % growth % of Sales see Additional Assumptions see Additional Assumptions Fixed Fixed 1,887,005 726,623 1,057,284 30,208 166,787 0 31.568 1,191,209 1,756,906 747,580 1,178,867 26,536 0 0 5,464 29,499 1,717,832 2,039,037 866,232 1,282,797 28,130 128,638 6,187 34,225 1,329,831 6,500 34,000 Please enter all expenses as positive #s. Interest expense Interest income Foreign exchange losses (gains) Other income (expense), net Earning before taxes (EBT) % of avg Debt for the year % of avg Cash for the year % of Sales Fixed 5.90% 0.50% 0.27% 12,500 Same for Taxes. 220,491 4,105 22,991 14,256 966,089 215,793 4,397 24,064 12.987 1,495,359 207,044 4,653 27,058 12.501 1,112,883 A negative number for an expense would indicate a credit. Tax Rate 28.80% Income taxes Net Income (Loss) 230.272 735.817 346,084 1.149.274 255,091 857.792 Common stock dividends declared % growth 5.20% 504,546 538,081 579,536 H15 fx A B C D E F G H | J L L N 0 P Q 1 WK Corp Do not insert, delete, move or name any rows, columns, or range. Save your work often. 2 3 4 Do all calculations in the cells of the provided items. Do not add any additional items. Set calculation to Automatic and Enable Iteration (File, Options, Formulas) to handle circular references. To save time, build your formulas for 20x4 so that you can copy them over to 20x5. Note that totals for actual results may appear off due to rounding. INCOME STATEMENT (in thousands of $s) For the years ended Actual 20x1 Actual 20x2 Actual 20x3 Forecast 20x4 Forecast 20x5 Assumptions Net revenues Cost of Goods Sold Gross profit Sales Growth % of Sales 6.50% 42.40% Please enter all expenses as positive #s. 8,816,213 3.725529 5,090,684 9,551,415 4,088,731 5,462,683 9,883,078 4,168,000 5,715,078 Same for Taxes. 5 6 7 8 9 10 11 IS 12 13 13 14 14 15 o 16 17 ir 18 19 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 19.80% 8.10% 11.70% A negative number for an expense would indicate a credit. Selling, general & administration expense Research & development expense Advertising expenses Amortization of intangibles Goodwill impairment expense Restructuring expense Other operating expense (income) Operating earnings (EBIT) % of Sales % growth % of Sales see Additional Assumptions see Additional Assumptions Fixed Fixed 1,887,005 726,623 1,057,284 30,208 166,787 0 31.568 1,191,209 1,756,906 747,580 1,178,867 26,536 0 0 5,464 29,499 1,717,832 2,039,037 866,232 1,282,797 28,130 128,638 6,187 34,225 1,329,831 6,500 34,000 Please enter all expenses as positive #s. Interest expense Interest income Foreign exchange losses (gains) Other income (expense), net Earning before taxes (EBT) % of avg Debt for the year % of avg Cash for the year % of Sales Fixed 5.90% 0.50% 0.27% 12,500 Same for Taxes. 220,491 4,105 22,991 14,256 966,089 215,793 4,397 24,064 12.987 1,495,359 207,044 4,653 27,058 12.501 1,112,883 A negative number for an expense would indicate a credit. Tax Rate 28.80% Income taxes Net Income (Loss) 230.272 735.817 346,084 1.149.274 255,091 857.792 Common stock dividends declared % growth 5.20% 504,546 538,081 579,536
Step 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