My first post, so sorry for the format of question if wrong.
I have created a (in Excel 2010) basic rolling calendar on which I project revenue recognition splits based on in-bound work mix. Projects come in about 14 types, each type has a fixed price, and the total ordered projects each day is in the hundreds typically spanning 6 to 10 of the projects types. I use historical completion averages for each type to project the revenue value of each days case mix onto a calendar. (see example image below). Revenue accumulating on each forecast date is then totaled up for monthly projections, etc. My crude method places all of the projected revenue onto a single future date (daily values can exceed 100K per project type) while the true completion times vary plus or minus 10 days so for more accurate forecasting I need to take each projected value and split it over 20 days or so using the current projected calendar date as the center point of the curve.
I need help applying the right formulas to the right cells to make this distribution happen. Can anyone help with this, as I am an Excel novice.
I have created a (in Excel 2010) basic rolling calendar on which I project revenue recognition splits based on in-bound work mix. Projects come in about 14 types, each type has a fixed price, and the total ordered projects each day is in the hundreds typically spanning 6 to 10 of the projects types. I use historical completion averages for each type to project the revenue value of each days case mix onto a calendar. (see example image below). Revenue accumulating on each forecast date is then totaled up for monthly projections, etc. My crude method places all of the projected revenue onto a single future date (daily values can exceed 100K per project type) while the true completion times vary plus or minus 10 days so for more accurate forecasting I need to take each projected value and split it over 20 days or so using the current projected calendar date as the center point of the curve.
I need help applying the right formulas to the right cells to make this distribution happen. Can anyone help with this, as I am an Excel novice.
