none
how to assign monetary value to tasks and track it over time MSP-2010 RRS feed

  • Question

  • What I would like to be able to do is create a task for a project phase and be able to assign a monetary value to it as a percentage of the total project fee.  so 4 tasks in a project with a $10k fee would be $2,500 per phase/task.

    From that I would like to be able to determine what is owed as of "today" and at the beginning of each month.  It would be great if it would also be able to take into consideration what has been received in payment and deduct that, and even better if it could tell me what has been billed, but maybe that's too much to ask of MS Project.

    This is easy to do manually for one project, but I have all my projects set up in one master file and if I could easily see all of it together to be able to total it up, I would have a better sense of the outlook projecting forward, and how much I am owed.

    Thanks!

    Thursday, June 25, 2015 3:19 PM

All replies

  • Jimminy,

    So why are you using Project and not Excel or a small business accounting application? When you talk about payments and billing, that's accounting, not scheduling.

    But okay, let's say that you really do need Project for it's scheduling. You indicate what you want is easy to do manually for one project, but you have all your projects set up in one master file. What fields do you use for your various monetary data when you set it up manually? When you say a master file, what exactly do you mean? Do you have multiple "projects" set up in a single file that you call a "master", or is each project an individual file that is inserted dynamically into a master? If you can set up your desired data in a single file, what problems are you experiencing such that you can't go ahead and propagate that into the master?

    I know, lot's of questions, but I think we need a better understanding of what you are doing in order to help you.

    John

    Thursday, June 25, 2015 3:42 PM
  • The only reason I don't do it in excel is that I don't know how to break such things down over time.  The rest would be much easier.  Just a total project fee cell, then cells for the phases.  In fact, I have a spreadsheet doing exactly that but it tells me nothing about how things project out over time, month by month.

    Our phases run consecutively, and never overlap and I don't care at this point about add services or expenses.  I would just like to be able to see a chart that graphically shows the phases/tasks projected out over time, with columns broken down by month, and a number at each month line that tells me (assuming everything is on schedule) what percentage of that task is complete, and can be billed for.

    It would just be a matter of Project assigning a value to the phase/task based on the percentage of total fee that phase represents and having Project figure out what percentage of the phase would be complete on the first of the month and telling me what that number is.

    As for the question about master files, I have a project file set up with all the projects dynamically inserted as sub-projects.  This allows me a holistic view of everything going on.

    Add all of the active projects in their various phases together and we know how much we can theoretically bill on that date going out over time into the future.

    This isn't really accounting at all, just a visual for the sake of understanding where we are heading with billables.  If it can be done in excel, cool, I just don't know how to add that time dimension.

    Thanks!

    Thursday, June 25, 2015 10:50 PM
  • Jimminy,

    If you already have a spreadsheet with a column for each month, then what's missing? I assume you linearly spread the total cost over the months, so it would be a simple matter of assessing how much progress was made each month to determine billing, (which you would need to do in Project also), or if it is all level of effort, the progress is simply the number of months from the start divided by the total months. It's all just some formulas and Excel "Excels" at that. I don't see any "scheduling" in your scenario, just a bunch of math. Further, Excel is much better equipped to create charts and graphs, if that is what you need.

    However, in your initial post you mentioned that you could easily set this up for a single project but had some type of unspecified problem in the master file environment. I asked what Project fields you were using for your costing data and you didn't answer. If we don't understand how you have it set up in Project for a single file, we can't really help you extend that process to a master file.

    And just for reference, dynamic master files in Project are prone to corruption. You must never rename, move, overwrite, or save off any files in the structure. Further, all files should reside in a single folder on a local drive.

    John

    Thursday, June 25, 2015 11:23 PM
  • No, I don't have an excel file with a column for each month, I think if I did I would be happy with that, but I don't know how to do such a thing.  What I have is a spreadsheet that breaks down each phase as a percentage of the total fee, then I type in a percentage for how much I feel I've completed for that phase on the first of the month and that gives me a value based.  But I have to manually put in that percentage to know how much I can bill.  And I have a separate excel file for each project.

    I also have an MS Project files for each project, broken down into phases charted over time.  Each of these files is embedded into a master MS Project file that allows me to see all the projects at once.

    This is what I said I could easily set up.  I didn't mean to imply that I could accomplish what I was describing earlier as my goal in one project's MS Project file, but not the master.

    I have seen Excel files that are dynamic enough to be "time-aware", so to speak, but I'm not savvy enough to create one.

    Maybe I'm just drastically over-complicating things.  I just want to be able to see where all my projects are on specific dates based on the the schedule of the project over different phases, and since MS Project can so easily show me that graphically, I assumed it would be a simple thing to get the values as I described.

    Friday, June 26, 2015 1:41 AM
  • Jimminy,

    The basic question comes down to this, are you scheduling or just doing some basic accounting? I still don't see that you are scheduling anything (i.e. a schedule being a series of tasks that are linked in a logical sequence to achieve an end goal. One or more resources (labor and/or material) are assigned to each task and cost is derived from resource rates and perhaps some fixed costs).

    But, let's pursue the project approach and we'll figure it out or determine that what you want is really not a good match for Project.

    You said in your first post, "this is easy to do manually for one project". To say that you must have tried it and would therefore have something you could show us. A screenshot would be very helpful.

    What data are you inputting to Project? And into what fields? Given that input, what do you expect Project to provide?

    And yes, this does seem to be getting over-complicated and we're still not on the same page. You said you want to be able to see where all your projects are on specific dates (e.g. monthly). Are you therefore going to update your projects by inputting a % complete value and what will that input be based on?

    Again, a screenshot would be great. If you are not yet "verified" to post images, a detailed example would be helpful.

    John

    Friday, June 26, 2015 2:21 AM
  • I tried the screenshot thing yesterday but not verified.

    The basic question comes down to this, are you scheduling or just doing some basic accounting? I still don't see that you are scheduling anything (i.e. a schedule being a series of tasks that are linked in a logical sequence to achieve an end goal. One or more resources (labor and/or material) are assigned to each task and cost is derived from resource rates and perhaps some fixed costs).

    The scheduling in MS Project is fairly detailed.  The project is broken into phases of the AIA contract (we're architects) and those phases are broken down into milestones and tasks.  But while that detail is useful internally, only the larger phases and how far along we are within those phases is important to this effort.  The accounting is very basic.  Our labor could be inserted and we could see costs, but we don't really care about that either for now. 

    But, let's pursue the project approach and we'll figure it out or determine that what you want is really not a good match for Project.

    You said in your first post, "this is easy to do manually for one project". To say that you must have tried it and would therefore have something you could show us. A screenshot would be very helpful.

    Meaning, it's easy to use the excel spreadsheet to see the "accounting" side of this even though I have to manually input the percentage complete for the phase, and it's easy to create an MS Project file to see the schedule over time and manually combine the information from each software.  If I have to do this for 10 projects every month it gets tedious.

    What data are you inputting to Project? And into what fields? Given that input, what do you expect Project to provide?

    As said above, in Project I'm doing the schedule.  I'm also creating an office master with all the projects shown together on one gantt chart.  I'm hoping to have MS Project be aware of the value of the all the phases and be able to determine what percentage of the current phase for each project is complete on the first of the month going off infinitely.  It would tell me Z if Z=phase value X percent complete on the first of the month.  And even better if the phase value could be based on a percentage of the project fee so I only have to put in the overall fee for each project and see how revenue on the first of the month looks.

    And yes, this does seem to be getting over-complicated and we're still not on the same page. You said you want to be able to see where all your projects are on specific dates (e.g. monthly). Are you therefore going to update your projects by inputting a % complete value and what will that input be based on?

    I will only be updating the project schedule, and the whole point is to let Project tell me the percent complete

    I hope that helps a little!  Thanks.

    Friday, June 26, 2015 4:57 PM
  • Jimminy,

    When you say you will be updating the project, what exactly will you be updating? If you are not assigning any resources then you can update the project in two ways, either enter a value into the Percent Complete field, or enter a value in the Actual Duration field and Project will calculate percent complete using the formula:

    percent complete = actual duration/total duration.

    Is that what you are doing?

    In the absence of a screenshot, a detail example would be very beneficial. For example, are your phases summary lines with milestones and tasks as subtasks? What fields are you using to input data? Keep in mind that summary lines in Project are not tasks at all, rather they are simply a summary of the subtasks under them. So if you are only interested in seeing data at summary (i.e. phase) level, you will need to update the status of the subtasks in order for that data to roll up to summary level (i.e. you cannot input percent complete or actual duration at summary level, those are calculated values based on data from the subtasks under the summary).

    I just want to make sure you are not headed down a path expecting Project to do something it is not designed to do.

    John


    Friday, June 26, 2015 5:33 PM