Hello, and thank you in advance for your help. I'm attempting to actualize invoice data per month (easy part), and then re-forecast the target revenue for the remaining months (I hate this with a fire of a thousand suns). I'm currently able to forecast for the entire advertising campaign based upon a timeframe and revenue per month, but once I pull in 'actual' invoice data for the month, I can't seem to find an easy way to 're-forecast' for the remaining months based upon a change in a past month.
<tbody>
</tbody>
Currently, I'm able to forecast by month using the following:
Column G
=(IF(AND($F2>=G$1,$E2<=DATE(YEAR(G$1),MONTH(G$1)+1,0)),MIN($F2,DATE(YEAR(G$1),MONTH(G$1)+1,1))-MAX($E2,G$1),0)/($F2-$E2))*$D2
My main question remains then, how do I adequately actualize the March Invoice (H) and give Column J (and any subsequent month) a new revenue goal? How can this stay consistent month to month without changing the formula (so when a new month is added, the projections are instantaneous with the new invoice data?). If you'd like to play around in it, I have a google sheet going: https://docs.google.com/spreadsheets/d/1kNVzcWHxKFXfK43MxsYgLim3JM3E0SGfr3-zD1tn2CY/edit#gid=0https://docs.google.com/spreadsheets/d/1kNVzcWHxKFXfK43MxsYgLim3JM3E0SGfr3-zD1tn2CY/edit#gid=0
| Line Item (A) | Rate (B) | Targeting Impressions (C) | Target Spend (D) | Flight Date Start (E) | Flight Date End (F) | 3/1/2015 (G) | March Invoice (H) | Revenue Goal Less March (I) | 4/1/2015 (J) |
| Animated | $5 | 1,000,000 | $10,000 | 3/3/2015 | 7/31/2015 | $1,933.33 | $2,000 | $8,000 | ?????? |
<tbody>
</tbody>
Currently, I'm able to forecast by month using the following:
Column G
=(IF(AND($F2>=G$1,$E2<=DATE(YEAR(G$1),MONTH(G$1)+1,0)),MIN($F2,DATE(YEAR(G$1),MONTH(G$1)+1,1))-MAX($E2,G$1),0)/($F2-$E2))*$D2
My main question remains then, how do I adequately actualize the March Invoice (H) and give Column J (and any subsequent month) a new revenue goal? How can this stay consistent month to month without changing the formula (so when a new month is added, the projections are instantaneous with the new invoice data?). If you'd like to play around in it, I have a google sheet going: https://docs.google.com/spreadsheets/d/1kNVzcWHxKFXfK43MxsYgLim3JM3E0SGfr3-zD1tn2CY/edit#gid=0https://docs.google.com/spreadsheets/d/1kNVzcWHxKFXfK43MxsYgLim3JM3E0SGfr3-zD1tn2CY/edit#gid=0
