讲解 AEM4060 Risk Simulation Assignment 3 Spring 2024辅导 Processing

AEM4060 Risk Simulation

Assignment 3

Spring 2024

Due by CANVAS May xxx, 2024

Directions:

(1) Your solutions must be provided in the answer template and submitted in pdf format. Excel worksheets will not be accepted.

(2) All tables and graphs etc. should be cut and paste using the Export function (lower left icon on most charts)- do not include a screenshot of the entire screen, as this makes it hard to see the graph.

(3) Make sure you show/explain your work including any assumptions you might have made.

(4) When you have built your models and are ready to submit set the SEED to 2024 and report those final results. Run the simulations at least 2500 times for submission.

[30m] PART A: Enterprise Risk and Correlation

Turn to the FarmPlan worksheet. We completed this worksheet in class to illustrate how Monte Carlo could be used to correlate enterprise budgets. The example is for a multi-product farm growing wheat, beans, soybeans, corn and canola. Enterprise budgets are commonly used for multiproduct firms to identify line profitability, and in our case risk. Agriculture is somewhat special because it is characterized by randomness in both prices and crop yields (production/output). In class we used batch fitting and used the best-fit distributions for crop yields (row 28) and lognormality for prices (row 29). The scale of production, measured in acres planted, is found in row 27. It is assumed that variable and fixed costs are non-random. The 5 crops were correlated across prices and yields.

a) [5m] The Raw Data worksheet holds the raw data for yields and prices. Using the

Data/Data Analysis/Correlation functionality in Excel, generate, and report, the 10x10 correlation matrix for crop prices and yields.

b) [5m] Using the Correlation functionality in @RISK add correlations to the model using the yield and price input functions in rows 28 and 29. Make sure that the placement of the @RISK correlation matrix is placed in cell A13. Confirm that the model captures the raw correlations by providing scatter plots for wheat yields and prices, and wheat and corn yields.

c) [5m] One of the advantages of Monte Carlo is that the correlations between all prices

and yields can be reduced to correlations between individual enterprises on a net revenue basis. The net revenues based on total acres planted are found in row 59 and on a per acre basis in row 60. The 'whole farm' total is found in Column I. The managers of multi-product firms often look at the whole business as a portfolio of the individual product lines that make up the business (in this case crops). Thus, they are interested in how each enterprise contributes to the risk of the whole business. In Row 63 use RiskCorrel function to compute the correlations between each of the 5 crops' net operating margin in row 59, and the whole farm net revenue in I59. What do these correlations tell you about enterprise risk? Report the correlations and standard deviations in Cell D63:I66.

d) [5m] Define, from any source, the term 'covariance', including its mathematical representation.

e) [10m] Strangely, @RISK does not compute covariance, and these must be computed

manually (Report the covariance matrix in cells D77 to H81). What is the variance of whole farm net margins in cell I59? In words, what is the relationship between the covariances in D77:H81 and this variance? (Hint: think portfolio theory, and examine cells C84 and C87; In other words, describe how can you obtain the variance in Cell I67 from the covariance matrix from Cell D77:H81)

[35m] Part B

Go to the Financial Statements tab. This is the worksheet for examining the coordinated financial statement under conditions of risk.

a) [2m] Define from any source the term "Operating Cash Flows".

b) [5m] Line 110 records operating cash flow for this business in thousands of dollars.

When firms face operating cash shortfalls (i.e. negative) they often must maintain liquidity reserves in the form. of a line of credit. Across all years, what is the maximum line of credit the financial managers will need to negotiate and in what year does it occur?

c) [3m] In rows 74 and 123 are recorded "Cash and securities at end of the year ". What is

the difference in this measure of cash flow to the operating cash flows in (a) and (b).(Only text answer required).

d) [6m] Across all years, what are the minimum and maximum cash flow balances and in what specific years did they occur?

e) [4m] What is the probability of negative cash flow for each of the 5 years?

f) [2m] Provide an overlay of 2007 operating cash flows from row 110 and row 123.

g) [3m] Why might the two distributions of cash flow in (f) be so different? (Hint: A good answer would explain the sustainable growth assumption applied to the irreversible acquisition of Net plant and equipment- as discussed in class- in row 78.)

h) [5m] Financial analysts often use the price earnings ratio as a rule of thumb for

