會計數(shù)據(jù)分析 Solutions-Manual Chapter-7-Labs-SM

Copyright?2019McGraw-HillEducation.Allrightsreserved.NoreproductionordistributionwithoutthepriorwrittenconsentofMcGraw-HillEducation.

CHAPTER7LABS

Lab7-1:EvaluatemanagementrequirementsandidentifyusefulKPIsfromalist.

Lab7-2:CreateabalancedscorecarddashboardinTableau

Lab7-3:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel

Lab7-4:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartII)

Lab7-5:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartIII)

Lab7-6:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartIV)

Lab7-1EvaluatemanagementrequirementandidentifyusefulKPIsfromalist.

Keyperformanceindicatorshelpmanagerskeeptrackofperformanceandstrategicobjectives.

BernardMarrcameupwithalistof75KPIsthathebelieveseverymanagerneedstoknow.Source:

/pulse/20130905053105-64875646-the-75-kpis-every-manager-needs-to-know/

(Accessed10/13/2017)

Inthislab,youwill:

Learnaboutmanyofthekeyperformanceindicators.

EvaluatewhichKPIsbestworkforTesla

ConsiderthedataneededandthedesiredfrequencytoprovideeachoftheseKPIs.

Q1.ImagineyouworkforTesla,choose20KPIsthatyoubelieveismostimportanttoTesla’smanagement(includingfivefromeachcategory).

Answerswillvary.

(Level2Header)Part1:Identifythequestions

Foreachofthese20KPIs:

Q2.Identifythespecificequation/relationship/dataneededtocalculatetheKPI.Ifyouneedfrequentdata,howfrequent?

Answerswillvary.

Q3.Describeasimplevisualization,ordashboardthatwouldhelpamanagertracktheKPI.Isitred,yellowandgreenindicators,ordoyouhavesomethingelseinmindthatwouldbebetter?

Answerswillvary.

(Level2Header)Part2:Masterthedata

Q4.IdentifyabenchmarkforfiveoftheseKPIsforTesla–howwouldyousetit?BasedonaveragesforTeslaoronperformanceoneweekoronemonthoroneyearearlier?Forthecarindustryoradifferentone?

Answerswillvary.

ENDOFLAB

Lab7-2CreateaBalancedScorecardDashboardinTableau

Superstorehasbroughtyouintohelpthemdevelopsomemetricstoevaluateperformanceacrossdifferentdimensionsoftheirbusiness,includingfinance,customers,process,andemployeegrowth.

CompanySummary

Superstoreisalargesellerofretailandwholesaleofficesupplies,furnitureandtechnology.TheyoperateintheUnitedStatesandhavedividedtheirsalesregionsintoNorth,South,East,andWest.Eachregionhasaregionalsalesrepresentativewhointeractswiththecustomerstotakeordersanddealwithreturns.

Data

Salesorderdataisavailablefrom2013-2016.Itincludesdemographicdataaboutthecustomersaswellasmaincategoriesandsub-categoriesofproducts.

Technique

Inthislab,youwilluseTableautogenerateadashboardtoevaluatefourkeyperformanceindicators.

Softwareneeded

Tableau

Inthislab,youwill:

Generatesomekeyperformanceindicators.

Evaluatethedata

Performanalysesandgeneratevisualizations

(Level2Header)Part1:IdentifytheQuestions

YourunderstandingofkeyperformanceindicatorshasgivenyousomeinsightintohowmanagementatSuperstoremightmeasureandevaluateperformanceacrossdifferentaspectsofthebusiness.Theyaredependingonyourexpertisetodojustthat.

Assumingyou’llhaveaccesstosalesorderandreturnsdata,aswellasthesalesrepresentativesinvolved,thinkaboutdifferentwaysyoucouldmeasureperformance.

Q1.WhatKPIswouldyouconsiderusingtoevaluatesalesfinancialperformance?

Answerswillvary.

Q2.WhatKPIswouldyouconsiderusingtoevaluatecustomerrelationships?

Answerswillvary.

Q3.WhatKPIswouldyouconsiderusingtoevaluateprocessefficiency?

Answerswillvary.

Q4.WhatKPIswouldyouconsiderusingtoevaluateemployeegrowth?

Answerswillvary.

Q5.ForeachKPI,identifyabenchmarkvalueorKPIgoalthatyouthinkmanagementmightuse.

Answerswillvary.

(Level2Header)Part2:Generatearequestfordata

