Tables for comparison (Case Study)

Mark HHP

Ok, so this is part asking for opinions, part typing it out to make use of the Rubber Duck principle so bear with me.

I need to build a Forecasting Recon module for the Consultants on my CRM.

This is the theory: every Consultant needs to go to the Clients that they work with, and on each Client add a Forecast line item. Basically saying "I think that next year Client will book in XYZ months". Once he has added all of the line items for all of his Clients (tedious) the Manager comes along and hits a button and these all become the Budget for the year. The Forecast remains editable so I imagine it will just be a straight transfer of information from Forecast BO to Budget BO.

So now, in the Client, I'd have two tables, one for Forecast, one for Budget. Now I need a third for Recon. This will need to pull any differences between the two and maybe show them in a different colour. This will be quite tricky I reckon as it basically needs to be like this:

In Your Budget, Client X has been predicted as booking in May for a medium. In Forecast you see Client X has changed his plans, and is now booking in April for a large. So the Recon window would need to show the differences between what is predicted and what is booked, using Quotes (also using line items). I've never done any kind of comparison table like this.

Does this make sense to anyone? Anyone done this before? Am I over-complicating/simplifying this?

If I can pull this off my boss will be hella impressed 😛

tford

Sounds pretty easy to accomplish in AwareIM.

customaware

Why 3 BOs

Can't each item have

FCastQuant
FCastMonth
BudgetQuant
BudgetMont
ReconQuant
ReconMonth

Or something like that

Mark HHP

HI Mark,

OK, that's thrown a few more options into the ring. I'll need to rethink things but that's not always a bad thing. Just need to think about the display and how that will work.

customaware

Send me a spreadsheet of what the data flow might look like Mark

Mark HHP

Oh and the biggest issue is that they can have multiples. We do single, 3 month, 6 month and 12 month contracts. So I don't know if single attributes will work...I think that's why line items of separate objects would probably work the best.

I think the biggest issue I've encountered (in my virtual version in my head) is the recon table to show differences. We refer to 'Actual' as actual bookings. So you're comparing what was predicted against what came in. So I'd have the following windows in my version,

• Forecast: what was predicted but fluid, changing through year
• Budget: what Consultant has decided is their target, basically
• Actual: the Quotes >> Bookings that have come through
• Recon: to explain visually, this would need to show either a green value to indicate that what was budgeted was Booked, or red to show that the budgeted was not booked/moved to another month (with another column for Reason)

Mark HHP

I also need to be conscious of how long it will take for both the Consultants and Manager to deal with all of this (we currently have around 7000 Clients) so obviously automation as far as possible is the goal.

I was working on another system for ForecastLineItems and ForecastRows many moons ago, that seemed to be getting close to what I needed. If I did everything in Rows and LineItems, I'd need to have Recon be a combination of BudgetRows and ActualRows, maybe group by those to differentiate.

I feel like I'm talking myself in circles :/

tford

From what you have described so far, there seems to be a few unknowns:

1) How many of each type of record will there be for a client per year?

2) What are you trying to compare .. the entire year forecast for a client vs. the entire year actual for a client? Or will there be multiple comparisons for a client per year?

customaware

And for Recon.... which I think means the delta between Forecast and Budget......

A Forecast vs Budget Quantity delta is a value
A Forecast vs Budget Month is either Late or Early or maybe +- Days

But what is the Delta between Medium and Large?

Paint us the full picture and more than happy to try and suggest a solution

Mark HHP

Ok, let me flesh it out a bit more and I'll come back. My goal posts get shifted often so let me check what the latest requirement is.

Thanks for the feedback so far.

Mark HHP

Ok, here's a basic idea of what I'm going for.

https://docs.google.com/spreadsheets/d/1mSfSER63VAI3Jt9ZVb50zdoL__Op2-bmiI9FYQC-ITQ/edit?usp=sharing

Jaymer

I updated the sheet with a Scenario.
Seeing your initial movement of Feb->Mar, it made me wonder if the movement of a booking to another spot needs to be tracked? or is helpful to be tracked? cause you're going to enter that as a freeform comment possibly. but the comment won't help any future automated analysis of the mods that have happened.

And my example was to show how if you did think about tracking it, what about the scenario where it moves to 2 (or more) months.
Like someone may budget a full page once a year, and then change cause business dictated it. Or vice versa, consolidate 2 1/s pages into 1 full page. Seems like a nightmare to track where that $ actually got reallocated to when you think about 7000 clients, but probably doable.
jaymer...

BLOMASKY

1). I would suggest a structure with 5 attributes (ok, lots more, like date of last change, comments for etc, etc. but the 5main attributes are:

Client (magazine)
Attribute Type (Forecast, Budget or Actual)
Year
Month # (1-12) (with month #'s would be easy to roll up Months to quarters, years, etc.
Amount

You could have a batch process to generate this from prior forecast (or actual) to give the consultants a starting point.

Since your data is not REAL TIME. I assume you do not have to have results pop as soon as a record is changed, then you can easily have a batch process compare any 2 and create an output record into another BO with similar layout (except instead of attribute type, everything will be the DELTA value. So your batch process would ask which 2 (of the 3) attributes you wanna compare. it would create the delta records and run a pretty report.

Bruce

BLOMASKY

I was not thinking with my AWARE hat on. Sorry, was wearing my SQL hat at the time.

You do want 3 diff. BO's. One for Forecast, One for Budget, One for Actual.
Any (or all) can have a shortcuts to the other 2. and then a computed field with the DELTA. This eliminates batch processing, and all you have to do is have a report (or query) showing the field, the shortcut (if you want) and the delta calculated field. Your item display rules will color code the cells if the delta field <> 0

bruce