forecasting share prices. In Row 17 the stock price is currently constant at $23/share Suppose that you are an analyst and know that the P/E ratio for this stock ranges from between $8/share and $13/share with a modal value of $10/share. Assuming a Pert distribution for the P/E ratio, the earnings per share as reported in row G135, and recognizing that stock prices cannot be negative. Provide a (Excel) bar chart of the expected share price for 2003 through 2007.

i) [5m] Now assume you have a client interested in buying these shares at $23. What is the probability that in 2007 the stock price is above the current price, and what is the maximum possible price attainable in 2007?

[45m] Part C

TurvCo is looking at two mutually exclusive investment alternatives. Project A - a six-year project-will cost $40,000 and Project B - a three-year project - will cost $20,000. Cash flows are risky, and for both projects are expected to rise and then fall. TurvCo’s Weighted Average Cost of Capital is 12%.

Experience allowed the managers to assign certain measures of skewness to the yearly cash flows: Project A baseline modal (consensus) cash flows from years one to six were 8000, 14000, 13000, 12000, 11000 and 10000. Using a PERT distribution, the managers assigned min. and max. pairs for each of the cash flows. These were {0.75, 1.35}, {0.8, 1.35}, {0.85, 1.40}, {0.9, 1.45}, {0.9, 1.45}, {0.95, 1.50}. For example, the minimum cash flow for year 1 would be 0.75*8000 and the maximum would be 1.35*8000.

Project B modal and consensus cash flows for years 1 to 3 were 7,000, 16,000 and 8,000. These were assigned min. and max. pairs of {.85, 1.40), {0.75, 1.30} and {0.65, 1.20}.

The first problem was the recognition that the two projects had different lives. To solve this problem, management decided that the risk analysis would be based on an annuity equivalent basis.

To compute NPV you can use Excel NPV function. =NPV(rate,cash flow range)-Initial Investment To convert NPV to an annuity you can use the Excel function, = -PMT(NPV,years,discount rate).

a) [5m] Build the NPV models to evaluate these two investments. Provide a graphic of the cumulative distribution overlays of the two investments (use annuities) including the statistical grid on the right (Hint: On the Export tab of graph image, use Copy Graph and Grid. You may have to drag the left side of the grid.) .

b) [2m] Based on the proposition of First Order Stochastic dominance which investment would you prefer and why?

c) [2m] If you were to select the investment with the lowest risk, which would you choose and why?

d) [3m] Over what range (approximations ok) does Project A stochastically dominate

Project B, and over what range does Project B dominate Project A (Hint: examine the CDF curves).

e) [3m] Assume you have Log Utility Function U= Log(NPV). Compute the expected utility from each investment. Which of project -A or B- would you choose? Briefly explain why.

[40m] Part D

ALLIED FOOD PRODUCTS

(Adapted from Brigham and Houston)

After seeing Snapple’s success with non-cola soft drinks and learning of Coke’s and

Pepsi’s interest, Allied Food Products has decided to consider an expansion of its own in the fruit juice business. The product being considered is fresh lemon juice. Assume that you were recently hired as assistant to the director of capital budgeting, and you must evaluate the new project.

The lemon juice would be produced in an unused building adjacent to Allied’s Fort

Myers plant; Allied owns the building, which is fully depreciated. The required equipment would cost $200,000, plus an additional $40,000 for shipping and installation. In addition, inventories would rise by $25,000, while accounts payable would go up by $5,000. All of these costs would be incurred at t = 0. By a special ruling, the machinery could be depreciated under the MACRS system as 3-year property. The applicable depreciation rates are 33%, 45%, 15%, and 7%.

The project is expected to operate for 4 years, at which time it will be terminated. At the end of the fourth year, all inventories related to the lemon juice business are sold and all accounts payable related to the lemon juice business are paid to the creditors. The cash inflows are assumed to begin one year after the project is undertaken, or at t = 1, and to continue out to t = 4. At the end of the project’s life (t = 4), the equipment is expected to have a salvage value of $20,000.

Unit sales are expected to total 100,000 cans in the first year increasing at 8%

thereafter, and the expected sales price is $2.00 per can but will increase at 5%/year thereafter. Cash operating costs for the project (total operating costs less depreciation) are expected to total 60% of dollar sales.

Allied’s tax rate is 20% on all net income and capital gains/losses, and its weighted average cost of capital is 15%. Tentatively, the lemon juice project is assumed to be of equal risk to Allied’s other assets.

