关闭

关闭

封号提示

内容

首页 Financial Analysis With Excel.pdf

Financial Analysis With Excel.pdf

Financial Analysis With Excel.p…

上传者: Trazom 2010-01-07 评分 0 0 0 0 0 0 暂无简介 简介 举报

简介:本文档为《Financial Analysis With Excelpdf》,可适用于哈哈哈哈领域,主题内容包含CHAPTERSpreadsheetBasicsTheterm“spreadsheet”coversawidevarietyofelementsus符等。

CHAPTERSpreadsheetBasicsTheterm“spreadsheet”coversawidevarietyofelementsusefulforquantitativeanalysisofallkindsEssentially,aspreadsheetisasimpletoolconsistingofamatrixofcellsthatcanstorenumbers,text,orformulasThespreadsheet’spowercomesfromitsabilitytorecalculateresultsasyouchangethecontentsofothercellsNolongerdoestheuserneedtodothesecalculationsbyhandoronacalculatorInstead,withaproperlyconstructedspreadsheet,changingasinglenumber(say,asalesforecast)canresultinliterallythousandsofautomaticchangesinthemodelThefreedomandproductivityenhancementprovidedbymodernspreadsheetspresentsanunparalleledopportunityforlearningfinancialanalysisAfterstudyingthischapter,youshouldbeableto:ExplainthebasicpurposeofaspreadsheetprogramIdentifythevariouscomponentsoftheExcelscreenNavigatetheExcelworksheet(entering,correcting,andmovingdatawithintheworksheet)ExplainthepurposeandusageofExcel’sbuiltinfunctionsandmacrofunctionsCreategraphicsandknowhowtoprintandsavefilesinExcelCHAPTER:SpreadsheetBasicsSpreadsheetUsesSpreadsheetstodaycontainbuiltinanalyticalcapabilitiespreviouslyunavailableinasinglepackageUsersoftenhadtolearnavarietyofspecializedsoftwarepackagestodoanyrelativelycomplexanalysisWiththenewestversionsofMicrosoftExcel,userscanperformtasksrangingfromtheroutinemaintenanceoffinancialstatementstomultivariateregressionanalysistoMonteCarlosimulationsofvarioushedgingstrategiesItisliterallyimpossibletoenumerateallofthepossibleapplicationsforspreadsheetsYoushouldkeepinmindthatspreadsheetsareusefulnotonlyforfinancialanalysis,butforanytypeofquantitativeanalysiswhetheryourspecialtyisinmarketing,management,engineering,statistics,oreconomicsForthatmatter,aspreadsheetcanalsoprovevaluableforpersonalusesWithExcelitisafairlysimplemattertobuildaspreadsheettomonitoryourinvestmentportfolio,doretirementplanning,experimentwithvariousmortgageoptionswhenbuyingahouse,keepamailinglist,etcThepossibilitiesarequiteliterallyendlessThemorecomfortableyoubecomewiththespreadsheet,themorevaluableusesyouwillfindAboveall,feelfreetoexperiment!TrynewthingsUsingaspreadsheetcanhelpyoufindsolutionsthatyouneverwouldhaveimaginedonyourownStartingMicrosoftExcelBeforeyoucandoanyworkinExcel,youhavetoruntheprogramInWindows,programsaregenerallystartedbydoubleclickingontheprogram’siconThelocationoftheExceliconwilldependontheorganizationofyoursystemYoumayhavetheExcelicon(left)onthedesktopOtherwise,youcanstartExcelbyclickingtheStartbuttonandthenchoosingMicrosoftExcelfromtheAllProgramsmenuForeasieraccess,youmaywishtocreateaDesktoporTaskbarshortcutTodothisrightclickontheExceliconintheAllProgramsmenuandeitherchooseCreateShortcutordragtheicontotheDesktoporTaskbarRememberthatashortcutisnottheprogramitself,soyoucansafelydeletetheshortcutifyoulaterdecidethatyoudon’tneeditExcelIconSpreadsheetBasicsPartsoftheExcelScreenPartsoftheExcelScreenFIGUREMICROSOFTEXCELTheTitleBarThetitlebaristheareaattheverytopoftheExcelscreenItservesanumberoffunctions:•IdentifiestheprogramasMicrosoftExcelanddisplaysthenameofthecurrentlyactiveworkbook•AppearsbrightlycoloredwhenExcelistheactiveprogram•Canbe“grabbed”withthemousetomovethewindowaroundwithintheWindowsenvironment,ifthewindowisnotmaximizedSpreadsheetBasicsCHAPTER:SpreadsheetBasics•Containsthesystemmenu(leftcorner)andtheminimize,maximize,andclosebuttons(rightcorner)ThesystemmenuprovideschoicesformovingthewindoworchangingitssizeaswellastheabilitytoswitchtoorrunotherprogramsTheminimizebuttonwillcollapsethewindowdowntoaniconatthebottomoftheWindowsscreenwhereitisstillactive,butoutofthewayThemaximizebuttoncausestheprogramtooccupytheentirescreenTheclosebuttonwillexittheprogram•Whendoubleclicked,thetitlebarduplicatesthefunctioningofthemaximizebuttonTheMenuBarFIGURETHEEXCELMAINMENUSThemainmenubarinExcelprovidesaccesstoninemenus,eachofwhichleadstofurtherchoicesTherearetwowaystoselectamenu:clickonthemenuofchoicewiththemouse,orusetheAltkeyincombinationwiththeunderlinedletterinthemenunameForexample,tochoosetheFilemenu,youcouldeitherclickontheword“File”orpressAltFonthekeyboardEithermethodwillleadtotheFilemenudroppingdown,allowingyoutomakeanotherchoiceInWindows,menusarepersistent,meaningthattheystayvisibleonthescreenuntilyoueithermakeaselectionorcancelthemenubypressingtheEsckeyWhilethemenuisvisible,youmayuseeitherthearrowkeysorthemousetoselectafunctionAttimes,somemenuselectionsaredisplayedinalightgraycolor(grayed)TheseoptionsarenotavailableforselectionatthetimethatthemenuisselectedForexample,ifyouhavenotcutorcopiedacell,thePasteoptionfromtheEditmenuhasnothingtopaste,soitisgrayedOnlythemenuoptionsdisplayedinblackmaybeselectedRefertoAppendixAforashortdescriptionofeachmenuselectionSpreadsheetBasicsPartsoftheExcelScreenTheToolbarsFIGUREMOSTCOMMONEXCELTOOLBARSImmediatelybelowthemenubar,ExceldisplaysaseriesofshortcutbuttonsonToolbarsTheexactbuttons,andtheirorder,maybedifferentondifferentmachinesThebuttonsprovideaquickwaytocarryoutcertaincommandswithoutwadingthroughmenusanddialogboxesToadd,delete,orrearrangebuttonschooseViewToolbarsCustomizefromthemenusYoucanlearnwhatfunctioneachbuttonperformsbysimplymovingthemousepointeroverabuttononaToolbarAfterafewseconds,amessagewillappearthatinformsyouofthebutton’sfunctionThismessageisknownasaToolTipToolTipsareusedfrequentlybyExceltohelpyoutoidentifythefunctionofvariousitemsonthescreenNotethatyoucanmoveaToolbar,ormakeitfloatovertheworksheet,byclickingonablankareaoftheToolbaranddraggingittothenewlocationDroppingitovertheworksheetareawillleaveitfloatingTheToolbarwillstaywhereveryoudropit,evenafterexitingandrestartingExcelTheFormulaBarFIGURETHEEXCELFORMULABARTheformulabardisplaysinformationaboutthecurrentlyselectedcellTheleftpartoftheformulabarindicatesthenameoftheselectedcellTherightpartoftheformulabardisplaysthecontentsoftheselectedcellIfthecellcontainsaformula,theformulabardisplaystheformula,andthecelldisplaystheresultoftheformulaSpreadsheetBasicsCHAPTER:SpreadsheetBasicsThefxbuttonontheformulabarisusedtoshowtheInsertFunctiondialogboxThisdialogboxhelpsyoutoenterfunctionswithouthavingtomemorizethemSeepageformoreinformationTheWorksheetAreaTheworksheetareaiswheretherealworkofthespreadsheetisdoneTheworksheetisamatrix(columnsand,rows)ofcells,eachofwhichcancontaintext,numbers,formulas,orgraphicsEachcellisreferredtobyacolumnletterandarownumberColumnletters(A,B,C,,IV)arelistedatthetopofeachcolumn,andrownumbers(,,,,)arelistedtotheleftofeachrowThecellintheupperleftcorneroftheworksheetisthereforereferredtoascellA,thecellimmediatelybelowAisreferredtoascellA,thecelltotherightofAiscellB,andsoonThisnamingconventioniscommontoallspreadsheetsandwillbecomecomfortableonceyouhavepracticedabitTheactivecell(theoneintowhichanyinputwillbeplaced)canbeidentifiedbyasolidblackborderaroundthecellNotethattheactivecellisnotalwaysvisibleonthescreen,butitisalwaysnamedintheleftmostportionofformulabarSheetTabsFIGURETHESHEETTABSExcelworksheetsarestoredinaformatwhichcombinesmultipleworksheetsintoonefileknownasaworkbookThisallowsseveralrelatedworksheetstobecontainedinonefileThesheettabs,nearthebottomofthescreen,allowyoutoswitchbetweensheetsinaworkbookYoumayrename,copy,ordeleteanyexistingsheetorinsertanewsheetbyclickingatabwithyourrightmousebuttonandmakingachoicefromtheresultingmenuYoucaneasilychangetheorderofthesheettabsbyleftclickingatabanddraggingittoanewpositionItiseasytodoanyoftheseoperationsonmultipleworksheetsatonce,exceptrenamingSimplyclickthefirstsheetandthenCtrlclickeachoftheothers(Youcanselectacontiguousgroupofsheetsbyselectingthefirst,andthenShiftclickthelast)Now,rightclickoneoftheselectedsheetsandselecttheappropriateoptionfromthepopupmenuWhensheetsaregrouped,anythingyoudotooneSpreadsheetBasicsNavigatingtheWorksheetsheetgetsdonetoallThisisusefulif,forexample,youneedtoenteridenticaldataintomultiplesheetsorneedtoperformidenticalformattingonseveralsheetsToungroupthesheets,eitherclickonanynongroupedsheetorrightclickasheettabandchooseUngroupSheetsfromthepopupmenuAnewfeatureinExcelallowsyoutochooseacolorforeachtabTodothisjustrightclickthetabandchooseTabColorfromthepopupmenuTheVCRstylebuttonstotheleftofthesheettabsarethesheettabcontrolbuttonstheyallowyoutoscrollthroughthelistofsheettabsRightclickingonanyoftheVCRstylebuttonswilldisplayapopupmenuthatallowsyoutoquicklyjumptoanysheettabintheworkbookThisisespeciallyhelpfulwhenyouhavetoomanytabsforthemalltobeshownStatusBarFIGURETHESTATUSBARThestatusbarcontainsinformationregardingthecurrentstateofExcel,aswellascertainmessagesForexample,mostofthetimetheonlymessageis“Ready”indicatingthatExceliswaitingforinputAtothertimes,Excelmayadd“Calculate”tothestatusbartoindicatethatitneedstorecalculatetheworksheetbecauseofchangesYoucanalsodirectExceltodocertaincalculationsonthestatusbarForexample,inFigureExceliscalculatingtheaverageofthecellsthatarehighlightedintheworksheetByrightclickingonthestatusbaryoucanalsogetExceltocalculatethesum,count,minimum,ormaximumofanyhighlightedcellsThisisusefulwhenyouneedaquickcalculation,butitdoesn’tneedtobeintheworksheetTherightsideofthestatusbarshowsiftheNumLockorScrollLockkeysareonNavigatingtheWorksheetTherearetwoprinciplemethodsformovingaroundwithintheworksheetarea:thearrowkeysandthemouseGenerallyspeaking,forsmalldistancesthearrowkeysprovideaneasymethodofchangingtheactivecell,butmovingtomoredistantcellsisusuallyeasierwiththemouseSpreadsheetBasicsCHAPTER:SpreadsheetBasicsMostkeyboardshaveaseparatekeypadcontainingarrowspointingup,down,left,andrightIfyourkeyboarddoesnot,thenthenumerickeypadcanbeusediftheNumLockfunctionisoff(youwillseethewordNUMinthestatusbarifNumLockison)Tousethearrowkeys,simplypresstheappropriatekeyonceforeachcellthatyouwishtomoveacrossForexample,assumingthatthecurrentcellisAandyouwishtomovetocellD,simplypresstheRightarrowkeythreetimesTomovefromDtoDpresstheDownarrowkeyfourtimesYoucanalsousetheTabkeytomoveonecelltotherightThemouseiseveneasiertouseWhilethemousepointerisovertheworksheetareaitwillbeintheshapeofafatcross(seeFigure)TochangetheactivecellmovethemousepointeroverthedestinationcellandclicktheleftbuttonTomovetoacellthatisnotcurrentlydisplayedonthescreen,clickonthescrollbarsuntilthecellisvisibleandthenclickonitForexample,iftheactivecellisAandyouwishtomakeAtheactivecell,merelyclickonthearrowatthebottomofthescrollbarontherighthandpartofthescreenuntilAisvisibleMovethemousepointerovercellAandclickwiththeleftbuttonEachclickonthescrollbarmovestheworksheetupordownonepageIfyouwishtomoveup,clickabovethethumbIfdown,clickbeneaththethumbThethumbisthesmallbuttonthatmovesupanddownthescrollbartoindicateyourpositionintheworksheetTomovemorequickly,youcandragthethumbtothedesiredpositionIfyouknowthenameoraddressofthecelltowhichyouwishtomove(forlargeworksheetsrememberingthecelladdressisn’teasy,butyoucanusenamedranges)usetheGoTocommandTheGoTocommandwillchangetheactivecelltowhatevercellyouindicateTheGoTodialogboxcanbeusedbychoosingtheEditmenuandthentheGoTocommand,bypressingtheFfunctionkey,orbypressingtheCtrlGkeycombinationTomovetocellA,simplypressF,type:AintheReferencebox,andthenpressEnterYouwillnoticethatcellAisnowhighlightedandvisibleonthescreenYoucanalsouseGoTotofindcertainspecialcells(eg,thelastcellthathasdatainit)bypressingtheSpecialbuttonintheGoTodialogboxSelectingaRangeofCellsManytimesyouwillneedtoselectmorethanonecellatatimeForexample,youmaywishtoapplyaparticularnumberformattoawholerangeofcells,oryoumightwishtoclearawholerangeSinceitwouldbecumbersometodothisonecellatatime,especiallyforalargerange,ExcelallowsyoutosimultaneouslyselectawholerangeandperformvariousfunctionsonallofthecellsatonceTheeasiestwaytoselectacontiguousrangeofcellsistousethemouseSimplypointSpreadsheetBasicsNavigatingtheWorksheettothecellintheupperleftcorneroftherange,clickandholddowntheleftbutton,anddragthemouseuntiltheentirerangeishighlightedAsyoudragthemouse,watchtheleftsideoftheformulabarExcelwillinformyouofthenumberofselectedrowsandcolumnsYoucanalsousethekeyboardtoselectarangeFirstchangetheactivecelltotheupperleftcorneroftherangetobeselected,pressandholddowntheShiftkey,andusethearrowkeystohighlighttheentirerangeNotethatifyoureleasetheShiftkeywhilepressinganarrowkeyyouwilllosethehighlightAveryusefulkeyboardshortcutistheShiftCtrlArrow(anyarrowkeywillwork)combinationThisisusedtoselectallofthecellsfromtheactivecellupto,butnotincluding,thefirstblankcellForexample,ifyouhavenumbersinacolumnandneedtoapplyaformat,justselectthefirstcellandthenpressShiftCtrlDownArrowtoselectthemallThisisfasterandmoreaccuratethanusingthemouseManytimesitisalsousefultoselectadiscontiguousrange(ie,twoormoreunconnectedranges)ofcellsTodothis,simplyselectthefirstrangeasusual,andthenholddowntheCtrlkeyasyouselecttheotherrangesTheabilitytoselectcellsinExceliscrucialbecauseExcel,likemostotherWindowsapplications,worksinthe“select,thenact”mode(Intheolddays,usersofDOSprogramswerefamiliarwiththe“act,thenselect”methodofoperation)InExcel,youfirstselectthecellsthatyouwishtoacton,thenchoosetheoperation(eg,EditCopy)thatyouwanttoperformThiswouldseemtobeaminorpoint,butitisactuallyabigproductivityimprovementInthe“select,thenact”method,thecellsstayselectedaftertheoperationhasbeenperformed,therebyallowinganotheroperationonthosecellswithoutreselectingthemUsingNamedRangesAnamedrangeisacell,orgroupofcells,forwhichyouhavesuppliedanameRangenamescanbeusefulinanumberofdifferentways,butlocatingarangeonabigworksheetisprobablythemostcommonuseTonamearangeofcells,startbyselectingtherangeForexample,selectA:CandthenchooseInsertNameDefinefromthemenusIntheeditboxatthetopoftheDefineNamedialogbox,enteraname,sayMyRange(notethatarangenamecannotcontainspacesormostspecialcharacters)Now,clicktheAddbuttonandtherangeisnamedFigureSpreadsheetBasicsCHAPTER:SpreadsheetBasicsshowshowthedialogboxshouldlookNotethatatthebottomtheRefersToeditboxshowstheaddresstowhichthenamerefersFIGURETHEDEFINENAMEDIALOGBOXOncetherangeisnamed,youcanselectitusingtheGoTocommandThenamewillappearinthelistontheGoTodialogboxAnevenfastermethodistousetheNameBoxontheleftsideoftheformulabarSimplydropthelistandchoosethenamedrangethatyouwishtoselectNamedrangescanalsobeusedinformulasinplaceofcelladdresses,andcanbeusedintheChartSeriesfunctionforchartsAsusefulastheycanbeattimes,thereisnorequirementtousethemEnteringTextandNumbersEachcellinanExcelworksheetcanbethoughtofasaminiaturewordprocessorTextcanbeentereddirectlyintothecellandthenformattedinavarietyofwaysToenteratextstring,firstselectthecellwhereyouwantthetexttoappearandthenbegintypingItisthatsimpleNotethatthenameisactuallydefinedasaformulaThissubtlepointisimportantforsomeofthemoreadvancedusesofnamedrangesForexample,therangeofcellsreferredtobythenamecanbemadetochangeautomaticallydependingoncircumstancesSpreadsheetBasicsNavigatingtheWorksheetExcelissmartenoughtoknowthedifferencebetweennumbersandtext,sotherearenoextrastepsforenteringnumbersLet’strythefollowingexampleofenteringnumbersandtextintotheworksheetMovethecellpointertocellA(usingthearrowkeys,mouse,ortheGoTocommand)andtype:MicrosoftCorporationSalesIncellAenter:(MillionsofDollars)SelectcellAandtype:toNotethattheentryincellAwillbetreatedastextbyExcelbecauseofthespacesandlettersincludedIncellsAtoFwenowwanttoentertheyearsInAtype:,inBtype:,nowselectA:BandmovethemousepointeroverthelowerrightcorneroftheselectionThemousepointerwillnowchangetoaskinnycrossindicatingthatyoucanusetheAutoFillfeatureClickanddragthemousetotherighttofillintheremainingyearsNoticethatthemostrecentdataistypicallyenteredattheleft,andthemostdistantdataattherightThisconventionallowsustoeas

职业精品

精彩专题

上传我的资料

热门资料

资料评价:

/ 387
所需积分:0 立即下载

意见
反馈

返回
顶部

Q