Financial model
mark39
StarOilandGasData.xlsIncome Statement
| Star Oil & Gas, Inc. | ||||||||
| Consolidated Income Statements | ||||||||
| Year Ended May 31 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | |
| (In millions except per share data) | ||||||||
| Revenues | 4,760.8 | 6,470.6 | 9,186.5 | 9,553.1 | 8,776.9 | 8,995.1 | 9,488.8 | |
| Cost of goods sold | 2,865.3 | 3,906.7 | 5,503.0 | 6,065.5 | 5,493.5 | 5,403.8 | 5,784.9 | |
| Gross profit | 1,895.6 | 2,563.9 | 3,683.5 | 3,487.6 | 3,283.4 | 3,591.3 | 3,703.9 | |
| Selling and administrative | 1,209.8 | 1,588.6 | 2,303.7 | 2,623.8 | 2,426.6 | 2,606.4 | 2,689.7 | |
| Operating income | 685.8 | 975.3 | 1,379.8 | 863.8 | 856.8 | 984.9 | 1,014.2 | |
| Interest expense | 24.2 | 39.5 | 52.3 | 60.0 | 44.1 | 45.0 | 58.7 | |
| Other expense, net | 11.7 | 36.7 | 32.3 | 20.9 | 21.5 | 23.2 | 34.1 | |
| Restructuring charge, net | - | - | - | 129.9 | 45.1 | (2.5) | - | |
| Income before income taxes | 649.9 | 899.1 | 1,295.2 | 653.0 | 746.1 | 919.2 | 921.4 | |
| Income taxes | 250.2 | 345.9 | 499.4 | 253.4 | 294.7 | 340.1 | 331.7 | |
| Net income | 399.7 | 553.2 | 795.8 | 399.6 | 451.4 | 579.1 | 589.7 | |
| Diluted earnings per common share | 1.36 | 1.88 | 2.68 | 1.35 | 1.57 | 2.07 | 2.16 | |
| Average shares outstanding (diluted) | 294.0 | 293.6 | 297.0 | 296.0 | 287.5 | 279.8 | 273.3 | |
| Growth (%) | ||||||||
| Revenue | 35.9 | 42.0 | 4.0 | (8.1) | 2.5 | 5.5 | ||
| Operating income | 42.2 | 41.5 | (37.4) | (0.8) | 15.0 | 3.0 | ||
| Net income | 38.4 | 43.9 | (49.8) | 13.0 | 28.3 | 1.8 | ||
| Margins (%) | ||||||||
| Gross margin | 39.6 | 40.1 | 36.5 | 37.4 | 39.9 | 39.0 | ||
| Operating margin | 15.1 | 15.0 | 9.0 | 9.8 | 10.9 | 10.7 | ||
| Net margin | 8.5 | 8.7 | 4.2 | 5.1 | 6.4 | 6.2 | ||
| Effective tax rate (%)* | 38.5 | 38.6 | 38.8 | 39.5 | 37.0 | 36.0 | ||
| *The U.S. statutory tax rate was 35%. The state tax varied yearly from 2.5% to 3.5%. |
DCF Analysis
| Exhibit 2 | ||||||||||||
| Star Oil and Gas, Inc | ||||||||||||
| Discounted Cash Flow Analysis | ||||||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | |||
| Assumptions: | ||||||||||||
| Revenue growth (%) | 7.0 | 6.5 | 6.5 | 6.5 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | ||
| COGS/Sales (%) | 60.0 | 60.0 | 59.5 | 59.5 | 59.0 | 59.0 | 58.5 | 58.5 | 58.0 | 58.0 | ||
| S&A/Sales (%) | 28.0 | 27.5 | 27.0 | 26.5 | 26.0 | 25.5 | 25.0 | 25.0 | 25.0 | 25.0 | ||
| Tax rate (%) | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | ||
| Current assets/Sales (%) | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | 38.0 | ||
| Current liabilities/Sales (%) | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | 11.5 | ||
| Yearly depreciation and capex equal each other. | ||||||||||||
| Terminal value growth rate (%) | 3.0 |
Balance Sheet
| Star O&G, Inc | |||||
| Consolidated Balance Sheets | |||||
| As of | May 31 | ||||
| (In millions) | 2015 | 2016 | |||
| Assets | |||||
| Current Assets: | |||||
| Cash and equivalents | $ | 254.3 | $ | 304.0 | |
| Accounts receivable | 1,569.4 | 1,621.4 | |||
| Inventories | 1,446.0 | 1,424.1 | |||
| Deferred income taxes | 111.5 | 113.3 | |||
| Prepaid expenses | 215.2 | 162.5 | |||
| Total current assets | 3,596.4 | 3,625.3 | |||
| Property, plant and equipment, net | 1,583.4 | 1,618.8 | |||
| Identifiable intangible assets and goodwill, net | 410.9 | 397.3 | |||
| Deferred income taxes and other assets | 266.2 | 178.2 | |||
| Total assets | $ | 5,856.9 | $ | 5,819.6 | |
| Liabilities and shareholders' equity | |||||
| Current Liabilities: | |||||
| Current portion of long-term debt | $ | 50.1 | $ | 5.4 | |
| Notes payable | 924.2 | 855.3 | |||
| Accounts payable | 543.8 | 432.0 | |||
| Accrued liabilities | 621.9 | 472.1 | |||
| Income taxes payable | - | 21.9 | |||
| Total current liabilities | 2,140.0 | 1,786.7 | |||
| Long-term debt | 470.3 | 435.9 | |||
| Deferred income taxes and other liabilities | 110.3 | 102.2 | |||
| Redeemable preferred stock | 0.3 | 0.3 | |||
| Shareholders' equity: | |||||
| Common stock, par | 2.8 | 2.8 | |||
| Capital in excess of stated value | 369.0 | 459.4 | |||
| Unearned stock compensation | (11.7) | (9.9) | |||
| Accumulated other comprehensive income | (111.1) | (152.1) | |||
| Retained earnings | 2,887.0 | 3,194.3 | |||
| Total shareholders' equity | 3,136.0 | 3,494.5 | |||
| Total liabilities and shareholders' equity | $ | 5,856.9 | $ | 5,819.6 | |
| Source: Company 10-K SEC filing |
Cost of Capital info
| Star Oil & Gas, Inc. | ||||||||||
| Capital Market and Financial Information | ||||||||||
| On or Around July 5,2016 | ||||||||||
| Current vields on U.S. Treasuries | Star O&G Share Price Performance Relative to S&P500: | |||||||||
| January 2015 to July 5, 2016 | ||||||||||
| 3-month | 3.59% | |||||||||
| 6-month | 3.59% | |||||||||
| I-year | 3.59% | |||||||||
| 5-year | 4.88% | |||||||||
| 10- year | 5.39% | |||||||||
| 20-year | 5.74% | |||||||||
| Historical Equitv Risk Premiums (1926-2016) | ||||||||||
| Geometric mean | 5.90% | |||||||||
| Arithmetic mean | 7.50% | |||||||||
| Current Yield on Publiclv Traded Star O&G Debt* | ||||||||||
| Coupon | 6.75% paid semi-annually | |||||||||
| Issued | 7/15/11 | |||||||||
| Maturity | 7/15/36 | |||||||||
| Current Price | $ | 95.60 | ||||||||
| Settlement Date | 7/5/16 | |||||||||
| Star O&G Historic Betas | ||||||||||
| 2011 | 0.98 | |||||||||
| 2012 | 0.84 | Star O&G share price on July 5, 2016: $ 42.09 | ||||||||
| 2013 | 0.84 | |||||||||
| 2014 | 0.63 | Dividend Historv and Forecasts | ||||||||
| 2015 | 0.83 | Paymt Dates | 31-Mar | 30-Jun | 30-Sep | 31-Dec | Total | |||
| YTD 06/30/15 | 0.69 | 2012 | 0.10 | 0.10 | 0.10 | 0.10 | 0.40 | |||
| 2013 | 0.12 | 0.12 | 0.12 | 0.12 | 0.48 | |||||
| Average | 0.80 | 2014 | 0.12 | 0.12 | 0.12 | 0.12 | 0.48 | |||
| 2015 | 0.12 | 0.12 | 0.12 | 0.12 | 0.48 | |||||
| Consensus EPS estimates: | 2016 | 0.12 | 0.12 | |||||||
| FY 2017 | FY 2018 | |||||||||
| $ | 2.32 | $ | 2.67 | Value Line Forecast of Dividend Growth from '14-'16 to '2018-2020: | ||||||
| 5.50% |
