-Advanced

You are currently browsing the archive for the -Advanced category.

Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract.  As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe??

What came immediately to mind is the fundraiser tracker.  You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money:

0823bars2_0

 

I wanted to create a visualization like that, in Excel, next to my family’s finances.  Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable.  How do I do it?

 

 

Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts.  Wanna draw a circle with an absolutely rectangular bar chart?  Let’s do it visually:

Step 1 – Create a stacked bar chart.  The number of stacks that you need depends… and trust me for now that you need 4 for a circle:

0823bars2_1

Step 2 – Make sure you have enough bars.  100 is always a good starting point in my opinion.

0823bars2_2

Step 3 – Set the gap between bars to 0%.  Now this chart looks like some European country’s flag.  Still quite rectangular:

0823bars2_3

Step 4 – Adjust the height of these 100 bars so they look like this.  Note how some stacks are much taller than others:

0823bars2_4

Step 5 – Resize your chart into a square.  Clean up the colors and update the axes ranges, and…

0823bars2_5

Ta-da!  Here’s the circle!  Don’t like how pixelated it is?  Add more bars!

0823bars2_6

 

The key here is to think of a picture as a rectangle with w-by-h pixels.  Then you can replicate it with w stacked bars each having a maximum height of h.  How many stacks you need depends on how complicated your graph is.  Is it possible in theory to draw a Mickey Mouse?  Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors.  You’d need to either set up formulas or a lookup table for the height of these stacks on each bar.  For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0.

 

Back to my original goal of drawing a target tracking graphic.  I decided to go with a house complete with a chimney, even though our home looks nothing like it.  It involves a handful of vertical, horizontal, and sloped lines:

0823bars2_7

 

The shape of my house, including where the chimney is, is entirely parameter-ized.  So by updating a few cells I can get to alternate versions like these:

0823bars2_8

 

Lastly, I needed to fill it in with the progress tracker, as follows.  Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value:

0823bars2_9

0823bars2_10

 

Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well:

0823bars2_11

 

This way, I’ve got a bar-based graphics that updates automatically.  The only thing left to do is actually paying off that mortgage!

This article demonstrates a simple VBA method to utilize the Goal Seek tool automatically, similar to a native Excel formula.

Goal Seek is an Excel built-in tool that uses numeric approximation methods to “back solve” for an input value to a system of formulas when given a desired output value.  If you are not familiar with Goal Seek, many tutorials online can guide you to using this tool.  I hate it, and try to avoid it at all costs… but like everything else that I hate, there come times when I just have to face it like a man.

The problem?  For one, it’s not transparent, and we can’t exactly tell what Excel did in the background.  But more importantly, it’s a pain to apply and to udpate.  While all other formulas can automatically re-calculate upon changes to the input values, Goal Seek requires you to click on the menu to bring up the dialog box, then fill out three input boxes manually.

See the spreadsheet above.  Everything in blue are input values to this dynamic model, which can affect the results.  We want to set E14 to a value specified in D3, so these two cells correspond to the first two parameters of Goal Seek (“Set cell” and “To value“).  As an alternative, we put the difference between the two in F6, and set this cell to 0 (x=y is equivalent to x-y=0).  This approach will slightly simplify the coding later on, and is an easier check for the user.

Download the Sample File

The “Necessary Adjustment” in D6 plays an accomondating role in adjusting each of the values so that the final calculation reaches target.  For Goal Seek, it is the third parameter “By changing cell“.  Initially we have no idea what this adjustment should be, and leave it at 0.  At this time, F6 is showing 0.8%, indicating that the necessary criteria haven’t been fulfilled.

Let’s do a Goal Seek now – set F6 to 0 by changing D6.  If you record this into a macro, say Macro1, you can quickly repeat it by using an assigned shortcut key stroke, or hook it up with a command button for an easy UI.  A lot of people do exactly this as an enhancement to the otherwise awkward model, but it’s not good enough.  Let’s automate it.

Press Ctrl+F11 to bring up the Visual Basic Editor to see the embedding code to Macro1, and you’ll see this line of code:

Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)

Fair enough.  It’s almost in plain English, and shouldn’t take an VBA expert to understand.  The next step is to copy and paste this line out of Macro1, to somewhere more automatically executable.

Browse to “Sheet1” under “Microsoft Excel Objects“.  Either type or use the drop-down box to create the following VBA event:

 Private Sub Worksheet_Calculate()
   
 End Sub

This subroutine will be called every time the “calculate” event is triggered on this worksheet.  Depending on whether you set the calculation to Automatic or Manual in Tools->Options, this happens when any formula-based cell changes value to a new input value.  The idea is to paste the Goal Seek code here so it will be called whenever F6 may change; however, let’s keep it in a separate function for the sake of good programming practice:

Private Sub Worksheet_Calculate()
   CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
   Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
End Sub

In the Visual Basic editor, it should look like this:

Now give it a try!  Change any of the blue input values and you’ll see Goal Seek working by itself to come up with the new adjustment values.  Awesome!  We’re done now in terms of automating a tedious task.

Note: Always save your work before you test out the macro.  depending on the version of your Excel, the repetitive call to Goal Seek may result in a run-time error.  However, the next part of this code will solve the problem.

Have you noticed how slow this enhancement has made your model?  Maybe not, if you have a relatively new computer or a small workbook like the sample file.  But if you add some temporary debugging code to the function, you’ll notice how many times your function is (unnecessarily) being triggered when you change any of the input values:

Private Sub CheckGoalSeek()
   Range(“F3”).Value = Range(“F3”).Value + 1
   Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
