Scenario table like in Excel
I want to build a table like the “data table” in Excel. To calculate different scenarios.
i dont like the scenario options of adding a category with different values to calculate the scenario.
Example:
rows: EBITDA ( lets say 10; 12; 14)
columns: multiple (6; 7; 8)
is there any function to do that same sensitivity analysis like in Excel? In Excel is very easy. You set the values for roes and columns and the output.
many thanks, Quanters
Hi Juan,
This is my version of the QM model for calculating the VE sensitivity to changes in EBITDA and Multiple. In addition to the EBITDA and Multiple inputs, you can also change the values for 1) EBITDA change step (ECS) and 2) Multiple change step (MCS). You can also change the number of items in the categories EBITDALst (Ei) and MultipleLst (Mj) – the model automatically recalculates all totals and item descriptors. The model contains two perspectives and some items notes.
I hope this is what you need.
Good luck.
what are the modeler permissions? My permission?
Hi Juan,
send your model with Modeler permissions and make basic explanations for it. I’ll see what you mean.
Thats perfect SAU. still have a problem 🙂 My model doesnt allow me to perform the calculation directly for the table. There many matrixes ad the ouput comes from many calculations in those matrixes. So I cant directly type the formula for the table. I have multiples, ebitdas and IRR. The irr comes from calculation s in those matrixes so I dont really know how to get the desired output. May I send my model and take a look?
Sorry for the work 🙂
Hi SAU!!!!
I attached an excel file to provide you with the output I’m looking for. In the example the calculation is very easy, but in my QM file there many matrixes.
My feeling is i) If I define an scenario, it gives me in QM a row (see highlighted row in the excel and ii) my model gets a little messy in terms of adding many rows for the scenarios.
What I want QM to do is take two inputs, change them within a range and combine each of the values of both. Lets say I have A; B; C; D for Input1 and 1,2,3,4 for Input2. I want A1; B1; C1; D1; A2; B2 and so on.
Take a look at th excel, please. I would love to be able to construct my tables in QM being able to change those 2 variables and make the model run under each assumption.
Thanks!!!!!
Hi Juan,
Can you explain to me the difference between the “data table” function in Excel and the QM’s “scenario matrix” in terms of structure and logic? Where do you think, if not in the matrix, QM should store the “data table” result?
To make the conversation substantive, attach a specific example of an Excel model (or, may be, QM-model) of what you need.
Good luck.
Well, Its as easy or difficult as the “data table” function in Excel.
I want to run sensitivities, but the “scenario” systema of adding as many rows as scenarios is not an elegant option. I would like something in line with the excel data table.
I dont really get your idea. My fault. The problem is my model is already a cumbersome combination of matrixes and difficult to sort out as you mentiones.
Anyway, I appreciate your time. 🙂
Thanks!!!!!
I think your question about doing Excel-like sensitivity analysis may be addressed by the new Solver feature… I wouldn’t know as I haven’t used it yet.
However, if you set up your scenarios such that every “scenario” is an item of a Category (let’s say called Scenarios), and all assumptions and the “single” output you desire are defined as items of another Category (let’s say Scenario Attributes), then you should be able to achieve the same result, although with more steps. Sorry if I can’t be more specific – can you describe your use case in more detail?