ACCT/561 wk3
helpmepass
Week3ConsolidatedInstructionsandLinks.docxWeek 3 Project: Tootsie, Inc.
Financial Analysis: 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. Incorporate several types of liquidity, solvency, and profitability ratios. Include industry comparisons and list your assumptions.
Accounting Periods: The assigned accounting periods for the historical and projected financial statements and ratio analysis are as follows.
Complete a three year historical analysis for the periods ending 12/31/16, 12/31/17, and 12/31/18 and a three year projected analysis for the periods ending 12/31/19, 12/31/20, and 12/31/21.
Spreadsheet: Complete your historical analysis and projections via an Excel spreadsheet. Enter the income statement for the period 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 team’s financial statements should be consistent and conform with the company’s 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 team’s ratio analysis. The formulas extract data from the financial statements. Incorporate benchmarking ratios and analysis. Further, your team should list all assumptions used to prepare a 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.
The financial statements for Tootsie, Inc. are published via the company’s web site. Alternatively, you can use the following links to the Securities and Exchange Commission. The information included herein contains, in part, income statement and balance sheet information for the periods ending 12/31/16 through 12/31/18.
In order to produce a quality spreadsheet in an efficient manner my suggestion is to perform the following steps.
1. Open the link listed at the bottom of this thread, select financial statements and subsequently select consolidated statement of earnings and retained earnings.
2. At the top left side of the document is the following: Print Document View Excel Document
Select View Excel Document
3. Save and view the Excel document. Included therein is a copy of, in part, the income statement and balance sheet. The income statement tab is titled consolidated statement of earnings. The balance sheet tab is titled consolidated statement of financial position. You will only use the income statement and balance sheet.
4. Copy the income statement and balance sheet information from the downloaded document to a NEW spreadsheet. Income Statement: Accounts listed below net earnings attributable to Tootsie Roll Industries, Inc. are not required. Balance Sheet: Information below Total Liabilities and Shareholders Equity is not required. The new spreadsheet is your working document that will be turned in with your final project.
5. Repeat the above steps to obtain the 2015 and 2016 balance sheets (see link below).
6. End Result: Income Statement - Six Years = Six Columns. Balance Sheet - Seven Years = Seven Columns.
7. Create a separate tab for ratio analysis and assumptions
The following are links to the financial statements. The first SEC internet site includes all required financial statements with the exception of the 2015 and 2016 balance sheet.
Your team needs the 2015 balance sheet in order to, in part, prepare a ratio analysis of the company. Example: If you are computing the 2016 return on assets ratio (net income / average total assets), the denominator is based on total assets as of 1/1/16 and 12/31/16. Total assets as of 1/1/16 is equal to the 12/31/15 balance (2015 balance sheet). To calculate the average you need to add the two numbers and divide by 2.
As discussed, my suggestion is to enter the historical and projected data for the income statement in one tab. Enter the historical and projected data for the balance sheet in a separate tab. Create a separate tab for the ratio analysis and assumptions. I want to encourage everyone in our class to develop/use critical thinking and problem solving skills. As a result, please do not hesitate to use a different format if you believe the presentation makes sense from a preparation and business perspective.
Projections: The following is an article that discusses the fundamentals of common size financial statements and projections. The information contained therein will provide guidance regarding the utilization of financial results (2016 - 2018) in order to prepare projections (2019 - 2021). My suggestion is to use vertical analysis (i.e. as compared to horizontal analysis). With respect to your spreadsheet, add a column between the years 12/31/16, 12/31/2017 and 12/31/2018. Use the column to calculate a vertical analysis for each item listed with the income statement and balance sheet.
http://www.accountingformanagement.org/vertical-analysis-of-financial-statements/
With respect to the income statement, the first step is to express each income statement item (2016 - 2018) as a percentage of sales (vertical analysis). The next step is to forecast sales (2019 - 2021). After you project sales for each year (2019 - 2021) you need to calculate the remaining items listed on the income statement as a percentage of sales. Example: Based on your historical analysis (2016 - 2018) assume product cost of goods sold ranges between 62.72% and 66.48% of sales. My suggestion is to average the years (2016 - 2018). An estimated cost of goods sold percentage is approximately 64.60% of sales. The resulting percentage (64.60%) is then applied to your team’s forecast sales figures for the periods 2019 - 2021 in order to derive projected cost of goods sold. Please see the example included via the link above for further guidance. Finally, provide support and list all assumptions.
The preparation of a projected balance sheet is different compared to an income statement. There are multiple methods to prepare a projected balance sheet. A simplified approach is to average each item on the balance sheet for the year 2016 - 2018. Sum 2016, 2017 and 2018 and divide by three. Example: Investment 2016=67,513 2017=41,606 2018=75,140 Total = 184,259/3=61,420. The result is your 2019 estimate. Averaging may not apply to every account. For instance, you may need to modify certain accounts impacted by your team’s business proposals. Example: An investment in fixed assets. With respect to your projections, there is not necessarily a right or wrong answer. However, I expect your team to use critical thinking and analysis via incorporating quality assumptions.
For projection/calculation purposes, total revenue (i.e. sales) for the periods 2016 - 2018 is as follows.
2016: 521,100
2017: 519,289
2018: 518,920
Projected sales is an estimate based on, in part, prior sales results, management expectations of future growth or lack thereof, and analysis of key metrics such as competition or future changes to your business plan. With respect to this project, your team should discuss the business and develop a conclusion. For instance, if sales typically increase 2% per year then a 10% top line growth rate is excessive unless you can justify why this makes sense. Finally and consistent with project requirements, list the assumptions and factors that influenced your team’s conclusion in a separate tab of your team’s spreadsheet.
The following is an article that will help you understand the process. However, you need to consider the fact that we are forecasting sales for a multinational corporation not a small business.
http://www.entrepreneur.com/article/77674#
Benchmarking: One option is to identify the organization's competitors and separately research historical performance for each company. For additional information please see the link below and select “ANALYSIS”. Select Financial Health and Management Effectiveness.
Using the same internet site, select “RELATED” at the top of the page. The company’s major competitors are listed therein. If you cannot locate the specific information you are looking for my suggestion is to compute key ratios separately via an analysis of their current and prior year(s) financial statements. The process is not time consuming if you set-up an excel spreadsheet with formulas.
http://www.msn.com/en-us/money/stockdetails/analysis/fi-126.1.TR.NYS
For additional financial information related to Mondelez International, Inc. and The Hershey Company review the following internet sites. Select Analysis (Key Statistics / Growth / Profitability / Price Ratios). Financial data is also listed at the bottom and includes financial health and management effectiveness. There is additional competitor data available via selecting the RELATED tab.
https://www.msn.com/en-us/money/stockdetails/analysis/fi-126.1.MDLZ.NAS
https://www.msn.com/en-us/money/stockdetails/analysis/fi-56.1.HSHY34.BSP
Business Plan: Prepare a business plan that includes a synopsis, marketing and sales analysis, product line narrative, funding request/purpose of loan, and a financial summary using the results from your spreadsheet. It is important to collaborate with everyone on the team and discuss how the request/purpose of the loan and financial analysis will impact each section of the business plan.
10-K: The following is a link to the company's most recent 10-K. The information incorporated therein will help you with the preparation of the business plan.
https://www.sec.gov/Archives/edgar/data/98677/000155837019001395/tr-20181231x10k.htm
Annual Report: The following is a link to the annual report. The information incorporated therein will help you with the preparation of the business plan.
Select 2018 as the year to display. The 2018 annual report is listed under the Annual Reports heading.
http://tootsie.com/financials/
For additional details regarding business plans please review the following internet site.
