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

Excel 2010 Financials Cookbook

Copyright © 2011 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: July 2011

Production Reference: 1230611

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-849691-18-5

www.packtpub.com

Cover Image by Charwak A ( )

Previous Section
Next Chapter