You have written a stock trading book that guarantees vast riches for anyone who utilizes its strategies. To publish the book you can either use ABC publishing or you can self-publish.

If the book is published with ABC, there may be an opportunity to go on a book tour. If you go on the book tour and if the book is successful, it is estimated that it will sell 30,000 copies. If you go on the book tour and the book is not successful, sales are estimated at 2000 copies. If you do not go on a book tour (no matter if you self-publish or use ABC), then the estimated sales for a successful book are 20,000 copies and the estimated sales for an unsuccessful book are 1000 copies.

If you choose ABC as your publisher you will receive a $2000 signing bonus and $1 for each book sold. The first thing ABC will do is send the book out for review. Due to their clout in the industry, the book will receive generally positive reviews with a probability of 0.7. With a probability of 0.3, the book will receive generally negative reviews. Given that the book has received negative reviews, the probability that the book is a success is only 0.2 and the probability that the book is not successful is 0.8. If you receive positive reviews, then you may decide if you would like to go on a book tour. If you go on the book tour, the probability of a successful book is 0.85, while if you do not go on the book tour, the probability of a successful book is 0.75. Because you dislike travel, you assess a cost of $2000 to the book tour.

If you self-publish you will earn $2 for each book sold, however, there is very little impactful marketing that you can do. Based on the evidence at your disposal, you estimate a probability of the book being successful at 0.585 and the probability of the book not being successful at 0.415.

You are strongly considering using ABC to publish the book, but would like to investigate the situation more carefully before committing. In particular, you wish to simulate the process of book review, book success, and book sales. Note, you have decided that you will definitely go on the book tour if the review is positive, so there is no need to simulate that decision. Develop a simulation of 100 instances and determine the average number of books sold. In particular, your simulation should:

i. Determine if the book receives positive or negative reviews (use the probabilities above).

ii. Determine if the book will be successful or not, based on the book’s reviews (use the probabilities above).

iii. Determine the book sales, based on the books success (or lack thereof). Assume that book sales are normally distributed. In cases where the reviews were negative, assume that the mean sales are 20,000 with a standard deviation of 2000 for a successful book; for an unsuccessful book, assume that mean sales are 1000 with a standard deviation of 200. In cases where the reviews are positive, assume that the mean sales are 30,000 with a standard deviation of 2000 for a successful book; for an unsuccessful book, assume that the mean sales are 2000 with a standard deviation of 300.

Please provide a simulation in Excel and show all formulas used.

## Expert Answer

Formulae:

B20 =IF(RAND()<=$D$7,$C$7,$C$13)

C20 =IF(B20=$C$7,IF($C$17=$E$7,IF(RAND()<=$G$7,$F$7,$F$8),IF(RAND()<=$G$10,$F$10,$F$11)),IF(RAND()<=$G$13,$F$13,$F$14))

D20 =IF(B20=$C$7,IF($C$17=$E$7,IF(C20=$F$7,NORMINV(RAND(),$H$7,$I$7),NORMINV(RAND(),$H$8,$I$8)),IF(C20=$F$10,NORMINV(RAND(),$H$10,$I$10),NORMINV(RAND(),$H$11,$I$11))),IF(C20=$F$13,NORMINV(RAND(),$H$13,$I$13),NORMINV(RAND(),$H$14,$I$14)))

J19 =COUNTIF(B20:B119,C7)/COUNT(A20:A119)

J22 =COUNTIF(B20:B119,C13)/COUNT(A20:A119)

L19 =COUNTIF(E20:E119,I19&” and “&K19)/COUNT(A20:A119)/J19

L20 =COUNTIF(E20:E119,I19&” and “&K20)/COUNT(A20:A119)/J19

L22 =COUNTIF(E20:E119,I22&” and “&K22)/COUNT(A20:A119)/J22

L23 =COUNTIF(E20:E119,I22&” and “&K23)/COUNT(A20:A119)/J22