Physical Quantities.  Models are much more helpful if they can relate back to a non-financial business metric.  Simply using units and price to drive your model can open up a world of insight.  If you are able to relate physical quantities, you will learn even more about the business you are analyzing.  Please open the free physical quantity example Excel download:  Physical.  This example is heavily truncated and concentrated (inputs, outputs, model, etc all on one sheet) to focus on the important points in building a physical quantity model.

  1. Drivers.  While many physical quantity models use units and price, it is often helpful to use financial expectations to determine physical quantities.  When managers or buyers have a particular growth rate desire or expectation, but the human and physical capital are unknown, a physically based model can determine the amount of investment needed, and whether the firm can handle it.  In a sales driven business (such as life insurance) where there is a lead time for training and trained sales people drive revenue generation, this approach will determine when to hire and how many people to hire to achieve targets (and the cost to do so while they are unproductive).  In service and manufacturing industries, this approach can determine the level of major assets needed (factory “lines”, rigs, etc.).  Explicitly tying into hard assets (human or plant) also helps avoid models that over-run capacity.  Many businesses are valued under assumptions that not possible given the physical attributes of the business.  This download focuses on determining the financial results and number of drilling rigs needed for a given revenue level.
  2. Sources of information.  Modeling is best done to reflect the business reality of a given situation.  The best assessment of business reality is taken from experts inside and outside of a company.  These experts think in terms of their operations, not financial metrics (feet drilled, not dollars spent).  A good physically based model will use these operational metrics directly to drive financial results.  At the top of the physical quantity example sheet in rows 7-21 are some examples of inputs that might come from operational experts.  Rows 15-18 use simple math to convert these inputs into financially meaningful results.  This may seem very simple, but it is crucial to talk to experts about what they know and understand.  Pricing and sales people may know sales cost based on well attributes, operational people may know how fast a rig drills, how long it takes to get on and off site, and how long a rig is typically down for maintenance.  Others (purchasing, executives, etc.) may know the cost of rigs and their order lead time.  Asking any one of these people “How much revenue will a rig generate in a year?” might result in an answer, but most likely that answer will be imprecise or historical (the last year’s results divided by the fleet).  Your results and your understanding of the future are much more robust when building up from the bottom and cross-checking historical results at the top level.  This technique also yields insights into the business and can drive probabilistic analysis.  This is true independent of the type of information to be modeled.
  3. Financial parameter inputs.  Rows 24-32.  At this point it is not obvious, but this model is driven by the financial growth rate.  In a sense it is “backwards” in that rig purchase decisions and the ability to sell the rigs is what really produces revenue.  However, model architecture will depend on the question to be answered and the ability to effectively operate or “drive” the model.  Guessing around at how many rigs are necessary and when, and when those rigs must be ordered is a difficult way to arrive at a financial result (which is usually the primary motivator and output from a model).  Building the model to be driven from rig fleet size could make sense in some situations, but would most often make the model difficult at best and unworkable at worst.  Other financial parameters could certainly be built up from physical metrics as well, but here simple percentages are used as secondary financial inputs since they are not a focus of this example.  The debt and working capital assumptions are retained from another model, but are also not germane here.
  4. Physical mechanics.  Rows 38-63.  Ordinarily, these calculations would be performed off sheet (or at least at the bottom of the financial statements). 

Revenue.  On row 63, the target revenue is calculated.  This is the revenue that drives the model and is determined by the target growth rate.  It is “target” because it must be fulfilled through physical assets.  On row 68, model revenue is the lesser of the target revenue or the max revenue generated by the drilling fleet (with the embedded assumption that rigs are delivered in the middle of the year).

Unit Count.  First, since revenue is a driver, the quantity of assets necessary for the given revenue is calculated in row 40.  This is simply the revenue divided by the revenue producing capability of the asset.  Next, this needed quantity is converted into number of rigs at the end of the following year (based on mid-year delivery, or the average of beginning and ending count) in row 42.  Now that the end of year quantity is known from the desired revenue, the number of additional assets can be determined on row 43.  Assets have to be added before they are needed because of lead time restrictions.  It is possible for the asset addition equation to result in a negative number, which does not make sense for this analysis.  An error trap is added to the formula on row 43 to avoid a negative result.  Now that the needed rig count is determined, the lead time can be taken into account to build an order book.  On rows 46-48, the lead time is taken into account in the order timing.  The lead time input is on row 13.  It is only valid for an input of 1,2, or 3.  The “need lag” in this model cannot be helped as it is a given that no orders can be placed in year zero.  The fleet count segment uses the CHOOSE function on row 52 to track the delivered fleet, which limits the revenue function.  Note that it is redundant (but for lag effects) to have revenue limited to a rig count which itself was determined from revenue.  However, when constructing this type of model, this sort of redundant or derivative calculation is necessary to prevent errors, especially with high complexity.  The revenue line in the income statement would immediately not agree with the target (once past the order lag period) were there an issue with under-counting rigs.  You could even create a purely rig-driven revenue line to ensure that revenue capacity was not over-bought.

Capital Expenditures.  This type of model typically has two main conclusions:   (i) how much to buy and (ii) whether it is affordable.  Capital expenditures are the main source of affordability issues (although implementation lag can also greatly affect cash flow).  Rows 57-59 calculate the capital expenditure amount necessary for each of the scenarios.  The formulas assume even payments from order to delivery for the construction of the rig (for example, with a one year lead time, the ½ is paid when the order is placed (year 0) and ½ when delivered (year 1).  A three year lead time is paid over 4 payments (order, progress 1 & 2, and then delivery in year 3)).  While all of this could be packed in to one line, including the selection of the correct capex calculation on Row 61, the formula would have been impenetrable and prone to error.  The result on row 61 is then fed into the model capital expenditure on the cash flow statement.  This model, as it is focused on physical inputs, does not contain significant credit analysis information.  However, since the lead time spending and cost of the asset is fed back through the model, one can apply credit analysis techniques and valuation techniques to evaluate the ability to expand and value of expanding vs. the base business.