Chris Wood, CPA: Captain Excel

A Call to Accounting

Times read: 37

05/14/08

Captain Excel Chris Wood CPA

So my boss just tagged me with a project to teach fundamental accounting to our management and supervisor team. They include the plant manager and all the departmental supervisors, the quality department, human resources, engineering, and more. It contains the basics such as definitions of assets, liabilities, equity, income and expenses. I will go over the assets equal liabilities plus owners’ equity. I will talk about accruals and the matching principle. I’ve been in accounting for 26 years with big 4, local and regional public accounting, manufacturing industry, and private consulting experience, so this can’t be hard. Can it?

It so happens I have some experience at this. I have been teaching an eight hour advanced Excel CPE course for four years now and I have also been presenting one hour breakout sessions for the Ohio Society of CPA’s various conferences and seminars. I have presented Pivot Tables, An Introduction to Excel 07, Access Queries, Advanced Functions, and Tips and Tricks. You can see Tips & Tricks at the Accounting Show in Dayton on Thursday May 22. My shameless plug says go here for more details:

http://www.ohioscpa.com/Public/Catalog/CourseDetails.aspx?courseID=0812001DA

The course has evolved over the years. You get a feel that participants didn’t care for this but were wowed by that, so I substitute that for this. Over the years I’ve had my problem students too. The old 80-20 rule works here, 20% of the students take 80% of your time. I’ve had some instances were a student had NO business taking my class because the fundamentals weren’t there. However, in all cases the student chose to be there. They paid to get specific knowledge that I hope I delivered. There was the time that I presented at a seminar that didn’t have separate breakout sessions. There was an “A&A Update”, “What’s New in Taxes”, and me doing “Advanced Functions”. I remember I was demonstrating the INDIRECT function, and how it could be used to switch a comparison of actual vs. budget to actual vs. forecast with a selection from a validation list. I was getting some ohs and ahs from a few participants that genuinely seemed excited about it, but as I gazed over the rest of the say 100 people out there, I could tell that most of them were tax practitioners, financial planners or public auditors that were bored out of their gourd.

To keep the captive non-accountants from being too bored I have included a series of transaction cycles that will show the various department heads financial ramifications that can be expected as it relates to their job. For instance, here is what happens Ms. Purchaser when you accept an overshipment of stock from a supplier, or here’s the result Mr. Supervisor when you run out that coil of steel on the same job when some of it was meant for another order. I want to show them how they have an impact on the financial statements which are the basis of where everybody is ultimately judged in the Company.

John Adams once said ‘Liberty cannot be preserved without a general knowledge among the people”. I like to think that what’s true in politics holds true for business too. “Financial success cannot occur without a broad comprehension of accounting among the employees.”


Share My Voice 

The High Cost of Gas

Times read: 213

04/23/08

By Chris Wood, CPA - Last weekend I went to South Carolina for a reunion of fellow accountants that I used to work with on a major consulting job I had in Louisville. The Company in Louisville relocated to Pittsburgh. Then it was sold and moved to Cleveland. Besides our host who lives in South Carolina there was one person from Chicago, one person from Cleveland (who canceled) and myself from Columbus. My friend from Chicago had just bought a motorcycle, so we were compelled to go to the Thunder Tower Harley Davidson dealership in Columbia, SC. I always try to buy tee shirts with some local connection for my daughters when I travel out of town.

I asked my friend “Why did you buy that motorcycle? You haven’t ridden one since high school”.
“Quite simple really; my Jeep gets about 12 miles to the gallon and my new bike gets about 53 miles to the gallon”. He replied righteously. And why not have a righteous attitude. A barrel of oil has just topped $117 with no end in sight and a gallon of gas is now $3.60. I want to save too, but I drive a Sentra that gets 34 MPG. I drive 100 miles round trip per day. Let’s do the math:

Looking pretty good, but wait a minute. It will take three years to pay off the bike. So what’s the price of gas three years in the future? We could take a look at the recent history. Let’s see, Google “US gas history”, download, pivot, filter for 2005 through today, insert a graph, format a little, and we get:

Gas has increased from about 1.80 in January of 2005 to about 3.40 in April of 2008. That’s nearly a 90% increase in 3.3 years. So if we assume a similar increase, and why not, it could even be more with increased demand from China or another major terrorist attack. We would be might be looking at an average price per gallon of 4.67 ((3.4-1.8)/1.8*3.5*1.5). Now we get:

Now we’re starting to talk some serious scratch, but wait just another minute. I can’t ride the bike during the winter. I can’t ride it when it’s raining, and I can’t ride it when I’m traveling out of town. So when my friend asks me “Why did you buy that motorcycle? You haven’t ridden one since high school”.

I’ll righteously reply “Because the chicks dig it”. And why not!

Share My Voice 

Inflationary Spreadsheet Tactics

Times read: 339

03/19/08

The stock markets are plunging, subprime woes are drying up the credit market and manufacturing jobs are still leaving this country in droves. How do we keep on top of all these events during the day while we putter around on our computers hoping not to become part of these dreary statistics?

Well the information highway is chock full of all sorts of information and tables just waiting to be accessed and analyzed in Excel. It’s easy and here’s an example. First open a blank sheet in Excel. Select “From Web” in the “Get External Data” group under the “Data” tab.

This will open up a browser window in the internet. I’ve gone to the U.S. Department of labor statistics. There’s plenty to get depressed about here.

Unfortunately, all the data was imported into one row for every individual year. Use the “Test to Columns” feature in the “Data Tools” group under the “Data” tab to clean this up.

