Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Projections Formula
I have a multiple sheet file that I use to develop projections from a
forecast + actual YTD. Each month's cell analyzes whether there is anything in the actual sheet, if not, it looks for something in the forecast sheet, if there is nothing there is sums the year to date actual and subtracts that from year end budget and then divides that by how many months are left to see what I need to do the rest of the year to meet budget. The formula is =IF(Actual!J210,Actual!J21,IF('Forecast Input'!J210,'Forecast Input'!J21,((($O21-SUM($B21:$H21))-SUM('Budget 2007'!$I21:$M21))*(1/5))+'Budget 2007'!J21)) My problem is that I am manually changing the formula every month to adjust the range being summed and the number of months (i.e. 1/5). There could be 11 possibilites depending on month. I think there is probably a simple solution that would avoid the updates but I've overthought it to the point I've got myself confused. Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Projections Formula
try something like (1/(13-month(now())
"SWM" wrote: I have a multiple sheet file that I use to develop projections from a forecast + actual YTD. Each month's cell analyzes whether there is anything in the actual sheet, if not, it looks for something in the forecast sheet, if there is nothing there is sums the year to date actual and subtracts that from year end budget and then divides that by how many months are left to see what I need to do the rest of the year to meet budget. The formula is =IF(Actual!J210,Actual!J21,IF('Forecast Input'!J210,'Forecast Input'!J21,((($O21-SUM($B21:$H21))-SUM('Budget 2007'!$I21:$M21))*(1/5))+'Budget 2007'!J21)) My problem is that I am manually changing the formula every month to adjust the range being summed and the number of months (i.e. 1/5). There could be 11 possibilites depending on month. I think there is probably a simple solution that would avoid the updates but I've overthought it to the point I've got myself confused. Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Financial projections - Income statement, bal sheet and cash flows | Excel Discussion (Misc queries) | |||
Projections | Charts and Charting in Excel | |||
Trendlines, best fit and projections | Charts and Charting in Excel | |||
Financial projections calendar in a Outlook-style for ease of use | Excel Discussion (Misc queries) |