How would you like to update an entire year’s worth of catalog response in a matter of minutes? It’s easier than you think. The beauty of the system described here is its future benefits: Set up your sheet once, and it becomes the framework for your next mailing analysis.
The basics. To begin, you need nine pieces of data:
· Key code list with English word descriptions of each list selection.
· Quantity mailed per key code.
· Cost per piece in the mail (postage and printing).
· Number of orders per key code.
· Gross sales per key code.
· Cost of goods sold per key code.
· Number of returns per key code.
· Gross dollars of returns.
· Cost of goods of returns.
First of all, let’s look at what you prepare. This spreadsheet will have the key codes, the English word description of the list select, quantity mailed per key code and the promotion cost per segment. This spreadsheet is the basis for your contribution sheet.
It will look something like Figure A. (Note: If you set your sheets to duplicate these examples, you will have an easier time applying these steps.)
In your raw data, you will have the remaining pieces of information. This data sheet varies from company to company, but it should look something like Figure B. You’re now ready to build your contribution sheet.
Getting started. In Excel, open both your contribution and your raw data spreadsheets. Prepare your raw data spreadsheet first. Having the exact order of the columns is not important. However, you must have the key codes in the far left column.
The second step is to format your raw data. This is a simple step that often must be done on raw data to make it compatible with Excel.
Insert a column to the right of your key codes. In this column, next to the first key code, type, <=value( > , click on the key code in the cell to your immediate left, close the formula with a < ) > and hit enter. (For this article < and > bracket in what you are to type in a cell. Don’t include < and > in the formula.)
Copy this formula down the new column for every key code. Next, copy this entire column and paste its “values” over your original list of key codes. Once you accomplish this, you can delete the column that you inserted and all of the “value” formulae in it.
In the same spreadsheet, highlight the data and sort it in ascending order by key code. This is a critical step. Your formulae won’t work without it.
For your next step, along the top of your raw data, just above your column headers, insert a row, and number each column sequentially beginning with your key codes as 1. Your column to the immediate right of the key codes is 2; the next one is 3 and so on. Save this sheet, but keep it open. Your raw data is now ready for processing.
The contribution sheet. Your next step is to click on your contribution sheet and write your lookup formulae.
In column A, you should have your key codes. In column B, you should have your English word description of each key code’s list selection. In column C, list the quantity mailed per key code. In column D, multiply your cost per piece by the quantity mailed per key code. This is your total promotion cost per key code.
In addition to the column headings in Figure A add: Number of Orders, Gross Sales, Cost of Goods Sold, Number of Returns and Gross Dollars of Returns.
In your contribution sheet’s column E, which will be your number of orders, you will write your first lookup formula. In the appropriate column and cell where you want the number to appear, type < =vlookup( > .
Next, take your cursor and click on the key code cell in the same row. Notice that the cell reference automatically appears in the formula. Now type a comma. Your formula will read < =vlookup(A2,>.
After you type your comma, click on your raw data sheet, which should still be open. Now click on the cell that holds the first key code in the upper left of the spreadsheet. While holding down your cursor, highlight the entire table of data, pulling to the lower right corner. It is important to include all of the data, but do not include the column headings. After you’ve highlighted the data, type a comma.
Now look at the top row of numbers that you inserted along the top of your raw data. If you are pulling in your number of orders, type the number that appears above the number-of-orders column heading. Close your formula with a < ) > and hit enter.
The number of orders associated with that key code now appears in the cell. You have just written your first “lookup” formula. For a quick quality control check, find the same key code in your raw data and verify that you pulled in the correct number.
For each cell in your first row, repeat this process. The formula essentially will be the same for every cell, except for the number that you type at the end of the formula. That number will change according to the number above each column heading in your raw data.
After you have keyed all of your formulae for the first row, highlight the cells with the formulae, and copy and paste them into the cells and rows beneath your first row. Since you already have your key codes in column A, the data will automatically populate these cells.
The next time you get an update of response for your raw data, go through the same formatting steps, ensuring every column matches the correct column of the old data. Every column must match exactly. Then save your new data with the same file name and in the same folder as your old data. Yes, write over the old data. Your formulae in your spreadsheet now automatically reference your new data and update your contribution sheet! It’s an instant update.
Figuring your P&L. In your contribution sheet, add five more column headings to the right of your previous headings. The first is Percent Response, the second is Average Order Value, the third is Contribution, the fourth is Contribution per Order and the fifth is Gross Dollars per Book.
Under the Percent Response column heading, you will type a formula that divides your Number of Orders by the number of pieces mailed. Format this column as a percent. Under Average Order Value, your formula will divide your Gross Dollars by Number of Orders.
Under Contribution, your formula will take Gross Dollars and subtract all of your expenses, including promotion, cost of goods sold and dollars of returns. Under Contribution per Order, you will divide the Contribution number you just calculated by Number of Orders.
Finally, under Dollars per Book, divide Gross Dollars by the number of pieces you mailed. After you have your first row input, highlight these cells and once again copy them to the empty cells beneath the first row.
You now have everything you need to analyze your data. All of your key metrics are in one place, and you can easily update them weekly. Repeat this procedure for each of your mailings, and you develop a valuable history of response. You will know how each segment performed for each of your mailings.
