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.
2 Replies
Try considering the following:
- Cost Composition Analysis
Develop a model that analyzes the composition of costs (labor, material, equipment, etc.) for each service order. You can use historical data to determine how different cost compositions affect the final billing and margin. This can be done using regression analysis or machine learning techniques.
- Segmented Margin Analysis
Instead of using a single historic margin percentage, segment your service orders based on cost composition. For example:
- High Material Cost Orders: Calculate a specific margin for orders with high material costs.
- High Labor Cost Orders: Calculate a different margin for orders with high labor costs.
- Weighted Average Margin
Calculate a weighted average margin based on the proportion of each cost category. This can provide a more nuanced forecast by considering the relative impact of each cost type on the final billing.
- Predictive Modeling
Implement predictive modeling techniques such as:
- Linear Regression: To predict final billing based on the composition of costs.
- Random Forest: To handle non-linear relationships and interactions between different cost categories.
- Neural Networks: For more complex patterns in your data.
- Dynamic Adjustment Factors
Introduce dynamic adjustment factors that modify the historic margin based on current trends or changes in cost structures. For example, if material costs have recently increased, adjust the margin accordingly.
Example Formula
Here's a refined formula incorporating cost composition:
Where:
- Weighted Marginis calculated based on the proportion of each cost category and their respective historic margins.
Kidd_Ip,
thank you for the insight. I received the similar answer from ChatGPT when I put my request in to see what it would advise. To provide more clarification, I am looking for an Excel formula or multiple formulas to allow me to make the forecasts. Thank you,
