Hi There,
I'm looking for some assistance with a formula I've been stewing over for a couple of days now and would appreciate some assistance.
I am putting together a spreadsheet to help with forecasting where I have an input template that will feed a formulated forecasting vs. actual calc, and I need to calculate a rate for the month (by a category) multiplied by a dollar value.
Tab one will be known as my forecast spreadsheet, and I will explain below. This is where I need to enter a formula to calculate total cost for the month, by category, being 'Project Resource' or 'Vendor Resource'
Cell A6: "Project Resource"
Cell A7: "Vendor Resource"
Cell C5: Cell CN: Months (Oct-2018, Nov-2018, Dec-2018 etc.)
The formula to calculate a total dollar figure will be sitting in C6:N6 (against project resource) and vice verser for Vendor Resources in C7:N7.
Tab two is my data input template. It is a 'calendar' type worksheet showing each person working on the project, the forecasted days they will work into the future, their rate and if they are a project or vendor resource. I need to look up this data, sum their days worked in the month, categorise them by vendor or project and multiply by their rate.
A7:A29 is the Employee on the project
C7:C29 is if they are a project or vendor resource
E7:E29 is the employee's rate $
F7:F29 is day one on the project, G7:G29 is day two, etc. forecasted to Cell NQ7:NQ29 (each column is one day, and not all resources are forecasted to work each day)
To summarise, I need total forecasted cost of each resource type (project or vendor) by month, using a daily calendar type input.
Hopefully I have explained this well enough. It's a bit complicated.
Thanks,
Matt
I'm looking for some assistance with a formula I've been stewing over for a couple of days now and would appreciate some assistance.
I am putting together a spreadsheet to help with forecasting where I have an input template that will feed a formulated forecasting vs. actual calc, and I need to calculate a rate for the month (by a category) multiplied by a dollar value.
Tab one will be known as my forecast spreadsheet, and I will explain below. This is where I need to enter a formula to calculate total cost for the month, by category, being 'Project Resource' or 'Vendor Resource'
Cell A6: "Project Resource"
Cell A7: "Vendor Resource"
Cell C5: Cell CN: Months (Oct-2018, Nov-2018, Dec-2018 etc.)
The formula to calculate a total dollar figure will be sitting in C6:N6 (against project resource) and vice verser for Vendor Resources in C7:N7.
Tab two is my data input template. It is a 'calendar' type worksheet showing each person working on the project, the forecasted days they will work into the future, their rate and if they are a project or vendor resource. I need to look up this data, sum their days worked in the month, categorise them by vendor or project and multiply by their rate.
A7:A29 is the Employee on the project
C7:C29 is if they are a project or vendor resource
E7:E29 is the employee's rate $
F7:F29 is day one on the project, G7:G29 is day two, etc. forecasted to Cell NQ7:NQ29 (each column is one day, and not all resources are forecasted to work each day)
To summarise, I need total forecasted cost of each resource type (project or vendor) by month, using a daily calendar type input.
Hopefully I have explained this well enough. It's a bit complicated.
Thanks,
Matt
