Excel over form

Prevent duplicate entries in excel sheet

Many of us would have used data validation more to use the list (drop down) tool, the data validation with a minor tweak can prevent you from entering duplicate entries in a specific range of cells. Its pretty easy, heres how

1. First select the range of cells you want to apply the Data Validation rule to. Also, note the Active Cell (the unshaded cell in the selected range);
2. In Excel 2003: From the Data menu select Data Validation. In Excel 2007: On the Data tab, click the Data Validation dropdown in the Data Tools group and select Data Validation from the options;
3. On the Settings tab select Custom from the Allow dropdown;
4. In the Formula field enter =COUNTIF(selected_range,active_cell)=1 Make sure you use Absolute References (i.e. dollar signs $A$1:$A$200) ** for the selected_range and a Relative Reference (i.e. no dollar signs A1) for the active_cell;
5. Click the Error Alert tab and enter a Title for the error (i.e. Duplicate Entry!) & Click OK.

Identifying Your Conditionally Formatted Cells (XL2003/XL2007)

If you're a fan of Conditional Formatting you likely have struggled with this little problem. Once you have applied Conditional Formatting to a range (or ranges) of cells you may have needed to go back later and make changes to the formatting or the condition used. You need to make the change to all the cells you previously formatted, but how can you tell which cells have Conditional Formatting?Not a problem!

There is an easy way to identify cells that have Conditional Formatting applied.

1. Press the F5 key (or click Edit, Go To) and click the Special button at the bottom of the Go To dialog. Click on the Conditional Formats option and another option (All, Same) becomes available.
2. To identify which cells have Conditional Formatting the same as the currently Active Cell, click Same and OK. All cells on the worksheet that have the same Conditional Formatting will be selected.
3. You can now easily make changes or select additional cells to apply the formatting to.Instead of selecting the Same option, All will help you identify all cells in your worksheet containing Conditional Formatting.

PWC settles for a $97.5M settlement - (adds liquidity)

In times of bailouts, the FED  needs a helping hand. PWC just did.. for its client AIG. 

PricewaterhouseCoopers agreed to pay $97.5 million to the state of Ohio to settle a class-action lawsuit on behalf of investors in troubled insurer AIG (American International Group). In May 2005, AIG's accounting problems led to a $3.9-billion restatement, and removal of former CEO Maurice Greenberg.

The settlement is among the 10 highest to be paid by an accounting firm to settle a securities fraud class action lawsuit, according to Nancy Rogers, Ohio's attorney general.

The "partial" settlement, on Friday, came after the Ohio Public Employees Retirement System,  and the Ohio Police and Pension Fund filed a lawsuit seeking damages for investors who bought AIG securities from 1999 to 2005. In the complaint, PwC was accused of violating securities laws relating to a market division scheme allegedly involving AIG that was disclosed in 2004 and improper accounting for reinsurance and other transactions.

AIG currently is facing another lawsuit filed in May by the Jacksonville Police and Fire Pension Fund. The Florida fund accused the insurer of manipulating the market by making false statements about its financial health before disclosing a first quarter loss of $7.8 billion. PwC is not implicated in that lawsuit and in February it gave a warning sign of AIG's problems when it found that there was a "material weakness in its internal control" relating to the accounting of its credit default swaps portfolio.

Last month the U.S. government agreed to an $85 billion bail out AIG in exchange for warrants to purchase 80 percent of the company, which is selling off several units of its business to repay the loan.

American Banks ask SEC to give them a fair deal

Well to be honest, i guess Fair value accounting was reserved more for columnists and bloggers like us, rather than to be used practically.

In the latest dent, the American Banking Association (ABA) has asked the SEC to step in and override the controversial FAS 157. In a sharp critique, the ABA has gone after FASB' staff position 157-3 arguing that the revised guidance by the FASB still requires banks to mark-to-market assets at fire sale prices. "Given the importance of this issue, the impact it has on the crisis in the financial markets, and the seeming inability of the FASB to address in a meaningful way the problems of using fair-value in dysfunctional markets, we believe it is necessary for the SEC to use its statutory authority to step in and override the guidance issued by FASB," Edward L. Yingling, president and chief executive officer of the ABA wrote in a letter to SEC Chairman Christopher Cox.

I am not sure what the FASB's revised guidance (FSP 157-3) was set to achieve, after taking more flak than any other accounting pronouncement, the revised guidance should have addressed the needs of the current market situations, rather it has complicated to level beyond compare. This is why

• The revised guidance did away with the requirement of mark-to-marking distressed assets at exit prices - Kudos to this (the BIG 4 were against this)
• Distressed assets were to be valued on a composite valuation model to reflect the true value of the assets. This is a much required guidance, as in illiquid markets, the best way to mark-to-market is on the basis of the holding capacity of the investor rather than the market condition - kudos to this
• In calculating the fair value, the valuation model must factor in the liquidity risk of the asset........did i hear it right THE LIQUIDITY RISK........... ain't this a circular error.

On one hand, the pronouncement does away with the requirement of fair-valuing the assets at fire prices in an illiquid market, but at the same time requires valuation models to include the impact of liquidity risk in valuing this assets. Whats the difference.....does the need to value distressed assets arises in liquid market or illiquid markets.....

Well a silver lining, the DOW Jones is up 11% at the time of writing this blog, here's hoping we get out of this illiquid mess and FAS 157. SEC its upta you

Wall Street gets ready to PLAY-BOY

A 700 point crash...ouch. market to go below 10K ......ooof......no increments this year...oooi maa/biwi/GF, a quarter close admist dusshera and diwali.........arre mat yaad dilao yaar, then what about financial analysts on PLAYBOY ...oomph bring it on

Well when the chips are down, sex is the best thing to cheer you up. Playboy the adult entertainment magazine, might just get your spirits UP.

The adult entertainment magazine, famous for its photo spreads of nude women is launching a search for models to pose for its upcoming feature, "Women of Wall Street" for its Feb 2009 edition.

Playboy has in the past published editions with themes such as Women of Enron and Women of Worldcom, now comes up with this idea "Women of Wall Street" to cheer up moods in a dull and sombre market. "When the news gets bad, then maybe that's a chance to make people smile by coming up with something that puts a different twist on it," said Gary Cole, Playboy's photo editor.

To be eligible for the shoot, the model (female) must be 18 years of age and must have worked in a financial institution. Well wonder what accountants will have to comment on the true fair value of the fairer sex .... i mean the financial statements as per FAS 157.

So coming Feb 09,  for starters; quit looking at ICICIdirect.com (by popular vote) you aint gonna get richer, stop reading financial news portals....... just oogle by visiting playboy.com to get real hot Wall Street action. I bet even your boss may put in his shirt.

FORGET THE BULLS AND THE BEARS, ITS THE REIGN OF THE BUNNY.

P.S - Disclaimer -  this article is meant only for readers over 18........ I do not hold any playboy.com stocks (hmm not a bad company to invest in - what do you say) and neither do i have any affiliations with LAY-MAN (lehman)

GE serves warrants for Buffet

General Electric received a helping hand in the wake of US's "credit crisis" from the BANK BUFFET. A lot of valuers and investors around the world have been speculating to know the true valuations of GE, with its opaque mix of finance and manufacturing. Well the BANK OF BUFFET just figured it out.

GE issued warrants of preferred stock worth $3 BN @ $22.25 per share, $2 below its last traded price and almost half its stock price at the beginning of the year.

GE who, wants to hold on to its AAA rating, also offered to issue another $12BN in form of common stock to other investors.

Many analysts felt Buffet's move is a show of returning the favour to the US, by giving a helping hand and bringing back confidence in resurrecting the US economy. Buffet who last week invested $5BN in Goldman Sachs, has now contributed personally close to 1% of money equivalent of the massive $700BN bailout package.

The soothsayer from Ohama did play it tough in striking the deal, with the interest rate on the stock pegged at 10%, a shade lower than GE's cost of equity, which is pegged to be in the range of 12% to 14%, and much higher than GE's average cost of debt @5.5%.

Many investment bankers felt that, this move would further disturb the sentiments of the economy, as they expected the big giant "GE" to give a helping hand to the US economy.

While GE boasts some $846 billion in assets, close to $600 billion of that is on the finance side--either in the form of loans or leased assets like airplanes. There's also $100 billion in intangibles and goodwill, leaving less than $200 billion in hard assets like buildings, equipment and inventory. It has about $90 billion in commercial paper, which is short-term, low-cost borrowing made for as little as one month. Commercial paper costs have risen for G.E., as it has for others, though it has been tapping that market throughout the crisis

Warren believes he may have bought GE's stock at rock bottom & believes the US economy have hit the bottom. If he is right then the US conglomerate will recover, will sell and lease more planes, automotive and finance them.

If he is wrong, then GE will be forced to borrow more or sell its stock much cheaper. Hoping Brand Buffet's gamble pays off this time.

FAS 157 fair value - NEO gets Mr. Smith in Part 1

Just a couple of days back, i had commented on the SEC's move to change fair value accounting. This move was short changed with the bailout package being rejected.

Well on numbers, this is what fair value accounting has done so far since its inception in Dec 07.

Citibank announced a further $15-21BN write down for estimated Q3 on its distressed assets.

Morgan stanley wrote down $3BN in its Q3 earnings (not so bad as many expected $6BN)

Bank analysts predicted total write downs in excess of $64B on collateralised debt obligations from all investment banks.

Now with the bailout package being approved, heres what the SEC has proposed for Q3 reportings, which could just about reverse some of the write downs and infuse life into the financial statements

• Mark to Marking of assets and liabilities in inactive markets can now be done on the aandy dandy mathematical models instead marking them on the exit prices or fire prices in illiquid markets - which means auditors and accountants go back to reading more of CON 7

• SEC has allowed managements to use their internal assumptions in fair value accounting while maintaing the hieracy of using fair value techniques.

The crux of the SEC statement is primarily aimed at ensuring distressed assets are not marked to market in inactive markets at throwaway prices.  The concept of a fair value measurement assumes an orderly transaction between market participants. An orderly transaction is one that involves market participants that are willing to transact and allows for adequate exposure to the market. Distressed or forced liquidation sales are not orderly transactions, and thus the fact that a transaction is distressed or forced should be considered when weighing the available evidence. 

To make matters interesting, the US BIG ACCOUNTING FIRMS, which all along were silent on the rejection of the $700BN bailout package, were opposing congress efforts and were gearing to lobby to scrap the changes in the mark to market rules.