Excel 2010 Financials: Using Graphs for Analysis

Exclusive offer: get 50% off this eBook here
Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook — Save 50%

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook

$23.99    $12.00
by Andre Odnoha | June 2011 | Cookbooks Enterprise Articles Microsoft

Using graphs for financial and statistical analysis allows you to analyze and present data in a meaningful and functional way utilizing graphs that even Excel didn't know it could do. In this article by Andre Odnoha, author of Excel 2010 Financials Cookbook, you will learn to not only add reusable methods to automate graph production, but also how to create graphs and graphing sets that are not native to Excel.

In this article, you will learn the following recipes:

  • Charting financial frequency trending with a histogram
  • Creating a stem and leaf plot
  • Creating a Box and Whisker Plot
  • Using a graph overlay for profit and expenses
  • Graphing the principal of a loan automatically

 

Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

        Read more about this book      

(For more resources on this subject, see here.)

Introduction

Graphing is one of the most effective ways to display datasets, financial scenarios, and statistical functions in a way that can be understood easily by the users. When you give an individual a list of 40 different numbers and ask him or her to draw a conclusion, it is not only difficult, it may be impossible without the use of extra functions. However, if you provide the same individual a graph of the numbers, they will most likely be able to notice trending, dataset size, frequency, and so on. Despite the effectiveness of graphing and visual modeling, financial and statistical graphing is often overlooked in Excel due to difficulty, or lack of native functions.

In this article, you will learn to not only add reusable methods to automate graph production, but also how to create graphs and graphing sets that are not native to Excel. You will learn to use box and whisker plots, histograms to demonstrate frequency, stem and leaf plots, and other methods to graph financial ratios and scenarios.

Charting financial frequency trending with a histogram

Frequency calculations are used throughout financial analysis, statistics, and other mathematical representations to determine how often an event has occurred. Determining the frequency of financial events in a transaction list can assist in determining the popularity of an item or product, the future likelihood of an event to reoccur, or frequency of profitability of an organization. Excel, however, does not create histograms by default.

In this recipe, you will learn how to use several functions including bar charts and FREQUENCY functions to create a histogram frequency chart within Excel to determine profitability of an entity.

Getting ready

When plotting histogram frequency, we are using frequency and charting to determine the continued likelihood of an event from past data visually. Past data can be flexible in terms of what we are trying to determine; in this instance, we will use the daily net profit (Sale income Versus Gross expenses) for a retail store. The daily net profit numbers for one month are as follows:

$150, $237, -$94.75, $1,231, $876, $455, $349, -$173, -$34, -$234, $110, $83, -$97,
-$129, $34, $456, $1010, $878, $211, -$34, -$142, -$87, $312

How to do it...

