Question

Below is my forecasted P&L for reference. I need to Forecast the below balance sheet with the following assumptions indicated on the right.

I included formulas to compute the totals however I've 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 given to forecast from 2011 (original data) to 2017 (forecasted data) Can you please help me with this ? (please show calculations so I can learn)

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 $

Answer 1

2011 9,65,922 $ 7.14.775 $ 2,51,147 $ 2012 9,94,900 $ 7,36,226 $ 2,58,674 $ 2013 10,24,747 $ 7,58,313 $ 2,66,434 $ 2014 10,55,489 $ 7,81,062 $ 2,74,427 $ 2015 10,87,154 $ 8,04,494 $ 2,82,660 $ 2016 11,19,768 $ 8.28,629 $ 2,91,140 $ 2017 Assumptions 11,53,361 Increase Every Year by 3% 8,53,487 Maintain the 2011 proportion of cost of sales to sales 2,99,874 96,960 44,415 $ $ 99.869 49,745 . $ $ 1,02,865 $ 51,237 $ - $ 1,09,129 $ 54,358 $ 1,12,403 $ 55,988 $ Net Sales Cost Of Sales Gros Profit Operating Expenses: SGRA Advertisine Restructuring And Other Loss on Termination of Defined benefit Plan Acquisition and integration related Expenses Total Operating Expenses Income From operations Interest Expenses, Net Other Income Before Income Taxes Income Tax (Tax Benefit) net Income (Loss) Earnine Per Share Basic Diluted - 5 16,792 $ 1,58,167 $ 92,980 $ 23,940 $ 1,05,951 $ 52,774 $ - $ - S 8,392 $ 1,67,117 $ 1,07,310 $ 22,050 $ 13,992 $ 1,63,606 $ 95,069 $ 22,050 $ 11,192 $ 1,65,294 $ 1,01,140 $ 22,050 $ - 5,592 1,69,079 1.13.581 22,050 5 $ $ $ $ 2,792 $ 1,71,184 $ 1,19,956 $ 22,050 $ 1,15,775 Change as per rate of Sales Growth 57,664 For Each year, Percentage of Sales 5% Consistent with 2011, o for each year - Consistent with 2011, o for each year . Treduce each year by Elven amount, 2017 number will be 0, 2800 1,73,443 1,26,431 22,050 Interest Rate on Average long term obligations will Be 4.5% Consistent with 2011, 0 for each year 1,04,381 34,446 Income Tax Rate for Yeach Year is 33% 69,935 $ 69,140 18,924 50,216 $ $ 5 73,018 $ 24,096 $ 48,922 $ 79,090 $ 26,100 $ 52.990 $ 85,260 28,136 57,124 $ $ 5 91,531 $ 30,205 S 61,326 5 97,906 $ 32,309 $ 65,597 $ 3.18 2.28 $ 2.22 $ 2.22 $ 2.11 $ 2.41 $ 2.29 $ 2.60 2.47 $ $ 2.79 2.66 $ $ 2.98 2.85 $ $ Shares Used to Compute Earning Per Share Basic Diluted Total Dividents on Common Stock Annual Dividends 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 $ 5,123 Same Payout as in 2011 - 7.89% 0.25 Supplementary Information (From Annaual report) Depriciation Amortisation 21,600 $ 7,865 $ 22,248 $ 8,189 $ 22,915 $ 8,189 $ 23,603 $ 8,189 $ 24,311 $ 8,189 $ 25,040 $ 8.189 $ 25,792 Change per rate of Sales Growth 8,189 2011 annual Reports Indicate the annual amount during 2012 2016 will be Current Assets Cash And Cash Equivalents Trade receivables, Net Inventories Deferred Tax Assets Current 3,112 $ 98,218 $ 1.45.575 $ 13,249 4,749 1,01,165 $ 1,49,942 $ 1,04,199 $ 1,54,441 $ 1,07,325 $ 1,59,074 $ 1,10,545 $ 1,63,846 $ 1.13,862 $ 1,68,761 $ For Each Year a plug number that makes Balance Sheet Balance 1,17,277 Change as per rate of Sales Growth 1,73,824 Change as per rate of Sales Growth the proportion of deferred tax assets to income tax will remain the same: No Change Samne Number each year as in 2011 2,783 2,783 $ 2,783 $ 2,783 $ 2,783 $ - S Prepaid Income Taxes Property Held for Sale prepaid Expenses and Other Current Assets Total Current Assets Restricted Cash 13,102 2,76,039 15,795 $ $ $ 2,783 $ - $ 13,495 $ 2.72.134 $ 16,269 $ 2,783 $ - S 13,900 $ 2,75,323 $ 16,757 $ 14,317 $ 2,83,499 $ 17,250 $ 14,746 $ 2,91,921 $ 17,777 $ 15,189 $ 3,00,595 $ 18,311 $ 15,644 Change as per rate of Sales Growth 3,09,529 18,860 Change as per rate of Sales Growth Consistent with The Recent Trend, Assunme That PPE 4.44.885.49 Tumover(Sales/Yer-End PPE) will Decrease 0.2 each Year Property. Plant and Equipment (Gross) $ 2,47,645 2,77,270.89 3,08,096.21 3,40,190.46 3.73,629.65 4 ,08,497.30 Accumulated Depriciation - Property, Plant and Equipment Property, plant and Equipment (Net) $ $ 1,20,238 1,27,407 $ $ 1,42,486 $ 1,34,785 $ -1,65,401 $ 1,42,695 $ -1,89,00 1,51,186 $ $ -2,13,315 $ 1,60,314 $ -2,38,356 $ 1,70,142 $ -2,64,147 Balance at The Beginning of the year + Depridation Expense for the year 1,80,738

