Corporate Finance (3 page report excluding calculation explanation)
ringringring
1837457679_3.xlsxFinancial History
| START HERE | Enter data in the yellow cells only. Comments to help you are in blue or red font. Take one row at a time. | ||||||||
| CURRENCY: | USD | <-- This is the organization's home or functional currency. E.g., USD, INR (Indian Rupee), BRL (Brazilian Real), EUR (Euro), CNY (Chinese Yuan). Information is found on financial statements. | |||||||
| SCALING: x | 1,000 | <-- Local currency units: Could be 1 (so 1 means 1), 1000 (so 1 = 1,000), or 1,000,000 (so 1 = 1.0 million). Usually 1000 is used. Information is found on financial statements. | |||||||
| Starbucks Corporation | <-- | Any financial report should show the name of the organization in the heading. | |||||||
| INCOME STATEMENT HIGHLIGHTS | <-- | Always identify the type of report. | |||||||
| Unaudited; Amounts USD x 1000 | <-- | The currency and scaling needs to be defined. | |||||||
| For Fiscal Years ended | October | [Day] | <-- | An organization's fiscal year might end on Dec 31, or June 30, or something else. State it here. | |||||
| % Growth vs Prior Year | |||||||||
| 2016 | 2015 | 2014 | 2016 | 2015 | <-- | Replace leftmost year number (Cell C9) with most recent year of data available. | |||
| TOTAL REVENUE | $ 21,315,900.0 | $ 19,162,700.0 | $ 16,447,800.0 | 11.2% | 16.5% | ||||
| Cost of Goods Sold | $ 8,511,100.0 | $ 7,787,500.0 | $ 6,858,800.0 | 9.3% | 13.5% | ||||
| Gross Profit or (Loss) | $ 12,804,800.0 | $ 11,375,200.0 | $ 9,589,000.0 | 12.6% | 18.6% | ||||
| Other Operating Expenses | $ 8,632,900.0 | $ 7,774,200.0 | $ 6,507,900.0 | 11.0% | 19.5% | ||||
| OPERATING INCOME | $ 4,171,900.0 | $ 3,601,000.0 | $ 3,081,100.0 | 15.9% | 16.9% | ||||
| Interest Income or (Expense), Net | $ 108,000.0 | $ 372,500.0 | $ 142,700.0 | -71.0% | 161.0% | <-- | Be sure to enter interest income as a positive number; interest expense as a negative number. | ||
| Other Income or (Expense), Net | $ (81,300.0) | $ (70,500.0) | $ (64,100.0) | -15.3% | -10.0% | <-- | Be sure to enter other income as a positive number, other expense as a negative number. | ||
| Income before Tax Provision | $ 4,198,600.0 | $ 3,903,000.0 | $ 3,159,700.0 | 7.6% | 23.5% | ||||
| Provision for Income Taxes | $ 1,511,496.0 | $ 1,143,700.0 | $ 1,092,000.0 | 32.2% | 4.7% | ||||
| Net Income or (Loss) from Continuing Operations | $ 2,687,104.0 | $ 2,759,300.0 | $ 2,067,700.0 | -2.6% | 33.4% | <-- | FYI, most analysts consider this better than total Net Income as an indicator of underlying business performance. | ||
| Discontinued Operations Income (Loss), Net | $ - 0 | $ - 0 | $ - 0 | 0.0% | 0.0% | <-- | Typically, from shutting down or selling part of the business. | ||
| NET INCOME OR (LOSS) | $ 2,687,104.0 | $ 2,759,300.0 | $ 2,067,700.0 | -2.6% | 33.4% | <-- | Confirm this matches what is listed on the financial statement you downloaded. | ||
| Average Diluted Shares Outstanding | $ 1,900,000.0 | $ 1,820,000.0 | $ 1,350,000.0 | 4.4% | 34.8% | <-- | Make sure this value is scaled the same way as the other numbers (thousands or millions). | ||
| DILUTED EARNINGS OR (LOSS) PER SHARE | $ 1.41 | $ 1.52 | $ 1.53 | -6.7% | -1.0% | ||||
| Net Income Margin % | 12.6% | 14.4% | 12.6% | <-- | Net income or Loss / Total Revenue. Typical values are 2% to 20%, but it can be negative, too. | ||||
| Common Stock Share Price at each Year-End | $ 55.52 | $ 60.03 | $ 41.03 | -7.5% | 46.3% | <-- | Mergent Online instructions include how to find historical stock prices. Go back four years because you will need fourth year for Line 123. | ||
| Total Equity Value (= share price x shares) | $ 105,488,000.0 | $ 109,254,600.0 | $ 55,390,500.0 | <-- | Better to use end-of-year shares outstanding, but this figure is close enough for this course. | ||||
| Price / Earnings Ratio (P/E) | 39.26 | 39.60 | 26.79 | -0.9% | 47.8% | <-- | Market price at the end of the year divided by that year's earnings per share. Typical values are 10 to 30. | ||
| Source: [Title (May 20th). Retrieved from https://www.sec.gov/Archives/edgar/data/829224/000082922416000083/sbux-1022016x10xk.htm#sA81B26E970E8EBC307543265204EBB47 | <-- | Include APA citation for where you found the financial statement data. | |||||||
| Starbucks Corporation | |||||||||
| CASH FLOW STATEMENT HIGHLIGHTS | |||||||||
| Unaudited; Amounts USD x 1000 | |||||||||
| For Fiscal Years ended October [Day] | |||||||||
| % Growth vs Prior Year | |||||||||
| 2016 | 2015 | 2014 | 2016 | 2015 | |||||
| Net Income or (Loss), from Above | $ 2,687,104.00 | $ 2,759,300.00 | $ 2,067,700.00 | -2.6% | 33.4% | ||||
| Depreciation and Amortization Expense | $ 1,030,100.00 | $ 933,800.00 | $ 748,400.00 | 10.3% | 24.8% | <-- | This is a noncash expense, so we add it back to net income here. | ||
| Other Operating Sources and (Uses) | $ 857,896.00 | $ 56,000.00 | $ (2,208,300.00) | 1432.0% | 102.5% | <-- | These are working capital changes and other adjustments. | ||
| Cash Flow from Operating Activities | $ 4,575,100.00 | $ 3,749,100.00 | $ 607,800.00 | 22.0% | 516.8% | <-- | By entering the total here, the row above will be automatically calculated. | ||
| (Capital Expenditures, Net of Disposals) | $ (2,247,800.00) | $ (1,242,800.00) | $ (798,600.00) | -80.9% | -55.6% | <-- | This is normally a negative number. | ||
| Other Investing Activities | $ 24,900.00 | $ (277,500.00) | $ (19,100.00) | 109.0% | -1352.9% | <-- | This is normally a negative number. | ||
| Cash Flow from Investing Activities | $ (2,222,900.00) | $ (1,520,300.00) | $ (817,700.00) | -46.2% | -85.9% | <-- | This is normally a negative number. | ||
| Increase or (Decrease) in Debt | $ 1,254,500.00 | $ 238,400.00 | $ 748,500.00 | 426.2% | -68.1% | <-- | Borrowing money is a source of cash; repaying it is a use of cash. | ||
| (Dividend Payments) | $ (3,012,900.00) | $ (2,533,100.00) | $ (1,402,000.00) | -18.9% | -80.7% | <-- | Dividend payments should normally be a negative number, because they are a cash outflow. | ||
| Other Financing Activities | $ (114,400.00) | $ (93,600.00) | $ (84,200.00) | -22.2% | -11.2% | ||||
| Cash Flow from Financing Activities | $ (1,872,800.00) | $ (2,388,300.00) | $ (737,700.00) | 21.6% | -223.7% | ||||
| Cumulative Translation Adjustment | $ - 0 | $ - 0 | $ - 0 | 0.0% | 0.0% | <-- | Don't try to understand what this number means at this time. It is applicable to most multicurrency organizations. | ||
| NET CASH FLOW | $ 479,400.00 | $ (159,500.00) | $ (947,600.00) | 400.6% | 83.2% | <-- | Confirm this matches what is listed on the financial statement you downloaded. | ||
| Memo: Free Cash Flow | $ 2,327,300.00 | $ 2,506,300.00 | $ (190,800.00) | -7.1% | 1413.6% | <-- | Usually defined as Cash Flow from Operating Actitivies less Capital Expenditures. Note: If the latter is a negative number, then the formula is Op Cash Flow + Cap Exp, e.g., 1,000 + -100 = 900. Free Cash Flow must be less than Operating Cash Flow. | ||
| Source: [Title (May 20th). Retrieved from https://www.sec.gov/Archives/edgar/data/829224/000082922416000083/sbux-1022016x10xk.htm#sA81B26E970E8EBC307543265204EBB47 | |||||||||
| Starbucks Corporation | |||||||||
| BALANCE SHEET HIGHLIGHTS | |||||||||
| Unaudited; Amounts USD x 1000 | |||||||||
| For Fiscal Years ended October [Day] | |||||||||
| % Growth vs Prior Year | |||||||||
| 2016 | 2015 | 2014 | 2016 | 2015 | |||||
| Current Assets | |||||||||
| Cash and Marketable Securities | $ 2,128,800.00 | $ 1,530,100.00 | $ 1,708,400.00 | 39.1% | -10.4% | ||||
| Accounts Receivable, Net | $ 768,800.00 | $ 719,000.00 | $ 948,400.00 | 6.9% | -24.2% | <-- | These amounts are for invoices the organization has sent to clients, but that they have not yet paid. | ||
| All Other Current Assets | $ 1,862,900.00 | $ 1,721,900.00 | $ 1,511,900.00 | 8.2% | 13.9% | ||||
| Total Current Assets | $ 4,760,500.00 | $ 3,971,000.00 | $ 4,168,700.00 | 19.9% | -4.7% | <-- | Enter total current assets values, and the spreadsheet will calculate "other current assets." | ||
| Non-current Assets | |||||||||
| Property, Plant and Equipment, Net | $ 4,533,800.00 | $ 4,088,300.00 | $ 3,519,000.00 | 10.9% | 16.2% | <-- | These are for PP&E net of accumulated depreciation. | ||
| Goodwill and Other Intangible Assets | $ 1,719,600.00 | $ 1,575,400.00 | $ 856,200.00 | 9.2% | 84.0% | <-- | Includes intellectual property (IP), such as patents and acquired technology. | ||
| Other Non-current Assets | $ 3,315,600.00 | $ 2,781,600.00 | $ 2,209,000.00 | 19.2% | 25.9% | ||||
| Total Non-current Assets | $ 9,569,000.00 | $ 8,445,300.00 | $ 6,584,200.00 | 13.3% | 28.3% | ||||
| TOTAL ASSETS | $ 14,329,500.00 | $ 12,416,300.00 | $ 10,752,900.00 | 15.4% | 15.5% | <-- | Enter Total Assets, and the spreadsheet will calculate Total Noncurrent Assets and Other Noncurrent Assets. | ||
| Current Liabilities | |||||||||
| Accounts Payable, Net | $ 2,975,700.00 | $ 2,664,300.00 | $ 2,244,200.00 | 11.7% | 18.7% | <-- | These are for bills the organization has received but not yet paid. | ||
| Other Current Liabilities | $ 1,571,200.00 | $ 983,800.00 | $ 794,500.00 | 59.7% | 23.8% | ||||
| Total Current Liabilities | $ 4,546,900.00 | $ 3,648,100.00 | $ 3,038,700.00 | 24.6% | 20.1% | ||||
| Non-current Liabilities | |||||||||
| Long-term Debt | $ 3,202,200.00 | $ 2,347,500.00 | $ 2,048,300.00 | 36.4% | 14.6% | ||||
| Other Non-current Assets | $ 696,400.00 | $ 602,700.00 | $ 393,900.00 | 15.5% | 53.0% | ||||
| Total Non-current Liabilities | $ 3,898,600.00 | $ 2,950,200.00 | $ 2,442,200.00 | 32.1% | 20.8% | ||||
| TOTAL LIABILITIES | $ 8,445,500.00 | $ 6,598,300.00 | $ 5,480,900.00 | 28.0% | 20.4% | ||||
| SHAREOWNERS' EQUITY | |||||||||
| Common Stock, at par | $ 1,500.00 | $ 1,500.00 | $ 700.00 | ||||||
| Additional Paid-in Capital | $ 41,100.00 | $ 41,100.00 | $ 38,400.00 | ||||||
| Retained Earnings | $ 5,949,800.00 | $ 5,974,800.00 | $ 5,206,600.00 | ||||||
| CTA and Other | $ (108,400.00) | $ (199,400.00) | $ 26,300.00 | ||||||
| TOTAL SHAREOWNERS' EQUITY | $ 5,884,000.00 | $ 5,818,000.00 | $ 5,272,000.00 | <-- | By definition, shareowners' equity equals total assets minus total liabilities. Confirm this matches what is listed on the financial statement you downloaded. | ||||
| Source: [Title (May 20th). Retrieved from https://www.sec.gov/Archives/edgar/data/829224/000082922416000083/sbux-1022016x10xk.htm#sA81B26E970E8EBC307543265204EBB47 | |||||||||
| Starbucks Corporation | |||||||||
| SELECTED FINANCIAL RATIOS | |||||||||
| Unaudited; Amounts USD x 1000 | |||||||||
| For Fiscal Years ended October [Day] | |||||||||
| % Growth vs Prior Year | |||||||||
| 2016 | 2015 | 2014 | 2016 | 2015 | |||||
| FINANCIAL RATIOS | |||||||||
| Price / Earnings Ratio | 39.3 | 39.3 | 39.3 | 0.0% | 0.0% | <-- | Price per Share / Earnings per Share. Typical values are 10 to 40. | ||
| Debt / Equity Ratio | 1.4 | 1.4 | 1.4 | 0.0% | 0.0% | <-- | Total Liabilities / Total Shareowners' Equity. Typical values are 0.2 to 0.6. | ||
| Return on Equity (ROE) % | 45.7% | 45.7% | 45.7% | 0.0% | 0.0% | <-- | Net Income / Total Shareowners' Equity. Typical values are 2% to 40%. | ||
| Return on Assets (ROA) % | 18.8% | 18.8% | 18.8% | 0.0% | 0.0% | <-- | Net Income / Total Assets. Typical values are 2% to 40%. Almost always LOWER than ROE. | ||
| Net Profit Margin % | 12.6% | 12.6% | 12.6% | 0.0% | 0.0% | <-- | Net Income / Total Revenue. Typical values are 1% to 15%. | ||
| Free Cash Flow | $ 2,327,300.00 | $ 2,327,300.00 | $ 2,327,300.00 | 0.0% | 0.0% | <-- | Net Cash Flow minus Capital Expenditures. Value is almost always LESS THAN net cash flow. | ||
| OTHER USEFUL RATIOS | |||||||||
| Earnings per Share or EPS | $ 1.4143 | $ 1.4143 | $ 1.4143 | 0.0% | 0.0% | <-- | Net Income / Diluted Shares Outstanding. Typical values are $1.00 to $10.00. May also be negative. | ||
| Current Ratio | 1.0 | 1.0 | 1.0 | 0.0% | 0.0% | <-- | Current Assets / Current Liabilities. Typical values are 0.7 to 1.5. | ||
| Days Sales Outstanding (DSO) | 13 | 13 | 13 | 0.0% | 0.0% | <-- | (Accounts Receivable / Total Revenue) x 365. Typical values are 5-120. Lower is better. | ||
| COMMON STOCK PRICE | |||||||||
| Adjusted Close Price on or near October [Day] | $ 55.52 | $ 60.03 | $ 41.03 | -7.5% | 46.3% | <-- | Usually, somewhere between a few dollars and a couple of hundred dollars per share. | ||
| RATE OF RETURN CALCULATIONS | 2013 | 2014 | 2015 | 2016 | Pct Change | <-- | Need fiscal end-of-year information for four years to calculate three-year percentage change. | ||
| Adjusted Close Price at fiscal End of Year (EOY) | $ 78.39 | $ 55.52 | -29.2% | <-- | For this, ROR% = ($End - $Beg) / $Beg. More precisely, you would add dividends received to the $End value. | ||||
| Annual Dividends per Share | $ (1.5857) | $ (1.5857) | $ (1.5857) | <-- | Approximate dividends/share are calculated here, but you may want to override those with disclosed div/share figures. | ||||
| If you buy 1 share at end of fiscal 78.39, collect dividends, then sell at end of fiscal 2016, your 3-year percent gain would be: | -35.2% | <-- | Not required here, but a more complete measurement. | ||||||
| BUT WHAT IF WE VIEW THIS AS A TIME VALUE OF MONEY QUESTION? | |||||||||
| Investor's Annual Cash Flow for 1 Share | $ (78.39) C. Jeffrey Smith: This is negative because we're assuming you pay this out to buy your 1 share of stock. | $ (1.59) | $ (1.59) | $ 53.93 | <-- | For an investor who buys 1 share at beginning, collects dividends, then sells at end of third year. | |||
| Solve for the annual Internal Rate of Return or IRR, with N=3 Yrs | -13.1% C. Jeffrey Smith: This will always be LESS THAN 1/3 of the total 3-yr Percent Change figures above. Why? Because of COMPOUNDING. | <-- | This IRR is the best overall measure of this stock's performance over the time period. | ||||||
| Source: [Title (May 20th). Retrieved from https://www.sec.gov/Archives/edgar/data/829224/000082922416000083/sbux-1022016x10xk.htm#sA81B26E970E8EBC307543265204EBB47 |
&F Printed &D Page &P of &N
Capital Structure
| This tab is used to calculate Weighted Average Cost of Capital (WACC). Enter data in the yellow cells only. Comments to help you are indicated by a red triangle in the top right corner of cell; hover over the cell to review. | |||||||||
| Enter Company Full Name: | Starbucks Corporation | ||||||||
| Enter Fiscal Year: | 2016 | ||||||||
| Starbucks Corporation | |||||||||
| CAPITAL STRUCTURE | |||||||||
| For End of Fiscal Year 2016 | |||||||||
| Unaudited; Amounts USD x 1000 | |||||||||
| SIMPLE METHOD | TEXTBOOK METHOD | ||||||||
| Capital Funding Amount C. Jeffrey Smith: C. Jeffrey Smith: All of the funds the organization has received from banks, bond-buyers, stockholders, and other investors. | Cost of Capital: Estimated % Return Req'd by Investors | Corporate Marginal Tax Rate % C. Jeffrey Smith: C. Jeffrey Smith: Always 0% for stock and retained earnings; may be 0% - 50% for debt and leases. | 1 - Corp Tax Rate | % Cost of Capital, After Tax Savings C. Jeffrey Smith: C. Jeffrey Smith: Equals Column D times column F. | $ Cost of Capital per Year (Column C x Coumn G) | Wgt x Cost | % of Total | ||
| Debt: Bank Loans | $ 750,000,000.0 | 2.1% | 32.9% | 67.1% | 1.4% | $ 10,574,550.0 | 0.5% | 33.2% | |
| Debt: Bonds | 500,000,000.0 | 2.5% | 32.9% | 67.1% | 1.6% | 8,224,650.0 | 0.4% | 22.2% | |
| Debt: Commercial Paper | 1,000,000,000.0 | 0.0% | 32.9% | 67.1% | 0.0% | - 0 | 0.0% | 44.3% | |
| Debt: Other or Unidentified | - 0 | 0.0% | 32.9% | 67.1% | 0.0% | - 0 | 0.0% | 0.0% | |
| Leases (a form of Debt) | 1,223,200.0 | 0.0% | 32.9% | 67.1% | 0.0% | - 0 | 0.0% | 0.1% | |
| Preferred Stock (if any) | - 0 | 0.0% | 0.0% | 100.0% | 0.0% | - 0 | 0.0% | 0.0% | |
| Common Stock: At Par | 1,500.0 | 0.0% | 0.0% | 100.0% | 0.0% | - 0 | 0.0% | 0.0% | |
| Common Stock: Add'l Paid-in Capital | 41,100.0 | 0.0% | 0.0% | 100.0% | 0.0% | - 0 | 0.0% | 0.0% | |
| Retained Earnings | 5,949,800.0 | 0.0% | 0.0% | 100.0% | 0.0% | - 0 | 0.0% | 0.3% | |
| [Other] | - 0 | 0.0% | 0.0% | 100.0% | 0.0% | - 0 | 0.0% | 0.0% | |
| TOTAL | $ 2,257,215,600.0 | ? | $ 18,799,200.0 | 0.8% | 100.0% C. Jeffrey Smith: C. Jeffrey Smith: The % of Total should always add up to 100.0%. If not, you've done something wrong. |
||||
|
C. Jeffrey Smith: C. Jeffrey Smith: Equals Column D times column F. | WEIGHTED AVERAGE COST OF CAPITAL: | ||||||||
| WACC = [$ Total Annual Cost of Capital] / [$ Total Capital Funding] = | $18799200 / $2257215600 | = | 0.8% | ||||||
| TOTAL DEBT AND EQUITY | Amount | Pct of Total | |||||||
| Total Debt, incl. Leases & Preferred Stock | 2,251,223,200.0 | 99.7% | So Debt/Equity Ratio = | 375.7 | |||||
| Total Equity, incl "Other" | 5,992,400.0 | 0.3% | And Debt/Total Capital Ratio = | 1.0 | |||||
| TOTAL | $ 2,257,215,600.0 | 100.0% | |||||||
| NOTES: | |||||||||
| a) | The Corporate Marginal Tax Rate only affects debt and leases. For businesses, it is usually between 0% and 50%. For nonprofits and goverments, it is always 0%. | ||||||||
| b) | Leases are a form of debt. | ||||||||
| c) | The % annual cost of debt is always less than the % annual cost of equity. | ||||||||
| d) | Retained Earnings are basically common stock dividends that have not been paid out. Retained earnings therefore have the same required rate of return as common stock. | ||||||||
| e) | The organization's treasurer should be the best source for all of this information. | ||||||||
| HOW TO ESTIMATE REQUIRED RATE OF RETURN FOR COMMON STOCK | |||||||||
| Dividend Growth Model: | ($Dividend / $Current Price) + Expected % Dividend Growth Rate | <-- Only works if constant future growth is expected. | |||||||
| Example: | ($1.50 / $20.00) + 6.5% = 0.075 + 0.065 = 0.140 = 14.0% | ||||||||
| Intrinsic Value Method | The internal rate of return (IRR) of the future cash flows investors expect to receive. Use a spreadsheet IRR function to calculate. | ||||||||
| Industry Averages | Evaluate reasonable estimates for industry averages or for other organizations with similar risk. Not easy, by the way. |
&8&F &A &8Printed &D &8Page &P of &N
Valuation
| This tab is used for the corporate valuation report (Final Project I). Enter data in the yellow cells only. | |||||||||||||||||
| CURRENCY: | USD | <-- Probably U.S. dollars, or perhaps another (e.g., euros or pesos). We will use U.S. dollars. | |||||||||||||||
| SCALING: x | 1,000 | <-- Could be x1 (such as just dollars), x1000 (meaning amounts in Thousands), or Millions (meaning amounts in millions). We will use x1000. | |||||||||||||||
| Starbucks Corporation - COMPANY VALUATION | |||||||||||||||||
| (Unaudited; USD 1000) | |||||||||||||||||
| Fiscal Year --> | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | Total | |||||
| REVENUE | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 21,315,900 | 234,474,900 | |||||
| Growth Rate vs Prior Year | 11.2% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | ||||||
| Total Operating Expenses | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 17,144,000 | 188,584,000 | |||||
| OP INCOME OR (LOSS) | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 45,890,900 | |||||
| Operating Margin | 19.6% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | ||||||
| Interest & Other Income (Exp) | 26,700 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 26,700 | |||||
| PRETAX INCOME OR (LOSS) | 4,198,600 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | 4,171,900 | ||||||
| Tax Provision | 1,511,496 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 1,501,884 | 16,530,336 | |||||
| Discont'd Ops Income (Loss) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| NET INCOME OR (LOSS) | 2,687,104 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 29,387,264 | |||||
| Diluted Avg Shares | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | 1,900,000 | ||||||
| DILUTED EPS | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 1.41 | $ 15.47 | |||||
| Net Income or (Loss), from Above | 2,687,104 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 29,387,264 | |||||
| Deprec'n & Amortiz'n Expense | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 1,030,100 | 11,331,100 | |||||
| Other Op Sources & (Uses) | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 857,896 | 9,436,856 | |||||
| Cash Flow from Op Activities | 4,575,100 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 4,558,012 | 50,155,220 | |||||
| (Capital Expenditures, Net of Disposals) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (2,247,800) | (24,725,800) | |||||
| Other Investing Activities | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 24,900 | 273,900 | |||||
| Cash Flow from Invest'g Activities | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (2,222,900) | (24,451,900) | |||||
| Increase or (Decrease) in Debt | 1,254,500 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1,254,500 | |||||
| (Dividend Payments) | (3,012,900) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (32,950,302) | |||||
| Other Financing Activities | (114,400) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | (114,400) | |||||
| Cash Flow from Financ'g Activities | (1,872,800) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (2,993,740) | (31,810,202) | |||||
| Cumulative Translation Adjustment | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| NET CASH FLOW | 479,400 | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (6,106,882) | |||||
| Memo: Free Cash Flow | 2,327,300 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 2,310,212 | 25,429,420 | |||||
| VALUATION CALCULATIONS | |||||||||||||||||
| NET CASH FLOW "NCF" | 479,400 | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (6,106,882) | For the project analysis, we EXCLUDE the funding proceeds & repayment. | ||||
| NET PRESENT VALUE OF FUTURE CASH FLOWS | |||||||||||||||||
| NPV | @ | 5.0% | $(5,085,752) | For low-risk companies. The value here is what you would be willing to pay to buy the company under these assumptions. | |||||||||||||
| NPV | @ | 10.0% | $(4,046,985) | For medium-risk companies. The value here is what you would be willing to pay to buy the company under these assumptions. | |||||||||||||
| NPV | @ | 18.0% | $(2,959,932) | For high-risk companies. The value here is what you would be willing to pay to buy the company under these assumptions. | |||||||||||||
| If you had bought the whole company at the end of fiscal 2016 for its actual market value of ---> | $105,488,000 | (From Financial History worksheet) | |||||||||||||||
| Net Cash Flow w/ Investment | (105,488,000) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (658,628) | (112,074,282) | |||||
| Cumulative NCF | (105,488,000) | (106,146,628) | (106,805,256) | (107,463,885) | (108,122,513) | (108,781,141) | (109,439,769) | (110,098,397) | (110,757,025) | (111,415,654) | (112,074,282) | ||||||
| CF/Mth for Payback Calc | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||
| Cash Flow Payback Period | 11.00 | Years | After that many years, the cumulative cash flow turns positive. (It could turn negative again in one or more future years.) | If cumulative NCF has more than 1 change from - to +, payback period may be wrong. | |||||||||||||
| Internal Rate of Return | ERROR:#NUM! | IRR | At this discount rate R, the NPV will equal $0. IRR is a bit dangerous, because there can be more than one solution. | ||||||||||||||
| MODIFIED INTERNAL RATE OF RETURN (MIRR) | |||||||||||||||||
| Financing Rate | 0.0% | <-- This is the assumed cost to obtain financing. It could be the firm's cost of equity. | |||||||||||||||
| Reinivestment Rate | ERROR:#NUM! | <-- This is the assumed rate of return you would earn on excess funds. It might or might not equal the IRR from above. | |||||||||||||||
| MIRR | ERROR:#NUM! | MIRR | |||||||||||||||
| ECONOMIC VALUE ADDED (EVA), ALSO CALLED ECONOMIC PROFIT | |||||||||||||||||
| Net Income (from above) | 2,687,104 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 2,670,016 | 29,387,264 | |||||
| Invested Capital | 2,400 | From Financial History: Common Stock, at Par + Additional Paid-in Capital = | $1,500 | + | $ 900.00 | ||||||||||||
| WACC | 0.8% | From Capital Structure spreadsheet | |||||||||||||||
| EVA | 2,687,084 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 2,669,996 | 29,387,044 | EVA = Net Income - (Invested Capital x WACC) | ||||
| NOTES: | Projections of future cash flows are always uncertain; consider doing several scenarios of cash flows, such as most likely, best case, | ||||||||||||||||
| and worst case. | |||||||||||||||||
| You should use a low discount rate to calculate NPV for low-risk projects such as replacing equipment; perhaps 5%. Use a higher rate, such as | |||||||||||||||||
| 10%, for medium-risk projects, and use a higher rate of, say, 15% or 20% for the riskiest projects. Ask the company treasurer. | |||||||||||||||||
| The formula for calculating EVA is: Net Operating Profit After Taxes (NOPAT) - Invested Capital * Weighted Average Cost of Capital (WACC) | |||||||||||||||||
&8&F &A &8Printed &D &8Page &P of &N
Optional - TVM Help
| Time Value of Money (TVM) - Simple Calculators | ||||||||||||
| Enter known values in YELLOW cells. | Answers will be in GREEN cells. | |||||||||||
| GENERAL RULES AND DEFINITIONS | ||||||||||||
| - | Show cash you give to someone else (e. g., your bank), or outflows, as negative numbers; and show cash you receive, or inflows, as positive numbers. | |||||||||||
| - | Be clear and consistent about time periods: Are you doing everything in years, months, or some other intervals? | |||||||||||
| PV | Present Value. What is happening today, or what something is worth today. | |||||||||||
| N | Number of Periods. The number of years or months, or even weeks or days, you are looking at. | |||||||||||
| R | Rate of Return; or interest rate (sometimes labeled "i" instead of "r"). This is also the "compounding rate" (when going from the present to the future) or the | |||||||||||
| "discount rate" (when going from the future to the past). e.g., 7.5% annual rate. Remember 7.5% is same as 0.075. | ||||||||||||
| PMT | Annuity or Amortization Payment. Sometimes you are looking at a periodic loan repayment or a periodic savings or investing amount. | |||||||||||
| TYPE | TVM Type. Whether compounding or discounting is applied at the end of each period, as usually occurs with your savings accounts (usually "Type = 0"); or | |||||||||||
| whether compounding or discounting is applied at the beginning of each period, as is common with loans ("Type = 1"). | ||||||||||||
| GENERAL STEPS TO SOLVE TVM PROBLEMS | ||||||||||||
| 1a | Methodology: Understand the question, identify the relevant information, and, if required, make appropriate assumptions. | |||||||||||
| 1b | Methodology: Draw a timeline and write down the values for the items you know. Show the units and be consistent. e.g., if periods (N) are in months, | |||||||||||
| say so, and make sure your rate of return R is also in months. Don't write "PV=$1 million" and then "FV="$1,200,000." | ||||||||||||
| 2 | Calculation: Enter the known TVM values and calculate the unknown, using either the appropriate table below, a hand calculator, an online | |||||||||||
| TVM calculator site, a TVM smartphone app, or your own Excel or OpenOffice or Google Docs spreadsheet. | ||||||||||||
| 3 | Interpretation: Do a "does this all make sense?" check. If not, redo. Interpret your final answer. What does it mean? Why does it make sense? | |||||||||||
| 4 | Communication: Explain the question, your methodology, and your answer clearly, as if you are trying to convince your boss! | |||||||||||
| TO CALCULATE THE FUTURE VALUE OF A PRESENT AMOUNT | Formula: FV = PV (1 + R)^N | |||||||||||
| Example: You deposit money in a bank account, or invest it by buying a share of stock, and want to know what it might grow to. | ||||||||||||
| PV | N | R | PMT | TYPE | FV | |||||||
| Annual Compounding | Type: Beginning of Period | $ (1,000.00) | 3.0 | 5.00% | $ - 0 | 1 | $1,157.63 | |||||
| Type: End of Period | $ (1,000.00) | 3.0 | 5.00% | $ - 0 | 0 | $1,157.63 | ||||||
| Monthly Compounding | Type: Beginning of Period | $ (1,000.00) | 36 | 0.4167% | $ - 0 | 1 | $1,161.47 | |||||
| Type: End of Period | $ (1,000.00) | 36 | 0.4167% | $ - 0 | 0 | $1,161.47 | ||||||
| TO CALCULATE THE PRESENT VALUE OF FUTURE AMOUNT | Formula: PV = FV / (1 + R)^N | |||||||||||
| Example: How much should you put aside today to have a specified amount in the future, assuming N periods and R rate of return? | ||||||||||||
| PV | N | R | PMT | TYPE | FV | |||||||
| Annual Discounting | Type: Beginning of Period | $ (500.16) | 14.2 | 5.00% | $ - 0 | 1 | $1,000.00 | |||||
| Type: End of Period | $ (500.16) | 14.2 | 5.00% | $ - 0 | 0 | $1,000.00 | ||||||
| Monthly Discounting | Type: Beginning of Period | $ (492.37) | 170.4 | 0.4167% | $ - 0 | 1 | $1,000.00 | |||||
| Type: End of Period | $ (492.37) | 170.4 | 0.4167% | $ - 0 | 0 | $1,000.00 | ||||||
| TO CALCULATE THE NUMBER OF PERIODS | ||||||||||||
| Example: How long will it take to double your money? | ||||||||||||
| PV | N | R | PMT | TYPE | FV | |||||||
| Annual Compounding | Type: Beginning of Period | $ (1,000.00) | 15.2 | 5.00% | $ - 0 | 1 | $2,000.00 | |||||
| Type: End of Period | $ (1,000.00) | 14.2 | 5.00% | $ - 0 | 0 | $2,000.00 | ||||||
| Monthly Compounding | Type: Beginning of Period | $ (1,000.00) | 167.7 | 0.4167% | $ - 0 | 1 | $2,000.00 | |||||
| Type: End of Period | $ (1,000.00) | 166.7 | 0.4167% | $ - 0 | 0 | $2,000.00 | ||||||
| TO CALCULATE THE RATE OF RETURN | ||||||||||||
| Example: What rate of return do I need to pay for my kids' college if I save $X each year? | ||||||||||||
| PV | N | R | PMT | TYPE | FV | |||||||
| Annual Compounding | Type: Beginning of Period | $ - 0 | 8.0 | 9.00% | $ (1,455.78) | 1 | $17,500.00 | |||||
| Type: End of Period | $ - 0 | 8.0 | 11.37% | $ (1,455.78) | 0 | $17,500.00 | ||||||
| Monthly Compounding | Type: Beginning of Period | $ - 0 | 96 | 0.7935% | $ (121.32) | 1 | $17,500.00 | |||||
| Type: End of Period | $ - 0 | 96 | 0.8084% | $ (121.32) | 0 | $17,500.00 | ||||||
| TO CALCULATE PERIODIC LOAN OR SAVINGS PAYMENTS | ||||||||||||
| Example: What will my car loan payments be? | ||||||||||||
| PV | N | R | PMT | TYPE | FV | |||||||
| Annual Compounding | Type: Beginning of Period | $ - 0 | 8.0 | 9.00% | ($1,455.78) | 1 | $17,500.00 | |||||
| Type: End of Period | $ - 0 | 8.0 | 9.00% | $ (1,586.80) | 0 | $17,500.00 | ||||||
| Monthly Compounding | Type: Beginning of Period | $ - 0 | 96 | 0.7500% | $ (124.20) | 1 | $17,500.00 | |||||
| Type: End of Period | $ - 0 | 96 | 0.7500% | $ (125.13) | 0 | $17,500.00 | ||||||
| NOTE: TOTAL PAYMENTS | OF THE GRAND TOTAL PMTS | |||||||||||
| OF THE PERIODIC PAYMENTS ABOVE: | PER YEAR | GRAND TOTAL | PRINCIPAL | INTEREST | ||||||||
| Annual Compounding | Type: Beginning of Period | $ (1,455.78) | ($11,646.25) | $ - 0 | ($11,646.25) | |||||||
| Type: End of Period | $ (1,586.80) | $ (12,694.41) | $ - 0 | $ (12,694.41) | ||||||||
| Monthly Compounding | Type: Beginning of Period | $ (1,490.36) | $ (11,922.92) | $ - 0 | $ (11,922.92) | |||||||
| Type: End of Period | $ (1,501.54) | $ (12,012.34) | $ - 0 | $ (12,012.34) | ||||||||
| TO CALCULATE NET PRESENT VALUE (NPV) | ||||||||||||
| Example: What is the value today of a series of future cash flows? | NOTE: Extend timeline for however many periods you need --> | |||||||||||
| Timeline Periods --> | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | TOTAL | ||
| COSTS (Negative) | Initial Investment, if any | $ (5,000) | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ (5,000) | |
| Regular operating costs | (100) | (100) | (100) | (100) | (100) | (100) | (100) | (100) | (800) | |||
| Ending shutdown or cleanup costs | - | - | - | - | - | - | - | - | - | |||
| Opportunity cost | (25) | (25) | (25) | (25) | (25) | (25) | (25) | (25) | (200) | |||
| Cannibalization (if any) | (10) | (12) | (15) | (15) | (15) | (15) | (15) | (15) | (112) | |||
| Other costs (excl sunk costs) | - | - | - | - | - | - | - | - | - | |||
| BENEFITS (Positive) | New sales revenue | 50 | 300 | 500 | 600 | 600 | 600 | 600 | 700 | 3,950 | ||
| Add'l sales of existing stuff | - | - | - | - | - | - | - | - | - | |||
| Cost savings | 5 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 75 | |||
| Other incremental benefits | - | - | - | - | - | - | - | - | - | |||
| TOTAL | NET CASH FLOW | $ (5,000) | $ (80) | $ 173 | $ 370 | $ 470 | $ 470 | $ 470 | $ 470 | $ 570 | -2087 | |
| For R= | 10.00% | NPV | ($2,970) | |||||||||
| Note: "R" should be the risk-adjusted required rate of return for an investment of this estimated level of risk. It is usually between 3.0% and 20%. | ||||||||||||
| Note: Projections of future cash flows are almost always highly uncertain. Consider different scenarios, such as most likely, best case, and worst case. |
&8&F &8Printed &D &8Page &P of &N
Sheet1
| Month -> | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
| Interest Rate | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | 0.8333% | |
| Start of Month | $1,000.00 | $1,008.33 | $1,016.74 | $1,025.21 | $1,033.75 | $1,042.37 | $1,051.05 | $1,059.81 | $1,068.64 | $1,077.55 | $1,086.53 | $1,095.58 | |
| Interest Charge | $8.33 | $8.40 | $8.47 | $8.54 | $8.61 | $8.69 | $8.76 | $8.83 | $8.91 | $8.98 | $9.05 | $9.13 | |
| End of Month | $1,008.33 | $1,016.74 | $1,025.21 | $1,033.75 | $1,042.37 | $1,051.05 | $1,059.81 | $1,068.64 | $1,077.55 | $1,086.53 | $1,095.58 | $1,104.71 | |
Help-Depreciation
| DOES DEPRECIATION EXPENSE AFFECT CASH FLOW? | ||||||
| SITUATION | ||||||
| - | Suppose you have a really simple business: you've bought a new 3-D printer, and you rent it out to fellow SNHU students for $4000/yr. | |||||
| - | The students who rent the printer are responsible for supplies and maintenance. | |||||
| - | You estimate the printer's useful life is 3 years, and at the end of that time you can sell it for $500. | |||||
| - | Accounting rules require you to "recognize" the printer's cost by spreading it over the estimated useful life. | |||||
| YEAR 1 | YEAR 2 | YEAR 3 | CUMULATIVE | |||
| 1. | You buy the printer for cash, & sell it 3 yrs later. | Investment | $ (10,000) | $ - 0 | $ 500 | $ (9,500) |
| 2. | You rent it to other students for $4,000 / year | Revenue | $ 4,000 | $ 4,000 | $ 4,000 | $ 12,000 |
| 3. | You record depreciation expense for the printer | Expense | $ (3,167) | $ (3,167) | $ (3,167) | $ (9,500) |
| Notice the "Cumulative" column: Cumulative depreciation equals cumulative cash flow. Depreciation is simply a spreading out of the cash flow. | ||||||
| IN A REALLY SIMPLE WORLD, YOUR FINANCIALS MIGHT LOOK LIKE THIS: | ||||||
| REVENUE | $ 4,000 | $ 4,000 | $ 4,000 | $ 12,000 | ||
| Buying and Selling of 3-D Printer | $ (10,000) | $ - 0 | $ 500 | $ (9,500) | ||
| PRETAX PROFIT OR (LOSS), EQUALS PRETAX CASH FLOW | $ (6,000) | $ 4,000 | $ 4,500 | $ 2,500 | ||
| Tax Refunds or (Payments), @ 40% Tax Rate | $ 2,400 | $ (1,600) | $ (1,800) | $ (1,000) | ||
| NET PROFIT OR (LOSS), EQUALS NET CASH FLOW | $ (3,600) | $ 2,400 | $ 2,700 | $ 1,500 | ||
| Notice that the net profit line is rather lumpy. | ||||||
| BUT YOUR ACCOUNTING INCOME STATEMENTS WILL LOOK LIKE THIS: | ||||||
| REVENUE | $ 4,000 | $ 4,000 | $ 4,000 | $ 12,000 | ||
| Depreciation Expense | $ (3,167) | $ (3,167) | $ (3,167) | $ (9,500) | ||
| OPERATING PROFIT OR (LOSS) | $ 833 | $ 833 | $ 833 | $ 2,500 | ||
| Tax Provision Expense @40% Tax Rate | $ (333) | $ (333) | $ (333) | $ (1,000) | ||
| NET PROFIT (OR LOSS) | $ 500 | $ 500 | $ 500 | $ 1,500 | ||
| Notice that the net profit line above is smooth, but the Cumulative column hasn't changed. | ||||||
| AND YOUR CASH FLOW STATEMENTS (STANDARD FORMAT) WILL LOOK LIKE THIS: | ||||||
| Net Profit (Or Loss) from Above | $ 500 | $ 500 | $ 500 | $ 1,500 | ||
| Add back: Depreciation Expense | $ 3,167 | $ 3,167 | $ 3,167 | $ 9,500 | ||
| Change in Working Capital & Other Operating Activities | $ - 0 | $ - 0 | $ - 0 | $ - 0 | ||
| Cash Flow From Operating Activities | $ 3,667 | $ 3,667 | $ 3,667 | $ 11,000 | ||
| Capital Expenditures | $ (10,000) | $ - 0 | $ - 0 | $ (10,000) | ||
| Proceeds from Sale of Assets | $ - 0 | $ - 0 | $ 500 | $ 500 | ||
| Cash Flow from Investing Activities | $ (10,000) | $ - 0 | $ 500 | $ (9,500) | ||
| Additional Paid-in Capital | $ - 0 | $ - 0 | $ - 0 | $ - 0 | ||
| Dividends Paid | $ - 0 | $ - 0 | $ - 0 | $ - 0 | ||
| Cash Flow from Financing Activities | $ - 0 | $ - 0 | $ - 0 | $ - 0 | ||
| NET CASH FLOW | $ (6,333) | $ 3,667 | $ 4,167 | $ 1,500 | ||
| Cash Balance at Beginning Of Year | $ 10,000 | $ 3,667 | $ 7,333 | |||
| Cash Balance at End of Year | $ 3,667 | $ 7,333 | $ 11,500 | |||
