Forum Discussion
Forecast Revenue based on historic cost data
We perform approximately 10,000 service orders in a year. Each service order has detailed cost categories (labor hours, labor cost, burden, material cost, equipment cost, per diem cost, subcontractor cost). Since we utilize a percentage of completion method to recognize revenue we have to determine the accrued revenue at the end of each quarter on open and unbilled service orders.
My question is if there is a formula that can analyze the historic costs vs actual final billing on completed service orders and forecast unbilled service order revenue based on the composition of the costs. As an example, if a service order has a high material cost in relation to labor cost the final billing and subsequent margin percentage, (final billing - final cost) / final billing, is smaller than a service order has only labor.
Currently, we breakdown historic cost and billing on completed service orders by project manager, customer, etc. to determine a historic margin percentage on completed service orders. Then, using that historic margin we forecast estimated final billing on open service orders: current cost / (1-margin). While that has worked in the past, I would like to refine the process with a more detailed forecast method.
Thank you in advance for ideas.