Analysis Dashboards Forecast Actuals Months Cum forecast Cum actuals +...

Question

Analysis
    Dashboards
            Forecast    Actuals    Months    Cum forecast    Cum actuals + trend
    1    Jan    $ 82,280     XXXXXXXXXX    Jan    $ 82,280    $ 81,710
    2    Feb    82,880     XXXXXXXXXX    Feb    $ 165,160    $ 163,827        these are the monthly forecast totals and the actuals for Jan-Sept…and then I trended just the totals
    3    Mar    83,630     XXXXXXXXXX    Mar    $ 248,790    $ 246,235        When you trend the individual records there isn't enough data for it to be usable.
    4    Apr    83,900     XXXXXXXXXX    Apr    $ 332,690    $ 328,855        next, calculate monthly cumulative sales
    5    May    83,700     XXXXXXXXXX    May    $ 416,390    $ 411,827        then you do a chart
    6    Jun    83,800     XXXXXXXXXX    Jun    $ 500,190    $ 495,168        or two charts, basically showing the monthly
    7    Jul    83,580     XXXXXXXXXX    Jul    $ 583,770    $ 578,738        forecast v actual as well as the trend over the yea
    8    Aug    83,680     XXXXXXXXXX    Aug    $ 667,450    $ 662,682        Next you need to have more than one drill down into the data, to explain why
    9    Sep    83,800     XXXXXXXXXX    Sep    $ 751,250    $ 746,961        is it due to product, region, customer type or sales rep?
    10    Oct    84,000     XXXXXXXXXX    Oct    $ 835,250    $ 831,523
    11    Nov    85,100     XXXXXXXXXX    Nov    $ 920,350    $ 916,399
    12    Dec    85,650     XXXXXXXXXX    Dec    $ 1,006,000    $ 1,001,588
            $ 1,006,000    $ 1,001,588
                This answers the key question - you will not make your target level of sales
    Forecast detail
    Customer Type    Product     Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep    Oct    Nov    Dec    FY 2010
    Corporate        $ 27,230    $ 27,440    $ 27,690    $ 27,810    $ 27,710    $ 27,750    $ 27,670    $ 27,710    $ 27,750    $ 27,820    $ 28,190    $ 28,370    $ 333,140
         Copy Paper    $ 9,670    $ 9,750    $ 9,840    $ 9,880    $ 9,850    $ 9,870    $ 9,840    $ 9,850    $ 9,870    $ 9,890    $ 10,030    $ 10,100    $ 118,440
        Ink Jet Cartridges    9,940    10,010    10,100    10,130    10,110    10,120    10,090    10,110    10,120    10,150    10,280    10,340    121,500
        Toner Cartridges    7,620    7,680    7,750    7,800    7,750    7,760    7,740    7,750    7,760    7,780    7,880    7,930    93,200
    Educational        $ 24,640    $ 24,800    $ 25,020    $ 25,080    $ 25,040    $ 25,070    $ 25,020    $ 25,040    $ 25,070    $ 25,120    $ 25,450    $ 25,610    $ 300,960
         Copy Paper    9,800    9,850    9,930    9,940    9,940    9,950    9,950    9,940    9,950    9,960    10,090    10,160    119,460
        Ink Jet Cartridges    8,340    8,400    8,480    8,510    8,490    8,500    8,470    8,480    8,500    8,520    8,630    8,680    102,000
        Toner Cartridges    6,500    6,550    6,610    6,630    6,610    6,620    6,600    6,620    6,620    6,640    6,730    6,770    79,500
    Governmental        $ 30,410    $ 30,640    $ 30,920    $ 31,010    $ 30,950    $ 30,980    $ 30,890    $ 30,930    $ 30,980    $ 31,060    $ 31,460    $ 31,670    $ 371,900
         Copy Paper    10,970    11,050    11,150    11,180    11,160    11,170    11,140    11,150    11,170    11,200    11,340    11,420    134,100
        Ink Jet Cartridges    9,360    9,430    9,520    9,550    9,530    9,540    9,510    9,520    9,540    9,560    9,690    9,750    114,500
        Toner Cartridges    10,080    10,160    10,250    10,280    10,260    10,270    10,240    10,260    10,270    10,300    10,430    10,500    123,300
    Total Sales        $ 82,280    $ 82,880    $ 83,630    $ 83,900    $ 83,700    $ 83,800    $ 83,580    $ 83,680    $ 83,800    $ 84,000    $ 85,100    $ 85,650    $ 1,006,000
        Cum        $ 165,160    $ 248,790    $ 332,690    $ 416,390    $ 500,190    $ 583,770    $ 667,450    $ 751,250    $ 835,250    $ 920,350    $ 1,006,000    $ 1,006,000
    I did a pivot table with the same row selection as the forecast.
        1    2    3    4    5    6    7    8    9    10    11    12
    Row Labels    Sum of Jan    Sum of Feb    Sum of Mar    Sum of Apr    Sum of May    Sum of Jun    Sum of Jul    Sum of Aug    Sum of Sep    Oct trend    Nov trend    Dec trend    FY
    Corporate    $ 28,536    $ 28,702    $ 28,826    $ 28,886    $ 29,010    $ 29,167    $ 29,270    $ 29,394    $ 29,559    $ 29,650    $ 29,772    $ 29,894    $ 350,662
    Copy Paper    $ 10,502    $ 10,613    $ 10,646    $ 10,643    $ 10,694    $ 10,760    $ 10,808    $ 10,854    $ 10,886    $ 10,937    $ 10,982    $ 11,027    $ 129,351
    Ink Jet Cartridges    $ 9,282    $ 9,319    $ 9,391    $ 9,408    $ 9,443    $ 9,516    $ 9,547    $ 9,566    $ 9,644    $ 9,675    $ 9,718    $ 9,762    $ 114,269
    Toner Cartridges    $ 8,752    $ 8,770    $ 8,789    $ 8,835    $ 8,873    $ 8,891    $ 8,915    $ 8,974    $ 9,029    $ 9,038    $ 9,072    $ 9,106    $ 107,041
    Educational    $ 22,341    $ 22,423    $ 22,535    $ 22,602    $ 22,705    $ 22,772    $ 22,841    $ 22,968    $ 23,034    $ 23,124    $ 23,210    $ 23,297    $ 273,854
    Copy Paper    $ 7,442    $ 7,458    $ 7,546    $ 7,553    $ 7,569    $ 7,607    $ 7,628    $ 7,665    $ 7,691    $ 7,726    $ 7,757    $ 7,788    $ 91,433
    Ink Jet Cartridges    $ 8,795    $ 8,825    $ 8,827    $ 8,854    $ 8,916    $ 8,928    $ 8,954    $ 8,985    $ 9,023    $ 9,044    $ 9,073    $ 9,101    $ 107,322
    Toner Cartridges    $ 6,104    $ 6,140    $ 6,162    $ 6,195    $ 6,220    $ 6,237    $ 6,259    $ 6,318    $ 6,320    $ 6,354    $ 6,381    $ 6,408    $ 75,099
    Governmental    $ 30,833    $ 30,992    $ 31,047    $ 31,132    $ 31,258    $ 31,402    $ 31,459    $ 31,582    $ 31,686    $ 31,789    $ 31,893    $ 31,998    $ 377,072
    Copy Paper    $ 10,948    $ 10,977    $ 10,990    $ 11,047    $ 11,101    $ 11,186    $ 11,225    $ 11,291    $ 11,323    $ 11,375    $ 11,426    $ 11,477    $ 134,366
    Ink Jet Cartridges    $ 9,907    $ 9,986    $ 9,982    $ 10,009    $ 10,026    $ 10,046    $ 10,024    $ 10,036    $ 10,084    $ 10,093    $ 10,109    $ 10,125    $ 120,428
    Toner Cartridges    $ 9,978    $ 10,029    $ 10,075    $ 10,076    $ 10,131    $ 10,170    $ 10,210    $ 10,255    $ 10,279    $ 10,321    $ 10,358    $ 10,396    $ 122,278
    Grand Total    $ 81,710    $ 82,117    $ 82,408    $ 82,620    $ 82,973    $ 83,341    $ 83,570    $ 83,944    $ 84,279    $ 84,562    $ 84,876    $ 85,189    $ 1,001,588
            $ 163,827    $ 246,235    $ 328,855    $ 411,827    $ 495,168    $ 578,738    $ 662,682    $ 746,961    $ 831,523    $ 916,399    $ 1,001,588    $ 6,383,804
    A useful analysis would be comparing actual v forecast with a drill down ability

    Region    Product    Customer Type    Sales Representative    Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep                            31-Jan-10    28-Feb-10    31-Mar-10    30-Apr-10    31-May-10    30-Jun-10    31-Jul-10    31-Aug-10    30-Sep-10    31-Oct-10    30-Nov-10    31-Dec-10
    East    Toner Cartridges    Corporate    Nathan Clark    $ 827    $ 825    $ 826    $ 827    $ 830    $ 830    $ 852    $ 877    $ 884                            $ 827    $ 825    $ 826    $ 827    $ 830    $ 830    $ 852    $ 877    $ 884    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Corporate    Rachel Smith    463    467    465    470    467    468    469    466    464                            463    467    465    470    467    468    469    466    464    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Corporate    Karen Winthrop    186    185    186    185    186    188    188    187    190                            186    185    186    185    186    188    188    187    190    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Corporate    Walter Jones    186    187    186    185    185    184    185    185    185                            186    187    186    185    185    184    185    185    185    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Educational    Nathan Clark    337    334    331    337    341    340    341    342    346                            337    334    331    337    341    340    341    342    346    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Educational    Rachel Smith    313    313    315    313    311    317    314    316    313                            313    313    315    313    311    317    314    316    313    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Educational    Karen Winthrop    749    759    768    777    782    800    800    824    823                            749    759    768    777    782    800    800    824    823    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Educational    Walter Jones    490    495    494    491    495    495    491    506    508                            490    495    494    491    495    495    491    506    508    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Governmental    Nathan Clark    199    201    204    205    203    203    205    207    209                            199    201    204    205    203    203    205    207    209    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Governmental    Rachel Smith    845    845    838    832    839    831    848    847    843                            845    845    838    832    839    831    848    847    843    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Governmental    Karen Winthrop    812    818    813    811    818    829    835    829    829                            812    818    813    811    818    829    835    829    829    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Toner Cartridges    Governmental    Walter Jones    713    712    722    718    729    746    751    758    760                            713    712    722    718    729    746    751    758    760    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Corporate    Nathan Clark    739    732    732    733    733    732    745    740    746                            739    732    732    733    733    732    745    740    746    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Corporate    Rachel Smith    227    227    230    237    241    240    243    241    239                            227    227    230    237    241    240    243    241    239    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Corporate    Karen Winthrop    861    862    854    852    851    850    849    858    881                            861    862    854    852    851    850    849    858    881    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Corporate    Walter Jones    316    317    326    330    331    337    341    350    358                            316    317    326    330    331    337    341    350    358    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Educational    Nathan Clark    879    878    872    879    885    896    896    900    903                            879    878    872    879    885    896    896    900    903    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Educational    Rachel Smith    562    563    559    559    561    558    557    553    556                            562    563    559    559    561    558    557    553    556    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Educational    Karen Winthrop    155    154    153    153    152    152    152    154    157                            155    154    153    153    152    152    152    154    157    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Educational    Walter Jones    522    525    526    522    520    519    516    518    521                            522    525    526    522    520    519    516    518    521    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Governmental    Nathan Clark    161    162    161    160    162    162    161    161    160                            161    162    161    160    162    162    161    161    160    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Governmental    Rachel Smith    870    886    884    887    888    905    906    899    891                            870    886    884    887    888    905    906    899    891    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Governmental    Karen Winthrop    1,011    1,010    1,005    1,001    1,005    997    1,001    995    1,010                            1,011    1,010    1,005    1,001    1,005    997    1,001    995    1,010    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Ink Jet Cartridges    Governmental    Walter Jones    905    923    927    926    932    940    932    939    940                            905    923    927    926    932    940    932    939    940    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Corporate    Nathan Clark    644    656    652    655    654    661    665    662    659                            644    656    652    655    654    661    665    662    659    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Corporate    Rachel Smith    973    977    1,001    998    1,006    1,016    1,044    1,041    1,033                            973    977    1,001    998    1,006    1,016    1,044    1,041    1,033    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Corporate    Karen Winthrop    931    949    940    936    937    945    946    967    970                            931    949    940    936    937    945    946    967    970    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Corporate    Walter Jones    132    132    136    135    135    134    133    135    134                            132    132    136    135    135    134    133    135    134    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Educational    Nathan Clark    995    994    1,023    1,014    1,015    1,030    1,030    1,035    1,039                            995    994    1,023    1,014    1,015    1,030    1,030    1,035    1,039    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Educational    Rachel Smith    647    642    654    649    645    647    645    641    642                            647    642    654    649    645    647    645    641    642    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Educational    Karen Winthrop    174    172    173    178    177    178    177    176    176                            174    172    173    178    177    178    177    176    176    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Educational    Walter Jones    819    810    810    813    814    816    811    803    803                            819    810    810    813    814    816    811    803    803    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Governmental    Nathan Clark    774    776    772    768    768    765    762    784    785                            774    776    772    768    768    765    762    784    785    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Governmental    Rachel Smith    1,048    1,049    1,070    1,086    1,099    1,094    1,084    1,097    1,103                            1,048    1,049    1,070    1,086    1,099    1,094    1,084    1,097    1,103    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Governmental    Karen Winthrop    1,105    1,104    1,105    1,126    1,129    1,153    1,142    1,144    1,146                            1,105    1,104    1,105    1,126    1,129    1,153    1,142    1,144    1,146    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    East    Copy Paper    Governmental    Walter Jones    557    557    552    551    548    555    551    550    555                            557    557    552    551    548    555    551    550    555    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Corporate    Nathan Clark    584    592    590    585    588    585    589    604    604                            584    592    590    585    588    585    589    604    604    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Corporate    Rachel Smith    179    181    180    180    180    181    181    182    181                            179    181    180    180    180    181    181    182    181    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Corporate    Karen Winthrop    948    943    939    955    951    948    942    944    949                            948    943    939    955    951    948    942    944    949    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Corporate    Walter Jones    698    693    693    698    709    708    711    712    714                            698    693    693    698    709    708    711    712    714    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Educational    Nathan Clark    299    299    300    308    318    316    316    319    324                            299    299    300    308    318    316    316    319    324    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Educational    Rachel Smith    167    170    170    174    175    179    180    182    184                            167    170    170    174    175    179    180    182    184    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Educational    Karen Winthrop    602    604    622    624    628    623    630    625    624                            602    604    622    624    628    623    630    625    624    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Educational    Walter Jones    390    399    399    398    394    392    395    400    401                            390    399    399    398    394    392    395    400    401    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Governmental    Nathan Clark    895    921    934    936    938    944    935    944    966                            895    921    934    936    938    944    935    944    966    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Governmental    Rachel Smith    825    836    853    848    844    836    848    852    854                            825    836    853    848    844    836    848    852    854    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Governmental    Karen Winthrop    553    553    549    544    540    547    544    556    551                            553    553    549    544    540    547    544    556    551    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Toner Cartridges    Governmental    Walter Jones    225    223    229    228    230    228    226    228    229                            225    223    229    228    230    228    226    228    229    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Corporate    Nathan Clark    661    656    657    654    669    682    675    683    687                            661    656    657    654    669    682    675    683    687    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Corporate    Rachel Smith    381    384    396    397    396    395    394    403    401                            381    384    396    397    396    395    394    403    401    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Corporate    Karen Winthrop    435    432    429    431    428    439    436    439    439                            435    432    429    431    428    439    436    439    439    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Corporate    Walter Jones    354    356    356    354    351    357    355    364    362                            354    356    356    354    351    357    355    364    362    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Educational    Nathan Clark    361    359    357    356    354    356    354    356    361                            361    359    357    356    354    356    354    356    361    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Educational    Rachel Smith    439    440    449    448    447    447    445    449    445                            439    440    449    448    447    447    445    449    445    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Educational    Karen Winthrop    653    652    646    647    642    647    662    663    668                            653    652    646    647    642    647    662    663    668    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Educational    Walter Jones    194    198    199    199    200    200    201    201    200                            194    198    199    199    200    200    201    201    200    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Governmental    Nathan Clark    848    859    866    862    866    873    876    875    886                            848    859    866    862    866    873    876    875    886    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Governmental    Rachel Smith    308    309    313    313    314    312    316    317    316                            308    309    313    313    314    312    316    317    316    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Governmental    Karen Winthrop    156    158    157    157    156    158    160    159    162                            156    158    157    157    156    158    160    159    162    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Ink Jet Cartridges    Governmental    Walter Jones    821    830    825    827    822    821    815    830    831                            821    830    825    827    822    821    815    830    831    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Corporate    Nathan Clark    718    722    738    740    742    757    765    788    805                            718    722    738    740    742    757    765    788    805    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Corporate    Rachel Smith    236    236    235    234    239    238    244    242    243                            236    236    235    234    239    238    244    242    243    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Corporate    Karen Winthrop    192    196    196    195    196    195    198    198    198                            192    196    196    195    196    195    198    198    198    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Corporate    Walter Jones    1,115    1,128    1,139    1,130    1,121    1,111    1,107    1,097    1,098                            1,115    1,128    1,139    1,130    1,121    1,111    1,107    1,097    1,098    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Educational    Nathan Clark    199    206    209    206    212    212    217    218    218                            199    206    209    206    212    212    217    218    218    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Educational    Rachel Smith    70    73    73    74    74    74    75    74    74                            70    73    73    74    74    74    75    74    74    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Educational    Karen Winthrop    740    750    757    768    777    793    786    782    790                            740    750    757    768    777    793    786    782    790    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Educational    Walter Jones    638    644    646    645    644    647    654    658    660                            638    644    646    645    644    647    654    658    660    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Governmental    Nathan Clark    423    427    429    432    432    430    438    439    440                            423    427    429    432    432    430    438    439    440    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Governmental    Rachel Smith    806    804    805    803    820    830    835    828    831                            806    804    805    803    820    830    835    828    831    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Governmental    Karen Winthrop    761    766    777    781    784    805    808    803    802                            761    766    777    781    784    805    808    803    802    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    North    Copy Paper    Governmental    Walter Jones    765    772    767    766    762    781    802    807    811                            765    772    767    766    762    781    802    807    811    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Corporate    Nathan Clark    682    685    693    697    697    714    712    709    703                            682    685    693    697    697    714    712    709    703    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Corporate    Rachel Smith    990    1,002    1,005    999    1,005    1,017    1,026    1,029    1,031                            990    1,002    1,005    999    1,005    1,017    1,026    1,029    1,031    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Corporate    Karen Winthrop    569    566    563    564    563    564    563    571    581                            569    566    563    564    563    564    563    571    581    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Corporate    Walter Jones    737    748    750    759    769    767    761    772    786                            737    748    750    759    769    767    761    772    786    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Educational    Nathan Clark    946    943    935    944    935    942    937    951    944                            946    943    935    944    935    942    937    951    944    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Educational    Rachel Smith    86    85    86    86    86    85    87    87    88                            86    85    86    86    86    85    87    87    88    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Educational    Karen Winthrop    463    465    462    461    468    466    478    475    474                            463    465    462    461    468    466    478    475    474    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Educational    Walter Jones    412    422    424    423    423    423    424    424    420                            412    422    424    423    423    423    424    424    420    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Governmental    Nathan Clark    718    721    719    721    714    724    730    742    742                            718    721    719    721    714    724    730    742    742    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Governmental    Rachel Smith    1,004    995    999    999    1,018    1,016    1,008    1,005    1,004                            1,004    995    999    999    1,018    1,016    1,008    1,005    1,004    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Governmental    Karen Winthrop    722    735    731    738    744    740    747    745    740                            722    735    731    738    744    740    747    745    740    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Toner Cartridges    Governmental    Walter Jones    601    604    618    619    632    641    641    638    636                            601    604    618    619    632    641    641    638    636    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Corporate    Nathan Clark    389    393    392    389    385    386    386    383    386                            389    393    392    389    385    386    386    383    386    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Corporate    Rachel Smith    872    895    926    939    963    958    951    950    970                            872    895    926    939    963    958    951    950    970    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Corporate    Karen Winthrop    388    385    387    384    382    390    391    388    388                            388    385    387    384    382    390    391    388    388    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Corporate    Walter Jones    987    994    994    988    995    1,016    1,016    1,019    1,009                            987    994    994    988    995    1,016    1,016    1,019    1,009    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Educational    Nathan Clark    727    721    729    733    748    757    764    761    759                            727    721    729    733    748    757    764    761    759    ERROR:#VALUE!    ERROR:#VALUE!    ERROR:#VALUE!
    South    Ink Jet Cartridges    Educational    Rachel

