14 Feb 2008 by Chetan Shah.

For example :
Set the background color of Row 1 to Blue if the cell value of B1 is 5
Set the background color of Row 1 to Red if the cell value of B1 is 10
Set the background color of Row 1 to Orange if the cell value of B1 is 15

Following are the steps to highlight the entire row based on a cell value.

1. Select the entire Row (Row 1 in our case)
2. Go to Format –> Conditional Formatting
3. In Condition 1, Select “Formula is” from the first select box.
4. Enter “=IF($B1=5,1,0)” in second select box.
5. Click the “Format” button
6. Click on “Patterns” tab.
7. Select blue color.
8. Click ok
9. Click “Add” button
10. Follow steps 3-8, the only change is : in the formula enter 10 instead of 5 and select red color.
11. Click “Add button
12. Follow steps 3-8, the only change is : in the formula enter 15 instead of 5 and select orange color.
13. Done

If you want to apply it all rows in the sheet.

1. Complete the above steps. This will setup first row
2. Select first row
3. Click on “Format Painter” icon from your Standard Toolbar
4. Select all the rows on which you want to apply Step 1’s formatting.
5. Done!

Posted in Technology | 1 Comment »

11 Feb 2008 by Chetan Shah.

You might have come across the statement from your financial adviser or whoever, that paying off mortgage is not a wise thing to do because of IRS tax return deduction which in turns lowers your mortgage rate (effective rate). Also, if you invest the additional money which you apply towards principal payment in stock/bond market you can get better returns. I have even heard people saying that paying off your mortgage is liking putting money under your mattress.

I am not disputing the above facts but I still believe that it is wise to pay off your mortgage sooner for 2 reasons.

- Employment Instability
Layoffs are becoming a way of life. It is not uncommon to hear that such and such company is cutting costs and planning to layoff its employees. With offshoring on an increasing trend, the probability that an individual will have to face atleast one layoff in her career is much higher than what it has been in the past. In such a situation keeping ends together really becomes an issue as most of us do not have 6 months of emergency cash available in our checking accounts. Missing mortgage payments may result in bank’s foreclosing the house (sub prime crisis aggravated this situation) and can severely impact one’s credit report. Agreed that the person will not be out of job forever but wouldn’t it be less stressful if you had paid off or at least nearer to completion to “owing” your biggest financial asset.

- Rental Property
Paying off your home mortgage will enable you to buy a rental property. Not only you will be owner of your home but someone else will help you buy an investment property. By the time you are about to retire, chances are the rental property would also be paid off. This will result in a steady stream of rental income. Agreed that one can buy a rental property when she is paying mortgage on her primary residence, but having paid off one’s primary residence will leave her with more cash in hand to invest, with peace of mind that even if things go wrong she has her house to lean on.

Needless to say I will be putting my rebate check from the latest economic stimulus package and income tax refund towards my primary residence mortgage.

As always your comments and suggestions are most welcome.

Posted in Personal Finance | No Comments »

9 Feb 2008 by Chetan Shah.

Umbrella (excess liability) insurance policy is a perfect way to have extra personal liability insurance. Particularly now a days when the chances of getting sued are higher. Umbrella insurance kicks in after the personal liability limits are exhausted on your homeowners or auto insurance policy. The cost of getting a $1Million coverage is around $150 dollars. As with any insurance policy, it is imperative to get this policy from a reputable insurance company.

From tax deduction perspective however, the umbrella policy premium is not deductible from your income tax return. Much like homeowners insurance you cannot deduct umbrella policy premium. However, if you have a rental property and you purchased the umbrella policy because of the rental property then you may claim the insurance premium as part of your expenses on Schedule E of Income Tax return. The policy should itemize how much premium is being charged due to the rental property. Only the premium towards your rental property can be claimed as an expense on Schedule E.

Posted in Personal Finance | No Comments »

8 Feb 2008 by Chetan Shah.

Whenever a customer requests a homeowners / auto insurance policy quote from any insurance company, the company checks the past claims history for your property before they give you a quote. Like a personal credit report, it is imperative to keep the claims report accurate so that you as a customer are not charged any extra insurance premium for the claims which you never filed.

In order to see what claims were filed against your property, every customer is entitled for a free CLUE (Comprehensive Loss Underwriting Exchange) report. This report has claims history (5 years) of the property in consideration. This report can also be used at the time of buying the property to validate the sellers disclosures. Additionally it can used to make a more informed decision whether the insurance premium quote given by the insurance company is reasonable. It will be prudent to check this report every year before you renew your auto or homeowners insurance.

Every individual has a credit score, so does every property. One of the data values which determines the credit score of the property is derived from CLUE report.

To get your CLUE Report please visit ChoiceTrust

Posted in Personal Finance | 1 Comment »

7 Feb 2008 by Chetan Shah.

While designing a software application from scratch an important point to consider is the underlying database design’s capability of supporting future analytics. Most applications are designed on the basis of user requirements and more often than not the system requirements are overlooked in a rush to meet the delivery deadlines. Requirements such as “Capability to harvest numbers in future” are not explicitly stated nor can be expected to come from the end users.

This is one of the areas where the technology team can influence requirements and make this type of needs as part of the software requirements.

The advantages of having database design which takes care of analytics are as follows :

1. Ability to determine which functions are lightly used. There are times when a complicated piece of functionality is used so
rarely that it is not worth spending maintenance dollars on that functionality, the better option is to just sunset that functionality. It also enables the technical staff to determine which areas to concentrate on to exceed customer expectations by improving the response times/usability of highly used functionality.

2. Ability to determine which business rules are really effective and which are creating more work rather than making end users
efficient.

3. Ability to determine application usage peak times. This will enable the technical staff to schedule batch loads/downtimes in off peak
hours.

4. Ability to audit user actions. In most financial organizations this is a must as part of Compliance requirements.

5. Ability to apply advanced statistical techniques to drive business process improvement.

Capturing application numbers keeping the above advantages in perspective will directly translate into an application which is stable (because we can identify the pain points faster), robust (as it will remain effective as time progresses) and highly maintainable (by capturing and mining performance, usage numbers)

Posted in Technology | No Comments »

6 Feb 2008 by Chetan Shah.

Often times there is a need to generate a matrix. For simplicity sake, let’s assume that you have a table containing Products and States in which they are sold. Microsoft Excel has a very efficient utility called pivot tables which one can leverage to generate the required matrix.

Following are the steps to generate the desired output using Microsoft Excel’s Pivot tables

1. Go to Data –> PivotTable and Pivot Chart Report
2. On Pivot Table and Pivot Chart Wizard Step 1 of 3
2a. Select the Microsoft Office Excel List or database
2b. Select “Pivot Table” for the 2nd question.
2c. Click “Next.
3. Select range (where your data resides on the worksheet) and click next
4. Determine where you want to put this pivot table and click next
5. Click Finish
6. Drag and Drop the “Product” in “Drop Row Fields Here” area.
7. Drag and Drop the “States” in “Drop Column Fields Here” area.
8. Drag and Drop the “States” in “Drop Data Items Here” area.
9. Done!

If you have a product which is not being sold in any states and if you want to show it on the matrix, you can add a row to the product/states table. Set the value of state to . If you followed the above mentioned process all you will have to do is right click on the pivot table area and hit “Refresh Data”, your matrix will be updated in a split second!

Posted in Technology | 5 Comments »