Al said:
Given: 100 Contractors at $XXX.XX/hr (Enterprise Standard Rate)
- 30% - Capital Labor (Internal Labor - Capital Budgeted)
- 70% - Expensed Labor (External Labor - Department Expense).
- All 100 Contractors Enterprise Resources entering hours at the task level
Unknown: If a contract rate is $300, what's the best method for spliting
the
Enterpise Standard Rate into a Capital Labor cost representing $100 and
another Expense Labor cost representing $200.
Does this come out the box or does this require VB code?
Deliverable: To create a Capital and Expense report every four weeks,
thirteen times per year.
VBA for sure
This code should get you started.
to use it you need to insert the Flag1, Number1, Number2, Cost1, Cost2 and
Text1 fields into a Gantt Chart view.
Flag1 should be set to True for any task that needs this calculation run.
If it has a No in that field then the calc will not run for that task.
Number1 should hold the Cap % in decimal form. Number2 holds the Exp % in
decimal form. Cost1 will be the Cap Cost, Cost2 will be the Exp Cost.
Text1 holds an error message in case you mess up and enter a
Number1-Number2 value combo that does not add up to 1.0.
The Cap and Exp costs will also be held at the Assignment level in
Assignment Cost1 and Assignment Cost2 in case you want to see it there as
well. you can see these by inserting these fields into a Task or Resource
Usage view.
I could have written this to run faster and with much more efficiency but
this way is a little easier to follow in case you are new to VBA.
PLEASE work with this macro ina test project a while before you put it to
work in production.
Sub CapExp()
Dim T As Task
Dim A As Assignment
Dim SumAssnCapCost As Currency
Dim SumAssnExpCost As Currency
Dim ErrorFlag As Boolean
For Each T In ActiveProject.Tasks
If Not (T Is Nothing) Then
If T.Flag1 = True Then
If (T.Number1 + T.Number2 = 1) Then
T.Text1 = ""
Else
T.Text1 = "ERROR with Cap\Exp %"
ErrorFlag = True
End If
End If
End If
Next T
If ErrorFlag = True Then
MsgBox Prompt:="Examine Text1 and Fix Cap\Exp Percentages" & _
Chr(13) & "They must Add up to 1", Buttons:=vbCritical, _
Title:="Cap\Exp Error"
Exit Sub
End If
For Each T In ActiveProject.Tasks
If Not (T Is Nothing) Then
If T.Flag1 = True Then
For Each A In T.Assignments
A.Cost1 = A.Cost * T.Number1
A.Cost2 = A.Cost * T.Number2
SumAssnCapCost = SumAssnCapCost + A.Cost1
SumAssnExpCost = SumAssnExpCost + A.Cost2
Next A
End If
End If
T.Cost1 = SumAssnCapCost
T.Cost2 = SumAssnExpCost
SumAssignmentCosts = 0
Next T
End Sub
