Overhead Allocation Model - using Job Days to assign costs

FIRST, take a deep breath.  There are a lot of numbers in this model.  But this is very useful stuff.

We have discussed in a previous section the development of the Job Days overhead allocation model. A quick review here should suffice.

To use this costing strategy and the template below, you are going to need two numbers to get started.

First, you will need last year's overhead expenses. This number can be found on your year-end Income Statement, usually titled "Expenses" or "Indirect Expenses". This category will be after the "Cost of Goods Sold" section, and following the "Gross Profit" number.

You will also need last year's Job Days. A Job Day is defined as every day that falls between the start of a job and the completion of a job. Total Job Days would be all job days for Job1 plus all Job Days for Job2 plus all Job Days for Job3 and continuing to all Job Days for JobN, or your last job of the year. You will count jobs that are not finished by December 31, from the starting day of the job to December 31.

This exercise is a bit of a pain, but you only have to do it once. And it will be revealing to see the amount of time each job takes.

View the Overhead Allocation Model Based on Job-Days ---->

Download the
Overhead Allocation Model Based on

Calculating $ per Job Day

Once you have those two numbers, divide the number of Job Days into the Total Overhead Expense to get Job cost per day.

For example: Assume the the total overhead expense for the year was $196,000, and the total Job Days was 1250. The calculation looks like this--

Total Overhead Expense Last Year/Total Job Days Last Year = $ per Job Day Last Year

$196,000/1250 = $156.80, round to $157.00

In other words, each day that a job was in production last year cost $157.00 in overhead costs for that job.

Using the Cost per Job Day

To Estimate - In the estimating process, the estimator calculates the number of days a project is expected to take, multiplies that number by $157.00 (to use numbers from the above calculation), and assigns that amount to the estimate as Overhead.

Assume that the job is expected to take 115 days to complete.

115 days X 157.00 = $18,055.00 in overhead costs assigned to job.

To Allocate Overhead to a Job -If your historical Job Day Overhead cost is $157.00, to allocate overhead expenses you simply multiple the Job Days in each job for the current period by $157.00.

Assume the estimator hit the duration of the job exactly. The job started on March 12 and was completed on July 4.

20 days x $157.00 per day = $3,140 allocated overhead for month of March
30 days x $157.00 per day = $4,710 allocated overhead for month of April
31 days x $157.00 per day = $4,867 allocated overhead for month of May
30 days x $157.00 per day = $4,710 allocated overhead for month of June
4 days x $157 per day = $628 allocated overhead for partial month of July

Of course, if the job is completed early, the overhead allocation is less. Conversely, if the job runs long, the overhead allocation will be greater. This method of overhead allocation is very efficient in pointing out the cost of schedule over-runs.

The major drawback to this allocation model is the use of an Overhead Allocation Factor derived from year-old information, and the inability of the model to dynamically update the Factor value based on changes in overhead load.

A Dynamic Job-Day Overhead Allocation Model

To correct that deficiency, I have developed an allocation model which accounts for changes in overhead load and which smooths the load calculation, thereby moderating the peaks and valleys of changes in the Overhead Allocation Factor. The template for this model is available for downloading below, and the pdf formatted sample documents are included in the text.

Sample Quarter 1

The first Sample document displays the first quarter of activity.

  • Note how uncompleted jobs from the previous year (Jobs 11-24 and 11-25) are included in this year's work.
  • The Actual Overhead Expense, found on the Income Statement, is entered in the appropriate month.
  • The job ID and the start date of the job is entered. This Sample document displays the first Quarter activity, during which two of the jobs have been completed.
  • The job days for the month are entered under JD for each month. The model calculates the remainder of the information.

The overhead allocation amount to be charged monthly to the job is in the unshaded column under the month in the same row as the job information.

The Overhead Allocation Factor to be used for estimates generated for the following month is found in the row "Per Job Day Smoothed", shaded red. In the month of February, the estimator would use a Factor of $172.09 per day, in March would use a Factor of $170.90 per day, and so on.

Model Summary

The information shown in Sample Quarter 2, Sample Quarter 3, and Sample Quarter 4, display the results for the remainder of the year.

Note that the Overhead Allocation Factor adjusts on a monthly basis, reflecting the previous aggregate monthly results. Compare the non-smoothed results in the row marked "Per Day Actual" with the smoothed results in the "Per Job Day Smoothed" and the effect of smoothing is obvious. Look especially at the results for May to see the effect.

The model also tracks Total Job Days for the Year-To-Date, and Average $/Job Day Year-To-Date.

Note also that the Actual Overhead Expenses Y-T-D and the Overhead Expense allocated Y-T-D are not the same. This is caused by the fact that a previous-month Overhead Allocation Factor is being used to calculate the overhead allocation, not the actual Overhead Expense number. This differential can be positive or negative, and is balanced in the year-end reconciliation.

If you would like to try the Job Day allocation model, it is ready to be downloaded below. Let me know if you have any difficulties, or would like to see other information. I'm interested in your feedback.

View the Overhead Allocation Model Based on Job-Days ---->

Download the
Overhead Allocation Model Based on

Return to ---->