Applied Finance Assignment
MGMT 648 : Applied Finance Crotty, Fall 2017 Final Exam GENERAL INSTRUCTIONS You may take up as much time as you would like to work this exam. Please download the Excel template from Canvas . You should upload your final by 11:59 pm on Sunday , August 2 7th. The exam is open note and open book. You may not consult your classmates for any reason . The exam has three spreadsheet -based questions. Please assume that I will print your exam and grade the print -out. Make sure your spreadsheet is well documented and easy to follow so that I can give you as much credit as possible. For instanc e, displaying formulas with FORMULATEXT is very helpful. If you only want me to see a specific part of your worksheet, select the part you want me to see and set that as the print area, using the ‘Print Area’ command in the Page Layout tab in Excel. Pleas e make sure your ID# is on the first page of the exam (top right) , along with your mailbox . After you complete the exam, uplo ad your excel sheet to Canvas . Your exam file must be l abeled with your student ID as XXXXXXXX.xls (i.e., please replace the X’s with your student ID number.) Remember, if you want partial credit, you need to show me detailed steps. Also , please save your work at least every 10 minutes. Good Luck! Q1 (40 points) : Consider two risky assets with the following attributes: Expected Return Standard Deviation Asset 1 11% 18% Asset 2 9% 25% Assume that the asset returns are each normally distributed and that the correlation between the assets’ returns is 0. 4. T he risk -free rate is 3%. a. (15 points) What is the optimal portfolio of the two risky assets? b. (5 points) Please explain in a concise sentence or two how you solved part (a). A fellow analyst is also considering the optimal combination of the two assets and is assessing their proposed portfolio through simulation using @Risk . Specifically, your colleague is arguing that a portfolio that is 95% in asset 1 and 5% in asset 2 is more attractive than the optimal portfolio you found in part (a). Their evidence for this is the following simulation of the asset returns a nd the 95/5 portfolio returns. The simulation model and output are on the following page ( and are duplicated in the first tab of the soft -copy spreadsheet template available on Canvas under Assignments/Final ). c. (10 points) Based on your colleague’s sim ulation output, what is the expected return , standard deviation, and Sharpe ratio of the 95/5 portfolio? d. (10 points) Critically assess your colleague’s Monte Carlo model and propos ed portfolio relative to what you proposed in part (a). Q2 (20 points ) Based on the regression s below , determine the valu e per share (price) of the firm . Assume that the risk free rate is 3% and the market premium is 5%. Dividends are paid every year, and the last dividend paid of $2.50 per share was just paid . The n ext dividend is one year away. (Hint: It is always important to check for statistical significance. ) Regression Model 1:
% = + + for t =1,2,…,20 where % Δ= −−1 −1 . This model forecasts percentage changes in dividends per share . Regression Statistics R Square 0.02 Adjusted R Square 0.01 Standard Error 0.34 Observations 20.00 Variable Coefficient Std. Err. t Stat P-value Intercept 0.051 0.002 25.56 0.00 Time 0.01 0.21 0.0476 0.96 Regression Model 2: ,− ,= + (,− ,)+ This model estimates the relationship between percentage changes in the price of the stock in excess of the risk -free rate to percentage changes in the level of the market index in excess of the risk -free rate . Regression Statistics R Square 0.72 Adjusted R Square 0.70 Standard Error 0.34 Observations 20.00 Variable Coefficient Std. Err. t Stat P-value Intercept 0.002 0.002 1.00 0.33 Market Returns 1.27 0.46 2.76 0.01 Q3 (40 points) You are interning in the treasury group of a company that recently experienced a large negative shock in the price of their output good. Luckily, the firm still has access to debt markets, and management wants to know how much debt the company may need to issue today in order to be sure to have cash on hand for the next 5 years in the event output prices do not recover for five years. Management is reluctant to consider dividend cuts, but they want the model to be able to accommodate this possibility as w ell. Build out a pro -forma of the firm with the following assumptions and those in the soft -copy template available on Canvas under Assignments/Final. 1. Any new debt will be borrowed over the coming year (that is, it will be included in the year 1 fore casted debt balance), and should be sufficiently large to ensure positive cash balances for the next five years. Debt will remain at this same new level from year 1 through year 5. Management wants the projected year 5 cash balance to equal the current balance of $ 14 . In the interim, they are fine with cash balances floating around. This means that the plug in the model for year 5 is the debt balance, and the plug for years 1 -4 is the cash balance. 2. The firm will not issue or repurchase stock. 3. The output price has declined by 45% relative to year 0 and is expected to remain constant over the five year period. As a result, the number of units sold is expected to be 10% higher over the coming year compared to year 0 unit sales and will remain constant over the five year period. 4. Unfortunately, input prices have not fallen. COGS and Inventory are both tied to units sold. Costs are $3.50 per unit. The end -of-period inventory reflects 1/3 of the forecasted cost of the next year’s unit sales (please assume ye ar 6 unit sales are projected to be the same as those from years 1 -5). 5. The firm will not divest or invest in capital assets, so gross fixed assets will remain constant. 6. The firm can take tax credits associated with any operating losses. Build a one -page spreadsheet model to answer the questions listed below: 1. (25 points) Complete the five -year forecasted pro -forma financial statement for the firm assuming dividends paid remain constant. How much new debt must be issued from year 0 to year 1? 2. (15 po ints) If the firm cuts its dividend, it will do so in the first year (that is, it will remain at the new level from year 1 through year 5). Incorporate a possible dividend cut into your model. Specifically, create a data table and plot that shows the amo unt of new debt that must be issued from year 0 to year 1 as a function of the magnitude of the dividend cut. Consider dividend cut magnitudes (in % terms) of 0% to 25 % in increments of 2.5%. At approximately what dividend cut magnitude would the firm no t need any new debt?