Monday, December 29, 2008

Disable automatic check out in Visual Studio 2005/2008

To disable automatic check out when editing files in Visual Studio (2005, 2008):

Go to Tools menu, select Options, expand Source Control.

Change the section called “Checked-in item behavior” for On Edit (and On Save) to whatever you want. It’s defaulted to Check-Out Automatically.

Friday, December 26, 2008

Water heater leak

Found a small pool of water at the base of my water heater Christmas eve and thought I'd have to buy a new water heater. That would have been a very expensive repair bill! Luckily, it turns out the leak is due to the inlet adapter pipes rusting out due to my water softener. I guess the salt that I used to use caused the galvanized pipe to leak. So water was being forced out through the pipe at the top as the tank filled up. I've been using potassium tablets instead of sodium for my softener now, but I guess the few years of using salt took it's toll already. Tough to find parts and plumbers during a holiday break like Christmas.

Update: Need to replace water heater due to internal tank corrosion. I'll need to make sure to inspect and/or replace the anode every 5 years or so to avoid this problem in the future.

Monday, December 15, 2008

Virtual PC resize hard drive partition

If you have a VPC install and need to expand your boot virtual drive, unfortunately, the only solution I'm aware of is using a commercial application.

However, there are two work-arounds:

1. The slow way, which is to create a 2nd, larger virtual hard drive, mount that, boot into the host, then using a ghosting software, ghost (image) your primary hard drive onto the new, larger drive. Then shutdown the OS, modify the setting so that the 2nd hard drive is now your primary boot drive and then remove the original hard drive.

2. The fast way, is to create a 2nd hard drive, boot into the OS, assuming a Windows XP guest, right-click on My Computer and select Manage, and go to the Disk Management. Right click on the new hard drive, select Change drive letter. Remove the drive letter. Click Ok. It should unmount the drive. Then click Change drive letter, Add, and check "Mount in the following empty NTFS folder." Create a folder on your C: drive and select that as the mount point for your 2nd drive. Done! If you're running a Linux guest, you should be able to setup the drive on a folder mount point as well (just Google for the instructions).

The 2nd option allows you to add hard drives as folders of your C: drive. This indirectly expands the available space in your VPC without having to modify the size of the original drive. This is useful if you need more storage for non-system files (ie. applications, data, etc.) This will work for any of the folders on your drive, including the Program Files folder, if needed. But the mount point should be empty. Otherwise, when the new hard drive is mounted, it'll "hide" the contents of the folder. But if you need to mount to an existing folder, just rename it first, create an empty folder with the same name, and then mount the hard drive to that folder.

Saturday, December 6, 2008

Excel Offset function

One thing I've noticed with beginning (and some advanced) financial analysts is that they either haven't been taught how to use the Offset formula or they're not clear what its usefulness is when creating models in Excel.

I use it all the time to create schedules that are dynamic and allows you to work with ranges based on some criteria.

The most common use for offset to me is to provide a way to get the annual total of some monthly range without hardcoding the range itself (ie. not using =sum(a1:a12)).

To create an annual schedule from a monthly schedule, in a new sheet, I first create a row that has the year in the columns B:xx (assume in row 1), where xx is the number of years. They should be numerical (ie. 1, 2, 3, etc.). Rather than type "Year 1", enter 1 into the cell and set the format to be custom. In the cell custom format, use the format: "Year" 0 (include the quote and notice the space between the close quote and the zero).

Then I set the row labels to point to the corresponding label in the monthly schedule (=MonthlySchedule!A2) starting in cell A2 of the annual sheet (It doesn't have to start in the same row, but you'll want to make sure each row label is relatively located in the same place as the first row label. You'll see why later).

Under year 1, for the first row of data, I place the formula as such:
=sum(offset(MonthlySchedule!$B2,0,(B$1-1)*12, 1, 12))

B2 represents the first row's first month data in the monthly schedule. B1 is the annual sheet's year 1 heading value.

Now you can copy that formula to every cell under each year for each row labeled. As long as the label rows in the annual sheet are in the same relative positions as in the monthly sheet, the data will be correct. Now you've just created an annual report without having to manually set each 12 month range! If you add more months, just copy the last column more years over! If you add more rows of monthly data, just copy the rows in the annual (making sure it corresponds to the monthly row's relative position).

What the offset function lets you do is to not only move your starting position of a range, but also how many rows and columns worth of data to return. And because it returns a range of data, summing the returned array is possible.

In this case, I want to move over 12 cells for every year, minus 1 (since in year 1, we don't want to shift over at all). Then I want 1 row, 12 columns worth of cells returned in an array. That array is summed and the result is the 12 month summation starting in month 1.

Other useful ways for Offset include using a drop-down to select a year for display, determining how many months or cells you want some calculation done on, etc.

I've used Offset in my models not only to create my annual schedule, but to display the total value of a trailing x month worth of finances, given a selected month, using drop-downs and offset into my monthly financial schedule. I've also created reports that show the yield on investment for any given year selected without having to predefine the results (I calculate it based on a schedule by using offset to span the selected amount of periods).

Offset is a very powerful and useful function for creating dynamic models where static formulas are used.