Deferred Income Taxes 3,870 5 4,819 $ 5,220 $ 5,627 $ 6,041 $ 6,462 $ Goodwill Intangible assets not subject to Amortization Intane ble assets subject to Amortization - Gross Accumulated Amortization - other Intangible assets Intangible assets subject to Amortization - Net Other long Term Assets Total Assets 4.07.587 $ 3.01.148 $ 1,63,786 $ -14,079 $ 1,49,707 $ 5,842 $ 12,88,395 $ 4,07,587 $ 3.10.182 $ 1,68,700 $ -22,268 $ 1,46,432 $ 7,017 $ 12,99,255 $ 4,07,587 $ 3.19.489 $ 1,73,761 $ -30,457 $ 1,43,304 $ 7,259 $ 13,17,632 $ 4,07,587 3.29,073 1,78,973 38,646 1,40,327 7,476 13,42,035 $ S $ $ $ $ $ 4,07,587 $ 3.38.945 S 1,84,343 $ 46,835 $ 1,37,508 $ 7,701 $ 13,67,793 $ 4,07,587 $ 3.49,113 $ 1,89,873 $ 55,024 $ 1,34,449 $ 7,932 $ 13,94,990 $ 6,889 the proportion of deferred Income Taxus to income tax will remain the sal No Change Samne Number each year as in 2011 4,07,587 3,59,586 Change as per rate of Sales Growth 1,95,569 Change as per rate of Sales Growth -53,213 Balance at The Beginning of the year. Amortisation Expense for the year 1,32,356 8,170 Change as per rate of Sales Growth 14,23,716 $ Liabilities and Stockholders Equity Current Liabilities Current Portion of Long Term Debt Accounts Payable and Accrcued Liabilities Payable to Growers Total Current Uablities Long Term Obligations Deferred Income Taxes Othere Liabilities 41,700 1,53,882 Change as per rate of Sales Growth 18,133 Change as per rate of Sales Growth 41,700 $ 1,28,874 $ 15,186 $ 1,85,760 $ 4,90,001 $ 1,31,870 $ 25,969 $ 41,700 $ 1,32,740 $ 15,642 S. 1,90,082 $ 4,90,001 $ 1,35,826 $ 26,748 $ 41,700 $ 1,36,722 $ 16,111 $ 1,94,533 $ 4,90,001 $ 1,39,901 $ 27,551 $ 41,700 $ 1,40,824 $ 16,594 $ 1,99,118 $ 4,90,001 $ 1,44,098 $ 28,377 $ 41,700 $ 1,45,049 $ 17,092 $ 2,03,841 $ 4,90,001 $ 1,48,421 $ 29,228 $ 41,700 $ 1,49,400 $ 17,605 S 2,08,705 $ 4,90,001 $ 1,52,873 $ 30,105 $ 4,90,001 Balance at Beginning of Year+ New Debts Issued-Debts Repaid During the 1,57,460 Change as per rate of Sales Growth 31,008 Change as per rate of Sales Growth Stockholders Equity Common Stock, $0.001 Par Value Treasury Stock, At Cost 22 $ -6.967 $ 22 $ 6.867 $ 22 S 6,867 $ 22 6,867 S $ 22 , AG7 S $ 22 S 6,867 $ G 22 No Change Same Number each year as in 2011 5,222 2011 footnotes indicate that APIC increased by $1,803, assume the 3,28,901 same increase each year 18,500 No Change Samne Number each year as in 2011 1,25,057 Nothing Mentioned, Assumed to be Samo Additional Paid-in Capital (APIC) Accumulated Other Comprehensive Gain/(Loss) Retainind Earnings $ $ $ 3,18,083 $ 18,500 $ 1,25,057 $ 3,19,885 $ 18,500 $ 1,25,057 $ 3,21,689 $ 18,500 $ 1,25,057 $ 3,23,492 $ 18,500 $ 1,25,057 $ 3,25,295 $ 18,500 $ 1,25,057 $ 3,27,098 $ 19,500 $ 1,25,057 $ Total Stockholders Equity $ 4,54,795 $ 4,56,598 $ 4,58,401 $ 4,60,204 $ 4,62,007 $ 4,63,810 For 2012-2016 total of all components of Shareholders Equity, For 2017 4,77,702 Increase total Shareholders Equity by the rate of sales Growth (Terminal $ Total Liabilities and Shareholders Equity $ 12,88,395 $ 12,99,255 $ OS 13,10,387 $ 7,245 $ 13,21,798 $ 20,237 $ 13,33,498 $ 34,295 $ 13,45,495 49,495 $ $ 13,69,886 53,830 Notes Interest Expenses For Long Term Obligations any New debt issued and Repaid During the year has to be derived from Cash Flow - as the same is Treasury Stock, At Cost To Exatly Derivive the same also We need Have Cash Floe Statement as Mentiod in Assumptions Sections

Here The Problem is Solved as per the assumptions, To Solve the Problem with 100% accuracy just a Cash flow is required for one amount i.e treasury stock, for 2011 the Balance Sheet is equal for assets and liabilities