hw-10-example-zwbvnoto.xlsx acuatl-hw-10-sw0mrq3u.xlsx

Sandeep · Accepted Answer

Analysis
	Dashboards
			Forecast	Actuals	Months	Cum forecast	Cum actuals + trend
	1	Jan	$   70,126	$77,509	Jan	$   70,126	$   77,509
	2	Feb	$   80,728	$84,365	Feb	$   150,854	$   161,874		these are the monthly forecast totals and the actuals for Jan-Sept…and then I trended just the totals
	3	Mar	$   113,137	$91,143	Mar	$   263,991	$   253,017		When you trend the individual records there isn't enough data for it to be usable.
	4	Apr	$   113,502	$97,892	Apr	$   377,493	$   350,909		next, calculate monthly cumulative sales
	5	May	$   113,232	$104,837	May	$   490,725	$   455,746		then you do a chart 
	6	Jun	$   113,367	$111,976	Jun	$   604,092	$   567,722		or two charts, basically showing the monthly
	7	Jul	$   113,069	$118,829	Jul	$   717,161	$   686,551		forecast v actual as well as the trend over the year
	8	Aug	$   113,205	$112,765	Aug	$   830,366	$   799,316		Next you need to have more than one drill down into the data, to explain why
	9	Sep	$   113,367	$106,485	Sep	$   943,733	$   905,801		is it due to product, region, customer type or sales rep?
	10	Oct Trend	$   113,638	$123,191	Oct	$   1,057,371	$   1,028,992
	11	Nov Trend	$   115,126	$127,700	Nov	$   1,172,496	$   1,156,692
	12	Dec Trend	$   115,870	$132,210	Dec	$   1,288,366	$   1,288,902
			$   1,288,366	$   1,288,902
				This answers the key question - you will exceed your target level of sales
	Forecast detail
	Region	Customer Type	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	FY 2016
	Americas		$   23,208	$   26,728	$   37,460	$   37,622	$   37,487	$   37,541	$   37,433	$   37,487	$   37,541	$   37,636	$   38,136	$   38,380	$   426,657
		Corporate	$   8,242	$   9,497	$   13,312	$   13,366	$   13,325	$   13,352	$   13,312	$   13,325	$   13,352	$   13,379	$   13,569	$   13,664	$   151,695
		Educational	$   8,472	$   9,750	$   13,664	$   13,704	$   13,677	$   13,691	$   13,650	$   13,677	$   13,691	$   13,731	$   13,907	$   13,988	$   155,602
		Government	$   6,494	$   7,481	$   10,484	$   10,552	$   10,484	$   10,498	$   10,471	$   10,484	$   10,498	$   10,525	$   10,660	$   10,728	$   119,360
	Europe		$   21,000	$   24,156	$   33,848	$   33,929	$   33,875	$   33,915	$   33,848	$   33,875	$   33,915	$   33,983	$   34,429	$   34,646	$   385,420
		Corporate	$   8,352	$   9,594	$   13,434	$   13,447	$   13,447	$   13,461	$   13,461	$   13,447	$   13,461	$   13,474	$   13,650	$   13,745	$   152,972
		Educational	$   7,108	$   8,182	$   11,472	$   11,513	$   11,486	$   11,499	$   11,458	$   11,472	$   11,499	$   11,526	$   11,675	$   11,743	$   130,632
		Government	$   5,540	$   6,380	$   8,942	$   8,969	$   8,942	$   8,956	$   8,929	$   8,956	$   8,956	$   8,983	$   9,105	$   9,159	$   101,815
	Asia		$   25,918	$   29,844	$   41,829	$   41,951	$   41,870	$   41,911	$   41,789	$   41,843	$   41,911	$   42,019	$   42,560	$   42,844	$   476,289
		Corporate	$   9,350	$   10,763	$   15,084	$   15,125	$   15,098	$   15,111	$   15,071	$   15,084	$   15,111	$   15,152	$   15,341	$   15,449	$   171,738
		Educational	$   7,977	$   9,185	$   12,879	$   12,920	$   12,892	$   12,906	$   12,865	$   12,879	$   12,906	$   12,933	$   13,109	$   13,190	$   146,642
		Government	$   8,591	$   9,896	$   13,866	$   13,907	$   13,880	$   13,894	$   13,853	$   13,880	$   13,894	$   13,934	$   14,110	$   14,205	$   157,910
	Total Sales		$   70,126	$   80,728	$   113,137	$   113,502	$   113,232	$   113,367	$   113,069	$   113,205	$   113,367	$   113,638	$   115,126	$   115,870	$   1,288,366
		Cum		$   150,854	$   263,991	$   377,493	$   490,725	$   604,092	$   717,161	$   830,366	$   943,733	$   1,057,371	$   1,172,496	$   1,288,366	$   1,288,366
			2	3	4	5	6	7	8	9	10	11	12
	Actual Sales
	Row Labels	Sum of Jan	Sum of Feb	Sum of Mar	Sum of Apr	Sum of May	Sum of Jun	Sum of Jul	Sum of Aug	Sum of Sep	Sum of Oct Trend	Sum of Nov Trend	Sum of Dec Trend	FY 2016
	Americas	$26,550	$28,854	$31,168	$33,445	$35,795	$38,268	$40,618	$38,564	$36,414	$42,101	$43,639	$45,178	$440,595
	Corporate	$7,778	$8,467	$9,143	$9,810	$10,485	$11,189	$11,982	$11,400	$10,784	$12,439	$12,903	$13,368	$129,748
	Educational	$7,970	$8,630	$9,349	$10,027	$10,716	$11,471	$12,113	$11,505	$10,858	$12,556	$13,008	$13,461	$131,664
	Governmental	$10,802	$11,758	$12,676	$13,608	$14,594	$15,608	$16,523	$15,659	$14,772	$17,106	$17,728	$18,349	$179,183
	Asia	$22,364	$24,399	$26,427	$28,342	$30,305	$32,376	$34,403	$32,672	$30,891	$35,707	$37,022	$38,337	$373,245
	Corporate	$7,797	$8,471	$9,163	$9,825	$10,507	$11,208	$11,869	$11,310	$10,685	$12,331	$12,779	$13,227	$129,171
	Educational	$5,702	$6,232	$6,757	$7,270	$7,783	$8,305	$8,846	$8,375	$7,918	$9,174	$9,516	$9,858	$95,737
	Governmental	$8,865	$9,697	$10,507	$11,247	$12,015	$12,863	$13,688	$12,987	$12,288	$14,201	$14,727	$15,253	$148,338
	Europe	$28,596	$31,111	$33,548	$36,105	$38,737	$41,332	$43,808	$41,529	$39,180	$45,383	$47,039	$48,695	$475,062
	Corporate	$10,580	$11,566	$12,496	$13,429	$14,440	$15,497	$16,414	$15,533	$14,686	$17,035	$17,672	$18,309	$177,657
	Educational	$7,151	$7,745	$8,348	$8,993	$9,657	$10,269	$10,896	$10,393	$9,774	$11,315	$11,728	$12,141	$118,410
	Governmental	$10,864	$11,800	$12,704	$13,682	$14,640	$15,566	$16,498	$15,603	$14,720	$17,034	$17,639	$18,244	$178,995
	Grand Total	$77,509	$84,365	$91,143	$97,892	$104,837	$111,976	$118,829	$112,765	$106,485	$123,191	$127,700	$132,210	$1,288,902
			$161,874	$253,017	$350,909	$455,746	$567,722	$686,551	$799,316	$905,801	$1,028,992	$1,156,692	$1,288,902	$7,655,523
	Forecast Sales
	Row Labels	Sum of Jan	Sum of Feb	Sum of Mar	Sum of Apr	Sum of May	Sum of Jun	Sum of Jul	Sum of Aug	Sum of Sep	Sum of Oct	Sum of Nov	Sum of Dec	Sum of FY 2016
	Americas	$23,208	$26,728	$37,460	$37,622	$37,487	$37,541	$37,433	$37,487	$37,541	$37,636	$38,136	$38,380	$426,657
	Corporate	$8,242	$9,497	$13,312	$13,366	$13,325	$13,352	$13,312	$13,325	$13,352	$13,379	$13,569	$13,664	$151,695
	Educational	$8,472	$9,750	$13,664	$13,704	$13,677	$13,691	$13,650	$13,677	$13,691	$13,731	$13,907	$13,988	$155,602
	Government	$6,494	$7,481	$10,484	$10,552	$10,484	$10,498	$10,471	$10,484	$10,498	$10,525	$10,660	$10,728	$119,360
	Asia	$25,918	$29,844	$41,829	$41,951	$41,870	$41,911	$41,789	$41,843	$41,911	$42,019	$42,560	$42,844	$476,289
	Corporate	$9,350	$10,763	$15,084	$15,125	$15,098	$15,111	$15,071	$15,084	$15,111	$15,152	$15,341	$15,449	$171,738
	Educational	$7,977	$9,185	$12,879	$12,920	$12,892	$12,906	$12,865	$12,879	$12,906	$12,933	$13,109	$13,190	$146,642
	Government	$8,591	$9,896	$13,866	$13,907	$13,880	$13,894	$13,853	$13,880	$13,894	$13,934	$14,110	$14,205	$157,910
	Europe	$21,000	$24,156	$33,848	$33,929	$33,875	$33,915	$33,848	$33,875	$33,915	$33,983	$34,429	$34,646	$385,420
	Corporate	$8,352	$9,594	$13,434	$13,447	$13,447	$13,461	$13,461	$13,447	$13,461	$13,474	$13,650	$13,745	$152,972
	Educational	$7,108	$8,182	$11,472	$11,513	$11,486	$11,499	$11,458	$11,472	$11,499	$11,526	$11,675	$11,743	$130,632
	Government	$5,540	$6,380	$8,942	$8,969	$8,942	$8,956	$8,929	$8,956	$8,956	$8,983	$9,105	$9,159	$101,815
	Grand Total	$70,126	$80,728	$113,137	$113,502	$113,232	$113,367	$113,069	$113,205	$113,367	$113,638	$115,126	$115,870	$1,288,366
