Can you provide an analysis of each of the red tabs included in the spreadsheet.

1.Short Term Ratios Analyses

2.Long Term Ratios Analyses

3.Profitability Ratios Analyses

4.question on tab 4

check over and better my DuPont explanation

5.Stock Ratios Analyses

6.Vertical and Horizontal Analyses

1.Explain why not all the Fiscal Years Ended listed for National Beverage below do not fall on the last day of April.

2.For the Vertical and Horizontal Analyses only, explain several notable Ratios (you do not have to explain all of them)that have the most meaning to prove the analysis you are trying to make about the company.

The annual reports are included as well if needed.

You will see below 3 "green" tabs and 6 "red tabs". Green Tabs Represent the information you will need to compute the answers required in the r The Red tabs represent where you will calculate your ratios. In each box where a calcuclated amount is required be sure to show the excel formula of the r calculate. I have prepared one ratio for you in the 1-Short Term Tab as an example Legend for Tabs Listed Below answers required in the red tabs. the excel formula of the ratio you m Tab as an example Green Tabs - Information Required for Red Tab Answers Historical Stock Prices found on "1-Instructions" 1. IS - Income Statement 2. BS - Balance Sheet 3. SCF - Statement of Cash Flows Red Tabs - You will supply your answers in these tabs In the Boxes you will put your calculations for the ratio required 1. Short-Term - Answers to your short term ratios 2. Long-Term - Answers to your long term ratios 3. Profitability - Answers to your profitability ratios 4. DuPont Analysis - Answers to your DuPont Analysis ratios 5. Stock - Answers to your Stock ratios 6. Vert-Horiz Analysis - Information to be used in your Word D ratio analysis will be performed here since they have already b red for Red Tab Answers n "1-Instructions" ws answers in these tabs alculations for the ratio required o your short term ratios o your long term ratios o your profitability ratios o your DuPont Analysis ratios o your Stock ratios ation to be used in your Word Document Analysis and no d here since they have already been computed. You will prepare in the additional Word Document this type of an analysis to complete the as additional Word Document that you will prepare with your written analyses Partial Example of Short Term Analysis: The current ratio for X Company has been decreasing across fiscal 20X4 and 20X1 im To better show this trend, the working capital has also decreased from $2000 in 20X1 t decreasing during this same period. The decline may mean that X Company may not h the company, in their annual reports, will tell you why this is happening if possible to pro more meaningful way.) The inventory turnover for X Company has been increasing (or decreasing if that is wh went from X in 20X1 to X+ in 20X4. The increase (or decrease if that's what happened) in the number of days to collect rec future periods compound the ability to pay current liabilities as they become due. This is only a sampling of an example. This kind of analysis can be done for each of the ratios computed in the section analysis to complete the assignment. You will turn in 2 documents for this assignment. (1) This Excel D n analyses fiscal 20X4 and 20X1 implying that the company is less liquid and is not the best short term risk. sed from $2000 in 20X1 to $500 in 20X4. Finally, it can also be seen that the acid test ratio has al at X Company may not have enough short term assets to cover additional liabilities. (You may a appening if possible to provide more credence for your answer. You can do this for any ratio you w r decreasing if that is what is happening) across the years provided shows greater efficiency in its ber of days to collect receivable balances may show that the economy in this industry is slowing d they become due. omputed in the section you are working on. signment. (1) This Excel Document (2) An the best short term risk. the acid test ratio has also been al liabilities. (You may also want to see if do this for any ratio you want to explain in a ws greater efficiency in its buying. This ratio n this industry is slowing down which may in National Beverage Corp. Annual Reports Income Statement (Amounts in thousands except per share data) Fiscal Year Ended Net sales Cost of sales Gross profit Expenses: Selling, general and administrative Other operating expense (income)net Interest expense Total Expenses Income before income taxes Provision for income taxes Net income Basic EPS Diluted EPS Dividends Per Share 4/30/2016 $ $ 5/2/2015 5/3/2014 4/27/2013 704,785 $ 463,348 241,437 645,825 $ 426,685 219,140 641,135 $ 423,480 217,655 662,007 444,757 217,250 148,384 145 203 148,732 92,705 31,507 61,198 $ 145,157 (1,101) 371 144,427 74,713 25,402 49,311 $ 153,220 666 660 154,546 63,109 19,474 43,635 $ 146,223 173 403 146,799 70,451 23,531 46,920 $1.31 $1.31 $0.00 $1.06 $1.05 $0.00 $0.93 $0.92 $0.00 $1.01 $1.01 $2.55 National Beverage Corp. Annual Reports Balance Sheet (Amounts in thousands) Fiscal Year Ended ASSETS Current assets: Cash and equivalents Trade receivablesnet Inventories Deferred income taxesnet Prepaid and other assets Total current assets Property, plant and equipmentnet Goodwill Intangible assets Other assets Total assets 4/30/2016 $ 5/3/2014 5/2/2015 4/27/2013 $ 105,577 $ 61,046 47,922 ### ### 223,671 $ 52,456 $ 59,951 42,924 ### ### 167,729 $ 29,932 $ 58,205 43,914 ### ### 143,141 $ 18,267 64,069 39,234 ### ### 130,941 $ 61,932 13,145 ### ### 305,498 $ 60,182 ### ### ### 247,750 $ 59,494 13,145 ### ### 222,841 $ 57,307 13,145 ### ### 208,642 $ 49,391 $ 26,195 28 44,896 $ 21,257 98 45,606 $ 18,873 44 44,261 19,142 34 $ 75,614 $ 66,251 $ 64,523 $ 63,437 14,474 10,000 15,245 30,000 13,873 50,000 14,327 9,258 99,346 $ 8,472 99,968 $ 8,244 116,640 $ 10,562 138,326 150 $ 506 270 $ 504 390 $ 504 550 504 LIABILITIES AND SHAREHOLDERS EQUITY Current liabilities: Accounts payable Accrued liabilities Income taxes payable Total current liabilities Long Term Notes Payable Deferred income taxes - net Other liabilities Total liabilties $ Shareholders' equity: Preferred Stock Common Stock Additional Paid in Capital (Assume all Common) 1 $ 34,570 37,759 42,775 50,398 Retained Earnings 190,733 129,773 80,737 37,828 (2,524) (205) (964) (18,000) (18,000) 106,201 70,316 (also assume no dividends will be paid to preferred stockholders) Accumulated other comprehensive income (loss) (1,807) Treasury Stock at Cost (Assume all Common) 1 (18,000) Total shareholders' equity 206,152 Total liabilities and shareholders' equity ### 147,782 $ 305,498 $ 247,750 $ 222,841 $ 208,642 National Beverage Corp. Annual Reports Statement of Cash Flows (Amounts in thousands) Fiscal Year Ended OPERATING ACTIVITIES: Net income 4/30/2016 $61,198 5/2/2015 $49,311 5/3/2014 $43,635 Adjustments to reconcile net income to net cash provided by (used in) operating activities: Depreciation and amortization Deferred income tax (benefit) provision Loss (gain) on disposal of property, net Stock-based compensation Changes in assets and liabilities: Trade receivables Inventories Prepaid and other assets Accounts payable Accrued and other liabilities Net cash provided by operating activities $ 12,056 $ (1,299) 129 228 (1,095) (4,998) (485) 4,495 8,726 $78,955 11,580 $ 1,076 (1,188) 307 (1,746) 990 (605) (710) (995) $58,020 11,708 79 51 95 5,864 (4,680) (2,548) 1,345 (3,167) $52,382 INVESTING ACTIVITIES: Net Additions to property, plant and equipment $ (12,024) $ (9,725) $ (12,062) Net cash used in investing activities $ (12,024) $ (9,725) $ (12,062) FINANCING ACTIVITIES: Dividends paid on common stock Dividends paid on preferred stock Repayments under credit facilities, net Redemption of preferred stock Proceeds from stock options exercised Stock-based tax benefits (186) (10,000) (6,000) 848 1,528 (239) (20,000) (6,000) 228 240 (659) (20,000) (8,000) 47 17 Other (60) Net cash used in financing activities $ (13,810) $ (25,771) $ (28,655) NET INCREASE IN CASH AND EQUIVALENTS CASH AND EQUIVALENTSBEGINNING OF YEAR $ 53,121 $ 52,456 22,524 $ 29,932 11,665 18,267 CASH AND EQUIVALENTSEND OF YEAR $ 105,577 $ 52,456 $ 29,932 OTHER CASH FLOW INFORMATION: Interest paid Income taxes paid $ $ 116 $ 29,473 $ 380 $ 24,745 $ 723 23,079 4/27/2013 $46,920 $ 11,002 172 63 230 (2,478) 1,628 (2,466) (10,614) (4,193) $40,264 $ (9,616) $ (9,616) (118,139) (12) 50,000 19,704 239 201 $ (48,007) $ (17,359) 35,626 $ 18,267 $ $ 341 24,327 Liquidity Ratios Calculate the Ratios Below: Fiscal Year Ended 4/30/2016 Current ratio 2.96 Working capital 148,057 Acid-test ratio 2.26 Inventory turnover (times) 10.20 Days sales in inventory 35.78 Accounts receivable turnover (times) 2.75 Days sales in receivables 132.53 Free cash flow $ (72,918.99) $ Average inventory Average A/R 45,423 60,499 5/2/2015 2.53 101,478 1.76 9.83 37.14 0.92 397.84 (39,654.14) 43,419 59,078 Explain the Ratios in separate Word Document that you will prepare as stated in "1-In (See "Example Analysis" for help on how to write this caption) $ 5/3/2014 4/27/2013 2.22 2.06 The answer is here as an example to show 78,618 67,504 you how I want each ceil to be filled in 1.41 1.36 so it will prove your work 10.19 35.83 0.39 Need to fill in these blanks with the ratio c 927.16 Be sure to show your work in each cell (20,631.66) $ 47,993.40 DO NOT CHAN 41,574 61,137 l prepare as stated in "1-Instructions Tab" lanks with the ratio calculations required r work in each cell DO NOT CHANGE THE FORMATTING OF ANY CELL Incorrect Ratios Total Ratios This Page Assessment Points his Page 0 34 10 Solvency Ratios Calculate the Ratios Below: Fiscal Year Ended 4/30/2016 5/2/2015 Debt to equity Interest coverage 0.48 457.67 0.68 202.38 Long term liabilities $14,474 $25,245 Explain the Ratios in separate Word Document that you will prepare as stated in "1 (See "Example Analysis" for help on how to write this caption) 5/3/2014 4/27/2013 1.10 96.62 1.97 175.82 $43,873 $64,327 ill prepare as stated in "1-Instructions Tab" Need to fill in these blanks with the ratio callculatio Be sure to show your work in each cell either cell r DO NOT CHANGE THE F with the ratio callculations required n each cell either cell references or numbers used O NOT CHANGE THE FORMATTING OF ANY CELL Incorrect Ratios Total Ratios This Page Assessment Points 0 12 10 Profitability Ratios Calculate the Ratios Below: Fiscal Year Ended Asset turnover Return on sales Gross margin % Return on assets Return on equity Average interest rate Average total assets 4/30/2016 5/2/2015 2.55 20.03% 34.26% 22.12% 34.58% 0.54% 2.74 19.90% 33.93% 20.96% 38.83% 0.77% 276,624 235,296 Interest Net Income tax rate 1 - Income tax rate Net of tax interest expense 241437 33.99% 66.01% $159,381.50 219140 34.00% 66.00% $144,633.63 Net of tax interest expense Net Earnings Adjusted net income $159,381 $61,198 $220,579 $144,634 $49,311 $193,945 Average equity Average total liabilities $176,967 $99,657 $126,992 $108,304 Explain the Ratios in separate Word Document that you will prepare as stated in "1-In (See "Example Analysis" for help on how to write this caption) 5/3/2014 2.97 19.58% 33.95% 20.23% 49.44% 0.65% 4/27/2013 22.49% 32.82% 215,742 217655 30.86% 69.14% $150,491.62 217250 33.40% 66.60% $144,687.37 $150,492 $43,635 $194,127 $144,687 $46,920 $191,607 Inc Tax / Pre Tax Earnings Calculate (1 - Inc Tx Rate) x Interest-Net Bring Down from Prior Row Net Earnings + Net of Tax Interest Expense $88,259 $127,483 prepare as stated in "1-Instructions Tab" Incorrect Ratios Total Ratios This Page Assessment Points Need to fill in these blanks with the ratio calculations required Be sure to show your work in each cell either cell references or numbers used DO NOT CHANGE THE FORMATTING OF ANY CELL his Page mbers used OF ANY CELL 0 57 10 DuPont Analysis of ROA Calculate the Ratios Below: Fiscal Year Ended 4/30/2016 Return on assets (calculated) 22.12% Return on sales X Asset turnover = Product 8.68% 2.55 22.12% (Your calculated product should equa the return on assets calculated above Explain below the DuPont Therory (Use no less than 200 words) Include in your answer why the product calculated above should e The purpose of the Dupont theory is to produce the highest return on equity whichis afected by three things ope efciency, and fnancial leverage (equity multiplier).The equation therefor calculates ROE where ROE = Proft Ma Multiplier. DuPont uses these components to determine which factor is most responsible for causing change in RO they are using their assets to the best of their ability. If a company is profting relative to their assets, the product 5/2/2015 5/3/2014 20.96% 20.23% 7.64% 2.74 20.96% 6.81% 2.97 20.23% Need to fill in these blanks with the ratio calculations require Be sure to show your work in each cell either cell references DO NOT CHANGE THE FORMATT ur calculated product should equal return on assets calculated above) oduct calculated above should equal the first calculation return on assets whichis afected by three things operating efciency, asset use lculates ROE where ROE = Proft Margin x Asset Turnover Ratio x Equity responsible for causing change in ROE. It helps companies understand if relative to their assets, the product and return should be equal. tio calculations required l either cell references or numbers used HANGE THE FORMATTING OF ANY CELL Incorrect Ratios Total Ratios This Page Assessment Points his Page 0 3 10 Calculate the Ratios Below: Fiscal Year Ended 4/30/2016 5/2/2015 Book value per common share Earnings per share (basic) Earnings per share (diluted) P/E Ratio Dividend yield Dividend payout $4.84 $1.31 $1.31 3.69 0.00% 0.00% $3.57 $1.06 $1.05 3.37 0.00% 0.00% Book value relating to common shares equity Common shares outstanding (thousands) Adjusted closing price Dividends per share $4.45 0 $4.84 $0.00 $3.19 0 $3.57 $0.00 Explain the Ratios in separate Word Document that you will prepare as stated in "1 (See "Example Analysis" for help on how to write this caption) 5/3/2014 4/27/2013 $2.67 $0.93 $0.92 2.87 0.00% 0.00% $1.90 $1.01 $1.01 1.88 134.61% 251.79% $2.28 0 $2.67 $0.00 $1.51 46,310 -$0.66 $2.55 Need to fill in these blanks with the ratio calculations requir Be sure to show your work in each cell either cell reference DO NOT CHANGE THE FORMATT (Total Shareholders' Equity less: Preferred Stockholders' Equity) prepare as stated in "1-Instructions Tab" tio calculations required l either cell references or numbers used HANGE THE FORMATTING OF ANY CELL Incorrect Ratios Total Ratios This Page Assessment Points 0 40 10 Perform a both a vertical and horizontal analysis of the company's income statements and you to use for your horizonal analysis) To be done in the Word Document that you will pre In performing this vertical and horizontal analysis, consider any notable trends or changes Also use as many statements as you feel are necessary. You can compare between years o Word Document. Use as many analyses as necessary to show a good picture of the compa You should write a minimum of 500 words in this area. Explain the Ratios in separate Word Document that you will prepare as stated in "1-In both a vertical and horizontal analysis of the company's income statements and balance sheets. (I have pre for your horizonal analysis) To be done in the Word Document that you will prepare ming this vertical and horizontal analysis, consider any notable trends or changes that you observe that may s many statements as you feel are necessary. You can compare between years or use all the years to show ument. Use as many analyses as necessary to show a good picture of the company. d write a minimum of 500 words in this area. Income Statement (Amounts in thousands except per share data) Fiscal Year Ended Net sales Cost of sales Gross profit Expenses: Selling, general and administrative Other operating expense (income)net Interest expense Total Expenses Income before income taxes Provision for income taxes Net income Balance Sheet Balance Sheet (Amounts in thousands) Fiscal Year Ended ASSETS Current assets: Cash and equivalents Trade receivablesnet Inventories Deferred income taxesnet Prepaid and other assets Total current assets Property, plant and equipmentnet Goodwill Intangible assets Other assets Total assets LIABILITIES AND SHAREHOLDERS EQUITY Current liabilities: Accounts payable Accrued liabilities Income taxes payable Total current liabilities Long Term Notes Payable Deferred income taxes - net Other liabilities Total liabilties Shareholders' equity: Preferred Stock Common Stock Additional Paid in Capital (Assume all Common) 1 Retained Earnings Accumulated other comprehensive income (loss) Treasury Stock at Cost (Assume all Common) 1 Total shareholders' equity Total liabilities and shareholders' equity os in separate Word Document that you will prepare as stated in "1-Instructions Tab" nts and balance sheets. (I have prepared the vertical ananysis information relating to the financial stateme will prepare hanges that you observe that may provide useful information concerning its financial condition. years or use all the years to show the trend you are trying to explain. You should write up your results in p company. 4/30/2016 $ $ $ 704,785 463,348 241,437 % 100.00% 65.74% 34.26% 148,384 145 203 148,732 92,705 31,507 61,198 21.05% 0.02% 0.03% 21.10% 13.15% 4.47% 8.68% 5/2/2015 $ $ 5/3/2014 $ 645,825 426,685 219,140 % 100.00% 66.07% 33.93% 145,157 (1,101) 371 144,427 74,713 25,402 49,311 22.48% -0.17% 0.06% 22.36% 11.57% 3.93% 7.64% $ $ $ 641,135 423,480 217,655 153,220 666 660 154,546 63,109 19,474 43,635 4/30/2016 5/2/2015 5/3/2014 $ % $ % $ $ 105,577 61,046 47,922 4,454 4,672 223,671 34.56% $ 19.98% 15.69% 1.46% 1.53% 73.22% $ 52,456 59,951 42,924 4,348 8,050 167,729 21.17% $ 24.20% 17.33% 1.75% 3.25% 67.70% $ 29,932 58,205 43,914 2,685 8,405 143,141 20.27% 4.30% 0.53% 1.68% 60,182 13,145 1,615 5,079 247,750 24.29% 5.31% 0.65% 2.05% $ 61,932 13,145 1,615 5,135 305,498 59,494 13,145 1,615 5,446 222,841 16.17% 8.57% 0.01% 0.00% $ 49,391 26,195 28 75,614 0.00% 4.74% 0.00% 3.03% $ 14,474 9,258 99,346 $ 100.00% $ 24.75% $ 32.52% $ 100.00% $ 44,896 21,257 98 66,251 18.12% 8.58% 0.04% 0.00% 10,000 15,245 8,472 99,968 4.04% 6.15% 0.00% 3.42% 26.74% $ 40.35% $ 45,606 18,873 44 64,523 30,000 13,873 8,244 116,640 $ $ 150 506 34,570 190,733 (1,807) (18,000) 206,152 $ 305,498 n "1-Instructions Tab" 0.05% $ 0.17% 11.32% 62.43% -0.59% -5.89% 67.48% $ 100.00% $ 270 504 37,759 129,773 (2,524) (18,000) 147,782 247,750 0.11% $ 0.20% 15.24% 52.38% -1.02% -7.27% 59.65% $ 100.00% $ 390 504 42,775 80,737 (205) (18,000) 106,201 222,841 g to the financial statements presented below for Incorrect Ratios Total Ratios This Page cial condition. Assessment Points write up your results in paragraph form in your 5/3/2014 4/27/2013 % 100.00% 66.05% 33.95% 23.90% 0.10% 0.10% 24.11% 9.84% 3.04% 6.81% $ $ $ 662,007 444,757 217,250 % 100.00% 67.18% 32.82% 146,223 173 403 146,799 70,451 23,531 46,920 22.09% 0.03% 0.06% 22.17% 10.64% 3.55% 7.09% 5/3/2014 4/27/2013 % $ % 13.43% $ 26.12% 19.71% 1.20% 3.77% 64.23% $ 18,267 64,069 39,234 3,665 5,706 130,941 8.76% 30.71% 18.80% 1.76% 2.73% 26.70% 5.90% 0.72% 2.44% 57,307 13,145 1,615 5,634 208,642 27.47% 6.30% 0.77% 2.70% 100.00% $ 20.47% 8.47% 0.02% 0.00% 28.95% $ 13.46% 6.23% 0.00% 3.70% 52.34% $ 62.76% 100.00% 44,261 19,142 34 63,437 21.21% 9.17% 0.02% 0.00% 50,000 14,327 10,562 138,326 23.96% 6.87% 0.00% 5.06% 30.40% 66.30% 0.18% $ 0.23% 19.20% 36.23% -0.09% -8.08% 47.66% $ 100.00% $ 550 504 50,398 37,828 (964) (18,000) 70,316 208,642 0.26% 0.24% 24.16% 18.13% -0.46% -8.63% 33.70% 100.00% 0 0 10