Much can be done in Excel using various reference functions to acquire data in the table above. I use a link to the web to import interest rates based on prime and the daily LIBOR to calculate my month end accrual. These tables don’t have to manipulated, so I can just do a refresh on the worksheet with a link to the internet to update the rates for the month.

Back to our example, you can see that 2007 was not a particularly good year. It had the highest increase (8.24) December to December since 1980.

Things aren’t that bad. The percentage increase in 1990 was much worse than 2007 (6.1% vs. 4.1%).

Hopefully, we can all look forward to a better 2008. So far we have an increase of 1.65 through February (211.69- 210.04). That is an annualized change of 9.9 (1.65 /2*12). Hmm, with consumer confidence declining, uncertainty in Iraq and oil prices surging well over $100 per barrel maybe 1990 wasn’t so bad.

Share My Voice 

The Annual Audit Dilemma

Times read: 381

02/12/08

My outside auditors were in all last week performing the various tasks associated with issuing the “audited” financial statements. I’ve been through these endless times, but it’s still unnerving. I dread audit time. What did I miss? What question will they ask that I can’t answer? About the hardest thing they came up with this year was “How do you explain the variance in inventory turns between the years?” I was taken aback. I don’t know. Why have my turns changed so drastically? I’m sure I sit around at the end of everyday agonizing over my inventory turns and why they are different. I know, I’ll ask the plant manager. He’s got his finger on the pulse. Surely he’ll know why my inventory turns have changed.

“Hey Jeff, What’s up? By the way the auditors were asking about inventory turns. Do you know they changed so drastically?” I politely asked.

“Get out of my office bean counter. I’m too busy to be bothered with such trivial matters,” he rudely responded.

“OK Jeff, I’m sorry. Oh yeah, I approved your expense report. I’m going to get that paid” I responded, and then under my breath I muttered “in 2015”. He’ll never learn.
Still I need to know why my inventory turns have changed. I know, I’ll ask the IT manager. He’s always creating reports about this and that with the manufacturing software.

“Hey Nick, How ya doin’? The auditors want to know why our inventory turns changed so drastically. I told them you’d know for sure. You probably already have a report that explains it all. Don’t you?”

“No, I don’t.” Nick dryly retorted. “Would you like to see the bookings for the next three months?”

“Yeah go ahead and email that to me.” Still, I need to tell the auditors something. If I can’t answer that, they’ll think something else is wrong. They’ll expand their testing. They’ll be here forever. I’ve got to come up with something.

My auditors’ names are Josh and Courtney. Whatever happened to Bill and Tom or Karen and Debbie. I must be getting too old when I speculate over the names of my auditors. “Hey Josh, I noticed that you used the labor and overhead content in my inventory to calculate the theoretical inventory turns. Be advised that we reduced our labor by about 2,000 hours when we moved our stamping operation to Tennessee. We now purchase that material that we used to make. Also that new automated cell we put in this year reduced labor by another 1,500 hours. That’s probably why you’re calculating that variance”.

“OK, Thanks.” He said as he documented his work papers. That’s it? I can’t wait until next year’s audit.

Next time – Pivot tables. Accountants that know how to use pivot tables use them often.


Share My Voice  | 1 Voice(s) Have Spoken. Read What They Said!

Wooing the non-accounting with Excel know-how

Times read: 515

01/10/08

I took a four day trip to New Orleans with several friends to attend the BCS national championship game and have a good time in general. We all wanted to be fair, so we decided to split the cost of food, lodging and other expenses evenly. How better to keep track and allocate cost than with an Excel spreadsheet. I could use it to allocate meals using the specific identification method. Let’s see, you had the Jambalaya, that’s $12.50, and you had the Po-Boy that’s $9.95. I’ll use a formula to determine the portion of the tip. Some expenses can be allocated based on a percentage. The parking was $25 divided by four equals $6.25 each. I would be able show all my friends what their share of all of the expenses were. I knew they would be impressed and amazed at what I could do with an Excel spreadsheet. They were not accountants and never or rarely used a spreadsheet. Well today I finally got around to summarizing my costs for the trip.

I bought the airfare in September when it was only $35 each way. The airport and security fees pushed the total cost to $100.80. I spent an extra $20 to get priority seating so I could sit in an aisle seat in the exit row. That’s the best $20 I ever spent. You’re really not risking that much if your team doesn’t make it to the nation championship game. The worst that can happen is that you skip the game and visit New Orleans anyway. A win/win situation.

The ticket was through a friend of a friend of a friend who knew someone in the athletic department of a major university that knew someone in the athletic department at The Ohio State University that owed them a favor. At least that’s what I’m told, but let’s face it. It was a great seat at face value. I hit the lotto here.

The Cajun Cabin has bad food and even worse service. If you ever find yourself hungry on Bourbon Street, don’t go there.

Not showing is the lodging. I was able to use reward points to get a free room and breakfast. A perk I still carry from my days on the road as a consultant. I reserved the room in September when I got the flight, and it was a good thing because the rewards rooms were no longer available in December.

The last expense for $103 just melted out of my pocket. I think most of it was for rounds of beer, but I just can’t seem to remember!?! I’m pretty sure this last expense is responsible for preventing a final pooling of receipts and disbursements for a reconnoitering of cash due to/from everyone. I think that we all realized we got to see the game with all the amenities on the cheap compared to travel packages ranging from $2,000 to $3,000, so if I get this round or buy this dinner and you get the next one, then that’s good enough.

I’ve still got some unused frequent flier miles. I’m ready to book my tickets to Miami next year. GO BUCKS!


Share My Voice