Book Image

Excel 2010 Financials Cookbook

By : Andre Odnoha
Book Image

Excel 2010 Financials Cookbook

By: Andre Odnoha

Overview of this book

<p>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.<br /><br />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.<br /><br />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.</p>

Related Content you might be interested in

Current Title:

Small book image
Excel 2010 Financials Cookbook
Andre Odnoha
Jul, 2011 | 260 pages
No titles found
Table of Contents (14 chapters)
Excel 2010 Financials Cookbook
Excel 2010 Financials Cookbook
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Normalizing Financial Data within Excel
Normalizing Financial Data within Excel
Introduction
Setting up an Excel spreadsheet
Correcting Excel calculations
Removing formulas from a list of numbers
Highlighting the blanks in a list of data
Making printing easier to read
Splitting financial data
Combining financial data
Redefining the data format
Grouping transaction details in a statement of accounts
Displaying financial summary formulas within their cells
2
Analyzing Financial Data—Staying in the Grid
Analyzing Financial Data—Staying in the Grid
Introduction
Analyzing with an IF statement
Advancing what IF analysis scenarios
Discounting proration by payment date
Collecting user sales data across multiple sheets
Calculating total sales by day of the week
Directing entry with conditional messages
Validating user interaction
Locking cells in the grid
Calculating total number of payments in a list by an individual
3
Moving Beyond the Grid—Financial Data Via an Interface
Moving Beyond the Grid—Financial Data Via an Interface
Introduction
Personalizing your splash screen
Entering data via a form
E-mailing an invoice from Excel
Adding username and password options for securing financial functions
Providing customers with on-demand help by using VB macros and a user form
Calculating loan terms using Excel
Creating a dashboard for financial functions while minimizing Excel
4
Using Graphs for Financial and Statistical Analysis
Using Graphs for Financial and Statistical Analysis
Introduction
Charting financial frequency trending with a histogram
Creating a stem and leaf plot
Creating a box and whisker plot
Creating a graph overlay for profit and expenses
Graphing the principal of a loan automatically
Adding animations to Excel graphs
Adding a graph to e-mail automatically
5
Representing Data without Graphs
Representing Data without Graphs
Introduction
Visualizing payments with a graphical calendar
Creating a five-number summary for analysis
Calculating hours with a visual timesheet
Analyzing financial data via repetition
Adding mini graphs within the data
Creating a table of financial information
6
Building Financial Functions into Excel
Building Financial Functions into Excel
Introduction
Determining standard deviation for assessing risk
Analyzing benefits between interest and payment investments
Calculating the number of payments in a loan
Calculating the depreciation of assets
Calculating the future versus current value of your money
Determining the difference between effective and nominal interest rates
Identifying the profitability of an investment
Calculating and planning for inventory requirements
7
Augmenting Excel Functions with Customized Data Mining
Augmenting Excel Functions with Customized Data Mining
Introduction
Highlighting user edits and storing the versions
Adding financial tools to the Excel ribbon
Completing web forms
Importing data lists from the Web
Counting by colors instead of numbers
Converting dates with code
Analyzing financial data with pop-up tools
Index
Index

Analyzing financial data with pop-up tools

We have discussed and learned how powerful a quick analysis can be for determining profitability and other factors of finance, specifically the snapshot attained from a five-number summary. In this book, we have also discussed methods for formatting data tables and creating analysis worksheets for viewing this information. However, with changing data, the ability to attain a summary analysis on data without having to formally build an analysis can be extremely valuable.

In this recipe, you will learn to create an add-in function that will provide a five-number summary of selected data, on-demand.

How to do it...

We will begin this recipe by opening a new blank worksheet:

  1. 1. Within the Excel worksheet, press Alt + F11 on the keyboard to open the visual basic editor (VBE).

  2. 2. Within the VBE, from the toolbar, choose Insert | Module.

    This will open a blank module:

  3. 3. Enter the following code:

    Sub AnalysisPop()
Dim nRange As Range
Dim min1, q1, med, q3, max1...
Previous Section
End of Chapter 7
Next Chapter