who can help me done these 3 finance problems within next 2 hours? Also need to fill out the templete

Q1 Asset 1 Asset 2 Correlation Risk-free rate Standard Expected Return Deviation 11% 9% 0.4 3% Student ID: 18% 25% Mailbox: Q2 Note: no initial template given Q3 Assumptions Growth in units sold from year 0 to year 1 Unit price growth from year 0 to year 1 COGS (per unit sold) Other Current Assets/Sales Inventory as % of forecasted COGS next year Current Liabilities/Sales Depreciation rate (% of avg. gross fixed assets) Interest rate on debt Interest paid on cash and mrk sec. Tax rate Dividend Cut Percentage from year 0 to year 1 10% -45% $3.50 15% 33% 8% 10% 10% 8% 40% 0% Year 0 Forecast 1 Income statement Units Price Sales COGS Interest payment on debt Interest earned on Cash and Mrk Sec. Depreciation Profit Before tax Taxes Profit after tax Dividends Retained earnings Balance Sheet Cash and Marketable Sec. Other Current Assets Inventory Fixed Assets At cost Depreciation Net Fixed Assets Total Assets 100.00 $7.00 $700 -$350 -$3 $4 -$70 $281 -$113 $169 -$63 $106 $14 $105 $128 $749 -$210 $539 $786 Current liabilities Debt Stock Accumulated Retained Earnings $56 $30 $200 $500 Total liabilities and equity $786 110.00 $3.85 Notes: Other Current assets/Sales does not include cash or inventory Depreciation and interest are calculated based on average Balance Sheet items. The firm can take any tax credits associated with losses. Forecast 2 Forecast 3 110.00 $3.85 Forecast 4 110.00 $3.85 Forecast 5 110.00 $3.85 110.00 $3.85 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 27th. 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 instance, 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. Please make sure your ID# is on the first page of the exam (top right), along with your mailbox. After you complete the exam, upload your excel sheet to Canvas. Your exam file must be labeled 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 Asset 1 11% Asset 2 9% Standard Deviation 18% 25% Assume that the asset returns are each normally distributed and that the correlation between the assets' returns is 0.4. The 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 and 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 simulation 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 proposed portfolio relative to what you proposed in part (a). Q2 (20 points) Based on the regressions below, determine the value 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 next dividend is one year away. (Hint: It is always important to check for statistical significance.) Regression Model 1: where % = 1 1 % = + + for t =1,2,...,20 . 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 Coefficient Std. Err. t Stat P-value Intercept Variable 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 well. 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 forecasted 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 year 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 points) 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 amount 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 not need any new debt