Mac Forum / Applications / Excel / May 2006

I work as a sales rep for the Kansas City STAR. We've just introduced a
new rate structure that has a great deal of variables to determine the
best rate for my client. (If our advertisers play by all the rules
their rates are sometimes cut by more than half.)

It seems to me that there should be a way to create a spreadsheet that
by entering the date(s), size and section the advertiser wishes to run
their ads it would give the correct pricing for each ad.

Our rates are based on the number of ads ran within a seven day window
and the section of the paper they run in. We special discounts if the
advertiser will run the same ad for multiple days or in certain areas
of our paper.

I envision a spreadsheet where I could enter something like this;

    Client Name or Account Number; John Doe, 12345678

    Ad Size, Color, Etc.; 3 column by 10 inch, B&W, etc.

    Date when the ads are to start running; SUN, May 27

    The days of the week and the section each ad will run;
         SUN - Main News
         MON - Sports
         TUE - Metro  (and so on)

The spreadsheet from this information would know what pricing level the
advertiser is qualified for based on the investment level they've
committed to. It can then determine the best rates for the advertiser
and populate a worksheet that would fill in product codes and other
variables the production staff needs to properly place the ad in the
correct publication and section.

Here is a link to our rate card that may better explain how this might
work. Any help or advise someone could provide would be greatly
appreciated.

Please feel free to contact me and I can go into greater detail.

http://www.kansascity.com/multimedia/kansascity/kansascitystar/archive/RateCard/
RetailRatesROP.pdf