We will create a spreadsheet-based tool to assess stocking decisions in the newsvendor problem. In this problem a newsvendor has to decide how many newspapers to stock in order to satisfy demand during a day.

First let’s use a deterministic approach to the problem, assuming a known demand. In a worksheet, enter values for the following parameters: Price, Cost, Expected Demand, Stock.

1. Write code to read these parameters in, calculate the total costs and return the result in a different cell.

2. Do the same for total sales and profits.

3. Return the total profits in a message box.

Now let’s assume an uncertain demand and see the expected outcomes. You can continue working in the same spreadsheet. Keep the same price, cost, and stock as before, and now the value for expected demand represents the expectation value of a random variable, rather than a fixed number.

4. In column 1, put the numbers from 1 to 10 using a FOR.. NEXT structure.

5. In column 2, put random demand values between 0 and twice the expected demand using the RND VBA function. (Insert only the values into the cells, not formulas.) Hint: if r is a random variable uniformly distributed between 0 and 1, then r*N will have random values between 0 and N. Round the demand to an integer.

6. Calculate the profits for each demand value and insert the results in column 3.

7. Write the code to insert the formula which calculates the average profits into a cell. Do the same for the standard deviation.

8.* Include a button that assesses the stock level selected based on the following rules:

• If the average profit is negative, print “Bad!”
• If the average profit is positive, print “Good!”
• If the average profit is zero, print “You can do better!”

We now want to provide a little more flexibility to the above procedure, by allowing the number of replications to be chosen by the user. To obtain this number we need to provide a way for the user to tell the program what value it should be.

9. Assign a new cell for the number of replications. Insert a button that re-runs steps 1-2 and 4-6 with the number of replications specified.

10.* Insert a button that clears the table, deleting the replication number and the current values of demand and profit. (This should work for any number of replications.) [update oct 3: your code must not make reference to a number of replications, and you must clear the table rather than assigning a new value to it. https://msdn.microsoft.com/en-us/library/office/ff820947.aspx ]

11.* Insert a button control that asks for the number of replications using an INPUTBOX and clears the current replications (if any). Then, based on the specified number of replications, the procedure calculates the average profits and standard deviation, and returns a message based on the rules in 8.