Business Tools Blog

Weighted Average adds a volume dimension to a plain average calc.  Each quantity being averaged is assigned a weight.

Example: What if we were trying to determine the average install interval (the time between when a product is sold and when it is installed)?

We could use the average (sum up all of the days shown below in column B and divide by 20). This calculation would = 43 day average install interval.

But what if we were going to use the install interval to forecast recurring revenue?  We would need to know the weighted average install interval.  (i.e. the larger the revenue, the higher the weight.)

We could do this manually:  [(180*1,000,000)+(75*900,000)+(80*810,000)+ … +(6*135,085)]/8,784,233 = 63 days.

Or, use Excel’s sumproduct function.  Sumproduct multiplies 2 columns of data by each other, in the same way that the manual formula above multiplies columns B and C.  How to:

1. SUMPRODUCT(B2:B21,C2:C21) multiplies B2*C2+B3*C3+B4*C4 +…+B21*C21
2. Divide SUMPRODUCT(B2:B21,C2:C21) by the sum of the Revenue 8,784,233
3. The total is a 63 day weighted average

Why use Weighted Average instead of the Average Days to Install?

The average days to install metric to use in a revenue forecast would be the weighted average of 63 days to install service.  20 days might not seem like a big deal, but if you use the rule of 78’s the compounding effect is huge.

The example below shows how $10K/mo. in sales on the first day of each month converts to revenue if the install interval is 43 days.  e.g. a sale on Jan 1st would be installed on Feb 12th.  The revenue for Feb would be for 17 days or 59% of the month.   The annual revenue in this example is $614,701.

The chart below shows that a 63 days install interval would result in $543,140 in annual revenue.

With just $10K per month in sales, the annual difference in revenue is $71,516.  If you were selling $100K per month the annual difference in the forecast would be $710,516.

Understanding the weighted average install interval is extremely important if you want to forecast recurring revenue accurately.

The above examples also show why getting installs done just a few days faster can yield a lot more revenue.  With Zayo’s bonus and commission plans, more revenue = more bonus and more commission!