Certain industries need the ability to look at a backlog of work, and project that backlog into the future for planning purposes. The classic example is a large services organization with multi-month or multiyear contracts representing a large sum of future dollars to be earned and/or hours to be worked. Construction companies, law firms and other organizations with long term projects or commitments have similar requirements. Manufacturers that ship against standing blanket orders may also find this technique helpful.
Backlog planning requirements come in several flavors supporting different areas of the organization. Finance needs to understand future cash flow in terms of expenditures and cash receipts, and properly project both invoiced and recognized revenue for management planning and expectation setting. There are operational requirements to understand the flow of work for manpower, resource management and capacity planning purposes. And the sales organization will want to understand how the backlog will ultimately flow to understand future attainment measures.
Dimensional schemas can be populated when a new contract is signed, capturing the initial acquisition or creation of the contract and thus the new backlog opportunity. In addition, another schema can be created that captures the work delivered against the contract over time. These two schemas are interesting and useful, but by themselves are not enough to support the future planning requirements. They show that the organization has “N” number of contracts worth “X” millions of dollars with “Y” millions of dollars having been delivered. From these two schemas, the current backlog can be identified by subtracting the delivered amount from the contracted amount. Often it is worthwhile to populate the backlog values in another schema as the rules required to determine the remaining backlog may be relatively complex. Once the backlog amount is understood, it then needs to be accurately projected into the future based on appropriate business rules.
The use of another schema we call the “spread” fact table is helpful in supporting the planning requirements. The spread fact table is created from the backlog schema discussed above. The backlog and remaining time on the contract are evaluated and the backlog is then spread out into the appropriate future planning time buckets and rows are inserted into the fact table. For this discussion we’ll assume monthly time periods, but it could just as easily be daily, weekly or quarterly. Thus the grain of our spread fact table will be at the month by contract (whatever is the lowest level used in the planning process). This schema will also include other appropriate conformed dimensions such as customer, product, sales person, and project manager. In our example, the interesting metrics might include the number of hours to be worked, as well as the amount of the contract value to be delivered in each future month.
In addition, we include another dimension called the scenario dimension. The scenario dimension describes the planning scenario or version of the spread fact table’s rows. This may be a value such as “2009 October Financial Plan” or “2009 October Operational Plan.” Thus, if we plan monthly, there will be new rows inserted into the spread fact table each month described by a new row in the scenario dimension. The secret sauce of the spread fact table is the business rules used to break down the backlog value into the future spread time buckets. Depending on the sophistication and maturity of the planning process, these business rules may simply spread the backlog into equal buckets based on the remaining months in the contract. In other organizations, more complex rules may be utilized that evaluate a detailed staffing and work plan incorporating seasonality trends using a complex algorithm to calculate a very precise amount for each future time period in the spread fact table.
By creatively using the scenario dimension, it is possible to populate several spreads each planning period based on different business rules to support different planning assumptions. As indicated in the scenario descriptions above, it may be possible that the financial planning algorithms are different than the operational planning algorithms for a variety of reasons.
The spread fact table is not just useful for understanding the backlog of actual work. Similar planning requirements often surface with other business processes. Another example is planning for sales opportunities that are proposed but have not yet been signed. Assuming the organization has an appropriate source for the future sales opportunities, this would be another good fit for a spread fact table. Again, appropriate business rules need to be identified to evaluate a future opportunity and determine how to spread the proposed contract amounts into the appropriate future periods. This schema can also include indicators that describe the likelihood of winning the opportunity, such as forecast indicators and percent likely to close attributes. These additional attributes will enable the planning process to look at best case/worst case future scenarios. Typically, the sales opportunities spread fact table will need to be populated as a separate fact table than the actual backlog spread as the dimensionality between the two fact tables is typically quite different. A simple drill across query will enable the planning process to align the solid backlog along with the softer projected sales opportunities to paint a more complete picture of what the future may hold for the organization.