Admin
مدير المنتدى
عدد المساهمات : 18992
التقييم : 35482
تاريخ التسجيل : 01/07/2009
الدولة : مصر
العمل : مدير منتدى هندسة الإنتاج والتصميم الميكانيكى
 |  موضوع: كتاب 100 Excel Simulations - Using Excel to Model Risk الثلاثاء 20 أبريل 2021, 1:09 pm | |
| 
أخوانى فى الله
أحضرت لكم كتاب
100 Excel Simulations - Using Excel to Model Risk
Dr. Gerard M. Verschuuren
و المحتوى كما يلي :
Contents
Gambling . 2
1. The Die Is Cast 2
2. Casting Six Dice 4
3. Frequencies 6
4. Roulette Machine 8
5. Gambler’s Ruin . 10
6. Random Walk . 12
7. Gambling Strategy 14
8. Cracking a Password . 16
Statistics . 18
10. The Mean of Means . 20
11. Sampling 22
12. The Normal Distribution 24
13. Normalizing . 26
14. Repeats . 28
15. Confidence Margins . 30
16. Power Curves . 32
17. Hidden Peaks . 34
18. Sampling Sizes . 36
19. Quality Control 38
Genetics . 40
21. Sex Determination 42
22. Radiation and Mutation . 44
23. Mendel . 46
24. Hardy-Weinberg Law 48
25. Genetic Drift . 50
26. Lethal Homozygote . 52
27. Reduced Vitality . 54
28. Two Selective Forces . 56
29. Balanced Equilibrium 58
30. Molecular Clock 60
31. DNA Sequencing . 62
Financial 64
33. Risk Analysis . 66
34. Missing or Exceeding Targets . 68
35. Two-Dimensional Filters 70
36. Scenarios . 72
37. Moving Averages . 74
38. Return on Investment . 76
39. Employee Stock Options . 78
40. Value at Risk . 80
41. Asian Options . 82
42. Black-Scholes Model . 84vi
Expansion 86
44. Extrapolation 88
45. Predator-Prey Cycle 90
46. Homeostasis 92
47. Taking Medication 94
48. Population Pyramid . 96
49. Titration 98
50. EC50 Determination . 100
51. Converter 102
52. Conditional Training 104
53. Epidemics 106
Monte Carlo Simulations 108
55. Brownian Motion 110
56. A Traffic Situation . 112
57. Uncertainties in Sales . 114
58. Exchange Rate Fluctuations . 116
59. Cost Estimates 118
60. Market Growth . 120
61. Integration with Monte Carlo 122
Iterations 124
63. Win or Lose? . 126
64. Circular Gradients 128
65. Single-Cell Arrays . 130
66. Data Management . 132
67. Solving Equations . 134
68. Least Squares Method . 136
69. Combining Scenarios . 138
70. Logistics 140
Extras . 142
72. Graph Manipulation . 144
73. Detecting Outliers 146
74. False Positives . 148
75. Probability of Beliefs 150
76. Unbiased Sampling . 152
77. Numbering Records 154
78. Fiscal Year . 156
79. Stock Market . 158
80. Forecasting Temperatures 160
Miscellanea 162
82. Graph Manipulation 164
83. Simulation of a Slot Machine 166
84. Letter Game 168
85. A Hawk-Dove Simulation 170
86. Flock Behavior . 172
87. Simulation of Sick Cases 1741
88. Ehrenfest’s Urn Simulation 176
89. Two Monte Carlo Integrations 178
90. Randomness in Gene Pools . 180
91. Random Mating . 182
92. Differences in Fitness . 184
93. Loan Simulation 186
94. Stock Volatility . 188
95. S&P 500 Performance 190
96. Scenario Risks 192
97. Temperature Fluctuations . 194
98. Juror Selection . 196
99. Waiting Time Simulation 198
100. Project Delays . 200
Appendices . 202
1. Locking Cell References . 202
2. Nested functions . 204
3. What-If Tables 206
5. Simulation Controls . 208
Index
Index
Symbols
$ in cell references 202
:east squared residuals 135
#N/A
explained 50
generated by =NA() 50
hiding chart points 74
#VALUE! error 130
A
ABS
to highlight lowest value 59
to remove minus sign 33
Absolute cell references 202
Allele
dominant vs. recessive 46
Alpha error
explained 32
from CONFIDENCE 30
Amortization table 186
Ampersand operator (&) 16
Amplitude 92
Analysis Toolpak
for CONVERT function 102
for Moving average 75
for Normal distribution 24
for QUOTIENT function 154
for RANDBETWEEN 2
AND for multiple conditions 37
Annuity 79
APR 64
raise to (1+APR)^Years 65
Area under the curve 178
Array functions
as result of Data Table 206
can not delete part of array 31
compared to SUMPRODUCT 130
for Least squares 137
FREQUENCY 6
multi-cell 29, 44, 88
require Ctr+Shift+Enter 45
single-cell 130, 132, 137
TABLE 30
TREND for extrapolation 88
TREND for interpolation 44
Asian options 82
Auto numbering records 154
AVERAGE
for calculating mean 7
for moving averages 75
inside NORMDIST 25
with circular reference 128
B
Bayesian probability 151
Bayes Theorem 148
Beautiful Mind, A 170
Bell-shaped curve
defined 27
with BINOMDIST 19
Beta error 32
BINOMDIST
for bell-shaped curve 19
with BINOM.INV 40
with “successes” 41
BINOM.INV
for chromosomes 40
for quality control 38
Birds in flight 172
Black-Scholes model 84
Brownian motion 110
Brown, Robert 110
C
Calculation
for data tables 206
manual 9, 206
Cell references
Locking 202
CHAR
for lower case 16
for upper case 168
Chromosomes
for sex determination 42
from grandparent 40
recessive allele 46
Circular references
for iteration 124
for memory 162
Clinic
waiting time 198
CODE 16
Coin flip 19
Collective behavior 172
Combination 154
Compounding 65
CONCATENATE
formatting phone numbers 204
vs & operator 16
Conditional Formatting 7
Conditional probability 148
Confidence
defined 27
margin 30
with exchange rates 117
CONFIDENCE 30
Conflict and cooperation 170
Confounding factor 144
CONVERT 102
Converting metric 102
COUNT 25
versus COUNTA 158
COUNTA 158
COUNTIF
for data management 133
for uncertainty 114
with ROW 153
with SMALL 139
COUNTIFS 13
CRITBINOM 38
versus BINOMDIST 40, 174
Crowe, Russell 170
Ctr Sh Enter
braces in formula bar 7
for array formulas 6
versus SUMPRODUCT 130
with squared residuals 137
with TREND 44
Ctr ~ to show formulas 3, 141, 202
Cumulative summing 169
D
Data Table
as What-If Table 206
for chromosmes 41
for iterations of RAND 11
for random walk 13
genetic drift 50
how to use 30
no row, empty column 111
Overhead 188
reduced vitality 54
sampling sizes 37
to detect repeats 29
with DGET 142
with only row input 67
Data Validation
from formula 157
from list 33
Death penalty 196
Delays, project 200
Denaturation 62
Developer tab 208
DGET 142
Discounting cash flows 192
Discrete distribution 14
DNA
sequencing 62
Dogs with fleas 176
DSUM 70
E
EC50 100
Ehrenfest’s urn 176
Einstein, Albert 110
Employee stock 78
Epidemic 106
Equations 134211
ESS 170
Eulers method 106
Evolutionary strategy 170
EXP 82
for logistic equation 101, 137
for S-shaped curves 99
Expiration date 84
Exponential discounting 192
Exponential smoothing 75
Extrapolation 88
F
F4-key 202
F9
calculate current sheet 4
to calculate 2
Fair option value 79
False positives 148
Feedback in homeostasis 92
Filters, two-dimensional 70
FIND 102
Fiscal year 156
Fitness in gene selection 56
Fitness of genotypes 184
Flea infestations 176
Flock behavior 172
FREQUENCY
bins explained 6
with TRANSPOSE 164
Function arguments dialog 204
fx Button 204
G
Gantt chart 200
Gene pool randomness 180
Genetic drift 50
Genotype fitness 184
Gradient 128
Graph manipulation 164
Growth rate 69
H
Haplotype 61
Hardy-Weinberg law 48
Hawk-dove 170
Hayes theorem 148
Herd behavior 172
Heterozygote 46
in equilibrium 58
HLOOKUP 72
Homeostasis 92
Homozygote 52
I
IC50 100
IF 3
IFERROR 13
INDEX 158
INT 2
verus ROUND 30
Integration 122
alternatives 178
Interpolation 44
Intersection of 2 lines 170
ISBLANK 133
ISERROR 143
ISNUMBER 103
Iterations
circular references 124
in array formulas 130
in Solver 134
of Monte Carlo 112
using a Data Table 11
J
Juror selection 196
L
LARGE 132
Law of large numbers 20
LEFT 102
Letter game 168
Linear relationship 44
Loan simulation 186
Locking cell references 202
LOG 99
Logistic
half-way point 100
in least-squares 136
in titration 98
Los Alamos National Laboratory 207
Lotka-Volterra model 90
Lower and uppercase 182
M
Malaria 56
Manhattan Project 207
MATCH
explained 13
Mating random 182
MAX
with circular reference 125
MEDIAN
as array formula 147
Median absolute deviation 146
Memory 162
Mendel’s laws 182
Metropolis, Nicholas 207
MID
formatting phone numbers 204
with LEFT and RIGHT 102
MINVERSE 135
MMULT 135
MOD
explained 9
for remainder after division 154
Molecular clock 60
Monte Carlo Simulations
account for risks 114
and random numbers 108
for Stock Market 188
replacing integration 122
speed issues 207
tame the future 112
MONTH 156
Moving average 74
Mutations
as molecular clock 60
due to radiation 44
N
NA
for outliers 147
from NA() function 50
preventing chart point 74
Name Manager 132
Nash, John 170
Nested functions 204
Neumann, John von 207
Normal distribution
bell-shaped curve 19
for cost estimates 119
mean of means 20
versus sampling distribution 22
with Analysis ToolPak 24
without Analysis Toolpak 26
with RAND 207
Normalize 27
NORM.DIST or NORMDIST
binomial distribution probability
174
explained 24
NORM.INV or NORMINV
bell-shaped curve 27
explained 207
for Asian options 82
for exchange rates 116
for investments at risk 81
for random values 39
for survival rate 97
simulate APR fluctuations 65
versus CRITBINOM 174
with S&P data 190
with temperatures 194
NORMSDIST
for standard normal 32
NORMSINV
for standard normal 32
NOW
versus TODAY 126
NPV
with scenario risks 192212
O
OFFSET 74
Offspring simulation 182
Option pricing 78
OR 3
as validation formula 9
Outliers 112, 117, 144, 146, 207
P
Patient flow 198
Payment and interest 186
PERCENTILE
explained 65
versus QUARTILE 147
PERCENTILE.EXC 65
PERCENTILE.INC 65
Percentile on chart 194
PERMUT 154
Permutation 154
PMT for loans 186
Polymorphism 56
balanced 58
Population pyramid 96
Power curve 32
Probability, conditional 148
Probability distribution
for traffic 112
in Monte Carlo 207
Project delays 200
Pseudo-random 108
Put option 84
PV 79
Pyramid 96
Q
QUARTILE 147
QUOTIENT 154
R
RAND 2
for Binomial distribution 41
for coin flip 19
for DNA sequencing 63
for genetic drift 50
for letters 16
for Normal distribution 207
for sex determination 43
for up versus down 13
reliability 108
uniform distribution 24
verus Normal distribution 26
with Data table 11
with INT 21
RANDBETWEEN 16
availability 2
for selecting 153
returning decimals 97
uniform distribution 207
Randomized variation 207
Random letters 168
Random mating 182
Random number generator 25
discussion of seed 109
Random numbers
for unbiased sampling 152
from Analysis ToolPak 24
in Monte Carlo simulations 207
in Normal distribution 26, 116
recalculate with Sh+F9 20
with RAND 168
with RANDBETWEEN 16
Random walk
Brownian motion 110
genetic drift 51
simulating gambling 12
Range name 132
Relative cell references 202
Residuals
least squares method 136
versus squared residuals 100
Return on investment (ROI) 76
RIGHT
for leading zeroes 155
formatting phone numbers 204
with VALUE 102
Risk-free rate 78, 84
ROUND
advantages versus INT 30
for random integers 16
ROUNDUP 156
ROW
for descending months 158
for incrementing percentages 162
for moving averages 74
for numbering records 155
for sampling 153
for sequential numbers 28
instead of {1,2,3,4,5} 133
R-squared 144
S
Sample size 22, 36
effect on confidence interval 30
Sampling
distribution 22
techniques 152
Scenarios
with 3rd dimension 72
with data table 64
with iteration 138
Scoll bar for input 142
Sequencing, dideoxy 62
Sex-linked 46
Shift F9 2
Shoaling behavior 172
Sick cases 174
Sickle cell anemia 184
Simulation control 44
SIN 92
Sine wave 93
SIR model 106
SKEW 34
Slot machine 166
SMALL 139
Solver
for equations 134
for shipping 140
least squares method 136
Sorting 132
with a formula 155
S&P 500 190
SQRT 23
Squared residuals
minimizing 100
with Solver 136
Standard deviation
and NORMINV 116
estimating 22
normal distribution 24
of cost estimates 118
Standard error 22
z or t values 27
Statistics and sample sizes 22
STDEV
versus STDEVP 23
Stock market
S&P 500 190
Stock options 78
Stock-price tree 78
Stock volatility 188
Strike price 84
Students t-distribution 27
SUMPRODUCT 130
SUMXMY2 135
Swarming behavior 172
T
TABLE
can not be typed 30
explained 206
introduced 11
Table with memory 162
Temperature fluctuations 194
Test sensitivity 148
Test specificity 148
TODAY
in fiscal years 156
versus NOW 126
Training 105
TRANSPOSE 164
TREND
in extrapolation 88213
in interpolation 44
Type I vs Type II errors
explained 18
reducing 32
U
Ulam, Stanislaw 207
Upper and lower case 182
V
Validation formula 9
VALUE 102
Value at Risk (VaR) 80
VBA 209
cracking passwords 16
VLOOKUP
double VLOOKUP 153
explained 14
sort with a formula 155
versus HLOOKUP 72
verus INDEX 158
with scrollbar 142
W
Waiting time 198
Weighted characters 168
Weighting 130
What-if analysis 206
confidence margins 30
sampling sizes 37
X
X-chromosome 42
Y
Y-chromosome 42
YEAR 156
Z
Z-value 32
كلمة سر فك الضغط : books-world.net
The Unzip Password : books-world.net
أتمنى أن تستفيدوا من محتوى الموضوع وأن ينال إعجابكم
رابط من موقع عالم الكتب لتنزيل كتاب 100 Excel Simulations - Using Excel to Model Risk
رابط مباشر لتنزيل كتاب 100 Excel Simulations - Using Excel to Model Risk 
|
|