End Sub

Give it another run and, hey:

You just changed one input, and Goal Seek ran close to 2,000 times!  Why???

The subroutine Worksheet_Calculate is triggered every time any function calculates a new value.  There are six cells (in column E as well as cell F6) that depend on the changing cell D6.  As Goal Seek cycles through possible values as the input, all these six cells have to update – and in turn call up more instances of Goal Seek.

Imagine you want to paint a wall red.  Every time you see that the wall isn’t entirely red, you call up a painter to do the job.  As soon as the first painter gets to work, you realize that it’s still not entirely red, so you call up another one.  Even when you’ve got 30 painters to simultaneously working on the wall (if that’s physically possible), there might still be a white spot remaining and you make yet another phone call… chances are, the job would be complete before the last five painters arrive on site.  This algorithm gets the job done, but heck, is super inefficient.  Add a little check to the process and avoid ever calling more than one painter!

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6”).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“F3”).Value = Range(“F3”).Value + 1
      Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
      isWorking = False
   End If
End Sub

Adding in the static variable helps flagging whether the Goal Seek function has been employed.  The first time this function is called, it raises the flag and will not release it until the full iteration of Goal Seek completes.  During this time, this subroutine would still be called 2,000 instances, but it would simply bypass all the code and exit.  Try again and F3 increments only once per change of input values, indicating that the Goal Seek isn’t running wild to waste your computer’s resources.  That’s what we wanted, and now we can remove that debugging code.

The first part of the If statement checks whether the solution already exists – i.e. if Worksheet_Calculate is being triggered by some formula updates that are completely irrelevant to this system.  If so, there’s no need to call Goal Seek at all.

Here’s another catch: if you want a robust model, you’ve gotta consider any potential for error.  How about an invalid input value?  Keeping the target overall raise at 5% and changing the max raise to 0% is for sure an impossible scenario, and Goal Seek goes crazy as follows:

We realize that wasn’t very smart and change the max back to 6%… and geez it doesn’t work!  The adjustment factor had been thrown too far off anything reasonable, and now Goal Seek doesn’t know what to do with such an awkward initial guess.  Our model is stuck, and that ain’t cool.  A quick way to fix this is to reset the initial guess every time we call Goal Seek:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6”).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“D6”).Value = 0
      Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
      isWorking = False
   End If
End Sub

It’s a slight waste of resources if educated and reasonable input values can be expected at all times, but definitely a worthy investment if you want to prevent ever getting “stuck”.

Lastly, it’s dangerous to use absolute cell references in macros.  If columns or rows are added or removed, the key cells would have changed locations (from D6 to D7, for example) and the macro will get confused!  The best practice is to name the cells and refer to them by name, like the following:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“GoalSeekCell“).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“ByChangingCell“).Value = 0
      Range(“GoalSeekCell“).GoalSeek Goal:=0, ChangingCell:=.Range(“ByChangingCell“)
      isWorking = False
   End If
End Sub

Now we have a robust macro that automatically updates Goal Seek!  Enjoy!

Download the Sample File

 

This article discusses an issue on rounding error, specifically related to breaking down a total amount with a trend/inflation rate.  It’s more of a math problem than an Excel problem, and if you work in the healthcare industry, this problem probably will look familiar to you.

Note: “Trend” here is assumed to be a compounding annual cost inflation.  When “trending” an amount from one period to another, the length of time is measured between the mid-points of the two periods.

Suppose you made a projection for your client’s healthcare spendings to be $12 million in the upcoming calendar year, assuming 10% annual trend.  Now the client wants to break that down to monthly figures.

The answer is not $1 million for each of the 12 months.  The force of trend makes this spending an increasing function over time, i.e. February should cost slightly more than January, and so on.  The industry-standard formula is to apply the 10% trend, from the mid-point of the year to the mid-point of each month, i.e.

Monthly Amount = Annual Amount / 12 * (1 + Trend) ^ ([mid month] – [mid year])

While this is a perfectly valid and defendable approach, the results always have a problem – the “rounding error”.  As seen in the table below, adding the 12 months of breakdown back together produces a slightly higher sum than anticipated.  This error is usually small ($4,511 in this case, < 0.1%) and people tend to overlook such insignificant imperfection.


Numbers don’t add up

Note: This type of difference is widely known in the industry as a “rounding error”, but technically it isn’t.  Rather, it is a methodological inaccuracy.

But not everybody can tolerate imperfections – certain demanding bosses/clients could get very upset if your basic numbers don’t add up!  To correct this situation, I’ve seen people throw that $4,511 in one of the months, break it out evenly across the 12 months, or use some GoalSeek magic to add a margin to each number until the total comes out right.  None of these methods is pretty.

Take a step back and think like a Calculus student again… the trending cost can be represented as the area under curve of an exponential function, and the aforementioned approach to monthly amounts is a discrete numerical approximation of the integral!


The pink bars are the results of the previous method, which closely but not perfectly approximate the area under the blue (exponential) curve.

Now, if you can take a basic anti-derivative from point a to point b (one month to another), you’ll have a solution that’s better than a good approximation:

Monthly = Annual / Trend * ((1 + Trend)^(i/12) – (1 + Trend)^((i-1)/12))

The resulting formula is difficult to understand when you look at it for the first time.  However, once you understand the concepts, it’s very easy to explain.  The monthly breakdown sum up beautifully to the exact $12 million, so you won’t have to do any unsightly tweaking!


Numbers add up perfectly!

Download the Sample File

The sample file contains a hand-written derivation of the formula.