Finance
Batman007
midterm.part_.b.s2.2015.xlsIncome Stmt
| Professor h | ||||||
| Supervalue | ||||||
| Income Statement | ||||||
| All numbers are in millions. | ||||||
| Actual | Projected | |||||
| 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | |
| Total Revenue | 36,100.0 | 36,461.0 | 36,825.6 | 37,193.9 | 37,565.8 | 37,941.5 |
| Cost of Revenue | 25,992.0 | 364.6 | 368.3 | 371.9 | 375.7 | 379.4 |
| Gross Profit | 10,108.0 | 36,096.4 | 36,457.3 | 36,822.0 | 37,190.1 | 37,562.1 |
| Operating Expenses | ||||||
| Depreciation | 949.0 | 6,362.0 | 6,365.6 | 6,369.2 | 6,372.9 | 6,376.6 |
| S, G & A | 5,054.0 | 364.6 | 368.3 | 371.9 | 375.7 | 379.4 |
| Non-recurring charge | 1,400.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| Operating Income | 2,705.0 | 29,369.8 | 29,723.4 | 30,080.9 | 30,441.5 | 30,806.1 |
| Interest Expense | 410.8 | 49.2 | 49.2 | 49.2 | 49.2 | 49.2 |
| Earnings before income taxes | 2,294.2 | 29,320.6 | 29,674.2 | 30,031.7 | 30,392.3 | 30,756.9 |
| Income Taxes | 803.0 | 293.2 | 296.7 | 300.3 | 303.9 | 307.6 |
| Net Income | 1,491.2 | 29,027.4 | 29,377.5 | 29,731.4 | 30,088.4 | 30,449.3 |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Bal Sheet
| Professor h | ||||||
| Supervalue | ||||||
| Balance Sheet | ||||||
| All numbers are in millions. | ||||||
| 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | |
| Cash | 1,750 | 35,936 | 65,310 | 95,037 | 125,122 | 155,568 |
| Receivables | 4,097 | 365 | 368 | 372 | 376 | 379 |
| Inventories | 4,783 | 365 | 368 | 372 | 376 | 379 |
| Current Assets | 10,630 | 36,666 | 66,046 | 95,781 | 125,874 | 156,326 |
| Net PPE | 6,362 | 6,366 | 6,369 | 6,373 | 6,377 | 6,380 |
| Goodwill | 2,466 | 2,466 | 2,466 | 2,466 | 2,466 | 2,466 |
| Total Assets | 19,458 | 45,497 | 74,881 | 104,620 | 134,717 | 165,172 |
| Payables | 2,888 | 365 | 368 | 372 | 376 | 379 |
| Other Liabilities | 830 | 365 | 368 | 372 | 376 | 379 |
| Long Term Debt | 4,924 | 4,924 | 4,924 | 4,924 | 4,924 | 4,924 |
| Common Equity | 1,762 | 1,762 | 1,762 | 1,762 | 1,762 | 1,762 |
| Retained Earnings | 9,054 | 38,081 | 67,459 | 97,190 | 127,279 | 157,728 |
| Total Liabilities & Equity | 19,458 | 45,497 | 74,881 | 104,620 | 134,717 | 165,172 |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Valuation
| Professor h | ||||||
| Supervalue | ||||||
| Valuation | ||||||
| All numbers are in millions. | ||||||
| 2016 | 2017 | 2018 | 2019 | 2020 | ||
| EBIT | 29,369.8 | 29,723.4 | 30,080.9 | 30,441.5 | 30,806.1 | |
| Depreciation | 6,362.0 | 6,365.6 | 6,369.2 | 6,372.9 | 6,376.6 | |
| Taxes | 293.7 | 297.2 | 300.8 | 304.4 | 308.1 | |
| NWC | 5,162.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| CapEx | -6,365.6 | -6,369.2 | -6,372.9 | -6,376.6 | -6,380.4 | |
| Terminal Value | 7 x EBITDA | 260,278.9 | ||||
| Free Cash Flow | 34,234.5 | 29,422.6 | 29,776.4 | 30,133.4 | 290,773.1 | |
| Discount Factor | 1.00% | 0.9901 | 0.9803 | 0.9706 | 0.961 | 0.9515 |
| Enterprise Value (DCF) | 397,268.4 | 33,895.6 | 28,843.0 | 28,901.0 | 28,958.2 | 276,670.6 |
| Debt | 4,924.0 | |||||
| Cash | 1,750.0 | |||||
| Equity Value | 394,094.4 |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Subsidary Schedules
| Professor h | ||||||
| Supervalue | ||||||
| Subsidary Schedules | ||||||
| 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | |
| Net Working Capital | ||||||
| Receivables | 4097 | 365 | 368 | 372 | 376 | 379 |
| Inventories | 4783 | 365 | 368 | 372 | 376 | 379 |
| Payables | 2888 | 365 | 368 | 372 | 376 | 379 |
| Other Liabilities (current) | 830 | 365 | 368 | 372 | 376 | 379 |
| Total | 5162 | 0 | 0 | 0 | 0 | 0 |
| Change NWC | 5162 | 0 | 0 | 0 | 0 | |
| Retained Earnings | ||||||
| Beg | 9054 | 38081.4 | 67458.9 | 97190.3 | 127278.7 | |
| NI | 29027.4 | 29377.5 | 29731.4 | 30088.4 | 30449.3 | |
| Div | 0 | 0 | 0 | 0 | 0 | |
| End | 9054 | 38081.4 | 67458.9 | 97190.3 | 127278.7 | 157728 |
| Capital Expenditures | ||||||
| Change in Sales | 361 | 364.6 | 368.3 | 371.9 | 375.7 | |
| Asset/Sales Ratio | 1.00% | 1.00% | 1.00% | 1.00% | 1.00% | |
| Growth Expenditures | 3.6 | 3.6 | 3.7 | 3.7 | 3.8 | |
| Replacement Expenditures | 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | |
| 6365.6 | 6369.2 | 6372.9 | 6376.6 | 6380.4 | ||
| Depreciation Calculation | ||||||
| Beg PPE | 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | |
| Depreciation Rate (1/life) | 1 | 1 | 1 | 1 | 1 | |
| 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | ||
| PPE | ||||||
| Beg | 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | |
| Additions | 6365.6 | 6369.2 | 6372.9 | 6376.6 | 6380.4 | |
| Depreciation or Disposals | 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | |
| End | 6362 | 6365.6 | 6369.2 | 6372.9 | 6376.6 | 6380.4 |
| Debt | ||||||
| Beg | 4924 | 4924 | 4924 | 4924 | 4924 | |
| Additions | 0 | 0 | 0 | 0 | 0 | |
| Reductions | 0 | 0 | 0 | 0 | 0 | |
| End | 4924 | 4924 | 4924 | 4924 | 4924 | 4924 |
| Interest Calculation | ||||||
| Beg Debt Level | 4924 | 4924 | 4924 | 4924 | 4924 | |
| Interest Rate | 1.00% | 1.00% | 1.00% | 1.00% | 1.00% | |
| 49.2 | 49.2 | 49.2 | 49.2 | 49.2 | ||
| Cash | ||||||
| Beg | 1,750.0 | 35,935.8 | 65,309.7 | 95,037.4 | 125,122.1 | |
| Operating Cash Flow | 35,389.4 | 35,743.1 | 36,100.6 | 36,461.3 | 36,825.9 | |
| NWC | 5,162.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| Debt Borrowings | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| Sources | 40,551.4 | 35,743.1 | 36,100.6 | 36,461.3 | 36,825.9 | |
| Dividends | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| Capital Expenditures | 6,365.6 | 6,369.2 | 6,372.9 | 6,376.6 | 6,380.4 | |
| Debt Payments | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| Uses | 6,365.6 | 6,369.2 | 6,372.9 | 6,376.6 | 6,380.4 | |
| End | 1750 | 35,935.8 | 65,309.7 | 95,037.4 | 125,122.1 | 155,567.6 |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Assumptions
| Professor h | |||
| Supervalue | |||
| Assumptions | |||
| Author | Professor h | ||
| Initial year | 2015 | ||
| Receivables | % of Sales | 1.00% | |
| Inventories | % of Sales | 1.00% | |
| Payables | % of Sales | 1.00% | |
| Other liabilities | % of Sales | 1.00% | |
| Cost of Revenue | % of Sales | 1.00% | |
| SGA | % of Sales | 1.00% | |
| Interest Rate | % of Debt | 1.00% | |
| Tax Rate | % of EBT | 1.00% | |
| Dividend | % of NI | 0.00% | |
| Discount Rate | 1.0% | ||
| Depreciation Life | Years | 1 | Based on historical depreciation-to-fixed assets ratio |
| Capital Expenditures | % of Chg.Sales | 1.00% | Based on historical fixed assets-to-sales ratio |
| Growth Rate | 2016 | 1.0% | |
| 2017 | 1.0% | ||
| 2018 | 1.0% | ||
| Terminal | 1.0% | ||
| Non-Recurring | % of Sales | 0 | Careful, non-recurring means non-recurring! |
| Debt Growth | % Growth Rate | 0 | During five year forecast period, 3% after that. |
| Debt Reduction | % of O/S Balance | 0 | |
| Round | Num of places | 0 |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Instructions
| Instructions & Comments |
| Below are historical financial statements and other pertinent data for Supervalue Inc. Please use these data in the following exercises. You may assume for the purposes of your calculations that "today" is Jan 1, 2016—that is, you do not have to worry about partial year calculations. Round your answers to the nearest 1,000 (consistent with initial year reporting). |
| A recent article in the Wall Street Journal reports that Supervalue (owner of grocery stores such as Albertsons and Jewel-Osco) is considering putting itself up for sale. Supervalue's earnings have taken a hit in the most recent quarter, falling 45%. Its share price has been falling as well, and management seems to be abandoning its previously optimistic outlook. Let's say you are the CEO of a competing grocery store chain and are considering the acquisition of Supervalue. You are confident that you will be able to achieve synergies, and you plan to operate the target as a subsidiary. You are now ready to calculate the value of Supervalue to determine if these synergies will be enough to make this a deal worth pursuing, and what price you should offer. Use these assumptions and the provided historical financial statements to answer the following questions. |
| Please consider the following assumptions: |
| Supervalue expects the expansion will increase revenues and operating expenses by 20% in 2016, 15% in 2017, 10% in 2018 and 3% thereafter. |
| The levels of cash and interest-bearing debt will grow at the same rate as free cash flows after the five year period. Any liabilities labeled as "other" are non-interest-bearing. |
| Supervalue's marginal cost of debt is 8.0%, and WACC is 11.0%. The marginal tax rate is 35%. |
| Make any other assumptions you feel are necessary to perform the following tasks, and explain why you are making them. |
| Required |
| (A) 15 points. In Excel, create a pro forma income statement and balance sheet for Supervalue for next five years after initial year. |
| (B) 15 points. Refer to your pro forma income statement from part (a) and the pro forma balance sheet provided. All numbers are in thousands of dollars. What are Supervalue's free cash flows for 2016 through 2020? You may provide your answer using the attached template. |
| (c) 10 points. Based on your previous answers and using a DCF analysis, what is Supervalue's current (beginning 2013) enterprise value? Equity value? Use the EBITDA multiple method to calculate the terminal value. The appropriate EBITDA multiple is 7x. |
| (D) 4 points. Given your calculations in part (c), what is the maximum price (equity value) you would be willing to pay for Supervalue? Briefly explain why you wouldn't be willing to pay more than this. Fee free to comment on it. |
| An income statement, balance sheet, subsidiary schedules and assumptions page have been provided for you. You will not be able to change the financial statements and schedules directly. They will be locked. You WILL have to modify the assumptions based on your judgment. That will change the income, assets, liabilities, and equity automatically. However,You WILL have to create the valuation sheet. A comment sheet is provided as the last tab to answer the text questions. Do not forget to enter your name in the name field in the assumptions sheet, it is labeled 'Your name goes here'. You may be tempted to try to reuse this spreadsheet for other assignments, not a good idea. Certainly you can use it for ideas. Of course, do not share ideas, data, etc. with others while working on this exam. |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
Students Comments
| Professor h |
| Student's Comments |
&C&"Times New Roman,Regular"&12&A
&C&"Times New Roman,Regular"&12Page &P
