A project under consideration has a 10-year projected life. The initial investment for the project is estimated to have a normally distributed mean of $10,000 and a standard deviation of $1,000. The annual receipts are independent, uniformly distributed with each year's expected return between $1,700 and $1900. Compound Interest Rate is 12%.

Rate: 12% Number of Periods: 10

Q1. What is the value for the initial investment?

Q2. What are the Year 1-10 values?

Q3. You are interested is reviewing the 3-period moving average. Compute the 3-period moving averages for years 4-10 in the yellow cells (J19:J25).

Q4. Now that you have the 3-period moving averages for years 4-10, you've read that a better estimate is not with each period equally weighted but with weights assigned given the period. Your weights are given in cells H31:I33. Compute the 3-period weighted moving averages for years 4-10 in the purple cells (K19:K25).

Oldest Period 0.2

Two Periods Ago 0.3

Most Recent Period 0.5

Q5. Based on the information in questions 1 & 2, what is the Net Present Value for this project? Use the values in cells H13:H25 for your cash flows.

(Since you're taking the NPV of a series of random numbers, your NPV will jump around as your random numbers recalculate. This is OK.)

Now that you've reviewed moving averages and weighted moving averages, you want to develop some random numbers and use in a Monte Carlo Simulation.

Q6. On a separate worksheet, simulate 1000 runs (column A) for the initial investment (Column B), 1000 values for the Years 1-10 values (Columns C-L), and then compute the Net Present Value in Column M. The Compound Interest Rate for each run is the same, 12% (cell G9). Be sure to keep your formulas for grading purposes. Call this worksheet 6 - MC Formulas.

On a second separate worksheet, copy and paste your values so they are frozen for analytical purposes. Call this worksheet 6 - MC Values.

Q7. Create a histogram of the simulated NPVs with the data computed on the second worksheet with the frozen values.

Create a histogram using your 6 - MC Values worksheet using the Insert Tab, Charts Section & Histogram and place it in the green cells K47:Q42.

Make sure your histogram includes the full data range.

Include:

* A histogram graph with axis labels and chart title.

NOTE: For the chart title and axis labels, do not use the defaults. Please describe what you are plotting - ensure they make sense for any observer.

Q8. For the computed NPV values, please compute the following statistics (assume your simulated numbers are a sample of the total population):

Maximum NPV -

Minimum NPV -

NPV Mean -

NPV Median -

NPV Variance -

NPV Standard Deviation -

NPV Range -

NOTE: Best practices sometimes also include confidence intervals. For simplicity, we are not including those on this worksheet, but do be aware that confidence intervals give measurements of the bounds of errors in your simulation.

A 7 Points 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 0 0 0 B Possible 5 10 10 10 D Question 1: Question 2: A project under consideration has a 10 year projected life. The initial investment for the project is estimated to have a normally distributed mean of $10,000 and a standard deviation of $1,000. The annual receipts are independent, uniformly distributed with each year's expected return between $1,700 and $1900. Compound Interest Rate is 12%. E For the following questions, determine the solutions: Question 3: cells (J19:J25) You are interested is reviewing the 3-period moving average. Compute the 3-period moving averages for years 4-10 in the yellow Question 4: (K19:K25). What is the value for the initial investment? - F What are the Year 1-10 values? - Now that you have the 3-period moving averages for years 4-10, you've read that a better estimate is not with each period equally weighted but with weights assigned given the period. Your weights are given in cells H31:133. Compute the 3-period weighted moving averages for years 4-10 in the purple cells G Rate 12% YEAR 1 2 3 4 5 6 7 8 9 10 H Number of Periods 10 Unif Dist Oldest Period - Two Periods Ago- Most Recent Period - Screenshot 0.2 0.3 0.5 J Moving Ave Weighted Ave L 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 A 0 0 0 B 5 20 20 D E Based on the information in questions 1 & 2, what is the Net Present Value for this project? Use the values in cells H13:H25 for Question 5: your cash flows. (Since you're taking the NPV of a series of random numbers, your NPV will jump around as your random numbers recalculate. This is OK.) Now that you've reviewed moving averages and weighted moving averages, you want to develop some random numbers and use in a Monte Carlo Simulation. On a separate worksheet, simulate 1000 runs (column A) for the initial investment (Column B), 1000 values for the Years 1-10 values (Columns C-L), and then compute the Net Present Value in Column M. The Compound Interest Rate for each run is the same, 12% (cell G9). Be sure to keep your formulas for grading purposes. Question 6: Call this worksheet 6-MC Formulas. On a second separate worksheet, copy and paste your values so they are frozen for analytical purposes. Call this worksheet 6 - MC Values. Create a histogram of the simulated NPVs with the data computed Question 7: on the second worksheet with the frozen values. Create a histogram using your 6-MC Values worksheet using the Insert Tab, Charts Section & Histogram and place it in the green cells K47:Q42. Make sure your histogram includes the full data range. Include: *A histogram graph with axis labels and chart title. NOTE: For the chart title and axis labels, do not use the defaults. Please describe what you are plotting- ensure they make sense for any observer. F G H Screenshot I J K Place your histogram here. 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 A 0 B 10 C E For the computed NPV values, please compute the following statistics (assume your simulated numbers are a sample of the Question 8: total population): D F NOTE: Best practices sometimes also include confidence intervals. For simplicity, we are not including those on this worksheet, but do be aware that confidence intervals give measurements of the bounds of errors in your simulation. G Maximum NPV. Minimum NPV- NPV Mean- NPV Median NPV Variance NPV Standard Deviation- NPV Range- H Screenshot J A 7 Points 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 0 0 0 B Possible 5 10 10 10 D Question 1: Question 2: A project under consideration has a 10 year projected life. The initial investment for the project is estimated to have a normally distributed mean of $10,000 and a standard deviation of $1,000. The annual receipts are independent, uniformly distributed with each year's expected return between $1,700 and $1900. Compound Interest Rate is 12%. E For the following questions, determine the solutions: Question 3: cells (J19:J25) You are interested is reviewing the 3-period moving average. Compute the 3-period moving averages for years 4-10 in the yellow Question 4: (K19:K25). What is the value for the initial investment? - F What are the Year 1-10 values? - Now that you have the 3-period moving averages for years 4-10, you've read that a better estimate is not with each period equally weighted but with weights assigned given the period. Your weights are given in cells H31:133. Compute the 3-period weighted moving averages for years 4-10 in the purple cells G Rate 12% YEAR 1 2 3 4 5 6 7 8 9 10 H Number of Periods 10 Unif Dist Oldest Period - Two Periods Ago- Most Recent Period - Screenshot 0.2 0.3 0.5 J Moving Ave Weighted Ave L 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 A 0 0 0 B 5 20 20 D E Based on the information in questions 1 & 2, what is the Net Present Value for this project? Use the values in cells H13:H25 for Question 5: your cash flows. (Since you're taking the NPV of a series of random numbers, your NPV will jump around as your random numbers recalculate. This is OK.) Now that you've reviewed moving averages and weighted moving averages, you want to develop some random numbers and use in a Monte Carlo Simulation. On a separate worksheet, simulate 1000 runs (column A) for the initial investment (Column B), 1000 values for the Years 1-10 values (Columns C-L), and then compute the Net Present Value in Column M. The Compound Interest Rate for each run is the same, 12% (cell G9). Be sure to keep your formulas for grading purposes. Question 6: Call this worksheet 6-MC Formulas. On a second separate worksheet, copy and paste your values so they are frozen for analytical purposes. Call this worksheet 6 - MC Values. Create a histogram of the simulated NPVs with the data computed Question 7: on the second worksheet with the frozen values. Create a histogram using your 6-MC Values worksheet using the Insert Tab, Charts Section & Histogram and place it in the green cells K47:Q42. Make sure your histogram includes the full data range. Include: *A histogram graph with axis labels and chart title. NOTE: For the chart title and axis labels, do not use the defaults. Please describe what you are plotting- ensure they make sense for any observer. F G H Screenshot I J K Place your histogram here. 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 A 0 B 10 C E For the computed NPV values, please compute the following statistics (assume your simulated numbers are a sample of the Question 8: total population): D F NOTE: Best practices sometimes also include confidence intervals. For simplicity, we are not including those on this worksheet, but do be aware that confidence intervals give measurements of the bounds of errors in your simulation. G Maximum NPV. Minimum NPV- NPV Mean- NPV Median NPV Variance NPV Standard Deviation- NPV Range- H Screenshot J