Corp. Buyer's Spreadsheet Analyzes Agency Fees
<B> Corp. Buyer's Spreadsheet Analyzes Agency Fees</B>
By Sarah Welt
<I>New York</I> - In a move to simplify the fee, commission and rebate analysis process, Sony Music Entertainment Inc. in the past couple of months has created a spreadsheet to explain transaction fees. The document is the brainchild of Howard Brooks, Sony's senior director of expense management and travel administration.
Brooks said the concept behind the spreadsheet is to make sure that the six travel agencies the company uses remain "whole" and that "whatever the airlines do to the commission structure in the future, their revenue stays constant." Brooks went through the analysis process with each individual travel agency.
The spreadsheet is broken into three different variables--transactions for domestic tickets, for international and for refunds because, "as opposed to doing 15 different transactions we said to travel agencies we only want to deal with these three," Brooks said, adding that the company doesn't want to pay for inquiries. While Sony is testing an automated booking product, this spreadsheet focuses only on negotiating with travel agencies using the standard phone scenario.
On the sample spreadsheet (see Transaction Fee Spreadsheet), variables are in italics and sample numbers are used throughout. The first assumption in the first section, entitled Transaction Fee Agency Analysis, focuses on full-year transaction volume. To get that figure, Brooks took the number of air transactions from his corporate card report by that travel agency--in this case it equates to 10,000 transactions with a total air spend of $5.25 million. Both represent the amount of work processed by that agency.
The second part of the first assumption divides the mix of international vs. domestic transactions--in this case 80 percent domestic, 20 percent international.
The second section of the spreadsheet is entitled Commission and Rebate Agency Analysis. The first assumption focuses on what the travel agency made in 1997 based on a rebate scenario.
Assumption two inputs variables for the percentage of transactions ticketed at the maximum of $50, at the minimum of $25 and at the midpoint of $38 dollars. Assumption three works on the same principle for international tickets.
The bottom of the chart gives an estimation of the agency's commission revenue as well as the corporate rebate earned, including a calculation for overrides.
The next part of the spreadsheet deals with transaction fee impact, projecting the net amount due the corporation from its agency. Said Brooks, "You need to come up with a comfortable refund fee for domestic and international tickets," so that by the end of the spreadsheet roughly 100 percent goes back to both the corporation and the agency. Brooks noted that any time a variable changes, it will impact any outcome.
Brooks created the spreadsheet because with agencies following the commission cuts, there has been "no change in salaries, only change in commission and we want to keep them whole so they've got the same amount of revenue and we adjust it on the back office end," he said, adding the document is for senior management to "justify where the numbers are coming from. They can see what they are making, what we are making, and what we are committing to.