Combining entered values and calculated values in a matrix
I often work on financial projections, as I’m sure other Quantrix users must do also.
Normally, I have some historical information, say in the P&L, and I want to project future P&L values.
I have 2015 YTD P&L, so I only have values for Jan-Mar. I’d like to somehow use Quantrix to leave historical values in the spreadsheet if known (Jan-Mar), and if blank, use formulas to calculate values (Apr-Dec).
Ideally, the viewer could see which cells have calculated data – perhaps using italics, and which were entered values.
Is there a way to do this?
I wondered about using 2 matrices – one with historical information (where known) and another ‘presentation’ matrix. The cells in the ‘presentation’ matrix would use values from the historical matrix, if known, and otherwise calculate values. This seems a bit clunky for such an elegantly designed tool, though.
golden463,
See the attached model I think it will get you there on what you want to do.
The budget and Actuals matrices are being pulled into the summary matrix.
Then the display helper matrix determines which to use in the summary matrix 1 is for actuals 0 is for budget. You the user enter the 1 & 0s to determine which is shown (there is a way to base this off the current date too, but I have not shown this here).
On the summary view I’ve also added an expression in conditional formatting to gray those cells holding actual. I’ve also collapsed the Actual and Budget rows.
I hope this helps.
Respond if you have questions. I also encourage you to watch my youtube channel Quantrix Authority. I’ll be covering this in a future episode.
Regards,
Rich Lopez