PacktLib: Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook

Credits

About the Author

About the Reviewers

www.PacktPub.com

Preface

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

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

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

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

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

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

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