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

Adding username and password options for securing financial functions

When working with customers to calculate payment information, it is often necessary to change loan parameters to meet customer needs. Although making these modifications is important, you may want to limit the users who have the ability to make these changes.

In this recipe, you will add a button to change the interest rates for a loan, which will ask you for a username and password before allowing the change to be made.

Getting ready

This recipe will begin with an existing worksheet that has information entered for calculating loan payment. The interest rate is in cell B8:

How to do it...

We will begin by creating the code that will prompt for a password as well as ask for the new interest rate:

  1. 1. Begin by pressing Ctrl + F11 on the keyboard to open the Visual Basic Editor (VBE). From the VBE toolbar, choose Insert | Module to add a new blank module:

  2. 2. Enter the following function code: 

    Public Sub interest_change()
Dim strPass...
Previous Section
End of Section 6
Next Section