Hi,
Despite my limited Excel experience, I am certain Excel has the functionality to generate the tool I need.
My current project will require the engagement and management of 50+ contractors. The goal is to create a standard Excel spreadsheet to be provided to each contractor which has a single input worksheet. The less input from the contractor the better. The contractor would compile their 'planned' monthly burn prior to commencing the contracted works. The contractor will update this on a monthly basis. 'Forecast' would initially match 'Planned' until the first status date, at which point 'actuals' would be added along with any variations. The 'forecast' would represent the estimated monthly spend remaining, accounting for actuals and variations.
Cumulative performance for each variable and cumulative % complete is calculated in a separate worksheet. This will be locked. the final worksheet is a one page report comparing planned with actual, intended as a simple tool for management to quickly grasp progress. Again, this will be locked.
The sheet has been set up so far so as to require no modification regardless of the variance in contract. Here is where my problems arise and expertise fails.
My questions are:
1) The project may run for as long as 24 months but each contract will only run for a matter of months. I want to lock the input sheet so only the columns which represent periods within the contract can be modified. Ideally the others would be shaded out. Cells have been denoted for contract start and completion. How can this be achieved?
2) Further to q. 1, I need the tables on the reporting sheet to reflect only the contract period and not the entire project period. How can this be achieved?
3) The purpose of the reporting sheet is to reflect progress to the most recent monthly status. How can I set the sheet up to return values based on the reporting period? Also is it possible to have a red status line run vertically through the input and cumulative sheets and the reporting graphs based on the nominated reporting period?
4) Finally, when actuals are compiled they will supersede the forecast data and the forecast will require re-estimation. I want each graph to reflect the actuals to the status date and forecast from the status date (as opposed to the current arrangement). They should perform as one unbroken line but ideally have a different colour for actuals and forecast.
I realise there is quite a bit which goes into these questions but any assistance would be greatly appreciated.
My main goal is to have a fully standardised, self contained spread sheet which is customisable based simply on contract period and status period. This way the contractor should only have to update the status date and input monthly actuals, variations and revised forecast.
The attached spreadsheet has some dummy data but requires a lot of work.
Again any assistance would be much appreciated!
To clarify, I do not have permission to attach the spreadsheet, so any direction will be great.
Despite my limited Excel experience, I am certain Excel has the functionality to generate the tool I need.
My current project will require the engagement and management of 50+ contractors. The goal is to create a standard Excel spreadsheet to be provided to each contractor which has a single input worksheet. The less input from the contractor the better. The contractor would compile their 'planned' monthly burn prior to commencing the contracted works. The contractor will update this on a monthly basis. 'Forecast' would initially match 'Planned' until the first status date, at which point 'actuals' would be added along with any variations. The 'forecast' would represent the estimated monthly spend remaining, accounting for actuals and variations.
Cumulative performance for each variable and cumulative % complete is calculated in a separate worksheet. This will be locked. the final worksheet is a one page report comparing planned with actual, intended as a simple tool for management to quickly grasp progress. Again, this will be locked.
The sheet has been set up so far so as to require no modification regardless of the variance in contract. Here is where my problems arise and expertise fails.
My questions are:
1) The project may run for as long as 24 months but each contract will only run for a matter of months. I want to lock the input sheet so only the columns which represent periods within the contract can be modified. Ideally the others would be shaded out. Cells have been denoted for contract start and completion. How can this be achieved?
2) Further to q. 1, I need the tables on the reporting sheet to reflect only the contract period and not the entire project period. How can this be achieved?
3) The purpose of the reporting sheet is to reflect progress to the most recent monthly status. How can I set the sheet up to return values based on the reporting period? Also is it possible to have a red status line run vertically through the input and cumulative sheets and the reporting graphs based on the nominated reporting period?
4) Finally, when actuals are compiled they will supersede the forecast data and the forecast will require re-estimation. I want each graph to reflect the actuals to the status date and forecast from the status date (as opposed to the current arrangement). They should perform as one unbroken line but ideally have a different colour for actuals and forecast.
I realise there is quite a bit which goes into these questions but any assistance would be greatly appreciated.
My main goal is to have a fully standardised, self contained spread sheet which is customisable based simply on contract period and status period. This way the contractor should only have to update the status date and input monthly actuals, variations and revised forecast.
The attached spreadsheet has some dummy data but requires a lot of work.
Again any assistance would be much appreciated!
To clarify, I do not have permission to attach the spreadsheet, so any direction will be great.