You have been asked to evaluate the project and to make a recommendation as to

whether it should be accepted or rejected. To guide you in your analysis, your boss gave you a standard form. that is used in the capital budgeting process; see Table 1 below.

On her orders you proceeded with a standard NPV analysis, reporting on NPV and IRR. When she read your brief (which you are to provide) she started questioning you on sensitivity analysis and started to question the NPV’s sensitivity to changes in variables and risk. (Hint: Copy Allied Certainty worksheet to Allied Risk (or simply make a copy) and use that to build risk model.)

1) The $20,000 most likely value for salvage value might range from $5,000 to $50,000 (Hint, use PERT).

2) Working capital from inventories and accounts payable were reasonable for the start. Most certainly all payables will be paid, but it is possible that not all inventories will be recovered. She had no idea of how this would be distributed but suggested that inventory recovery would be no less than 80%, no more than 100% and uniformly distributed in-between.

3) Although there is an expectation of likely sales in the first year to be 100,000 units,

different opinions were raised by management with a consensus that the range of possible sales in year 1 would be no more than 120,000 and no less than 85,000 units. Thereafter, sales are expected to increase at a rate of 8%/year but with a standard deviation of 2.5%. Thereafter, sales grow rate is expected to be normally distributed with an average of 7.5%/year but with a standard deviation of 2.5%. (i.e. use RiskNormal() distribution)

4) Furthermore, prices in year 1 are also uncertain with a lognormal distribution with a

mean of $2/unit as in the deterministic case but with a standard deviation of $0.25. For years two through four, prices would grow by 5.0%/year but with a standard deviation of 1.5%. (under a normal distribution)

5) Your boss also questioned the relationship between variable operating costs and

revenues. She thought it to be true that 60% was most likely but varied from year to year. For similar drinks she noted that the rate was as low as 50% but never exceeded 70%.

6) Finally, your boss was trained in economics and thought there would be a strong

(negative) correlation between Sales and Price. That is, as prices went up sales would fall. She asked you to consider a Sales-Price correlation of -0.70. (Hint: From Correlations Tab, select Define Repeated Correlations. If you used Table 1 to set up your problem then select ‘Every Column’ and highlight the Unit Sales and Price rows.)

A) [16m] What are the means and standard deviations of (use the table of summary results and then show the respective graphs under that table):

a. Year 1 sales revenues

b. Year 2 sales price

c. Year 3 Sales revenues

d. Year 4 Operating costs

e. Terminal cash flows

f. Year 4 terminal cash flows

g. Net Present Value

h. IRR

	Mean	Standard Deviation
Year 1 sales revenues
Year 2 sales price
Year 3 sales revenues
Year 4 operating costs
Terminal cash flows (H24)
Year 4 terminal cash flows (H26)
Net Present Value
IRR

B) [5m] Show the scatter plots for units sold and prices for years 1 and 4. Are these consistent with the model inputs? What do the scatter graphs indicate to you?

C) Compare the mean NPV and its standard deviation with -0.7 correlation to NPV with 0 correlation.

D) [5m] Provide a Summary Trend for cumulative cash flows. How can this graph be related to the concept of payback?

E) Provide a Scatter Plot of IRR (x-axis) and NPV (Y-axis). What does this scatter plot tell you about the relationship between IRR and NPV?

F) If NPV is the primary measure of project selection for Allied, how might the IRR be used as a measure of risk?

G) [5m] Assume that Allied’s average project NPV has a coefficient of variation (CV=mean/std) in the range of 1.25 to 1.75. Would the lemon juice project be classified as high risk, average risk, or low risk? What type of risk is being measured here? (Note some of you may know the Coefficient of Variation to be Std/Mean. I prefer the dollar of return per dollar of risk interpretation.)

H) [4m] Prepare an overall assessment of the investment. Should they go ahead with the project and why? Include in your discussion strategic risk items such as the nature of probabilities, the probability of NPV<0 and so on. Provide figures where appropriate.

TABLE 1 Allied’s Lemon Juice Project (Total Cost in Thousands)

END OF YEAR: 0 1 2 3 4

I. Investment Outlay

Equipment cost

Installation

Increase in inventory

Increase in accounts payable

Total net investment

II. Operating Cash Flows

Unit sales (thousands)

Price/unit

Total revenues