Forecasting Your Workload and Finances, Part Two

In the first part of this article, we discussed a short-term projection of the studio workload. It showed four months of anticipated billings based on jobs currently active plus potential new jobs to which we applied a probability factor. Because it was developed from specific details, we referred to it as a “bottom up” projection. Now we’re ready to discuss a longer-term projection, meaning one that covers a year or more.

Long term

This type of forecast can be described as “top down” because it’s a forward projection of total financial activity based on your company’s past performance. It is not built up project by project. In accounting, this is called a pro forma projection, from the Latin meaning “according to form” or “as a matter of form.” This term is used to identify financial statements that have one or more assumptions or hypothetical conditions built into the data. (For example, our focus is on regular business operations, so we’re assuming there won’t be any extraordinary charges or expenses such as those related to a merger or acquisition.) Our projection will show the world on an “as if” basis—that is to say, its accuracy depends on whether the underlying assumptions hold true.

For creative firms, this long-term projection is primarily sales driven. Expenses are planned in relation to expected revenue. We’re taking last year’s actual numbers for monthly sales, expenses and net profits, and we’re projecting them forward in a conservative way, adjusting specific amounts as necessary to reflect anticipated changes in client relationships as well as any new assumptions we have about overall market conditions.

One of the big benefits of this extended time frame is that it allows us to analyze whether past activity has followed a seasonal or annual cycle. In many companies, history shows recurring fluctuations in the demand for particular services. For example, if you design annual reports or if you produce marketing materials for retail stores, you’ll see that certain months have always been much busier (or quieter) than others.

The first step in developing your long-term projection is to prepare a spreadsheet with actual income and expense data for the past 12 months. If you use a common spreadsheet application such as Excel, you might not have to format everything from scratch—many preformatted templates are available that have all the mathematical formulas already in place (one free online source is ExcelTemplates.net). As soon as you’ve entered your historical data, start a companion worksheet in the same format and plan out your expectations for the next 12 months.

The next step is to visualize your data by preparing two charts. Even if you tracked down a preformatted spreadsheet, you’ll probably want to create your own chart format. As a reference, Figure 3 shows a sample. As you can see, four separate lines are being plotted, showing monthly totals for sales, cost of sales, overhead, and net profits (or losses). Your goal is to have side-by-side charts that allow you to visually compare the recent past with the anticipated future.

Figure 3: A long-term chart of actual monthly totals from the P&L will help you recognize seasonal patterns and identify your break-even point. (In this example, the firm tends to lose money when monthly sales fall below $250K.) (Click on image to enlarge in new window)

I must add one quick note regarding the line that represents your monthly cost of sales: As stated in an earlier CPM article on financial management, payroll expenses should be split based on the actual billable/non-billable hours reported in each pay period. Billable labor is part of your cost of sales, while non-billable labor is part of overhead. This means that when your sales go down, the line for cost of sales will also go down if you’re doing a good job of matching project expenses to project billings within each period; but overhead will go up because of the increased non-billable time being reported.

For comparison purposes, you may want to chart your performance against industry benchmarks for cost of sales, overhead, and net profit. The best way to do this is to prepare three small supplemental charts, each with just two lines: one for your actual monthly activity (which will be jagged) and another for the industry average (which will be a straight horizontal line). Benchmarks vary from one creative discipline to another. You’ll have to do some research to find numbers relevant to your specialty. As an example, here are typical P&L percentages for design firms:

Sales

100%

Cost of sales

56%

Overhead expenses

33%

Net profit before incentives and taxes

11%

Chart variations

Some firms take a slightly different approach to long-term projections by calculating a “moving average.” In statistics, a moving average (also called a “rolling average” or “running average”) is used to smooth out brief fluctuations and highlight longer-term trends or cycles. Each new month’s numbers are added to the average, and the oldest month is dropped. This is how the average moves forward in time. In general, the shorter the time frame used, the more volatile the activity will appear. For our purposes, a three-month average works well. Figure 4 shows an example.

Figure 4: A three-month moving average smooths out monthly volatility and makes it easier to see overall trends. This is a good basis for making forward projections. (Click on image to enlarge in new window)

If you’ve calculated a moving average as the basis of your revenue trend, you might also want to add a “head count required” calculation, just as we did with our earlier short-term forecast. The moving average has given you a steadier base for head count planning than actual monthly sales numbers, because it shows fewer sudden changes.

Another variation for charting revenue is to split your sales history into layers representing your major client categories. This allows you to visualize which ones have been gradually expanding or contracting. Then you can project those layers forward to reflect what you know about industry conditions plus any changes you plan to make in your marketing focus.

Best guess

Ultimately, this long-term projection is your best guess of what will happen, based on the information available to you on the date the projection was prepared. As noted earlier, it’s also based on a series of underlying assumptions. Perhaps the most basic of these is that your company is a “going concern.” This is a term used by accountants; it refers to a company that is in solid financial shape and can continue operating for the foreseeable future. For this to happen, your company must have several important attributes:

An active base of regular clients

Employees who know the business and have strong working relationships with clients and suppliers

Equipment, furniture, fixtures and similar assets that are in good condition and still being depreciated (meaning that they are still within their expected useful life)

The ability to pay off any long-term debts within a reasonable time frame

If your design firm is indeed a going concern, then you have some momentum carrying you forward. A certain base level of activity can be predicted with some confidence. On top of this, you may also be planning for growth. If so, you have to decide how optimistic and how flexible your plan will be. One approach is to define a range for acceptable performance. This involves projecting a conservative trend line that is as realistic as you can make it, and then allowing for variations of plus or minus 5 percent, creating a range of acceptable performance that is 10 percentage points wide. One of the benefits of this approach is that it allows you to manage by exception—corrective action will be triggered if and when actual monthly performance strays outside of the allowed range.

Reaping the benefits

Both short-term and long-term forecasts take a lot of work to put together, but the process of preparing them will generate significant benefits for your creative firm:

You’ll keep a closer eye on current balances for active projects.

You’ll quantify all new business expectations.

You’ll link your firm’s staffing level more closely to the workload.

You’ll set specific targets for future financial activity.

You’ll track and respond to trends more quickly.

You’ll have fewer bad surprises.

Just remember that for this “distant early warning” system to be as effective as possible, you will need to update your forecasts with fresh data on a regular schedule.

About the Author:

Shel Perkins is a graphic designer, management consultant and educator with more than twenty years of experience in managing the operations of leading design firms in the U.S. and the U.K. He has served on the national boards of AIGA and the Association
of Professional Design Firms. He has been honored as an AIGA Fellow "in recognition of significant personal and professional contributions to raising the standards of excellence within the design community." The third edition of his best-selling book, Talent
Is Not Enough: Business Secrets For Designers, is available from New Riders.