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.
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 ??????
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...2CY/edit#gid=0
Example.xlsx
Bookmarks