Question

I have inserted the formulas needed to calculate the income statement. However I do not know how to enter the formulas for my assumptions that are highlighted on the right.

I am trying to create a forecasted income statement in excel from 2011 (original data) -through 2017 (forecasted data) . Can you help me with this.

Net sales 2011 2012 2013 2014 2015 2016 2017 Assumptions 965,922 forecast forecast forecast forecast forecast forecast Increase Year by 3% Maintain the 2011 proportion of cost of sales 714,775 forecast forecast forecast forecast forecast forecast to sales 251,147 forecast forecast forecast forecast forecast forecast Cost of sales Gross profit Operating Expenses : SG&A Advertising Restructuring and Other Loss on Termination of defined benefit plan 96,960 44,415 Change per the rate of sales growth For each year, percent of sales 5% Consistent with 2011, 0 for each year Consistent with 2011, o for each year Reduce each year by the given amount; 2017 number will be zero 2,800 0 Acquisition and integration related expenses Total Operating Expenses Income from operations 16792 158,167 92,980 23,840 Interest rate on AVERAGE long-term obligations will be 4.5% Consistent with 2011, o for each year Interest expense, net Other Income before income taxes Income taxes (tax benefit) Net income (loss) 69,140 18,923.62 50,216.38 Income tax rate for each year 33% Earnings per share: Basic Diluted 2.28 2.22 Shares used to compute earnings per share: Basic Diluted Determine amount for each year using the same payout ratio as in 2011 7.89% Total dividends on Common stock: Annual dividend per share: 3,962 0.18 Supplementary information (from annual report) Depreciation 21,600 Change per the rate of sales growth 2011 Annual report mentions that the annual amount during 2012-2016 will be 8,189 Amortization 7,865

Answer 1

DATE 2011 Net sales 965 922 Cost of sales 714,775 tross Prolit 251,147 2012 994900 736 226 258.674 2013 2014 2015 102477 lossu89 1087154 758312.78 781061.86 804494 266,424-22 274427.14 282660 2016 1119769 $28629 291140 2017 11.53362 853488 299874 998688 49745 102864.86 51237.35 105950.80 109121.32 112403.19 115775.28 52774.45 54357.7 55988.45 57668.1 0 S6A 96.960 Advertising 44,415 Rest & Other Loss on terminata 4 DBP - AG & integrat go 16,792 16.792 MATINSB.167 Income from op 92.980 Interest 3.840 13992 13992 1636058 95068,2 1192 5592 8392 2792 1119 165290.21 167117.25 101140.0 107309.89 113580.98 11995636 23840 23840 23840 23840 126430.62 23840 23840 other income before por 69,140 ca 140 89-140.93 16116 36 102590.62 712282 235053 773000 25509.00 83469.89 21545.06 29614.52 31718.39 33854,90 Income Tax 18,923.62 50,216.38 47722.9 51791.0) Net income 55904.83 60126.46 64397.97 68735.72 EPS Basic Diluted 2.28 2.22 2.1668 2.1094 2.35 2.29 2.539 2.49 2.73 2.658 2.924 3.12 2.847 3.04 ? Dividends 3962 0.18 3765.33 0.17 4086.31 0.1856 4412:47 0.20 4743.97 02155 5080.99 0.2308 $423.24 0.2463 DPS Shares 22024 Basic | 22024 Diluted 22620 22024 22620 22024 22620 22024 22620 22024 22620 22024 22.620 22620 Deprecia 21 600 22248 22915.44 23602.90 24310.98 2504030 25791.50 Amortizat 7865 8189 8189 8189 8180 &189 8189 Averase lorg term obligations not given. Hence same interest around taken. I Answers rounded olet to nearest whole number).