Spreadsheet software Books

Book Synopsis

Read more less

Book SynopsisJoan Lambert has worked closely with Microsoft technologies since 1986, and in the training and certification industry since 1997. As President and CEO of Online Training Solutions, Inc. (OTSI), Joan guides the translation of technical information and requirements into useful, relevant, and measurable resources for people who are seeking certification of their computer skills or who simply want to know how to get things done efficiently. Joan is the author or coauthor of more than four dozen books about Windows and Office (for Windows, Mac, and iPad), five generations of Microsoft Office Specialist certification study guides, video-based training courses for SharePoint and OneNote, QuickStudy guides for Windows and Office, and the GO! series book for Outlook 2016. Blissfully based in America's Finest City, Joan is a Microsoft CertifiedTable of Contents Introduction Taking a Microsoft Office Specialist exam Exam 77-727: Microsoft Excel 2016 Core Prerequisites Objective group 1: Create and manage worksheets and workbooks Objective 1.1: Create worksheets and workbooks Objective 1.2: Navigate in worksheets and workbooks Objective 1.3: Format worksheets and workbooks Objective 1.4: Customize options and views for worksheets and workbooks Objective 1.5: Configure worksheets and workbooks for distribution Objective group 2: Manage data cells and ranges Objective 2.1: Insert data in cells and ranges Objective 2.2: Format cells and ranges Objective 2.3: Summarize and organize data Objective group 3: Create tables Objective 3.1: Create and manage tables Objective 3.2: Manage table styles and options Objective 3.3: Filter and sort tables Objective group 4: Perform operations by using formulas and functions Objective 4.1: Summarize data by using functions Objective 4.2: Perform conditional operations by using functions Objective 4.3: Format and modify text by using functions Objective group 5: Create charts and objects Objective 5.1: Create charts Objective 5.2: Format charts Objective 5.3: Insert and format objects

Read more less

Book SynopsisFinancial models in Excel allow investment analysts and other finance professionals to take the laborious number crunching out of financial analysis and forecasting. Models help them to gain meaningful insights into the way that a business is working and focus attention on areas to improve bottom-line results. They can also be used as powerful tools to test the potential impact of various risks on business performance.In this brand new guide, financial modelling expert Paul Lower presents step-by-step instructions for seven spreadsheet models that will help the user to gain a better understanding of the financial data coming out of a business.These seven models can be used to:1. Assess how a business is performing on key financial indicators.2. Produce sales and cost forecasts.3. Create a cash flow forecast.4. Understand the impact of product price changes on profitability.5. Assess potential investment decisions.6. Check the sensitivity of key financial measures to risk events.7. Produce a business valuation.The book also includes downloadable spreadsheets of the author''s original Excel models and introductory chapters about best practice when modelling in Excel.With this suite of seven tools, a financial analyst will be equipped to use Excel to achieve a deep understanding of a business and its financial data.

Read more less

Book SynopsisThe quick way to learn Microsoft Excel 2019! This is learning made easy. Get more done quickly with Microsoft Excel 2019. Jump in wherever you need answers–brisk lessons and detailed screenshots show you exactly what to do, step by step. Quickly set up workbooks, enter data, and format it for easier viewing Perform calculations and find and correct errors Create sophisticated forecast worksheets, key performance indicators (KPIs), and timelines Visualize data with 3D maps, funnel charts, and other powerful tools Import, process, summarize, and analyze huge datasets with Excel’s improved PowerPivot and Power Query Build powerful data models and use them in business intelligence Look up just the tasks and lessons you need Table of Contents Part 1: Create and format workbooks Chapter 1: Set up a workbook Chapter 2: Work with data and Excel tables Chapter 3: Perform calculations on data Chapter 4: Change workbook appearance Part 2: Analyze and present data Chapter 5: Manage worksheet data Chapter 6: Reorder and summarize data Chapter 7: Combine data from multiple sources Chapter 8: Analyze alternative data sets Chapter 9: Create charts and graphics Chapter 10: Create dynamic worksheets by using PivotTables Part 3: Collaborate and share in Excel Chapter 11: Print worksheets and charts Chapter 12: Automate repetitive tasks by using macros Chapter 13: Work with other Microsoft Office apps Chapter 14: Collaborate with colleagues Part 4: Perform advanced analysis Chapter 15: Perform business intelligence analysis Chapter 16: Create forecasts and visualizations

Read more less

