Yell Budgeting tool: possible changes to formulas

Sharon will have to clarify this when it comes time, but am preserving this. Bug
is going on the sustaining engineering list per Sharon:

From Mike Holly

There is a problem with the MTD Spend % calculation being done in the
"aggregator".

I think we need to keep track of, and present a new 'YesterdayExpectedSpend'
value. This value is how much we expect to have spent up to yesterday, and is
calculated as follows:

YesterdayExpectedSpend = (Target Spend * (DayOfMonth - 1)) / (DaysInMonth)

The YesterdayExpectedSpend differs from MTDSpendTarget because the
MTDSpendTarget is giving us a value that we want to reach by the end of today,
whereas YesterdayExpectedSpend gives us what we wanted to spend by the end of
yesterday.

For comparison, the MTDSpendTarget calculation is:

MTDSpendTarget = (TargetSpend * DayOfMonth) / (DaysInMonth)

The MTD Spend % calculation done at the campaign level is:

Campaign Level MTDSpend% = MTDSpend / YesterdayExpectedSpend

For the first day of the month we short circuit that calculation and choose a
value of 1, since we don't expect to have spent anything yet.

The calculation done at the BF and BT levels are still set to use:

BT and BF MTDSpend% = MTDSpend / MTDSpendTarget

This leads to MTD Spend % being too low for the BF and BT levels, because we're
dividing data collected up to yesterday by data we expect to hit by the end of
today.

If we kept track of the 'YesterdayExpectedSpend' value, then the 'aggregator'
could roll that value up and use that value to perform MTD Spend % calculations
that match what is being done at the campaign level.

I believe we should also change the calculation being done for MTD Spend Diff.
Today it is being calculated as:
MTDSpendDiff = MTDSpendTarget - MTDSpend
The variables in this equation represent two different time frames,
MTDSpendTarget is where we want to be by the end of today, but MTDSpend
represents where we were at yesterday, so the MTDSpendDiff always looks larger
then it should be.

I suggest we change the equation to:
MTDSpendDiff = YesterdayExpectedSpend - MTDSpend

While we're at it, we should keep track of the suggested budget value as well!
It is the point of this whole tool, but we don't display it anywhere.

– Column Info –

Projected Monthly Spend : Our best guess, based on data up to yesterday

MTD Spend Target : The amount we want to spend by the end of today

MTD Spend Diff : How much we're short of the MTD Spend Target, Is today
calculated as MTDSpendTarget - MTDSpend, I suggest we change to
(YesterdayExpectedSpend - MTDSpend)

MTD Spend % : How well are we doing up to yesterday, Should be = MTDSpend /
ExpectedSpend