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.

May you please provide the formula to complete the assumptions so I can understand what I am doing

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

for 2012

Net sales = 965922*(1+(3%)) = 994899.66 (put "=" and select the specified numbers)

Cost of sales = (714775/965922)* 994899.66 =736218.25

Gross profit = 994899.66 - 736218.25 = 258681.41

SG&A = 96960 * 1.03 = 99868.8

Advertising = 994899.6* 5% = 49744.983

Restructuring & others = 0

loss on termination of defined benefit plan = 0

Acquisition & integration related expenses = 16792/6 = 2798.67 ~2800

Total operating expenses = 99868.8 +49744.983 +0+0+2798.67 = 152412.453

Income from operations = Gross profit - total operating expenses = 258681.41 - 152412.453 = 106268.957

Interest on Avg long term obligation = Please specify, Assume = 23,840

others = 0

Income before taxes = income from operations - interest on LT obligations = 106268.957 - 23840 = 82428.957

Income tax = 33% * 82428.957 = 27202.555

Net Income = 82428.957 - 27202.555 = 55226.402

Total dividends on common stock = net income * 7.89% = 55226.402 * 7.89% = 4357.363

Depreciation = 21600 * 1.03 = 22248

Amortization = 8189 as specified. (Don't amortize for 2017, as specified)

Do the same for all the years from 2013 - 2017. Connect each year cells to previous year cells or the resultant numbers.