Why is it desirable to conduct Monte Carlo simulations using as many replications of the experiment as possible? [5 marks] b. Explain in detail how pseudo-random numbers are generated in Excel. [5 marks] c. In which range of numbers do we expect the random numbers drawn from a standard normal distribution to fall? Explain why. [5 marks] d. Marcus is a 45-year-old. He has a new job and intends to save £10,000 today and in each of the next 14 years (15 deposits altogether). He is considering investing in an investment policy in which he would invest 30% of his assets in a risk-free bond with 3% continuously compounded annual interest and the remaining 70% in a risky asset that has lognormally distributed returns with a mean μ = 12% and standard deviation σ = 35%. Marcus applied Monte Carlo simulation to decide whether he should invest his money in this investment strategy. The Excel spreadsheet below reports the end-of-year wealth based on one simulation that he conducted. Write down and explain the Excel formula used to calculate the yellowed values in cells E11 and F11, in the Excel spreadsheet below. A B C D E F MARCUS'S INVESTMENT/SAVINGS DECISION 1 2 Annual deposit 3 Risk-free rate 4 Parameters of risky investment 5 Expected annual return Standard deviation of return Proportion invested in risky 8 Accumulation at age 60 9 10000 0.03 0.12 0.35 0.7 462700.73 Total investment at Investment at Random Number beginning of period beginning of New investment normally (investment BOY + new period (BOY) distributed investment) o 10000 10000 -0.812923943 9029.45 10000 19029.44907 0.000354184 20903.51 10000 30903.50729 -0.157521283 32635.61 10000 42635.61002 -0.080117421 45899.80 10000 55899.80068 1.6561585 96051.38 10000 106051.3802 -0.901927446 93827.07 10000 103827.0698 0.234302332 121045.23 10000 131045.2266 -0.507783094 127097.31 10000 137097.3066 -0.732013116 126129.69 10000 136129.6883 0.64071202 127939.70 10000 137939.7007 1.968071518 259440.32 10000 269440.3247 -0.06797099 290949.65 10000 300949.652 1.369325587 476608.26 10000 486608.2619 -1.080348058 413562.11 10000 423562.1061 0.021732861 462700.73 Total investment at end of period Age 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 9029.449067 20903.50729 32635.61002 45899.80068 96051.38024 93827.06981 121045.2266 127097.3066 126129.6883 127939.7007 259440.3247 290949.652 476608.2619 413562.1061 462700.7342
Added by Carolina S.
Close
Step 1
Pseudo-random numbers are generated in Excel using the RAND() function. This function generates a random number between 0 and 1. The RAND() function uses a seed value, which is a starting point for the random number generation algorithm. By default, Excel uses the Show more…
Show all steps
Your feedback will help us improve your experience
Akash M and 93 other Principles of Accounting educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Recommended Videos
Learning Exercises for Section 27.3 1. Set up and complete a simulation of tossing a die 100 times. How do the probabilities of each outcome compare with the theoretical probabilities? Do the same simulation for 500 repetitions. What pattern do you notice as the number of repetitions increases? 2. a. Set up and complete a simulation to find the probability of getting green twice in a row if a spinner is on a circular region that is 1/3 green, 1/6 blue, 1/3 red, and the rest yellow. The spinner is spun twice. Carry out the simulation 30 times and record the outcomes. (Your record should include colors.) Answer parts (b) through (e) based on the outcomes of your experiment in part (a), and tell whether your experimental values are close to the theoretical values. b. Which outcome is most likely? Why? c. What is the probability of getting a green on the first spin and a blue on the second? d. What is the probability of getting a green on one of the spins and a blue on the other? Why is this question different from the question in part (c)? e. What is the probability of not getting green twice in a row? (Hint: There is an easy way to compute the probability.) 3. On one run of the free-throw simulation in Activity 10, the event YYYYN had a proportion 0.319. What does that mean? 4. Does a simulation using randomly generated numbers give theoretical or experimental probabilities? Explain. 5. a. Go to http://illuminations.nctm.org/ and click on "Interactives." In the search bar, type in "Adjustable Spinner." Scroll down to the "Adjustable Spinner" option. Set the sectors and the number of probabilities for the sectors by moving the dots on the circle or by moving the buttons for each color. Set the number of spins to 1000, and click on "Spin." You will see how the spinner works. You can click "Skip to end" to see the end results of all the spins. Write down the numbers in the results frame. These are the relative sizes of the regions of the circles and show theoretical probabilities (but in percents). b. Use this spinner activity to simulate a five-outcome experiment with unequally likely outcomes, and run the simulation 100 times. How do the experimental results compare with the theoretical ones in the table at the bottom of the screen? c. Repeat with a run of 1000 simulations. How do the experimental results compare with the theoretical ones? d. Repeat with a run of 10,000 simulations. How do the experimental results compare with the theoretical ones? Supplementary Learning Exercises for Section 27.3
Kari H.
For the following problem, you are to create a Macro using the Record Macro functionality. Assuming that your computer allows Macros, you may have to turn this feature on by using "Customize Ribbon" to add the "Developer" tab. Customize Ribbon might be found under "Options" from a File or Home screen in Excel. NOTE FOR MAC USERS Create an Excel sheet with the following headings: You are to start with an initial deposit of $13,000. Your Total will earn interest each quarter at a rate that will appear in cell C3. At the end of each year, you will put in an additional deposit of: - $1000 at the end of the first year - $2000 at the end of the second ... and so on until you deposit - $20,000 at the end of year 20 The "Total after 20 years" in cell G3 will be set equal to the cell at the bottom of the table that contains the Total immediately after you deposited that last $20,000. The quarterly compounding interest rate that you will earn will be a random number chosen by Excel in cell C3. In C3 you are to enter: =3.42% + 3%*rand() The function "rand()" selects a random number from a uniform distribution between 0 and 1. If you press ‘‘recalculate’’ a few times (F9 on PCs), you should see different possible outcomes appear in cell G3 depending on the quarterly interest rate that the investment realized over those 20 years. You are to create a macro to record 500 random outcomes from cell G3, and save them in a column of cells beginning at G8 and going down. Instructions for creating the Macro are here. When you have the 500 outcomes in column G, have excel compute the maximum of those 500 numbers, the minimum, and the median. (a) What is the maximum value that you achieved? (b) What is the minimum value that you achieved? (c) What is the median value that you achieved?
Dominador T.
1. Why is the probability that a continuous random variable is equal to a single number zero? (i.e. Why is P(X=a)=0 for any number a) [1 sentence] 2. In what ways can a quick drawing of the normal curve (not a detailed empirical rule drawing but a simple one like that shown in the instructor's video) be used to estimate or verify your answer to a problem like practice exercises 2-4? [2 sentences] 3. The empirical rule says that 95% of the population is within 2 standard deviations of the mean, but when I find the z-scores that mark off the middle 95% of the standard normal distribution I calculate -1.96 and 1.96. Is this a contradiction? Why or why not? In other words why are the normal distribution calculators not agreeing with the empirical rule? [2 sentences] 4. What does a z-score tell you about a number in a data set? [1 sentence] 5. What two quantities do we need to fully describe a normal distribution? [1 sentence] 6. How is probability determined from a continuous distribution? Why is this easy for the uniform distribution and not so easy for the normal distribution? [2 sentences] 7. What does the symmetric bell shape of the normal curve imply about the distribution of individuals in a normal population? [2 sentences] 8. How can the empirical rule be restated in terms of z-scores and percentiles? Restate it for four of the seven z-scores. Hint: Use the definitions of z-score and percentile and avoid use of the phrase "standard deviation" or the numbers 68, 95, and 99.7. [4 statements]
Madhur L.
Recommended Textbooks
Horngren’s Cost Accounting
Cost Accounting A Managerial Emphasis
Principles of Accounting Volume 1: Financial Accounting
Watch the video solution with this free unlock.
EMAIL
PASSWORD