Excel 2010 Financials Cookbook

Exploit the flexibility of Excel to the max by using it to manage and analyze your financial data. These hands-on recipes will give you the techniques you need to go from casual user to power user.

Excel 2010 Financials Cookbook

Cookbook
Andre Odnoha

Exploit the flexibility of Excel to the max by using it to manage and analyze your financial data. These hands-on recipes will give you the techniques you need to go from casual user to power user.
$10.00
$39.99
RRP $23.99
RRP $39.99
eBook
Print + eBook
$12.99 p/month

Get Access

Get Unlimited Access to every Packt eBook and Video course

Enjoy full and instant access to over 3000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Code Files
+ Collection
Free Sample

Book Details

ISBN 139781849691185
Paperback260 pages

About This Book

  • 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

Who This Book Is For

This book is for anybody who wants to use Excel to manage their revenues, budgets, and other financials. It's especially suitable for small businesses that already use Excel and want to get more from it.

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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

What You Will Learn

  • Normalize financial data in Excel, so that data from different sources can be easily compared
  • Organize your money and finances in the best way to enable easy and powerful analysis of that data
  • Present your financials graphically using charts and other methods
  • Discover powerful analysis functions in Excel, and combine existing functions to create new ones

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.

Authors

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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

Book Details

ISBN 139781849691185
Paperback260 pages
Read More