Sentimental Novelties
• Please read the attached case with exhibits carefully and answer all the questions. Please provide a single summary tab in your spreadsheet that summarizesboth your written and calculated answers.
• You must use the valuation template included in the workbookfile provided below to introduce some consistency among submissions but may modify and add sheets to it as you see fit.The template has some numbers filled in only for the purpose of reducing cell errors and are not meant to be values you use. You must indicate the source of spreadsheets you adapted and incorporated, as long as those spreadsheets are not directly applicable to this case study.
• You may find different data that is inconsistent with that provided in this case study workbook or mistakes in the case study data. Just like in the real world, data provided to you is not always correct! You may choose to use better data if you wish.
• Remember to provide ALL sources of information used (with a clip or snip in addition to webpage links) and clearly highlight/circle the exact data used. You are required to show all your work clearly, along with data sources and raw data extracted from data sources that are used in ratio or other calculations.
• There is no separate Word document, and only focus on excel sheet with clear explanation and calculation.
Questions:
1. [15 Points] According to the case, the market is assigning Sentimental Novelties an Enterprise-value-to-EBITDA ratio of 3.5.
a. Prepare a table of comparable data and ratios for at least 5-10 peer companies. Expand your search beyond those in Exhibit 7; more comparables are better than less.
b. What Enterprise-value-to-EBITDA ratio would you recommend for Sentimental Novelties if you disagree with the market and explain why? What stock price for the company would this valuation imply?
2. [25 Points] Estimate an appropriate cost of capital for Sentimental Novelties making believe that tax reform applied from 2017 onwards.
3. [45 Points] Please model the cash flows for Sentimental Novelties for fiscal years 2017 through 2021 and estimate the implied enterprise value of Sentimental Novelties and its corresponding price per share. The case provides some guidance on two scenarios for cash flow forecasts: a bullish and a bearish forecast and you must present the results for both scenarios. You may use a marginal tax rate of 25%, reflecting the new federal rate plus state/local taxes. Needless to say, you may have to prepare otherreasonable data estimates where there is incomplete data.
4. [5 Points] Prepare a football chart of alternate valuation results.
5. [10 Points] Should the management of Sentimental Novelties repurchase the shares? A $75 million repurchase program has been suggested. Are there alternatives to a share repurchase that should also be considered? Justify your response.
How to adjust the worksheets and answer the question by calculation
In Excel worksheet
● Blue cells are for data input
● Green cells are links to between the different worksheets
● Black cells are formulas (don’t change)
● Remember to press F9 to update the data tables
• The DCF and DCF working in capital are the templates, and the writers should use them to complete all the questions.
• Identify and find good comparable companies in Exh7 (all the companies should be same industry, some of the companies in this sheet is not good, need to be delete)
Requirements for worksheets
a) Should WACC be based on Net Debt or expand the WACC calculation to separately weight surplus cash?
• Use Separately growth debt and separable cash to calculate WACC (in this case use three components: cash, equity and debt to calculate, it has a template in excel worksheet, just fill in after estimated)
b) How should levered beta be estimated?
• First identify good comparable companies, use their Betas to get leverage Betas
• Then unlevered betas for those companies
• Then take the average of unlevered betas, then re-levered betas based on the sentimental novelties. (find the appropriate formula to calculate from levered to unlevered)
c) Should other assets &liabilities be reflected in the “working capital” calculation?
• Other assets and liabilities should be reflected in the net working capital calculation
• When you estimate the free cash flow, you should use the operating cash, since there is no given operating cash, you should estimate the operating cash to calculate
• When you calculate the working capital, ignore the deferred taxes since it is not given, and don’t need to estimate the deferred tax
d) If depreciation is already included in some of the other P&L lines, how should the model be modified?
• You should find the depreciation to do the free cash flow calculation by using PPE in Exh. 3 (Since the depreciation is not given, you should use this formula to find the depreciation. Formula Net PPE = Growth PPE+ Capital Expenditure – accumulate depreciation)
e) What modifications need to be made to use APV or ECF method if leverage is not constant?
• First to decide whether this case has a constant leverage or not
• If not, what modifications need to be made, to use APV (Adjust present value) method or ECF (equity cash flow) method? Then show your work and explain it.
f) Do you want to better organize where data inputs are located?
• Choose to organize date input better, show your work
g) If you change the model relationships, be sure that you don’t introduce errors!
h) When you calculate the free cash flow, you should subtract the working capital change
i) When you fill in the DCF – Working capital Exhibit, please add in one role of the operating cash to calculate. (using the operating cash you estimated before)
