Problem 5: Long Term Financial Planning You work as an analyst covering Mernie Badoff Inc. Your boss is concerned about the large investment he has in this firm and wants you to produce a valuation template to help him decide whether he should maintain his large holdings or not. He already came up with what he believes are realistic assumptions about the future of Mernie Badoff Inc. Your task is two-fold: 1. Produce a pro-forma model using the given assumptions. Consider Debt the plug variable. If no information is given about a certain line item, assume it stays constant. Using this model, go through a valuation exercise and come up with the projected price per share using end of year discounting. 2. Perform a sensitivity analysis of price per share to changes in Costs/Sales. Let Costs/Sales vary from 40% to 95% in increments of 5%. Make a plot of price per share vs Costs/Sales and briefly explain your results. 3. In your original valuation model, your boss told you to use debt as the plug variable. After extensive conversations with the CEO of Mernie Badoff Inc., you now learned that he would like to control his level of debt. He would also like to increase dividends every year and to vary his marketable securities (see below for specifics). On the other hand, he is not very concerned with liquidity issues, so he communicated that he would be able to handle large variations in cash (hence, cash is a more appropriate plug for your model). Copy the model from Part 1 above and incorporate the following changes: (1) long term debt increases by 15% every year; (2) the dividends increase by 10% every year; (3) marketable securities vary with sales (they represent 7% of sales); (4) cash becomes the plug. Identify each line you changed (make it green). Next to each line you modified, use the FORMULATEXT () function to show me the formula you used for that line. Are these assumptions feasible going forward (YES/NO)? Briefly explain your answer.