Q6.Usingtheavailablefields,identifysomecalculationsorrelationshipsthatwouldsupportyourKPIsfromQ1-Q4.

Answerswillvary.

Q7.ArethereanyKPIsyouselectedthatdon’thavesupportingdatafields?

Answerswillvary.

(Level2Header)Part3:Performananalysisofthedata

TAKEASCREENSHOT(1)

TAKEASCREENSHOT(2)

TAKEASCREENSHOT(3)

TAKEASCREENSHOT(4)

TAKEASCREENSHOT(5)

(Level2Header)Part4:Addressandrefineresults

Q8.Whichproductcategorieshavefallenbelowtheprofitgoalof13%?

Bookcases,chairs,furnishings,tables,andstorage

Q9.Whichstatesaretakingsignificantlymorethan4daystoshipto?

Allofthestatesinredtakelongerthan4days.Ifsignificantlylongerisdefinedby5daysormore,Wyoming,Maine,andtheDistrictofColumbiatakethelongesttoshipto.

Q10.Whichregion(s)haveexperiencedahigherthan10%returnratefromcustomers?

TheWest

Q11.Whichsalesrepresentativeisleadingtherestforthemostrecentmonth?

AnnaAndreadiisleading

ENDOFLAB

(Level1Header)Lab7-3ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel

(Level2Header)Part1:IdentifytheQuestions

Q1:Whywouldcomparingcurrentyearsalestoprioryearsalesbeuseful?

Answerswillvary.Butresponsesmayincludethatcomparingsalestopriorperiodscanhelpbenchmarkprogress.

(Level2Header)Part2:MasteringtheDataandPerformingtheAnalysis

Q2:Whymightyouwanttoeditthestatusthresholds?Does22.4seemlowfortheupperlimit?

Answerswillvary.Responsesmayincludethattheabilitytoeditstatusthresholdsisimportanttohavecontroloverhowdifficultoreasyitshouldbetoreachthethresholds.

Takeascreenshot.

KeyScreenshot:

Q3:HowdidDillard’sperforminSeptember2016comparedtoSeptember2015?Doyouthinkthetargetissettoohighortoolow?Whichday(s)performedtheworst,comparedtothesamedateinthepreviousperiod?Whydoyouthinkthatis?

Answerswillvary.Generally,Dillard’sperformedbetterin2016thanin2015.TheworstwasSeptember2nd.ItispossiblethatSeptember2ndisworstbecausethedataiscomparingthe2ndtothe2nd,notnecessarilyaSaturdaytoaSaturday.Thisdiscrepancymaymakethedatacomparisonslessreliable.

ENDOFLAB

(Level1Header)Lab7-4:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartII)

(Level2Header)Part1:IdentifytheQuestions

(Level2Header)Part2:MastertheData

TakeaScreenshot.

KeyScreenshot:

Toprovidesomedrill-downcapabilities,addtheDayfieldtotheRows(beneathMonth).

Q2:Doyounoticeapatternwithhowfrequentlythe“bad”(redicon)daysappearin2016inrelationto2015?

Answersmayvary,butthepatternisjustaboutevery7thdayin2016ismarkedwitharedsignal.

Q3:Whatdoyouthinkisthepotentialproblemwithcomparingdays(forexample,comparingSept1,2016toSept1,2015)?Howcouldthisbeimproved?

Thereasoningforthisislikelyduetocomparingdirectdates(the1sttothe1st),insteadofcomparingweekdays.

ENDOFLAB

(Level1Header)Lab7-5:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartIII)

Takeascreenshot.

KeyScreenshot:

Q1:Somethingshouldseemabitoffwithyournumbers.Therearesomebigdisparitiesmonthovermonthforsomeweekdays.LookbackoverourqueryandtheERDiagram(andifyoucompletedComprehensiveLab7-2,comparethequeryyouexecutedinthislabtothequeryfromthatlab)–whatdidweleaveoutofthisquery?Howcoulditcauseustomakepoordecisions?

Weleftoutthefilterfortran_type=‘p’.Thiscouldcausethecompanytomakebaddecisionsbecausetherefundswillbefactoredintothevalues,withouttheanalystrealizingit.

(Level2Header)Part4:Analyzeandrefinetheresults

Q2.Whatwouldbethebestwaytovisualizethisdatatoeasedecision-makingandinsight?

Answerswillvary.

ENDOFLAB

(Level1Header)Lab7-6:ComprehensiveCase:Dillard’sStoreData–CreatingKPIsinExcel(PartIV–PuttingitAllTogether)

(Level2Header)Part1: