Multiple Billing Rates Question

[Jlhoffner]

I'm working on a project for a small non-profit where we need to develop reporting that shows projected revenue for the week ahead. The data to leverage are as follows:

Employees - Assigned to multiple projects, but have different hourly billing rates for each project. So John Smith is billed at $XX on Project #1 and $YY Project #2

Projects - 40-50 active projects at any given time

Project Hrs - Managers will be entering the # of hours their employees will be working on each project the next week.

The reporting needs to translate those hours into revenue. Seems imminently doable in PowerPivot. I'm envisioning the following data model:

EMPLOYEES
Employee ID
Name

PROJECTS
Project ID
Project Name

BILLING RATES
Employee ID
Project ID
Hourly Rate

PROJECTED HOURS
Employee ID
Project ID
Hours (#)
Date (Day)

Calendar
Date
Weeknum
etc.

Two questions:
1) does anyone see any major problems with the data model? I will probably build a simple Access DB with a form for Managers to enter data for PROJECTED HOURS

2) how do I get my DAX formula to pick up the different rates by project for individual employees when doing to math to project the revenue for the week ahead?

Help appreciated. Thanks!

jh

 

[Smitty]

Welcome to the Board!

As long as you have relationships set up for Employee ID/Project ID, it should work fine.

But if you're going to build a form in Access, why not just set up a query there?

 

[Jlhoffner]

We may end up doing just that, but I'm not sure yet. One reason to do it PPVT would be to enable a little more dynamic/flexible interaction with the numbers on an ongoing basis. So if we see that the projections are way off from a desired objective, for instance, we could quickly drill into which employees are being underutilized and might be deployed against other billable projects. Once we get the metrics set up in PPVT we should be able to do that kind of forensics super quickly.

 

[Smitty]

I love PP and its flexibility. How about nixing Access altogether and building a user form in Excel?

 

[Jlhoffner]

YES! Can you help me with that? I've been stumbling through that to be honest. When I've tried the Forms option, I'm getting a message saying I have too many data points for the form to handle. There has to be a better way to create something that the manager can use to enter the data and commit to a table in the PP data model...

 

[Smitty]

Here's a good user form tutorial:

Create an Excel UserForm

Once you get the hang of it, it's not that hard to customize, although it's not as easy as setting up a bound form in Access.

 

[Jlhoffner]

Awesome. Thank you very much!

 

[Jlhoffner]

Well... you were right. setting up a form isn't very difficult. The challenge I'm finding is that there isn't a simple guidebook on the VBA code to transfer the data from the 4 fields in the form to the table that will be used in the PPvt data model.

 

[Smitty]

The easiest way is to identify a last row variable, then use that to place your form values:

Dim lr As Long
Dim ws As WorkSheet

  Set ws = Sheets("PP Data")

  lr = ws.Cells(Rows.Count,"A").End(xlUp).Offsest(1).Row

  With ws
    .Cells(lr,"A").Value = Me.TextBox1.Value
    .Cells(lr,"B").Value = Me.TextBox2.Value
    .Cells(lr,"C").Value = Me.TextBox3.Value
    .Cells(lr,"D").Value = Me.TextBox4.Value
  End With

 

[Jlhoffner]

Wow, seems to be working. You have been helpful beyond words my esteemed Smitty.