Utilizing the profit numbers from above, we will begin by adding the data to the Excel worksheet:

  1. Within Excel, enter the daily net profit numbers into column A starting on row 2 until all the data has been entered:

    Excel 2010 Financials tutorial on Using Graphs

    We must now create the boundaries to be used within the histogram. The boundary numbers will be the highest and the lowest number thresholds that will be included within the graph. The boundaries to be used in this instance will be of $1500, and -$1500.

    These boundaries will encompass our entire dataset, and it will allow padding on the higher and lower ends of the data to allow for variation when plotting larger datasets encompassing multiple months or years worth of profit.

    We must now create bins that we will chart against the frequency. The bins will be the individual data-points that we want to determine the frequency against. For instance, one bin will be $1500, and we will want to know how often the net profit of the retail location falls within the range of $1500. The smaller the bins chosen, the larger the chart area.

    You will want to choose a bin size that will accurately reflect the frequency of your dataset without creating a large blank space. Bin size will change depending on the range of data to be graphed.

  2. Enter the chosen bin number into the worksheet in Column C. The bins will be a $150 difference from the previous bin.

    The bin sizes needed to include an appropriate range in order to illustrate the expanse of the dataset completely. In order to encompass all appropriate bin sizes, it is necessary to begin with the largest negative number, and increment to the largest positive number:

    Excel 2010 Financials tutorial on Using Graphs

    The last component for creating the frequency histogram actually determines the frequency of net profit to the designated bins. For this, we will use the Excel function FREQUENCY.

  3. Select rows D2 through D22, and enter the following formula:

    =FREQUENCY(A:A,C2:C22)

  4. After entering the formula, press Shift + Ctrl + Enter to finalize the formula as an array formula.

    Excel has now displayed the frequency of the net profit for each of the designated bins:

    Excel 2010 Financials tutorial on Using Graphs

    The information for the histogram is now ready. We will now be able to create the actual histogram graph.

  5. Select rows C2 through D22.
  6. With the rows selected, using the Excel ribbon, choose Insert | Column:

    Excel 2010 Financials tutorial on Using Graphs

  7. From the Column drop-down, choose the Clustered Column chart option:

    Excel 2010 Financials tutorial on Using Graphs

    Excel will now attempt to determine the plot area, and will present you with a bar chart. The chart that Excel creates does not accurately display the plot areas, due to Excel being unable to determine the correct data range:

    Excel 2010 Financials tutorial on Using Graphs

  8. Select the chart. From the Ribbon, choose Select Data:

    Excel 2010 Financials tutorial on Using Graphs

    From the select Data Source window that has appeared, you will change the Horizontal Axis to include the Bins column (column C), and the Legend Series to include the Frequency (column D).

  9. Excel will also create two series entries within the Legend Series panel; remove Series2 and select OK:

    Excel 2010 Financials tutorial on Using Graphs

    Excel now presents the histogram graph of net profit frequency:

    Excel 2010 Financials tutorial on Using Graphs

  10. Using the Format graph options on the Excel Ribbon, reduce the bar spacing and adjust the horizontal label to reduce the chart area to your specifications:

    Excel 2010 Financials tutorial on Using Graphs

Using the histogram for financial analysis, we can now determine that the major trend of the retail location quickly and easily, which maintains a net profit within $0 - $150, while maintaining a positive net profit throughout the month.

How it works...

While a histogram graph/chart is not native to Excel, we were able to use a bar/column chart to plot the frequency of net profit within specific bin ranges.

The FREQUENCY function of Excel follows the following format:

=FREQUENCY(Data Range to plot, Bins to plot within)

It is important to note that within the data range, we chose the range A:A. This range includes all of the data within the A column. If arbitrary or unnecessary data unrelated to the histogram were added into column A, this range would include them. Do not allow unnecessary data to be added to column A, or use a limited range such as A1:A5 if your data was only included in the first five cells of column A.

We entered the formula by pressing Shift + Ctrl + Enter in order to submit the formula as an array formula. This allows Excel to calculate individual information within a large array of data.

The graph modifications allowed the bins to show frequency in the vertical axis, or indicate how many times a specific number range was achieved. The horizontal axis displayed the actual bins.

There's more...

The amount of data for this histogram was limited; however, its usefulness was already evident. When this same charting recipe is used to chart a large dataset (for example, multiple year data), the histogram becomes even more useful in displaying trends.

Excel 2010 Financials Cookbook Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on this subject, see here.)

Creating a stem and leaf plot

Stem and leaf plots are an invaluable method for charting frequency. While a stem and leaf plot is not a true graph compared to bar graphs, pie charts, and so on, it is still used as a method for graphing/charting the frequency of a data within a population. Financial analysts can use this type of chart to determine inventory levels over a specific date range.

In this recipe, you will learn how to create a stem and leaf plot to chart the number of products sold over the course of several months.

How to do it...

Begin with inventory levels listed in Column A:

Excel 2010 Financials tutorial on Using Graphs