Book SynopsisWenn schon der Gedanke an Excel-Arbeitsblätter Ihnen Kopfschmerzen bereitet, ist dieses Buch das richtige für Sie. Im Handumdrehen können Sie Arbeitsblätter erstellen und bearbeiten, Zellen formatieren, Formeln eingeben, Diagramme erstellen und bearbeiten, Diagramme einfügen, Datenbankformulare entwerfen und vieles mehr. Außerdem erhalten Sie eine leicht verständliche Anleitung für fortgeschrittene Excel-Fertigkeiten, wie das Hinzufügen von Hyperlinks zu Arbeitsblättern, das Speichern von Arbeitsblättern als Webseiten, das Hinzufügen von Arbeitsblattdaten zu einer bestehenden Webseite und vieles mehr.Table of ContentsÜber den Autor 13 Einleitung 25 Über dieses Buch 25 Wie Sie dieses Buch nutzen können 26 Was Sie nicht lesen müssen 26 Törichte Annahmen über den Leser 26 Wie dieses Buch aufgebaut ist 27 Teil I: Für den Anfang: Das absolute Minimum 27 Teil II: Ändern nach Lust und Laune 27 Teil III: Den Datenüberblick behalten 27 Teil IV: Hier geht’s zur Datenanalyse 27 Teil V: Ein Leben nach den Arbeitsblättern 28 Teil VI: Der Top-Ten- Teil 28 Konventionen in diesem Buch 28 Symbole, die in diesem Buch verwendet werden 29 Wie es weitergeht 30 Teil I: Für den Anfang: Das absolute Minimum 31 Kapitel 1: Das große Excel-Benutzerexperiment 33 Die Benutzeroberfläche von Excel 34 Backstage mit der Registerkarte »Datei« 36 Das vielseitige Menüband 37 Die Symbolleiste für den Schnellzugriff anpassen 42 Viel Spaß mit der Bearbeitungsleiste 47 Was der Arbeitsblattbereich zu bieten hat 48 Die informative Statusleiste 55 Excel starten und beenden 56 Excel über das Startmenü starten 56 Excel über das Suchfeld von Windows starten 57 Excel aus dem Windows-Tablet- Modus starten 57 Excel beenden 58 Hilfe ist unterwegs 59 Sie wünschen bitte? 60 Kapitel 2: Die erste Arbeitsmappe 63 Die Arbeit beginnt 64 Das Wichtigste zur Dateneingabe 65 Das ABC der Dateneingabe 66 Welche Daten sind denn Ihr Typ? 68 Text (weder Fisch noch Fleisch) 69 Jede Menge Zahlen 70 Formeln, die der Aufgabe gewachsen sind 76 Würden Sie mir das bitte mal markieren? 79 Vorfahrt achten! 79 Wenn Formeln spinnen 80 Fehlersuche und -bekämpfung 81 Einmal AutoKorrektur, bitte 82 Die Regeln der Zellbearbeitung 83 Keine Angst vor der Dateneingabe 85 Ohne AutoVervollständigen bin ich ein Nichts 85 Abrakadabra – AutoAusfüllen! 86 AutoAusfüllen mit der Blitzvorschau 93 Symbole einfügen 95 Das große Zellcursorspringen 95 Do it again, Excel! 96 Funktionieren die Funktionen? 97 Viel Spaß beim Einfügen von Funktionen 98 Formeln bearbeiten 100 Lassen Sie addieren! 101 Und jetzt noch die Summe via Schnellanalyse ermitteln 104 Jetzt wird gespeichert 105 Den Standardspeicherort ändern 108 Der Unterschied zwischen dem XLSX-und dem XLS-Format 109 Die Arbeitsmappe als PDF speichern 109 AutoWiederherstellen – die Rettung nach dem Crash 110 Teil II: Ändern nach Lust und Laune 113 Kapitel 3: Ein bisschen Glanz für nüchterne Zahlen 115 Sie haben die Wahl! 116 Zellauswahl à la Maus 116 Zellauswahl mit der Tastatur 120 Der Tabellenformatvorlagenkatalog 123 Integrierte Tabellenformatvorlagen an eigene Vorstellungen anpassen 125 Eine eigene Tabellenformatvorlage basteln 125 Zellen mit den Optionen der Registerkarte »Start« formatieren 127 Zellen direkt an der Quelle mit der Minisymbolleiste formatieren 130 Auch ein Dialogfeld zeigt Format 131 Für jede Zahl das richtige Format 132 Nicht immer ist es so, wie es scheint 137 Und jetzt ein Datum 138 Maßgeschneiderte Zahlenformate 139 Wem die Spaltenbreite nicht passt … 139 Das ist doch die Zeilenhöhe! 140 Das Arbeitsblattversteckspiel 141 Das ist aber schriftartig! 142 Alles in Reih und Glied bringen 144 Die Sache mit dem Einzug 145 Ausrichten in der Vertikalen 145 Alles im Umbruch 146 Richtungsänderungen 148 Raum ist in der kleinsten Zelle 150 Auf den äußeren Rahmen kommt es an 150 Füllfarben, Muster und Fülleffekte 152 Zellen mit Format 153 Neue Formatvorlagen für den Katalog erstellen 153 Benutzerdefinierte Formatvorlagen von einer Arbeitsmappe in eine andere kopieren 154 Den Formatierungspinsel schwingen 154 Bedingte Formatierung 156 Grafische bedingte Formatierung 157 Zellen je nach Wert hervorheben 158 Mit der Schnellanalyse formatieren 159 Kapitel 4: Änderungen durchführen, ohne Chaos anzurichten 161 Arbeitsmappen, wo seid ihr? 162 Der Bildschirm »Öffnen« 163 Das Dialogfeld »Öffnen« 164 Die Einstellungen für die zuletzt verwendeten Dateien ändern 165 Mehr als eine Arbeitsmappe gleichzeitig öffnen 166 Wo habe ich diese Arbeitsmappe bloß abgelegt? 166 Was es noch im Dialogfeld »Öffnen« gibt 167 Bitte rückgängig machen! 168 Das Rückgängigmachen rückgängig machen 168 Wenn mit »Rückgängig« nichts mehr vorwärtsgeht 169 Ziehen bis zum Ablegen 169 Kopieren mit »Ziehen und Ablegen« 171 Darf ich mal eben dazwischen? 172 Die Formel und das AutoAusfüllen 173 Alles relativ 174 Absolut richtig 175 Man muss auch einfügen können 177 Kopieren – so oft Sie wollen 178 Und noch mehr Optionen fürs Einfügen 179 Einfügen mit dem Aufgabenbereich »Zwischenablage« 181 Auch beim Einfügen sollte man wählerisch sein 182 Inhalt oder Zelle löschen, das ist hier die Frage 184 Inhalte löschen 185 Das absolute Ende für einen Zellbereich 185 Vorsicht, Einfügearbeiten! 186 Tippfehlern den Garaus machen 187 Kapitel 5: Und jetzt alles aufs Papier gebracht 191 Einen prüfenden Blick auf Arbeitsblätter im Seitenlayout werfen 192 Einen prüfenden Blick auf Arbeitsblätter in der Seitenansicht werfen 194 Das aktuelle Arbeitsblatt drucken 196 Auf der Jagd nach der perfekten Seite 198 Die Werkzeuge der Gruppe »Seite einrichten« 199 Die Werkzeuge der Gruppe »An Format anpassen« 205 Die Werkzeuge der Gruppe »Blattoptionen« 205 Von Kopf bis Fuß 206 Kopf-und Fußzeilen von der Stange 207 Kopf-und Fußzeilen selbst gemacht 208 Alles an seinem Platz 212 Auch Formeln wollen gedruckt werden 214 Teil III: Den Datenüberblick behalten 217 Kapitel 6: Ihre Daten in den Griff kriegen 219 Zoom, zoom, zoom, Fenster zoom herum 220 Wer klopft da an mein Unterfenster? 222 Eingefrorene Zeilen-und Spaltenüberschriften 224 Elektronische Notizen 226 Notizen in Zellen einfügen 227 Notizen im Überblick 228 Kommentare bearbeiten 229 Notizen drucken 230 Kommentare 230 Einen Kommentar einfügen 231 Kommentare einblenden und ausblenden 232 Kommentare bearbeiten 232 Auf einen Kommentar antworten 233 Wie heißt denn die Zelle? 233 Das Kind beim Namen nennen 234 Auch Formeln verwenden Namen 236 Wie heißt denn die Konstante? 237 Wer suchet, der findet 239 Aufgepasst bei Zellersatzteilen 242 Mit der intelligenten Suchfunktion recherchieren 243 Manuell neu berechnen 245 Schutzmaßnahmen ergreifen 246 Kapitel 7: Mit mehreren Arbeitsblättern jonglieren 249 Was ich an Arbeitsblättern so mag 250 Blatt für Blatt aneinandergereiht 251 Gruppenarbeit (oder alle für einen) 253 Mal mehr, mal weniger 254 Jedem Register seinen Namen 255 Jedem Register seine Farbe 256 Arbeitsblätter nach Belieben anordnen 256 In Arbeitsblättern »fensterln« 257 Arbeitsblätter Seite an Seite vergleichen 262 Von Arbeitsmappe zu Arbeitsmappe 263 Fassen wir zusammen 266 Teil IV: Hier geht’s zur Datenanalyse 271 Kapitel 8: Was wäre, wenn …? 273 »Was wäre wenn« mit Datentabellen spielen 274 Datentabelle mit einer Variablen 274 Datentabelle mit zwei Variablen 277 »Was wäre wenn« mit der Zielwertsuche spielen 280 »Was wäre wenn« mit dem Szenario-Manager spielen 282 Verschiedenste Szenarien 282 Szenarien im Überblick 285 Kapitel 9: PivotTables – die ultimative Datenauswertung 287 Was man mit PivotTables so alles machen kann 288 Eine PivotTable mithilfe der Schnellanalyse bauen 288 Eine PivotTable auf Empfehlung 290 Eine PivotTable selbst basteln 292 Eine PivotTable formatieren 295 Das PivotTable-Format überarbeiten 295 Die Werte einer PivotTable formatieren 295 PivotTable-Daten filtern und sortieren 296 PivotTables nach Filter filtern 296 Nach Zeilen-und Spaltenbeschriftungen filtern 297 PivotTables mit Datenschnitten filtern 298 PivotTables mit einer Zeitachse filtern 299 PivotTables sortieren 301 PivotTables umstrukturieren 301 Die angezeigten Daten in einer PivotTable ändern 301 Die Struktur einer PivotTable ändern 302 Die zusammenfassende Funktion in einer PivotTable ändern 302 PivotCharts – die grafische Darstellung von PivotTable-Daten 304 Ein PivotChart in ein separates Diagrammblatt stecken 305 Ein PivotChart filtern 306 Ein PivotChart formatieren 306 Teil V: Ein Leben nach den Arbeitsblättern 307 Kapitel 10: Ein Bild sagt mehr als tausend Worte 309 Professionelle Diagramme zaubern 310 Diagramme auf Empfehlung 310 Diagramme à la carte 311 Diagramme via Schnellanalyse 313 Fertigdiagramme 315 Mal größer, mal kleiner, mal hier, mal dort 316 Ein eingebettetes Diagramm in ein separates Diagrammblatt verschieben 317 Diagrammtyp und –formatvorlage auswählen 317 Diagrammelemente bearbeiten 318 Dem Diagramm einen Titel geben 321 Und noch mehr Grafiken 323 Mit Sparklines Daten interpretieren 323 Mit Textfeldern texten 326 Onlinebilder einfügen 328 3D-Bilder einfügen 330 Lokal gespeicherte Bilder einfügen 331 Bilder bearbeiten 332 2D-Bilder formatieren 333 3D-Bilder formatieren 335 Formen vom Feinsten 336 Piktogramme hinzufügen 337 Mit WordArt werkeln 338 Smarte SmartArts 339 Bildschirmabbildungen gefällig? 342 Eigene Designs entwerfen 342 Mit überlappenden grafischen Objekten arbeiten 343 Übereinanderliegende Grafiken neu ordnen 343 Grafische Objekte gruppieren 345 Mal sichtbar, mal unsichtbar 345 Nur das Diagramm aufs Papier bringen 346 Gemeinsame genutzte Arbeitsmappen barrierefrei machen 346 Kapitel 11: Zahlen und Fakten griffbereit 351 Tabellen erstellen 351 Daten in die Excel-Tabelle eingeben 353 Scroll me up, Scotty 357 Heureka! Ich hab’s gefunden! 358 Daten von A bis Z (oder wie bringe ich Ordnung in das Chaos) 360 Nach einem Feld sortieren 361 Nach mehreren Feldern sortieren 361 Daten in Tabellen filtern 364 Nur die Top 10 bitte 366 Einsatzbereite Datumsfilter 367 Selbst geschnitzte Filter 367 Externe Daten in eine Tabelle importieren 370 Eine Access-Datenbanktabelle importieren 370 Daten über Webabfragen importieren 373 Kapitel 12: Erweitern, verknüpfen, automatisieren und gemeinsam nutzen 375 Mit Office-Add- Ins arbeiten 376 Spezielle Excel-Add- Ins einsetzen 378 Arbeitsblätter mit Hyperlinks ausstatten 380 Aufgaben mit Makros automatisieren 383 Makros aufzeichnen 383 Makros ausführen 387 Makros in das Menüband aufnehmen 388 Makros in die Symbolleiste für den Schnellzugriff aufnehmen 390 Arbeitsblätter gemeinsam nutzen 391 Arbeitsmappen auf OneDrive freigeben 391 Einen Freigabelink abrufen 395 Arbeitsmappe per E-Mail weitergeben 395 Arbeitsmappen online bearbeiten 396 Teil VI: Der Top-Ten- Teil 401 Kapitel 13: Die Top Ten für Anfänger 403 Excel über das Startmenü von Windows starten 403 Beim Starten von Excel auch gleich eine vorhandene Arbeitsmappe öffnen 403 Einen nicht sichtbaren Teil des Arbeitsblatts sichtbar machen 403 Eine neue Arbeitsmappe (mit einem leeren Arbeitsblatt) erstellen 404 Eine geöffnete Arbeitsmappe auf dem Bildschirm (vor allen anderen) anzeigen 404 Irgendetwas in Ihr Arbeitsblatt eingeben 404 Zelleninhalte überarbeiten 404 Einen der vielen Befehle aus dem Menüband wählen 405 Zum allerersten Mal speichern 405 Excel nach getaner Arbeit wieder verlassen 405 Kapitel 14: Die Top Ten für alle 407 Du sollst das Speichern nicht vergessen 407 Du sollst deine Arbeitsmappe mit einem klangvollen Namen versehen 407 Du sollst die Daten in deinen Arbeitsblättern eng zusammenhalten 407 Du sollst alle Formeln mit einem Gleichheitszeichen beginnen 408 Du sollst deine Zellen markieren, bevor du sie bearbeitest 408 Du sollst auf »Rückgängig« klicken, wenn du einen Fehler gemacht hast 408 Du sollst Zeilen und Spalten nur nach sorgfältiger Prüfung einfügen oder löschen 408 Du sollst das Arbeitsblatt vor dem Drucken in der Seitenlayoutansicht prüfen 409 Du sollst die Berechnung von riesigen Mappen auf manuell umstellen 409 Du sollst deine Arbeitsmappen vor dem Zugriff anderer schützen 409 Abbildungsverzeichnis 411 Stichwortverzeichnis 419

