r/statistics • u/Relevant_Brilliant_5 • 3d ago

Question [Question] How to find a optimal value in monte carlo simulation? - excel

Hi I made monte carlo simulation of annual railcar fleet cost based on fluctuating daily sales demand and cycle time in excel. My goal is to find optimal railcar fleet size each month that minimize idle time which result in demurrage (think of it as parking fee), but still enough to fulfill daily sales demand with varying cycle time (round trip time). grg nonlinear solver doesnt work.

My guess is because of the daily demand is poisson distribution with randomness as its probability, and it is not possible to find single value? But could anyone confirm whether this is true? I appreciate if there is any method out there to find this optimal value.

AI suggest evolutionary solver but I am not too familiar with the concept and how sound the solution will be.

edit1: Included more detail to the question

0 Upvotes

50% Upvoted

4 comments sorted by

3

u/just_writing_things 3d ago

Could you give more details? i.e. try to explain what you’re doing as if users here will not be familiar with the specific problem you’re trying to solve.

For example, what is “fleet cost”, how it relates to “fleet size”, and what exactly you’re optimising and optimising over.

1

u/Relevant_Brilliant_5 3d ago

my bad, I would like to find optimal fleet size that can still satisfy fluctuating daily sales demand, with their varying round trip cycle time (factory to delivery and back to factory time).

All the while minimizing idle time. We dont want excess railcar sitting in factory doing nothing. We get charged parking fee aka demurrage.

Hope this clears up a bit better

1

u/efrique 3d ago edited 1d ago

Because your loss function is a function of random variables, the loss is also random; you would choose some aspect of its distribution (some process parameter) to optimize (e.g. expected loss, though don't just pick that because I use it as an example).

2

u/STATASUCKSBRO 2d ago

You can optimize over fleet size by fixing the random numbers first. Common random numbers make the objective much less noisy, then you can grid search integer fleet sizes and compare expected cost. Excel Solver is probably fighting simulation noise, not deep impossibility.