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 |
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.
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.
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.
The first Sample document displays the first quarter of activity.
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". 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.
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 |