Analysis Dashboards ForecastActualsMonthsCum forecastCum actuals + trend 1Jan$ 82,280 XXXXXXXXXXJan$ 82,280$ 81,710 2Feb82,880 XXXXXXXXXXFeb$ 165,160$ 163,827 these are the monthly...

1 answer below »
See attached: File# 1 is EXAMPLE; Fie#2 is the actual HW. Need help to do the ACTUAL HW. Thanks..


Analysis Dashboards ForecastActualsMonthsCum forecastCum actuals + trend 1Jan$ 82,28081709.88Jan$ 82,280$ 81,710 2Feb82,88082116.88Feb$ 165,160$ 163,827these are the monthly forecast totals and the actuals for Jan-Sept…and then I trended just the totals 3Mar83,63082407.88Mar$ 248,790$ 246,235When you trend the individual records there isn't enough data for it to be usable. 4Apr83,90082619.88Apr$ 332,690$ 328,855next, calculate monthly cumulative sales 5May83,70082972.88May$ 416,390$ 411,827then you do a chart 6Jun83,80083340.88Jun$ 500,190$ 495,168or two charts, basically showing the monthly 7Jul83,58083569.88Jul$ 583,770$ 578,738forecast v actual as well as the trend over the year 8Aug83,68083943.88Aug$ 667,450$ 662,682Next you need to have more than one drill down into the data, to explain why 9Sep83,80084278.88Sep$ 751,250$ 746,961is it due to product, region, customer type or sales rep? 10Oct84,00084562.49Oct$ 835,250$ 831,523 11Nov85,10084875.86Nov$ 920,350$ 916,399 12Dec85,65085189.22Dec$ 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 TypeProduct JanFebMarAprMayJunJulAugSepOctNovDecFY 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 Cartridges9,94010,01010,10010,13010,11010,12010,09010,11010,12010,15010,28010,340121,500 Toner Cartridges7,6207,6807,7507,8007,7507,7607,7407,7507,7607,7807,8807,93093,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 Paper9,8009,8509,9309,9409,9409,9509,9509,9409,9509,96010,09010,160119,460 Ink Jet Cartridges8,3408,4008,4808,5108,4908,5008,4708,4808,5008,5208,6308,680102,000 Toner Cartridges6,5006,5506,6106,6306,6106,6206,6006,6206,6206,6406,7306,77079,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 Paper10,97011,05011,15011,18011,16011,17011,14011,15011,17011,20011,34011,420134,100 Ink Jet Cartridges9,3609,4309,5209,5509,5309,5409,5109,5209,5409,5609,6909,750114,500 Toner Cartridges10,08010,16010,25010,28010,26010,27010,24010,26010,27010,30010,43010,500123,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. 123456789101112 Row LabelsSum of JanSum of FebSum of MarSum of AprSum of MaySum of JunSum of JulSum of AugSum of SepOct trendNov trendDec trendFY 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 RegionProductCustomer TypeSales RepresentativeJanFebMarAprMayJunJulAugSep31-Jan-1028-Feb-1031-Mar-1030-Apr-1031-May-1030-Jun-1031-Jul-1031-Aug-1030-Sep-1031-Oct-1030-Nov-1031-Dec-10 EastToner CartridgesCorporateNathan Clark$ 827$ 825$ 826$ 827$ 830$ 830$ 852$ 877$ 884$ 827$ 825$ 826$ 827$ 830$ 830$ 852$ 877$ 884ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesCorporateRachel Smith463467465470467468469466464463467465470467468469466464ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesCorporateKaren Winthrop186185186185186188188187190186185186185186188188187190ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesCorporateWalter Jones186187186185185184185185185186187186185185184185185185ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesEducationalNathan Clark337334331337341340341342346337334331337341340341342346ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesEducationalRachel Smith313313315313311317314316313313313315313311317314316313ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesEducationalKaren Winthrop749759768777782800800824823749759768777782800800824823ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesEducationalWalter Jones490495494491495495491506508490495494491495495491506508ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesGovernmentalNathan Clark199201204205203203205207209199201204205203203205207209ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesGovernmentalRachel Smith845845838832839831848847843845845838832839831848847843ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesGovernmentalKaren Winthrop812818813811818829835829829812818813811818829835829829ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastToner CartridgesGovernmentalWalter Jones713712722718729746751758760713712722718729746751758760ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesCorporateNathan Clark739732732733733732745740746739732732733733732745740746ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesCorporateRachel Smith227227230237241240243241239227227230237241240243241239ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesCorporateKaren Winthrop861862854852851850849858881861862854852851850849858881ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesCorporateWalter Jones316317326330331337341350358316317326330331337341350358ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesEducationalNathan Clark879878872879885896896900903879878872879885896896900903ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesEducationalRachel Smith562563559559561558557553556562563559559561558557553556ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesEducationalKaren Winthrop155154153153152152152154157155154153153152152152154157ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesEducationalWalter Jones522525526522520519516518521522525526522520519516518521ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesGovernmentalNathan Clark161162161160162162161161160161162161160162162161161160ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesGovernmentalRachel Smith870886884887888905906899891870886884887888905906899891ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesGovernmentalKaren Winthrop1,0111,0101,0051,0011,0059971,0019951,0101,0111,0101,0051,0011,0059971,0019951,010ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastInk Jet CartridgesGovernmentalWalter Jones905923927926932940932939940905923927926932940932939940ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperCorporateNathan Clark644656652655654661665662659644656652655654661665662659ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperCorporateRachel Smith9739771,0019981,0061,0161,0441,0411,0339739771,0019981,0061,0161,0441,0411,033ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperCorporateKaren Winthrop931949940936937945946967970931949940936937945946967970ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperCorporateWalter Jones132132136135135134133135134132132136135135134133135134ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperEducationalNathan Clark9959941,0231,0141,0151,0301,0301,0351,0399959941,0231,0141,0151,0301,0301,0351,039ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperEducationalRachel Smith647642654649645647645641642647642654649645647645641642ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperEducationalKaren Winthrop174172173178177178177176176174172173178177178177176176ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperEducationalWalter Jones819810810813814816811803803819810810813814816811803803ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperGovernmentalNathan Clark774776772768768765762784785774776772768768765762784785ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperGovernmentalRachel Smith1,0481,0491,0701,0861,0991,0941,0841,0971,1031,0481,0491,0701,0861,0991,0941,0841,0971,103ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperGovernmentalKaren Winthrop1,1051,1041,1051,1261,1291,1531,1421,1441,1461,1051,1041,1051,1261,1291,1531,1421,1441,146ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! EastCopy PaperGovernmentalWalter Jones557557552551548555551550555557557552551548555551550555ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesCorporateNathan Clark584592590585588585589604604584592590585588585589604604ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesCorporateRachel Smith179181180180180181181182181179181180180180181181182181ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesCorporateKaren Winthrop948943939955951948942944949948943939955951948942944949ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesCorporateWalter Jones698693693698709708711712714698693693698709708711712714ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesEducationalNathan Clark299299300308318316316319324299299300308318316316319324ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesEducationalRachel Smith167170170174175179180182184167170170174175179180182184ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesEducationalKaren Winthrop602604622624628623630625624602604622624628623630625624ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesEducationalWalter Jones390399399398394392395400401390399399398394392395400401ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesGovernmentalNathan Clark895921934936938944935944966895921934936938944935944966ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesGovernmentalRachel Smith825836853848844836848852854825836853848844836848852854ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesGovernmentalKaren Winthrop553553549544540547544556551553553549544540547544556551ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthToner CartridgesGovernmentalWalter Jones225223229228230228226228229225223229228230228226228229ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesCorporateNathan Clark661656657654669682675683687661656657654669682675683687ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesCorporateRachel Smith381384396397396395394403401381384396397396395394403401ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesCorporateKaren Winthrop435432429431428439436439439435432429431428439436439439ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesCorporateWalter Jones354356356354351357355364362354356356354351357355364362ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesEducationalNathan Clark361359357356354356354356361361359357356354356354356361ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesEducationalRachel Smith439440449448447447445449445439440449448447447445449445ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesEducationalKaren Winthrop653652646647642647662663668653652646647642647662663668ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesEducationalWalter Jones194198199199200200201201200194198199199200200201201200ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesGovernmentalNathan Clark848859866862866873876875886848859866862866873876875886ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesGovernmentalRachel Smith308309313313314312316317316308309313313314312316317316ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesGovernmentalKaren Winthrop156158157157156158160159162156158157157156158160159162ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthInk Jet CartridgesGovernmentalWalter Jones821830825827822821815830831821830825827822821815830831ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperCorporateNathan Clark718722738740742757765788805718722738740742757765788805ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperCorporateRachel Smith236236235234239238244242243236236235234239238244242243ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperCorporateKaren Winthrop192196196195196195198198198192196196195196195198198198ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperCorporateWalter Jones1,1151,1281,1391,1301,1211,1111,1071,0971,0981,1151,1281,1391,1301,1211,1111,1071,0971,098ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperEducationalNathan Clark199206209206212212217218218199206209206212212217218218ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperEducationalRachel Smith707373747474757474707373747474757474ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperEducationalKaren Winthrop740750757768777793786782790740750757768777793786782790ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperEducationalWalter Jones638644646645644647654658660638644646645644647654658660ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperGovernmentalNathan Clark423427429432432430438439440423427429432432430438439440ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperGovernmentalRachel Smith806804805803820830835828831806804805803820830835828831ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperGovernmentalKaren Winthrop761766777781784805808803802761766777781784805808803802ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! NorthCopy PaperGovernmentalWalter Jones765772767766762781802807811765772767766762781802807811ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesCorporateNathan Clark682685693697697714712709703682685693697697714712709703ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesCorporateRachel Smith9901,0021,0059991,0051,0171,0261,0291,0319901,0021,0059991,0051,0171,0261,0291,031ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesCorporateKaren Winthrop569566563564563564563571581569566563564563564563571581ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesCorporateWalter Jones737748750759769767761772786737748750759769767761772786ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesEducationalNathan Clark946943935944935942937951944946943935944935942937951944ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesEducationalRachel Smith868586868685878788868586868685878788ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesEducationalKaren Winthrop463465462461468466478475474463465462461468466478475474ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesEducationalWalter Jones412422424423423423424424420412422424423423423424424420ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesGovernmentalNathan Clark718721719721714724730742742718721719721714724730742742ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesGovernmentalRachel Smith1,0049959999991,0181,0161,0081,0051,0041,0049959999991,0181,0161,0081,0051,004ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesGovernmentalKaren Winthrop722735731738744740747745740722735731738744740747745740ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthToner CartridgesGovernmentalWalter Jones601604618619632641641638636601604618619632641641638636ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesCorporateNathan Clark389393392389385386386383386389393392389385386386383386ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesCorporateRachel Smith872895926939963958951950970872895926939963958951950970ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesCorporateKaren Winthrop388385387384382390391388388388385387384382390391388388ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesCorporateWalter Jones9879949949889951,0161,0161,0191,0099879949949889951,0161,0161,0191,009ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesEducationalNathan Clark727721729733748757764761759727721729733748757764761759ERROR:#VALUE!ERROR:#VALUE!ERROR:#VALUE! SouthInk Jet CartridgesEducationalRachel
hw-10-example-zwbvnoto.xlsx acuatl-hw-10-sw0mrq3u.xlsx
Answered Same DayMar 20, 2022

Answer To: Analysis Dashboards ForecastActualsMonthsCum forecastCum actuals + trend 1Jan$ 82,280...

Sandeep answered on Mar 21 2022
98 Votes
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...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?