We are currently struggling to get the right calculations in place to support a utilization forecast report for our engineering teams. We have 3 separate sources we are blending - Employee List, Actual Hours, and ETC Hours. The Employee List is technically the primary datasource (we can get away with using ETC hours as the primary). Essentially, though, we need to blend the Actual and ETC hours to get a monthly forecast that changes as the month goes on (If week 2 is complete, we have two weeks of actuals and two weeks of ETCs - in a four week month). The row grand totals are what are incorrect. Sample workbook and data attached.

Also, a secondary problem exists in calculating base hours based on weeks worked (if someone starts with the company mid-month). If we were to assume everyone had a base of 40/wk, that would be easy enough, but we need to do some calculations based on when they worked, and aggregate appropriately for the same row grand total issue above. We currently are trying to base the calculation on the null values in the Actual and ETC Hour datasources, but do have the ability to get hire/term dates included in the Employee List if that helps with the base hours calculations.

Any help is greatly appreciated.

+

+

=