Hello,
I need to Forecast this balance sheet with the following assumptions indicated on the right. The growth rate of sales is 3% and income tax is at 33%
I included formulas to compute the totals however Ive noticed that my balance sheet is out of balance (but these are the figures I was given)
I need help inserting the formulas for the assumptions give to forecast from 2011 (original data) to 2017 (forecasted data) Can you please help me with this ?
2011 965,922 $ Net sales $ 2012 2013 2014 2015 2016 2017 Assumptions 994,900 $1,024,747 $1,055,489 $1,087,134 $1,119,769 $1,153,362 Increase Year by 3% Maintain the 2011 proportion of cost of sales 736,226 $ 758,313 $ 781,062 $ 804,494 $ 828,629 $ 853,988 to sales 258,674 $ 266,434 $ 274,427 $ 282,640 $ 291,140 $ 299,374 $ $ 214,775 $ 251,147 $ Cost of sales Gross profit Operating Expenses : SG&A Advertising Restructuring and Other Loss on Termination of defined benefit plan $ 96,960 44,415 $ $ 99,869 49,745 $ $ 102,865 $ 51,237 $ 105,951 52,774 $ $ Acquisition and integration related expenses Total Operating Expenses Income from operations $ $ $ 16,792 $ 158,167 $ 92,980 $ 13,992 $ 11,192 $ 8,392 163,606 $ 165,294 $ 167,117 95,068 $ 101,140 $ 107,310 $ 109,129 $ 112,403 $ 115,775 Change per the rate of sales growth $ 54,358 $ 55,988 $ 57,668 For each year, percent of sales 5% Consistent with 2011, o for each year Consistent with 2011, o for each year Reduce each year by the given amount; 2017 $ 5,592 $ 2,792 $ . number will be zero 2,800 $ 169,079 $ 171,184 $ 173,443 $ 113,561 $ 119,956 $ 126,431 Interest rate on AVERAGE long-term $ 23,840 $ 23,840 $ 23,840 obligations will be 4.5% Consistent with 2011, 0 for each year $ 89,741 $ 96,116 $ 102,591 $ 29,615 $ 31,718 $ 33,855 Income tax rate for each year 33% $ 60,126 $ 64,398 $ 68,736 23,840 $ 23,840 $ 23,840 $ 23,840 $ $ Interest expense, net Other Income before income taxes Income taxes (tax benefit) Net income (loss) S 09, $ $ 69,140 18,924 50,216 $ $ $ 71,228 $ 23,505 $ 47,723 $ 77,300 $ 25,509 $ 51,791 $ 83,470 27,545 55,925 Earnings per share: Basic Diluted 2.2800 $ 2.2200 $ 2.1668 2.1094 $ $ 2.3500 2.2900 $ $ 2.5390 $ 2.4700 $ 2.7300 2.6580 $ $ 2.9240 2.8470 $ $ 3.1200 3.0400 $ Shares used to compute earnings per share: Basic Diluted Total dividends on Common stock: Annual dividend per share: $ $ 3,962 $ 0.18 $ 3,765 $ 0.17 $ 4,086 0.19 $ $ 4,412 0.20 $ $ 4,744 $ 0.22 $ 5,081 $ 0.23 $ Determine amount for each year using the 5,423 same payout ratio as in 2011 7.89% 0.25 Supplementary information (from annual report) Depreciation $ 21,600 $ 22,248 $ 22,915 $ 23,603 $ 24,311 $ 25,040 $ 25,792 Change per the rate of sales growth 2011 Annual report mentions that the annual 8,189 amount during 2012-2016 will be 8,189 Amortization $ 7,865 $ 8,189 $ 8,189 $ 8,189 $ 8,189 $ 8,189 $ 2011 2012 2013 2014 2015 2016 2017 Assumptions Current Assests: Cash and cash equivalents Trade receivables, net ! Inventories 3,112 98,218 145,575 For each year, a plug number, that makes the balance sheet balance Change per the rate of sales growth Change per the rate of sales growth The proportion of deferred tax assets to income tax expenses will remain the same as in 2011 No change; the same number each year as in 2011 13,249 2,783 Deferred tax assets, current Prepaid income taxes Property Held for Sale Prepaid expenses and other current assets Total Current Assets Restricted cash Change per the rate of sales growth 13,102 276,039 15,795 Property, Plant and Equipment (gross) Accumulated Depreciation - Property, plant and equipment Property, Plant and Equipment (Net) 247,645 (120,238) 127,407 Change per the rate of sales growth Consistent with the recent trend, assume that PPE Turnover (Sales/Year-end PPE) will decrease by 0.2 each year Balance at the beginning of the year + Depreciation expenses for the year Deferred income taxes Goodwill Intangible assets not subject to amortization Intangible assets subject to amortization, gross Accumulated amortization-other intangible assets Intangible assets subject to amortization, net Other long-term assets Total Assets: 3,870 407,587 301,148 163,786 (14,079) 149,707 6,842 1,289,470 The proportion of deferred income taxes to income tax expenses will remain the same as in 2011 No change; the same number each year as in 2011 Change per the rate of sales growth Change per the rate of sales growth Balance at the beginning of the year + Amortization expenses for the year S Change per the rate of sales growth $ $ $ No change; the same number each year as in 2011 Change per the rate of sales growth Change per the rate of sales growth Liabilities and Stockholders Equity Current Liabilities: Current portion of long-term debt Accounts payable and accrued liabilities Payable to growers Total Current Liabilities Long-term obligations Deferred income taxes Other liabilities Stockholders Equity | Common stock, $0.001 par value 41,700 128,874 15,186 185,760 490,001 131,870 25,969 $ Balance at the beginning of the year +New debt issued - Repayments of debt Change per the rate of sales growth Change per the rate of sales growth $ $ 22 Treasury stock, at cost (6,867) No change; the same number each year as in 2011 For 2012-2016, treasury stock will be increased by the amount purchased each year (provided in the Statement of Cash Flows). For 2017, total treasury stock purchased should be total stockholders' equity minus all other components of stockholders' equity 2011 footnote mentions that APIC increased by $1,803. Assume the same increase! for each year. No change; the same number each year as in 2011 Additional paid-in capital (APIC) Accumulated other comprehensive gain/loss) | Retained Earnings 318,083 18,500 125,057 For 2012-2016, total of all components of stockholders' equity. For 2017, increase total stockholders' equity by the rate of sales growth (terminal growth assumption) | Total Stockholders Equity Total Liabilities and Stockholders' Equity 461,640 --- 1,288,373 2011 965,922 $ Net sales $ 2012 2013 2014 2015 2016 2017 Assumptions 994,900 $1,024,747 $1,055,489 $1,087,134 $1,119,769 $1,153,362 Increase Year by 3% Maintain the 2011 proportion of cost of sales 736,226 $ 758,313 $ 781,062 $ 804,494 $ 828,629 $ 853,988 to sales 258,674 $ 266,434 $ 274,427 $ 282,640 $ 291,140 $ 299,374 $ $ 214,775 $ 251,147 $ Cost of sales Gross profit Operating Expenses : SG&A Advertising Restructuring and Other Loss on Termination of defined benefit plan $ 96,960 44,415 $ $ 99,869 49,745 $ $ 102,865 $ 51,237 $ 105,951 52,774 $ $ Acquisition and integration related expenses Total Operating Expenses Income from operations $ $ $ 16,792 $ 158,167 $ 92,980 $ 13,992 $ 11,192 $ 8,392 163,606 $ 165,294 $ 167,117 95,068 $ 101,140 $ 107,310 $ 109,129 $ 112,403 $ 115,775 Change per the rate of sales growth $ 54,358 $ 55,988 $ 57,668 For each year, percent of sales 5% Consistent with 2011, o for each year Consistent with 2011, o for each year Reduce each year by the given amount; 2017 $ 5,592 $ 2,792 $ . number will be zero 2,800 $ 169,079 $ 171,184 $ 173,443 $ 113,561 $ 119,956 $ 126,431 Interest rate on AVERAGE long-term $ 23,840 $ 23,840 $ 23,840 obligations will be 4.5% Consistent with 2011, 0 for each year $ 89,741 $ 96,116 $ 102,591 $ 29,615 $ 31,718 $ 33,855 Income tax rate for each year 33% $ 60,126 $ 64,398 $ 68,736 23,840 $ 23,840 $ 23,840 $ 23,840 $ $ Interest expense, net Other Income before income taxes Income taxes (tax benefit) Net income (loss) S 09, $ $ 69,140 18,924 50,216 $ $ $ 71,228 $ 23,505 $ 47,723 $ 77,300 $ 25,509 $ 51,791 $ 83,470 27,545 55,925 Earnings per share: Basic Diluted 2.2800 $ 2.2200 $ 2.1668 2.1094 $ $ 2.3500 2.2900 $ $ 2.5390 $ 2.4700 $ 2.7300 2.6580 $ $ 2.9240 2.8470 $ $ 3.1200 3.0400 $ Shares used to compute earnings per share: Basic Diluted Total dividends on Common stock: Annual dividend per share: $ $ 3,962 $ 0.18 $ 3,765 $ 0.17 $ 4,086 0.19 $ $ 4,412 0.20 $ $ 4,744 $ 0.22 $ 5,081 $ 0.23 $ Determine amount for each year using the 5,423 same payout ratio as in 2011 7.89% 0.25 Supplementary information (from annual report) Depreciation $ 21,600 $ 22,248 $ 22,915 $ 23,603 $ 24,311 $ 25,040 $ 25,792 Change per the rate of sales growth 2011 Annual report mentions that the annual 8,189 amount during 2012-2016 will be 8,189 Amortization $ 7,865 $ 8,189 $ 8,189 $ 8,189 $ 8,189 $ 8,189 $ 2011 2012 2013 2014 2015 2016 2017 Assumptions Current Assests: Cash and cash equivalents Trade receivables, net ! Inventories 3,112 98,218 145,575 For each year, a plug number, that makes the balance sheet balance Change per the rate of sales growth Change per the rate of sales growth The proportion of deferred tax assets to income tax expenses will remain the same as in 2011 No change; the same number each year as in 2011 13,249 2,783 Deferred tax assets, current Prepaid income taxes Property Held for Sale Prepaid expenses and other current assets Total Current Assets Restricted cash Change per the rate of sales growth 13,102 276,039 15,795 Property, Plant and Equipment (gross) Accumulated Depreciation - Property, plant and equipment Property, Plant and Equipment (Net) 247,645 (120,238) 127,407 Change per the rate of sales growth Consistent with the recent trend, assume that PPE Turnover (Sales/Year-end PPE) will decrease by 0.2 each year Balance at the beginning of the year + Depreciation expenses for the year Deferred income taxes Goodwill Intangible assets not subject to amortization Intangible assets subject to amortization, gross Accumulated amortization-other intangible assets Intangible assets subject to amortization, net Other long-term assets Total Assets: 3,870 407,587 301,148 163,786 (14,079) 149,707 6,842 1,289,470 The proportion of deferred income taxes to income tax expenses will remain the same as in 2011 No change; the same number each year as in 2011 Change per the rate of sales growth Change per the rate of sales growth Balance at the beginning of the year + Amortization expenses for the year S Change per the rate of sales growth $ $ $ No change; the same number each year as in 2011 Change per the rate of sales growth Change per the rate of sales growth Liabilities and Stockholders Equity Current Liabilities: Current portion of long-term debt Accounts payable and accrued liabilities Payable to growers Total Current Liabilities Long-term obligations Deferred income taxes Other liabilities Stockholders Equity | Common stock, $0.001 par value 41,700 128,874 15,186 185,760 490,001 131,870 25,969 $ Balance at the beginning of the year +New debt issued - Repayments of debt Change per the rate of sales growth Change per the rate of sales growth $ $ 22 Treasury stock, at cost (6,867) No change; the same number each year as in 2011 For 2012-2016, treasury stock will be increased by the amount purchased each year (provided in the Statement of Cash Flows). For 2017, total treasury stock purchased should be total stockholders' equity minus all other components of stockholders' equity 2011 footnote mentions that APIC increased by $1,803. Assume the same increase! for each year. No change; the same number each year as in 2011 Additional paid-in capital (APIC) Accumulated other comprehensive gain/loss) | Retained Earnings 318,083 18,500 125,057 For 2012-2016, total of all components of stockholders' equity. For 2017, increase total stockholders' equity by the rate of sales growth (terminal growth assumption) | Total Stockholders Equity Total Liabilities and Stockholders' Equity 461,640 --- 1,288,373
