1 / 56

MSOffice Excel – Part 4 Analyzing and Charting Financial Data

MSOffice Excel – Part 4 Analyzing and Charting Financial Data. New Perspectives on Microsoft Office 2013. XP. Objectives • Use the PMT function to calculate a loan payment • Create an embedded pie chart • Apply styles to a chart • Add data labels to a pie chart

laddie
Download Presentation

MSOffice Excel – Part 4 Analyzing and Charting Financial Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MSOffice Excel – Part 4 Analyzing and Charting Financial Data NewPerspectivesonMicrosoftOffice2013

  2. XP Objectives •UsethePMTfunctiontocalculatealoan payment •Createanembeddedpiechart •Applystylestoachart •Adddatalabelstoapiechart •Formatachartlegend •Createaclusteredcolumnchart •Createastackedcolumnchart NewPerspectivesonMicrosoftExcel2013 2

  3. Objectives XP • • • • • • • Createalinechart Createacombinationchart Formatchartelements Modifythechart’sdatasource Addsparklinestoaworksheet Formatcellswithdatabars Insertawatermark NewPerspectivesonMicrosoftExcel2013 3

  4. VisualOverview:Session4.1 XP NewPerspectivesonMicrosoftExcel2013 4

  5. ChartElements XP NewPerspectivesonMicrosoftExcel2013 5

  6. IntroductiontoFinancialFunctionsXP •Excelprovidesawiderangeoffinancialfunctions relatedtoloansandinvestments. •OneoftheseisthePMTfunction,whichcanbeused tocalculatetheinstallmentpaymentandpayment schedulerequiredtocompletelyrepayaloan. •Otherloanfunctionsincludefuturevalue,present value,calculatingtheinterestpartofapayment, calculatingtheprinciplepartofapayment,andthe loaninterestrate. NewPerspectivesonMicrosoftExcel2013 6

  7. FinancialFunctionsforLoansand Investments XP NewPerspectivesonMicrosoftExcel2010 7

  8. WorkingwithFinancialFunctionsXP •Costofaloantotheborrowerislargelybased onthreefactors: –Principal:amountofmoneybeingloaned –Interest:amountaddedtotheprincipalby thelender •Calculatedassimpleinterestoras compoundinterest –Timerequiredtopaybacktheloan NewPerspectivesonMicrosoftExcel2010 8

  9. ExplanationofFunctionUse XP NewPerspectivesonMicrosoftExcel2010 9

  10. PMTFunctionVariables XP •Tocalculatethecostsassociatedwithaloan, youmusthavethefollowinginformation: –Theannualinterestrate –Thenumberofpaymentperiodsperyear –Thelengthoftheloanintermsofthetotalnumber ofpaymentperiods –Theamountbeingborrowed –Whenloanpaymentsaredue NewPerspectivesonMicrosoftExcel2010 10

  11. UsingthePMTFunction XP NewPerspectivesonMicrosoftExcel2010 11

  12. UsingthePMTFunction XP NewPerspectivesonMicrosoftExcel2010 12

  13. ExcelCharts XP •Chartsshowtrendsorrelationshipsindata thatareeasiertoseeinagraphic representationratherthanviewingtheactual numbersordata. •Whencreatingachart,rememberthatyour goalistoconveyimportantinformationthat wouldbemoredifficulttointerpretfrom columnsofdatainaworksheet. NewPerspectivesonMicrosoftExcel2013 13

  14. ChoosingtheRightChart XP NewPerspectivesonMicrosoftExcel2013 14

  15. CommunicatingEffectivelywith Charts XP • • • • • • Keepitsimple Focusonthemessage Limitthenumberofdataseries Usegridlinesinmoderation Choosecolorscarefully Limitcharttoafewtextstyles NewPerspectivesonMicrosoftExcel2013 15

  16. 4StepsforCreatingExcelCharts XP •Selecttherangecontainingthedatayouwanttochart. •OntheINSERTtab,intheChartsgroup,clickthe RecommendedChartbuttonoracharttypebutton,andthen clickthechartyouwanttocreate(orclicktheQuick •Analysisbutton,clicktheCHARTScategory,andthenclickthe chartyouwanttocreate). •OntheCHARTTOOLSDESIGNtab,intheLocationgroup,click theMoveChartbutton,selectwhethertoembedthechartin aworksheetorplaceitinachartsheet,andthenclicktheOK button. NewPerspectivesonMicrosoftExcel2013 16

  17. CreatinganExcelChart •Selectarangetouseaschart’sdatasource XP NewPerspectivesonMicrosoftExcel2013 17

  18. CreatinganExcelChart •Selectcharttypethatbestrepresentsthedata –Useoneof53built-inchartsorganizedinto10 categories,or… –Createcustomcharttypesbasedonbuilt-ins XP NewPerspectivesonMicrosoftExcel2013 18

  19. InsertingaPieChartwiththe XP QuickAnalysisTool •Afteryouselectanadjacentrangetouseasa chart’sdatasource,theQuickAnalysistool appears.Itincludesacategoryforcreating charts.TheCHARTcategorylists recommendedcharttypes,whicharethe chartsthataremostappropriateforthedata sourceyouselected. NewPerspectivesonMicrosoftExcel2013 19

  20. TocreateapiechartwiththeQuick Analysistool •Makesurethecorrectrangeisselected. •ClicktheQuickAnalysisbuttoninthelower- rightcorneroftheselectedrange •ClicktheCHARTScategory. –Thecharttypesyouwillmostlikelywanttouse withtheselecteddatasourcearelisted. •ClickPietoselectthepiechart. XP NewPerspectivesonMicrosoftExcel2013 20

  21. CHARTSCategoryoftheQuick AnalysisTool XP NewPerspectivesonMicrosoftExcel2013 21

  22. MovingandResizingCharts XP •Excelchartsareeitherplacedintheirownchart sheetsorembeddedinaworksheet. •Whenyoucreateachart,itisembeddedinthe worksheetthatcontainsthedatasource. •Selectingthechartdisplaysaselectionbox(usedto moveorresizetheobject) –Tomovethechart,dragselectionboxtonew locationinworksheet –Toresizethechart,dragasizinghandle NewPerspectivesonMicrosoftExcel2013 22

  23. ChoosingaChartStyle XP •Recallthatastyleisacollectionofformats thataresavedwithanameandcanthenbe appliedatonetime. •Inachart,theformatofthecharttitle,the locationofthelegend,andthecolorsofthe pieslicesareallpartofthedefaultchartstyle. •Youcanquicklychangetheappearanceofa chartbyselectingadifferentstylefromthe ChartStylesgallery. NewPerspectivesonMicrosoftExcel2013 23

  24. DesigningaPieChart •Chooselocationofthelegend,andformatit usingtoolsonChartToolsLayouttab XP NewPerspectivesonMicrosoftExcel2013 24

  25. FormattingthePieChartLegend XP •Youcanfine-tuneachartstylebyformatting individualchartelements.FromtheChart Elementsbutton,youcanopenasubmenufor eachelementthatincludesformatting options,suchastheelement’slocationwithin thechart. •YoucanalsoopenaFormatpane,whichhas moreoptionsforformattingtheselectedchart element. NewPerspectivesonMicrosoftExcel2013 25

  26. FormattedChartLegend XP NewPerspectivesonMicrosoftExcel2013 26

  27. FormattingPieChartDataLabels XP NewPerspectivesonMicrosoftExcel2013 27

  28. FormattingtheChartArea XP •Thechart’sbackground,whichiscalledthe chartarea,canalsobeformattedusingfill colors,borderstyles,andspecialeffectssuch asdropshadowsandblurrededges. •Thechartareafillcolorusedinthepiechartis white,whichblendsinwiththeworksheet background. NewPerspectivesonMicrosoftExcel2013 28

  29. DesigningaPieChart XP •Explodedpiecharts –Moveonesliceawayfromtheothers –Usefulforemphasizingonecategoryabove alloftheothers NewPerspectivesonMicrosoftExcel2013 29

  30. PerformingWhat-IfAnalysesand FilteringwithCharts •Achartislinkedtoitsdatasource,andas XP changesaremadetothedatasourcethe changestranslatetothechartallowingavisual representationoftheWhat-ifchanges. •Filteringisanothertypeofwhat-ifanalysis thatlimitsthedatatoasubsetoftheoriginal valuesinaprocess. NewPerspectivesonMicrosoftExcel2013 30

  31. CreatingaColumnChart XP •Columnchart –Displaysvaluesindifferentcategoriesas columns –Heightofeachcolumnisbasedonitsvalue •Barchart –Columnchartturnedonitsside –Lengthofeachbarisbasedonitsvalue NewPerspectivesonMicrosoftExcel2013 31

  32. FilteredPieChart XP NewPerspectivesonMicrosoftExcel2013 32

  33. ChartsvsPieCharts XP •Column/barchartsaresuperiortopiecharts –Forlargenumberofcategoriesorcategoriesclose invalue –Easiertocompareheightorlengththanarea –Canbeappliedtowiderrangeofdata –Canincludeseveraldataseries(piechartsusually showonlyonedataseries) NewPerspectivesonMicrosoftExcel2013 33

  34. ComparingColumnChartSubtypes XP NewPerspectivesonMicrosoftExcel2013 34

  35. InsertingaColumnChart XP • • • • Selectdatasource Selecttypeofcharttocreate Moveandresizethechart Changechart’sdesign,layout,andformatby: –Selectingoneofthechartstyles,or –Formattingindividualchartelements NewPerspectivesonMicrosoftExcel2013 35

  36. MovingaCharttoaDifferent Worksheet •MoveChartdialogboxprovidesoptionsfor movingcharts XP NewPerspectivesonMicrosoftExcel2013 36

  37. EditingtheAxisScaleandText XP •Rangeofvalues(scale)ofanaxisisbasedon valuesindatasource •Vertical(value)axis:rangeofseriesvalues •Horizontal(category)axis:categoryvalues •Primaryandsecondaryaxescanusedifferent scalesandlabels •Adddescriptiveaxistitlesifaxislabelsarenot self-explanatory(defaultisnotitles) NewPerspectivesonMicrosoftExcel2013 37

  38. ChangingandFormattingaChartXP Title NewPerspectivesonMicrosoftExcel2013 38

  39. Session4.2VisualOverview XP NewPerspectivesonMicrosoftExcel2013 39

  40. Charts,Sparklines,andDataBarsXP NewPerspectivesonMicrosoftExcel2013 40

  41. AddingSparklinesandDataBars •Bothconveygraphicalinformationabout worksheetdatawithoutoccupyingalotof space XP NewPerspectivesonMicrosoftExcel2013 41

  42. CreatingSparklines XP •Aminichartdisplayedwithinaworksheetcell •Compactinsize;doesn’tincludechart elements •Goalistoconveymaximumamountof graphicalinformationinaverysmallspace •Canbegroupedorungrouped –Groupedsparklinesshareacommonformat –Ungroupedsparklinescanbeformatted individually NewPerspectivesonMicrosoftExcel2013 42

  43. XP TypesofSparklines •Linesparkline –Highlightstrends •Columnsparkline –Forcolumncharts •Win/Losssparkline –Highlightspositiveand negativevalues NewPerspectivesonMicrosoftExcel2013 43

  44. AddingandFormattingSparkline Markers XP •Canspecifyonlylinecolorandmarkercolor •Cancreatelinemarkersforhighestvalue, lowestvalue,allnegativevalues,firstvalue, andlastvalue •Cancreatemarkersforalldatapoints regardlessofvalueorpositionindatasource •Canaddanaxistoasparkline–horizontalline thatseparatespositiveandnegativevalues NewPerspectivesonMicrosoftExcel2013 44

  45. CreatingaLineChart XP •Usewhendataconsistsofvaluesdrawnfrom categoriesthatfollowasequentialorderat evenlyspacedintervals •Displaysdatavaluesusingaconnectedline ratherthancolumnsorbars NewPerspectivesonMicrosoftExcel2013 45

  46. EditingtheScaleoftheVertical Axis XP NewPerspectivesonMicrosoftExcel2013 46

  47. FormattingtheChartColumns •Columnsusuallyhaveacommonformat– distinguishedbyheight,notcolor XP NewPerspectivesonMicrosoftExcel2013 47

  48. WorkingwithColumnWidths XP NewPerspectivesonMicrosoftExcel2013 48

  49. FormattingDataMarkers XP NewPerspectivesonMicrosoftExcel2013 49

  50. FormattingthePlotArea XP NewPerspectivesonMicrosoftExcel2013 50

More Related