cellMatrix.net

Compounded Change

When forecasting it's often useful to know what the compounded rate of change over a given period of time is for a selected variable. As an example, let's say a pharmacy dispenses 50ml of saline in period 1. The following periods it dispenses 57.17ml, 59.25ml, 64.23ml, 65.18ml, and 71.50ml. The actual rates of change for each period are 14.34%, 18.50%, 28.46%, 30.36%, and 43.00% respectively. What formula can you use to calculate a compounded rate of change which will in turn allow you to build a simple linear forecast of how much saline will be dispensed into the near future?

Simply put, the formula is:

((Last Period Data / First Period Data) ^ (1 / Number of Periods)) - 1 

Given the example above, the formula returns a compounded rate of change equaling 7.42%.

The compounded rate of change is calculated as:

(( 71.50 / 50.00 ) ^ ( 1 / 5)) - 1

The result can be proved out as follows:

Finally, a chart visually shows the actual growth (blue line) vs. the compounded growth (red line):

Given the compounded growth rate of 7.42%, the screenshot below shows how the rate is applied to the historical data to estimate two periods into the future (periods six and seven).

The chart has been modified to include the projection periods six and seven:

Please note that this is a very simplistic example that is useful for statistical forecasting only.

Extract Data from an Excel Chart

From Microsoft, this Knowledge Base article provides a macro that allows the user to extract data from a chart.

In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged in some way. When the source data to a chart is lost, the data can still be retrieved from the chart itself, by using a Microsoft Visual Basic for Applications macro.

First add a worksheet into your workbook and call it "ChartData". Next, add the following macro to a module within your workbook. Finally, run the macro to extract the chart data.

Sub GetChartValues()
   Dim NumberOfRows As Integer
   Dim X As Object
   Counter = 2

   ' Calculate the number of rows of data.
   NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

   Worksheets("ChartData").Cells(1, 1) = "X Values"

   ' Write x-axis values to worksheet.
   With Worksheets("ChartData")
      .Range(.Cells(2, 1), _
      .Cells(NumberOfRows + 1, 1)) = _
      Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
   End With

   ' Loop through all series in the chart and write their values to
   ' the worksheet.
   For Each X In ActiveChart.SeriesCollection
      Worksheets("ChartData").Cells(1, Counter) = X.Name

      With Worksheets("ChartData")
         .Range(.Cells(2, Counter), _
         .Cells(NumberOfRows + 1, Counter)) = _
         Application.Transpose(X.Values)
      End With

      Counter = Counter + 1
   Next

End Sub

Auto-Sort Chart

This example allows a fixed range for inputs, automatically sorts the results, and charts those results. The input area and chart source look like this:

The formulas in column D calculate a very small number that is subtracted from the original. The purpose is to prevent possible duplication of entries. Below is the formula in cell D4.

=C4-ROW()/10^10

The formulas in column F sort the data in column D from highest to lowest value. Below is the formula in cell F4.

=LARGE($D$4:$D$13,ROWS($B$4:B4))

Finally, the formulas in column E utilize the INDEX and MATCH worksheet functions to perfrom a left-sided look-up on the data in column F. Below is the formula in cell E4.

=INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0))

The chart below provides the end-result.

Salary Forecasting and Spread Model

These entries showed how to perform a variance analysis given two, three, or four components of variance:

• Variance Analysis - Two Components
• Variance Analysis - Three Components
• Variance Analysis - Four Components

The logic illustrated in these posts can be applied to annual and monthly forecasting models. The example below illustrates how one could build a monthly salary forecasting model given a salary, FTE, and paid day history and projection. The example assumes that the salaries and FTEs for the period spanning January 2007 through April 2007 are given. The task is to project salaries on a per-month basis given a 4% salary increase that occurs in October 2007, a 5% salary increase that occurs in August 2008, an increase of 1 paid day between 2007 and 2008, and an increase of 11 FTEs that occur between 2007 and 2008.

The cells shaded yellow provide inputs to the template. Of note is the trend in salary rate which is shown in the chart below. The white bars show the actual rate and the blue bars show the projected rate. The salary rate, given the FTE, paid day, and average salary calculations, proves to be a step-function as it moves from $1.10 to $1.15 to $1.20 based on when the salary increases occur.

In addition to salaries, models that forecast and spread revenue, discounts, benefits, supplies, services, and more can be created based on the logic in this template.

Auto-Color Chart Bars Version 2

This next variation of the color-bars macro allows you to assign colors to the chart bars based on the background colors of selected cells. For example, assume the screenshot below:

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer
    Dim Color As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Color = Rng.Interior.ColorIndex
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        Pts.Interior.ColorIndex = Color
        Cnt = Cnt + 1
    Next Rng

End Sub

The final product looks like the chart below.