Answers to your Excel Questions

Sunday: 4:30 p.m.-11:30 p.m

I don't know why this is, I am not a major blogger, but my blogs always post to June 15 for some reason so it never automatically shows the actual time and date I blog.

I want to answer the two questions that have been posed about excel and I am sorry for getting to them so late. Here are my thoughts.

The questions revolve around how to train yourself or what functions to be proficient in. If you have had a chance to read the post I put up about the Excel Whisperer you probably salivated over the fact that he can do what he does. Basically, to master excel you need to be proficient in 3 areas (1) Navigation (2) Formulas and Formatting (3) Linking, and making your spreadsheets Dynamic.

Navigation This is the foundation for being a good analyst in Excel because this skill cuts down on time, and time is of the essence in this job. What do I mean by navigation? Know your shortcuts. Resolve NOW to never use a mouse again, and this goes for programs outside of excel, like your email box and navigating Windows XP or Vista. For example if you want to "show desktop" most of us might click the little icon in the bottom left hand corner, but did you know if you hit the windows key + D it does it for you? Those types of things are crucial. So lets role play. You are in excel and you want to auto adjust the column width. You could reach for the mouse and double click on the line separating the columns or you can stop and ask, "how would I do this without a mouse?" The ALT key is a great place to start. Ask yourself, "What am I doing to the columns? I am formatting them." So you look at the top of the tool bar and under the O in Format you see a line so you hit O..."What next?" Find the word Column because that is what you are formatting, now what? You see that auto adjust or auto fit there is an A underlined. So now you know ALT+OCA auto adjusts the column. Eventually you will memorize the lists from the ALT menu and things will be much smoother. They are pretty easy to memorize because they are somewhat mnemonic. You will also begin to notice that there are EVEN SHORTER ways to do things, for example I can't remember the long way to pull up the format cell box ALT+OC? Not Sure. I just know that if you hit CTRL+1 it pulls up the box you want. Learn to Navigate fast. One of the tendencies I have is to grab the mouse when I get flustered or things get really time sensitive. I wish I had spent even more time learning to navigate.

Formulas and Formatting. Formatting is more of a navigation thing, so I won't spend too much time on that. It is important to know how to format cells or erase grid liness or whatever. When it comes to formulas, this is what makes the Excel Whisperer so good. Learn to think, "There has to be a better way." Read the post on the Excel Whisperer. This is what I learned: If you stop, take the time to really think, you may find that you were faster even though you stopped to think. Here is a good method to use that the Excel Whisperer taught me long ago. Lets say you come to a complex issue that you think could be resolved by a formula, you just don't know where to start. (1) First take a step back and determine what your end result should look like (2) try to dumb it down a little, rather than fly off the handle and try linking a billion different spreadsheets to one location, open a new workbook and very simply try to create a smaller boiled down replica. You may only need a couple of cells. Then here is the important part, as you search possible solutions, when you discover the answer SAVE THE WORKBOOK, or more save the spreadsheet and name the Workbook, Excel Answers. When you come across another problem, open the workbook, start a new spreadsheet, solve the problem, and save the workbook. What does this do? Now you don't have to commit complicated formulas to memory. Six months from now you might come across a similar problem and say, "Wait. I have done something like this before. Ah, yes! My Excel Answers Workbook." This is what my friend has done and he has a huge portfolio of workbooks with little spreadsheets with solutions to past issues.

Dynamic Spreadsheets. This is where it gets tough for me and you may want to consult The Analyst on this. To answer the question, how do we practice modeling or building a model or how do we use excel to do financial stuff. I haven't had much practice on this yet in my internship, but I would suggest finding a model and rebuilding it. Go through it over and over again. Another thought might be to role play with real companies. Sit down at your computer and say, "I want to merge McDonald's and Burger King, what do I need to do?" When you come to questions, Google it or ask a professor. Or, "If Blackstone wanted to take Yahoo private, what would the model look like?"

The other suggestion I would make is find a textbook that comes with CDs and usually they will have a companies statements in them that the textbook company imported in excel. Those are free, already built financial statements that you can play around with and try to link together. A lot of this is easier said than done, but good for you guys for asking. I would say the reason why analysts work so late, is for these reasons; as fast as they are in excel, they probably wish they were faster, so they could go home earlier.

I hope that has been helpful, keep in mind this is just my advice. You may meet others that have better ideas.

Anyway, let me tell you about my day, because there is some funny detail! When I got in at 4:30 I quickly had to look up total share count for a company over the last 3 years for each quarter. You can find that on the first page of the SEC filings. It took 10 minutes.

I then got comments back from an analyst on the company profile I was doing. Interesting how The Analyst in his latest blog said that he has done a lot of Company Profiles, well, so have I. It must be across the board. Basically what goes into a profile is a company overview, a price volume chart of the stock, analyst recommendations from Bloomberg terminal, (Type HPQ F8 ANR and it brings up analyst recommendations for HPQ), a bio on management and board of directors, and a quick spread of some ratios like gross margin, revenue growth, etc. I am sure different coverage groups have different profile formats but that is what it is for my group.

Anyway, I got nailed in my comments for not paying attention to detail! I mean killer stuff that I should have caught, like "fro" which should have been "from" "competed" which should have been "completed" I missed some things that should have been italicized and some things that should have been bolded. I was a little embarrassed and got the old, "You need to print stuff off and proof read before you turn it in." They are right, the sad thing is The Analyst mentioned that in his blog and I failed to remember it when it came to a time I needed to print. The biggest mistake? Not being consistent on each page. I think the analyst reviewing my work would have been happier had I been consistant on each page, even if the consistancy is wrong. In most cases there is no right or wrong answer, JUST BE CONSISTANT!

Needless to say I made the changes and then printed a copy off...OOPS! found a mistake, print another copy off...OOPS! found another one! Printed a copy off. And so on and so on. I must have printed 8 different final versions before I turned in the actual final. I was pleased at this, (and I hope those doing internships as well or who are full time can appreciate this:)) after all the work and my eyes were burning from them being so dry and after spending so much time re-reading the same thing over and over and wanting to go home... I caught my last mistake...A dash, yes, a dash that had been bolded that wasn't suppose to be bolded!!!:) I was so proud. Especially because the font was size 8 which you should try some time, print a size 8 dash bolded and then unbolded and see if you can tell the difference. Instantly the tune to "Its beginning to look alot like Christmas," pops into my head except the words now say, "I'm beginning to be more like a banker...I catch mistakes on every page!" Yeah, I am probably delirious, but so happy.

Well I am looking forward to the week. I love this stuff. I love going to work everyday and figuring things out.