# 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.

Skills: Excel, Excel VBA

( 0 reviews ) Navi Mumbai, India

Project ID: #15679963

## 9 freelancers are bidding on average ₹3448 for this job

abhinovpankaj

I have been working as freelancer since last ~ 5 years,prior to this was working with a MNC as an Sr. Software Professional. Relevant Skills and Experience -- VBA and Excel,Word,Office Applications -- Expert on C# More

₹2777 INR in 3 days
(31 Reviews)
5.1
AhmedHassan1122

Excel VBA Expert Relevant Skills and Experience Excel VBA Expert Proposed Milestones ₹1300 INR - Full Price

₹3333 INR in 1 day
(12 Reviews)
4.0
wsnel

I am an actuarial analyst, trained in the art of simulation! I have done/used simulation in Excel many times; I consider it an amazing tool to solve complex problems! I am also a skilled VBA coder and specialise in del More

₹4444 INR in 5 days
(3 Reviews)
3.0
mpunn2007

. Relevant Skills and Experience . Proposed Milestones ₹2777 INR - ms

₹2777 INR in 3 days
(9 Reviews)
3.2
₹5555 INR in 7 days
(5 Reviews)
2.4
jayu2580

A proposal has not yet been provided

₹2150 INR in 2 days
(0 Reviews)
0.0
johnsarmiento

A proposal has not yet been provided

₹3888 INR in 3 days
(0 Reviews)
0.0
AhmedKDawood

i can deliver a model in 24 hour and i assure a user-friendly interface , and available for any modifications you require after 10 from delivery

₹3333 INR in 2 days
(0 Reviews)
0.0
₹2777 INR in 3 days
(0 Reviews)
0.0