Note: PLEASE Use the excel sheet provided by me. All you have to do is Change the assumption value in Assumptions tab and Make a new VALUATION SHEET with proper format. PLEASE READ THE INSTRUCTIONS CAREFULLY..... 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 January 1, 2016?that is, you do not have to worry about partial year calculations. Round your answers to the nearest 1,000 (consistent with lst year's 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.

Beginning in 2018, Supervalue will settle into a permanent free cash flow growth rate of 3% per year.

The levels of cash and interest-bearing debt are expected to remain constant through 2017, then they will grow at the same rate as free cash flows. Any liabilities labeled as "other" are non-interest-bearing.

Supervalue's marginal cost of debt is 7.9%, 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 2016 through 2020.

(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

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

Professor h Supervalue Valuation All numbers are in millions. 2016 EBIT Depreciation Taxes NWC CapEx Terminal Value Free Cash Flow Discount Factor Enterprise Value (DCF) Debt Cash Equity Value 2017 2018 2019 2020 31,349.4 6,362.0 313.5 3,465.0 -6,365.8 31,722.6 6,365.8 317.2 0.0 -6,369.6 32,099.9 6,369.6 321.0 0.0 -6,373.5 32,480.5 6,373.5 324.8 0.0 -6,377.4 34,497.1 31,401.6 31,775.0 32,151.8 32,865.3 6,377.4 328.7 0.0 -6,381.4 274,698.9 307,231.5 1.00% 0.9901 0.9803 0.9706 0.961 0.9515 419,008.1 34,155.6 30,783.0 30,840.8 30,897.9 292,330.8 7 x EBITDA 4,924.0 1,750.0 415,834.1 Professor h Supervalue Income Statement All numbers are in millions. Actual Total Revenue Cost of Revenue Gross Profit Operating Expenses Depreciation S, G & A Non-recurring charge Operating Income Interest Expense Earnings before income taxes Income Taxes Net Income 2015 38,100.0 25,910.0 12,190.0 Projected 2016 38,481.0 384.8 38,096.2 2017 38,865.8 388.7 38,477.1 2018 39,254.5 392.5 38,862.0 2019 39,647.0 396.5 39,250.5 2020 40,043.5 400.4 39,643.1 834.0 4,953.0 1,600.0 4,803.0 345.0 4,458.0 1,560.0 2,898.0 6,362.0 384.8 0.0 31,349.4 49.2 31,300.2 313.0 30,987.2 6,365.8 388.7 0.0 31,722.6 49.2 31,673.4 316.7 31,356.7 6,369.6 392.5 0.0 32,099.9 49.2 32,050.7 320.5 31,730.2 6,373.5 396.5 0.0 32,480.5 49.2 32,431.3 324.3 32,107.0 6,377.4 400.4 0.0 32,865.3 49.2 32,816.1 328.2 32,487.9 Professor h Supervalue Balance Sheet All numbers are in millions. 2015 1,750 4,873 3,538 10,161 2016 35,889 385 385 36,659 2017 66,928 389 389 67,706 2018 98,337 393 393 99,123 2019 130,119 396 396 130,911 2020 162,278 400 400 163,078 Net PPE 6,362 6,366 6,370 6,374 6,377 6,381 Goodwill 2,466 2,466 2,466 2,466 2,466 2,466 18,989 45,490 76,541 107,962 139,754 171,925 Payables Other Liabilities Long Term Debt 3,682 1,264 4,924 385 385 4,924 389 389 4,924 393 393 4,924 396 396 4,924 400 400 4,924 Common Equity Retained Earnings 1,762 7,357 1,762 38,034 1,762 69,077 1,762 100,490 1,762 132,276 1,762 164,439 18,989 45,490 76,541 107,962 139,754 171,925 Cash Receivables Inventories Current Assets Total Assets Total Liabilities & Equity Professor h Supervalue Subsidary Schedules 2015 Net Working Capital Receivables Inventories Payables Other Liabilities (current) Total Change NWC Retained Earnings Beg NI Div End 2016 2017 2018 2019 2020 4873 3538 3682 1264 3465 385 385 385 385 0 3465 389 389 389 389 0 0 393 393 393 393 0 0 396 396 396 396 0 0 400 400 400 400 0 0 7357 30987.2 309.9 38034.3 38034.3 31356.7 313.6 69077.4 69077.4 31730.2 317.3 100490.3 100490.3 32107 321.1 132276.2 132276.2 32487.9 324.9 164439.2 381 1.00% 3.8 6362 6365.8 384.8 1.00% 3.8 6365.8 6369.6 388.7 1.00% 3.9 6369.6 6373.5 392.5 1.00% 3.9 6373.5 6377.4 396.5 1.00% 4 6377.4 6381.4 6362 1 6362 6365.8 1 6365.8 6369.6 1 6369.6 6373.5 1 6373.5 6377.4 1 6377.4 6362 6365.8 6362 6365.8 6365.8 6369.6 6365.8 6369.6 6369.6 6373.5 6369.6 6373.5 6373.5 6377.4 6373.5 6377.4 6377.4 6381.4 6377.4 6381.4 7357 Capital Expenditures Change in Sales Asset/Sales Ratio Growth Expenditures Replacement Expenditures Depreciation Calculation Beg PPE Depreciation Rate (1/life) PPE Beg Additions Depreciation or Disposals End 6362 Debt Beg Additions Reductions End 4924 0 0 4924 4924 0 0 4924 4924 0 0 4924 4924 0 0 4924 4924 0 0 4924 4924 1.00% 49.2 4924 1.00% 49.2 4924 1.00% 49.2 4924 1.00% 49.2 4924 1.00% 49.2 1,750.0 35,888.5 66,927.8 98,336.8 130,118.8 Operating Cash Flow NWC Debt Borrowings Sources 37,349.2 3,465.0 0.0 40,814.2 37,722.5 0.0 0.0 37,722.5 38,099.8 0.0 0.0 38,099.8 38,480.5 0.0 0.0 38,480.5 38,865.3 0.0 0.0 38,865.3 Dividends Capital Expenditures Debt Payments Uses 309.9 6,365.8 0.0 6,675.7 313.6 6,369.6 0.0 6,683.2 317.3 6,373.5 0.0 6,690.8 321.1 6,377.4 0.0 6,698.5 324.9 6,381.4 0.0 6,706.3 35,888.5 66,927.8 98,336.8 130,118.8 162,277.8 4924 Interest Calculation Beg Debt Level Interest Rate Cash Beg End 1750 Professor h Supervalue Assumptions Author Professor h Initial year 2015 Receivables Inventories Payables Other liabilities Cost of Revenue SGA Interest Rate Tax Rate Dividend Discount Rate Depreciation Life Capital Expenditures Growth Rate % of Sales % of Sales % of Sales % of Sales % of Sales % of Sales % of Debt % of EBT % of NI Years % of Chg.Sales 2016 2017 2018 Terminal 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.0% 1 Based on historical depreciation-to-fixed assets ratio 1.00% Based on historical fixed assets-to-sales ratio 1.0% 1.0% 1.0% 1.0% Non-Recurring % of Sales 0 Careful, non-recurring means non-recurring! Debt Growth Debt Reduction Round % Growth Rate % of O/S Balance Num of places 0 During five year forecast period, 3% after that. 0 0 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 January 1, 2016that is, you do not have to worry about partial year calculations. Round your answers to the nearest 1,000 . 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. Beginning in 2018, Supervalue will settle into a permanent free cash flow growth rate of 3% per year. The levels of cash and interest-bearing debt are expected to remain constant through 2017, then they will grow at the same rate as free cash flows. Any liabilities labeled as "other" are non-interest-bearing. Supervalue's marginal cost of debt is 7.9%, 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 2013 through 2017. (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 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. Professor h Student's Comments