Read more less

Book SynopsisReviews from previous editions: "Excel for Chemists should be part of any academic library offering courses and programs in Chemistry. " Choice "I highly recommend the book; treat yourself to it; assign it to a class; give it as a gift.Trade Review“Finally this new edition, provides chemists and students a detailed guide and examples how to apply the current versions of Excel to their needs. It should be added to the shelves of those using this program within their scientific work.” (Materials and Corrosion, 1 November 2012)Table of ContentsPreface to the Third Edition xxv Before You Begin xxvii Part I The Basics Chapter 1 Working with Excel 2007 or Excel 2010 3 Chapter 2 Working with Excel 2003 79 Chapter 3 Excel Formulas and Functions 137 Chapter 4 Excel 2007/2010 Charts 177 Chapter 5 Excel 2003 Charts 209 Part II Advanced Spreadsheet Topics Chapter 6 Advanced Worksheet Formulas 233 Chapter 7 Array Formulas 267 Chapter 8 Advanced Charting Techniques 289 Chapter 9 Using Excel's Database Features 327 Chapter 10 Importing Data into Excel 349 Chapter 11 Adding Controls to a Spreadsheet 365 Chapter 12 Other Language Versions of Excel 385 Part III Spreadsheet Mathematics Chapter 13 Mathematical Methods for Spreadsheet Calculations 403 Chapter 14 Linear Regression and Curve Fitting 435 Chapter 15 Nonlinear Regression Using the Solver 463 Part IV Excel's Visual Basic for Applications Chapter 16 Visual Basic for Applications: An Introduction 491 Chapter 17 Programming with VBA 503 Chapter 18 Working with Arrays in VBA 543 Part V Some Applications of VBA Chapter 19 Command Macros 557 Chapter 20 Custom Functions 571 Chapter 21 Automatic Procedures 589 Chapter 22 Custom Menus 595 Chapter 23 Custom Toolbars and Toolbuttons 607 Part VI Appendices Appendix A What's Where in Excel 2007/2010 629 Appendix B Selected Worksheet Functions by Category 633 Appendix C Alphabetical List of Selected Worksheet Functions 639 Appendix D Renamed Functions in Excel 2010 661 Appendix E Selected Visual Basic Keywords by Category 663 Appendix F Alphabetical List of Selected Visual Basic Keywords 667 Appendix G Selected Excel 4 Macro Functions 689 Appendix H Shortcut Keys by Keystroke 693 Appendix I Selected Shortcut Keys by Category 703 Appendix J ASCII Codes 707 Appendix K Contents of the CD-ROM 709 Index 719

Read more less

Book SynopsisIt is common to blame the inadequacy of credit risk models for the fact that the financial crisis has caught many market participants by surprise. On closer inspection, though, it often appears that market participants failed to understand or to use the models correctly.Table of ContentsPreface to the 2nd edition xi Preface to the 1st edition xiii Some Hints for Troubleshooting xv 1 Estimating Credit Scores with Logit 1 Linking scores, default probabilities and observed default behavior 1 Estimating logit coefficients in Excel 4 Computing statistics after model estimation 8 Interpreting regression statistics 10 Prediction and scenario analysis 12 Treating outliers in input variables 16 Choosing the functional relationship between the score and explanatory variables 20 Concluding remarks 25 Appendix 25 Logit and probit 25 Marginal effects 25 Notes and literature 26 2 The Structural Approach to Default Prediction and Valuation 27 Default and valuation in a structural model 27 Implementing the Merton model with a one-year horizon 30 The iterative approach 30 A solution using equity values and equity volatilities 35 Implementing the Merton model with a T -year horizon 39 Credit spreads 43 CreditGrades 44 Appendix 50 Notes and literature 52 Assumptions 52 Literature 53 3 Transition Matrices 55 Cohort approach 56 Multi-period transitions 61 Hazard rate approach 63 Obtaining a generator matrix from a given transition matrix 69 Confidence intervals with the binomial distribution 71 Bootstrapped confidence intervals for the hazard approach 74 Notes and literature 78 Appendix 78 Matrix functions 78 4 Prediction of Default and Transition Rates 83 Candidate variables for prediction 83 Predicting investment-grade default rates with linear regression 85 Predicting investment-grade default rates with Poisson regression 88 Backtesting the prediction models 94 Predicting transition matrices 99 Adjusting transition matrices 100 Representing transition matrices with a single parameter 101 Shifting the transition matrix 103 Backtesting the transition forecasts 108 Scope of application 108 Notes and literature 110 Appendix 110 5 Prediction of Loss Given Default 115 Candidate variables for prediction 115 Instrument-related variables 116 Firm-specific variables 117 Macroeconomic variables 118 Industry variables 118 Creating a data set 119 Regression analysis of LGD 120 Backtesting predictions 123 Notes and literature 126 Appendix 126 6 Modeling and Estimating Default Correlations with the Asset Value Approach 131 Default correlation, joint default probabilities and the asset value approach 131 Calibrating the asset value approach to default experience: the method of moments 133 Estimating asset correlation with maximum likelihood 136 Exploring the reliability of estimators with a Monte Carlo study 144 Concluding remarks 147 Notes and literature 147 7 Measuring Credit Portfolio Risk with the Asset Value Approach 149 A default-mode model implemented in the spreadsheet 149 VBA implementation of a default-mode model 152 Importance sampling 156 Quasi Monte Carlo 160 Assessing Simulation Error 162 Exploiting portfolio structure in the VBA program 165 Dealing with parameter uncertainty 168 Extensions 170 First extension: Multi-factor model 170 Second extension: t-distributed asset values 171 Third extension: Random LGDs 173 Fourth extension: Other risk measures 175 Fifth extension: Multi-state modeling 177 Notes and literature 179 8 Validation of Rating Systems 181 Cumulative accuracy profile and accuracy ratios 182 Receiver operating characteristic (ROC) 185 Bootstrapping confidence intervals for the accuracy ratio 187 Interpreting caps and ROCs 190 Brier score 191 Testing the calibration of rating-specific default probabilities 192 Validation strategies 195 Testing for missing information 198 Notes and literature 201 9 Validation of Credit Portfolio Models 203 Testing distributions with the Berkowitz test 203 Example implementation of the Berkowitz test 206 Representing the loss distribution 207 Simulating the critical chi-square value 209 Testing modeling details: Berkowitz on subportfolios 211 Assessing power 214 Scope and limits of the test 216 Notes and literature 217 10 Credit Default Swaps and Risk-Neutral Default Probabilities 219 Describing the term structure of default: PDs cumulative, marginal and seen from today 220 From bond prices to risk-neutral default probabilities 221 Concepts and formulae 221 Implementation 225 Pricing a CDS 232 Refining the PD estimation 234 Market values for a CDS 237 Example 239 Estimating upfront CDS and the ‘Big Bang’ protocol 240 Pricing of a pro-rata basket 241 Forward CDS spreads 242 Example 243 Pricing of swaptions 243 Notes and literature 247 Appendix 247 Deriving the hazard rate for a CDS 247 11 Risk Analysis and Pricing of Structured Credit: CDOs and First-to-Default Swaps 249 Estimating CDO risk with Monte Carlo simulation 249 The large homogeneous portfolio (LHP) approximation 253 Systemic risk of CDO tranches 256 Default times for first-to-default swaps 259 CDO pricing in the LHP framework 263 Simulation-based CDO pricing 272 Notes and literature 281 Appendix 282 Closed-form solution for the LHP model 282 Cholesky decomposition 283 Estimating PD structure from a CDS 284 12 Basel II and Internal Ratings 285 Calculating capital requirements in the Internal Ratings-Based (IRB) approach 285 Assessing a given grading structure 288 Towards an optimal grading structure 294 Notes and literature 297 Appendix A1 Visual Basics for Applications (VBA) 299 Appendix A2 Solver 307 Appendix A3 Maximum Likelihood Estimation and Newton’s Method 313 Appendix A4 Testing and Goodness of Fit 319 Appendix A5 User-defined Functions 325 Index 333

Read more less

