Tag Archives: Cost Accounting

1981-1988 TSI: GrandAd: System Structure

Posted on by
4

The design of TSI’s ad agency system. Continue reading →

This entry is rather wonkish. There are no funny stories. I just wanted to record the details while I could still remember them.

I gave our system the name “GrandAd”, but I doubt that anyone ever called it that. We were lucky if the users called it “TSI” rather than “Mike’s system” or “Sue’s system”. The original programs were designed to be run on a Datamaster with an external hard drive (described here). A few years later the system was converted to run on a System/36 (described here). We also converted it to run on an AS/400 (described here).

In the early eighties, administrative software systems generally fell into two categories: BICARSA (Billing, Inventory Control, Accounts Receivable, Sales Analysis) and GLAPPR (General Ledger, Accounts Payable, Payroll). Accounts Receivable was abbreviated as A/R. The GLAPPR modules were abbreviated as G/L, A/P, and P/R)..

GrandAd contained versions of all of these modules except payroll. I don’t remember even one of our agency clients using TSI’s payroll system. From an accounting perspective the distinctive features were that the GrandAd system had two separate methods of billing—media and other—that fed a rather standard accounts receivable system and a unique “media liability” module. The accounts payable system likewise had two separate sets of data entry programs, one for media vendors and one for others. If a bill from a media vendor included production charges, it could be entered in the media payables program after the media portion was completed.

None of the operating systems used by our customers distinguished between tables and data files, but our documentation usually did. In our parlance a table was maintainable by the users through simple editing. Data files could only be maintained through transactions that always created auditable detail records. Transactions were also usually entered in batches that updated the data files all at once. Changes made to items in tables took place immediately. Some important tables contained a few fields that could not be edited by the users. For example the G/L master file contained thirteen fields for the amounts for each month and adjustments. These fields could only be updated by transactions.

Every table had a unique key. The open item files for A/R, A/P, and media liability also had unique keys, as did the file with job costs by category and the media detail table. Detail files for transactions either had no key or keys that were not unique.

There was also one record-oriented specs table that TSI did not tell users about. We maintained this ourselves. It contained things like the agency’s name and address and a large number of “switches”, mostly binary Y/N designations that indicated whether the agency used optional features or the manner in which it used them.

Here were the principal tables, at least as I remember them:

  • Client: The key was a three-digit number. There was a field on this table for an “associated” client number to handle those cases where one company owned several semi-independent entities. Client #10 was usually used for in-house jobs and #11 for one-shot projects.
  • Job Type: The key was a two-digit number.
  • Job: The key was the client number plus a three digit-job number. The numbers could be assigned by the system. A few agencies had their own job numbering system. We kept this number as a twenty-character reference number. In general, 90000 was used for media billings. Every job was assigned a job type.
  • Job Cost Categories: The cost categories also had three digit keys. The categories were of three principal types: time, agency-owned materials, and vendor costs. The entries in the time categories consisted of hours worked on a job. The entries in the materials categories were dollar amounts. The vendor costs came from the accounts payable system.
  • Employee: The key was a three-character code. Initials were usually used here.
  • Rate: The key consisted of the employee code and the category number. It might also have had a date to allow rates to keep up with inflation.
  • Vendor: Vendors were keyed by a five character code and a two-digit location number. Checks were always cut to the name and address on location #0.
  • Media Type: A two digit code identified types of media—radio, television, magazine, newspaper, mailing house, yellow pages, etc.
  • Pub: Like the vendors, pubs were identified by a five character code and a two-digit number (usually 0). Every pub was associated with one vendor and had one media type. A vendor could have any number of pubs associated with it. Although the name “pub” was derived from “publication”, this table was also used for other media entities.
  • Media Ad: The key was the client number, a five-digit ad number, and a one-character version code (usually blank). The ad number was usually the production job number, but it could be something else.
  • Media Schedule: The key was the media ad key plus the pub key plus the date in the form YYMMDD and a two-digit number to preserve uniqueness. The last field was necessary because the same ad could be run more than once on the same day in the same publication or station. Three costs were stored on each record: net (the amount the agency paid the vendor), gross (net plus agency commission), and charge (the amount the client would pay).

