# Free Sample Sales Letter by obs22303

VIEWS: 121 PAGES: 4

• pg 1
This spreadsheet provides you a template for calculating sales lead ratio formulas.
A completed example is also included. Feel free to modify the formulas or add others
that may be of value to you. This spreadsheet is cited in the white paper
http://www.customermfg.com/wpfile/closedloop.pdf

www.customermfg.com

We have done our best to proof the formulas in this spreadsheet and believe they are accurate,
but you should check them before basing any business decisions on the reuslts.
A                                            B                  C                   D                  E             F                   G
1
2    sales leads. A further refinement would breakout sales
5   Hard Costs:
6      Preparation                                                     Creative           250                Creative            2000           Creative         15000
7      Production                                                      Development        500                Printing            5000           Booth, etc.      35000
8      Distribution                                                    Air time           10000              Postage             11500          Shipping         5000
9   Soft Costs: (salary/opportunity costs)
10      Preparation:
11    Managers                                                          Review/approval 1000                  Review/approval 1500               Review/approval 5000
12    Employees                                                         Coordination    5000                  Coordination    10000              Coordination    20000
13      Production:
14    Managers                                                          N/A                0                  N/A                 0              N/A              0
15    Employees                                                         Coordination       1000               Coordination        3000           Coordination     5000
16      Distribution:
17    Managers                                                          N/A                0                  N/A                 0              Travel/Show      12000
18    Employees                                                         Coordination       1000               Coordination        2000           Travel/Show      15000
19
20                           Total Lead Generation Cost                                    =SUM(C6:C19)                           =SUM(E6:E19)                    =SUM(G6:G19)
21
22                          Number of Leads Generated                                      823                                    697                             1259
23
24                                              Cost Per Lead                              =C20/C22                               =E20/E22                        =G20/G22
25
27   Number of initial lead sales                                                          202                                    239                             164
28                                  Lead Conversion Rate                                   =C27/C22                               =E27/E22                        =G27/G22
29
30   3. Initial Sale to Lead Ratio:
31   Total revenue generated                                                               =C27*C32                               =E27*E32                        =G27*G32
32   Average revenue per lead                                                              67                                     55                              127
33                        Initial Sale to Lead Cost Ratio                                  =C24/C32                               =E24/E32                        =G24/G32
34
35   4. Gross Profit to Lead Ratio:
36   Total gross profit generated                                                          =C27*C37                               =E27*E37                        =G27*G37
37   Average gross profit per lead                                                         31.5                                   32.75                           69.5
38   Average gross margin                                                                  =C37/C32                               =E37/E32                        =G37/G32
39                     Gross Profit to Lead Cost Ratio                                     =C24/C37                               =E24/E37                        =G24/G37
40
41   5. Repeat Sales to Lead Ratio:
42   Est. # of repeat sales (next 12 mos.)                                                 3.2                                    4.5                             1.8
43   Total revenue generated                                                               =C42*C31                               =E42*E31                        =G42*G31
44   Average revenue per lead                                                              =C43/C27                               =E43/E27                        =G43/G27
45                    Repeat Sales to Lead Cost Ratio                                      =C24/C44                               =E24/E44                        =G24/G44
46
47   6. Repeat Gr. Profit to Lead Ratio:
48   Total gross profit generated                                                          =C36*C42                               =E36*E42                        =G36*G42
49   Average gross profit per lead                                                         =C48/C27                               =E48/E27                        =G48/G27
50           Repeat Gross Profit to Lead Cost Ratio                                        =C24/C49                               =E24/E49                        =G24/G49
51
53   Estimated customer lifetime (yrs.)                                                    3                                      5                               10
54   Lifetime revenue                                                                      =C53*C43                               =E53*E43                        =G53*G43
55   Lifetime gross profit                                                                 =C53*C48                               =E53*E48                        =G53*G48
56

Developed
C:\D\DRDATA\ARTICLES\8c7863e1-56c6-4804-833f-2e50c0dea6fe.xls   by Dr. David Palmer, The OMT Group, Santa Clara, CA Tel: 408-496-4596                   July 10, 1997

NOTE: This sample lead analysis "groups" all product

Hard Costs:
Preparation                                                 Creative                   \$250.000 Creative                \$2,000.000 Creative           \$15,000.000
Production                                                  Development                 500.000 Printing                 5,000.000 Booth, etc.         35,000.000
Distribution                                                Air time                 10,000.000 Postage                 11,500.000 Shipping             5,000.000
Soft Costs: (salary/opportunity costs)
Preparation:
Managers                                                      Review/approval           1,000.000 Review/approval          1,500.000 Review/approval       5,000.000
Employees                                                     Coordination              5,000.000 Coordination            10,000.000 Coordination         20,000.000
Production:
Managers                                                      N/A                           0.000 N/A                          0.000 N/A                        0.000
Employees                                                     Coordination              1,000.000 Coordination             3,000.000 Coordination           5,000.000
Distribution:
Managers                                                      N/A                           0.000 N/A                          0.000 Travel/Show          12,000.000
Employees                                                     Coordination              1,000.000 Coordination             2,000.000 Travel/Show          15,000.000

Total Lead Generation Cost                                     \$18,750.000                        \$35,000.000                    \$112,000.000

Number of Leads Generated                                                 823                                697                               1,259

Cost Per Lead                                     \$22.783                            \$50.215                           \$88.959

Number of initial lead sales                                                                   202                                239                              164
Lead Conversion Rate                                          24.54%                             34.29%                           13.03%

3. Initial Sale to Lead Ratio:
Total revenue generated                                                                \$13,534.00                         \$13,145.00                      \$20,828.00
Average revenue per lead                                                                   \$67.00                             \$55.00                         \$127.00
Initial Sale to Lead Cost Ratio                                        34.00%                             91.30%                          70.05%

4. Gross Profit to Lead Ratio:
Total gross profit generated                                                            \$6,363.00                          \$7,827.25                      \$11,398.00
Average gross profit per lead                                                              \$31.50                             \$32.75                          \$69.50
Average gross margin                                                                      47.01%                             59.55%                          54.72%
Gross Profit to Lead Cost Ratio                                           72.33%                            153.33%                         128.00%

5. Repeat Sales to Lead Ratio:
Est. # of repeat sales (next 12 mos.)                                                         3.2                                 4.5                            1.8
Total revenue generated                                                                \$43,308.80                         \$59,152.50                      \$37,490.40
Average revenue per lead                                                                  \$214.40                            \$247.50                         \$228.60
Repeat Sales to Lead Cost Ratio                                            10.63%                             20.29%                          38.91%

6. Repeat Gr. Profit to Lead Ratio:
Total gross profit generated                                                           \$20,361.60                         \$35,222.63                      \$20,516.40
Average gross profit per lead                                                             \$100.80                            \$147.38                         \$125.10
Repeat Gross Profit to Lead Cost Ratio                                              22.60%                             34.07%                          71.11%

Estimated customer lifetime (yrs.)                                                                3                                    5                              10