Q$4.$solve using excel Young entrepreneur Susie Perez has launched a business

venture in which Susie uses stories submitted by university

students as the basis for comics in a monthly anime$-$style

magazine. Based on market research, Susie estimates that

average monthly demand will be $500$ copies. Susie has decided

to model monthly demand as normal random variable with a

mean of $500$ and a standard deviation of $300.$

Susie must pay a publishing company $$3\mathrm{.}75$ for each copy of the

comic printed. Susie then sells the magazine for $$5$ each. Rather

than having a store$-$front, Susie sells the magazines through a

group of student vendors who sell the comics out of their

backpacks while on campus. Susie pays a student vendor $$0\mathrm{.}35$

for each magazine sold. Susie only sells each issue for one

month, and then a new issue is sold. However, the publishing

company has agreed to buy back any unsold copies at the end of

each month for $$2\mathrm{.}25.$ Construct a simulation model that

computes Susie's profit.

a$.$ As Susie validates the simulation model you have

constructed, Susie observes that when using a normal

distribution with a mean of $500$ and a standard deviation of

$300$ to model demand, negative values of demand are often

generated. How can you modify the simulation model to

address this issue?

b$.$ Based on the simulation model that incorporates a

remedy for the validation issue observed in part $($a$),$ what is

the estimate of the average profit if Susie sets the order

quantity to $1,200?$ What is the $95\%$ confidence interval on

this estimate of average profit?

c$.$ For an order quantity of $1,200$ copies, what is the profit

value such that $2\mathrm{.}5\%$ of the profit outcomes are smaller than

this value? What is the profit value such that $2\mathrm{.}5\%$ of the

profit outcomes are larger than this value? Why doesn't this

range correspond to the $95\%$ confidence interval in part $($b$)?$