In this session we will continue working on the newsvendor simulation spreadsheet. You can use the Newsvendor template to write your procedures to complete the following steps. First name ranges corresponding to the Inputs, Replications, and Outputs tables.
1. Use the OFFSET method in combination with a FOR loop to populate the Replications table. Insert the replication number up to the number specified.
2. Using the OFFSET method populate the demand (assume a uniform distribution between 0 and the expected demand) and profit values for each replication.
3. Create a variable named ProfitsRange that references the range with the profit values. Hint: first define auxiliary range variables for the first and last cell of the profits column using the END property.
4. Use ProfitsRange to calculate the average and standard deviation of the profits. Insert the values rounded to 1 decimal place in the Outputs table.
5. Create a sub that clears the Replications and Outputs values. Use the END or CURRENTREGION method so it works for any table size.
6. Add a button that clears and populates the replications and outputs tables.
Now we are going to define alternative demand distributions using custom VBA functions. Create functions that perform the following operations:
7. Return a random whole number drawn from a uniform distribution, with the lower and upper bounds passed as arguments.
8.* Return a random whole number drawn from a triangular distribution, with the parameters passed as arguments.
9.* Return a random whole number drawn from a Poisson distribution, with the mean passed as an argument. Here are a couple of algorithms you can use.
10. Adjust the Inputs table and VBA procedures to use one of the previous functions in the simulation.
Finally, we will use forms to set the newsvendor parameters and write them into the spreadsheet.
11. Insert a ‘Set parameters’ button that loads a VBA form.
12. Add a text box to the form so the user can input the Stock.
13. Add a ‘Run Simulation’ button to the form. Modify its code so that it reads the value inserted in the textbox, writes it in the corresponding cell of the spreadsheet, and then runs the simulation.
14.* Add text boxes to set the cost, price, demand (based on your choice in step 10) and replication parameters. Update the Run button to write these values in the spreadsheet too.
15.* Add a combo box for the number of replications with the values 10, 50, 100 and 1000. Update the Run button. [Clarification Oct 24: Replace the text box from step 12 with this combo box. Submit the version with the combo box…I trust that you were able to follow the stuff with the text box in class.]
16.* Set the form so that it opens with predefined values for each parameter. (Use the Initialize procedure.)