Optional (make-up) Project:

  F o r m   L e t t e r s  

CPT 105 · Franklin College · Erich Prisner · 2002-2006

Deadline for the project is Friday, December 10.

Assume you are the boss of 6 traveling salesmen/women. Prepare an Excel sheet where you can keep track of the monthly sales numbers for each of them. The first names of them should be in the first column, the second names in the second, and the third, fourth, and fifth columns should show the respective sales numbers in the corresponding month (in $). You may use these numbers

Oct 04 Nov 04 Dec 04
$24.678,00 $25.479,00 $32.322,00
$15.476,00 $53.423,00 $42.376,00
$39.685,00 $37.765,00 $37.645,00
$6.543,00 $3.374,00 $7.674,00
$35.468,00 $25.699,00 $12.165,00
$26.786,00 $25.676,00 $32.365,00

or you may make numbers up. In the sixth column, the sum over the three month period should be computed automatically. Don't forget to label all columns in the first row, that is important for the form letter.

Assume you classify your salespersons according to the following:

In the seventh column you should write a formula which results in a "yes" if the salesperson is "improving". In the eighth column a formula should give a "yes" if the person is "struggling" (both according to the definition above). In both cases use a combination of the "IF"- and the "AND"-formula. The ninth column you indicate the salesperson of the quarter. Only if you don't succeed with the formula, you may fill in the values by hand.

Now create a form letter, which you will send to your salespersons after the period. The letter should automatically fill in:

The letters don't have to contain the addresses, but they should look professional---use the Word features.

Needless to say that everything should pop up automatically.

November 2004