You are previewing Excel 2010 Financials Cookbook.
O'Reilly logo
Excel 2010 Financials Cookbook

Book Description

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

  • Harness the power of Excel to help manage your business finances

  • Build useful financial analysis systems on top of Excel

  • Covers normalizing, analysing, and presenting financial data

  • Clear and practical with straight forward, step-by-step instructions

  • In Detail

    Excel is one of the mostused software tools in the world and just about every business has a copy somewhere. Despite its power and flexibility it is not always clear how to use it to perform some of the most important tasks in any business: organizing, analysing, and presenting financial information.

    Excel 2010 Financials Cookbook contains a rich collection of useful techniques for handling financial data in Excel. From integrating data from a variety of different sources, through organazing and analyzing financial data, to presenting it in a variety of graphical forms, this book has you covered.

    The book deals first with "normalizing" financial data - that is, bringing data from a number of different sources into a single format where you can analyze them together. Then you'll learn techniques for managing and analyzing the data before discovering ways to present it graphically. The book then looks at Excel's built in features for financial analysis, and even shows how you can combine the built in features to build your own analysis functions.

    Table of Contents

    1. Excel 2010 Financials Cookbook
      1. Excel 2010 Financials Cookbook
      2. Credits
      3. About the Author
      4. About the Reviewers
      5. www.PacktPub.com
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
      6. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Errata
          2. Piracy
          3. Questions
      7. 1. Normalizing Financial Data within Excel
        1. Introduction
        2. Setting up an Excel spreadsheet
          1. Getting ready
          2. How to do it...
          3. How it works…
          4. There's more...
        3. Correcting Excel calculations
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Removing formulas from a list of numbers
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        5. Highlighting the blanks in a list of data
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        6. Making printing easier to read
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Splitting financial data
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. Combining financial data
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        9. Redefining the data format
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Grouping transaction details in a statement of accounts
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        11. Displaying financial summary formulas within their cells
          1. How to do it...
          2. How it works...
          3. There's more...
      8. 2. Analyzing Financial Data—Staying in the Grid
        1. Introduction
        2. Analyzing with an IF statement
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Advancing what IF analysis scenarios
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Discounting proration by payment date
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        5. Collecting user sales data across multiple sheets
          1. How to do it...
          2. How it works...
          3. There's more…
            1. Preventing mistakes from an unknown
        6. Calculating total sales by day of the week
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        7. Directing entry with conditional messages
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Validating user interaction
          1. Getting ready...
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also...
        9. Locking cells in the grid
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Calculating total number of payments in a list by an individual
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      9. 3. Moving Beyond the Grid—Financial Data Via an Interface
        1. Introduction
        2. Personalizing your splash screen
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Images in the UserForm
        3. Entering data via a form
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. E-mailing an invoice from Excel
          1. Getting started
          2. How to do it...
          3. How it works...
          4. There's more...
        5. Adding username and password options for securing financial functions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Providing customers with on-demand help by using VB macros and a user form
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Multiple userforms
        7. Calculating loan terms using Excel
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Creating a dashboard for financial functions while minimizing Excel
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Different buttons
      10. 4. Using Graphs for Financial and Statistical Analysis
        1. Introduction
        2. Charting financial frequency trending with a histogram
          1. Getting ready
          2. How to do it…
          3. How it works...
          4. There's more...
        3. Creating a stem and leaf plot
          1. How to do it...
          2. How it works…
          3. There's more...
        4. Creating a box and whisker plot
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Creating a graph overlay for profit and expenses
          1. How to do it...
          2. How it works...
          3. There's more...
          4. Even more...
        6. Graphing the principal of a loan automatically
          1. Getting started
          2. How to do it...
        7. Adding animations to Excel graphs
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. Adding a graph to e-mail automatically
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      11. 5. Representing Data without Graphs
        1. Introduction
        2. Visualizing payments with a graphical calendar
          1. How to do it...
          2. How it works…
          3. There's more…
        3. Creating a five-number summary for analysis
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        4. Calculating hours with a visual timesheet
          1. Getting started
          2. How to do it...
          3. How it works...
          4. There's more…
        5. Analyzing financial data via repetition
          1. How to do it…
          2. How it works…
          3. There's more…
        6. Adding mini graphs within the data
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Creating a table of financial information
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also...
      12. 6. Building Financial Functions into Excel
        1. Introduction
        2. Determining standard deviation for assessing risk
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Analyzing benefits between interest and payment investments
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Calculating the number of payments in a loan
          1. How do it...
          2. How it works...
          3. There's more...
        5. Calculating the depreciation of assets
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Calculating the future versus current value of your money
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Determining the difference between effective and nominal interest rates
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Identifying the profitability of an investment
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        9. Calculating and planning for inventory requirements
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      13. 7. Augmenting Excel Functions with Customized Data Mining
        1. Introduction
        2. Highlighting user edits and storing the versions
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Adding financial tools to the Excel ribbon
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Letting others use the add-in
        4. Completing web forms
          1. Getting ready
          2. How to do it...
          3. How it works…
          4. There's more…
            1. Expanding the use
        5. Importing data lists from the Web
          1. How to do it...
          2. How it works...
          3. There's more...
            1. A helpful suggestion
        6. Counting by colors instead of numbers
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Converting dates with code
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Analyzing financial data with pop-up tools
          1. How to do it...
          2. How it works...
          3. There's more...