Book SynopsisThis book is an introduction to the modelling of cash collateralised debt obligations (CDOs). It is intended that the reader have a basic understanding of CDOs and a basic working knowledge of Microsoft Office Excel. There will be written explanations of concepts along with understandable mathematical explanations and examples provided in Excel.Table of ContentsForeword. Acknowledgments. 1 Introduction. 1.1 To Excel or Not to Excel? 1.2 Existing Tools and Software. 2 What is a Cash CDOs? 2.1 Types of CDOs. 2.2 Description of a Cash Flow CDO. 2.3 Life Cycle of a Cash CDO. 2.4 Contribution to the “Credit Crunch”. 3 Introduction to Modelling. 3.1 Goals in Modelling. 3.2 Modelling Philosophies and Trade-Offs. 3.3 Flexibility. 3.4 Organization and Layout of a Model. 3.5 Life-Cycle Issues: Building an Adaptable Model. 4 Prerequisites to Cash Flow Modelling. 4.1 Modelling Dates. 4.2 Interest Rate Curve Modelling. 4.3 Present Value Modelling. 5 Getting Started. 5.1 Create the Input Sheet. 5.2 The Value of Labelling. 6 Modelling Assets. 6.1 Initial Asset Pool: Rep Line Modelling vs. Actual Assets. 6.2 The Collateral Sheet in the Cash Flow Model. 6.3 Modelling Defaults and Recoveries. 6.4 Amortization. 6.5 Modelling Reinvestment. 6.6 Reinvestment Cohorts. 6.7 Accounts. 6.8 Timing Models vs. Actual Timing. 6.9 Simple Warehouse Modelling. 7 Basic Waterfall Modelling. 7.1 Basic Waterfalls. 7.2 Layout and Design. 7.3 Avoiding Negative Values. 7.4 Timing Modelled vs. Actual Timing. 7.5 Liabilities Cash Flows. 7.6 Fees and Expenses Cash Flows. 7.7 Interest Waterfall. 7.8 Interest Waterfall (Available Funds after Payment). 7.9 Interest Waterfall Calculations. 7.10 Principal Waterfall. 7.11 Principle Waterfall (Available Funds after Payment). 7.12 Principal Waterfall Calculations. 7.13 Adding Over-Collaterization Tests. 7.14 Adding Interest Coverage Tests. 7.15 Technical Issues with Coverage Tests. 8 Outputs Sheet. 8.1 Purpose of the Outputs Sheet. 8.2 Collating Waterfall Outputs. 8.3 Present Value. 8.4 Duration. 8.5 Weighted Average Life and Internal Rate of Return. 8.6 Equity Analysis. 8.7 Basic Auditing. 9 Moody’s Rating Agency Methodology. 9.1 Introduction to Agency Methodologies. 9.2 The Bet Approach. 9.3 Evaluating the Collateral. 9.4 Creating the Moody’s Sheet and Related References in the Cash Flow Model. 9.5 Default Profiles. 9.6 Interest Rate Profiles. 9.7 Running the Analysis. 9.8 Variations on the BET. 9.9 2009 Methodology Update. 10 Standard & Poor’s Rating Methodology. 10.1 The S&P Approach. 10.2 Evaluating the Collateral. 10.3 Modelling Recovery Rates. 10.4 CDO Evaluator. 10.5 Default Rates. 10.6 Interest Rate Stresses. 10.7 Amortization. 10.8 Additional S&P Modelling Criteria. 10.9 Building the S7P Sheet and Related References. 10.10 Running the Stress Scenarios. 11 Advanced Waterfall Modelling. 11.1 Hedge Agreements. 11.2 Fixed Notes. 11.3 Variable Funding Notes. 11.4 Liquidity Facilities. 11.5 Interest Reserve Accounts. 11.6 Other Structural Features. 11.7 Combination Notes. 11.8 Collateral Manager Equity Analysis. 12 Maintaining the Cash Flow Model. 12.1 Adapting Your Model for Different Capital Structures. 12.2 Audit Sheet. 12.3 Debugging. 13 Advanced Structuring Issues. 13.1 Projecting Accrued Interest. 13.2 Collating Collateral Cash Flows. 14 Sourcing and Integrating Data From External Systems. 14.1 Data Requirements. 14.2 Trustee Reports. 14.3 Bloomberg. 14.4 Loan Level Information Sources. 15 Regulatory Applications of CDO Technology. 15.1 The Basel Accords. 15.2 Regulatory Capital Requirements for CDO Notes. 15.3 The Standardized Approach for CDOs. 15.4 The Internal Ratings-Based Approach for CDOs. 15.5 The Internal Ratings-Based Approach for CDOs: The Ratings-Based Approach. 15.6 The Internal Ratings-Based Approach for CDOs: The Supervisory Formula Approach. 15.7 The Internal Ratings-Based Approach: Liquidity Facilities, Overlapping Exposures, Credit Risk Mitigation and Early Amortization Features. 15.8 Supervisory Provisions. 15.9 Updates to Basel II. 16 CDO Valuation. 16.1 Introduction. 16.2 Basic Valuation Approaches. 16.3 Traditional Underwriter Analysis. 16.4 Fundamental Cash Flow Analysis. 16.5 Using Rating Agency Models. 16.6 Transition Matrices. 16.7 Conclusion. 17 In Conclusion. Index.

Read more less

Book SynopsisThis new and unique book demonstrates that Excel and VBA can play an important role in the explanation and implementation of numerical methods across finance. Advanced Modelling in Finance provides a comprehensive look at equities, options on equities and options on bonds from the early 1950s to the late 1990s. The book adopts a step-by-step approach to understanding the more sophisticated aspects of Excel macros and VBA programming, showing how these programming techniques can be used to model and manipulate financial data, as applied to equities, bonds and options. The book is essential for financial practitioners who need to develop their financial modelling skill sets as there is an increase in the need to analyse and develop ever more complex ''what if'' scenarios. Specifically applies Excel and VBA to the financial markets Packaged with a CD containing the software from the examples throughout the book Note: CD-ROM/DVD and other suppTrade ReviewNo. 4 bestseller in 'General Finance' (erivativesreview.com, December 2001)Table of ContentsPreface xi Acknowledgements xii 1 Introduction 1 1.1 Finance insights 1 1.2 Asset price assumptions 2 1.3 Mathematical and statistical problems 2 1.4 Numerical methods 2 1.5 Excel solutions 3 1.6 Topics covered 3 1.7 Related Excel workbooks 5 1.8 Comments and suggestions 5 Part One Advanced Modelling in Excel 7 2 Advanced Excel functions and procedures 9 2.1 Accessing functions in Excel 9 2.2 Mathematical functions 10 2.3 Statistical functions 12 2.3.1 Using the frequency function 12 2.3.2 Using the quartile function 14 2.3.3 Using Excel’s normal functions 15 2.4 Lookup functions 16 2.5 Other functions 18 2.6 Auditing tools 19 2.7 Data Tables 20 2.7.1 Setting up Data Tables with one input 20 2.7.2 Setting up Data Tables with two inputs 22 2.8 XY charts 23 2.9 Access to Data Analysis and Solver 26 2.10 Using range names 27 2.11 Regression 28 2.12 Goal Seek 31 2.13 Matrix algebra and related functions 33 2.13.1 Introduction to matrices 33 2.13.2 Transposing a matrix 33 2.13.3 Adding matrices 34 2.13.4 Multiplying matrices 34 2.13.5 Matrix inversion 35 2.13.6 Solving systems of simultaneous linear equations 36 2.13.7 Summary of Excel’s matrix functions 37 Summary 37 3 Introduction to VBA 39 3.1 Advantages of mastering VBA 39 3.2 Object-oriented aspects of VBA 40 3.3 Starting to write VBA macros 42 3.3.1 Some simple examples of VBA subroutines 42 3.3.2 MsgBox for interaction 43 3.3.3 The writing environment 44 3.3.4 Entering code and executing macros 44 3.3.5 Recording keystrokes and editing code 45 3.4 Elements of programming 47 3.4.1 Variables and data types 48 3.4.2 VBA array variables 48 3.4.3 Control structures 50 3.4.4 Control of repeating procedures 51 3.4.5 Using Excel functions and VBA functions in code 52 3.4.6 General points on programming 53 3.5 Communicating between macros and the spreadsheet 53 3.6 Subroutine examples 56 3.6.1 Charts 56 3.6.2 Normal probability plot 59 3.6.3 Generating the efficient frontier with Solver 61 Summary 65 References 65 Appendix 3A The Visual Basic Editor 65 Stepping through a macro and using other debug tools 68 Appendix 3B Recording keystrokes in ‘relative references’ mode 69 4 Writing VBA user-defined functions 73 4.1 A simple sales commission function 73 4.2 Creating Commission(Sales) in the spreadsheet 74 4.3 Two functions with multiple inputs for valuing options 75 4.4 Manipulating arrays in VBA 78 4.5 Expected value and variance functions with array inputs 79 4.6 Portfolio variance function with array inputs 81 4.7 Functions with array output 84 4.8 Using Excel and VBA functions in user-defined functions 85 4.8.1 Using VBA functions in user-defined functions 85 4.8.2 Add-ins 86 4.9 Pros and cons of developing VBA functions 86 Summary 87 Appendix 4A Functions illustrating array handling 88 Appendix 4B Binomial tree option valuation functions 89 Exercises on writing functions 94 Solution notes for exercises on functions 95 Part Two Equities 99 5 Introduction to equities 101 6 Portfolio optimisation 103 6.1 Portfolio mean and variance 103 6.2 Risk–return representation of portfolios 105 6.3 Using Solver to find efficient points 106 6.4 Generating the efficient frontier (Huang and Litzenberger’s approach) 109 6.5 Constrained frontier portfolios 111 6.6 Combining risk-free and risky assets 113 6.7 Problem One–combining a risk-free asset with a risky asset 114 6.8 Problem Two–combining two risky assets 115 6.9 Problem Three–combining a risk-free asset with a risky portfolio 117 6.10 User-defined functions in Module1 119 6.11 Functions for the three generic portfolio problems in Module1 120 6.12 Macros in ModuleM 121 Summary 123 References 123 7 Asset pricing 125 7.1 The single-index model 125 7.2 Estimating beta coefficients 126 7.3 The capital asset pricing model 129 7.4 Variance–covariance matrices 130 7.5 Value-at-Risk 131 7.6 Horizon wealth 134 7.7 Moments of related distributions such as normal and lognormal 136 7.8 User-defined functions in Module1 136 Summary 138 References 138 8 Performance measurement and attribution 139 8.1 Conventional performance measurement 140 8.2 Active–passive management 141 8.3 Introduction to style analysis 144 8.4 Simple style analysis 145 8.5 Rolling-period style analysis 146 8.6 Confidence intervals for style weights 148 8.7 User-defined functions in Module1 151 8.8 Macros in ModuleM 151 Summary 152 References 153 Part Three Options on Equities 155 9 Introduction to options on equities 157 9.1 The genesis of the Black–Scholes formula 158 9.2 The Black–Scholes formula 158 9.3 Hedge portfolios 159 9.4 Risk-neutral valuation 161 9.5 A simple one-step binomial tree with risk-neutral valuation 162 9.6 Put–call parity 163 9.7 Dividends 163 9.8 American features 164 9.9 Numerical methods 164 9.10 Volatility and non-normal share returns 165 Summary 165 References 166 10 Binomial trees 167 10.1 Introduction to binomial trees 167 10.2 A simplified binomial tree 168 10.3 The Jarrow and Rudd binomial tree 170 10.4 The Cox, Ross and Rubinstein tree 173 10.5 Binomial approximations and Black–Scholes formula 175 10.6 Convergence of CRR binomial trees 176 10.7 The Leisen and Reimer tree 177 10.8 Comparison of CRR and LR trees 178 10.9 American options and the CRR American tree 180 10.10 User-defined functions in Module0 and Module1 182 Summary 183 References 184 11 The Black–Scholes formula 185 11.1 The Black–Scholes formula 185 11.2 Black–Scholes formula in the spreadsheet 186 11.3 Options on currencies and commodities 187 11.4 Calculating the option’s ‘greek’ parameters 189 11.5 Hedge portfolios 190 11.6 Formal derivation of the Black–Scholes formula 192 11.7 User-defined functions in Module1 194 Summary 195 References 196 12 Other numerical methods for European options 197 12.1 Introduction to Monte Carlo simulation 197 12.2 Simulation with antithetic variables 199 12.3 Simulation with quasi-random sampling 200 12.4 Comparing simulation methods 202 12.5 Calculating greeks in Monte Carlo simulation 203 12.6 Numerical integration 203 12.7 User-defined functions in Module1 205 Summary 207 References 207 13 Non-normal distributions and implied volatility 209 13.1 Black–Scholes using alternative distributional assumptions 209 13.2 Implied volatility 211 13.3 Adapting for skewness and kurtosis 212 13.4 The volatility smile 215 13.5 User-defined functions in Module1 217 Summary 219 References 220 Part Four Options on Bonds 221 14 Introduction to valuing options on bonds 223 14.1 The term structure of interest rates 224 14.2 Cash flows for coupon bonds and yield to maturity 225 14.3 Binomial trees 226 14.4 Black’s bond option valuation formula 227 14.5 Duration and convexity 228 14.6 Notation 230 Summary 230 References 230 15 Interest rate models 231 15.1 Vasicek’s term structure model 231 15.2 Valuing European options on zero-coupon bonds, Vasicek’s model 234 15.3 Valuing European options on coupon bonds, Vasicek’s model 235 15.4 CIR term structure model 236 15.5 Valuing European options on zero-coupon bonds, CIR model 237 15.6 Valuing European options on coupon bonds, CIR model 238 15.7 User-defined functions in Module1 239 Summary 240 References 241 16 Matching the term structure 243 16.1 Trees with lognormally distributed interest rates 243 16.2 Trees with normal interest rates 246 16.3 The Black, Derman and Toy tree 247 16.4 Valuing bond options using BDT trees 248 16.5 User-defined functions in Module1 250 Summary 252 References 252 Appendix Other VBA functions 253 Forecasting 253 ARIMA modelling 254 Splines 256 Eigenvalues and eigenvectors 257 References 258 Index 259

