Running Totals

[Guest]

I have created a spreadsheet to forecast monthly financial results. Each
month has a projected amount, an actual, and a variance for each product.
After December, I have a total for the projected, actual and variance.
However, I would like to create a means of calculating the running variance
as I enter the actual monthlyh data. For example, the February projected
data is in column AP, the acutal is in column AR and the Variance for
February is in Column AT. I would like to be able to have a formula that
recognizes the fact that February is the most recent month for which I have
actual data and therefore totals the variance for February in Column AT w/
Column AN which has the variance for January. I would want to do this for
each month of the year. Please let me know if this requires furhter
explanation. Thank you in advance for your assistance.

 

[Guest]

There is a common trick to doing this:

1. in A1 thru A12 put the monthly projections
2. In B1 thru B12 put the monthly actuals
3. somewhere else enter:
=SUMPRODUCT((A1:A12)-(B1:B12))

Now the trick:

Do not initially load B1 thru B12 with blanks. Load them with the same
values is in A1 thru A12. The variance will initially be zero. As months go
by, replace the pseudo-actuals with the real actuals.

 

[Guest]

Thank you. Have a follow up question if you don't mind. How would I adjust
this formula to keep the running variance so that in the next month it adds
the previous month's variance to the current month's variance and so on until
the end of the year? Thanks again.

 

[Guest]

First the formula as stated will produce the cummulative variance. If you
want visibility into both the monthly vaiances and the cumm, then:

In C1 enter:
=A1-B1
and copy down thru C12

In D1 enter:
=C1
In D2 enter:
=D1+C2
then copy D2 to D3 thru D12

Column C will display the monthly variance.
Column D will display the cumm variance through that month.