Hello - I need assistance with the below. Here is the assignment : I basically just need to know the formula to calculate the projections for 2019,2020 and 2021 for the income statement and balance sheet.
** I don't need help with the financial and business analysis part only the below part (section #1) which is the spreadsheet part***
Prepare a financial and business analysis of Tootsie Roll Industries, Inc. (1,500 to 2,750 words) in order to secure a loan for the company. The loan will increase the company's total liabilities by 10%. The contents of the plan should include the following:
1. Prepare a financial analysis (spreadsheet) in Excel using the company's financial statements. The spreadsheet should include historical financial statements (balance sheet and income statement), projected financial statements (balance sheet and income statement), a three year ratio analysis of the company's historical financial statements, and a three year ratio analysis of the company's projected financial statements. The accounting periods for the historical and projected analysis are provided at the beginning of week three. The ratio analysis should include several types of liquidity, solvency, and profitability ratios. Incorporate industry comparisons with your ratio analysis and list your assumptions. Submit the final spreadsheet with your team's paper.
Here are the directions for the spreadsheet.
Spreadsheet: Complete your historical analysis and projections via an Excel spreadsheet. Enter the income statement for the periods ending 12/31/2016, 12/31/17, 12/31/2018, 12/31/2019, 12/31/2020, and 12/31/2021 in one tab. Enter the balance sheet for the aforementioned years in a separate tab of your spreadsheet. Column A of each tab will list the account names such as, in part, Net Product Sales, Rental and Royalty Revenue, Cash and cash equivalents, etc. Each column thereafter will list the annual accounting numbers. Add a separate column for each year.
Your team is required to include an income statement and balance sheet for each year. The format of your teams financial statements should be consistent and conform with the companys 10-K. With respect to the income statement, include the accounts net product sales through net earnings attributable to Tootsie Roll Industries, Inc. Exclude all accounts listed below net earnings attributable to Tootsie Roll Industries, Inc. Create an additional tab that uses formulas to calculate your teams ratio analysis. The formulas extract data from the financial statements. Incorporate benchmarking ratios and analysis. Further, your team should list all assumptions and issues impacting your financial analysis of the company. The reasons and justifications for the new loan will impact the projected financial statements and related ratio computations for the years 2019 - 2021.
INCOME STATEMENT
BALANCE SHEET 2018 & 2017
BALANCE SHEET 2016 &2015
CONSOLIDATED STATEMENTS OF EARNINGS AND RETAINED EARNINGS - USD (S) shares in Thousands, $ in Thousands 12 Months Ended Dec. 31, 2018Dec. 31, 2017 Dec. 31, 2016 $518,920 $519,289 $521,100 Revenue 330,747 Costs 327,383 321,888 Gross Margin 188,173 191,906 199,212 Selling, marketing and administrative expenses 117,691 121,484 107,979 Earnings from operations 70,482 70,422 91,233 Other income, net 2,724 14,139 6,679 Earnings before income taxes 84,561 73,206 97,912 Provision for income taxes 3,907 16,401 30,593 67,319 Net earnings 56,805 80,654 (210) $80,864 $1.24 Less: Net earnings (loss) attributable to noncontrolling interests (191) $67,510 $56,893 Net earnings attributable to Tootsie Roll Industries, Inc. $0.89 $1.02 Net earnings attributable to Tootsie Roll Industries, Inc. per share (in dollars per share) Average number of shares outstanding (in shares) 64,216 65,048 65,955 $57,225 $43,833 $52,349 Retained earnings at beginning of period Net earnings attributable to Tootsie Roll Industries, Inc. 56,893 80,864 67,510 CONSOLIDATED STATEMENTS OF FINANCIAL POSITION - USD (S) $ in Thousands Dec. 31, 2018Dec. 31, 2017 CURRENT ASSETS: Cash and cash equivalents $110,899 $96,314 Restricted cash 388 406 Investments 75,140 41,606 Accounts receivable trade, less allowances of $1,820 and $1,921 49,777 47,354 Other receivables 5,425 2,941 Inventories Finished goods & work-in-process Raw material & supplies 31,922 32,159 22,365 22,905 1 Income taxes receivable 12,974 Prepaid expenses 12,014 10,377 Total current assets 270,920 304,046 4 PROPERTY, PLANT& EQUIPMENT, at cost: 5 Land 21,726 21,962 Buildings 121,780 118,491 Machinery & equipment 381,665 401,037 4,866 3 Construction in progressS 3,408 Property, plant and equipment, gross 547,951 526,984 348,012 Less-accumulated depreciation 361,850 1 Net property, plant and equipment 186,101 178,972 OTHER ASSETS: Goodwill 73,237 73,237 4 Trademarks 175,024 175,024 5 Investments 170,409 190,510 5 Split dollar officer life insurance 26,042 26,042 7 Prepaid expenses and other 11,980 15,817 Deferred income taxes 522 424 Total other assets 457,214 481,054 0 Total assets 947,361 930,946 1 CURRENT LIABILITIES: 2Accounts payablee 11,817 11,928 Bank loans 373 440 4Dividends payable 5,772 5,660 5 Accrued liabilities 45,157 42,849 6 Postretirement health care benefits 603 580 Total current liabilities 61,391 63,788 9 Total current liabilities 72,062 63,561 0 NONCURRENT LIABILITIES: 1 Deferred income taxes 46,060 47,594 2 Bank loans 383 230 3 Postretirement health care 11,615 10,952 4 Industrial development bonds 7,500 7,500 5 Liability for uncertain tax positions 5,185 5,101 74,412 6 Deferred compensation and other liabilities 66,843 7 Total noncurrent liabilities 145,002 138,373 8 TOOTSIE ROLL INDUSTRIES, INC. SHAREHOLDERS' EQUITY: 9 Capital in excess of par value 646,768 622,882 0 Retained earnings 43,833 52,349 (17,364) 1 Accumulated other comprehensive loss (20,246) 2 Treasury stock (at cost83 shares and 80 shares, respectively 3 Total Tootsie Roll Industries, Inc. shareholders' equity (1,992) 711,364 (1,992) 698,183 4 Noncontrolling interests 174 365 5 Total equity 711,538 698,548 6 Total liabilities and shareholders' equity 920,101 908,983 CONSOLIDATED STATEMENTS OF Financial Position - USD () $ in Thousands Dec. 31, 2016 Dec. 31, 2015 CURRENT ASSETS: $119,145 $126,145 Cash & cash equivalents Restricted cash 382 395 Investments 67,513 42,155 Accounts receivable trade, less allowances of $1,884 and $2,225 42,964 51,010 Other receivables 3,299 2,772 Inventories: Finished goods & work-in-process 34,631 35,032 0 Raw material & supplies 22,900 27,231 1 Prepaid expenses 7,146 5,935 2 Deferred income taxes 1,320 3,131 3 Total current assets 299,300 293,806 4 PROPERTY, PLANT& EQUIPMENT, at cost: 5 Land 22,081 22,188 6 Buildings 116,398 114,562 7 Machinery & equipment 357,627 369,802 3,546 8 Construction in progresS 5,158 9 Property, plant and equipment, gross 511,827 499,535 0 Less-accumulated depreciation 330,922 314,949 1 Net property, plant and equipment 180,905 184,586 2 OTHER ASSETS: 3 Goodwill 73,237 73,237 4 Trademarks 175,024 175,024 5 Investments 164,665 152,930 6 Split dollar officer life insurance 26,042 26,042 7 Prepaid expenses and other 3,050 602 8 Deferred income taxes 326 308 9 Total other assets 439,896 430,591 0 Total assets 920,101 908,983 1 CURRENT LIABILITIES: 2 Accounts payable 10,320 11,322 3 Bank loan 336 4 Dividends payable 5,573 5,486 5 Accrued liabilities 50,117 46,300 6 Postretirement health care 513 448 7 Income taxes payable 4,436 8 Deferred income taxes 519 9 Total current liabilities 72,062 63,561 0 NONCURRENT LIABILITIES: 1 Deferred income taxes 46,060 47,594 2 Bank loans 383 230 3 Postretirement health care 11,615 10,952 4 Industrial development bonds 7,500 7,500 5 Liability for uncertain tax positions 5,185 5,101 74,412 6 Deferred compensation and other liabilities 66,843 7 Total noncurrent liabilities 145,002 138,373 8 TOOTSIE ROLL INDUSTRIES, INC. SHAREHOLDERS' EQUITY: 9 Capital in excess of par value 646,768 622,882 0 Retained earnings 43,833 52,349 (17,364) 1 Accumulated other comprehensive loss (20,246) 2 Treasury stock (at cost83 shares and 80 shares, respectively 3 Total Tootsie Roll Industries, Inc. shareholders' equity (1,992) 711,364 (1,992) 698,183 4 Noncontrolling interests 174 365 5 Total equity 711,538 698,548 6 Total liabilities and shareholders' equity 920,101 908,983 CONSOLIDATED STATEMENTS OF EARNINGS AND RETAINED EARNINGS - USD (S) shares in Thousands, $ in Thousands 12 Months Ended Dec. 31, 2018Dec. 31, 2017 Dec. 31, 2016 $518,920 $519,289 $521,100 Revenue 330,747 Costs 327,383 321,888 Gross Margin 188,173 191,906 199,212 Selling, marketing and administrative expenses 117,691 121,484 107,979 Earnings from operations 70,482 70,422 91,233 Other income, net 2,724 14,139 6,679 Earnings before income taxes 84,561 73,206 97,912 Provision for income taxes 3,907 16,401 30,593 67,319 Net earnings 56,805 80,654 (210) $80,864 $1.24 Less: Net earnings (loss) attributable to noncontrolling interests (191) $67,510 $56,893 Net earnings attributable to Tootsie Roll Industries, Inc. $0.89 $1.02 Net earnings attributable to Tootsie Roll Industries, Inc. per share (in dollars per share) Average number of shares outstanding (in shares) 64,216 65,048 65,955 $57,225 $43,833 $52,349 Retained earnings at beginning of period Net earnings attributable to Tootsie Roll Industries, Inc. 56,893 80,864 67,510 CONSOLIDATED STATEMENTS OF FINANCIAL POSITION - USD (S) $ in Thousands Dec. 31, 2018Dec. 31, 2017 CURRENT ASSETS: Cash and cash equivalents $110,899 $96,314 Restricted cash 388 406 Investments 75,140 41,606 Accounts receivable trade, less allowances of $1,820 and $1,921 49,777 47,354 Other receivables 5,425 2,941 Inventories Finished goods & work-in-process Raw material & supplies 31,922 32,159 22,365 22,905 1 Income taxes receivable 12,974 Prepaid expenses 12,014 10,377 Total current assets 270,920 304,046 4 PROPERTY, PLANT& EQUIPMENT, at cost: 5 Land 21,726 21,962 Buildings 121,780 118,491 Machinery & equipment 381,665 401,037 4,866 3 Construction in progressS 3,408 Property, plant and equipment, gross 547,951 526,984 348,012 Less-accumulated depreciation 361,850 1 Net property, plant and equipment 186,101 178,972 OTHER ASSETS: Goodwill 73,237 73,237 4 Trademarks 175,024 175,024 5 Investments 170,409 190,510 5 Split dollar officer life insurance 26,042 26,042 7 Prepaid expenses and other 11,980 15,817 Deferred income taxes 522 424 Total other assets 457,214 481,054 0 Total assets 947,361 930,946 1 CURRENT LIABILITIES: 2Accounts payablee 11,817 11,928 Bank loans 373 440 4Dividends payable 5,772 5,660 5 Accrued liabilities 45,157 42,849 6 Postretirement health care benefits 603 580 Total current liabilities 61,391 63,788 9 Total current liabilities 72,062 63,561 0 NONCURRENT LIABILITIES: 1 Deferred income taxes 46,060 47,594 2 Bank loans 383 230 3 Postretirement health care 11,615 10,952 4 Industrial development bonds 7,500 7,500 5 Liability for uncertain tax positions 5,185 5,101 74,412 6 Deferred compensation and other liabilities 66,843 7 Total noncurrent liabilities 145,002 138,373 8 TOOTSIE ROLL INDUSTRIES, INC. SHAREHOLDERS' EQUITY: 9 Capital in excess of par value 646,768 622,882 0 Retained earnings 43,833 52,349 (17,364) 1 Accumulated other comprehensive loss (20,246) 2 Treasury stock (at cost83 shares and 80 shares, respectively 3 Total Tootsie Roll Industries, Inc. shareholders' equity (1,992) 711,364 (1,992) 698,183 4 Noncontrolling interests 174 365 5 Total equity 711,538 698,548 6 Total liabilities and shareholders' equity 920,101 908,983 CONSOLIDATED STATEMENTS OF Financial Position - USD () $ in Thousands Dec. 31, 2016 Dec. 31, 2015 CURRENT ASSETS: $119,145 $126,145 Cash & cash equivalents Restricted cash 382 395 Investments 67,513 42,155 Accounts receivable trade, less allowances of $1,884 and $2,225 42,964 51,010 Other receivables 3,299 2,772 Inventories: Finished goods & work-in-process 34,631 35,032 0 Raw material & supplies 22,900 27,231 1 Prepaid expenses 7,146 5,935 2 Deferred income taxes 1,320 3,131 3 Total current assets 299,300 293,806 4 PROPERTY, PLANT& EQUIPMENT, at cost: 5 Land 22,081 22,188 6 Buildings 116,398 114,562 7 Machinery & equipment 357,627 369,802 3,546 8 Construction in progresS 5,158 9 Property, plant and equipment, gross 511,827 499,535 0 Less-accumulated depreciation 330,922 314,949 1 Net property, plant and equipment 180,905 184,586 2 OTHER ASSETS: 3 Goodwill 73,237 73,237 4 Trademarks 175,024 175,024 5 Investments 164,665 152,930 6 Split dollar officer life insurance 26,042 26,042 7 Prepaid expenses and other 3,050 602 8 Deferred income taxes 326 308 9 Total other assets 439,896 430,591 0 Total assets 920,101 908,983 1 CURRENT LIABILITIES: 2 Accounts payable 10,320 11,322 3 Bank loan 336 4 Dividends payable 5,573 5,486 5 Accrued liabilities 50,117 46,300 6 Postretirement health care 513 448 7 Income taxes payable 4,436 8 Deferred income taxes 519 9 Total current liabilities 72,062 63,561 0 NONCURRENT LIABILITIES: 1 Deferred income taxes 46,060 47,594 2 Bank loans 383 230 3 Postretirement health care 11,615 10,952 4 Industrial development bonds 7,500 7,500 5 Liability for uncertain tax positions 5,185 5,101 74,412 6 Deferred compensation and other liabilities 66,843 7 Total noncurrent liabilities 145,002 138,373 8 TOOTSIE ROLL INDUSTRIES, INC. SHAREHOLDERS' EQUITY: 9 Capital in excess of par value 646,768 622,882 0 Retained earnings 43,833 52,349 (17,364) 1 Accumulated other comprehensive loss (20,246) 2 Treasury stock (at cost83 shares and 80 shares, respectively 3 Total Tootsie Roll Industries, Inc. shareholders' equity (1,992) 711,364 (1,992) 698,183 4 Noncontrolling interests 174 365 5 Total equity 711,538 698,548 6 Total liabilities and shareholders' equity 920,101 908,983
