GNOME Bugzilla – Bug 144710
Simulation features/bugs
Last modified: 2018-05-22 13:07:19 UTC
This is a little uber bug/feature report for the simulation tool... Here's a simple model to illustrate the kind of simulation I'm looking for: A B C 1 Mean Stdev 2 Rev Growth 20% 10% 3 COGS/Sales 50% 5% 4 5 Rev FY04 FY05E 6 Rev 10 =B6*(1+randnorm(B2,C2)) 7 COGS 5 =C6*randnorm(B3,C3) 8 Gross profit =b6-b7 =c6-C7 Here are the issues I see: 1. Inputs a) Must the simulation inputs be set to all the random variables? Or simply intermediate variables in the model that I want simulation visibility of? In the former case must all inputs be specified? Based on the results, I expect it's the latter, but then why must I specify any at all? If I enter nothing the dialog box says the input is invalid. I just want to see how the output varies with the randomness I've created. I don't necessarily need to see anything about the input variance - at least for now. b) I notice that I can't set multiple ranges in the simulation inputs or I crash gnumeric... e.g. "b6,b7" as opposed to "b6:b7". I would clearly want this capability as the model scales and the inputs to track are not contiguous. I suppose I could replicate the cells elsewhere so they are contiguous but this seems a bit much. Definitely the crash is a bug. c) Minor item... How are the names generated in the report. If I select c6 as the input cell the output report names it Rev FY05E... that's what I want but how would I actually specify this myself in the case where the tool isn't so smart? 2. Saving settings - If I close the simulation tool and re-invoke it the settings are reset. It would be really nice if the last simulation's settings were retained. I'd say this is a bug. 3. Results detail - It would be really useful to be able to see the full results traces. E.g. for whatever inputs and outputs requested I'd like to be able to see the actual values for each iteration. Actually I don't need the full detail but a cumulative XY plot to get a better view of the distribution would be very powerful. I'd call this an important feature but I imagine it's not trivial. This is available in Crystal Ball. 4. Results location - Minor detail but it would be nice if I could get away with just specifying the top left cell for the output range if I elect not to use a new sheet. However if I do that all I see is the 3 lines of header info and the word SUMMARY - that's it.
1 a) I don't understand what you are saying...sorry. b) you should be able to specify multiple ranges without a crash. That is definitely a bug. 1 c) I'll need to check the code on that. 2 That would be nice. Unfortunately currently we aren't saving any settings. 3 This sounds like a nice but complicated enhancement request 4 You should be able to just select the top left cell. THis is clearly a bug.
Ok - sounds like we're all good except for the first thing 1a. Probably best to start with a basic question. If I just want to monitor an output why do I need to specify an input for a simulation?
I have never used the simulation nor done any coding with respect to it, but looking at the code I find the comment: /* * Monte Carlo Simulation tool. Helps decision making by generating * random numbers for given input variables. */ Looking at the code further tells me: The input fields should contain functions involving a random number generation. In a simulation round, the input fields are recalculated and then the dependent cells are recalculated. Statistics for the input and output fields are then output. So if you do not specify an input field there is no reason for any of the values in any cells to change.
Ok - I confirmed this... if I specify a cell formula with a probability function like randnorm but do not specify it as an input in the simulator it will be treated as a constant input for the simulation rather than a distribution. Tested this with two parallel inputs and outputs that are simply equated to the inputs. Out1=In1 Out2=In2 In1=randnorm(10,1) In2=randnorm(10,2) If Out1 and Out2 are both specified as outputs but only In1 is specified as an input, Out1 will exhibit expected variance but Out2 will remain constant. If Out1 and Out2 are both specified as outputs but only In2 is specified as an input, Out2 will exhibit expected variance but Out1 will remain constant. If Out1 and Out2 are both specified as outputs and both In1 and In2 are specified as inputs, both Out1 and Out2 will exhibit the variance expected. thanks Note that the problem 1b I identified is all the more critical b/c every input must be specified to get the results I need. I had previously expected that specifying inputs was simply a way to monitor them in the final report. I hadn't thought that this was necessary to actually have them behave as distributions - I had thought that was implicit.
In gnumeric cvs head (1.3.x branch) 1b does not yield a crash. Of course, this simply because the okay button is disabled with that input. This does not really solve the issue at hand.
Just to collect the issues as I see them with the simulation tool: 1) inputs and outputs should not need to be contiguous 2) the names for the inputs and outputs should be specifiable 3) the settings of the simulation dialog should be retained within a session 4) the location of the result should be specifiable by just giving the top left corner as for the other statistics tools 5) the tool should be undoable as all other statistics tools are. and, of considerably lower priority: 6) the complete (raw) data created should be available, possibly with the option of having graphs created.
*** Bug 632107 has been marked as a duplicate of this bug. ***
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/27.