badabing
05-23-2009, 03:48 AM
I am trying to develop a spreadsheet to prepare a consolidated cost forecast made up of many different line items. The total forecast is made up of the sum of the numerous line items listed on the ‘Line Items’ worksheet, each item will be phased based on a variety of preloaded forecasts that will generate a monthly forecast. Line items that are greater than $2m dollars will be phased individually. I would like to be able to press a macro button that will populate the ‘Cost Forecast’ worksheet (retaining the format shown in the example) with all line items over $2M listed on the ‘Line Items’ worksheet. A separate line would be generated the sum of the remaining (less than $2M) line items will be phased as a whole.
Line Item types
Each of the line items to be phased are to catagorised in one of 5 types. O, PV, R, N and P. The line items are registered as numbers in one of these categories in the ‘Line Item’ worksheet (columns B, D, E, F &G).
Value
Each of the line items have a numerical value associated to them indicating whether the item is approved (column U) or unapproved (column M). If an item is approved I would like A to be indicated in column B and U if unapproved. The value of the item (be it approved or unapproved)should be placed in column L. If a line item is approved there will be the same value in both the approved and unapproved columns in the ‘Line Items’ worksheet.
Forecast and Forecast Selections
Each of the line items on the ‘Cost forecast’ worksheet (all line items over $2M and the remainder) need to be forecast individually. This can be one of 15 preprogrammed forecasts or a manually forecast option, where the user will enter their own forecast. The 15 preprogrammed forecasts are listed in the forecasts worksheet (e.g. forecast 1.1). The option to select which forecast is to be applied to the line items is done from the drop down menu in column K. Upon selection of the forecast from the dropdown menu the relevant % forecast will be populated against the line item, the corresponding cost profile of the forecast % multiplied by the value in column L of the ‘Cost Forecast’ worksheet thus generating the monthly profile for that item. As the forecast will change monthly the forecast is only to be applied from the date specified in cell C8 onwards. The actual that are already populated will remain unchanged.
Summary
In summary I would like a spreadsheet like the mock up I have attached that will bring in line items over $2M from the line item sheet (as in my example). The list of the line items will change every month with new items added and values associated with the existing line items constantly changing. To those line items I would like to be able to easily apply a preprogrammed forecast, as seen in the forecast worksheet. These forecast will be updated every month but the number of forecasts will as a rule of thumb remain constant.
I would greatly appreciate any help, advice in writing macros to aid me in developing this spreadsheet as my knowledge is limited but if the above is possible my worktime will be greatly reduced.
Thank you in advance. : pray2:
Line Item types
Each of the line items to be phased are to catagorised in one of 5 types. O, PV, R, N and P. The line items are registered as numbers in one of these categories in the ‘Line Item’ worksheet (columns B, D, E, F &G).
Value
Each of the line items have a numerical value associated to them indicating whether the item is approved (column U) or unapproved (column M). If an item is approved I would like A to be indicated in column B and U if unapproved. The value of the item (be it approved or unapproved)should be placed in column L. If a line item is approved there will be the same value in both the approved and unapproved columns in the ‘Line Items’ worksheet.
Forecast and Forecast Selections
Each of the line items on the ‘Cost forecast’ worksheet (all line items over $2M and the remainder) need to be forecast individually. This can be one of 15 preprogrammed forecasts or a manually forecast option, where the user will enter their own forecast. The 15 preprogrammed forecasts are listed in the forecasts worksheet (e.g. forecast 1.1). The option to select which forecast is to be applied to the line items is done from the drop down menu in column K. Upon selection of the forecast from the dropdown menu the relevant % forecast will be populated against the line item, the corresponding cost profile of the forecast % multiplied by the value in column L of the ‘Cost Forecast’ worksheet thus generating the monthly profile for that item. As the forecast will change monthly the forecast is only to be applied from the date specified in cell C8 onwards. The actual that are already populated will remain unchanged.
Summary
In summary I would like a spreadsheet like the mock up I have attached that will bring in line items over $2M from the line item sheet (as in my example). The list of the line items will change every month with new items added and values associated with the existing line items constantly changing. To those line items I would like to be able to easily apply a preprogrammed forecast, as seen in the forecast worksheet. These forecast will be updated every month but the number of forecasts will as a rule of thumb remain constant.
I would greatly appreciate any help, advice in writing macros to aid me in developing this spreadsheet as my knowledge is limited but if the above is possible my worktime will be greatly reduced.
Thank you in advance. : pray2: