Product Sales Analysis Tool by demandmetric

VIEWS: 705 PAGES: 8

More Info
									                                                                                                                                        Product Sales Analysis Tool


Use this tool to compare actual sales vs. planned sales for each product.


Action Steps
   1. Enter actual and planned sales revenue, % margin, sales volume, new orders, and for each product using the "Product" tabs below
   2. Analyze variance between planned & actuals by using the charts provided
   3. Use the "Product Reports" tab to further examine sales revenue, sales volume and orders by product
                                                                                                                                                                                                            Product Sales Analysis Tool


Product #1 Name

                                   Historical Month - June 2007                         Current Month - June 2008                                    Historical YTD - 2007                                       YTD - 2008

                             Actual         Plan        Variance        %         Actual        Plan        Variance        %          Actual          Plan       Variance         %          Actual          Plan        Variance      %

Sales Revenue                 $439,002      $500,000      -$60,998          88%   $428,934      $450,000     -$21,066           95%   $5,000,000     $5,200,000   -$200,000            96%   $5,500,000     $6,000,000     -$500,000        92%
% Margin                          8.0%         10.0%           -2%          80%       9.0%        10.0%           -1%           90%           8.0%       10.0%         -2%             80%          8.0%         10.0%           -2%        80%
Sales Volume                      1384          1500           -116         92%       1247          1200           47        104%         16987          20000        -3013            85%         18000         17500            500   103%
New Orders                        1290          1400           -110         92%       1160          1150           10        101%         14567          17500        -2933            83%          9000          8500            500   106%
Renewals                             94           100              -6       94%            87          50          37        174%             2420        2500          -80            97%          9000          8000           1000   113%
Average Order ($)              $317.20       $333.33       -$16.14          95%    $343.97       $375.00      -$31.03           92%     $294.34         $260.00      $34.34          113%        $305.56       $342.86        -$37.30       89%


       Actual Sales Revenue (Historical Month vs. Current Month)                                Goal Variance (Current Month & Current YTD)                                   Actual Sales Revenue (Historical YTD vs. Current YTD)

   $440,000
   $438,000                                                                                $0                                                                      $5,600,000

   $436,000                                                                                                                                                        $5,500,000
                                                                                  -$100,000
                                                                                                                                                                   $5,400,000
   $434,000
                                                                                  -$200,000                                                                        $5,300,000
   $432,000
                                                                                                                                                                   $5,200,000
   $430,000                                                                       -$300,000
                                                                                                                                                                   $5,100,000
   $428,000
                                                                                  -$400,000                                                                        $5,000,000
   $426,000
                                                                                                                                                                   $4,900,000
   $424,000                                                                       -$500,000
                                                                                                                                                                   $4,800,000
   $422,000                                                                       -$600,000                                                                        $4,700,000
                                                                                                                                                                                                            Product Sales Analysis Tool


Product #2 Name

                                     Historical Month - June 2007                     Current Month - June 2008                                      Historical YTD - 2007                                       YTD - 2008

                               Actual         Plan       Variance      %         Actual          Plan       Variance        %          Actual          Plan       Variance         %          Actual          Plan        Variance      %

Sales Revenue                $1,000,000      $975,000       $25,000    103%      $900,000      $1,000,000   -$100,000           90%   $5,500,000     $5,000,000    $500,000          110%    $5,000,000     $5,750,000     -$750,000        87%
% Margin                          15.0%         15.0%            0%    100%        16.0%           17.0%          -1%           94%       15.0%          15.0%          0%           100%         16.5%          17.0%           -1%        97%
Sales Volume                        2750          2600           150   106%          3000           2900          100        103%         17000          16500         500           103%          18000         17500            500   103%
New Orders                          1750          1700            50   103%          2000           1900          100        105%             8500        8500           0           100%           9000          8500            500   106%
Renewals                            1000           900           100   111%          1000           1100         -100           91%           8500        8000         500           106%           9000          8000           1000   113%
Average Order Size               $363.64      $375.00        -$11.36       97%    $300.00        $344.83      -$44.83           87%     $323.53         $303.03      $20.50          107%        $277.78       $328.57        -$50.79       85%


           Actual Sales Revenue (Historical Month vs. Current Month)                            Goal Variance (Current Month & Current YTD)                                   Actual Sales Revenue (Historical YTD vs. Current YTD)

   $1,020,000
   $1,000,000                                                                             $0                                                                       $5,600,000

     $980,000                                                                    -$100,000                                                                         $5,500,000

                                                                                 -$200,000                                                                         $5,400,000
     $960,000
                                                                                                                                                                   $5,300,000
     $940,000                                                                    -$300,000
                                                                                                                                                                   $5,200,000
     $920,000                                                                    -$400,000
                                                                                                                                                                   $5,100,000
     $900,000                                                                    -$500,000
                                                                                                                                                                   $5,000,000
     $880,000                                                                    -$600,000                                                                         $4,900,000
     $860,000                                                                    -$700,000                                                                         $4,800,000
     $840,000                                                                    -$800,000                                                                         $4,700,000
                                                                                                                                                                                                            Product Sales Analysis Tool


Product #3 Name

                                     Historical Month - June 2007                      Current Month - June 2008                                     Historical YTD - 2007                                       YTD - 2008

                               Actual         Plan       Variance      %         Actual        Plan       Variance         %          Actual           Plan       Variance         %          Actual          Plan        Variance        %

Sales Revenue                  $300,000      $400,000     -$100,000        75%   $350,000      $450,000    -$100,000           78%   $1,500,000      $2,000,000   -$500,000            75%   $2,500,000     $3,000,000     -$500,000          83%
% Margin                          20.0%         20.0%            0%    100%         29.0%        30.0%           -1%           97%       21.0%           22.0%         -1%             95%        30.0%          33.0%           -3%          91%
Sales Volume                     100000        110000        -10000        91%     120000       130000        -10000           92%      700000          800000      -100000            88%      1000000       1100000        -100000          91%
New Orders                         50000        60000        -10000        83%      60000        70000        -10000           86%      400000           50000      350000           800%        500000         600000       -100000          83%
Renewals                           50000        50000              0   100%         60000        60000              0       100%        300000           30000      270000         1000%         500000         500000                0   100%
Average Order Size                 $3.00         $3.64        -$0.64       83%      $2.92         $3.46       -$0.54           84%           $2.14        $2.50      -$0.36            86%         $2.50          $2.73        -$0.23         92%


           Actual Sales Revenue (Historical Month vs. Current Month)                           Goal Variance (Current Month & Current YTD)                                    Actual Sales Revenue (Historical YTD vs. Current YTD)


     $360,000                                                                             $0                                                                       $3,000,000
     $350,000
                                                                                 -$100,000                                                                         $2,500,000
     $340,000
     $330,000                                                                    -$200,000                                                                         $2,000,000
     $320,000
                                                                                 -$300,000                                                                         $1,500,000
     $310,000
     $300,000                                                                    -$400,000                                                                         $1,000,000
     $290,000
                                                                                 -$500,000                                                                           $500,000
     $280,000
     $270,000                                                                    -$600,000                                                                                   $0
                                                                                                                                                                                                         Product Sales Analysis Tool


Product #4 Name

                                     Historical Month - June 2007                     Current Month - June 2008                                   Historical YTD - 2007                                       YTD - 2008

                               Actual         Plan       Variance      %         Actual      Plan       Variance         %          Actual          Plan       Variance         %          Actual          Plan        Variance      %

Sales Revenue                    $50,000     $100,000      -$50,000        50%    $30,000    $100,000     -$70,000           30%    $150,000        $300,000   -$150,000            50%     $120,000       $300,000     -$180,000        40%
% Margin                           5.0%          6.0%           -1%        83%      4.0%        5.0%           -1%           80%           5.0%        7.0%         -2%             71%          4.0%          8.0%           -4%        50%
Sales Volume                        2567          3000          -433       86%       2000        3000        -1000           67%       10000          15000        -5000            67%          9000         15000          -6000       60%
New Orders                          1567          1500            67   104%          1000        1500         -500           67%           5000        7500        -2500            67%          6000          7500          -1500       80%
Renewals                            1000          1500          -500       67%       1000        1500         -500           67%           5000        7500        -2500            67%          3000          7500          -4500       40%
Average Order Size                $19.48        $33.33       -$13.86       58%     $15.00      $33.33      -$18.33           45%      $15.00          $20.00      -$5.00            75%        $13.33        $20.00         -$6.67       67%


           Actual Sales Revenue (Historical Month vs. Current Month)                         Goal Variance (Current Month & Current YTD)                                   Actual Sales Revenue (Historical YTD vs. Current YTD)


       $60,000                                                                          $0                                                                        $160,000
                                                                                  -$20,000                                                                        $140,000
       $50,000
                                                                                  -$40,000
                                                                                                                                                                  $120,000
       $40,000                                                                    -$60,000
                                                                                  -$80,000                                                                        $100,000
       $30,000                                                                   -$100,000                                                                         $80,000
                                                                                 -$120,000                                                                         $60,000
       $20,000
                                                                                 -$140,000
                                                                                                                                                                   $40,000
       $10,000                                                                   -$160,000
                                                                                 -$180,000                                                                         $20,000
             $0                                                                  -$200,000                                                                                $0
                                                                                                                                                                                                           Product Sales Analysis Tool


Product #5 Name

                                     Historical Month - June 2007                      Current Month - June 2008                                    Historical YTD - 2007                                       YTD - 2008

                               Actual         Plan       Variance      %         Actual         Plan       Variance        %          Actual          Plan       Variance         %          Actual          Plan        Variance      %

Sales Revenue                  $800,000      $900,000     -$100,000        89%   $900,000      $950,000     -$50,000           95%   $4,000,000     $4,500,000   -$500,000            89%   $4,700,000     $5,000,000     -$300,000        94%
% Margin                          25.0%         30.0%           -5%        83%      29.0%         30.0%          -1%           97%       24.0%          30.0%         -6%             80%        29.5%          30.0%           -1%        98%
Sales Volume                         400           500          -100       80%        500           600         -100           83%           2000        2500         -500            80%          2500          3000           -500       83%
New Orders                           200           250           -50       80%        250           300          -50           83%           1000        1250         -250            80%          1250          1500           -250       83%
Renewals                             200           250           -50       80%        250           300          -50           83%           1000        1250         -250            80%          1250          1500           -250       83%
Average Order Size            $2,000.00     $1,800.00       $200.00    111%      $1,800.00     $1,583.33     $216.67        114%      $2,000.00      $1,800.00     $200.00          111%      $1,880.00     $1,666.67       $213.33    113%


           Actual Sales Revenue (Historical Month vs. Current Month)                           Goal Variance (Current Month & Current YTD)                                   Actual Sales Revenue (Historical YTD vs. Current YTD)


     $920,000                                                                             $0                                                                      $4,800,000
     $900,000
                                                                                  -$50,000                                                                        $4,600,000
     $880,000
     $860,000                                                                    -$100,000
                                                                                                                                                                  $4,400,000
     $840,000                                                                    -$150,000
                                                                                                                                                                  $4,200,000
     $820,000                                                                    -$200,000
     $800,000                                                                                                                                                     $4,000,000
                                                                                 -$250,000
     $780,000
                                                                                 -$300,000                                                                        $3,800,000
     $760,000
     $740,000                                                                    -$350,000                                                                        $3,600,000
                                                                                                                      Product Sales Analysis Tool


DO NOT ENTER VALUES INTO THIS SPREADSHEET.

Month - Sales Revenue Actual                     YTD - Sales Revenue Actual                 New Orders YTD - Actual
Product #1 Name                      $428,934    Product #1 Name               $5,500,000   Product #1 Name                                  9000
Product #2 Name                      $900,000    Product #2 Name               $5,000,000   Product #2 Name                                  9000
Product #3 Name                      $350,000    Product #3 Name               $2,500,000   Product #3 Name                                500000
Product #4 Name                       $30,000    Product #4 Name                $120,000    Product #4 Name                                  6000
Product #5 Name                      $900,000    Product #5 Name               $4,700,000   Product #5 Name                                  1250
Total                               $2,608,934   Total                        $17,820,000   Total                                          525250




Month - Sales Volume Actual                      YTD - Sales Volume Actual                  Renewals YTD - Actual
Product #1 Name                          1247    Product #1 Name                   18000    Product #1 Name                                  9000
Product #2 Name                          3000    Product #2 Name                   18000    Product #2 Name                                  9000
Product #3 Name                        120000    Product #3 Name                 1000000    Product #3 Name                                500000
Product #4 Name                          2000    Product #4 Name                    9000    Product #4 Name                                  3000
Product #5 Name                           500    Product #5 Name                    2500    Product #5 Name                                  1250
Total                                  126747    Total                           1047500    Total                                          522250
                                                                                                                                                    Product Sales Analysis Tool


DO NOT ENTER VALUES INTO THIS SPREADSHEET.

            Sales Revenue - Actual Current Month/Product                                            Actual YTD Sales Revenue by Product




                                              $428,934                                $120,000
              $900,000                                                                             $4,700,000                          $5,500,000
                                                                    Product #1 Name                                                                       Product #1 Name
                                                                    Product #2 Name                                                                       Product #2 Name
                                                         $900,000   Product #3 Name
                                                                                                 $2,500,000
                                                                                                                                                          Product #3 Name
                         $350,000                                                                                         $5,000,000
                                                                    Product #4 Name
                                                                                                                                                          Product #4 Name
                                                                    Product #5 Name
                                                                                                                                                          Product #5 Name

        $30,000




             Sales Volume - Actual Current Month/Product                                            Actual YTD Sales Volume by Product



                                       500 3000
                                    20001247                                                                         18000
                                                                                                                   9000
                                                                                                                    2500 18000



                                                                    Product #1 Name                                                                         Product #1 Name
                                                                    Product #2 Name                                                                         Product #2 Name

                                                                    Product #3 Name                                                                         Product #3 Name
                                    120000                                                                      1000000
                                                                    Product #4 Name                                                                         Product #4 Name

                                                                    Product #5 Name                                                                         Product #5 Name




                  New Orders YTD - Actual by Product                                                  Renewals YTD - Actual by Product


                                      1250 9000
                                        9000
                                     6000                                                                           1250 9000
                                                                                                                      9000
                                                                                                                   3000



                                                                    Product #1 Name                                                                         Product #1 Name

                                                                    Product #2 Name                                                                         Product #2 Name

                                                                    Product #3 Name                                                                         Product #3 Name
                                500000                                                                          500000
                                                                    Product #4 Name                                                                         Product #4 Name

                                                                    Product #5 Name                                                                         Product #5 Name

								
To top