Read more less

Book Synopsis

Read more less

Book SynopsisA practical guide to data mining using SQL and Excel Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most popular tools for data query and analysisSQL and Excelto perform sophisticated data analysis without the need for complex and expensive data mining tools. Written by a leading expert on business data mining, this book shows you how to extract useful business information from relational databases. You''ll learn the fundamental techniques before moving into the where and why of each analysis, and then learn how to design and perform these analyses using SQL and Excel. Examples include SQL and Excel code, and the appendix shows how non-standard constructs are implemented in other major databases, including Oracle and IBM DB2/UDB. The companion website includes datasets and Excel spreadsheets, and the book provides hints, warnings, and technical asides to help you every step of the way. Data Analysis Using SQL and Excel, 2nd Table of ContentsForeword xxxiii Introduction xxxvii Chapter 1 A Data Miner Looks at SQL 1 Databases, SQL, and Big Data 2 Picturing the Structure of the Data 6 Picturing Data Analysis Using Dataflows 16 SQL Queries 21 Subqueries and Common Table Expressions Are Our Friends 36 Lessons Learned 47 Chapter 2 What’s in a Table? Getting Started with Data Exploration 49 What Is Data Exploration? 50 Excel for Charting 51 Sparklines 65 What Values Are in the Columns? 68 More Values to Explore—Min, Max, and Mode 79 Exploring String Values 81 Exploring Values in Two Columns 86 From Summarizing One Column to Summarizing All Columns 90 Lessons Learned 96 Chapter 3 How Different Is Different? 97 Basic Statistical Concepts 98 How Different Are the Averages? 105 Sampling from a Table 110 Counting Possibilities 115 Ratios and Their Statistics 128 Chi-Square 132 What Months and Payment Types Have Unusual Affinities for Which Types of Products? 140 Lessons Learned 143 Chapter 4 Where Is It All Happening? Location, Location, Location 145 Latitude and Longitude 146 Census Demographics 160 Geographic Hierarchies 172 Mapping in Excel 188 Lessons Learned 194 Chapter 5 It’s a Matter of Time 197 Dates and Times in Databases 198 Starting to Investigate Dates 204 How Long Between Two Dates? 218 Year-over-Year Comparisons 229 Counting Active Customers by Day 239 Simple Chart Animation in Excel 247 Lessons Learned 254 Chapter 6 How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value 255 Background on Survival Analysis 256 The Hazard Calculation 260 Survival and Retention 269 Comparing Different Groups of Customers 280 Comparing Survival over Time 287 Important Measures Derived from Survival 293 Using Survival for Customer Value Calculations 298 Forecasting 308 Lessons Learned 314 Chapter 7 Factors Affecting Survival: The What and Why of Customer Tenure 315 Which Factors Are Important and When 316 Left Truncation 328 Time Windowing 336 Competing Risks 342 Before and After 353 Lessons Learned 366 Chapter 8 Customer Purchases and Other Repeated Events 367 Identifying Customers 368 RFM Analysis 393 Which Households Are Increasing Purchase Amounts Over Time? 404 Time to Next Event 416 Lessons Learned 420 Chapter 9 What’s in a Shopping Cart? Market Basket Analysis 421 Exploring the Products 422 Products and Customer Worth 437 Product Geographic Distribution 448 Which Customers Have Particular Products? 451 Lessons Learned 463 Chapter 10 Association Rules and Beyond 465 Item Sets 466 The Simplest Association Rules 480 One-Way Association Rules 483 Two-Way Associations 489 Extending Association Rules 499 Lessons Learned 506 Chapter 11 Data Mining Models in SQL 507 Introduction to Directed Data Mining 508 Look-Alike Models 515 Lookup Model for Most Popular Product 522 Lookup Model for Order Size 528 Lookup Model for Probability of Response 534 Naive Bayesian Models (Evidence Models) 546 Lessons Learned 559 Chapter 12 The Best-Fit Line: Linear Regression Models 561 The Best-Fit Line 562 Measuring Goodness of Fit Using R2 581 Direct Calculation of Best-Fit Line Coefficients 584 Weighted Linear Regression 592 More Than One Input Variable 600 Lessons Learned 607 Chapter 13 Building Customer Signatures for Further Analysis 609 What Is a Customer Signature? 610 Designing Customer Signatures 617 Operations to Build Customer Signatures 622 Extracting Features 639 Summarizing Customer Behaviors 644 Lessons Learned 653 Chapter 14 Performance Is the Issue: Using SQL Effectively 655 Query Engines and Performance 656 Considerations When Thinking About Performance 660 Performance: Its Meaning and Measurement 663 Performance Improvement 101 665 Using Indexes Effectively 668 When OR Is a Bad Thing 683 Pros and Cons: Different Ways of Expressing the Same Thing 686 Window Functions 694 Lessons Learned 701 Appendix Equivalent Constructs Among Databases 703 Index 731