The system provided for the following types of transactions:

Transactions were ordinarily entered in batch mode.
  • Menus specific to each type of transaction allowed for recording of new items, editing or (at least deleting) of items entered but not updated, printouts of the contents of the batch, and updating of data files.
    • Production and fee billing. Invoices could be printed on special multi-part forms or just recorded. The update program created records on the A/R detail file. Summary records on the job file were updated and entries were created in the batch file for the G/L system.
    • Media billing: Detail was selected from the media schedule. The update program created records on the A/R detail file, media liability records, and G/L batch entries. Summary records on the job file were updated.
    • Media payables: The update filled in the amount relieved on media liability records and created A/P Detail records and G/L batch entries. YTD spending on the vendor table was updated.
    • Other payables: The update created G/L batch entries. YTD spending on the vendor table was updated. For production costs job cost detail records were written and the job-to-date costs on the job file and job cost summary file were updated.
    • Checks: Invoices from vendors could be selected for payment in various ways. The update program wrote A/P detail records and entries for the G/L batch.
    • General ledger: Entries were generated by other modules, but they could also be entered one at a time. The update program wrote out journal entries and updated the monthly totals by account.
  • Live entries:
    • Cash receipts: The program to record cash received from clients updated paid amounts on open A/R items, and cash received month-to-date and year-to-date on the client table. It also wrote out A/R detail records and batch entries for the general ledger.
    • Individual disbursements: This program was used for partial payments, write-offs, and other A/P issues that were difficult to handle. It could also print checks, but that feature was seldom used by agencies.

The best thing about AdDept and also the worst thing was the month end reconciliation process. That is, it was the invaluable process that underscored the reliability of the data, but its vigor was still dreaded at the end of each month.

We provided the customers with checklists for what needed to be in agreement with what at the end of the month. For example, the total for the list of items in A/P must be the same as the balance in the A/P account in the G/L. If there were discrepancies, I showed the users the reporting tools to use to find the discrepancy and how to fix it. This could easily consume an entire day, and I often had to help them for two or three months before they understood what was causing the errors and how to fix them.

An account at one installation showed a discrepancy of only ten cents that we discovered was the result of three different errors, each of which was for more than $1,000.

I mostly dealt with the manager of the business office. However, I almost always sat down with one of the principals to go over the cost accounting report by client at least once. This report had columns for each source of income (media commissions, fees, billings for production jobs) and both direct and allocated expenses. A primary purpose of the reconciliation process was to insure that the total profit by client was the same as the total profit on the general ledger1. I tried to make it clear that the allocation of indirect expenses was only as accurate as the timesheets. If some employees (media buyers, for example) were not reporting their time accurately, the accuracy would suffer.

I have no doubt that our competitors did a lot less for their customers. Some tried to support their clients without any on-site visits. Some partnered with locals to provide hand-holding. I feel sure that they must have had some unhappy customers.

We actually used parts of this same process in TSI’s office. An unintended benefit of this rigorous approach was that it was rather easy to catch embezzlers.

The system was not sexy. The screens were green, and the output was columnar. The first few months were frustrating and difficult for almost all users. However, in the end it always saved a lot of time and produced valuable information.

One of our agency clients told us that they used a PC-based system called Media Management Plus2 to purchase broadcast commercials from radio and television stations and to evaluate the performance of the spots. We contacted Glenn DeKraker at the company in New Jersey. He told us that the software had several forms of files that it could produce to feed billing and accounting systems. We chose one of them, and wrote software on the System/36 (it would have been very difficult to do it on a Datamaster) to create records on the ads file in GrandAd’s media system from the records uploaded from the PC.

It worked smoothly from the beginning. We did not need to make any adjustments to our files at all. The users just needed to follow a simple convention when it came to naming the pubs. They had to use the call letters and a one-character code: A for AM, F for FM, and T for television.

This project taught us not to fear building interfaces with other software companies.

The original design of GrandAd was surprisingly stable, especially when the constraints under which we operated are considered. The screens supported nothing but text and had only 78 usable columns and 24 rows. The reports were limited to 132 characters on each line. The amount that could be stored on the hard drives was quite limited. If a customer ran out of room, the options were poor.

It was, thank goodness, never necessary to increase the size of any fields, although we had to pass on requests for proposals from two agencies. One was a New York agency that specialized in theatrical productions, each of which was a separate client, and a separate agency that specialized in want ads. Our biggest problem was in handling the version of the system that was used by TSI. When we started selling the AxN system to newspapers, The 999 clients allowed by the three-digit client number might not be sufficient. Besides, we did not want to intermingle the newspapers with our other clients. We decided to allow alphabetic characters in the client number field.

In retrospect we obviously should have used eight-digit dates from the beginning. We devoted countless hours in the late nineties to fixing this oversight.

1. Occasionally some G/L accounts, such as investment income, were excluded for this purpose.

2. In 2021 the company is now known as CoreMedia Systems. Its website is here.

1981-1988 TSI: Working with Ad Agencies

Posted on by
3

What I learned from working with advertising agencies. Continue reading →

When Sue and I first met with the people from Harland-Tine in 1981, everything that I knew about advertising agencies came from watching Bewitched when I was in high school. I soon learned1 that most medium-sized ad agencies included people with the following types of jobs:

  • Account executives: These were the people who meet with the clients. Their primary function was to keep the clients happy, either by explaining what the agency was accomplishing or by keeping them in the dark. Some were also responsible for recruiting new clients.
  • Creative: Writers and artists usually worked in teams of two or three to come up with the campaigns and the ads used in them.
  • Production: These people actually executed the ideas.
  • Media: These employees communicated with the stations, networks, publications, etc. to schedule the advertising. They also made sure that the media vendor provided documentation that the ads were run as scheduled. In broadcast this was done via affidavits. Print media supplied tearsheets.
  • Administrative: Billing and other functions were done by the business office. Sometimes there were other areas as well, such as building maintenance and parties. Ad agencies liked parties.

Ad agencies preferred to measure themselves by their “billings”, a more than slightly deceptive term. An agency that describes itself as having $10 million in annual billings did not arrive at that figure by adding up the figures on the invoices that they sent to clients. The reason for this is that the broadcast entities and most magazines paid, at least in theory, ad agencies a 15 percent commission on everything that they bought. So, if the agency had sent invoices to the client for $10 million of media that it had billed, $8.5 million of it probably was paid to the media. Still, the agency billed it all, right?

Some vendors, notably most newspapers, did not give agencies commissions. Some agencies therefore marked up these charges by 17.65 percent to make them equivalent to other media. Regardless of what they actually billed, the reported billings would include the markup, not on their tax forms, but for promotional purposes.

Luis Delgado, who played Officer Billings on The Rockford Files, was also James Garner’s best friend.

In order to put agencies that that concentrated on other aspects of the business on the same footing as ones that billed a lot of media, it was common practice for agencies to use the same or similar ratios to calculate the “billings” that they reported. If a client paid a retainer, for example, of $1.5 million per year, that also was treated as $10 million in “billings”.

Further adjustments were also made, sometimes for questionable reasons. If they often used freelancers or other vendors on projects, should their costs be deducted before doing the calculation? The one overarching principle was that no one ever adjusted billings down. I once walked into an advertising agency that claimed “$50 million” in annual billings. I counted only six desks there. .

When I wanted to know how big an ad agency was, I asked them how many employees worked there. That was a better proxy variable for the amount of work that went through the agency than its billings.

Not every agency was a “full-service” agency. Some of them used a service to purchase media. On the other hand an agency in Illinois that we talked with did almost nothing but place newspaper ads for Sears. Another ad agency that used our system mostly produced point-of-sale signage for the beef industry. One “agency” in the Hartford area actually tried to talk its clients out of advertising. Instead, it promoted the idea of press releases. The agency would write them, and then try to persuade various media to run them verbatim or at least cover them as news items.

The most important equipment for an ad agency in the eighties.

An agency could be as small as one or two people. The two essential items are a telephone and a good contact list, which in my day was likely kept on a rolladex.