We will now need to create the stems. The stems are the first number of the inventory levels (for example, 49 would have stem of 4):

  1. Enter the formula =INT(A2/10) into cell C2, and copy down to cell C24 to calculate all of the stems:

    Excel 2010 Financials tutorial on Using Graphs

    We will now create the leaves for the stem and leaf plot. The leaves are the remaining numbers after the stem.

  2. In cell D2, enter the formula =MID(A2,LEN(C2)+1,10) and copy this formula down to cell D24:

    Excel 2010 Financials tutorial on Using Graphs

    The stems and leaves are now created. In order to allow Excel to place the leaves in the correct order and next to the correct stem, we must determine the position of each leaf relative to its corresponding stem.

  3. In cell E2, enter the formula =COUNTIF($A$2:$A24,"<"&A2)- COUNTIF($A$2:$A$24, "<"&INT(A2-D2))+COUNTIF(A$2:A2,A2) and copy this formula down to cell D24:

    Excel 2010 Financials tutorial on Using Graphs

    The data within column C is formatted as text. Excel does not allow proper calculation of text, so it is important to change the text formatting into numbers to allow calculation.

  4. Select column C, right-click on the column heading of C, and choose copy.
  5. Right-click in cell C2, and choose Paste Special | Values. After the paste has been performed, the original formulas will be gone, and only the values remain. Change the values to numbers.

    All of the information we need to prepare the plot is ready.

  6. Select cells C2 through D22, then choose Insert | Pivot Table from the Ribbon toolbar:

    Excel 2010 Financials tutorial on Using Graphs

  7. From the create pivot table wizard, choose OK to accept the default configuration and placement of the pivot chart onto a new worksheet:

    Excel 2010 Financials tutorial on Using Graphs

  8. In the pivot chart designer, drag the Stem label into the Row panel, the Location label into the Column panel, and the Leaves label into the Values panel:

    Excel 2010 Financials tutorial on Using Graphs

    The stem and leaf plot is now taking shape.

  9. From the ribbon, choose Design, and for each of the options of Subtotals, Grand Totals, Row Headers, and Column Headers, choose Remove or Do Not Show, to format the plot area properly:

    Excel 2010 Financials tutorial on Using Graphs

The finished product is a formatted stem and leaf Plot for product Inventory:

Excel 2010 Financials tutorial on Using Graphs

From this stem and leaf plot, you can determine that the inventory must exceed the range of 50 products to allow for proper inventory levels.

How it works...

The first formula within this recipe utilizes the INT function of Excel. The INT function takes the value of a number and rounds that number down to the closest integer.

The formula entered into column C, =INT(A2/10) takes the value of A2 divided by 10, and rounds down the integer. This provides the first number for any given inventory level.

Next, the formula =MID(A2,LEN(C2)+1,10) takes the value of A2, then starting at the position of 2, provides the remainder. The position of 2 is calculated by taking the length of the result of the first formula. We use the length to allow for triple digit stems. If we entered the number 2, rather than calculating length, in a number such as 123, Excel would return 23, rather than the 3, which is the true leaf.

The formula for the location =COUNTIF($A$2:$A24,"<"&A2)-COUNTIF($A$2:$A$24,"<"&INT(A2-D2))+COUNTIF(A$2:A2,A2) first finds how may numbers in the dataset are less than A2, then subtracts that from how many are less than the stem multiplied by 10. Last, it adds the number of times the value of A2 exists. This accounts for duplicate numbers.

With all of the calculations complete, we use the Excel Pivot Chart function to group the stems together in rows, and then list each corresponding leaf in the correct position.

There's more...

The stem and leaf plot is similar to a histogram with regards to how the data is displayed; however, the stem and leaf plot provides the ability to retain the original values. Rather than seeing frequency with a bar, you are able to determine not only the frequency, but also what values make up that frequency.

Box and whisker plot

A box and whisker plot is a common graphical summary function for viewing five separate data values in a single graph image. Five number summaries are invaluable methods for displaying useful information about a large set of data. In financial analysis, the box and whisker plot will display the average, minimum, maximum, and upper and lower quartiles of financial transactions. Having this information will allow a quick estimate of financial strength. Despite its usefulness, the box and whisker plot is not a function or graph type that is native to Excel.

In this recipe, you will create a box and whisker plot from the five-number summary of annual transactions for a retail location over a 3-year period.

How to do it...

