Sales Analytics Can Aid Cash Flow Forecasts

A comment about my recent bad debt column helped convince me to tackle cash forecasting and the role of DSO — days sales outstanding. While categorization of past-due accounts can affect reported income and impact bonuses, it’s cash flow that determines whether those bonus checks can be spent.

Think of cash flow in terms of your water heater. Payments, like cold water, come in at the top. Expenditures, like heated water, flow out the bottom. When client payments don’t come in as expected (or your teenage son takes an extra-long shower), there’s a problem.

Getting a handle on DSO is particularly important for media businesses. As the bad debt column commenter noted, payments from large advertisers can be received more than four months after the billing period. That can affect everything from sales commissions to how much money the company needs to borrow to meet its obligations.

Understanding DSO’s Role

It’s important to note that the timing of the payment typically doesn’t impact revenue recognition; it just affects access to the cash itself. A company may find itself tapping a line of credit to pay its expenses, say employee salaries, even though its revenue for the period is significantly greater than the same period’s expenses. That increases the company’s cost of doing business and is one of the reasons cash flow projections are so critical to media businesses.

What’s the best way to forecast future cash receipts? To answer that, we turned to Scott Jenkins, senior manager of collections at Disney Worldwide Services and a member of the MFM board. Jenkins, who’s worked for ESPN and The Walt Disney Company for 17 years, says it’s a matter of predicting how much billed revenue will be collected and, more specifically, how to improve collection. To do that, he turns to DSO, which measures the number of days between the sale and collection of the payment for that sale.  

Using the water heater analogy, one way to improve the amount of hot water in the tank is to slow down its usage. Using consolidated buying power to negotiate more favorable buying terms (more time to pay) is like using a water-saving shower head – everyone knows it’s there and accepts it.

Reducing DSO Averages

But, what about discrepancies? I think of them as those hard water rocks that accumulate in the faucet aerator or the shower head. Regardless of their source, they block the flow to a trickle until they are removed.

Jenkins outlines several ways to ensure an invoice is free of discrepancies when it is sent to the media buyer. The first involves making certain the insertion order accurately reflects what the customer ordered.

The next challenge is to ensure that the advertising campaign ran as originally proposed. He says: “After the campaign is over, ensure that your accounting information is corrected immediately by checking for variances with your agency contacts and your sales teams.”

Also look at the invoice delivery itself. Jenkins comments that an invoice delivery process that is “nearly transparent,” can “decrease your DSO by days.”

The Impact Of Seasonality

Market trends need to be considered when predicting future cash flows. Jenkins uses the term “seasonality” to describe these.

For example, the fall and winter typically generate more revenue with their new programming seasons, the National Football League and college football games.

Other examples of seasonality include the increased media buying in the fourth quarter that coincides with holiday shopping and the increases in political ad spending that occur during national election years.

Historical Analysis

One of the ways to accommodate seasonal fluctuations in ad revenue forecasts is to calculate DSO over a longer period of time. Jenkins recommends gathering the prior three years of agings.

Payments can vary by media type Jenkins points out that television is typically paid within 30-45 days; cable television and digital are paid in 60-90 days; and radio and print media are generally at 90-120 days. For this reason, he also suggests categorizing these agings by your station or company’s media streams, such as broadcast TV and digital. Disney will have as many as six different aging trend analyses encompassing broadcast TV, cable TV, digital, international, radio and print.

Jenkins creates a table for each media type. It includes a beginning aging, which is usually the first month of the quarter, and an ending aging (the third month of the quarter). The table is used to calculate the percentages collected from the beginning of the quarter to the end of the quarter. From there, he runs the calculation over a span of three years, taking the average percentage to come up with an average trend that accounts for seasonal factors.

Forward-Looking Agings

Once the average collection percentage per media type has been established, it can be used to predict future collections for the upcoming quarter. For example, calculating from the December aging out to the end of March, will give you a 90+ day amount for the December aging.

The next step involves applying the three-year DSO averages to the next three months of forecasted revenue. Jenkins notes: “Taking these four buckets (months of December to March) and multiplying them by your three-year average collection percentage per bucket, you will have estimated not only the amount that you will collect for the quarter, but you will have also estimated what your ending aging balances will be.”

The methodology used to estimate the aging buckets can also be used to forecast month-over-month revenues. “As you go through your estimated agings over the quarter and lay them out accordingly within your DSO calculation template, you will see that you have the ability to predict your quarter-end DSO.”

Of course, actual collection data will vary from the amounts estimated in the DSO forecast. Jenkins recommends “re-forecasting on the fly and implementing the right course of action.”

Since I have summarized the formulas he used to conduct these analyses and have not included the accompanying sample charts which make everything much clearer, I encourage you to read his entire article, entitled “The Power of DSO.” It appears in the March-April issue of MFM’s member magazine TFM – The Financial Manager; a digital copy of the magazine will be available on our website for a limited time.

DSO can vary among station groups or even locations. This is one of the reasons MFM calculates quarterly television and radio station DSO reports. They contain data from most of the industry’s groups, are separated by market size and are only available (in aggregate) to other report participants.

DSO data pertaining to individual advertisers may also be gleaned from BCCA’s Media Whys credit reports, which provide a credit score based on media industry-specific aging data as well as trade data from Experian or D+B.

As Jenkins observes in his article, forecasting cash flow is a lot like forecasting the weather — it isn’t always very precise, particularly when it’s done weeks or months in advance. But if you keep your analysis fresh, you’re far better prepared to weather a storm and avoid an unexpected cold shower.

Mary M. Collins is president and CEO of the Media Financial Management Association and its BCCA subsidiary. She can be reached at [email protected]. Her column appears in TVNewsCheck every other week. You can read her earlier columns here.