PART B: You are expected to develop an Excel spreadsheet model for Question 4 and upload it together with your solutions for Part A on Blackboard following the Assignment 1 link.

4. The owner of a newsstand believes that the demand is normally distributed for the newspaper with a mean of 80 and standard deviation of 18. The paper costs 70 cents, which sells for $2.5. For each unsold paper, the owner receives 30 cents credit.

Create a simulation model using Excel and run it for 100 days.

a) What is the average profit of the newsstand if the owner decides to buy 80 papers every day? (10 points)

b) What is the average profit of the newsstand if the owner decides to buy 88 papers every day? (10 points)

c) Comparing your results in (a) and (b), what do you observe? Using the optimal fill rate formula used earlier in class, find the optimal number of papers the owner needs to buy. Is it consistent with the observation you made in the simulation outcome? (10 points)

[HINT: To create the above model, you should first generate samples for a normally distributed random variable with mean 80 and standard deviation 18, which will correspond to demand realizations for each day. The excel function "=NORMINV(RAND(),80, 18)" generates normally distributed random numbers with mean 80 and standard deviation 18. Then, you need to copy the formula for 100 rows to create 100 demand samples. Below is an example of how the first row of the spreadsheet model will look like. As discussed in Lecture 3 (slide 16), based on how many papers are purchased, the realized demand and cost/price values given above, you need to write the profit function in another cell and (copy this function as well for 100 rows). Finally take an average of profits (using "=AVERAGE()") which will represent the "expected value" in the original profit formula. Now you need to do the same profit calculation for a different amount for part b. You can do that by entering the same profit function in another column.

A	B (for part (a))	C (for part (b))-
= NORMINV(RAND(),80, 18). (Will generate normally distributed random number)	=Profit Function (as discussed in Lecture 3 slide 16).	=Profit Function.