We will start by utilizing a five-number summary and corresponding labels:

  1. Select cells A1 through F4.

    Excel 2010 Financials tutorial on Using Graphs

  2. From the Excel ribbon toolbar, choose Insert | Charts | Other Charts:

    Excel 2010 Financials tutorial on Using Graphs

  3. In the Other Charts drop-down, choose Stock | Volume – Open – High – Low – Close:

    Excel 2010 Financials tutorial on Using Graphs

  4. In the resulting graph, select the blue bar that appears below the first box in the graph:

    Excel 2010 Financials tutorial on Using Graphs

  5. Within the Design tab of the Excel Ribbon, select Change Chart Type:

    Excel 2010 Financials tutorial on Using Graphs

  6. In the choose chart window, select the Line with Markers chart type, and then select OK:

    Excel 2010 Financials tutorial on Using Graphs

  7. From the Excel ribbon, choose Layout | Axes | Secondary Vertical Axis, and select None:

    Excel 2010 Financials tutorial on Using Graphs

  8. Click on the line you just created within the box graph, right click, and choose Format Data Series:

    Excel 2010 Financials tutorial on Using Graphs

  9. In the formatting window, choose Marker, and change the type to Built-in, line type, with a size of 10:

    Excel 2010 Financials tutorial on Using Graphs

  10. Under Marker Fill, change the fill to Solid fill:

    Excel 2010 Financials tutorial on Using Graphs

  11. Lastly, change the Line Color to No line, and click on Close:

    Excel 2010 Financials tutorial on Using Graphs

  12. On the graph, select one of the boxes, and under Format fill, change the fill type to No Fill:

    Excel 2010 Financials tutorial on Using Graphs

The box and whisker plot is now complete. Within the plot, you can easily determine the median transactions throughout a given year, as well as determine the consistent averages, despite the upper and lower thresholds.

How it works...

The box and whisker plot displays the upper and lower quartiles within the box. The whiskers extend to the maximum and minimum prices on either end of the quartiles. Finally, the median is displayed within the box providing the graphical representation of price fluctuations within the median.

There's more...

When selecting the stock graph used in this recipe, if the data is not organized exactly as shown above, Excel will display an information box explaining how the data must be formatted. You will need to reformat the data exactly as shown and in the same order for Excel to allow you to proceed in graph creation:

Excel 2010 Financials tutorial on Using Graphs

Excel 2010 Financials Cookbook Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on this subject, see here.)

Creating a graph overlay for profit and expenses

Often, when displaying information in Excel, one type of graph or chart used exclusively does not always show the full picture. When charting profit and expenses, a column chart will assist in displaying comparison between years, while a line chart will assist in displaying trends. Taking this one-step further, overlaying charts will allow both representations to be displayed within the same graph. Like many other financial graphing requirements, Excel does not have a Chart Overlay or Graph Merge wizard or built-in function for accomplishing this.

In this recipe, you will learn how to use native functions to create a chart overlay.

How to do it...

We will start with a simple worksheet displaying profit and expenses for one year with a monthly frequency:

Excel 2010 Financials tutorial on Using Graphs

  1. Select cells A3 through C15.
  2. From the Excel Ribbon choose Insert | Column | Clustered Column.

    Excel now inserts a basic column chart onto the worksheet with a colored bar for each data set of profit and expenses:

    Excel 2010 Financials tutorial on Using Graphs

  3. Select any of the columns for Expenses.
  4. Once selected, from the Ribbon Chart Tools Group, choose Design | Change Chart Type:

    Excel 2010 Financials tutorial on Using Graphs

  5. From the Change Chart Type window, choose a Line chart, and click on OK:

    Excel 2010 Financials tutorial on Using Graphs

Excel now converts Expenses into a line along the previous data points, creating an overlay of chart types:

Excel 2010 Financials tutorial on Using Graphs

How it works...

While Excel does not provide an actual function for performing overlays or merging two charts, utilizing a chart change within a data series, we can achieve a similar result.

In this recipe, we modified the chart type for only the Expenses data series to be represented as a line while maintaining the columns for profit. This provides a much more visually effective method of displaying the difference between profit and expense.

This overlay provides graphical analysis to show that while the profit increased throughout the year, expenses actually maintained a steady decline.

There's more...

We could add extra data series to this chart and continue to modify the chart type to increase the chart overlays.

Even more...

In this recipe, the data points fell within a shared series, allowing the two charts to share the same Y-axis; however, you can still graph and overlay charts with differing points and a nonshared Y-axis.

Graphing the principal of a loan automatically

Loans are an essential portion of finance. When calculating a loan it is often beneficial to graph portions of the loan to demonstrate how those aspects may be affected by the loan terms. Loans are not calculated easily without the assistance of a calculator, and principal is not a consistent number. To create a graph based on the loan principle accurately, it is necessary to first create an amortization schedule, and calculate interest, and so on. This process can be quite time consuming and requires recalculation when experimenting with loan parameters.

In this recipe, you will learn to use the VBA code to build an automatic amortization schedule and loan principal graph that can be used multiple times to adjust loan parameters.

Getting ready

For this recipe, we will use an already created UserForm with input for principal, interest, and term. Calculating loan terms in Excel is not covered in this recipie.

How to do it...

We will start by opening the Visual Basic Editor (VBE) to code manipulation:

  1. Within Excel, press Alt + F11 on your keyboard. Once in the VBE, double click on the frmLoan UserForm to open the UserForm within the code window:

    Excel 2010 Financials tutorial on Using Graphs

  2. Using the UserForm toolbox, select the CommandButton tool, and draw a button on the bottom of the Loan Calculator form, just to the left of the Calculate button:

    Excel 2010 Financials tutorial on Using Graphs

  3. With the new button still selected, in the Properties window to the lower left-hand side panel of the VBE, change the Caption property to Graph This:

    Excel 2010 Financials tutorial on Using Graphs

  4. Double click on the CommandButton labeled Graph This.

    Once double-clicked, Excel will open the code editor and add the code for the button click event. Within this code, we will be utilizing specific sheet names such as sheet1; it is important that we are truly using sheet1 to prevent errors in Excel.

  5. Enter the following code:

    Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim y As Double
    Dim z As Integer

    x = 0
    y = txtTerm.Value + 1
    z = 4

    Sheets("Sheet1").Activate
    Range("A1").Select
    ActiveCell.Value = txtPrincipal.Value

    Range("A2").Select
    ActiveCell.Value = txtTerm.Value

    Range("A3").Select
    ActiveCell.Value = txtInterest.Value

    Do Until x = y

    Range("A" & z).Select
    ActiveCell.FormulaR1C1 = x

    x = x + 1
    z = z + 1

    Loop

    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=PMT(R3C[-1]/R2C[-1],R[-2]C[-
    1],R[-3]C[-1])"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=IPMT(R3C1/R2C1,RC1,R2C1,R1C1)"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=PPMT(R3C1/R2C1,RC1,R2C1,R1C1)"

    Range("D4").Select
    ActiveCell.FormulaR1C1 = txtPrincipal.Value
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C4+RC3"
    Range("B5:D5").Select

    Selection.AutoFill Destination:=Range("B5:D6"),
    Type:=xlFillDefault
    Range("B5:D6").Select
    Range("A5:D6").Select
    Selection.AutoFill Destination:=Range("A5:D76"),
    Type:=xlFillDefault
    Range("A5:D76").Select

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").
    Range("A:A").Range("D:D")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"


    End Sub

  6. The above-entered code should be formatted as follows, within the VBE code window:

    Excel 2010 Financials tutorial on Using Graphs

  7. Save your work, and close the VBE.
  8. While in Excel, from the Ribbon, choose View | Macros | View Macros. From the Macro window, choose the Calculate_Loan macro, and choose Run:

    Excel 2010 Financials tutorial on Using Graphs

The Loan Calculator now displays to the user. Once all of the loan parameters have been entered, the user can click on Calculate to see the loan payment:

Excel 2010 Financials tutorial on Using Graphs

If the payment is acceptable, the user can click on Graph This. Upon clicking the button, Excel builds an amortization schedule on Sheet1, and creates the loan principal graph:

Excel 2010 Financials tutorial on Using Graphs

Summary

In this article we how to use graphs for representation of financial and statistical analysis. In the next article we will cover Adding animations to Excel graphs.


Further resources on this subject:


About the Author :


Andre Odnoha

Andre Odnoha has been working within the business and technology field for many years combining his knowledge of business and information technology. With his knowledge in both software and hardware related to computer systems and a Bachelor's degree in Business Administration, Andre works to combine and utilize technology from a business perspective.

Andre has over 11 years of experience in computer software and hardware and has programmed numerous software applications designed for medical billing, e-commerce, and content management.

Books From Packt


E-Business Suite 12.1 Financials Cookbook
E-Business Suite 12.1 Financials Cookbook

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation
Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Pentaho Data Integration 4 Cookbook
Pentaho Data Integration 4 Cookbook

Statistical Analysis with R
Statistical Analysis with R

iReport 3.7
iReport 3.7

Sage Beginner's Guide
Sage Beginner's Guide

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software