How To Use Margin Trending For Planning

Margin trending requires the ability to view cost information. This applies to standard margin values as part of the OpenAir database or custom calculated margin values using cost in the equation. Cost viewing is sensitive if cost is representative of a user’s unique cost to the company. To allow margin reporting without exposing sensitive cost information, additional cost levels (up to three) are available per user.

One cost level is usually dedicated to actual cost and restricted by role permission to viewing by Administrators, Executives, and Finance resources using NetSuite OpenAir. A second cost level may be used for a banded cost value that is the same across resources or the same across types of resource such as job codes. For example, all project managers cost the company $100/hr while technical writers may cost the company $50/hr.   Cost levels can be renamed in the terminology controls of OpenAir. In the examples that follow, the Primary Loaded Cost field has been renamed to Actuals and the Secondary Loaded Cost field has been renamed to Banded.

It is commonly known that different types of positions in the company are at different salary bands, so a banded general cost at the job code level does not expose any information other than a general cost value for that type of person. Role permissions provide control of what level of cost users will have access to and therefore which margin reports will be available for use.

OpenAir provides a standard reporting value on summary reports called Projects-Project Billing Margin. However, depending on your configuration such as various charge stages for controlling financial reporting, it is best to create custom calculations with values representing margin. This also supports the ability to create margin values using the different cost levels so one set of reporting values can be restricted to actual cost values and another set using banded costs.

As you get ready to create your margin custom calculations, it is recommended to use revenue values along with the cost of timesheets and expenses.   If you are not using the revenue recognition features of OpenAir, then invoiced amounts will suffice.

OpenAir does not provide a single cost value representative of the total cost of a project so this will be your first custom calculations – adding Timesheet Approved Actual Cost to Approved Expenses. Total cost values usually created include are:

Total Cost (actual) = Timesheet – approved actual cost [actual cost] + Approved Expenses

Total Cost (banded) = Timesheet – approved actual cost [banded cost] + Approved Expenses

Margin calculations are then calculated by using the total cost and earned revenue. The custom calculations would include:

Margin (actual) = Recognized Revenue – Time Cost (actual)

Margin (banded) = Recognized Revenue – Time Cost (banded)

To calculate a margin percentage, another set of custom calculations is required by dividing the margin value by the recognized revenue:

Margin %(actual) = Margin (actual) / Recognized Revenue

Margin %(banded) = Margin (banded) / Recognized Revenue

The full benefit of margin calculations is best realized when using custom fields on projects to identify the type of contract and/or the type of offering. By using contract type or service offering type as subtotals on summary reports, margin and margin % values can then be reviewed by each of these categories providing valuable data analysis for strategic business decisions.

Dashboard charts can be created to trend margins across timeframes for graphical representation of the data.

Considerations

Travel expense reimbursement by clients typically is a 0% margin or may have a small admin fee or markup associated with the rebilled receipts. By adding expense into the total margin calculations, the average margin of the project will tend to be reduced. It is recommended to compute margin and margin % using labor only then create a total margin and margin% custom calculation for comparative analysis. This will provide more visibility into the cost of labor versus the cost of expenses.