Description
It should be unique, and utilize various shapes of distributions. In your write-up, please describe your problem, provide outputs of your simulation results, and interesting calculations and probabilities.
Problem Explanation
Each year the University of Denver selects three students to conduct an institutional research on a current life dilemma. This year William, Zack, and Ashley were selected to conduct this research. As each student is selected, they are all given a certain task: William is responsible for running the experiments, Zack is responsible for collecting the DATA and Ashley is responsible for the Analysis. Each student varies on how long the task will take: For William, each experiment will take 15 minutes to complete. It will take Zack 30 minutes to collect each DATA set and lastly, it will take Ashley 60 minutes to conduct the summary analysis. Each research trail changes based on the findings of the pervious experiment. William can’t start the next trial until Ashley finishes the analysis for each trial. And Zack is not able to obtain the data collection until William conducts the experiment. They have 6 hours to conduct this research before the final presentation with 3 experiments. This simulation is conducted to see on estimate how long it takes to complete each experiment If each person has a different task.
Probabilities
What is the probability that they will finish all three experiments in less than 6 hours (360 minutes)?Simulation
Simulation is a way to model possible outcomes of a situation. Exploring those possible
outcomes will help reduce uncertainty, and mitigate its risks.
In Excel, we can use the RAND() function to generate random numbers for a simulation model.
These random values represent possible outcomes of a situation. Then we can run several
iterations of the model (with different random numbers) to look at all the possible ending results.
We can then record those results to analyze how likely are they to happen. By understating the
likelihood of a future event, a decision can be made now to either encourage it happen or prevent
it.
Questions that can be answered using simulations:
How likely will the project finish by deadline?
The tasks within the project may take longer or shorter than the times they were assigned.
Randomly generated numbers are assigned to these tasks to show these variations. Then
the project completion time is calculated based on these new task durations. This process
is repeated many times to cover many possible project completion times. By exploring
these iterations, management can estimate the probability of the project finishing in time.
How likely will an investment exceed 10% ROI?
How likely will customers wait in line as they get ready to check out?
Problem
Solve this problem before class using the instructions below. You can find the solution posted on
canvas in an excel file labeled: Simulation – Investment Portfolio
Develop a worksheet simulation for the following investment portfolio:
Initial investment of $250,000
Annual investment of $20,000
Expected average rate of return is 10%
Standard deviation of 15%
25 years until retirement
What is the average of 500 possible ending values of this investment? How volatile can those
ending values be?
2 | P a g e
Solution
Step 1. Arrange information into a table
In a new Excel spreadsheet, enter the information presented in the problem into the
range A3:B7. (Current investment, average rate of return, standard deviation, years to
retirement and annual investment).
Step 2. Generate a random sample
We will generate a random sample of rate of returns for each year in the 25-yearperiod.
1. In column A, list the years 1 to 25 in the
range A11:A35
2. Column B will hold the random rates of
returns corresponding to each year. In cell
B11, enter the formula:
“=NORM.INV(RAND(),$B$4,$B$5)”
3. Drag to apply the formula to cells B12:B35.
This formula will generate a random sample
of rates that follow a normal distribution with
mean 10% and standard deviation 15%.
4. Column C will hold the ending value of the
investment at the end of each year given the
rate calculated in column B.
In cell C11 enter the formula:
“=B3* (1+B11) + $B$7”
3 | P a g e
The ending value is a function of the beginning investment, rate of return and
annual investment.
5. In cell C12, enter the formula “=C11*(1+B12)+$B$7”
This formula takes into account that the beginning investment of the second year is
the ending value of the first year. Whereas, in the first year, the beginning
investment was the current investment.
6. Drag to apply the formula to cells C13:C35.
Note: Numbers in column B & C may vary from screenshots, because Excel is
generating random numbers in the RAND() function.
Step 3. Explore many iterations of random samples
In the previous step, we calculated the ending value of
one random sample of rates. In this step we will look at
500 iterations of ending values based on 500 random
samples using a Data Table.
1. In column A, list the iteration numbers 1 to 500 in
the range A38:A537
2. In cell B37, reference the ending value calculated in
the previous step “=C35”
3. Select the table just created (Range A37:B537)
4. Go to Data What-If Analysis Data Table
5. Leave Row input cell empty
6. In Column input cell, enter any empty cell “$E$9”
(First 24 iterations out of 500)
4 | P a g e
Step 4. Calculate summary statistics
In order to better understand the results we got in the previous step, we will calculate
summary statistics of our 500 iterations. This will give us a better understanding of
how the investment is going to perform.
1. In column E, enter labels for the summary statistics (Mean, Median, Standard
Deviation, Percentiles: 5% and 25%)
2. In cell F2, enter the formula: “=AVERAGE(B38:B537)”
The range B38:B537 holds the 500 iterations of ending values.
3. In cell F3, enter the formula: “=MEDIAN(B38:B537)”
4. In cell F4, enter the formula: “=STDEV.S(B38:B537)”
5. In cell F6, enter the formula: “=PERCENTILE.INC(B38:B537,E6)”
6. In cell F7, enter the formula: “=PERCENTILE.INC(B38:B537,E7)”
The mean and median are the averages of the 500 ending values calculated in the
previous step. They provide us with some idea on how we can expect this investment
to perform in the future. However, the standard deviation shows the ending values to
be widely dispersed from the mean. Therefore, in this case, the mean may not be the
best predictor of future performance of this investment.
On the other hand, our percentile calculations are showing that we have 95% chance
to have ending value larger than $1,488,228 and a 75% chance to have an ending
value larger than $2,546,963.
5 | P a g e
Beta Distribution
In the previous example, you may have noticed that we did not just use the RAND() function to
generate the 500 iteration. Instead we used the function NORM.INV. In that step, we identified
that we need the random numbers to follow a normal distribution. In other words, we want most
of the generated numbers of rate of return to be close to the mean. In the histogram below you
can see how most rates of returns generated were 10% or close to it, which is the mean we were
given in the problem. The histogram also shows how it is less likely to have a rate of return that
is much higher or much lower than the mean.
This assumption is valid in this case. Rates of returns are not likely to differ greatly from the
average rate of return. However, this assumption does apply to all simulation problems.
Therefore, a more flexible type of distribution is used for such simulations, the beta distribution.
A beta distribution allows us to choose the shape of the distribution we think is appropriate to the
situation in hand. It also allows us to choose minimum and maximum possible values (as oppose
to the normal distribution that theoretically can go to infinity).
The following are commonly used beta distributions. The values on the top of each distribution
are called Alpha (α) and (β). They are used to change the shape of the distribution as needed:
6 | P a g e
7 | P a g e
Situations where different beta distributions are appropriate:
α = 2, β = 0.5 distribution can represent students’ assignment submissions in relation to
the due date. The x axis shows the days between when the project was assigned until the
due date, the y axis shows how many students submitted their project that day. We can
assume that almost no one submits the project the day it was assigned but most students
will submit it on the due date. The minimum and maximum values in this case are the
project assigned date and project due date.
α = 1, β = 1 distribution can represent the output of a machine producing a component.
We can assume that the machine will have the same level of productivity throughout the
day. So, it will produce the same number of components throughout the day. The
minimum and maximum values in this case are the first and last hour of the day.
α = 2, β = 5 distribution can represent the productivity of a worker during the day.
Unlike the machine in the previous example, a worker’s productivity may vary by the
time of day. A worker may start finishing only a small number of tasks, but then quickly
starts getting more productive and finishes many tasks in a few hours. After this peak
productivity in the beginning of the day, energy can go down, as well as productivity
levels as the day comes to an end. Similar to the previous example, the minimum and
maximum values in this case are the first and last hour of the day.
Simulation Example with Beta Distribution
Read before class
At an assembly line, two workers assemble components. Worker B needs Worker A to finish
assembling his parts so he can add his. In other words, they work sequentially.
Worker A takes 3-5 minutes to finish, but he is usually slow. Beta (5,2)
Worker B takes 2-4 minutes to finish, but he is usually fast. Beta (2,5)
How long does it take workers A and B to assemble the finished product, on average?
How many times does Worker B stays idle?
Model the production of 1,000 components.
Get professional assignment help cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why choose our academic writing service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why Choose Our Academic Writing Service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
How It Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
PLACE THIS ORDER OR A SIMILAR ORDER WITH Essay fount TODAY AND GET AN AMAZING DISCOUNT
The post Excel or Mini tab or JMP Stimulation project appeared first on Essay fount.
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