Read more less

Book SynopsisSolutions Manual to accompany Introduction to Quantitative Methods in Business: With Applications Using Microsoft(R) Office Excel(R).Table of Contents1. The Mathematical Toolbox: A Summary 1 1.2 Linear Functions 1 1.3.1 Solving Two Simultaneous Linear Equations 1 1.4 Summation Notation 2 1.5 Sets 3 1.6 Functions and Graphs 3 1.7 Working with Functions 4 1.8 Differentiation and Integration 5 Solutions to Odd-Numbered Exercises 8 2. Applications of Linear and Nonlinear Functions: A Summary 32 2.2 Linear Demand and Supply Functions 32 2.3 Linear Total Cost and Total Revenue Functions 33 2.4 Market Equilibrium 33 2.6 Applications of Nonlinear Functions 34 2.7 Present Value of an Income Stream 35 2.8 Average Values 35 2.9 Marginal Values 36 2.10 Elasticity 36 Solutions to Odd-Numbered Exercises 37 3. Optimization: A Summary 47 3.2 Unconstrained Optimization 47 3.2.1 Models of Profit and Revenue Maximization 47 3.2.3 Solution Using the Calculus Approach 47 3.2.5 Solution Using the Calculus Approach 47 3.3 Models of Cost Minimization: Inventory Cost Functions and Economic Order Quantity (EOQ) 48 3.3.2 Solution Using the Calculus Approach 49 3.4 Constrained Optimization: Linear Programming 50 3.4.1 Linear Programming: Maximization 50 3.4.2 Linear Programming: Minimization 51 Solutions to Odd-Numbered Exercises 52 4. What Is Business Statistics? 68 4.3 Descriptive Statistics: Tabular and Graphical Techniques 68 4.4 Descriptive Statistics: Numerical Measures of Central Tendency or Location of Data 70 4.4.1 Population Mean 70 4.4.2 Sample Mean 70 4.4.3 Weighted Mean 70 4.4.4 Mean of a Frequency Distribution: Grouped Data 71 4.4.5 Geometric Mean 71 4.4.6 Median 71 4.4.7 Quantiles, Quartiles, Deciles, and Percentiles 71 4.4.8 Mode 72 4.5 Descriptive Statistics: Measures of Dispersion (Variability or Spread) 73 4.5.2 Variance 73 4.5.3 Standard Deviation 74 4.5.4 Coefficient of Variation 74 4.5.5 Some Important Uses of the Standard Deviation 75 1. Standardization of Values 75 2. Chebysheff’s Theorem 75 4.5.6 Empirical Rule 75 4.6 Measuring Skewness 76 Solutions to Odd-Numbered Exercises 76 5. Probability and Applications 96 5.2 Some Useful Definitions 96 5.3 Probability Sources 96 5.3.1 Objective Probability 96 5.4 Some Useful Definitions Involving Sets of Events in the Sample Space 96 5.5 Probability Laws 97 5.5.2 Rule of Complements 97 5.5.3 Conditional Probability 97 5.5.4 General Multiplication Rule (Product Rule) 97 5.5.5 Independent Events 98 5.5.6 Probability Tree Approach 98 5.6 Contingency Table 98 Solutions to Odd-Numbered Exercises 100 6. Random Variables and Probability Distributions 105 6.2 Probability Distribution of a Discrete Random Variable X 105 6.3 Expected Value, Variance, and Standard Deviation of a Discrete Random Variable 106 6.3.1 Some Basic Rules of Expectation 106 6.3.2 Some Useful Properties of the Variance of X 107 6.4 Continuous Random Variables and Their Probability Distributions 107 6.5 A Specific Discrete Probability Distribution: The Binomial Case 108 6.5.1 Binomial Probability Distribution 108 6.5.2 Mean and Standard Deviation of the Binomial Random Variable 109 6.5.3 Cumulative Binomial Probability Distribution 110 Solutions to Odd-Numbered Exercises 110 Index 119

Read more less

Book SynopsisSet includes Introduction to Quantitative Methods in Business: With Applications Using Microsoft Office Excel ISBN 978-1-119-22097-8 and the accompanying Solutions Manual ISBN 978-1-119-22102-9 A well-balanced and accessible introduction to the elementary quantitative methods and Microsoft Office Excel applications used to guide business decision making Featuring quantitative techniques essential for modeling modern business situations, Introduction to Quantitative Methods in Business: With Applications Using Microsoft Office Excel provides guidance to assessing real-world data sets using Excel. The book presents a balanced approach to the mathematical tools and techniques with applications used in the areas of business, finance, economics, marketing, and operations. The authors begin by establishing a solid foundation of basic mathematics and statistics before moving on to more advanced concepts. The first part of the book starts by develop

Read more less

