# 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:

• "Excellent": Those whose sum exceeds \$100000 in the 3 month period.
• "Poor": Those whose sum is less than \$20000 in the 3 month period.
• "Improving": Those who show a steady improvement over the three month period.
• "Struggling": Those who show a steady decrease in sales numbers over the three month period.
• "Salesperson of the quarter": That person who achieves the highest monthly sales number occurring in the sheet (among the 18 monthly sales numbers).

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:

• first name
• last name
• the numbers for the three months
• the total number
• a phrase of praise if the salesperson is "excellent"
• a warning if the salesperson is "poor",
• a encouraging phrase if the salesperson is "improving",
• the question whether there are any problems outside of work, and the offer to help, for "struggling" salespersons,
• a congratulation for the salesperson of the quarter.

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