P(E) Lo Bnd Uppr Bnd Exp. Loss
0.050 500,000 2,000,000 54,643
0.100 400,000 2,500,000 116,785
I'm looking at a spreadsheet that accompanies the book "How to Measure Anything in Cyber Security Risk"
The relevant piece is shown above. The formula for the Expected Loss column is as follows
=EXP(((LN(D8)+LN(C8))/2)+((LN(D8)-LN(C8))/3.28971)^2/2)*B8
In the sheet, the cells D8,C8 and B8 refer to the upper bound, the lower bound and the probability of the event respectively.
So, there is a 5% chance of some adverse event occurring that will, with a 90% confidence interval, produce damages between \$500K and \$2M . And in row 2, a 10% chance of an event producing a loss between \$400 K and \$2.5 M. I understand that the standard deviation associated with that confidence interval is 3.2897, and the author has explained that he's using the lognormal distribution. I also understand that the Excel formula EXP() is making this expression a power of e. But I still don't understand this. Why are we adding the mean of the upper and lower bound to the difference of the upper and lower bound divided by the stnd dev? ...and squaring it? This is clearly related to the concept of Expected Value, but I don't see it. Wouldn't it be easier to express the expected loss without taking the LN of the bounds and exponentiation? Hope this can be explained to a mere mortal.
Apparently the context is such that $$\text{loss } = \begin{cases} X, & \text{if event E occurs} \\ 0, & \text{otherwise } \end{cases}$$ where $X$ has a lognormal distribution; viz., $X= e^{\mu+\sigma Z}$, where $Z$ has a standard normal distribution.
Therefore we have the following expected value: $$\mathbb{E}[\text{ loss }]=P(E)\,\mathbb{E}[X] = P(E)\,\exp\left({\mu+\frac{1}{2}\sigma^2}\right). $$
The Excel code you quoted is just this expected loss expression with $$\begin{align}\mu &= \frac{\log U + \log L}{2}\\ \\ \sigma &=\frac{\log U - \log L}{2\,z_{0.95}} \end{align} $$ where $z_{0.95}\approx 1.644854$ is the $95$th percentile of the standard normal distribution (so $2\,z_{0.95}\approx 3.289707$ is the mysterious constant in the code).
This causes $L$ and $U$ to be the $5$th and $95$th percentiles, respectively, of $X$, and therefore $P(L<X<U)=0.90$.
NB: The book calls $(L,U)$ a "$90\%$ confidence interval", which is a misnomer because $L$ and $U$ are simply "modeling parameters" (percentiles) describing a distribution. A confidence interval, on the other hand, is a sample-based interval estimate of some population parameter.