You are presented with a data file that includes sales to date by product, customer type etc.  You have actuals to date as well as the yearly forecast.  You have to develop a dashboard that indicates whether they will hit their year end target, and provide sufficient analysis and visual presentation for the sales executives to be able to understand why, or why not.
You must use at least 3 of the functions we have used this semester.
Present results by quarter.
forecast
	Forecast detail
	Region	Customer Type	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	FY 2016
	Americas	Corporate	$   8,242	$   9,497	$   13,312	$   13,366	$   13,325	$   13,352	$   13,312	$   13,325	$   13,352	$   13,379	$   13,569	$   13,664	$   151,695
	Americas	Educational	$   8,472	$   9,750	$   13,664	$   13,704	$   13,677	$   13,691	$   13,650	$   13,677	$   13,691	$   13,731	$   13,907	$   13,988	$   155,602
	Americas	Government	$   6,494	$   7,481	$   10,484	$   10,552	$   10,484	$   10,498	$   10,471	$   10,484	$   10,498	$   10,525	$   10,660	$   10,728	$   119,360
	Europe	Corporate	$   8,352	$   9,594	$   13,434	$   13,447	$   13,447	$   13,461	$   13,461	$   13,447	$   13,461	$   13,474	$   13,650	$   13,745	$   152,972
	Europe	Educational	$   7,108	$   8,182	$   11,472	$   11,513	$   11,486	$   11,499	$   11,458	$   11,472	$   11,499	$   11,526	$   11,675	$   11,743	$   130,632
	Europe	Government	$   5,540	$   6,380	$   8,942	$   8,969	$   8,942	$   8,956	$   8,929	$   8,956	$   8,956	$   8,983	$   9,105	$   9,159	$   101,815
	Asia	Corporate	$   9,350	$   10,763	$   15,084	$   15,125	$   15,098	$   15,111	$   15,071	$   15,084	$   15,111	$   15,152	$   15,341	$   15,449	$   171,738
	Asia	Educational	$   7,977	$   9,185	$   12,879	$   12,920	$   12,892	$   12,906	$   12,865	$   12,879	$   12,906	$   12,933	$   13,109	$   13,190	$   146,642
	Asia	Government	$   8,591	$   9,896	$   13,866	$   13,907	$   13,880	$   13,894	$   13,853	$   13,880	$   13,894	$   13,934	$   14,110	$   14,205	$   157,910
	Total Sales		$   70,126	$   80,728	$   113,137	$   113,502	$   113,232	$   113,367	$   113,069	$   113,205	$   113,367	$   113,638	$   115,126	$   115,870	$   1,288,366
