Excel Simulation Problem VBA Userform GUI

Problem 5 (40): A retail manager is interested in a simulation on how to set prices. Assume the following simplistic model of her situation. The manager decides on a price for a product. There exists an integer number of periods before sales end. There also exists an integer number of inventory in stock. In each period, a customer arrives, sees the price, and has a certain probability of buying the product depending on the price. A description of the probability function is given below. If the customer ends up buying the product, inventory is decreased by one, revenue is made and the next period occurs. If the customer doesn’t buy, the next period occurs with no change in inventory and no revenue generated. When no periods exist, if any inventory remains it is sold at a given salvage value. The manager requests your help. Construct a simulation to run the above model 1000 times with inputs decided by the user. The manager would like to see a histogram of total revenue across the 1000 simulations. The manager would also like to see descriptive statistics from the simulation. Specifically, she would like the min/max/average/standard deviation. Also, she would like the probability of selling out. As a final thought, the manager asks if a graph could be given of average profits given different prices, so that she doesn’t have to try out multiple prices. Probability function: The probability of purchase follows the following functional form. p(purchase|price)=max⁡(100-price*b,0) Where b is a parameter given by the manager. For example, given an initial parameter of b=5, a price of 20 or higher would result in no chance of a customer buying a product. Initial values for each parameter are as follows: Parameter Initial Value Inventory 50 Periods 100 b 5 price 10 Salvage Value $1 Create a nicely designed GUI (userform) where the manager can easily change the above inputs. The GUI should start with the above inputs automatically set, but allow the users to change them. Graphs, figures, tables and numbers can be shown on the actual worksheet. Hint: Use a simulation, no need to try to find these things mathematically.