Book SynopsisThe must-have statistics guide for students of health services Statistics for Health Care Management and Administration: Working with Excel introduces the uses of statistics in healthcare management and administration using the features and functions of Microsoft Excel. The book introduces students to statistics within the context of health care, focusing on the major data and analysis techniques used in the field. Step-by-step instructions in the latest version of Excel and numerous annotated screen shots make examples easy to follow and understand. This updated fourth edition provides the same content and explanations that have made the previous editions so popular, offering revisions drawn directly from universities where the book has been used. All content has been brought current with the newest version of excel, and changes in the field of healthcare administration are covered as well. Statistics for Health Care Management and Administration gets students off to a great start by introducing statistics in the context of their chosen field. Learn the basics of statistics in the context of ExcelUnderstand how to acquire data and display it for analysisMaster important concepts and tests, including regressionTurn test results into usable information with proper analysisThis book not only helps students develop the necessary data analysis skills, but also boosts familiarity with important software that employers will be looking for.Table of ContentsPreface xiii Introducing Excel xiii So How Did We Get to Here? xiii Intended Level of the Textbook xiv Textbook Organization xiv Leading by Example(s) xv Acknowledgments xvii The Authors xix About the Companion Website xxi Part 1 1 Chapter 1 Statistics and Excel 3 1.1 How This Book Differs from Other Statistics Texts 3 1.2 Statistical Applications in Health Policy and Health Administration 4 Exercises for Section 1.2 14 1.3 What Is the ‘‘Big Picture’’? 15 1.4 Some Initial Definitions 16 Exercises for Section 1.4 26 1.5 Five Statistical Tests 28 Exercises for Section 1.5 30 Chapter 2 Excel as a Statistical Tool 33 2.1 The Basics 33 Exercises for Section 2.1 35 2.2 Working and Moving Around in a Spreadsheet 36 Exercises for Section 2.2 41 2.3 Excel Functions 41 Exercises for Section 2.3 46 2.4 The =IF() Function 47 Exercises for Section 2.4 50 2.5 Excel Graphs 51 Exercises for Section 2.5 56 2.6 Sorting a String of Data 57 Exercise for Section 2.6 60 2.7 The Data Analysis Pack 61 2.8 Functions That Give Results in More than One Cell 63 Exercises for Section 2.8 66 2.9 The Dollar Sign ($) Convention for Cell References 67 Chapter 3 Data Acquisition: Sampling and Data Preparation 71 3.1 The Nature of Data 71 Exercises for Section 3.1 78 3.2 Sampling 79 Exercises for Section 3.2 93 3.3 Data Access and Preparation 94 Exercises for Section 3.3 107 3.4 Missing Data 108 Chapter 4 Data Display: Descriptive Presentation, Excel Graphing Capability 111 4.1 Creating, Displaying, and Understanding Frequency Distributions 111 Exercises for Section 4.1 129 4.2 Using the Pivot Table to Generate Frequencies of Categorical Variables 131 Exercises for Section 4.2 135 4.3 A Logical Extension of the Pivot Table: Two Variables 135 Exercises for Section 4.3 140 Chapter 5 Basic Concepts of Probability 141 5.1 Some Initial Concepts and Definitions 141 Exercises for Section 5.1 150 5.2 Marginal Probabilities, Joint Probabilities, and Conditional Probabilities 150 Exercises for Section 5.2 160 5.3 Binomial Probability 161 Exercises for Section 5.3 171 5.4 The Poisson Distribution 173 Exercises for Section 5.4 178 5.5 The Normal Distribution 178 Chapter 6 Measures of Central Tendency and Dispersion: Data Distributions 183 6.1 Measures of Central Tendency and Dispersion 183 Exercises for Section 6.1 196 6.2 The Distribution of Frequencies 197 Exercises for Section 6.2 208 6.3 The Sampling Distribution of the Mean 209 Exercises for Section 6.3 219 6.4 Mean and Standard Deviation of a Discrete Numerical Variable 220 Exercises for Section 6.4 222 6.5 The Distribution of a Proportion 222 Exercises for Section 6.5 227 6.6 The t Distribution 227 Exercises for Section 6.6 232 Part 2 235 Chapter 7 Confidence Limits and Hypothesis Testing 237 7.1 What Is a Confidence Interval? 237 Exercises for Section 7.1 243 7.2 Calculating Confidence Limits for Multiple Samples 244 Exercises for Section 7.2 246 7.3 What Is Hypothesis Testing? 247 Exercises for Section 7.3 249 7.4 Type I and Type II Errors 250 Exercises for Section 7.4 266 7.5 Selecting Sample Sizes 267 Exercises for Section 7.5 269 Chapter 8 Statistical Tests for Categorical Data 271 8.1 Independence of Two Variables 271 Exercises for Section 8.1 282 8.2 Examples of Chi-Square Analyses 283 Exercises for Section 8.2 289 8.3 Small Expected Values in Cells 290 Exercises for Section 8.3 292 Chapter 9 t Tests for Related and Unrelated Data 295 9.1 What Is a t Test? 295 Exercises for Section 9.1 302 9.2 A t Test for Comparing Two Groups 303 Exercises for Section 9.2 316 9.3 A t Test for Related Data 318 Exercises for Section 9.3 321 Chapter 10 Analysis of Variance 323 10.1 One-Way Analysis of Variance 323 Exercises for Section 10.1 339 10.2 ANOVA for Repeated Measures 340 Exercises for Section 10.2 348 10.3 Factorial Analysis of Variance 349 Exercises for Section 10.3 362 Chapter 11 Simple Linear Regression 365 11.1 Meaning and Calculation of Linear Regression 365 Exercises for Section 11.1 373 11.2 Testing the Hypothesis of Independence 374 Exercises for Section 11.2 380 11.3 The Excel Regression Add-In 381 Exercises for Section 11.3 388 11.4 The Importance of Examining the Scatterplot 388 11.5 The Relationship between Regression and the t Test 391 Exercises for Section 11.5 392 Chapter 12 Multiple Regression: Concepts and Calculation 395 12.1 Introduction 395 Exercises for Section 12.1 406 Chapter 13 Extensions of Multiple Regression 409 13.1 Dummy Variables in Multiple Regression 409 Exercises for Section 13.1 420 13.2 The Best Regression Model 421 Exercises for Section 13.2 431 13.3 Correlation and Multicolinearity 432 Exercises for Section 13.3 435 13.4 Nonlinear Relationships 435 Exercises for Section 13.4 447 Chapter 14 Analysis with a Dichotomous Categorical Dependent Variable 449 14.1 Introduction to the Dichotomous Dependent Variable 450 14.2 An Example with a Dichotomous Dependent Variable: Traditional Treatments 451 Exercises for Section 14.2 462 14.3 Logit for Estimating Dichotomous Dependent Variables 463 Exercises for Section 14.3 475 14.4 A Comparison of Ordinary Least Squares, Weighted Least Squares, and Logit 476 Exercises for Section 14.4 480 Appendix A Multiple Regression and Matrices 481 An Introduction to Matrix Math 481 Addition and Subtraction of Matrices 482 Multiplication of Matrices 483 Matrix Multiplication and Scalars 484 Finding the Determinant of a Matrix 484 Matrix Capabilities of Excel 486 Explanation of Excel Output Displayed with Scientific Notation 489 Using the b Coefficients to Generate Regression Results 490 Calculation of All Multiple Regression Results 491 Exercises for Appendix A 494 References 497 Glossary 499 Index 513

Read more less

Book SynopsisMaster Excel in less than two weeks with this unique project-based book! Let's face it, we all master skills in our own way, but building a soup-to-nuts project is one of the best ways to make learning stick and get up to speed quickly. Whether you are just getting started with Excel or are an experienced user, this book will elevate your knowledge and skills. For a beginner, the micro examples in each chapter will warm you up before you dive into the projects. For experienced users, the projects, especially those with table setup considerations, will help you become more creative in your interactions with Excel. Readers will benefit from building eight unique projects, each covering a different topic, including a word game, a food nutrition ranking, a payroll (tax withholding) calculation, an encryption, a two-way table, a Kaplan-Meier analysis, a data analysis via a pivot table and the K-means Clustering data mining method. Through these projects, you will experience firsthand how Table of ContentsChapter 1: Master Excel through Projects Chapter 2: Food Nutrition Ranking Chapter 3: Payroll Calculation Chapter 4: Public and Private Key Cryptography Chapter 5: Two-Way Table and Chi-Square Test Chapter 6: Kaplan-Meier Analysis Chapter 7: PivotTable Data Analysis Chapter 8: K-means Clustering and Iterative Calculation

Read more less

Book SynopsisThis practical guide teaches analysts a useful subset of Excel skills that will enable them to access and interpret financial informationwithout any prior programming experience.

Read more less

Book SynopsisIn this day and age of too much information and not enough time,' the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.Jerry Kohl, president of Brighton Collectibles Develop your Excel macro programming skills using VBA instantly with proven techniques Automate Reports Handle Errors Master Pivot Tables Produce Charts Build User-Defined Functions Migrate to Excel 2007 Query Web Data Build Dialog Boxes Use Data Visualizations Automate Word  You are an expert in Excel, but the macro recorder doesn't work and you can't make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 re

Read more less