Dashboard
		Year to Date 
		Month	Forecast Sales 	Actuals Sales	Variance in Sales (Favourable/(Unfavourable)
		Jan	$70,126	$77,509	$7,384
		Feb	$80,728	$84,365	$3,637
		Mar	$113,137	$91,143	($21,994)
		Apr	$113,502	$97,892	($15,611)
		May	$113,232	$104,837	($8,395)
		Jun	$113,367	$111,976	($1,391)
		Jul	$113,069	$118,829	$5,760
		Aug	$113,205	$112,765	($440)
		Sep	$113,367	$106,485	($6,882)
		Oct Trend	$113,638	$123,191	$9,553
		Nov Trend	$115,126	$127,700	$12,575
		Dec Trend	$115,870	$132,210	$16,340
		Total Monthly Sales	$1,288,366	$1,288,902	$536
		Month	Cum Forecast Sales	Cum Actual + Trend Sales
		Jan	$70,126	$77,509
		Feb	$150,854	$161,874
		Mar	$263,991	$253,017
		Apr	$377,493	$350,909
		May	$490,725	$455,746
		Jun	$604,092	$567,722
		Jul	$717,161	$686,551
		Aug	$830,366	$799,316
		Sep	$943,733	$905,801
		Oct Trend	$1,057,371	$1,028,992
		Nov Trend	$1,172,496	$1,156,692
		Dec Trend	$1,288,366	$1,288,902
		Total Monthly Sales		$2,577,804
		Chart 2 Region Wise Sales -Forecast vs Actual 
			Forecast Sales 			Actual Sales
		Month	Americas	Corporate	Educational	Americas	Corporate	Educational
		Jan	$23,208	$25,918	$21,000	$26,550	$22,364	$28,596
		Feb	$26,728	$29,844	$24,156	$28,854	$24,399	$31,111
		Mar	$37,460	$41,829	$33,848	$31,168	$26,427	$33,548
		Apr	$37,622	$41,951	$33,929	$33,445	$28,342	$36,105
		May	$37,487	$41,870	$33,875	$35,795	$30,305	$38,737
		Jun	$37,541	$41,911	$33,915	$38,268	$32,376	$41,332
		Jul	$37,433	$41,789	$33,848	$40,618	$34,403	$43,808
		Aug	$37,487	$41,843	$33,875	$38,564	$32,672	$41,529
		Sep	$37,541	$41,911	$33,915	$36,414	$30,891	$39,180
		Oct Trend	$37,636	$42,019	$33,983	$42,101	$35,707	$45,383
		Nov Trend	$38,136	$42,560	$34,429	$43,639	$37,022	$47,039
		Dec Trend	$38,380	$42,844	$34,646	$45,178	$38,337	$48,695
		Chart 3 Customer Type - Forecast vs Actual 
			Forecast Sales 			Actual Sales
		Month	Corporate	Educational	Government	Corporate	Educational	Government
		Jan	$25,943	$23,557	$20,625	$26,156	$20,823	$30,530
		Feb	$29,854	$27,117	$23,757	$28,503	$22,607	$33,255
		Mar	$41,829	$38,014	$33,293	$30,802	$24,453	$35,

Analysis Dashboards Forecast Actuals Months Cum forecast Cum actuals + trend 1 Jan $ 82,280 XXXXXXXXXX Jan $ 82,280 $ 81,710 2 Feb 82,880 XXXXXXXXXX Feb $ 165,160 $ 163,827 these are the monthly...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment