# Excel 2010 Financials: Using Graphs for Analysis

#### Excel 2010 Financials Cookbook

\$23.99

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

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

(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.

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:

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:

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:

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:

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

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:

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

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 now presents the histogram graph of net profit frequency:

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:

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.

(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:

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:

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:

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:

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:

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

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:

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:

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

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.

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

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

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

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

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

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

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

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

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

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

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

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:

(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:

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:

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

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

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

## 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.

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:

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:

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:

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 IntegerDim y As DoubleDim z As Integerx = 0y = txtTerm.Value + 1z = 4Sheets("Sheet1").ActivateRange("A1").SelectActiveCell.Value = txtPrincipal.ValueRange("A2").SelectActiveCell.Value = txtTerm.ValueRange("A3").SelectActiveCell.Value = txtInterest.ValueDo Until x = y   Range("A" & z).Select   ActiveCell.FormulaR1C1 = x   x = x + 1   z = z + 1Loop   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:

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:

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:

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:

# 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:

### Books to Consider

Excel 2013 Dashboard Design [Video]
\$ 72.25
Instant jQuery Flot Visual Data Analysis
\$ 12.99