Some international agencies were enormous, but many agencies of any size were startlingly ephemeral. I found that most ad agencies were no more than two phone calls away from oblivion. That is, very few of them could afford to lose their two biggest clients. In the big Madison Avenue agencies when an important account changed agencies, the creative team and sometimes others involved in the account might just move to the new agency. Any agency of any size that lost one of its biggest accounts would most likely cut staff and other expenses to compensate for the loss of revenue.

The proprietors of medium-sized agencies understood this. Most had at some point experienced it first-hand. In order to maximize their chances of surviving the dreaded phone call, they tried to minimize fixed costs. They would rather rent than own. Something that could not easily be used to wow a client or a potential client seldom made much of an impression on them. The usual argument that automation improved efficiency generally fell on deaf ears.

When I did pitches to clients, I emphasized billing. If the agency was not prebilling its media—billing from the schedule rather than from invoices from the vendor—I emphasized how easy it was to do this with our system. I argued that in those inflationary times they should be making 1 percent per month on their money. So, billing two months later was like forfeiting two percentage points of their commission!

I also explained how they could easily customize the invoices for other types of jobs. Each client’s could look different. It wasn’t exactly sexy, but at least it was client-oriented. I knew well that very few agency heads were looking to make life more pleasant for their bookkeeper.

The other thing that I focused on was the profitability of client agreements. Most agency owners with whom we dealt had only a vague idea as to which clients are profitable. Few of them were good businessmen. We could show them on the system’s cost accounting reports how to analyze the profitability of clients for the month and year-to-date, and, if they found a problem, they could probe more deeply with other reports.

I think that our system served our agency clients well. I know for a fact that it helped a few of them through some difficult times.

A few aspects of the way that we handled accounting for advertising agencies were somewhat unique. These features were not really marketable unless the agency’s accountant was involved in the purchase.

I never read this book either.
  • We developed separate systems for media billing and accounts payable. They did not use the same programs used for other billing and vendor invoices. Both the billing and the A/P systems matched up the invoices with the items from the media scheduling system.
  • Non-media jobs that were billed in advance were credited to an advance bills account. The entries taking these amounts as income had to be made individually.
  • Every agency that did any kind of production or creative work kept an asset account called billable work-in-process (WIP). The system printed a list of the WIP for every active job at the end of the month. The change in WIP from month to month was booked as income.2

These methods were my own invention. I never took an accounting class in college and, for that matter only one programming. Never, however, in my thirty-five years of setting up computerized accounting systems for people did anyone challenge my credentials.

Spare me the consultants!

Some agencies recognized that they needed to computerize the administration of the agency, but they were not competent to figure out how to do it. So, they hired consultants. I absolutely hated dealing with consultants. They would begin the research process with a set of standards that they had developed, not from dealing with ad agencies or even by listening to what the agency in question hoped to accomplish, but from their experience with other companies that were unlikely to be comparable to ad agencies.

Sometimes they would send out a Request for Proposal (RFP) that outlined the requirements that they intended to impose. Often I could tell that TSI would be eliminated immediately for one reason or another. Almost never did the RFP ask if the system could be customized, or if it generated any of the billing or client profitability advantages outlined above.

Only one of our installations was produced through a consulting firm, and it was a disaster for us (wasted time), the agency (wasted a lot of money), and the consultant (law suit), That experience is described here.

The experience with advertising agencies broadened my horizons. I learned enough about the details of advertising that when the opportunity arose, I was able to make a successful pitch to Macy’s advertising department. That story is told here.

I also amassed enough details about the mindset of an advertising executive that I wrote a prize-winning short story about one. That account is here.

I never got interested in advertising, but in order to market to the New England agencies I had to follow their ups and downs. Seeing a few dramatic crashes reinforced my desire not to depend too much on any one client. That philosophy helped to sustain us through the nightmarish year of 1991, when our two largest clients both declared bankruptcy.

1. The Internet drastically affected advertising agencies. Much of what I learned is probably obsolete in 2021.

2. The American Association of Advertising Agencies (AAAA, pronounced “four A’s”) recommended a different method that was more cumbersome and less useful. Every accountant who ever saw what we did thought that it was equivalent but provided more useful audit trails, took much less time, and was less susceptible to errors.